TAAS

Solution

1) GUI Changes
->Issue Date*(Default) and Supplier Bill Date* toggle
-Consider Items -> Like Customer Sale & Refund Report - Tick All Items
-Only BSP Suppliers
2) Summary
- Issued, Cancelled, ADM, ACM, Credit Note, Debit Note, Other
*Issue, ADM, Credit Note, Other(Cr) -> Cr=Supplier Amount
*Cancelled, ACM, Debit Note, Other(Dr) -> Dr=Supplier Amount, amounts are -ve
3) Issue
SELECT spd.vhr_supp_doc_no,
        spd.dat_issue,
        spd.dat_sup_billing_issue,
        spd.dat_sale,
        spd.vhr_invoice_doc_no,
        (spd.jsn_related_data->'AIR_NUM_CODE'::VARCHAR) AS vhr_airline_num_code,
        sfr.vhr_currency,
        (sfr.dbl_fare - sfr.dbl_fare_paid_agency_cc - sfr.dbl_fare_paid_cust_cc - sfr.dbl_fare_paid_pax_cc - sfr.dbl_fare_shared_supplier - sfr.dbl_fare_shared_account) AS dbl_mf_credit,
        (sfr.dbl_fare_paid_agency_cc + sfr.dbl_fare_paid_cust_cc + sfr.dbl_fare_paid_pax_cc) AS dbl_mf_cash,
        (sfr.dbl_tax - sfr.dbl_tax_paid_agency_cc - sfr.dbl_tax_paid_cust_cc - sfr.dbl_tax_paid_pax_cc - sfr.dbl_tax_shared_supplier - sfr.dbl_tax_shared_account) AS dbl_tax_credit,
        (sfr.dbl_tax_paid_agency_cc - sfr.dbl_tax_paid_cust_cc - sfr.dbl_tax_paid_pax_cc) AS dbl_tax_cash,
        sfr.dbl_comm_percentage,
        (sfr.dbl_commission - sfr.dbl_commission_shared_supplier - sfr.dbl_commission_shared_account) AS dbl_commission,
        sfr.dbl_paid_commission_tax,
        (sfr.dbl_supplier_amount - sfr.dbl_supplier_payable_shared_supplier - sfr.dbl_supplier_payable_shared_account) AS dbl_supplier_amount,
        (sfr.dbl_supplier_amount - sfr.dbl_supplier_paid_agency_cc - sfr.dbl_supplier_paid_cust_cc - sfr.dbl_supplier_paid_pax_cc - sfr.dbl_supplier_payable_shared_supplier - sfr.dbl_supplier_payable_shared_account) AS dbl_supplier_payable
    FROM service.tbl_supp_doc_ap_sup_sharing sups
        INNER JOIN service.tbl_supp_doc_no spd
            ON sups.fk_supp_doc_no_id = spd.pk_supp_doc_no_id
                   AND sups.sin_supp_doc_category = 1
                AND sups.sin_record_type = 1 
                AND spd.sin_last_action != 0
        LEFT JOIN service.tbl_supp_doc_fare sfr
            ON sups.fk_supp_doc_sup_ledg_fare_id = sfr.pk_supp_doc_fare_id --Input currency base
    --WHERE 
        --AND (spd.sin_sys_sale_side_status = IN (1,2),
        --AND spd.dat_issue BETWEEN '2023-08-08' AND '2023-08-08' //Bill Date Case -> AND ((spd.dat_sup_billing_issue IS NOT NULL AND spd.dat_sup_billing_issue BETWEEN '2023-08-08' AND '2023-08-08') OR spd.dat_sup_billing_issue IS NULL AND spd.dat_issue BETWEEN '2023-08-08' AND '2023-08-08')

4) Cancel
SELECT spd.vhr_supp_doc_no,
        spd.dat_cancel,
        spd.dat_sup_billing_cancel,
        spd.dat_refund,
        spd.vhr_refund_doc_no,
        (spd.jsn_related_data->'AIR_NUM_CODE'::VARCHAR) AS vhr_airline_num_code,
        sfr.vhr_currency,
        (sfr.dbl_fare - sfr.dbl_fare_paid_agency_cc - sfr.dbl_fare_paid_cust_cc - sfr.dbl_fare_paid_pax_cc - sfr.dbl_fare_shared_supplier - sfr.dbl_fare_shared_account) AS dbl_mf_credit,
        (sfr.dbl_fare_paid_agency_cc + sfr.dbl_fare_paid_cust_cc + sfr.dbl_fare_paid_pax_cc) AS dbl_mf_cash,
        (sfr.dbl_tax - sfr.dbl_tax_paid_agency_cc - sfr.dbl_tax_paid_cust_cc - sfr.dbl_tax_paid_pax_cc - sfr.dbl_tax_shared_supplier - sfr.dbl_tax_shared_account) AS dbl_tax_credit,
        (sfr.dbl_tax_paid_agency_cc - sfr.dbl_tax_paid_cust_cc - sfr.dbl_tax_paid_pax_cc) AS dbl_tax_cash,
        sfr.dbl_comm_percentage,
        (sfr.dbl_commission - sfr.dbl_commission_shared_supplier - sfr.dbl_commission_shared_account) AS dbl_commission,
        sfr.dbl_paid_commission_tax,
        (sfr.dbl_sup_rfd_charge - sfr.dbl_sup_rfd_charge_paid_agency_cc - sfr.dbl_sup_rfd_charge_paid_cust_cc - sfr.dbl_sup_rfd_charge_paid_pax_cc - sfr.dbl_sup_rfd_charge_shared_supplier - sfr.dbl_sup_rfd_charge_shared_account) AS dbl_sup_rfd_charge_cash,
        (sfr.dbl_sup_rfd_charge_paid_agency_cc + sfr.dbl_sup_rfd_charge_paid_cust_cc + sfr.dbl_sup_rfd_charge_paid_pax_cc) AS dbl_sup_rfd_charge_cash,
        (sfr.dbl_supplier_amount - sfr.dbl_supplier_payable_shared_supplier - sfr.dbl_supplier_payable_shared_account) AS dbl_supplier_amount,
        (sfr.dbl_supplier_amount - sfr.dbl_supplier_paid_agency_cc - sfr.dbl_supplier_paid_cust_cc - sfr.dbl_supplier_paid_pax_cc - sfr.dbl_supplier_payable_shared_supplier - sfr.dbl_supplier_payable_shared_account) AS dbl_supplier_receivable
    FROM service.tbl_supp_doc_ap_sup_sharing sups
        INNER JOIN service.tbl_supp_doc_no spd
            ON sups.fk_supp_doc_no_id = spd.pk_supp_doc_no_id
                   AND sups.sin_supp_doc_category = 1
                AND sups.sin_record_type = 2 
                AND spd.sin_last_action != 0
        LEFT JOIN service.tbl_supp_doc_fare sfr
            ON sups.fk_supp_doc_sup_ledg_fare_id = sfr.pk_supp_doc_fare_id --Input currency base
    --WHERE 
        --AND (spd.sin_sys_sale_side_status = IN (1,2),
        --AND spd.dat_cancel BETWEEN '2023-08-08' AND '2023-08-08' //Bill Date Case -> AND ((spd.dat_sup_billing_cancel IS NOT NULL AND spd.dat_sup_billing_cancel BETWEEN '2023-08-08' AND '2023-08-08') OR spd.dat_sup_billing_cancel IS NULL AND spd.dat_cancel BETWEEN '2023-08-08' AND '2023-08-08')
5) ADM/ACM, CN/DN, Other -> vhr_sys_module_name base grouping in .cs
SELECT tr.vhr_sys_module_name,
    doc.vhr_document_no,
    tr.dat_transaction,
    tr.dbl_ledg_cur_debit,
    tr.dbl_ledg_cur_credit,
    tr.vhr_reference,
    tr.txt_narration
    --jsn_related_data
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.fk_ledger_id = 1
    --AND tr.fk_branch_id = 1
    --AND tr.fk_department_id = 1
ORDER BY tr.dat_transaction

ISSUE FOUND
-----------------------
1) Showing invoice date for ticket that is only issued.
2) Data not showing in DN/CN tab.