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