TAAS

Solutions

-Ref Ledger Statement
-Consider SOA_SKIP_SETTILED_DOCUMENTS_AFTER_TO_DATE_BASED_ON_DATE admin setting - 'Consider Matching Done After To Date' filter case 

1) Summary
- Like Ledger Statement
1.1)Opening Balance
1.2)Current Amount
1.3)After To Date Amount

1.4)Total Not Approved Document Amount - [System Based(Before From Date + Current + After to Date)]
*sin_document_status = 0

1.5)Total Not Approved Posting Amount- [System Based(Before From Date + Current + After to Date)]
*sin_posting_status = 0 and sin_document_status = 1

1.6)Expected Posting(Paid Cheque Control Account to Bank) - [Only Bank Book, and Ledger Stmt]
*sin_transaction_status = 0

1.7)Projected Balance - [Bold]

1.8)Uncleared Received Cheque - [Total -> chr_received_cheque_status is not in ['P', 'F', 'C']]
1.9)Uncleared Paid Cheque - [Total chr_paid_cheque_status is not in ['P', 'F', 'C']]

-SQL:-
SELECT tr.sin_posting_status,
        tr.sin_transaction_status,
        tr.sin_document_status,
        rc.chr_current_cheque_status AS chr_received_cheque_status,
        pc.chr_current_cheque_status AS chr_paid_cheque_status,
        tr.dat_transaction,
        SUM(tr.dbl_base_cur_debit) AS dbl_base_cur_debit,
        SUM(tr.dbl_base_cur_credit) AS dbl_base_cur_credit,
        SUM(tr.dbl_ledg_cur_debit) AS dbl_ledg_cur_debit,
        SUM(tr.dbl_ledg_cur_credit) AS dbl_ledg_cur_credit
    FROM transaction.tbl_transaction tr
        LEFT JOIN receivedcheque.tbl_received_cheque rc
            ON tr.fk_oposit_received_cheque_id = rc.pk_received_cheque_id
        LEFT JOIN paidcheque.tbl_paid_cheque pc
            ON tr.fk_oposit_paid_cheque_id = pc.pk_paid_cheque_id
    WHERE tr.fk_ledger_id = 1
        AND tr.fk_branch_id = 1
        AND tr.fk_department_id = 1
        ...
    GROUP BY tr.sin_posting_status,
        tr.sin_transaction_status,
        tr.sin_document_status,
        rc.chr_current_cheque_status,
        pc.chr_current_cheque_status,
        tr.dat_transaction;

-Where Cases
* Status not consider
*Date not consider
*Matched and Outstanding not consider
*Consider Matching Done After To Date  not consider
*Show Matched Net Difference After OB not consider
*Show Opening Balance not consider
-If need the join Doc table
-Amount fields based on selected currency base

2) Details
-OB record calculate from SUMMARY iterations based on Status(sin_posting_status, sin_transaction_status, sin_document_status), date, etc... filter inputs
-SQL Same as Ledger stmt, 
*Select fk_documents_id, sin_group_id also,  
*order by: dat_transaction, fk_documents_id, fk_main_ledger_id, fk_ledger_id, sin_group_id
if (sin_group_id != 0) 
    if (binPrvDocumentsId == fk_documents_id and sinPrvGroupId == sin_group_id)
        lst[-1].amout += new amount
    else 
       lst.append()
else
   lst.append()

binPrvDocumentsId = fk_documents_id;
sinPrvGroupId = sin_group_id 

3) Not reported tab -> In NOT REPORTED SALE AND REFUND file use
SQL

SELECT tmp.vhr_supp_doc_no,
        tmp.dat_issue_or_cancel,
        tmp.fk_service_id,
        srv.vhr_service_code,
        srv.vhr_service_name,
        tmp.sin_record_type,
        tmp.sin_sys_status,
        tmp.fk_pax_profile_id,
        COALESCE(tmp.vhr_pax_name, pxp.vhr_pax_name, '') AS vhr_pax_name,
        tmp.sin_no_of_adult,
        tmp.sin_no_of_child,
        tmp.sin_no_of_infant,
        tmp.vhr_currency,
        tmp.dbl_customer_price
FROM (
   SELECT spd.vhr_supp_doc_no,
        CASE WHEN sin_record_type = 1 THEN spd.dat_issue ELSE spd.dat_cancel END AS dat_issue_or_cancel,
        spd.fk_service_id,
        tkt.sin_record_type,
        CASE WHEN sin_record_type = 1 THEN spd.sin_sys_sale_side_status ELSE spd.sin_sys_refund_side_status END AS sin_sys_status,
        tkt.fk_pax_profile_id,
        tkt.vhr_pax_name,
        tkt.sin_no_of_adult,
        tkt.sin_no_of_child,
        tkt.sin_no_of_infant,
        sfr.vhr_currency,
        sfr.dbl_customer_price
    FROM airticket.tbl_ticket tkt
        LEFT JOIN service.tbl_supp_doc_no spd
            ON tkt.fk_supp_doc_no_id = spd.pk_supp_doc_no_id
        LEFT JOIN service.tbl_supp_doc_fare sfr
            ON tkt.fk_ticket_cus_ledg_fare_id = sfr.pk_supp_doc_fare_id
    WHERE tkt.int_sys_action_id != -1
            AND ((sin_record_type = 1 AND  spd.dat_issue BETWEEN '2023-07-01' AND '2023-07-31' 
            AND  spd.sin_sys_sale_side_status = 1) 
           OR (sin_record_type = 2 AND spd.dat_cancel BETWEEN '2023-07-01' AND '2023-07-31' 
               AND spd.sin_sys_refund_side_status IN (1,2)))
               ...
        UNION ALL
        [Hotel]
        UNION ALL
        [TRANSFER]
        UNION ALL
        [VISA]
        UNION ALL
        [OTHER]) AS tmp
LEFT JOIN service.tbl_service srv
    ON tmp.fk_service_id = srv.pk_service_id
LEFT JOIN pax.tbl_pax_profile pxp
    ON tmp.fk_pax_profile_id = pxp.pk_pax_profile_id

4) Ageing - AGEING file use in CS
- FnGetAAccountAgeing(Inputs) 

* Ageing Period = '0,30, 60, 90, 180, 360' or 0-30-60-90-180-360 [* Remove space and check - or ,    1st item not zero add a zero],  [Default load from admin settings - Ageing Periods ]
* From Date = 07/08/2023
-SQL
SELECT sin_period_no,
    MIN(dat_transaction) AS dat_period_min_transaction,
    MAX(dat_transaction) AS dat_period_max_transaction,
    SUM(dbl_dr_outstanding) - SUM(dbl_cr_outstanding) AS dbl_period_outstanding
FROM (
    SELECT dat_transaction,
           CASE WHEN dat_transaction > '2023-08-07' THEN 6 -- 0 -> >07/08/2023
                WHEN dat_transaction >= '2023-07-08' THEN 5 -- 1-30 -> 08/07/2023-07/08/2023
                WHEN dat_transaction >= '2023-06-08' THEN 4 -- 31-60 -> 08/06/2023-07/07/2023
                WHEN dat_transaction >= '2023-05-09' THEN 3 -- 61-90- -> 09/05/2023-07/06/2023
                WHEN dat_transaction >= '2023-02-08' THEN 2 -- 91-180 -> 08/02/2023-08/05/2023
                WHEN dat_transaction >= '2022-08-12' THEN 1 -- 181-360 -> 12/08/2022-07/02/2023
                ELSE 0 END AS sin_period_no, -- >360 -> <12/08/2022
           dbl_dr_outstanding,
           dbl_cr_outstanding
    FROM (
        SELECT tr.dat_transaction,
            SUM(tr.dbl_ledg_cur_outstanding) FILTER (WHERE tr.dbl_ledg_cur_debit > 0) AS dbl_dr_outstanding,
            SUM(tr.dbl_ledg_cur_outstanding) FILTER (WHERE tr.dbl_ledg_cur_credit > 0) AS dbl_cr_outstanding
        FROM transaction.tbl_transaction tr
            LEFT JOIN document.tbl_documents doc
                ON tr.fk_documents_id = doc.pk_documents_id
        WHERE tr.sin_posting_status = 1
            AND tr.sin_transaction_status = 1
            AND tr.sin_document_status = 1
            AND tr.dbl_cr_outstanding > 0
            AND tr.fk_ledger_id = 1
            AND tr.fk_branch_id = 1
            AND tr.fk_department_id = 1
                        ...
        GROUP BY tr.dat_transaction) AS tmp1
    ) AS tmp2
GROUP BY sin_period_no;

Note:-
* Case when part in sql dynamic
*************************************************
SELECT tr.dat_transaction,
            SUM(tr.dbl_ledg_cur_outstanding) FILTER (WHERE tr.dbl_ledg_cur_debit > 0) AS dbl_dr_outstanding,
            SUM(tr.dbl_ledg_cur_outstanding) FILTER (WHERE tr.dbl_ledg_cur_credit > 0) AS dbl_cr_outstanding
        FROM transaction.tbl_transaction tr
            LEFT JOIN document.tbl_documents doc
                ON tr.fk_documents_id = doc.pk_documents_id
        WHERE tr.sin_posting_status = 1
            AND tr.sin_transaction_status = 1
            AND tr.sin_document_status = 1
            AND tr.dbl_cr_outstanding > 0
            AND tr.fk_ledger_id = 1
            AND tr.fk_branch_id = 1
            AND tr.fk_department_id = 1
                        ...
        GROUP BY tr.dat_transaction) AS tmp1

or

SELECT tr.dat_transaction,
        SUM(CASE WHEN tr.dbl_ledg_cur_debit > 0 THEN tr.dbl_ledg_cur_outstanding ELSE 0 END) AS dbl_dr_outstanding,
        SUM(CASE WHEN tr.dbl_ledg_cur_credit > 0 THEN tr.dbl_ledg_cur_outstanding ELSE 0 END) AS dbl_cr_outstanding
    FROM transaction.tbl_transaction tr
        LEFT JOIN document.tbl_documents doc
            ON tr.fk_documents_id = doc.pk_documents_id
    WHERE tr.sin_posting_status = 1
        AND tr.sin_transaction_status = 1
        AND tr.sin_document_status = 1
        AND tr.dbl_cr_outstanding > 0
        AND tr.fk_ledger_id = 1
        AND tr.fk_branch_id = 1
        AND tr.fk_department_id = 1
        ...
    GROUP BY tr.dat_transaction;
*************************************************

*IF NOT (MATCHED and OUTSTANDING) and Consider Matching Done After To Date UNTICK then JOIN -> Check ledger stmt
* ON tr.pk_transaction_id = ???.fk_transaction_id

*SUM(stld.dbl_ledg_cur_matched) AS dbl_not_consider_matched_amount, -- the matched amount add to Outstanding
SELECT stld.fk_transaction_dr_id AS fk_transaction_id,
    SUM(stld.dbl_ledg_cur_matched) AS dbl_ledg_cur_matched,
    SUM(stld.dbl_base_cur_matched) AS dbl_base_cur_matched,
    SUM(stld.dbl_cons_cur_matched) dbl_cons_cur_matched
FROM settlement.tbl_settlement stld
    LEFT JOIN settlement.tbl_settlement_master stlm
        ON stld.fk_settlement_master_id = stlm.pk_settlement_master_id
WHERE stlm.fk_ledger_id = 1
        AND currency = 'SQR
    AND stlm.dat_settlement > '2023-08-30' --*To Date --* or dat_transaction_dr Based on Admin settings[SOA Skip SETTLED DOCUMENT After TO DATE Based on date]
        AND stld.dat_transaction_dr <= '2023-08-30' --*To Date
GROUP BY stld.fk_transaction_dr_id

UNION ALL

SELECT stld.fk_transaction_cr_id AS fk_transaction_id,
    SUM(stld.dbl_ledg_cur_matched) AS dbl_ledg_cur_matched,
    SUM(stld.dbl_base_cur_matched) AS dbl_base_cur_matched,
    SUM(stld.dbl_cons_cur_matched) dbl_cons_cur_matched
FROM settlement.tbl_settlement stld
    LEFT JOIN settlement.tbl_settlement_master stlm
        ON stld.fk_settlement_master_id = stlm.pk_settlement_master_id
WHERE stlm.fk_ledger_id = 1
                AND currency = 'SQR
    AND stlm.dat_settlement > '2023-08-30' --*To Date --* or dat_transaction_cr Based on Admin settings[SOA Skip SETTLED DOCUMENT After TO DATE Based on date]
        AND stld.dat_transaction_cr <= '2023-08-30' --*To Date
GROUP BY stld.fk_transaction_cr_id

*ADD THE AMOUNT to OUTSTANDING

TESTING DONE

TESTING DONE

TESTING DONE

TESTING DONE

TESTING DONE

ISSUE FOUNDED

1.SEPERATE THE REVERSAL DOC DEBIT CREDIT AMOUNTS ON SEPERATE COLUMNS

Changes
-------------
(DONE)1. Change the title of the print Statement of Account, Customer Ageing Report--for ageing tab, Not Reported Statement of Account--for not sold tab
, Statement of Account(Ticket)