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;
  /