cb_submit_cb_out

-- Function: cb_submit_cb_out(bigint, character varying, character varying)

-- DROP FUNCTION cb_submit_cb_out(bigint, character varying, character varying);

CREATE OR REPLACE FUNCTION cb_submit_cb_out(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId              bigint;
    vCBOutId                bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);
    vEmptyId                bigint;
    vStatusRelease          character varying(1);
    vPaymentAmount          numeric;
    vRefAmount              numeric;
    vRoundingAmount         numeric;
    vPaymentId              bigint;
    vRefId                  bigint;
    vCbOutDocTypeId         bigint;
    vParentOuId             bigint;
    vJournalType            character varying(20);
    vStatusDraft            character varying(1);
    vRefDocTypeId           bigint;
    vPbCbDocTypeId          bigint;
    vRqCAdvDocTypeId        bigint;
    vPbApDocTypeId          bigint;
    vCAdvSettleDocTypeId    bigint;
    vPrepaymentDocTypeId    bigint;
    vSldPrepaymentDocTypeId bigint;
    vInvPrepaymentDocTypeId bigint;
    vSignDebit              character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(3);
    vActivityCOA            character varying(10);
    vSystemCOA              character varying(10);
    vCashBankCOA            character varying(10);
    vJournalTrxId           bigint;
    vCbOutDate              character varying(14);
    vCbOutNo                character varying(30);
    vEmptyValue             character varying(1);
    vFlagNo                 character varying(1);
    vRqCAdvId               bigint;

    vDocJournal             DOC_JOURNAL%ROWTYPE;
    vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
    result                  RECORD;
    vSldFakturPajakMasukan bigint;

    BEGIN
    vEmptyId    := -99;
    vStatusRelease := 'R';
    vParentOuId     := -99;
    vStatusDraft    := 'D';
    vPbCbDocTypeId  := 601;
    vRqCAdvDocTypeId := 602;
    vCAdvSettleDocTypeId := 603;
    vPbApDocTypeId := 231;
    vPrepaymentDocTypeId := 202;
    vSldPrepaymentDocTypeId := 212;
    vInvPrepaymentDocTypeId := 203;
    vSldFakturPajakMasukan := 223;
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vActivityCOA := 'ACTIVITY';
    vSystemCOA := 'SYSTEM';
    vCashBankCOA := 'CASHBANK';
    vEmptyValue := ' ';
    vFlagNo := 'N';

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'cb_submit_cb_out' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vCBOutId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'cashbankOutId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

-- get variable pendukung
-- vRefId adalah id cb_trx_cashbank_balance
/* NK, 27 Jan 2014 diganti dengan cara pakai user defined type untuk data ou dan data doc journal
    SELECT doc_type_id, ref_doc_type_id, ref_amount, ref_id, f_get_parent_ou_bu(A.tenant_id, A.ou_id), f_get_journal_type(A.doc_type_id),
                doc_no, doc_date INTO vCbOutDocTypeId, vRefDocTypeId, vRefAmount, vPaymentId, vParentOuId, vJournalType, vCbOutNo, vCbOutDate
    FROM cb_in_out_cashbank A
    WHERE in_out_cashbank_id = vCBOutId;
*/
    SELECT A.doc_type_id, A.ref_doc_type_id, A.ref_id, A.ref_amount, B.payment_id AS payment_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc,
                A.doc_no, A.doc_date
    FROM cb_in_out_cashbank A, cb_trx_cashbank_balance B
    WHERE A.in_out_cashbank_id = vCBOutId AND
        A.ref_id = B.trx_cashbank_balance_id INTO result;

    vCbOutDocTypeId := result.doc_type_id;
    vRefDocTypeId := result.ref_doc_type_id;
    vRefAmount := result.ref_amount;
    vPaymentId := result.payment_id;
    vRefId := result.ref_id;
    vOuStructure := result.ou;
    vDocJournal := result.doc;
    vCbOutNo := result.doc_no;
    vCbOutDate := result.doc_date;

-- update flag cb_trx_cashbank_balance
    UPDATE cb_trx_cashbank_balance SET flg_payment = 'Y', ref_doc_type_id = vCbOutDocTypeId, ref_id = vCBOutId,
                                        version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
    WHERE trx_cashbank_balance_id = vRefId;

-- hitung total nilai cash / bank payment
     SELECT COALESCE(SUM(payment_amount), 0) INTO vPaymentAmount
     FROM cb_in_out_cashbank_payment
     WHERE in_out_cashbank_id = vCBOutId;

     vRoundingAmount := vRefAmount - vPaymentAmount;

-- buat data  nilai rounding payment = ref_amount - vPaymentAmount
     INSERT INTO cb_in_out_cashbank_cost(
            tenant_id, in_out_cashbank_id, line_no,
            activity_gl_id, ou_rc_id, segment_id, curr_code, cost_amount,
            payment_amount, remark,
            version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT tenant_id, in_out_cashbank_id, 1,
            f_get_activity_gl_id_for_rounding(tenant_id), vEmptyId, vEmptyId, ref_curr_code, vRoundingAmount,
            vRoundingAmount, remark,
            0, vDatetime, vUserId, vDatetime, vUserId
    FROM cb_in_out_cashbank
    WHERE in_out_cashbank_id = vCBOutId;

-- update cb_in_out_cashbank : status doc to R
    UPDATE cb_in_out_cashbank SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
    WHERE in_out_cashbank_id = vCBOutId;

    /*
     * update payment order alloc balance untuk flg_alloc = C
     * buat data saldo prepayment, untuk cash bank out yang referensi dokumen PB AP
     */
    IF vRefDocTypeId = vPbApDocTypeId THEN

        UPDATE fi_payment_order_alloc_balance SET ref_alloc_id = vCBOutId, ref_doc_type_id = vCbOutDocTypeId, ref_doc_no = vCbOutNo, ref_doc_date = vCbOutDate,
                                                version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
        WHERE payment_order_id = vPaymentId AND
            flg_alloc = 'C';

        INSERT INTO fi_invoice_ap_balance
        (tenant_id, ou_id, doc_type_id, invoice_ap_id,
        doc_no, doc_date, ext_doc_no, ext_doc_date,
        ref_doc_type_id, ref_id, partner_id, due_date,
        curr_code, amount, remark, payment_amount, flg_payment,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT B.tenant_id, B.ou_id, vSldPrepaymentDocTypeId, B.invoice_ap_id,
            B.doc_no, vCBOutDate, B.ext_doc_no, B.ext_doc_date,
            B.doc_type_id, B.invoice_ap_balance_id, B.partner_id, vCbOutDate,
            A.credit_curr_code, A.credit_amount * -1, vEmptyValue, 0, vFlagNo,
            0, vDatetime, vUserId, vDatetime, vUserId
        FROM fi_payment_order_alloc_balance A, fi_invoice_ap_balance B
        WHERE A.payment_order_id = vPaymentId AND
            A.flg_alloc = 'C' AND
            A.credit_id = B.invoice_ap_balance_id AND
            A.credit_doc_type_id = B.doc_type_id AND
            B.doc_type_id = vPrepaymentDocTypeId;

        /*
         * NK, 2 Nov 2014
         * buat saldo invoice prepayment ap yang sudah dibayar cash bank out nya
         */
        INSERT INTO fi_invoice_advance_ap_balance
        (tenant_id, ou_id, doc_type_id, invoice_ap_id,
        doc_no, doc_date, ext_doc_no, ext_doc_date,
        ref_doc_type_id, ref_id, partner_id,
        curr_code, amount, payment_id, payment_date,
        purch_invoice_id, purch_invoice_date,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT  B.tenant_id, B.ou_id, vInvPrepaymentDocTypeId, B.invoice_ap_id,
                B.doc_no, B.doc_date, B.ext_doc_no, B.ext_doc_date,
                B.doc_type_id, B.invoice_ap_id, B.partner_id,
                A.credit_curr_code, A.credit_amount * -1, vCBOutId, vCBOutDate,
                vEmptyId, vEmptyValue,
                0, vDatetime, vUserId, vDatetime, vUserId
        FROM fi_payment_order_alloc_balance A, fi_invoice_ap_balance B
        WHERE A.payment_order_id = vPaymentId AND
            A.flg_alloc = 'C' AND
            A.credit_id = B.invoice_ap_balance_id AND
            A.credit_doc_type_id = B.doc_type_id AND
            B.doc_type_id = vInvPrepaymentDocTypeId;

        /*
         * NK, 18 Nov 2014
         * membuat data invoice tax advance ap balance
         */
        INSERT INTO fi_invoice_tax_advance_ap_balance
        (tenant_id, ou_id, doc_type_id, invoice_ap_id, partner_id,
        tax_id, tax_no, tax_date, curr_code, tax_amount,
        tax_curr_code, gov_tax_amount, ref_doc_type_id, ref_id,
        payment_id, payment_date, purch_invoice_id, purch_invoice_date,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT  B.tenant_id, B.ou_id, vSldFakturPajakMasukan, B.invoice_ap_id, B.partner_id,
            C.tax_id, C.tax_no, C.tax_date, B.curr_code, C.tax_amount * -1,
            C.tax_curr_code, C.gov_tax_amount * -1, B.doc_type_id, B.invoice_ap_balance_id,
            vCBOutId, vCBOutDate, vEmptyId, vEmptyValue,
            0, vDatetime, vUserId, vDatetime, vUserId
        -- Mod by WTC, 160916, join nya langsung ke table fi_invoice_ap_tax, krn ada kemungkinan tidak
        -- ada data fi_invoice_ap_tax_balance jika saldonya digabung
        FROM fi_payment_order_alloc_balance A, fi_invoice_ap_balance B, fi_invoice_ap_tax C
        WHERE A.payment_order_id = vPaymentId AND
            A.flg_alloc = 'C' AND
            A.credit_id = B.invoice_ap_balance_id AND
            B.doc_type_id = vInvPrepaymentDocTypeId AND
            A.credit_doc_type_id = B.doc_type_id AND
            B.invoice_ap_id = C.invoice_ap_id;

        /*
         * NK, 2 Nov 2014
         * membuat data pu_po_balance_advance_invoice, untuk invoice prepayment ap yang digunakan untuk di alokasi terhadap purchasing invoice
         */
        INSERT INTO pu_po_balance_advance_invoice
        (tenant_id, ou_id, partner_id,
        po_id, ref_doc_type_id, ref_id,
        tax_id, tax_percentage, tax_amount,
        advance_curr_code, advance_amount, flg_invoice, invoice_id,
        "version", create_datetime, create_user_id, update_datetime, update_user_id,
        ref_doc_no, ref_doc_date)
        SELECT A.tenant_id, A.ou_id, B.partner_id,
            B.ref_id, B.doc_type_id, B.invoice_ap_balance_id,
            COALESCE(C.tax_id, vEmptyId), COALESCE(D.percentage, vEmptyId), COALESCE(C.tax_amount, 0),
            A.credit_curr_code, A.credit_amount, 'N', vEmptyId,
            0, vDatetime, vUserId, vDatetime, vUserId,
            B.doc_no, B.doc_date
        FROM fi_payment_order_alloc_balance A, fi_invoice_ap_balance B
        LEFT OUTER JOIN fi_invoice_tax_ap_balance C ON B.invoice_ap_balance_id = C.invoice_ap_balance_id
        LEFT OUTER JOIN m_tax D ON C.tax_id = D.tax_id
        WHERE A.payment_order_id = vPaymentId AND
            A.flg_alloc = 'C' AND
            A.credit_id = B.invoice_ap_balance_id AND
            A.credit_doc_type_id = B.doc_type_id AND
            B.doc_type_id = vInvPrepaymentDocTypeId;

    END IF;


-- cash bank out yang referensi ke payment order cb, debet diambil dari detail payment order
-- cash bank out yang referensi ke req cash advance, debet diambil dari detail payment order
-- cash bank out yang referensi ke payment order ap, debet diambil dari detail in out cashbank untuk ref amount
-- Credit diambil dari cash / bank payment
    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
    FROM cb_in_out_cashbank A
    WHERE A.in_out_cashbank_id = vCBOutId;

    SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;

    INSERT INTO gl_journal_trx
    (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
    ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
    ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.doc_date, A.ref_curr_code, A.remark, vStatusDraft, 'DRAFT',
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM cb_in_out_cashbank A
    WHERE A.in_out_cashbank_id = vCBOutId;

    IF vRefDocTypeId = vPbCbDocTypeId THEN
        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
            A.doc_type_id, B.payment_order_cost_id,
            A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
            B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
            E.coa_id, B.curr_code, 0, vEmptyId,
            B.add_amount, A.doc_date, vTypeRate,
            1, 1, 'PAYMENT_ORDER_CB', B.remark
        FROM cb_payment_order A, cb_payment_order_cost B, m_activity_gl E
        WHERE A.payment_order_id = vPaymentId AND
            A.payment_order_id = B.payment_order_id AND
            B.activity_gl_id = E.activity_gl_id;
    END IF;

    IF vRefDocTypeId = vRqCAdvDocTypeId THEN
        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
            A.doc_type_id, B.payment_order_cost_id,
            A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
            B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
            E.coa_id, B.curr_code, 0, vEmptyId,
            B.add_amount, A.doc_date, vTypeRate,
            1, 1, 'CASH_ADVANCE', B.remark
        FROM cb_payment_order A, cb_payment_order_cost B, m_activity_gl E
        WHERE A.payment_order_id = vPaymentId AND
            A.payment_order_id = B.payment_order_id AND
            B.activity_gl_id = E.activity_gl_id;

        UPDATE cb_advance_balance
        SET flg_payment = 'Y', payment_doc_type_id = vCbOutDocTypeId, cash_bank_payment_id = vCBOutId, cash_bank_payment_date = vCbOutDate, ref_amount = vRefAmount
        WHERE advance_id = vPaymentId;
    END IF;

    IF vRefDocTypeId = vCAdvSettleDocTypeId THEN

        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
            A.doc_type_id, A.in_out_cashbank_id,
            A.partner_id, vEmptyId, vEmptyId, C.ou_rc_id,
            C.segment_id, vSignDebit, vActivityCOA, C.activity_gl_id,
            E.coa_id, A.ref_curr_code, 0, vEmptyId,
            A.ref_amount, B.cash_bank_payment_date, vTypeRate,
            1, 1, 'CASH_ADVANCE', A.remark
        FROM cb_in_out_cashbank A, cb_advance_balance B, cb_payment_order_cost C, m_activity_gl E
        WHERE A.in_out_cashbank_id = vCBOutId AND
            A.ref_id = B.ref_id AND
            B.advance_id = C.payment_order_id AND
            C.activity_gl_id = E.activity_gl_id;

    END IF;

    IF vRefDocTypeId = vPbApDocTypeId THEN
        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
            B.credit_doc_type_id, B.credit_id,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
            f_get_ap_coa_partner(A.tenant_id, A.partner_id), B.credit_curr_code, 0, vEmptyId,
            B.credit_amount, B.credit_doc_date, vTypeRate,
            1, 1, 'AP', A.remark
        FROM cb_in_out_cashbank A, fi_payment_order_alloc_balance B, cb_trx_cashbank_balance C
        WHERE A.in_out_cashbank_id = vCBOutId AND
            A.ref_id = C.trx_cashbank_balance_id AND
            C.payment_id = B.payment_order_id AND
                B.flg_alloc = 'C';
    END IF;

    IF (vRoundingAmount < 0) THEN
        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
            A.doc_type_id, B.in_out_cashbank_cost_id,
            A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
            B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
            E.coa_id, B.curr_code, 0, vEmptyId,
            B.cost_amount * -1, A.doc_date, vTypeRate,
            1, 1, 'ROUNDING', B.remark
        FROM cb_in_out_cashbank A, cb_in_out_cashbank_cost B, m_activity_gl E
        WHERE A.in_out_cashbank_id = vCBOutId AND
            A.in_out_cashbank_id = B.in_out_cashbank_id AND
            B.activity_gl_id = E.activity_gl_id;
    ELSE
        IF (vRoundingAmount > 0) THEN
            INSERT INTO tt_journal_trx_item
            (session_id, tenant_id, journal_trx_id, line_no,
            ref_doc_type_id, ref_id,
            partner_id, product_id, cashbank_id, ou_rc_id,
            segmen_id, sign_journal, flg_source_coa, activity_gl_id,
            coa_id, curr_code, qty, uom_id,
            amount, journal_date, type_rate,
            numerator_rate, denominator_rate, journal_desc, remark)
            SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
                A.doc_type_id, B.in_out_cashbank_cost_id,
                A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
                B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
                E.coa_id, B.curr_code, 0, vEmptyId,
                B.cost_amount, A.doc_date, vTypeRate,
                1, 1, 'ROUNDING', B.remark
            FROM cb_in_out_cashbank A, cb_in_out_cashbank_cost B, m_activity_gl E
            WHERE A.in_out_cashbank_id = vCBOutId AND
                A.in_out_cashbank_id = B.in_out_cashbank_id AND
                B.activity_gl_id = E.activity_gl_id;
        END IF;
    END IF;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, A.in_out_cashbank_id,
        A.partner_id, vEmptyId, B.cashbank_id, vEmptyId,
        vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
        C.coa_id, B.curr_code, 0, vEmptyId,
        B.cashbank_amount, A.doc_date, vTypeRate,
        1, 1, 'CASH_BANK', A.remark
    FROM cb_in_out_cashbank A, cb_in_out_cashbank_payment B, m_cashbank C
    WHERE A.in_out_cashbank_id = vCBOutId
    AND   A.in_out_cashbank_id = B.in_out_cashbank_id
    AND B.cashbank_id = C.cashbank_id;

    INSERT INTO gl_journal_trx_item
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId
    AND A.journal_desc IN ('PAYMENT_ORDER_CB', 'CASH_ADVANCE', 'AP', 'ROUNDING');

    INSERT INTO gl_journal_trx_mapping
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId
    AND A.journal_desc = 'CASH_BANK';

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION cb_submit_cb_out(bigint, character varying, character varying)
  OWNER TO sts;