Untitled

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';

BEGIN

    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, A.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 A.cashbank_id = C.cashbank_id
    JOIN m_coa D ON C.coa_id = D.coa_id
    WHERE A.cashbank_id = pCashbankId
        AND LEFT(doc_date, 6) = pYearMonth
        AND A.status_doc = pStatusDoc
    ORDER BY A.doc_date ASC;

    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, A.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(doc_date, 6) = pYearMonth
        AND doc_type_id = 631
        AND A.status_doc = pStatusDoc
    ORDER BY A.doc_date ASC;

    
    OPEN pRefHeader FOR
    SELECT
        TO_CHAR(TO_DATE(pYearMonth, 'YYYYMM'), 'MONTH YYYY') AS year_month,
        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
    SELECT *
    FROM ttzi_montly_cashbank_out;

    RETURN NEXT pRefDetail;

END;
$function$;
/