yang betul

BEGIN;
SELECT mlm_r_report_summary_mutasi_saldo('SESSION_DATA', 11, 14, '201707',333, 12);
FETCH ALL IN "refHeader";
ROLLBACK;

CREATE OR REPLACE FUNCTION mlm_r_report_summary_mutasi_saldo(character varying, bigint, bigint, character varying, bigint, bigint)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pOuId               ALIAS FOR $3;
    pPeriod             ALIAS FOR $4;
    pUserId             ALIAS FOR $5;
    pRoleId             ALIAS FOR $6;

    pRefHeader          REFCURSOR := 'refHeader';

    vDocTypeDpBalance   bigint := 252;
    vDocTypeCnArBonus   bigint := 255;
    vTglAwalBulan       character varying(8);
    vEmptyId        bigint := -99;
    vPartnerRank        character varying := 'PARTNERRANK';
    vYes            character varying := 'Y';
    vGroupCustomer      character varying := 'C';
    vCustomerDs     character varying := 'CUSTOMER DS';

BEGIN

    DELETE FROM tt_mlm_r_report_mutasi_saldo WHERE session_id = pSessionId;

    /*
    * insert ke table temporary untuk credit amount dan debt amount panggil function f_get_saldo_awal_partner_credit_limit_usage untuk
    * menentukan nilai nya.
    */
    INSERT INTO tt_mlm_r_report_mutasi_saldo(session_id, partner_id, order_type, credit_amount, debt_amount, remark)
    SELECT pSessionId, a.partner_id, 'Saldo Akhir',
        CASE WHEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) > 0 THEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) ELSE 0 END AS credit_amount,
        CASE WHEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) < 0 THEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) * -1 ELSE 0 END AS debt_amount,
        'Saldo Akhir'
    FROM m_partner a
    INNER JOIN  m_partner_cp n ON  a.partner_id = n.partner_id
    INNER JOIN m_ctgr_partner b ON b.tenant_id = a.tenant_id AND b.ctgr_partner_id = a.ctgr_partner_id
    INNER JOIN  mlm_ds  h ON a.partner_id = h.partner_id
    INNER JOIN  mlm_member  i ON h.member_id = i.member_id
    WHERE a.tenant_id = pTenantId;

    /*
    * tambahkan credit amount dan debt amount dari dokumen DNAP.
    */
    WITH TEMP AS (
        SELECT B.mlm_r_report_mutasi_saldo_id, A.partner_id, B.order_type, SUM(CASE WHEN A.record_type = 1 THEN A.amount ELSE 0 END) AS credit_amount, SUM(CASE WHEN A.record_type = -1 THEN A.amount ELSE 0 END) AS debt_amount
        FROM sl_log_partner_credit_limit_usage A, tt_mlm_r_report_mutasi_saldo B
        WHERE A.tenant_id = pTenantId
            AND B.session_id = pSessionId
            AND A.partner_id = B.partner_id
            AND SUBSTRING(A.doc_date, 1, 6) = pPeriod
            AND A.amount <> 0
            AND A.record_type <> 0
        GROUP BY A.partner_id, B.order_type, B.mlm_r_report_mutasi_saldo_id)
    UPDATE tt_mlm_r_report_mutasi_saldo Z
        SET credit_amount = Z.credit_amount+A.credit_amount, debt_amount = Z.debt_amount+A.debt_amount
    FROM TEMP A
    WHERE Z.session_id = pSessionId
        AND A.mlm_r_report_mutasi_saldo_id = Z.mlm_r_report_mutasi_saldo_id
        AND A.partner_id = Z.partner_id
        AND A.order_type = Z.order_type;

    OPEN pRefHeader FOR
        SELECT B.partner_code, B.partner_name,
            A.credit_amount, A.debt_amount, (A.debt_amount-A.credit_amount) AS total_saldo_akhir,
            CASE WHEN C.active = vYes THEN 'Aktif' ELSE 'Tidak Aktif' END AS status
        FROM tt_mlm_r_report_mutasi_saldo A
        INNER JOIN m_partner B ON A.partner_id = B.partner_id
        INNER JOIN mlm_ds C ON B.partner_id = C.partner_id
        WHERE session_id = pSessionId
        ORDER BY B.partner_code ASC;
    RETURN NEXT pRefHeader;

    DELETE FROM tt_mlm_r_report_mutasi_saldo WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;