SQL
Discus with Shanto and Junide
---- Based on Consider SALE -input
SELECT TO_CHAR('2023-12-31'::DATE, 'Month') AS str_month;
SELECT 'Sale' AS str_type,
mofsm.sin_mod_of_sale_type,
mofsm.fk_customer_ac_id,
mofsm.fk_cash_counter_id,
sm.fk_cost_centre_id,
spd.fk_supplier_id,
spd.fk_service_id,
TO_CHAR(spd.dat_sale, 'Month') AS str_month, --Dynamically set spd.dat_issue or spd.dat_sale based on
TO_CHAR(spd.dat_sale, 'YYYY') AS str_year -- Dynamically set spd.dat_issue or spd.dat_sale based on
doc.fk_branch_id,
SUM(spdf.dbl_supplier_amount) AS dbl_supplier_amount,
SUM(spdf.dbl_customer_price) AS dbl_customer_price,
SUM(spdf.dbl_normal_profit) AS dbl_normal_profit
FROM sale.tbl_mod_of_sale mofsm
LEFT JOIN sale.tbl_sale_master sm
ON mofsm.fk_sale_master_id = sm.pk_sale_master_id
LEFT JOIN document.tbl_documents doc
ON sm.fk_documents_id = doc.pk_documents_id
LEFT JOIN sale.tbl_mod_of_sale_supp_doc mofsd
ON mofsm.pk_mod_of_sale_id = mofsd.fk_mod_of_sale_id
LEFT JOIN service.tbl_supp_doc_no spd
ON mofsd.fk_supp_doc_no_id = spd.pk_supp_doc_no_id
LEFT JOIN service.tbl_supp_doc_fare spdf
ON mofsd.fk_base_fare_id = spdf.pk_supp_doc_fare_id
WHERE doc.int_sys_action_id != -1
GROUP BY mofsm.sin_mod_of_sale_type,
mofsm.fk_customer_ac_id,
mofsm.fk_cash_counter_id,
sm.fk_cost_centre_id,
spd.fk_supplier_id,
spd.fk_service_id,
str_month,
str_year,
doc.fk_branch_id
ORDER BY str_month --or dat_issue based on input
UNION ALL -- Based on Consider REFUND - input
*REFUND...
*.....
*....
* WHERE sin_sys_refund_side_status = 4 and/or 5
-- 4 = Void(From Refund GUI)
-- 5 = Refund
* Refund date or Cancel date use
- Base1 Currency
LEFT JOIN service.tbl_supp_doc_fare spdf
ON mofsd.fk_base_fare_id = spdf.pk_supp_doc_fare_id
- Other Currency Case
LEFT JOIN service.tbl_supp_doc_fare spdf
ON mofsd.fk_cus_ledg_fare_id = spdf.pk_supp_doc_fare_id
* Less refund amount from sales
