f_crosscheck_cashbank_docs

CREATE OR REPLACE FUNCTION f_crosscheck_cashbank_docs(character varying, bigint, bigint, character varying, bigint)
    RETURNS void AS
$BODY$
DECLARE
    pSessionId                              ALIAS FOR $1;
    pTenantId                               ALIAS FOR $2;
    pOuId                                   ALIAS FOR $3;   -- bisa -99
    pYearMonth                              ALIAS FOR $4;
    pCashbankId                             ALIAS FOR $5;   -- bisa -99

    vStatusRelease                          character varying := 'R';
    vRecTypeDebit                           character varying := 'D';
    vRecTypeKredit                          character varying := 'K';
    vTypeIn                                 character varying := 'I';
    vTypeOut                                character varying := 'O';
    vStatusAccept                           character varying := 'ACCEPT';

    vEmptyId                                bigint := -99;
    vDocTypeCBOut                           bigint := 611;
    vDocTypeFollowUpCashAdvanceSettlement   bigint := 641;
    vDocTypeCBTransfer                      bigint := 631;
    vDocTypeCBInPartner                     bigint := 621;
    vDocTypeCBInOther                       bigint := 623;
    vDocTypeCGRealization                   bigint := 625;
    vDocTypeEDCSettlement                   bigint := 622;
    vDocTypePOSShop                         bigint := 401;
    vDocTypeReturnPOSShop                   bigint := 402;

    vFilterCashbankIdA                      text := '';
    vFilterCashbankIdB                      text := '';
    vFilterCashbankIdC                      text := '';
    vFilterCashbankIdD                      text := '';
    vFilterCashbankToId                     text := '';
    vFilterOuId                             text := '';
    vFilterOuToId                           text := '';
    vFilterOuBuId                           text := '';
    vSpaceValue                             text := ' ';

BEGIN

    /*
     * 1. Ambil data transaksi Cash/Bank Out (K)
     * 2.a. Ambil data transaksi Follow Up Cash Advance Settlement (K)
     * 2.b. Ambil data transaksi Follow Up Cash Advance Settlement (D)
     * 3.a. Ambil data transaksi Cash/Bank Transfer (K)
     * 3.b. Ambil data transaksi Cash/Bank Transfer (D)
     * 4. Ambil data transaksi Cash/Bank In Partner Receive (D)
     * 5. Ambil data transaksi Cash/Bank In Other Receive (D)
     * 6. Ambil data transaksi Cheque/Giro Realization (D)
     * 7. Ambil data transaksi EDC Settlement (D)
     * 8. Ambil data transaksi POS Shop (D)
     * 9. Ambil data transaksi Return POS Shop (D)
     * 10. Ambil data transaksi Void POS Shop (K)
     *
     * 11. Buat data recap
     * 12. Ambil data dari cashbank balance
     * 13. Cari data recap yang tidak ada dalam cashbank balance
     * 14. Cari data cashbank balance yang tidak ada dalam recap
     * 15. Simpan selisih amount recap terhadap cashbank balance
     *
     * 16. Cari data yang tidak ada di jurnal
     * 17. Cari data yang tidak ada di detail jurnal
     * */

    IF ( pCashbankId <> vEmptyId ) THEN
        vFilterCashbankIdA := ' AND A.cashbank_id = ' || pCashbankId ;
        vFilterCashbankIdB := ' AND B.cashbank_id = ' || pCashbankId ;
        vFilterCashbankIdC := ' AND C.cashbank_id = ' || pCashbankId ;
        vFilterCashbankIdD := ' AND D.cashbank_id = ' || pCashbankId ;
        vFilterCashbankToId := ' AND A.cashbank_to_id = ' || pCashbankId ;
    END IF;

    IF ( pOuId <> vEmptyId ) THEN
        vFilterOuId := ' AND A.ou_id = ' || pOuId ;
        vFilterOuToId := ' AND A.ou_to_id = ' || pOuId ;
        vFilterOuBuId := ' AND C.ou_bu_id = ' || pOuId ;
    END IF;

    DELETE FROM tt_cb_check_balance_for_trx WHERE session_id = pSessionId;
    DELETE FROM tr_kartu_kas_by_doc_type WHERE session_id = pSessionId;
    DELETE FROM tt_cb_check_balance_for_recap WHERE session_id = pSessionId;
    DELETE FROM tt_cb_check_balance_for_cashbank_balance WHERE session_id = pSessionId;
    DELETE FROM tt_cb_check_balance_for_result WHERE session_id = pSessionId;
    DELETE FROM tt_cb_check_balance_for_gl_journal_trx WHERE session_id = pSessionId;
    DELETE FROM tt_cb_check_balance_for_gl_journal_trx_details WHERE session_id = pSessionId;


    -- 1. Ambil data transaksi Cash/Bank Out (K)
    EXECUTE '
        INSERT INTO tt_cb_check_balance_for_trx
            (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
            type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
        SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
            A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
        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
        WHERE A.tenant_id = $2
            AND SUBSTR(A.doc_date, 1, 6) = $4
            AND A.doc_type_id = $5 '
            || vFilterOuId
            || vFilterCashbankIdB ||'
        GROUP BY A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id, A.type_in_out_cashbank, A.status_doc'
    USING pSessionId, pTenantId, vRecTypeKredit, pYearMonth, vDocTypeCBOut;


    -- 2.a. Ambil data transaksi Follow Up Cash Advance Settlement (K)
    EXECUTE '
        INSERT INTO tt_cb_check_balance_for_trx
            (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
            type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
        SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
            A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
        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
        WHERE A.tenant_id = $2
            AND SUBSTR(A.doc_date, 1, 6) = $4
            AND A.doc_type_id = 5
            AND A.type_in_out_cashbank = $6 '
            || vFilterOuId
            || vFilterCashbankIdB || '
        GROUP BY A.type_in_out_cashbank, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
            A.type_in_out_cashbank, A.status_doc'
    USING pSessionId, pTenantId, vRecTypeKredit, pYearMonth, vDocTypeFollowUpCashAdvanceSettlement, vTypeOut;

    -- 2.b. Ambil data transaksi Follow Up Cash Advance Settlement (D)
    EXECUTE '
        INSERT INTO tt_cb_check_balance_for_trx
            (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
            type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
        SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
            A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
        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
        WHERE A.tenant_id = $2
            AND SUBSTR(A.doc_date, 1, 6) = $4
            AND A.status_doc = $5
            AND A.doc_type_id = $6
            AND A.type_in_out_cashbank = $7 '
            || vFilterOuId
            || vFilterCashbankIdB || '
        GROUP BY A.type_in_out_cashbank, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
            A.type_in_out_cashbank, A.status_doc'
    USING pSessionId, pTenantId, vRecTypeDebit, pYearMonth, vStatusRelease, vDocTypeFollowUpCashAdvanceSettlement, vTypeIn;


    -- 3.a. Ambil data transaksi Cash/Bank Transfer (K)
    EXECUTE '
        INSERT INTO tt_cb_check_balance_for_trx
            (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
            type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
        SELECT $1, $2, A.doc_type_id, A.transfer_cashbank_id, A.doc_no, A.doc_date, $3, A.cashbank_id,
            $4, $5, A.transfer_amount + COALESCE(B.cost_amount, 0), 0, A.status_doc
        FROM cb_transfer_cashbank A
        LEFT OUTER JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id = B.transfer_cashbank_id
        WHERE A.tenant_id = $2
            AND SUBSTR(A.doc_date, 1, 6) = $6
            AND A.doc_type_id = $7 '
            || vFilterOuId
            || vFilterCashbankIdA || '
        GROUP BY A.doc_type_id, A.transfer_cashbank_id, A.doc_no, A.doc_date, A.cashbank_id, A.transfer_amount, B.cost_amount, A.status_doc '
    USING pSessionId, pTenantId, vEmptyId, vTypeOut, vRecTypeKredit, pYearMonth, vDocTypeCBTransfer;

    -- 3.b. Ambil data transaksi Cash/Bank Transfer (D)
    EXECUTE '
        INSERT INTO tt_cb_check_balance_for_trx
            (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
            type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
        SELECT $1, $2, B.doc_type_id, B.transfer_cashbank_id, B.doc_no, B.doc_date, $3, A.cashbank_to_id,
            $4, $5, A.receive_amount, 0, B.status_doc
        FROM cb_transfer_cashbank_receive A
        INNER JOIN cb_transfer_cashbank B ON B.transfer_cashbank_id = A.transfer_cashbank_id
        WHERE A.tenant_id = $2
            AND SUBSTR(B.doc_date, 1, 6) = $6
            AND B.status_doc = $7
            AND B.doc_type_id = $8 '
            || vFilterOuToId
            || vFilterCashbankToId
    USING pSessionId, pTenantId, vEmptyId, vTypeIn, vRecTypeDebit, pYearMonth, vStatusRelease, vDocTypeCBTransfer;


    -- 4. Ambil data transaksi Cash/Bank In Partner Receive (D)
    EXECUTE '
        WITH tt_in_out_cashbank_cost AS (
            SELECT A.in_out_cashbank_id, SUM(C.cost_amount) AS cost_amount
            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
            WHERE A.tenant_id = $2
                AND SUBSTR(A.doc_date, 1, 6) = $4
                AND A.status_doc = $5
                AND A.doc_type_id = $6 '
                || vFilterOuId
                || vFilterCashbankIdB || '
                GROUP BY A.in_out_cashbank_id
        )
        INSERT INTO tt_cb_check_balance_for_trx
            (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
            type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
        SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
            A.type_in_out_cashbank, $3, SUM(B.cashbank_amount) - COALESCE(C.cost_amount, 0), 0, A.status_doc
        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
        LEFT OUTER JOIN tt_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
        WHERE A.tenant_id = $2
            AND SUBSTR(A.doc_date, 1, 6) = $4
            AND A.status_doc = $5
            AND A.doc_type_id = $6 '
            || vFilterOuId
            || vFilterCashbankIdB || '
        GROUP BY A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id, A.type_in_out_cashbank,
        C.cost_amount, A.status_doc'
    USING pSessionId, pTenantId, vRecTypeDebit, pYearMonth, vStatusRelease, vDocTypeCBInPartner;


    -- 5. Ambil data transaksi Cash/Bank In Other Receive (D)
    EXECUTE '
        INSERT INTO tt_cb_check_balance_for_trx
            (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
            type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
        SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
            A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
        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
        WHERE A.tenant_id = $2
            AND SUBSTR(A.doc_date, 1, 6) = $4
            AND A.status_doc = $5
            AND A.doc_type_id = $6 '
            || vFilterOuId
            || vFilterCashbankIdB || '
        GROUP BY A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id, A.type_in_out_cashbank, A.status_doc'
    USING pSessionId, pTenantId, vRecTypeDebit, pYearMonth, vStatusRelease, vDocTypeCBInOther;


    -- 6. Ambil data transaksi Cheque/Giro Realization (D)
    EXECUTE '
        WITH tt_in_out_cashbank_cost AS (
            SELECT A.in_out_cashbank_id, SUM(C.cost_amount) AS cost_amount
            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_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
            WHERE A.status_doc = $4
                AND SUBSTR(A.doc_date, 1, 6) = $5
                AND B.realization_status = $6
                AND A.doc_type_id = $7 '
                || vFilterOuId
                || vFilterCashbankIdB || '
            GROUP BY A.in_out_cashbank_id
        )
        INSERT INTO tt_cb_check_balance_for_trx
            (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
             type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
        SELECT $1, A.tenant_id, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, B.partner_id, B.cashbank_id,
            $2, $3, SUM(B.cheque_giro_amount) - COALESCE(C.cost_amount, 0), 0, A.status_doc
        FROM cb_in_out_cashbank A
        INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        LEFT OUTER JOIN tt_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
        WHERE A.status_doc = $4
            AND SUBSTR(A.doc_date, 1, 6) = $5
            AND B.realization_status = $6
            AND A.doc_type_id = $7 '
            || vFilterOuId
            || vFilterCashbankIdB || '
        GROUP BY A.tenant_id, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, B.partner_id, B.cashbank_id,
            C.cost_amount, A.status_doc'
    USING pSessionId, vTypeIn, vRecTypeDebit, vStatusRelease, pYearMonth, vStatusAccept, vDocTypeCGRealization;


    -- 7. Ambil data transaksi EDC Settlement (D)
    EXECUTE '
        INSERT INTO tt_cb_check_balance_for_trx
            (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
            type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
        SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
            A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
        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
        WHERE A.tenant_id = $2
            AND SUBSTR(A.doc_date, 1, 6) = $4
            AND A.status_doc = $5
            AND A.doc_type_id = $6 '
            || vFilterOuId
            || vFilterCashbankIdB || '
        GROUP BY A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id, A.type_in_out_cashbank,
            A.status_doc'
    USING pSessionId, pTenantId, vRecTypeDebit, pYearMonth, vStatusRelease, vDocTypeEDCSettlement;


    -- 8. Ambil data transaksi penjualan POS Shop (D)
    EXECUTE '
        INSERT INTO tr_kartu_kas_by_doc_type
        (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
        doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
        amount_debit,
        amount_credit)
        SELECT $1, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
            A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
            CASE WHEN ((A.trx_pos_id <> $5) AND (A.doc_no <> A.process_no)) THEN
                SUM(B.payment_amount)
            ELSE
                SUM(B.conversion_amount)
            END AS amount_debit,
            SUM(A.total_refund) AS amount_credit
        FROM i_trx_pos A
        INNER JOIN i_trx_pos_cash_payment B ON A.trx_pos_id = B.trx_pos_id AND A.process_no = B.process_no
        INNER JOIN m_document C ON A.doc_type_id = C.doc_type_id
        INNER JOIN m_cashbank D ON B.curr_payment_code = D.curr_code
        INNER JOIN m_cashbank_ou E ON D.cashbank_id = E.cashbank_id AND A.ou_id = E.ou_id
        WHERE A.tenant_id = $2
            AND SUBSTR(A.doc_date, 1, 6) = $3
            AND A.doc_type_id = $4
            AND A.tenant_id = B.tenant_id
            AND D.flg_cash_bank = '''|| 'C' ||''''
            || vFilterOuId
            || vFilterCashbankIdD || '
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id,
            D.cashbank_id, D.curr_code, A.process_no'
    USING pSessionId, pTenantId, pYearMonth, vDocTypePOSShop, vEmptyId;


    -- 9. Ambil data transaksi Return POS Shop (D)
    EXECUTE '
        INSERT INTO tr_kartu_kas_by_doc_type
        (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
        doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
        amount_debit, amount_credit)
        SELECT $1, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
            A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
            0, SUM(A.total_refund)
        FROM i_trx_pos A
        INNER JOIN m_document C ON A.doc_type_id = C.doc_type_id
        INNER JOIN m_cashbank D ON A.curr_code = D.curr_code
        INNER JOIN m_cashbank_ou E ON D.cashbank_id = E.cashbank_id AND A.ou_id = E.ou_id
        WHERE A.tenant_id = $2
            AND SUBSTR(A.doc_date, 1, 6) = $3
            AND A.doc_type_id = $4
            AND D.flg_cash_bank = '''|| 'C' ||''''
            || vFilterOuId
            || vFilterCashbankIdD || '
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id,
            D.cashbank_id, D.curr_code'
    USING pSessionId, pTenantId, pYearMonth, vDocTypeReturnPosShop;


    -- Ambil data POS dari table temporary tr_kartu_kas_by_doc_type,
    -- Masukkan ke table tt_cb_check_balance_for_trx
    -- Untuk transaksi penjualan dari ERP (D)
    INSERT INTO tt_cb_check_balance_for_trx
        (session_id, tenant_id, doc_type_id,
         doc_id, doc_no, doc_date, partner_id, cashbank_id,
         type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
    SELECT pSessionId, pTenantId, A.doc_type_id,
            A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
            vTypeIn, vRecTypeDebit, SUM(A.amount_debit), 0, ''
    FROM tr_kartu_kas_by_doc_type A
    WHERE A.tenant_id = pTenantId
    AND A.session_id = pSessionId
    AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
    AND A.ref_id = vEmptyId
    GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;

    -- Ambil data POS dari table temporary tr_kartu_kas_by_doc_type,
    -- Masukkan ke table tt_cb_check_balance_for_trx
    -- Untuk transaksi refund dari ERP (K)
    INSERT INTO tt_cb_check_balance_for_trx
        (session_id, tenant_id, doc_type_id,
         doc_id, doc_no, doc_date, partner_id, cashbank_id,
         type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
    SELECT pSessionId, pTenantId, A.doc_type_id,
            A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
            vTypeIn, vRecTypeKredit, SUM(A.amount_credit), 0, ''
    FROM tr_kartu_kas_by_doc_type A
    WHERE A.tenant_id = pTenantId
    AND A.session_id = pSessionId
    AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
    AND A.ref_id = vEmptyId
    GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;

    -- Ambil data POS dari table temporary tr_kartu_kas_by_doc_type,
    -- Masukkan ke table tt_cb_check_balance_for_trx
    -- Untuk transaksi dari POS
    INSERT INTO tt_cb_check_balance_for_trx
        (session_id, tenant_id, doc_type_id,
         doc_id, doc_no, doc_date, partner_id, cashbank_id,
         type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
    SELECT pSessionId, pTenantId, A.doc_type_id,
            A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
            vTypeIn, vRecTypeDebit, SUM(A.amount_debit - A.amount_credit), 0, ''
    FROM tr_kartu_kas_by_doc_type A
    WHERE A.tenant_id = pTenantId
    AND A.session_id = pSessionId
    AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
    AND A.ref_id <> vEmptyId
    GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;


    -- 10. Ambil data transaksi Void POS Shop
    -- Penjualan POS dari ERP (K)
    INSERT INTO tt_cb_check_balance_for_trx
        (session_id, tenant_id, doc_type_id,
         doc_id, doc_no, doc_date, partner_id, cashbank_id,
         type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
    SELECT pSessionId, pTenantId, A.doc_type_id,
            A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
            vTypeIn, vRecTypeKredit, SUM(A.amount_debit), 0, ''
    FROM tr_kartu_kas_by_doc_type A
    WHERE A.tenant_id = pTenantId
    AND A.session_id = pSessionId
    AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
    AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
                    WHERE A.tenant_id = B.tenant_id AND
                    A.doc_no = B.doc_no AND
                    A.doc_date = B.doc_date AND
                    A.ou_id = B.ou_id)
    AND A.ref_id = vEmptyId
    GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;

    -- 10. Ambil data transaksi Void POS Shop
    -- Refund POS dari ERP (D)
    INSERT INTO tt_cb_check_balance_for_trx
        (session_id, tenant_id, doc_type_id,
         doc_id, doc_no, doc_date, partner_id, cashbank_id,
         type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
    SELECT pSessionId, pTenantId, A.doc_type_id,
            A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
            vTypeIn, vRecTypeDebit, SUM(A.amount_credit), 0, ''
    FROM tr_kartu_kas_by_doc_type A
    WHERE A.tenant_id = pTenantId
    AND A.session_id = pSessionId
    AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
    AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
                    WHERE A.tenant_id = B.tenant_id AND
                    A.doc_no = B.doc_no AND
                    A.doc_date = B.doc_date AND
                    A.ou_id = B.ou_id)
    AND A.ref_id = vEmptyId
    GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;

    -- 10. Ambil data transaksi Void POS Shop
    -- Penjualan POS dari POS (K)
    INSERT INTO tt_cb_check_balance_for_trx
        (session_id, tenant_id, doc_type_id,
         doc_id, doc_no, doc_date, partner_id, cashbank_id,
         type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
    SELECT pSessionId, pTenantId, A.doc_type_id,
            A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
            vTypeIn, vRecTypeKredit, SUM(A.amount_debit), 0, ''
    FROM tr_kartu_kas_by_doc_type A
    WHERE A.tenant_id = pTenantId
    AND A.session_id = pSessionId
    AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
    AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
                    WHERE A.tenant_id = B.tenant_id AND
                    A.doc_no = B.doc_no AND
                    A.doc_date = B.doc_date AND
                    A.ou_id = B.ou_id)
    AND A.ref_id <> vEmptyId
    GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;

    -- 10. Ambil data transaksi Void POS Shop
    -- Refund POS dari POS (D)
    INSERT INTO tt_cb_check_balance_for_trx
        (session_id, tenant_id, doc_type_id,
         doc_id, doc_no, doc_date, partner_id, cashbank_id,
         type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
    SELECT pSessionId, pTenantId, A.doc_type_id,
            A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
            vTypeIn, vRecTypeDebit, SUM(A.amount_credit), 0, ''
    FROM tr_kartu_kas_by_doc_type A
    WHERE A.tenant_id = pTenantId
    AND A.session_id = pSessionId
    AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
    AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
                    WHERE A.tenant_id = B.tenant_id AND
                    A.doc_no = B.doc_no AND
                    A.doc_date = B.doc_date AND
                    A.ou_id = B.ou_id)
    AND A.ref_id <> vEmptyId
    GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;


    -- 11. Buat data recap
    EXECUTE '
        INSERT INTO tt_cb_check_balance_for_recap(session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, amount)
        SELECT $1, $2, A.cashbank_id, A.doc_date, A.sign_cashbank, B.curr_code, SUM(A.amount)
        FROM tt_cb_check_balance_for_trx A
        INNER JOIN m_cashbank B ON A.cashbank_id = B.cashbank_id
        WHERE A.session_id = $1 '
            || vFilterCashbankIdA || '
        GROUP BY A.cashbank_id, A.doc_date, A.sign_cashbank, B.curr_code '
    USING pSessionId, pTenantId;


    -- 12. Ambil data dari cashbank balance
    EXECUTE '
        INSERT INTO tt_cb_check_balance_for_cashbank_balance
            (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, amount)
        SELECT $1, $2, A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code, SUM(A.amount)
        FROM cb_cashbank_balance A
        WHERE A.tenant_id = $2
            AND SUBSTR(A.cash_bank_date, 1, 6) = $3
            AND A.rec_type <> ''' || 'A' || ''''
            || vFilterOuId
            || vFilterCashbankIdA || '
        GROUP BY A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code'
    USING pSessionId, pTenantId, pYearMonth;


    -- 13. Cari data recap yang tidak ada dalam cashbank balance
    INSERT INTO tt_cb_check_balance_for_result
        (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, trx_amount, balance_amount, diff_amount)
    SELECT A.session_id, A.tenant_id, A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code, A.amount, 0, A.amount
    FROM tt_cb_check_balance_for_recap A
    WHERE NOT EXISTS (
        SELECT 1 FROM tt_cb_check_balance_for_cashbank_balance B
        WHERE A.session_id = B.session_id
        AND A.tenant_id = B.tenant_id
        AND A.cashbank_id = B.cashbank_id
        AND A.cash_bank_date = B.cash_bank_date
        AND A.rec_type = B.rec_type
        AND A.curr_code = B.curr_code
    ) AND A.session_id = pSessionId;


    -- 14. Cari data cashbank balance yang tidak ada dalam recap
    INSERT INTO tt_cb_check_balance_for_result
        (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, trx_amount, balance_amount, diff_amount)
    SELECT A.session_id, A.tenant_id, A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code, 0, A.amount, A.amount
    FROM tt_cb_check_balance_for_cashbank_balance A
    WHERE NOT EXISTS (
        SELECT 1 FROM tt_cb_check_balance_for_recap B
        WHERE A.session_id = B.session_id
        AND A.tenant_id = B.tenant_id
        AND A.cashbank_id = B.cashbank_id
        AND A.cash_bank_date = B.cash_bank_date
        AND A.rec_type = B.rec_type
        AND A.curr_code = B.curr_code
    ) AND A.session_id = pSessionId;


    -- 15. Simpan selisih amount recap terhadap cashbank balance
    INSERT INTO tt_cb_check_balance_for_result
        (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, trx_amount, balance_amount, diff_amount)
    SELECT A.session_id, A.tenant_id, A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code, B.amount, A.amount, B.amount - A.amount
    FROM tt_cb_check_balance_for_cashbank_balance A
    INNER JOIN tt_cb_check_balance_for_recap B
        ON A.session_id = B.session_id
        AND A.tenant_id = B.tenant_id
        AND A.cashbank_id = B.cashbank_id
        AND A.cash_bank_date = B.cash_bank_date
        AND A.rec_type = B.rec_type
        AND A.curr_code = B.curr_code
    AND A.session_id = pSessionId;


    -- 16. Cari data yang tidak ada di jurnal
    INSERT INTO tt_cb_check_balance_for_gl_journal_trx
        (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, curr_code)
    SELECT pSessionId, pTenantId, A.doc_type_id, A.doc_id, A.doc_no, A.doc_date, C.curr_code
    FROM tt_cb_check_balance_for_trx A
    INNER JOIN m_cashbank C ON A.cashbank_id = C.cashbank_id
    WHERE A.session_id = pSessionId
    AND A.status_doc = vStatusRelease
    AND NOT EXISTS (
        SELECT 1 FROM gl_journal_trx B
        WHERE B.tenant_id = A.tenant_id
        AND B.doc_type_id = A.doc_type_id
        AND B.doc_id = A.doc_id
        AND B.doc_no = A.doc_no
        AND B.doc_date = A.doc_date
    );

    -- 17. Cari data yang tidak ada di detail jurnal
    EXECUTE '
        INSERT INTO tt_cb_check_balance_for_gl_journal_trx_details
            (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, cashbank_id, curr_code, amount, gl_detail_curr_code, gl_detail_amount, journal_trx_id)
        SELECT $1, $2, A.doc_type_id, A.doc_id, A.doc_no, A.doc_date, A.cashbank_id, D.curr_code, A.amount, $5, 0, -99
        FROM tt_cb_check_balance_for_trx A
        INNER JOIN m_cashbank D ON A.cashbank_id = D.cashbank_id
        WHERE A.session_id = $1
        AND A.status_doc = $4
        AND NOT EXISTS (
             SELECT 1 FROM vw_gl_journal_trx_details B
             INNER JOIN gl_journal_trx C ON B.journal_trx_id = C.journal_trx_id
             WHERE A.tenant_id = B.tenant_id
             AND C.doc_type_id = A.doc_type_id
             AND C.doc_id = A.doc_id
             AND C.doc_no = A.doc_no
             AND C.doc_date = A.doc_date
             AND A.amount = B.amount
             AND C.tenant_id = $2
             AND SUBSTR(C.doc_date, 1, 6) = $3'
            || vFilterOuBuId
            || vFilterCashbankIdC || '
        )'
    USING pSessionId, pTenantId, pYearMonth, vStatusRelease, vSpaceValue;

    UPDATE tt_cb_check_balance_for_gl_journal_trx_details A
    SET gl_detail_curr_code = B.curr_code, gl_detail_amount = B.amount, journal_trx_id = B.journal_trx_id
    FROM vw_gl_journal_trx_details B
    INNER JOIN gl_journal_trx C ON B.journal_trx_id = C.journal_trx_id
    WHERE A.tenant_id = B.tenant_id
     AND C.doc_type_id = A.doc_type_id
     AND C.doc_id = A.doc_id
     AND C.doc_no = A.doc_no
     AND C.doc_date = A.doc_date
     AND A.cashbank_id = B.cashbank_id;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /