query4

-- FUCAS -> nilai dokumen masuk (ref amount cb_in_out_cashbank < 0)
    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, A.ou_id, f_get_ou_code(A.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, 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 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 = $9 '||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.status_doc = $3
        AND A.doc_type_id = $8
        AND A.ref_amount < 0;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeFollowUpCashAdvanceSettlement, pOuId;

-- FUCAS -> nilai dokumen keluar (ref amount cb_in_out_cashbank > 0)
        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, A.ou_id, f_get_ou_code(A.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 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 = $9 '||
        vFilterCashbankV2||'
        AND A.doc_date BETWEEN $4 AND $5
        AND A.status_doc = $3
        AND A.doc_type_id = $8
        AND A.ref_amount > 0;
    'USING pSessionId, pTenantId, vStatusReleased, pDateStart, pDateEnd, vEmptyId, vEmpty, vDocTypeFollowUpCashAdvanceSettlement, pOuId;


-- 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,
        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, 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.flg_cash_bank, $6, 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
    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,
        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||''_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.flg_cash_bank, $6, 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
    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;