f_generate_return_purchase_invoice_by_nrb

CREATE OR REPLACE FUNCTION f_generate_return_purchase_invoice_by_nrb(character varying, bigint, bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId					ALIAS FOR $1;
    pTenantId					ALIAS FOR $2;
    pUserId						ALIAS FOR $3;
    pDatetime					ALIAS FOR $4;
    pRemark						ALIAS FOR $5;

    vFlagYes				character varying:='Y';
    vFlagNo					character varying:='N';
    vEmptyString			character varying:='';
    vNullId					bigint:=-99;
    vDocTypeIdRetur			bigint:=502;
    vDocTypeIdDraftRetur	bigint:=598;
    vDocTypeIdPo			bigint:=101;
    vDocTypeIdRg			bigint:=111;
    vDocTypeIdCn			bigint:=511;
    vDocTypeIdRpi			bigint:=141;
    vDocTypeIdPi			bigint:=131;
    vDefaultOuId			bigint:=10;
    vNotaReturPajakMasukan		bigint:= 222;

    vDefaultWarehouseId    	bigint;
    vYearMonth				character varying;
    vStatusDocApproved		character varying:='R';
    vStatusDocDraft			character varying:='D';
    vWorkflowStatusApproved		character varying:='APPROVED';
    vAutonumDatatypeRpi		character varying:='return_purchase_invoice_by_nrb';
    vProductStatusReturn	character varying:='RETURN';
    vProductStatusDraftRetur	character varying:='DRAFTRETUR';
    vCurrCodeIdr			character varying:='IDR';
    vZeroValue		    	bigint:=0;
    vUomId		    		bigint:=10;

    vRoundingModeNonTax		character varying(5);

    vJournalType			character varying;
    vLedgerCode				character varying;
    vOuBuId					bigint;
    vOuBranchId				bigint;
    vOuSubBuId				bigint;
    vTypeRate				character varying(3):='COM';

    vSignDebit				character varying:='D';
    vSignCredit				character varying:='C';
    vProductCOA				character varying:='PRODUCT';
    vSystemCOA				character varying:='SYSTEM';
    vTaxCOA					character varying:='TAX';

BEGIN

	SELECT LEFT(pDatetime,6) INTO vYearMonth;
	vDefaultWarehouseId := f_get_value_system_config_by_param_code(pTenantId, 'default.warehouse.for.retur')::bigint;
	SELECT journal_type, ledger_code INTO vJournalType, vLedgerCode FROM m_document_journal  WHERE doc_type_id  = vDocTypeIdPi;
	SELECT ou_bu_id, ou_branch_id, ou_sub_bu_id INTO vOuBuId, vOuBranchId, vOuSubBuId FROM m_ou_structure WHERE ou_id = vDefaultOuId;
	SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

	-- PROSES ADD RPI --

	-- 1 CN = 1 RPI
	INSERT INTO m_nrb_generated_document(
				retur_header_id, retur_no, ref_doc_type_id, ref_id, journal_trx_id,
				create_datetime, create_user_id, update_datetime, update_user_id, version )
	SELECT A.retur_header_id, A.retur_no, vDocTypeIdRpi, B.rpi_id, B.journal_id,
			pDatetime, pUserId, pDatetime, pUserId, vZeroValue
	FROM m_nrb_generated_document A
	JOIN (
		--	JOIN DENGAN DIRI SENDIRI YANG SUDAH DI GROUP BY PER CN ID, KARENA n NRB (RETUR_HEADER_ID) BISA MEMILIKI SAMA CN ID
		SELECT ref_id, journal_trx_id, ref_doc_type_id, nextval('pu_invoice_seq') AS rpi_id, nextval('gl_journal_trx_seq') journal_id, create_datetime
		FROM m_nrb_generated_document
		WHERE ref_doc_type_id  = vDocTypeIdCn
		AND create_datetime = pDatetime
		GROUP BY ref_id, ref_doc_type_id, journal_trx_id, create_datetime
	) B ON B.ref_id = A.ref_id AND B.ref_doc_type_id = A.ref_doc_type_id AND B.journal_trx_id = A.journal_trx_id
	WHERE A.ref_doc_type_id = B.ref_doc_type_id
	AND A.create_datetime = B.create_datetime;

	INSERT INTO pu_invoice(
            invoice_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id,
            ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id,
            ou_legal_id, due_date, purchaser_id, curr_code, gross_amount,
            advance_amount, tax_amount, add_amount, total_amount, status_doc,
            workflow_status, version, create_datetime, create_user_id, update_datetime,
            update_user_id)
    SELECT A.ref_id, pTenantId, A.ref_doc_type_id, f_gen_autonum('RPIG/'||vYearMonth||'/KPS/', vAutonumDatatypeRpi), C.doc_date, vDefaultOuId,
			D.doc_no, C.doc_date, D.doc_type_id, D.po_id,  vEmptyString, D.partner_id,
			vDefaultOuId, C.doc_date, D.purchaser_id, D.curr_code, vZeroValue,
			vZeroValue, vZeroValue, vZeroValue, vZeroValue, vStatusDocApproved,
			vWorkflowStatusApproved, vZeroValue, pDatetime, pUserId, pDatetime, pUserId
	FROM m_nrb_generated_document A
	JOIN m_nrb_generated_document B ON A.retur_header_id = B.retur_header_id AND B.ref_doc_type_id = vDocTypeIdCn
	JOIN in_inventory C ON B.ref_id = C.inventory_id AND B.ref_doc_type_id = C.doc_type_id
	JOIN pu_po D ON C.ref_id = D.po_id AND C.ref_doc_type_id = D.doc_type_id
	WHERE A.ref_doc_type_id = vDocTypeIdRpi
	AND A.create_datetime = pDatetime
	GROUP BY A.ref_id, A.ref_doc_type_id, C.doc_date,
		D.doc_no, C.doc_date, D.doc_type_id, D.po_id, D.partner_id,
		C.doc_date, D.purchaser_id, D.curr_code;

	INSERT INTO pu_invoice_item(
            tenant_id, invoice_id, line_no, ref_doc_type_id,
            ref_id, ref_item_id, ref_item_amount, remark, version, create_datetime,
            create_user_id, update_datetime, update_user_id, ref_price_po,
            original_ref_price_po, original_ref_item_amount)
	SELECT pTenantId, A.ref_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id), B.ref_doc_type_id,
		B.ref_id, C.ref_item_id, C.item_amount, vEmptyString ,vZeroValue, pDatetime,
		pUserId, pDatetime, pUserId, vZeroValue, vZeroValue, vZeroValue
	FROM m_nrb_generated_document A
	JOIN m_nrb_generated_document B ON A.retur_header_id = B.retur_header_id AND B.ref_doc_type_id = vDocTypeIdCn
	JOIN pu_po_balance_invoice C ON B.ref_id = C.ref_id AND B.ref_doc_type_id = C.ref_doc_type_id
	WHERE A.create_datetime = pDatetime
	AND A.ref_doc_type_id = vDocTypeIdRpi
	GROUP BY A.ref_id, B.ref_doc_type_id, B.ref_id, C.ref_item_id, C.item_amount, A.journal_trx_id;

	INSERT INTO pu_invoice_tax(
            tenant_id, invoice_id, tax_id, flg_amount, tax_percentage,
            base_amount, tax_amount, tax_no, tax_date, tax_curr_code, gov_tax_amount,
            remark, version, create_datetime, create_user_id, update_datetime,
            update_user_id, advance_amount, original_base_amount, original_tax_amount)
	SELECT pTenantId, B.invoice_id, C.tax_id, C.flg_amount, C.tax_percentage,
		SUM(C.base_amount), SUM(C.tax_amount), E.tax_no, E.tax_date, E.tax_curr_code, SUM(C.tax_amount),
		E.remark, vZeroValue , pDatetime, pUserId, pDatetime, pUserId, vZeroValue, vZeroValue, vZeroValue
	FROM (
		SELECT A.ref_id AS invoice_id, B.ref_id AS cn_id, B.ref_doc_type_id, A.create_datetime
		FROM m_nrb_generated_document A
		JOIN m_nrb_generated_document B ON A.retur_header_id = B.retur_header_id AND B.ref_doc_type_id = vDocTypeIdCn
		WHERE A.ref_doc_type_id = vDocTypeIdRpi
		AND A.create_datetime = pDatetime
		GROUP BY A.ref_id, B.ref_id, B.ref_doc_type_id, A.create_datetime
		ORDER BY A.ref_id, B.ref_id, B.ref_doc_type_id
	) B
	JOIN pu_po_balance_invoice_tax C ON B.cn_id = C.ref_id AND B.ref_doc_type_id = C.ref_doc_type_id
	JOIN pu_invoice D ON D.doc_type_id = vDocTypeIdRpi AND C.po_id = D.ref_id AND D.ref_doc_type_id = vDocTypeIdPo AND D.create_datetime = B.create_datetime
	JOIN pu_invoice_tax E ON D.invoice_id = E.invoice_id
	GROUP BY B.invoice_id, C.tax_id, C.flg_amount, C.tax_percentage, E.tax_no, E.tax_date,E.tax_curr_code, E.remark;

	UPDATE pu_invoice C
	SET gross_amount = A.ref_item_amount + COALESCE(B.tax_amount,0),
		tax_amount = COALESCE(B.tax_amount,0),
		total_amount = A.ref_item_amount
	FROM (
		SELECT A.invoice_id, SUM(B.ref_item_amount) AS ref_item_amount
		FROM pu_invoice A
		JOIN pu_invoice_item B  ON A.invoice_id = B.invoice_id
		WHERE EXISTS (
			SELECT C.ref_id
			FROM m_nrb_generated_document C
			WHERE A.invoice_id = C.ref_id
			AND C.ref_doc_type_id = vDocTypeIdRpi
			AND C.create_datetime = pDatetime
			GROUP BY C.ref_id
		)
		GROUP BY A.invoice_id
	) A
	LEFT JOIN pu_invoice_tax B ON A.invoice_id = B.invoice_id
	WHERE A.invoice_id = C.invoice_id;

	UPDATE pu_po_balance_invoice_ext_invoice_doc_type D
	SET invoice_id = A.ref_id,
		invoice_doc_type_id = A.ref_doc_type_id,
		version = D.version+1
	FROM m_nrb_generated_document A
	JOIN m_nrb_generated_document B ON A.retur_header_id = B.retur_header_id AND B.ref_doc_type_id = vDocTypeIdCn
	JOIN pu_invoice C ON A.ref_id = C.invoice_id AND A.ref_doc_type_id = C.doc_type_id
	WHERE A.create_datetime = pDatetime
	AND A.ref_doc_type_id = vDocTypeIdRpi
	AND C.ref_id = D.po_id
	AND B.ref_doc_type_id = D.ref_doc_type_id
	AND B.ref_id = D.ref_id;

	UPDATE pu_po_balance_invoice_tax_ext D
	SET invoice_id = A.ref_id,
		invoice_doc_type_id = vDocTypeIdPi,
		version = D.version+1
	FROM m_nrb_generated_document A
	JOIN m_nrb_generated_document B ON A.retur_header_id = B.retur_header_id AND B.ref_doc_type_id = vDocTypeIdCn
	JOIN pu_invoice C ON A.ref_id = C.invoice_id AND A.ref_doc_type_id = C.doc_type_id
	WHERE A.create_datetime = pDatetime
	AND A.ref_doc_type_id = vDocTypeIdRpi
	AND C.ref_id = D.po_id
	AND B.ref_doc_type_id = D.ref_doc_type_id
	AND B.ref_id = D.ref_id;

	WITH rpi AS (
		SELECT ref_doc_type_id, ref_id
		FROM m_nrb_generated_document
		WHERE ref_doc_type_id = vDocTypeIdRpi
		AND create_datetime = pDatetime
		GROUP BY ref_doc_type_id, ref_id
	), item_rpi AS (
		SELECT A.invoice_id, A.ref_id, A.ref_doc_type_id, A.ref_item_id
		FROM pu_invoice_item A
		JOIN rpi B ON A.invoice_id = B.ref_id
	)
	UPDATE pu_po_balance_invoice A
	SET flg_invoice = vFlagYes,
		invoice_id = B.invoice_id
	FROM item_rpi B
	WHERE A.ref_id = B.ref_id
	AND A.ref_doc_type_id = B.ref_doc_type_id
	AND A.ref_item_id =  B.ref_item_id;

	-- PROSES SUBMIT RPI--
	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 	A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
			A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
			A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
			A.curr_code, A.total_amount * -1, A.remark, 0, vFlagNo,
			0, pDatetime, pUserId, pDatetime, pUserId
	FROM pu_invoice A
	JOIN m_nrb_generated_document B ON A.invoice_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id
	WHERE B.create_datetime = pDatetime
	AND B.ref_doc_type_id = vDocTypeIdRpi
	GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
		A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
		A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
		A.curr_code, A.total_amount, A.remark;

	INSERT INTO fi_invoice_tax_ap_balance
		(tenant_id, ou_id, doc_type_id, invoice_ap_balance_id,
	  	partner_id, tax_id, tax_no, tax_date,
	  	curr_code, tax_amount, tax_curr_code, gov_tax_amount,
	  	due_date, remark, payment_amount, flg_payment,
	  	"version", create_datetime, create_user_id, update_datetime, update_user_id,
	  	gov_base_amount)
	SELECT A.tenant_id, A.ou_id, vNotaReturPajakMasukan, C.invoice_ap_balance_id,
		A.partner_id, B.tax_id, B.tax_no, B.tax_date,
		A.curr_code, B.tax_amount * -1, B.tax_curr_code, B.gov_tax_amount * -1,
		A.due_date, B.remark, 0, vFlagNo,
		0, pDatetime, pUserId, pDatetime, pUserId,
		f_get_amount_in_gov_tax_rate(A.tenant_id, B.base_amount - B.advance_amount, B.tax_date, A.curr_code) * -1
	FROM pu_invoice A, pu_invoice_tax B, fi_invoice_ap_balance C, m_nrb_generated_document D
	WHERE A.invoice_id = D.ref_id
		AND D.create_datetime = pDatetime
		AND D.ref_doc_type_id = vDocTypeIdRpi
		AND A.invoice_id = B.invoice_id
		AND A.tenant_id = C.tenant_id
		AND A.ou_id = C.ou_id
		AND A.partner_id = C.partner_id
		AND A.doc_type_id = C.doc_type_id
		AND A.invoice_id = C.invoice_ap_id
	GROUP BY  A.tenant_id, A.ou_id, C.invoice_ap_balance_id, A.partner_id, B.tax_id, B.tax_no, B.tax_date,
		A.curr_code, B.tax_amount, B.tax_curr_code, B.gov_tax_amount,
		A.due_date, B.remark, B.base_amount, B.advance_amount;


	PERFORM fi_insert_vat_in_for_reporting(pTenantId, pSessionId, A.invoice_ap_balance_id, pUserId, pDatetime)
	FROM fi_invoice_ap_balance A
	JOIN m_nrb_generated_document B ON A.invoice_ap_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id
	WHERE B.create_datetime = pDatetime
	AND B.ref_doc_type_id = vDocTypeIdRpi
	GROUP BY A.invoice_ap_balance_id;

	PERFORM fi_insert_return_vat_in_for_efaktur(pTenantId, pSessionId, A.invoice_ap_balance_id, pUserId, pDatetime)
	FROM fi_invoice_ap_balance A
	JOIN m_nrb_generated_document B ON A.invoice_ap_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id
	WHERE B.create_datetime = pDatetime
	AND B.ref_doc_type_id = vDocTypeIdRpi
	GROUP BY A.invoice_ap_balance_id;

	PERFORM gl_manage_admin_journal_trx(A.tenant_id, vOuBuId, A.ou_id, vJournalType, vLedgerCode, f_get_year_month_date(A.doc_date), 'DAILY', pDatetime, pUserId)
	FROM pu_invoice A
	WHERE A.invoice_id IN (
		SELECT B.ref_id
		FROM m_nrb_generated_document B
		WHERE B.ref_doc_type_id = vDocTypeIdRpi
		AND B.create_datetime = pDatetime
		GROUP BY B.ref_id
	);

	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 B.journal_trx_id, A.tenant_id, vJournalType, A.doc_type_id, A.invoice_id, A.doc_no, A.doc_date,
  		vOuBuId, vOuBranchId, vOuSubBuId, A.partner_id, vNullId, vNullId, A.ext_doc_no, A.ext_doc_date,
  		A.ref_doc_type_id, A.ref_id, A.due_date, A.curr_code, A.remark, vStatusDocDraft, 'DRAFT',
  		vZeroValue, pDatetime, pUserId, pDatetime, pUserId
  	FROM pu_invoice A
  	JOIN m_nrb_generated_document B ON A.invoice_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id
	WHERE B.create_datetime = pDatetime
	AND B.ref_doc_type_id = vDocTypeIdRpi
	GROUP BY B.journal_trx_id, A.tenant_id, A.doc_type_id, A.invoice_id, A.doc_no, A.doc_date,
  		A.partner_id, A.ext_doc_no, A.ext_doc_date,	A.ref_doc_type_id, A.ref_id, A.due_date, A.curr_code, A.remark;

  	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, D.journal_trx_id, 1,
		B.ref_doc_type_id, B.ref_item_id,
		A.partner_id, vNullId, vNullId, vNullId,
		vNullId, vSignCredit, vSystemCOA, vNullId,
		f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vNullId,
		B.ref_item_amount, C.doc_date, vTypeRate,
		1, 1, 'ACCR_AP', B.remark
	FROM pu_invoice A, pu_invoice_item B, in_inventory C, m_nrb_generated_document D
	WHERE A.invoice_id = D.ref_id
		AND D.create_datetime = pDatetime
		AND D.ref_doc_type_id = vDocTypeIdRpi
		AND A.invoice_id = B.invoice_id
		AND B.ref_id = C.inventory_id
	GROUP BY A.tenant_id, D.journal_trx_id,
		B.ref_doc_type_id, B.ref_item_id,
		A.partner_id, A.curr_code,
		B.ref_item_amount, C.doc_date, B.remark;

	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, D.journal_trx_id, 1,
		A.doc_type_id, B.invoice_tax_id,
		A.partner_id, vNullId, vNullId, vNullId,
		vNullId, vSignCredit, vTaxCOA, vNullId,
		C.receive_coa_id, C.tax_curr_code, 0, vNullId,
		B.gov_tax_amount, A.doc_date, vTypeRate,
		1, 1, 'VAT_IN', B.remark
	FROM pu_invoice A, pu_invoice_tax B, m_tax C, m_nrb_generated_document D
	WHERE A.invoice_id = D.ref_id
		AND D.create_datetime = pDatetime
		AND D.ref_doc_type_id = vDocTypeIdRpi
		AND A.invoice_id = B.invoice_id
		AND B.tax_id = C.tax_id
	GROUP BY A.tenant_id, D.journal_trx_id,A.doc_type_id, B.invoice_tax_id,
		A.partner_id, C.receive_coa_id, C.tax_curr_code,
		B.gov_tax_amount, A.doc_date, B.remark;

	/*
	 * buat juournal offside untuk yg credit ap
	 * dengan grouping data journal yang lain melakukan debit account lain
	 */
	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 A.session_id, B.tenant_id, B.journal_trx_id, 1,
		B.doc_type_id, B.doc_id,
		B.partner_id, vNullId, vNullId, vNullId,
		vNullId, vSignCredit, vSystemCOA, vNullId,
		f_get_ap_coa_partner(B.tenant_id, B.partner_id), A.curr_code, 0, vNullId,
		SUM(A.amount), B.doc_date, vTypeRate,
		1, 1, 'AP', B.remark
	FROM tt_journal_trx_item A, gl_journal_trx B
	WHERE A.session_id = pSessionId AND
		A.journal_trx_id = B.journal_trx_id AND
		A.sign_journal = vSignDebit AND
		journal_desc IN ('PURCH_COST')
	GROUP BY A.session_id, B.tenant_id, B.journal_trx_id, B.doc_type_id, B.doc_id,
		B.partner_id, A.curr_code, B.remark;

	/*
	 * buat juournal offside untuk yg debit ap
	 * dengan grouping data journal yang lain melakukan credit account lain
	 */
	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 A.session_id, B.tenant_id, B.journal_trx_id, 1,
		B.doc_type_id, B.doc_id,
		B.partner_id, vNullId, vNullId, vNullId,
		vNullId, vSignDebit, vSystemCOA, vNullId,
		f_get_ap_coa_partner(B.tenant_id, B.partner_id), A.curr_code, 0, vNullId,
		SUM(A.amount), B.doc_date, vTypeRate,
		1, 1, 'AP', B.remark
	FROM tt_journal_trx_item A, gl_journal_trx B
	WHERE A.session_id = pSessionId AND
		A.journal_trx_id = B.journal_trx_id AND
		A.sign_journal = vSignCredit AND
		journal_desc IN ('PURCH_COST','ACCR_AP','VAT_IN')
	GROUP BY A.session_id, B.tenant_id, B.journal_trx_id, B.doc_type_id, B.doc_id,
		B.partner_id, A.curr_code, B.remark;

--INSERT INTO gl_journal_trx_item
--INSERT INTO gl_journal_trx_mapping
--DELETE FROM tt_journal_trx_item

	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
		journal_desc IN ('PURCH_COST','ACCR_AP','VAT_IN');

	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
		journal_desc IN ('AP');

	DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

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