fzi_r_report_montly_cashbank_out
This PL/pgSQL new function generates a monthly cashbank report .
unknown
mysql
10 days ago
26 kB
3
Indexable
Never
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;
vDocTypeCreditNote bigint := 201;
vDocTypeDirectCostPayment bigint := 613;
vDocTypeRequestCashAdvance bigint := 602;
vDocTypePaymentOrder bigint := 601;
vDocTypeFollowUpCashAdvance bigint := 641;
vDocTypeCashAdvanceSettlement bigint := 603;
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,
E.doc_desc || ' / ' || A.doc_no || ' ' AS doc_desc_doc_no_item,
'' AS amount_debit, B.receive_amount AS amount_kredit, A.remark
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
JOIN m_document E ON A.doc_type_id = E.doc_type_id
WHERE A.cashbank_id = pCashbankId
AND LEFT(A.doc_date, 6) = pYearMonth
AND A.status_doc = vStatusReleased
ORDER BY week_index, doc_date, coa_item 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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
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,
E.doc_desc || ' / ' || A.doc_no || ' ' AS doc_desc_doc_no_item,
'' AS amount_debit, B.cost_amount AS amount_kredit, A.remark
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
JOIN m_document E ON A.doc_type_id = E.doc_type_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 week_index, doc_date, coa_item 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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
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,
CASE
WHEN f_get_product_name(C.product_id) IS NULL OR f_get_product_name(C.product_id) = '' THEN K.doc_desc || ' / ' || I.doc_no
ELSE K.doc_desc || ' / ' || I.doc_no || ' / ' || 'Produk ' || f_get_product_name(C.product_id)
END AS doc_desc_doc_no_item,
'' AS amount_debit, A.item_amount AS amount_kredit, I.remark
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
JOIN m_document K ON K.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 = vStatusReleased
AND EXISTS (
SELECT 1
FROM cb_in_out_cashbank_payment L
WHERE I.in_out_cashbank_id = L.in_out_cashbank_id
AND L.cashbank_id = pCashbankId
)
ORDER BY week_index, doc_date, coa_item 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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
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,
K.main_acc || '.' || K.sub_acc AS coa_item, K.coa_desc,
CASE
WHEN f_get_product_name(C.product_id) IS NULL OR f_get_product_name(C.product_id) = '' THEN L.doc_desc || ' / ' || I.doc_no
ELSE L.doc_desc || ' / ' || I.doc_no || ' / ' || 'Produk ' || f_get_product_name(C.product_id)
END AS doc_desc_doc_no_item,
'' AS amount_debit, A.item_amount AS amount_kredit, I.remark
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 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 G.payment_order_id = H.payment_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
JOIN m_coa K ON D.coa_id = K.coa_id
JOIN m_document L ON L.doc_type_id = E.doc_type_id
WHERE LEFT(I.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 M
WHERE I.in_out_cashbank_id = M.in_out_cashbank_id
AND M.cashbank_id = pCashbankId
)
ORDER BY week_index, doc_date, coa_item 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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
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,
I.doc_desc || ' / ' || H.doc_no || ' / ' || ' - Biaya Tambahan' AS doc_desc_doc_no_item,
'' AS amount_debit, A.add_amount AS amount_kredit, H.remark
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
JOIN m_document I ON I.doc_type_id = B.doc_type_id
WHERE LEFT(H.doc_date, 6) = pYearMonth
AND B.flg_payment = 'Y'
AND F.status_doc = vStatusReleased
AND EXISTS (
SELECT 1
FROM cb_in_out_cashbank_payment J
WHERE H.in_out_cashbank_id = J.in_out_cashbank_id
AND J.cashbank_id = pCashbankId
)
ORDER BY week_index, doc_date, coa_item ASC;
--- CashBank Out (Purchase Invoice -- Tax)
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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
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,
F.main_acc || '.' || F.sub_acc AS coa_item, F.coa_desc,
I.doc_desc || ' / ' || H.doc_no || ' / ' || ' - PPN Masukan' AS doc_desc_doc_no_item,
'' AS amount_debit, A.tax_amount AS amount_kredit, H.remark
FROM pu_po_balance_invoice_tax A
JOIN m_tax B ON B.tax_id = A.tax_id AND A.flg_invoice = 'Y'
JOIN fi_invoice_ap_balance C ON C.invoice_ap_id = A.invoice_id AND C.doc_type_id = vDocTypePurchaseInvoice
JOIN fi_payment_order_invoice D ON C.doc_type_id = D.ref_doc_type_id AND C.invoice_ap_balance_id = D.ref_id
JOIN fi_payment_order E ON E.payment_order_id = D.payment_order_id
JOIN m_coa F ON F.coa_id = B.receive_coa_id
JOIN cb_trx_cashbank_balance G ON E.payment_order_id = G.payment_id AND G.doc_type_id = E.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
JOIN m_document I ON I.doc_type_id = C.doc_type_id
WHERE LEFT(H.doc_date, 6) = pYearMonth
AND E.status_doc = vStatusReleased
AND C.flg_payment = 'Y'
AND EXISTS (
SELECT 1
FROM cb_in_out_cashbank_payment J
WHERE H.in_out_cashbank_id = J.in_out_cashbank_id
AND J.cashbank_id = pCashbankId
)
ORDER BY week_index, doc_date, coa_item ASC;
--- CashBank Out (Bayar Hutang)
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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
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,
E.main_acc || '.' || E.sub_acc AS coa_item, E.coa_desc,
CASE
WHEN f_get_partner_name(A.partner_id) IS NULL OR f_get_partner_name(A.partner_id) = '' THEN J.doc_desc || ' / ' || I.doc_no
ELSE J.doc_desc || ' / ' || I.doc_no || ' / ' || 'Mitra ' || f_get_partner_name(A.partner_id)
END AS doc_desc_doc_no_item,
'' AS amount_debit, C.add_amount AS amount_kredit, I.remark
FROM fi_invoice_ap_balance A
JOIN fi_invoice_ap B ON B.invoice_ap_id = A.invoice_ap_id AND B.doc_type_id = A.doc_type_id
JOIN fi_invoice_ap_cost C ON C.invoice_ap_id = A.invoice_ap_id
JOIN m_activity_gl D ON D.activity_gl_id = C.activity_gl_id
JOIN m_coa E ON E.coa_id = D.coa_id
JOIN fi_payment_order_invoice F ON A.doc_type_id = F.ref_doc_type_id AND A.invoice_ap_balance_id = F.ref_id
JOIN fi_payment_order G ON G.payment_order_id = F.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 m_document J ON J.doc_type_id = A.doc_type_id
WHERE LEFT(I.doc_date, 6) = pYearMonth
AND A.doc_type_id = vDocTypeCreditNote
AND A.flg_payment = 'Y'
AND G.status_doc = vStatusReleased
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 week_index, doc_date, coa_item ASC;
--- CashBank Out (Direct Payment)
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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
SELECT fzi_get_week_of_month(B.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
0 AS total_weekly, TO_CHAR(TO_DATE(B.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,
CASE
WHEN f_get_partner_name(B.partner_id) IS NULL OR f_get_partner_name(B.partner_id) = '' THEN E.doc_desc || ' / ' || B.doc_no
ELSE E.doc_desc || ' / ' || B.doc_no || ' / ' || 'Mitra ' || f_get_partner_name(B.partner_id)
END AS doc_desc_doc_no_item,
'' AS amount_debit, A.payment_amount AS amount_kredit, B.remark
FROM cb_in_out_cashbank_cost A
JOIN cb_in_out_cashbank B ON B.in_out_cashbank_id = A.in_out_cashbank_id and B.doc_type_id = vDocTypeDirectCostPayment
JOIN m_activity_gl C ON C.activity_gl_id = A.activity_gl_id
JOIN m_coa D ON D.coa_id = C.coa_id
JOIN m_document E ON E.doc_type_id = B.doc_type_id
WHERE LEFT(B.doc_date, 6) = pYearMonth
AND B.status_doc = vStatusReleased
AND EXISTS (
SELECT 1 FROM cb_in_out_cashbank_payment F
WHERE B.in_out_cashbank_id = F.in_out_cashbank_id
AND F.cashbank_id = pCashbankId
)
ORDER BY week_index, doc_date, coa_item ASC;
--- CashBank Out (Payment Order )
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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
SELECT fzi_get_week_of_month(D.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
0 AS total_weekly, TO_CHAR(TO_DATE(D.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,
F.main_acc || '.' || F.sub_acc AS coa_item, F.coa_desc,
CASE
WHEN f_get_partner_name(B.partner_id) IS NULL OR f_get_partner_name(B.partner_id) = '' THEN G.doc_desc || ' / ' || B.doc_no
ELSE G.doc_desc || ' / ' || B.doc_no || ' / ' || 'Mitra ' || f_get_partner_name(B.partner_id)
END AS doc_desc_doc_no_item,
'' AS amount_debit, A.add_amount AS amount_kredit, A.remark
FROM cb_payment_order_cost A
JOIN cb_payment_order B ON B.payment_order_id = A.payment_order_id
JOIN cb_trx_cashbank_balance C on C.payment_id = B.payment_order_id and C.doc_type_id = vDocTypePaymentOrder
JOIN cb_in_out_cashbank D on D.ref_id = C.trx_cashbank_balance_id and D.doc_type_id = C.ref_doc_type_id
JOIN m_activity_gl E ON E.activity_gl_id = A.activity_gl_id
JOIN m_coa F ON F.coa_id = E.coa_id
JOIN m_document G ON G.doc_type_id = B.doc_type_id
WHERE LEFT(D.doc_date, 6) = pYearMonth
AND B.status_doc = vStatusReleased
AND C.flg_payment = 'Y'
AND EXISTS (
SELECT 1 FROM cb_in_out_cashbank_payment H
WHERE D.in_out_cashbank_id = H.in_out_cashbank_id
AND H.cashbank_id = pCashbankId
)
ORDER BY week_index, doc_date, coa_item ASC;
--- CashBank Out (Request Cash Advance)
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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
SELECT fzi_get_week_of_month(D.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
0 AS total_weekly, TO_CHAR(TO_DATE(D.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,
F.main_acc || '.' || F.sub_acc AS coa_item, F.coa_desc,
CASE
WHEN f_get_partner_name(A.partner_id) IS NULL OR f_get_partner_name(A.partner_id) = '' THEN G.doc_desc || ' / ' || C.payment_doc_no
ELSE G.doc_desc || ' / ' || C.payment_doc_no || ' / ' || 'Mitra ' || f_get_partner_name(A.partner_id)
END AS doc_desc_doc_no_item,
'' AS amount_debit, A.amount AS amount_kredit, D.remark
FROM cb_advance_balance A
JOIN cb_payment_order I ON I.payment_order_id = A.advance_id
JOIN cb_in_out_cashbank_cost B on A.cash_bank_payment_id = B.in_out_cashbank_id
JOIN cb_trx_cashbank_balance C on B.in_out_cashbank_id = C.ref_id and C.payment_id = I.payment_order_id and C.doc_type_id = vDocTypeRequestCashAdvance
JOIN cb_in_out_cashbank D on D.ref_id = C.trx_cashbank_balance_id and D.doc_type_id = C.ref_doc_type_id
JOIN m_activity_gl E ON E.activity_gl_id = B.activity_gl_id
JOIN m_coa F ON F.coa_id = E.coa_id
JOIN m_document G ON G.doc_type_id = C.doc_type_id
WHERE LEFT(D.doc_date, 6) = pYearMonth
AND D.status_doc = vStatusReleased
AND C.flg_payment = 'Y'
AND EXISTS (
SELECT 1 FROM cb_in_out_cashbank_payment H
WHERE D.in_out_cashbank_id = H.in_out_cashbank_id
AND H.cashbank_id = pCashbankId
)
ORDER BY week_index, doc_date, coa_item ASC;
--- CashBank Out (Cash Advance Settlement)
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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
SELECT fzi_get_week_of_month(D.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
0 AS total_weekly, TO_CHAR(TO_DATE(D.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,
F.main_acc || '.' || F.sub_acc AS coa_item, F.coa_desc,
CASE
WHEN f_get_partner_name(D.partner_id) IS NULL OR f_get_partner_name(D.partner_id) = '' THEN G.doc_desc || ' / ' || C.payment_doc_no
ELSE G.doc_desc || ' / ' || C.payment_doc_no || ' / ' || 'Mitra ' || f_get_partner_name(D.partner_id)
END AS doc_desc_doc_no_item,
'' as amount_debit, A.amount AS amount_kredit, C.payment_remark
FROM cb_advance_balance A
JOIN cb_in_out_cashbank_cost B ON B.in_out_cashbank_id = A.cash_bank_payment_id
JOIN cb_trx_cashbank_balance C on C.payment_id = A.ref_id and C.doc_type_id = vDocTypeCashAdvanceSettlement
JOIN cb_in_out_cashbank D on D.ref_id = C.trx_cashbank_balance_id and C.doc_type_id = D.ref_doc_type_id
JOIN m_activity_gl E ON E.activity_gl_id = 110
JOIN m_coa F ON F.coa_id = E.coa_id
JOIN m_document G ON G.doc_type_id = C.doc_type_id
WHERE LEFT(D.doc_date, 6) = pYearMonth
AND D.status_doc = vStatusReleased
AND C.flg_payment = 'Y'
AND EXISTS (
SELECT 1 FROM cb_in_out_cashbank_payment H
WHERE D.in_out_cashbank_id = H.in_out_cashbank_id
AND H.cashbank_id = pCashbankId
)
ORDER BY week_index, doc_date, coa_item ASC;
--- CashBank Out (Cash Advance Settlement)
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, doc_desc_doc_no_item, amount_debit, amount_kredit, remark)
SELECT fzi_get_week_of_month(D.doc_date) AS week_index, '' AS week_desc, 0 AS saldo_weekly_start,
0 AS total_weekly, TO_CHAR(TO_DATE(D.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,
F.main_acc || '.' || F.sub_acc AS coa_item, F.coa_desc,
CASE
WHEN f_get_partner_name(D.partner_id) IS NULL OR f_get_partner_name(D.partner_id) = '' THEN G.doc_desc || ' / ' || D.doc_no
ELSE G.doc_desc || ' / ' || D.doc_no || ' / ' || 'Mitra ' || f_get_partner_name(D.partner_id)
END AS doc_desc_doc_no_item,
'' as amount_debit, C.amount AS amount_kredit, D.remark
FROM cb_advance_balance A
JOIN cb_in_out_cashbank_cost B ON B.in_out_cashbank_id = A.cash_bank_payment_id
JOIN cb_trx_cashbank_balance C on C.payment_id = A.ref_id
JOIN cb_in_out_cashbank D on D.ref_id = C.trx_cashbank_balance_id and C.doc_type_id = D.ref_doc_type_id and D.doc_type_id = vDocTypeFollowUpCashAdvance
JOIN m_activity_gl E ON E.activity_gl_id = 110
JOIN m_coa F ON F.coa_id = E.coa_id
JOIN m_document G ON G.doc_type_id = D.doc_type_id
WHERE LEFT(D.doc_date, 6) = pYearMonth
AND D.status_doc = vStatusReleased
AND C.flg_payment = 'Y'
AND EXISTS (
SELECT 1 FROM cb_in_out_cashbank_payment H
WHERE D.in_out_cashbank_id = H.in_out_cashbank_id
AND H.cashbank_id = pCashbankId
)
ORDER BY week_index, doc_date, coa_item ASC;
OPEN pRefHeader FOR
SELECT
TO_CHAR(TO_DATE(pYearMonth, 'YYYYMM'), 'MONTHYYYY') AS year_month,
f_get_cashbank_balance(pCashbankId, TO_CHAR(DATE_TRUNC('month', TO_DATE(pYearMonth, 'YYYYMM')) - INTERVAL '1 day', 'YYYYMMDD')) as saldo_awal,
f_get_cashbank_name(pCashbankId) as kas,
kas_awal,
TO_CHAR(CURRENT_TIMESTAMP, 'DD Month YYYY') AS date_now,
'Siti Shofiyah, SE' AS manager_name,
'Dr. Ahmad Lathifi, M.Kes' AS direktur_name,
TO_CHAR(TO_DATE(TO_CHAR(DATE_TRUNC('month', TO_DATE(pYearMonth, 'YYYYMM')) - INTERVAL '1 day', 'YYYYMMDD'), 'YYYYMMDD'), 'DD Mon YYYY') AS last_date
FROM (
VALUES
(249986900)
) AS header (kas_awal);
RETURN NEXT pRefHeader;
OPEN pRefDetail FOR
WITH daily_cashbank_out AS (
SELECT *,
DENSE_RANK() OVER (ORDER BY doc_date) AS day_no
FROM ttzi_montly_cashbank_out
)
SELECT * FROM daily_cashbank_out
ORDER BY week_index, doc_date, coa_item ASC;
RETURN NEXT pRefDetail;
END;
$function$;
/Leave a Comment