Feature #461
openMark Customer Inactive if no transaction for 6 months not happened
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
Subtasks
Related issues
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))
Updated by Theja Ponon 29 days ago
- Assignee changed from Amal Mathew to aryamol Vh