query3

-- 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, X.ou_id, f_get_ou_code(X.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, ''
        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.status_doc = $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, f_get_ou_code(C.ou_id),
            X.ou_id, f_get_ou_code(X.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,
            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_partner_ou C ON A.partner_id = C.partner_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, 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)
        -- 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, C.ou_id, f_get_ou_code(C.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
        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_partner_ou C ON A.partner_id = C.partner_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

        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, X.ou_id, f_get_ou_code(X.ou_id),
            C.ou_id, f_get_ou_code(C.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,
            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_partner_ou C ON A.partner_id = C.partner_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, vDocTypeConversionCBInPO, pOuId;