fzi_r_report_montly_cashbank_out

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$;
/