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