fzi_r_report_montly_cashbank_out
unknown
mysql
a year ago
13 kB
13
Indexable
CREATE OR REPLACE FUNCTION fzi_r_report_montly_cashbank_out(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying)
RETURNS SETOF refcursor
LANGUAGE plpgsql
AS $function$
DECLARE
pRefHeader REFCURSOR := 'refHeader';
pRefDetail REFCURSOR := 'refDetail';
pSessionId ALIAS FOR $1;
pTenantId ALIAS FOR $2;
pUserId ALIAS FOR $3;
pRoleId ALIAS FOR $4;
pDatetime ALIAS FOR $5;
pOuId ALIAS FOR $6;
pCashbankId ALIAS FOR $7;
pYearMonth ALIAS FOR $8;
pStatusDoc ALIAS FOR $9;
vStatusReleased varchar := 'R';
vDocTypePo bigint := 101;
vDocTypePurchaseInvoice bigint := 131;
vDocTypeCashbankTransfer bigint := 631;
BEGIN
--- Cashbank Transfer ( Tujuan Transfer)
CREATE TEMP TABLE ttzi_montly_cashbank_out ON COMMIT DROP AS
SELECT fzi_get_week_of_month(A.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
0 AS total_weekly, TO_CHAR(TO_DATE(A.doc_date, 'YYYYMMDD'), 'DD Mon YYYY') AS doc_date, ' ' AS coa_first_line, ' ' AS coa_desc_first_line,
0 AS amount_first_line, 0 AS total_daily, 0 AS saldo_daily,
D.main_acc || '.' || D.sub_acc AS coa_item, D.coa_desc AS coa_desc_item,
B.receive_amount AS amount
FROM cb_transfer_cashbank A
JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
JOIN m_cashbank C ON B.cashbank_to_id = C.cashbank_id
JOIN m_coa D ON C.coa_id = D.coa_id
WHERE A.cashbank_id = pCashbankId
AND LEFT(A.doc_date, 6) = pYearMonth
AND A.status_doc = vStatusReleased
ORDER BY A.doc_date ASC;
--- Cashbank Transfer ( Biaya Transfer)
INSERT INTO ttzi_montly_cashbank_out (week_index, week_desc, saldo_weekly_start, total_weekly, doc_date, coa_first_line, coa_desc_first_line,
amount_first_line, total_daily, saldo_daily, coa_item, coa_desc_item, amount)
SELECT fzi_get_week_of_month(A.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
0 AS total_weekly, TO_CHAR(TO_DATE(A.doc_date, 'YYYYMMDD'), 'DD Mon YYYY') AS doc_date, '' AS coa_first_line, '' AS coa_desc_first_line,
0 AS amount_first_line, 0 AS total_daily, 0 AS saldo_daily,
D.main_acc || '.' || D.sub_acc AS coa_item, D.coa_desc AS coa_desc_item,
B.cost_amount AS amount
FROM cb_transfer_cashbank A
JOIN cb_transfer_cashbank_cost B ON B.transfer_cashbank_id = A.transfer_cashbank_id
JOIN m_activity_gl C ON C.activity_gl_id = B.activity_gl_id
JOIN m_coa D ON C.coa_id = D.coa_id
WHERE A.cashbank_id = pCashbankId
AND LEFT(A.doc_date, 6) = pYearMonth
AND A.doc_type_id = vDocTypeCashbankTransfer
AND A.status_doc = vStatusReleased
ORDER BY A.doc_date ASC;
--- Cashbank Out (Purchase Invoice -- Persediaan/Stok)
INSERT INTO ttzi_montly_cashbank_out (week_index, week_desc, saldo_weekly_start, total_weekly, doc_date, coa_first_line, coa_desc_first_line,
amount_first_line, total_daily, saldo_daily, coa_item, coa_desc_item, amount)
SELECT fzi_get_week_of_month(I.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
0 AS total_weekly, TO_CHAR(TO_DATE(I.doc_date, 'YYYYMMDD'), 'DD Mon YYYY') AS doc_date, '' AS coa_first_line, '' AS coa_desc_first_line,
0 AS amount_first_line, 0 AS total_daily, 0 AS saldo_daily,
D.main_acc || '.' || D.sub_acc AS coa_item, D.coa_desc AS coa_desc_item, A.item_amount as amount
FROM pu_po_balance_invoice A
JOIN pu_receive_goods_item B ON A.ref_item_id = B.receive_goods_item_id AND A.ref_id = B.receive_goods_id
JOIN pu_po_item C ON B.ref_id = C.po_item_id AND B.ref_doc_type_id = vDocTypePo /* vDocTypePo */
JOIN m_coa D ON D.coa_id = f_get_product_coa_group_product(B.tenant_id, B.product_id)
JOIN fi_invoice_ap_balance E ON A.invoice_id = E.invoice_ap_id AND E.doc_type_id = vDocTypePurchaseInvoice /* purchase invoice */
JOIN fi_payment_order_invoice F ON E.doc_type_id = F.ref_doc_type_id AND E.invoice_ap_balance_id = F.ref_id
JOIN fi_payment_order G ON F.payment_order_id = G.payment_order_id
JOIN cb_trx_cashbank_balance H ON H.payment_id = G.payment_order_id AND H.doc_type_id = G.doc_type_id
JOIN cb_in_out_cashbank I ON I.ref_id = H.trx_cashbank_balance_id AND I.doc_type_id = H.ref_doc_type_id
JOIN pu_invoice J on J.invoice_id = E.invoice_ap_id AND J.doc_type_id = E.doc_type_id
WHERE LEFT(I.doc_date, 6) = pYearMonth
AND C.flg_stock = 'Y'
AND A.flg_invoice = 'Y'
AND E.flg_payment = 'Y'
AND G.status_doc = 'R'
AND EXISTS (
SELECT 1
FROM cb_in_out_cashbank_payment K
WHERE I.in_out_cashbank_id = K.in_out_cashbank_id
AND K.cashbank_id = pCashbankId
)
ORDER BY I.doc_date ASC;
-- SELECT fzi_get_week_of_month(I.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
-- 0 AS total_weekly, I.doc_date , ' ' AS coa_first_line, ' ' AS coa_desc_first_line,
-- 0 AS amount_first_line, 0 AS total_daily, 0 AS saldo_daily,
-- D.main_acc || '.' || D.sub_acc AS coa_item, D.coa_desc AS coa_desc_item, A.item_amount as amount
-- FROM pu_po_balance_invoice A
-- JOIN pu_receive_goods_item B ON A.ref_item_id = B.receive_goods_item_id AND A.ref_id = B.receive_goods_id
-- JOIN pu_po_item C ON B.ref_id = C.po_item_id AND B.ref_doc_type_id = 101 /* vDocTypePo */
-- JOIN m_coa D ON D.coa_id = f_get_product_coa_group_product(B.tenant_id, B.product_id)
-- JOIN fi_invoice_ap_balance E ON A.invoice_id = E.invoice_ap_id AND E.doc_type_id = 131 /* purchase invoice */
-- JOIN fi_payment_order_invoice F ON E.doc_type_id = F.ref_doc_type_id AND E.invoice_ap_id = F.ref_id
-- JOIN fi_payment_order G ON F.payment_order_id = G.payment_order_id
-- JOIN cb_trx_cashbank_balance H ON H.payment_id = G.payment_order_id
-- JOIN cb_in_out_cashbank I ON I.ref_id = H.trx_cashbank_balance_id AND I.doc_type_id = H.ref_doc_type_id
-- WHERE LEFT(I.doc_date, 6) = pYearMonth
-- AND C.flg_stock = 'Y'
-- AND A.flg_invoice = 'Y'
-- AND E.flg_payment = 'Y'
-- AND I.status_doc = pStatusDoc
-- AND EXISTS (
-- SELECT 1
-- FROM cb_in_out_cashbank_payment J
-- WHERE I.in_out_cashbank_id = J.in_out_cashbank_id
-- AND J.cashbank_id = pCashbankId
-- )
-- ORDER BY I.doc_date ASC;
--- Cashbank Out (Purchase Invoice -- Biaya/Aset)
INSERT INTO ttzi_montly_cashbank_out (week_index, week_desc, saldo_weekly_start, total_weekly, doc_date, coa_first_line, coa_desc_first_line,
amount_first_line, total_daily, saldo_daily, coa_item, coa_desc_item, amount)
SELECT fzi_get_week_of_month(H.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
0 AS total_weekly, TO_CHAR(TO_DATE(H.doc_date, 'YYYYMMDD'), 'DD Mon YYYY') AS doc_date, '' AS coa_first_line, '' AS coa_desc_first_line,
0 AS amount_first_line, 0 AS total_daily, 0 AS saldo_daily,
J.main_acc || '.' || J.sub_acc AS coa_item, J.coa_desc AS coa_desc_item, A.item_amount as amount
FROM pu_po_balance_invoice A
JOIN pu_receive_goods_item B ON A.ref_item_id = B.receive_goods_item_id AND A.ref_id = B.receive_goods_id
-- untuk mendapatkan dia stock persesediaan / bukan
JOIN pu_po_item C ON B.ref_id = C.po_item_id AND B.ref_doc_type_id = vDocTypePo /* vDocTypePo */
JOIN m_activity_gl D ON D.activity_gl_id = C.activity_gl_id
JOIN m_coa J ON D.coa_id = J.coa_id
JOIN fi_invoice_ap_balance E ON A.invoice_id = E.invoice_ap_id AND E.doc_type_id = vDocTypePurchaseInvoice /* purchase invoice */
JOIN fi_payment_order_invoice F ON E.doc_type_id = F.ref_doc_type_id AND E.invoice_ap_balance_id = F.ref_id
JOIN fi_payment_order G ON F.payment_order_id = G.payment_order_id
JOIN cb_trx_cashbank_balance K ON G.payment_order_id = K.payment_id AND K.doc_type_id = G.doc_type_id
JOIN cb_in_out_cashbank H ON H.ref_id = K.trx_cashbank_balance_id AND H.doc_type_id = K.ref_doc_type_id
JOIN pu_invoice L ON L.invoice_id = E.invoice_ap_id AND L.doc_type_id = E.doc_type_id
WHERE LEFT(H.doc_date, 6) = pYearMonth
AND C.flg_stock IN ('N', 'I')
AND A.flg_invoice = 'Y'
AND E.flg_payment = 'Y'
AND G.status_doc = vStatusReleased
AND EXISTS (
SELECT 1 FROM cb_in_out_cashbank_payment I
WHERE H.in_out_cashbank_id = I.in_out_cashbank_id
AND I.cashbank_id = pCashbankId
)
ORDER BY H.doc_date;
-- SELECT fzi_get_week_of_month(H.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
-- 0 AS total_weekly, H.doc_date , ' ' AS coa_first_line, ' ' AS coa_desc_first_line,
-- 0 AS amount_first_line, 0 AS total_daily, 0 AS saldo_daily,
-- J.main_acc || '.' || J.sub_acc AS coa_item, J.coa_desc AS coa_desc_item, A.item_amount as amount
-- FROM pu_po_balance_invoice A
-- JOIN pu_receive_goods_item B ON A.ref_item_id = B.receive_goods_item_id AND A.ref_id = B.receive_goods_id
-- -- untuk mendapatkan dia stock persesediaan / bukan
-- JOIN pu_po_item C ON B.ref_id = C.po_item_id AND B.ref_doc_type_id = 101 /* vDocTypePo */
-- JOIN m_activity_gl D ON D.activity_gl_id = C.activity_gl_id
-- JOIN m_coa J ON D.coa_id = J.coa_id
-- JOIN fi_invoice_ap_balance E ON A.invoice_id = E.invoice_ap_id AND E.doc_type_id = 131 /* purchase invoice */
-- JOIN fi_payment_order_invoice F ON E.doc_type_id = F.ref_doc_type_id AND E.invoice_ap_id = F.ref_id
-- JOIN fi_payment_order G ON F.payment_order_id = G.payment_order_id
-- JOIN cb_in_out_cashbank H ON H.ref_doc_type_id = G.doc_type_id
-- WHERE LEFT(H.doc_date, 6) = pYearMonth
-- AND C.flg_stock IN ('N', 'I')
-- AND A.flg_invoice = 'Y'
-- AND E.flg_payment = 'Y'
-- AND EXISTS (
-- SELECT 1 FROM cb_in_out_cashbank_payment I
-- WHERE H.in_out_cashbank_id = I.in_out_cashbank_id
-- AND I.cashbank_id = pCashbankId
-- )
-- ORDER BY H.doc_date ASC;
--- Cashbank Out (Purchase Invoice -- Additional Cost)
INSERT INTO ttzi_montly_cashbank_out (week_index, week_desc, saldo_weekly_start, total_weekly, doc_date, coa_first_line, coa_desc_first_line,
amount_first_line, total_daily, saldo_daily, coa_item, coa_desc_item, amount)
SELECT fzi_get_week_of_month(H.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
0 AS total_weekly, TO_CHAR(TO_DATE(H.doc_date, 'YYYYMMDD'), 'DD Mon YYYY') AS doc_date , '' AS coa_first_line, '' AS coa_desc_first_line,
0 AS amount_first_line, 0 AS total_daily, 0 AS saldo_daily,
D.main_acc || '.' || D.sub_acc AS coa_item, D.coa_desc AS coa_desc_item, A.add_amount as amount
FROM pu_invoice_cost A
JOIN fi_invoice_ap_balance B ON A.invoice_id = B.invoice_ap_id AND B.doc_type_id = vDocTypePurchaseInvoice
JOIN m_activity_gl C ON A.activity_gl_id = C.activity_gl_id
JOIN m_coa D ON C.coa_id = D.coa_id
JOIN fi_payment_order_invoice E ON B.doc_type_id = E.ref_doc_type_id AND B.invoice_ap_balance_id = E.ref_id
JOIN fi_payment_order F ON F.payment_order_id = E.payment_order_id
JOIN cb_trx_cashbank_balance G ON F.payment_order_id = G.payment_id AND G.doc_type_id = F.doc_type_id
JOIN cb_in_out_cashbank H ON H.ref_id = G.trx_cashbank_balance_id AND H.doc_type_id = G.ref_doc_type_id
WHERE LEFT(F.doc_date, 6) = pYearMonth
AND B.flg_payment = 'Y'
AND F.status_doc = vStatusReleased
AND EXISTS (
SELECT 1
FROM cb_in_out_cashbank_payment I
WHERE H.in_out_cashbank_id = I.in_out_cashbank_id
AND I.cashbank_id = pCashbankId
)
ORDER BY H.doc_date ASC;
OPEN pRefHeader FOR
SELECT
TO_CHAR(TO_DATE(pYearMonth, 'YYYYMM'), 'MONTH YYYY') AS year_month,
TO_CHAR(TO_DATE(balance_date, 'YYYYMMDD'), 'DD Mon YYYY') AS balance_date,
saldo_awal,
kas
FROM (
VALUES
('20240801', '20240731', 13100, 'CASHBANK')
) AS header (year_month, balance_date, saldo_awal, kas);
RETURN NEXT pRefHeader;
OPEN pRefDetail FOR
WITH daily_cashbank_out AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY doc_date ORDER BY doc_date) AS day_no
FROM ttzi_montly_cashbank_out
)
SELECT * FROM daily_cashbank_out
ORDER BY doc_date ASC;
RETURN NEXT pRefDetail;
END;
$function$;
/Editor is loading...
Leave a Comment