func_final_detail_mutation_cashbank

CREATE OR REPLACE FUNCTION public.r_report_detail_mutation_cashbank(
    character varying,
    bigint,
    character varying,
    bigint,
    bigint,
    character varying,
    character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader          REFCURSOR := 'refHeader';
    pRefDetail          REFCURSOR := 'refDetail';
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pDatetime           ALIAS FOR $3;
    pOuId               ALIAS FOR $4;
    pCashbankId         ALIAS FOR $5;
    pDateStart          ALIAS FOR $6;
    pDateEnd            ALIAS FOR $7;

    vEmptyId                bigint;
    vEmpty                  character varying(1);
    vCashBankInType         character varying(1);
    vCashBankOutType        character varying(1);
    vStatusReleased         character varying;
    vStatusVoid             character varying(1);
    vRecordTypeSaldoAwal    character varying(1);
    vRecordTypeKredit       character varying(1);
    vRecordTypeDebet        character varying(1);
    vTglAwalCashbankBalance character varying(8);
    vDocTypePosShop             bigint := 401;
    vDocTypeCashBankOut         bigint := 611;
    vDocTypeCashBankInOther     bigint := 623;
    vDocTypeCashBankInAr        bigint := 621;
    vDocTypeEdcSettlement       bigint := 622;
    vDocTypeFollowUpCashAdvanceSettlement       bigint := 641;
    vDocTypeCbInDt              bigint := 699;
    vDocTypeCgRealization       bigint := 625;
    vDocTypeCbTransfer          bigint := 631;
    vDocTypePaymentOrderNonAP           bigint := 601;
    vDocTypePaymentOrderAP          bigint := 231;
    vDocTypeRequestCashAdvance          bigint := 602;
    vDocTypeCashAdvanceSettlement           bigint := 603;
    vDocTypeConversionCBInOP            bigint := 626;
    vDocTypeConversionCBInPO            bigint := 627;
    vCount                      bigint;
    vYes                    character varying(1);
    vTotalCgAmount                  numeric;
    vTotalCgCost                    numeric;
    vModePaymentOther               character varying := 'OTHERS';

    vAnd                            character varying := 'AND ';
    vFilterCashbankV1               character varying := '';
    vFilterCashbankV2               character varying := '';
    vFilterCashbankV3               character varying := '';
    vStatusAccept                   character varying;
    vStatusSubmit                   character varying:='S';
    vFlagCash                   character varying:='C';

BEGIN

    vEmptyId := -99;
    vEmpty   := ' ';
    vCashBankInType := 'I';
    vCashBankOutType := 'O';
    vStatusReleased := 'APPROVED';
    vStatusVoid := 'V';
    vRecordTypeSaldoAwal := 'A';
    vRecordTypeKredit := 'K';
    vRecordTypeDebet := 'D';
    vTglAwalCashbankBalance := ' ';
    vYes := 'Y';
    vStatusAccept := 'ACCEPT';

    DELETE FROM tt_detail_mutation_cashbank WHERE session_id = pSessionId;

    IF pCashbankId <> vEmptyId THEN
        vFilterCashbankV1 := 'AND ( A.cashbank_id = '|| pCashbankId ||' OR B.cashbank_to_id = '||pCashbankId||' )';
        vFilterCashbankV2 := 'AND B.cashbank_id = '|| pCashbankId;
        vFilterCashbankV3 := 'AND E.cashbank_id = '|| pCashbankId;
    END IF;

    -- CB TRANSFER YG CB OU dari berasal dari assign ou sesuai filter (Untuk OU yang Transfer)
    EXECUTE '
        -- CB Transfer -> nilai dokumen keluar
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, activity_gl_code, activity_gl_name,
            coa_acc, coa_desc)
        SELECT  $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
            X.ou_id, f_get_ou_code(X.ou_id), -1 * (B.transfer_amount),
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, B.curr_code, B.remark,
            A.create_user_id, D.username, A.create_datetime,
            A.update_user_id, E.username, A.update_datetime,
            A.remark, $7, $7, $7,
            CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
        FROM cb_transfer_cashbank A
        INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
        INNER JOIN m_cashbank_assignment_ou C ON A.cashbank_id = C.cashbank_id
        INNER JOIN m_cashbank_assignment_ou X ON B.cashbank_to_id = X.cashbank_id
        INNER JOIN gl_journal_trx O ON A.transfer_cashbank_id = O.doc_id
                    AND A.doc_type_id = O.doc_type_id
                    AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
                    AND X.cashbank_id = P.cashbank_id
                    --AND O.doc_type_id = P.ref_doc_type_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        INNER JOIN t_user D ON A.create_user_id = D.user_id
        INNER JOIN t_user E ON A.update_user_id = E.user_id
        INNER JOIN m_cashbank G ON A.cashbank_id = G.cashbank_id
        WHERE A.tenant_id = $2
        AND C.ou_id = $6 '||
        vFilterCashbankV1   ||'
        AND A.workflow_status = $3
        AND P.sign_journal = ''D''
        AND A.doc_date BETWEEN $4 AND $5

        UNION ALL

        -- CB Transfer -> nilai cost
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
            B.ou_to_id, f_get_ou_code(B.ou_to_id), -1 * (COALESCE(C.payment_amount, 0)),
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, C.curr_code, C.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime,
            A.remark, $7, K.activity_gl_code, K.activity_gl_name,
            CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
        FROM cb_transfer_cashbank A
        INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
        INNER JOIN cb_transfer_cashbank_cost C ON A.transfer_cashbank_id = C.transfer_cashbank_id
        INNER JOIN m_cashbank_assignment_ou D ON A.cashbank_id = D.cashbank_id
        INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON K.coa_id = L.coa_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON A.cashbank_id = G.cashbank_id
        WHERE A.tenant_id = $2
        AND D.ou_id = $6 '||
        vFilterCashbankV1   ||'
        AND A.workflow_status = $3
        AND A.doc_date BETWEEN $4 AND $5
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, pOuId, vEmpty;

    -- CB TRANSFER YG CB OU dari berasal dari assign ou sesuai filter (Untuk OU yang Penerima)
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
            Z.ou_id, f_get_ou_code(Z.ou_id), B.receive_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, B.curr_code, B.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime,
            A.remark, $8, CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
        FROM cb_transfer_cashbank A
        INNER JOIN cb_transfer_cashbank_receive B ON A.transfer_cashbank_id = B.transfer_cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_to_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_to_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.transfer_cashbank_id = O.doc_id
                    AND A.doc_type_id = O.doc_type_id
                    AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
                    AND A.cashbank_id = P.cashbank_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $7 '||
        vFilterCashbankV1   ||'
        AND A.workflow_status = $3
        AND P.sign_journal = ''C''
        AND A.doc_date BETWEEN $4 AND $5
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, pOuId, vEmpty;

    -- CB Out dari Payment Order (AP)
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
            A.ou_id, f_get_ou_code(A.ou_id), -1 * B.cashbank_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, B.curr_code, A.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime,
            A.remark, ''CBO - Payment Order (AP)'', CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
                    AND A.doc_type_id = O.doc_type_id
                    AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $7 ' ||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
        AND P.line_no = 1
        AND A.doc_type_id = $6
        AND A.ref_doc_type_id = $8; '
        USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vDocTypeCashBankOut, pOuId,
        vDocTypePaymentOrderAP, vEmpty;

    -- CB Out dari Payment Order (Non AP)
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, activity_gl_code, activity_gl_name,
            coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
            A.ou_id, f_get_ou_code(A.ou_id), -1 * H.add_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, H.curr_code, H.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime,
            A.remark, ''CBO - Payment Order CB'', K.activity_gl_code, K.activity_gl_name,
            CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.trx_cashbank_balance_id
        INNER JOIN cb_payment_order D ON C.payment_id = D.payment_order_id AND C.doc_type_id = D.doc_type_id
        INNER JOIN cb_payment_order_cost H ON D.payment_order_id = H.payment_order_id
        INNER JOIN m_activity_gl K ON H.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON L.coa_id = K.coa_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $7 ' ||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND A.doc_type_id = $6
        AND A.ref_doc_type_id = $8; '
    USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vDocTypeCashBankOut, pOuId,
    vDocTypePaymentOrderNonAP, vEmpty;

    -- CB Out dari Req Cash Advance
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
            D.ou_id, f_get_ou_code(D.ou_id), -1 * B.cashbank_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, B.curr_code, A.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime,
            A.remark, ''CBO - Cash Advance'', CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
            AND A.doc_type_id = O.doc_type_id
            AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $7 ' ||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
        AND A.doc_type_id = $6
        AND A.ref_doc_type_id = $8; '
    USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vDocTypeCashBankOut, pOuId,
    vDocTypeRequestCashAdvance, vEmpty;

    -- CB IN Partner Receive -> nilai dokumen masuk
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
            Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, B.curr_code, A.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime,
            A.remark, $7, CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
            AND A.doc_type_id = O.doc_type_id
            AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
            AND O.doc_id = P.ref_id
            AND O.doc_type_id = P.ref_doc_type_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $9 '||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
        AND A.doc_type_id = $8;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeCashBankInAr, pOuId;

    -- CB IN Partner Receive -> jika ada nilai cost
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, activity_gl_code, activity_gl_name,
            coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
            A.ou_id, f_get_ou_code(A.ou_id), -1 * C.cost_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, B.curr_code, C.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime,
            A.remark, $6, K.activity_gl_code, K.activity_gl_name,
            CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON L.coa_id = K.coa_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $8 '||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND A.doc_type_id = $7
        AND C.cost_amount <> 0;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashBankInAr, pOuId;

    -- CB IN Other -> nilai dokumen (per activity gl terima)
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, activity_gl_code, activity_gl_name,
            coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
            Z.ou_id, f_get_ou_code(Z.ou_id), C.cost_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, B.curr_code, A.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime,
            A.remark, $6, K.activity_gl_code, K.activity_gl_name,
            CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON L.coa_id = K.coa_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $8 '||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND A.doc_type_id = $7;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashBankInOther, pOuId;

    -- Cash Advance Settlement (pengembalian BS)
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, activity_gl_code, activity_gl_name,
            coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
            Z.ou_id, f_get_ou_code(Z.ou_id), E.cashbank_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, E.curr_code, A.remark,
            A.create_user_id, H.username, A.create_datetime,
            A.update_user_id, I.username, A.update_datetime,
            A.remark, $6, $6, $6,
            CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
        FROM cb_advance_settle A
        INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
        INNER JOIN cb_in_out_cashbank D ON D.ref_doc_type_id = C.doc_type_id AND D.ref_id = C.trx_cashbank_balance_id
        INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON D.in_out_cashbank_id = O.doc_id
                    AND D.doc_type_id = O.doc_type_id
                    AND D.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        INNER JOIN t_user H ON A.create_user_id = H.user_id
        INNER JOIN t_user I ON A.update_user_id = I.user_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $8 '||
        vFilterCashbankV3||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
        AND A.doc_type_id = $7; '
    USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashAdvanceSettlement, pOuId;

    -- Cash Advance Settlement (cost activity gl)
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id,
            ou_rc_code,
            amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, activity_gl_code, activity_gl_name,
            coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
            CASE WHEN F.ou_branch_id = -99 OR F.ou_branch_id = null THEN
                CASE WHEN F.ou_sub_bu_id = -99 OR F.ou_sub_bu_id = null THEN
                    (A.ou_id)
                ELSE (COALESCE(F.ou_sub_bu_id, A.ou_id)) END
            ELSE (COALESCE(F.ou_branch_id, A.ou_id)) END AS ou_rc_id,
            CASE WHEN F.ou_branch_id = -99 OR F.ou_branch_id = null THEN
                CASE WHEN F.ou_sub_bu_id = -99 OR F.ou_sub_bu_id = null THEN
                    f_get_ou_code(A.ou_id)
                ELSE f_get_ou_code(COALESCE(F.ou_sub_bu_id, A.ou_id)) END
            ELSE f_get_ou_code(COALESCE(F.ou_branch_id, A.ou_id)) END AS ou_rc_code,
            -1 * B.cost_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, E.curr_code, B.remark,
            A.create_user_id, H.username, A.create_datetime,
            A.update_user_id, I.username, A.update_datetime,
            A.remark, $6, K.activity_gl_code, K.activity_gl_name,
            CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
        FROM cb_advance_settle A
        INNER JOIN cb_advance_settle_cost B ON A.advance_settle_id = B.advance_settle_id AND A.tenant_id = B.tenant_id
        INNER JOIN cb_trx_cashbank_balance C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.payment_id
        INNER JOIN cb_in_out_cashbank D ON D.ref_doc_type_id = C.doc_type_id AND D.ref_id = C.trx_cashbank_balance_id
        INNER JOIN cb_in_out_cashbank_payment E ON E.in_out_cashbank_id = D.in_out_cashbank_id
        LEFT OUTER JOIN cb_advance_settle_cost_custom_for_sasa F ON B.advance_settle_cost_id = F.advance_settle_cost_id
        INNER JOIN m_activity_gl K ON B.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON L.coa_id = K.coa_id
        INNER JOIN m_cashbank_assignment_ou Z ON E.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank G ON E.cashbank_id = G.cashbank_id
        INNER JOIN t_user H ON A.create_user_id = H.user_id
        INNER JOIN t_user I ON A.update_user_id = I.user_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $8 '||
        vFilterCashbankV3||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND A.doc_type_id = $7; '
    USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashAdvanceSettlement, pOuId;

    -- EDC  Settlement -> nilai dokumen masuk
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, Y.ou_id, f_get_ou_code(Y.ou_id),
            Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
            H.cashbank_code, H.cashbank_name, H.account_no,
            H.flg_cash_bank, H.bank_code, B.curr_code, A.remark,
            A.create_user_id, I.username, A.create_datetime,
            A.update_user_id, J.username, A.update_datetime,
            A.remark, $7, CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank_edc E ON A.in_out_cashbank_id = E.in_out_cashbank_id AND A.tenant_id = E.tenant_id
        INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
        INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
        INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
                    AND A.doc_type_id = O.doc_type_id
                    AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        INNER JOIN t_user I ON A.create_user_id = I.user_id
        INNER JOIN t_user J ON A.update_user_id = J.user_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $9 '||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
        AND A.doc_type_id = $8;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeEdcSettlement, pOuId;

    -- EDC  Settlement -> nilai cost
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, activity_gl_code, activity_gl_name,
            coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
            Y.ou_id, f_get_ou_code(Y.ou_id), -1 * C.cost_amount,
            H.cashbank_code, H.cashbank_name, H.account_no,
            H.flg_cash_bank, H.bank_code, B.curr_code, A.remark,
            A.create_user_id, I.username, A.create_datetime,
            A.update_user_id, J.username, A.update_datetime,
            A.remark, $7, K.activity_gl_code, K.activity_gl_name,
            CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank_edc E ON A.in_out_cashbank_id = E.in_out_cashbank_id AND A.tenant_id = E.tenant_id
        INNER JOIN cb_balance_settlement F ON E.balance_settlement_id = F.balance_settlement_id
        INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON K.coa_id = L.coa_id
        INNER JOIN m_device_merchant G ON F.device_merchant_id = G.device_merchant_id
        INNER JOIN m_cashbank_assignment_ou Y ON G.cashbank_id = Y.cashbank_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN m_cashbank H ON B.cashbank_id = H.cashbank_id
        INNER JOIN t_user I ON A.create_user_id = I.user_id
        INNER JOIN t_user J ON A.update_user_id = J.user_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $9 '||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND A.doc_type_id = $8
        AND C.cost_amount <> 0;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeEdcSettlement, pOuId;

        -- **WARNING: Jika comment dilepas, perlu penyesuaian data2 yang di insert ke temp table**

--  -- Conversion CB In Other to Partner (nilai dokumen yang konversi)
--  EXECUTE '
--      INSERT INTO tt_detail_mutation_cashbank(
--          session_id, tenant_id, order_type_data,
--          doc_type_id, doc_type_desc,
--          doc_no, doc_date, ou_id, ou_code,
--          ou_rc_id, ou_rc_code, amount,
--          mode_payment, bank_payment, curr_code, remark,
--          create_user_id, create_user_name, create_datetime,
--          update_user_id, update_user_name, update_datetime,
--          remark_header, transaction_type)
--      -- nilai yang dikonversi
--      SELECT $1, $2, 2,
--          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
--          A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
--          Z.ou_id, f_get_ou_code(Z.ou_id), -1 * B.cashbank_amount,
--          G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
--          A.create_user_id, E.username, A.create_datetime,
--          A.update_user_id, F.username, A.update_datetime,
--          A.remark, $6
--      FROM cb_in_out_cashbank A
--      INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
--      INNER JOIN cb_in_out_cashbank X ON A.ref_id = X.in_out_cashbank_id AND A.ref_doc_type_id = X.doc_type_id
--      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
--      INNER JOIN t_user E ON A.create_user_id = E.user_id
--      INNER JOIN t_user F ON A.update_user_id = F.user_id
--      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
--      WHERE A.tenant_id = $2
--      AND Z.ou_id = $8 '||
--      vFilterCashbankV2||'
--      AND A.doc_date BETWEEN $4 AND $5
--      AND A.workflow_status = $3
--      AND A.doc_type_id = $7
--
--      UNION ALL
--      --nilai yang terkonversi
--      SELECT $1, $2, 2,
--          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
--          A.doc_no, A.doc_date, C.ou_id_cb_in_partner, f_get_ou_code(C.ou_id_cb_in_partner),
--          Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
--          G.flg_cash_bank, B.bank_payment, B.curr_code, CONCAT(''CONVERT | '', A.remark),
--          A.create_user_id, E.username, A.create_datetime,
--          A.update_user_id, F.username, A.update_datetime,
--          A.remark, $6
--      FROM cb_in_out_cashbank A
--      INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
--      INNER JOIN cb_in_out_cashbank_ext C ON A.in_out_cashbank_id = C.in_out_cashbank_id
--      INNER JOIN cb_in_out_cashbank X ON A.ref_id = X.in_out_cashbank_id AND A.ref_doc_type_id = X.doc_type_id
--      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
--      INNER JOIN t_user E ON A.create_user_id = E.user_id
--      INNER JOIN t_user F ON A.update_user_id = F.user_id
--      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
--      WHERE A.tenant_id = $2
--      AND Z.ou_id = $8 '||
--      vFilterCashbankV2||'
--      AND A.doc_date BETWEEN $4 AND $5
--      AND A.workflow_status = $3
--      AND A.doc_type_id = $7
--  'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInOP, pOuId;
--
--  -- Conversion CB In Other to Partner (jika ada cost)
--  EXECUTE '
--  INSERT INTO tt_detail_mutation_cashbank(
--          session_id, tenant_id, order_type_data,
--          doc_type_id, doc_type_desc,
--          doc_no, doc_date, ou_id, ou_code,
--          ou_rc_id, ou_rc_code, amount,
--          mode_payment, bank_payment, curr_code, remark,
--          create_user_id, create_user_name, create_datetime,
--          update_user_id, update_user_name, update_datetime,
--          remark_header, transaction_type, activity_gl_code, activity_gl_name)
--      -- nilai yang dikonversi
--      SELECT $1, $2, 2,
--          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
--          A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
--          Z.ou_id, f_get_ou_code(Z.ou_id), -1 * B.cashbank_amount,
--          G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
--          A.create_user_id, E.username, A.create_datetime,
--          A.update_user_id, F.username, A.update_datetime,
--          A.remark, $6, K.activity_gl_code, K.activity_gl_name
--      FROM cb_in_out_cashbank A
--      INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
--      INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
--      INNER JOIN cb_in_out_cashbank X ON A.ref_id = X.in_out_cashbank_id AND A.ref_doc_type_id = X.doc_type_id
--      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
--      INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
--      INNER JOIN t_user E ON A.create_user_id = E.user_id
--      INNER JOIN t_user F ON A.update_user_id = F.user_id
--      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
--      WHERE A.tenant_id = $2
--      AND Z.ou_id = $8 '||
--      vFilterCashbankV2||'
--      AND A.doc_date BETWEEN $4 AND $5
--      AND A.workflow_status = $3
--      AND A.doc_type_id = $7
--  'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInOP, pOuId;
--
--
--  -- Conversion CB In Partner to Other (nilai dokumen yang konversi)
--  EXECUTE '
--      INSERT INTO tt_detail_mutation_cashbank(
--          session_id, tenant_id, order_type_data,
--          doc_type_id, doc_type_desc,
--          doc_no, doc_date, ou_id, ou_code,
--          ou_rc_id, ou_rc_code, amount,
--          mode_payment, bank_payment, curr_code, remark,
--          create_user_id, create_user_name, create_datetime,
--          update_user_id, update_user_name, update_datetime,
--          remark_header, transaction_type, activity_gl_code, activity_gl_name)
--      -- nilai yang dikonversi
--      SELECT $1, $2, 2,
--          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
--          A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
--          X.ou_id, f_get_ou_code(X.ou_id), -1 * B.cashbank_amount,
--          G.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
--          A.create_user_id, E.username, A.create_datetime,
--          A.update_user_id, F.username, A.update_datetime,
--          A.remark, $6, K.activity_gl_code, K.activity_gl_name
--      FROM cb_in_out_cashbank A
--      INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
--      INNER JOIN cb_in_out_cashbank_cost D ON A.in_out_cashbank_id = D.in_out_cashbank_id
--      INNER JOIN cb_in_out_cashbank X ON A.ref_id = X.in_out_cashbank_id AND A.ref_doc_type_id = X.doc_type_id
--      INNER JOIN m_activity_gl K ON D.activity_gl_id = K.activity_gl_id
--      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
--      INNER JOIN t_user E ON A.create_user_id = E.user_id
--      INNER JOIN t_user F ON A.update_user_id = F.user_id
--      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
--      WHERE A.tenant_id = $2
--      AND Z.ou_id = $8 '||
--      vFilterCashbankV2||'
--      AND A.doc_date BETWEEN $4 AND $5
--      AND A.workflow_status = $3
--      AND A.doc_type_id = $7
--
--      UNION ALL
--      --nilai yang terkonversi
--      SELECT $1, $2, 2,
--          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
--          A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
--          Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
--          G.flg_cash_bank, B.bank_payment, B.curr_code, CONCAT(''CONVERT | '', A.remark),
--          A.create_user_id, E.username, A.create_datetime,
--          A.update_user_id, F.username, A.update_datetime,
--          A.remark, $6, K.activity_gl_code, K.activity_gl_name
--      FROM cb_in_out_cashbank A
--      INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
--      INNER JOIN cb_in_out_cashbank_cost D ON A.in_out_cashbank_id = D.in_out_cashbank_id
--      INNER JOIN cb_in_out_cashbank X ON A.ref_id = X.in_out_cashbank_id AND A.ref_doc_type_id = X.doc_type_id
--      INNER JOIN m_activity_gl K ON D.activity_gl_id = K.activity_gl_id
--      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
--      INNER JOIN t_user E ON A.create_user_id = E.user_id
--      INNER JOIN t_user F ON A.update_user_id = F.user_id
--      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
--      WHERE A.tenant_id = $2
--      AND Z.ou_id = $8 '||
--      vFilterCashbankV2||'
--      AND A.doc_date BETWEEN $4 AND $5
--      AND A.workflow_status = $3
--      AND A.doc_type_id = $7
--  'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeConversionCBInPO, pOuId;

    -- Cheque Giro Realization -> nilai dokumen masuk (OU From ambil dari OU Partner)
--  EXECUTE '
--      INSERT INTO tt_detail_mutation_cashbank(
--          session_id, tenant_id, order_type_data,
--          doc_type_id, doc_type_desc,
--          doc_no, doc_date, ou_id, ou_code,
--          ou_rc_id, ou_rc_code, amount,
--          mode_payment, bank_payment, curr_code, remark,
--          create_user_id, create_user_name, create_datetime,
--          update_user_id, update_user_name, update_datetime,
--          remark_header, transaction_type)
--      SELECT $1, $2, 2,
--          A.doc_type_id, f_get_doc_desc(A.doc_type_id),
--          A.doc_no, A.doc_date, C.ou_id, f_get_ou_code(C.ou_id),
--          Z.ou_id, f_get_ou_code(Z.ou_id), B.cheque_giro_amount,
--          G.flg_cash_bank, B.bank_payment, B.curr_code, B.remark,
--          A.create_user_id, E.username, A.create_datetime,
--          A.update_user_id, F.username, A.update_datetime,
--          A.remark, $6
--      FROM cb_in_out_cashbank A
--      INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
--      INNER JOIN m_partner_ou C ON B.partner_id = C.partner_id
--      INNER JOIN cb_cheque_giro_balance D ON B.ref_balance_id = D.cheque_giro_balance_id
--      INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
--      INNER JOIN t_user E ON A.create_user_id = E.user_id
--      INNER JOIN t_user F ON A.update_user_id = F.user_id
--      INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
--      WHERE A.tenant_id = $2
--      AND Z.ou_id = $8 '||
--      vFilterCashbankV2||'
--      AND A.doc_date BETWEEN $4 AND $5
--      AND A.workflow_status = $3
--      AND A.doc_type_id = $7
--      AND B.realization_status = $9;
--  'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCGRealization, pOuId,vStatusAccept;

    -- Cheque Giro Realization -> nilai dokumen masuk (OU From ambil dari OU Header Document)
    EXECUTE '
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
            Z.ou_id, f_get_ou_code(Z.ou_id), B.cheque_giro_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, B.curr_code, B.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime,
            A.remark, $6, CONCAT(Q.main_acc, '' - '', Q.sub_acc), Q.coa_desc
        FROM cb_in_out_cashbank A
        INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_cheque_giro_balance D ON B.ref_balance_id = D.cheque_giro_balance_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = G.cashbank_id
        INNER JOIN gl_journal_trx O ON A.in_out_cashbank_id = O.doc_id
                    AND A.doc_type_id = O.doc_type_id
                    AND A.tenant_id = O.tenant_id
        INNER JOIN vw_gl_journal_trx_details P ON O.journal_trx_id = P.journal_trx_id
                    AND O.doc_id = P.ref_id
                    AND O.doc_type_id = P.ref_doc_type_id
        INNER JOIN m_coa Q ON P.coa_id = Q.coa_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $8 '||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND P.flg_source_coa IN (''SYSTEM'', ''ACTIVITY'')
        AND A.doc_type_id = $7
        AND B.realization_status = $9;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCGRealization, pOuId,vStatusAccept;

    -- Cheque Giro Realization -> nilai cost
    EXECUTE '
        WITH tt_in_out_cashbank AS(
            SELECT A.in_out_cashbank_id, B.cashbank_id
            FROM cb_in_out_cashbank A
            INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
            WHERE B.realization_status = $10 '||
            vFilterCashbankV2||'
            GROUP BY A.in_out_cashbank_id, B.cashbank_id
        )
        INSERT INTO tt_detail_mutation_cashbank(
            session_id, tenant_id, order_type_data,
            doc_type_id, doc_type_desc,
            doc_no, doc_date, ou_id, ou_code,
            ou_rc_id, ou_rc_code, amount,
            cashbank_code, cashbank_name, account_no,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime,
            remark_header, transaction_type, activity_gl_code, activity_gl_name,
            coa_acc, coa_desc)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, Z.ou_id, f_get_ou_code(Z.ou_id),
            COALESCE(C.ou_rc_id, A.ou_id), f_get_ou_code(COALESCE(C.ou_rc_id, A.ou_id)), -1 * C.cost_amount,
            G.cashbank_code, G.cashbank_name, G.account_no,
            G.flg_cash_bank, G.bank_code, C.curr_code, C.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime,
            A.remark, $7, K.activity_gl_code, K.activity_gl_name,
            CONCAT(L.main_acc, '' - '', L.sub_acc), L.coa_desc
        FROM cb_in_out_cashbank A
        INNER JOIN tt_in_out_cashbank B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
        INNER JOIN m_activity_gl K ON C.activity_gl_id = K.activity_gl_id
        INNER JOIN m_coa L ON K.coa_id = L.coa_id
        INNER JOIN m_cashbank_assignment_ou Z ON B.cashbank_id = Z.cashbank_id
        INNER JOIN t_user E ON A.create_user_id = E.user_id
        INNER JOIN t_user F ON A.update_user_id = F.user_id
        INNER JOIN m_cashbank G ON B.cashbank_id = B.cashbank_id
        WHERE A.tenant_id = $2
        AND Z.ou_id = $9
        AND A.doc_date BETWEEN $4 AND $5
        AND A.workflow_status = $3
        AND A.doc_type_id = $8
        AND C.cost_amount <> 0;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeCGRealization, pOuId, vStatusAccept;

    -- POS
    EXECUTE '
    INSERT INTO tt_detail_mutation_cashbank(
        session_id, tenant_id, order_type_data,
        doc_type_id, doc_type_desc,
        doc_no, doc_date, ou_id, ou_code,
        ou_rc_id, ou_rc_code,
        amount,
        cashbank_code, cashbank_name, account_no,
        mode_payment, bank_payment, curr_code, remark,
        create_user_id, create_user_name, create_datetime,
        update_user_id, update_user_name, update_datetime,
        remark_header, transaction_type, coa_acc, coa_desc)
    SELECT $1, $2, 2,
        A.doc_type_id, f_get_doc_desc(A.doc_type_id),
        A.doc_no, A.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
        A.ou_id, f_get_ou_code(A.ou_id),
        CASE WHEN E.flg_termin = $9 THEN B.payment_amount ELSE (B.payment_amount- A.total_refund) END AS amount,
        D.cashbank_code, D.cashbank_name, D.account_no,
        D.flg_cash_bank, D.bank_code, A.curr_code, A.remark,
        A.create_user_id, F.username, A.create_datetime,
        A.update_user_id, G.username, A.update_datetime,
        A.remark, $6, $6, $6
    FROM i_trx_pos A
    INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
        AND A.process_no = B.process_no
        AND A.trx_pos_id = B.trx_pos_id
    INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
        AND A.process_no = E.process_no
        AND A.trx_pos_id = E.trx_pos_id
    INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
    INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = $8
    INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
    INNER JOIN t_user F ON A.create_user_id = F.user_id
    INNER JOIN t_user G ON A.update_user_id = G.user_id
    WHERE A.tenant_id = $2
    AND Z.ou_id = $7
    AND A.status IN ($3, $10)
    AND A.doc_date BETWEEN $4 AND $5;
    'USING pSessionId, pTenantId, vStatusSubmit, pDateStart, pDateEnd, vEmpty, pOuId, vFlagCash, vYes, vStatusVoid;

    --POS Void
    EXECUTE '
    INSERT INTO tt_detail_mutation_cashbank(
        session_id, tenant_id, order_type_data,
        doc_type_id, doc_type_desc,
        doc_no, doc_date, ou_id, ou_code,
        ou_rc_id, ou_rc_code,
        amount,
        cashbank_code, cashbank_name, account_no,
        mode_payment, bank_payment, curr_code, remark,
        create_user_id, create_user_name, create_datetime,
        update_user_id, update_user_name, update_datetime,
        remark_header, transaction_type, coa_acc, coa_desc)
    SELECT $1, $2, 2,
        A.doc_type_id, f_get_doc_desc(A.doc_type_id),
        A.doc_no||''_VOID'', F.doc_date, A.ou_id, f_get_ou_code(A.ou_id),
        A.ou_id, f_get_ou_code(A.ou_id),
        CASE WHEN E.flg_termin = $9 THEN -1 * B.payment_amount ELSE -1 * (B.payment_amount- A.total_refund) END AS amount,
        D.cashbank_code, D.cashbank_name, D.account_no,
        D.flg_cash_bank, D.bank_code, A.curr_code, F.remark,
        F.create_user_id, G.username, F.create_datetime,
        F.update_user_id, H.username, F.update_datetime,
        A.remark, $6, $6, $6
    FROM i_trx_pos A
    INNER JOIN i_trx_pos_cash_payment B ON A.tenant_id = B.tenant_id
        AND A.process_no = B.process_no
        AND A.trx_pos_id = B.trx_pos_id
    INNER JOIN i_trx_pos_custom E ON A.tenant_id = E.tenant_id
        AND A.process_no = E.process_no
        AND A.trx_pos_id = E.trx_pos_id
    INNER JOIN i_trx_log_voided_pos_custom F ON A.tenant_id = F.tenant_id
        AND A.process_no = F.process_no
    INNER JOIN m_cashbank_ou C ON A.ou_id = C.ou_id
    INNER JOIN m_cashbank D ON C.cashbank_id = D.cashbank_id AND D.flg_cash_bank = $8
    INNER JOIN m_cashbank_assignment_ou Z ON D.cashbank_id = Z.cashbank_id
    INNER JOIN t_user G ON F.create_user_id = G.user_id
    INNER JOIN t_user H ON F.update_user_id = H.user_id
    WHERE A.tenant_id = $2
    AND Z.ou_id = $7
    AND A.status = $3
    AND A.doc_date BETWEEN $4 AND $5;
    'USING pSessionId, pTenantId, vStatusVoid, pDateStart, pDateEnd, vEmpty, pOuId, vFlagCash, vYes;

    Open pRefHeader FOR
    SELECT pOuId AS ou_id, f_get_ou_code(pOuId) AS ou_code, f_get_ou_name(pOuId) AS ou_name, pDateStart AS date_start, pDateEnd AS date_end,
           pDateStart AS date_trx, pDatetime AS datetime,
           pCashbankId AS cashbank_id,
           CASE WHEN pCashbankId = -99 THEN 'ALL' ELSE f_get_cashbank_code(pCashbankId)||' - '||f_get_cashbank_name(pCashbankId)END AS cashbank_code_name;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
        SELECT TO_CHAR(TO_TIMESTAMP(A.doc_date,'YYYYMMDD'),'DD-MM-YYYY') AS doc_date,
                A.doc_no,
                A.ou_code AS ou_from_code,
                A.ou_rc_code AS ou_to_code,
                A.remark,
                A.coa_acc, A.coa_desc,
                A.activity_gl_code, A.activity_gl_name,
                --A.remark_header,
                CASE WHEN A.mode_payment = 'C' THEN 'Kas' ELSE 'Bank' END AS cashbank_type,
                A.cashbank_code, A.cashbank_name, A.account_no,
                A.bank_payment,
                A.curr_code,
                CASE WHEN (SUM(A.amount) > 0 ) THEN SUM(A.amount) ELSE 0 END AS debit,
                CASE WHEN (SUM(A.amount) < 0 ) THEN -1 * SUM(A.amount) ELSE 0 END AS credit,
                --CASE WHEN (A.amount > 0 ) THEN A.amount ELSE 0 END AS debit,
                --CASE WHEN (A.amount < 0 ) THEN -1 * A.amount ELSE 0 END AS credit,
                CASE WHEN A.transaction_type = vEmpty THEN A.doc_type_desc ELSE A.transaction_type END AS transaction_type,
                A.create_user_name AS created_by,
                to_char(to_timestamp(A.create_datetime, 'YYYYMMDDHH24MISS'), 'DD-MM-YYYY HH24:MI') AS create_datetime,
                A.update_user_name AS last_update_by,
                to_char(to_timestamp(A.update_datetime, 'YYYYMMDDHH24MISS'), 'DD-MM-YYYY HH24:MI') AS last_update_datetime
        FROM tt_detail_mutation_cashbank A
        WHERE A.session_id = pSessionId
        GROUP BY A.doc_date, A.doc_no, A.ou_code, A.ou_rc_code, A.remark, A.mode_payment, A.bank_payment,
        A.cashbank_code, A.cashbank_name, A.account_no, A.doc_type_desc, A.transaction_type,
        A.coa_acc, A.coa_desc, A.activity_gl_code, A.activity_gl_name,
        A.curr_code, A.create_user_name, A.create_datetime, A.update_user_name, A.update_datetime
        ORDER BY A.update_datetime ASC, A.doc_date, A.doc_no, A.activity_gl_code, A.ou_code, A.ou_rc_code;
    RETURN NEXT pRefDetail;

    DELETE FROM tt_detail_mutation_cashbank WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  /