query

-- CB TRANSFER YG CB OU 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,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime)
        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 * (A.transfer_amount),
            G.flg_cash_bank, A.bank_payment, A.curr_code, A.remark,
            A.create_user_id, D.username, A.create_datetime,
            A.update_user_id, E.username, A.update_datetime
        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 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.status_doc = $3
        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.flg_cash_bank, A.bank_payment, A.curr_code, A.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime
        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 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.status_doc = $3
        AND A.doc_date BETWEEN $4 AND $5
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, pOuId, vEmpty;

-- CB TRANSFER YG CB OU 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,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime)
        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 AS ou_id_from, f_get_ou_code(A.ou_id) AS ou_from,
            B.ou_to_id AS ou_id_to, f_get_ou_code(B.ou_to_id) AS ou_to, B.receive_amount,
            G.flg_cash_bank, B.bank_payment, A.curr_code, B.remark,
            A.create_user_id, E.username, A.create_datetime,
            A.update_user_id, F.username, A.update_datetime
        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 E ON B.cashbank_to_id = E.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
        WHERE A.tenant_id = $2
        AND E.ou_id = $7 '||
        vFilterCashbankV1   ||'
        AND A.status_doc = $3
        AND A.doc_date BETWEEN $4 AND $5
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, pOuId, 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,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime)
        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.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
        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
        WHERE A.tenant_id = $2
        AND Z.ou_id = $7 ' ||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.status_doc = $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,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime)
        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.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
        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
        WHERE A.tenant_id = $2
        AND Z.ou_id = $7 ' ||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.status_doc = $3
        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,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime)
        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),
            A.ou_id, f_get_ou_code(A.ou_id), 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
        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 m_partner_ou C ON A.partner_id = C.partner_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 = $9 '||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.status_doc = $3
        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,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime)
        SELECT $1, $2, 2,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id),
            A.doc_no, A.doc_date, D.ou_id, f_get_ou_code(D.ou_id),
            A.ou_id, f_get_ou_code(A.ou_id), -1 * C.cost_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
        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_partner_ou D ON A.partner_id = D.partner_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.status_doc = $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,
            mode_payment, bank_payment, curr_code, remark,
            create_user_id, create_user_name, create_datetime,
            update_user_id, update_user_name, update_datetime)
        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), C.cost_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
        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 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.status_doc = $3
        AND A.doc_type_id = $7;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmpty, vDocTypeCashBankInOther, pOuId;