query2

-- 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,
            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),
            D.ou_id, f_get_ou_code(D.ou_id), E.cashbank_amount,
            G.flg_cash_bank, E.bank_payment, E.curr_code, A.remark,
            A.create_user_id, H.username, A.create_datetime,
            A.update_user_id, I.username, A.update_datetime
        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 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 '||
        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, 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,
            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),
            COALESCE(B.ou_rc_id, A.ou_id), f_get_ou_code(COALESCE(B.ou_rc_id, A.ou_id)), -1 * B.cost_amount,
            G.flg_cash_bank, E.bank_payment, E.curr_code, B.remark,
            A.create_user_id, H.username, A.create_datetime,
            A.update_user_id, I.username, A.update_datetime
        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
        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 '||
        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, vDocTypeCashAdvanceSettlement, pOuId;

-- Cheque Giro Realization -> 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),
            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
        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.status_doc = $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 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,
            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),
            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.flg_cash_bank, $7, C.curr_code, C.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 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_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.status_doc = $3
        AND A.doc_type_id = $8
        AND C.cost_amount <> 0;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeCGRealization, pOuId, vStatusAccept;

-- 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,
            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, Y.ou_id, f_get_ou_code(Y.ou_id),
            Z.ou_id, f_get_ou_code(Z.ou_id), B.cashbank_amount,
            H.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
            A.create_user_id, I.username, A.create_datetime,
            A.update_user_id, J.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_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 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.status_doc = $3
        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,
            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),
            Y.ou_id, f_get_ou_code(Y.ou_id), -1 * C.cost_amount,
            H.flg_cash_bank, B.bank_payment, B.curr_code, A.remark,
            A.create_user_id, I.username, A.create_datetime,
            A.update_user_id, J.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 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 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.status_doc = $3
        AND A.doc_type_id = $8
        AND C.cost_amount <> 0;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeEdcSettlement, pOuId;