Project

General

Profile

Edit Copy Actions

Feature #461

open

Mark Customer Inactive if no transaction for 6 months not happened

Added by Theja Ponon 2 months ago. Updated 29 days ago.

Status:
Ready for Coding
Priority:
Normal
Assignee:
Target version:
Start date:
01/07/2025
Due date:
% Done:

20%

Estimated time:
Owner(Agency):
NBK - Qatar
Time Taken(HH):
Module:
NBK Requirements
Tested By:
Code Reviewed By:

Description

System will mark a customer as inactive incase of no transactions happened for the customer for the six months -NBK Requirement


Add

Subtasks


Add

Related issues

Actions #1

Updated by Theja Ponon 2 months ago

  • Description updated (diff)

Updated by Anil KV about 2 months ago

  • Status changed from New to Ready for Coding
  • Assignee changed from travvise Admin to Amal Mathew
  • % Done changed from 0 to 20

461->Mark Customer Inactive if no transaction for 6 months not happened
=======================================================================
Menu:- Report -> Audit Report -> Inactive Accounts
=>Input:-
->Account Category*R multi select - Load journal gui popup -> Account label dropdowns(Account Not Need) - Default Tick Customer
->Inactive Period Chckbox | Month/Days/Years*r inputbox - Default month->6, Date: title:- Last Transaction Date -> Ref Logic->1
*Checkbox group->title->Tick means no transaction accounts, 
* Tick then disable inputbox and value emty
->Account Status - Mult Select -> ref Customer GUI status -> Untick 'Close' and 'Block'

=>List
->Header Grouping No Grouping/Category/Type/Group/Family
# | Checkbox | Code | Name | Phone | EMail | Address | Account Balance(Dr-Cr) | Status | Last Document -> Date | Last Document -> Number | Last Document -> Amount | Account Query Redirect icon
* Last Document main colus

=> Right side main button last 
-> Add 'Block' Button

=> Logic
-> Find out a datInputLastTranDate based on 'Inactive Period' and Current date
-> SQL

    WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY tr.fk_ledger_id ORDER BY tr.dat_transaction DESC, pk_transaction_id DESC) AS int_row_number,
                    tr.fk_ledger_id,
            ac.vhr_account_code,
                    ac.vhr_account_name,
            ac.vhr_display_name,
                    ac.sin_ac_category,
                    ac.fk_category_id,
            ac.fk_group_id,
            ac.fk_type_id,
            ac.fk_family_id,
                    ac.vhr_phone,
            ac.vhr_email,
            ac.vhr_address,
                    tr.dat_transaction,
                    tr.fk_documents_id,
                    doc.vhr_document_no,
                    doc.-amount,
                    doc.-doc_date
            tr.dbl_base_cur_debit,
                    tr.dbl_base_cur_credit
               FROM transaction.tbl_transaction tr
            LEFT JOIN accounts.tbl_account ac
                ON tr.fk_ledger_id = ac.pk_account_id
            LEFT JOIN document.tbl_documents doc
                ON tr.fk_documents_id = doc.pk_documents_id
           WHERE sin_ac_category IN (1,5,8)
            AND sin_record_status IN (1,2) 
            AND dat_transaction < datInputLastTranDate
            AND tr.fk_ledger_id NOT IN (SELECT DISTINCT fk_ledger_id FROM transaction.tbl_transaction WHERE dat_transaction >= datInputLastTranDate ))

    SELECT trc.fk_ledger_id,
            trc.vhr_account_code,
                    trc.vhr_account_name,
            trc.vhr_display_name,
                    trc.sin_ac_category,
                    trc.fk_category_id,
            trc.fk_group_id,
            trc.fk_type_id,
            trc.fk_family_id,
                    trc.vhr_phone,
            trc.vhr_email,
            trc.vhr_address,
                    trc.dat_transaction,
                    trc.fk_documents_id,
                    trc.vhr_document_no,
                    trc.-amount,
                    trc.-doc_date
            trc.dbl_base_cur_debit,
                    trc.dbl_base_cur_credit,
            lsum.dbl_total_base_cur_debit,
                    lsum.dbl_total_base_cur_credit
    FROM cte AS trc
    LEFT JOIN (SELECT  fk_ledger_id
            SUM(dbl_base_cur_debit) AS dbl_total_base_cur_debit,
            SUM(dbl_base_cur_credit) AS dbl_total_base_cur_credit)
           FROM cte 
           GROUP BY fk_ledger_id) lsum
         ON trc.fk_ledger_id = lsum.fk_ledger_id
    WHERE int_row_number = 1

Updated by Anil KV about 2 months ago

If not transaction account SQL

SELECT ...
FROM accounts.tbl_account
WERE pk_account_id NOT IN (SELECT DISTINCT fk_ledger_id FROM transaction.tbl_transaction))

Actions #4

Updated by Theja Ponon 29 days ago

  • Assignee changed from Amal Mathew to aryamol Vh
Edit Copy Actions

Also available in: Atom PDF