f_regenerate_journal_trx_void_pos_shop

CREATE OR REPLACE FUNCTION f_regenerate_journal_trx_void_pos_shop(
    character varying,
    bigint,
    bigint,
    bigint,
    character varying,
    bigint,
    character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pOuId               ALIAS FOR $3;
    pTrxPosId           ALIAS FOR $4;
    pProcessNo          ALIAS FOR $5;
    pUserId             ALIAS FOR $6;
    pDatetime           ALIAS FOR $7;

    vFlagNo             character varying(1);
    vProductStatus      character varying(5);
    vEmptyId            bigint;
    vDocTypeAdjStock    bigint;
    vFlagCash           character varying(1);
    vDebitType          character varying(1);
    vFlagYes            character varying(1);
    vEmptyValue         character varying(1);
    vJournalType        character varying(20);
    vJournalTypeDoPos   character varying(20);
    vJournalTypeAdjStock character varying(20);
    vSignDebit          character varying(1);
    vSignCredit         character varying(1);
    vTypeRate           character varying(3);
    vProductCOA         character varying(10);
    vSystemCOA          character varying(10);
    vTaxCOA             character varying(10);
    vCashBankCOA        character varying(10);
    vDocTypeDoPos       bigint;
    vRounding           integer;
    vParentOuId         bigint;
    vActivityCOA        character varying(10);
    vStatusPos          character varying(1);
    vStatusDraft        character varying(1);
    vActivityChargesId  bigint;
    vDocJournalDoPos    DOC_JOURNAL%ROWTYPE;
    vDocJournalAdjStock DOC_JOURNAL%ROWTYPE;
    vOuStructure        OU_BU_STRUCTURE%ROWTYPE;
    result              RECORD;
    vLedgerCode         character varying(10);

    vPosDocTypeId           bigint;
    vGroupProductNonJasaId  bigint;
    vGroupProductJasaId     bigint;
    vRoundingModeNonTax     character varying(5);

    vFlgPkp                 character varying;
    vCountBelumDiBayar      numeric;
    vDefaultRoleId          bigint;
    vFlowArDebtNoteId       bigint;
    vDebtNoteARDocScheme    character varying(10);
    vStateDraft             character varying(10);
    vDateVoid               character varying(8);
    vFlagRole               character varying(1);
    vDocTypeDebtNoteAr      bigint;
    vCount                  character varying;
    vTotalTaxItem           numeric;
    vTotalTaxItemNempil     numeric;
    vJournalTrxId           bigint;
    vRemarkJournalTrx       text := '';
    vProcessId              bigint;
    pOutletId               bigint;

BEGIN

    vDocTypeDebtNoteAr := 241;
    vFlagRole := 'R';
    vStateDraft := 'DRAFT';
    vDebtNoteARDocScheme := 'DF01';
    vCountBelumDiBayar := 0;
    vTotalTaxItem := 0;
    vTotalTaxItemNempil := 0;
    vFlgPkp := 'N';
    vStatusPos := 'S';
    vFlagNo := 'N';
    vProductStatus := 'GOOD';
    vEmptyId := -99;
    vDocTypeAdjStock := 413;
    vFlagCash := 'C';
    vDebitType := 'D';
    vFlagYes := 'Y';
    vEmptyValue := ' ';
    vJournalType := 'POS.SHOP';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vProductCOA := 'PRODUCT';
    vSystemCOA := 'SYSTEM';
    vTaxCOA := 'TAX';
    vCashBankCOA := 'CASHBANK';
    vParentOuId := -99;
    vActivityCOA := 'ACTIVITY'; -- DanieL : tambahan sementara agar tidak error
    --vActivityChargesId := 2;
    vActivityChargesId := f_get_value_system_config_by_param_code(pTenantId, 'CHARGES');
    vPosDocTypeId := 401;

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

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

    SELECT group_product_id INTO vGroupProductNonJasaId
    FROM m_group_product A WHERE A.group_product_code = 'FG';

    SELECT group_product_id INTO vGroupProductJasaId
    FROM m_group_product A WHERE A.group_product_code = 'SERVICE';

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

    /*
     * rounding nilai tax
     */
    vRounding := 0;

    -- get flag_pkp
    SELECT C.flg_pkp INTO vFlgPkp
    FROM m_ou_structure A
    INNER JOIN t_ou B ON A.ou_bu_id = B.ou_id
    INNER JOIN t_ou_legal C ON B.ou_id = C.ou_id AND B.tenant_id = C.tenant_id
    WHERE A.ou_id = pOuId AND B.tenant_id = pTenantId;

    -- cek LUNAS , jika count = 0 berarti LUNAS
    -- lUNAS = guest atau corporate bayar lunas
    -- get Lunas
    SELECT count(1) INTO vCountBelumDiBayar
    FROM i_trx_pos A
    INNER JOIN i_trx_pos_termin_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 t_ou C ON A.ou_id = C.ou_id AND A.tenant_id = C.tenant_id
    WHERE A.tenant_id = pTenantId
        AND A.process_no = pProcessNo
        AND A.ou_id = pOuId
        AND B.under_payment_amount > 0;

    -- get void date from i_trx_log_voided_pos_custom
    SELECT A.doc_date INTO vDateVoid
    FROM i_trx_log_voided_pos_custom A
        WHERE A.tenant_id = pTenantId
        AND A.process_no = pProcessNo
        AND A.ou_id = pOuId;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    -- Cek ada journal nya atau tidak
    IF EXISTS (
        SELECT 1 FROM i_trx_pos A
        INNER JOIN gl_journal_trx B ON A.tenant_id = B.tenant_id
                                    AND A.doc_type_id = B.doc_type_id
                                    AND CONCAT(A.doc_no, '_VOID') = B.doc_no
                                    AND B.journal_type = vJournalType
        WHERE A.tenant_id = pTenantId AND A.trx_pos_id = pTrxPosId AND A.process_no = pProcessNo) THEN

        -- Get Journal trx id & remark journal
        SELECT A.journal_trx_id, A.remark INTO vJournalTrxId, vRemarkJournalTrx
        FROM gl_journal_trx A
        INNER JOIN i_trx_pos B ON A.tenant_id = B.tenant_id
                               AND A.doc_type_id = B.doc_type_id
                               AND A.doc_no = CONCAT(B.doc_no, '_VOID')
        WHERE A.journal_type = vJournalType
        AND B.tenant_id = pTenantId
        AND B.trx_pos_id = pTrxPosId
        AND B.process_no = pProcessNo;

        -- Hapus journal item dan journal mapping
        DELETE FROM gl_journal_trx_item WHERE journal_trx_id = vJournalTrxId;
        DELETE FROM gl_journal_trx_mapping WHERE journal_trx_id = vJournalTrxId;
        DELETE FROM gl_journal_trx_fx WHERE journal_trx_id = vJournalTrxId;

        --==== Insert ulang journal item dan journal mapping ====--

        --jurnal untuk item POS SHOP non Jasa
            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, vEmptyId,
                    A.partner_id, B.product_id, vEmptyId, vEmptyId,
                    vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
                    f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), B.curr_code, SUM(B.qty), B.base_uom_id,
                    SUM(B.nett_amount_item), vDateVoid, vTypeRate,
                    1, 1, 'SALES_POS', vEmptyValue
            FROM i_trx_pos A, i_trx_pos_item B, m_product D, m_ctgr_product E
            WHERE A.tenant_id = pTenantId AND
                    A.ou_id = pOuId AND
                    A.process_no = pProcessNo AND
                    --A.status = vStatusPos AND -- tidak perlu memperhatikan status doc POS
                    A.trx_pos_id = B.trx_pos_id AND
                    A.process_no = B.process_no AND
                    A.tenant_id = B.tenant_id AND
            B.product_id = D.product_id AND
                    A.tenant_id = D.tenant_id AND
                    D.ctgr_product_id = E.ctgr_product_id AND
                    E.group_product_id = vGroupProductNonJasaId
            GROUP BY A.tenant_id, A.doc_type_id,
                    A.partner_id, B.product_id, B.curr_code, B.base_uom_id;

        --jurnal untuk item POS SHOP Jasa
        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, vEmptyId,
            A.partner_id, B.product_id, vEmptyId, vEmptyId,
            vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
            f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualanJasa'), B.curr_code, SUM(B.qty), B.base_uom_id,
                    SUM(B.nett_amount_item), vDateVoid, vTypeRate,
            1, 1, 'SALES_POS', vEmptyValue
        FROM i_trx_pos A, i_trx_pos_item B, m_product D, m_ctgr_product E
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.process_no = pProcessNo AND
            --A.status = vStatusPos AND -- tidak perlu memperhatikan status doc POS
            A.trx_pos_id = B.trx_pos_id AND
            A.process_no = B.process_no AND
            A.tenant_id = B.tenant_id AND
            B.product_id = D.product_id AND
            A.tenant_id = D.tenant_id AND
            D.ctgr_product_id = E.ctgr_product_id AND
            E.group_product_id = vGroupProductJasaId
        GROUP BY A.tenant_id, A.doc_type_id,
            A.partner_id, B.product_id, B.curr_code, B.base_uom_id;

        -- jurnal penjualan dari produk nempil, 6 Nov 2015
        WITH data_tax_pos AS(
            SELECT A.trx_pos_id, A.process_no, A.tenant_id,
                A.doc_type_id, SUM(B.tax_amount) AS tax_amount
            FROM i_trx_pos A, i_trx_pos_item B
            WHERE A.tenant_id = pTenantId AND
                A.ou_id = pOuId AND
                A.process_no = pProcessNo AND
                --A.status = vStatusPos AND
                A.trx_pos_id = B.trx_pos_id AND
                A.process_no = B.process_no AND
                A.tenant_id = B.tenant_id
            GROUP BY A.trx_pos_id, A.tenant_id, A.process_no, A.doc_type_id
        ),
        data_tax_total AS(
            SELECT A.trx_pos_id, A.process_no, A.tenant_id,
                A.doc_type_id, SUM(B.tax_amount) AS tax_amount
            FROM i_trx_pos A, i_trx_pos_tax B
            WHERE A.tenant_id = pTenantId AND
                A.ou_id = pOuId AND
                A.process_no = pProcessNo AND
                --A.status = vStatusPos AND
                A.trx_pos_id = B.trx_pos_id AND
                A.process_no = B.process_no AND
                A.tenant_id = B.tenant_id
            GROUP BY A.trx_pos_id, A.tenant_id, A.process_no, A.doc_type_id
        ),
        data_tax_nempil AS(
            SELECT A.trx_pos_id, A.process_no, A.tenant_id,
                A.doc_type_id, A.tax_amount - COALESCE(B.tax_amount,0) AS tax_amount
            FROM data_tax_total A
            LEFT JOIN data_tax_pos B ON A.trx_pos_id = B.trx_pos_id AND
                                           A.tenant_id = B.tenant_id AND
                                           A.process_no = B.process_no AND
                                           A.doc_type_id = B.doc_type_id
        )
        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, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
            f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualanBarangBengkel'), B.curr_code, SUM(B.qty), vEmptyId,
            CASE WHEN vFlgPkp = vFlagYes THEN
            SUM(B.gross_sell_price) - COALESCE(D.tax_amount, 0) ELSE -- gross sell price = sell price * qty
            SUM(B.gross_sell_price)
            END,
            vDateVoid, vTypeRate,
            1, 1, 'SALES_POS', vEmptyValue
        FROM i_trx_pos A
        INNER JOIN i_trx_pos_item_nempil_barang B ON A.trx_pos_id = B.trx_pos_id AND
                                                     A.process_no = B.process_no AND
                                                     A.tenant_id = B.tenant_id
        LEFT JOIN data_tax_nempil D ON A.trx_pos_id = D.trx_pos_id AND
                                       A.tenant_id = D.tenant_id AND
                                       A.doc_type_id = D.doc_type_id AND A.process_no = D.process_no
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.process_no = pProcessNo
            -- AND A.status = vStatusPos -- tidak perlu memperhatikan status doc POS
        GROUP BY A.tenant_id, A.doc_type_id,
            A.partner_id, B.curr_code, D.tax_amount;

            --jurnal untuk biaya kartu kredit
        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.trx_pos_add_cost_id,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
            f_get_system_coa_by_group_coa(A.tenant_id, 'BiayaKartuKredit'), B.curr_code, 0, vEmptyId,
            B.add_amount, vDateVoid, vTypeRate,
            1, 1, 'CHARGES_POS', B.remark
        FROM i_trx_pos A, i_trx_pos_add_cost B
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.process_no = pProcessNo AND
            --A.status = vStatusPos AND
            A.trx_pos_id = B.trx_pos_id AND
            A.process_no = B.process_no AND
            A.tenant_id = B.tenant_id AND
            B.activity_gl_id = vActivityChargesId;

        -- add if By: Ping An , 27 Nov 2015
        -- Jurnal VAT Hanya untuk yang PKP saja , kalo non PKP gak perlu dijurnal PPN karena non PKP tidak memungut dan melaporkan PPN
        IF vFlgPkp = vFlagYes 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, vEmptyId,
                A.partner_id, vEmptyId, vEmptyId, vEmptyId,
                vEmptyId, vSignDebit, vTaxCOA, vEmptyId,
                D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
                B.tax_amount , vDateVoid, vTypeRate,
                1, 1, 'VAT_OUT_POS', vEmptyValue
            FROM i_trx_pos A, i_trx_pos_tax B, m_tax D
            WHERE A.tenant_id = pTenantId AND
                A.ou_id = pOuId AND
                A.process_no = pProcessNo AND
                --A.status = vStatusPos AND
                A.trx_pos_id = B.trx_pos_id AND
                A.process_no = B.process_no AND
                A.tenant_id = B.tenant_id AND
                B.tax_id = D.tax_id;


        END IF;

            --jurnal balik untuk rounding amount
        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, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
            f_get_system_coa_by_group_coa(A.tenant_id, 'BiayaPembulatanNilai'), A.curr_code, 0, vEmptyId,
            A.rounding_amount, vDateVoid, vTypeRate,
            1, 1, 'ROUNDING_POS', vRemarkJournalTrx
        FROM i_trx_pos A
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.process_no = pProcessNo;

            --jurnal balik untuk refund, jika ada
        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, vEmptyId,
            A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
            vEmptyId, vSignDebit, vCashBankCOA, vEmptyId,
            E.coa_id, A.curr_code, 0, vEmptyId,
            A.total_refund, vDateVoid, vTypeRate,
            1, 1, 'REFUND_POS', A.remark
        FROM i_trx_pos A, i_cash_bank_data_outlet D, m_cashbank E
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.process_no = pProcessNo AND
            --A.status = vStatusPos AND
            D.outlet_id = pOutletId AND
            D.cashbank_id = E.cashbank_id AND
            A.curr_code = E.curr_code AND
            E.flg_cash_bank = vFlagCash AND
            A.total_refund > 0;

            --jurnal untuk cash payment
        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.trx_pos_cash_payment_id,
            A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
            vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
            E.coa_id, B.curr_payment_code, 0, vEmptyId,
            B.payment_amount, vDateVoid, vTypeRate,
            1, 1, 'CASH_PAYMENT_POS', B.remark
        FROM i_trx_pos A, i_trx_pos_cash_payment B, i_cash_bank_data_outlet D, m_cashbank E
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.process_no = pProcessNo AND
            --A.status = vStatusPos AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            D.outlet_id = pOutletId AND
            D.cashbank_id = E.cashbank_id AND
            B.curr_payment_code = E.curr_code AND
            E.flg_cash_bank = vFlagCash AND
            B.payment_amount > 0; -- add by: Ping An 30 Nov 2015, ditambah filter kalau tidak ada pembayaran tidak perlu dijurnal

        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.trx_pos_non_cash_payment_id,
            A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
            vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
            E.settle_coa_id, B.curr_payment_code, 0, vEmptyId,
            B.payment_amount, vDateVoid, vTypeRate,
            1, 1, 'NON_CASH_PAYMENT_POS', B.remark
        FROM i_trx_pos A, i_trx_pos_non_cash_payment B, i_device_merchant_data_outlet D, m_device_merchant E
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.process_no = pProcessNo AND
            --A.status = vStatusPos AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            D.outlet_id = pOutletId AND
            B.device_merchant_id = D.device_merchant_id AND
            D.device_merchant_id = E.device_merchant_id AND
            B.payment_amount > 0; -- add by: Ping An 30 Nov 2015, ditambah filter kalau tidak ada pembayaran tidak perlu dijurnal

        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, pDatetime, pUserId, pDatetime, pUserId
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            A.journal_desc IN ('SALES_POS','CHARGES_POS','VAT_OUT_POS', 'ROUNDING_POS', 'REFUND_POS');

        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, pDatetime, pUserId, pDatetime, pUserId
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
             -- rev by Ping An 18nov2015 A.journal_desc IN ('CASH_PAYMENT_POS','NON_CASH_PAYMENT_POS');
             A.journal_desc IN ('CASH_PAYMENT_POS','NON_CASH_PAYMENT_POS');

        -- update status gl_journal_trx
        UPDATE gl_journal_trx
        SET status_doc = 'D',
            workflow_status = 'DRAFT',
            update_user_id = pUserId,
            update_datetime = pDatetime
        WHERE journal_trx_id = vJournalTrxId;


    END IF;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

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