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

	vProcessId				bigint;
	vDoReceiptId					bigint;
	vUserId					bigint;
	vDatetime				character varying(14);
	vFlagInvoice 			character varying(1);
	vEmptyId				bigint;
	vStatusRelease  		character varying(1);
	vStatusDraft	  		character varying(1);
	vStatusFinal  			character varying(1);
	vStatusVoid  			character varying(1);
	vEmptyValue				character varying(1);
	vProductStatus			character varying(5);
	vSignDebit				character varying(1);
	vSignCredit				character varying(1);
	vTypeRate				character varying(3);
	vProductCOA				character varying(10);
	vSystemCOA				character varying(10);
	vSoId					bigint;
	vDoId					bigint;
	vUnfinishedItem			bigint;
	vParentOuId				bigint;
	vJournalTrxId			bigint;
	vJournalType			character varying(20);
	vOuId					bigint;
	vOuWarehouseId			bigint;
	vResult					character varying;

	vDocJournal					DOC_JOURNAL%ROWTYPE;
	vOuStructure				OU_BU_STRUCTURE%ROWTYPE;
	vOuStructureJournalItem		OU_BU_STRUCTURE%ROWTYPE;
	result						RECORD;

	vDoReceiptDocTypeId		bigint;
	vRoundingModeNonTax		character varying(5);
	vDoDocTypeId			bigint;
	vSoDocTypeId			bigint;
	vSlsInvTempId			bigint;
	vSlsInvTempDocTypeId	bigint;
	vNo						character varying(5);

	vRemarkModifyItemSo		text;
	vAutonumIdModifyItemSo	bigint;
	vAutonumIdSo			bigint;
	vDocNoNewSo				character varying;
	vDocNoModifyItemSo		character varying;
	vModifyItemSoId			bigint;
	vDocDate				character varying(8);
	vRoleId					bigint;
	vFlgUserRole			character varying;
	vSchemeDor				character varying(20) := 'EA25';
	vDocTypeCart			bigint;

	vInvoiceId				bigint;
BEGIN

	vFlagInvoice := 'N';
	vEmptyId := -99;
	vStatusRelease := 'R';
	vStatusVoid	:= 'V';
	vStatusDraft := 'D';
	vStatusFinal := 'F';
	vEmptyValue := '';
	vProductStatus := 'GOOD';
	vSignDebit := 'D';
	vSignCredit := 'C';
	vTypeRate := 'COM';
	vProductCOA := 'PRODUCT';
	vSystemCOA := 'SYSTEM';
	vUnfinishedItem := 0;
	vNo := 'N';

	vDoReceiptDocTypeId = 525;
	vDoDocTypeId := 311;
	vSoDocTypeId := 301;
	vSlsInvTempDocTypeId := 361;
	vDocTypeCart := 370;

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

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

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

	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;
/*
 * 1. update status doc in_do_receipt
 * 2. add sl_log_so_balance_item
 * 3. add sl_so_balance_invoice
 * 4. add sl_so_balance_invoice_tax
 * 5. update sl_so_balance_invoice untuk Do Id
 * 6. update sl_so_balance_invoice_tax untuk Do Id
 * 7. void sales invoice temporer untuk So Id
 * 8. delete data sales invoice temporer dari fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
 * 9. add in_log_product_balance_stock
 * 10. update in_product_balance_stock
 * 11. update in_balance_do_item
 * 12. update sl_so_balance_item - status dan qty
 * 13. update status sl_so. Jika seluruh balance item sudah final/cancel, maka status menjadi Final.
 * 14. add gl_journal_trx
 * 15. add gl_journal_trx_item
 * 16. add gl_journal_trx_mapping
 *
 */

	SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc,
			A.doc_date
	FROM in_do_receipt A
	WHERE A.do_receipt_id = vDoReceiptId INTO result;

	vDoId := result.ref_id;
	vOuStructure := result.ou;
	vDocJournal := result.doc;
	vDocDate := result.doc_date;

	SELECT ref_id INTO vSoId
	FROM sl_do
	WHERE do_id = vDoId;

	SELECT 'Automatic Modify Item SO from DO receipt items of SO : '||A.doc_no INTO vRemarkModifyItemSo
	FROM sl_so A
	WHERE A.so_id = vSoId;

	-- Get role id, flg user role awe submit DO
	SELECT role_id, flg_user_role INTO vRoleId, vFlgUserRole
	FROM awe_historydoc
	WHERE scheme = vSchemeDor
	AND doc_id = vDoReceiptId
	AND activity = 'SUBMIT';

	/*
	 * update status doc in_do_receipt menjadi Release
	 */
	UPDATE in_do_receipt SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
	WHERE do_receipt_id = vDoReceiptId;

	/*
	 * buat data log sl_log_so_balance_item
	 */
  	INSERT INTO sl_log_so_balance_item
  	(tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
  	 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
	"version", create_datetime, create_user_id, update_datetime, update_user_id)
	SELECT A.tenant_id, C.so_id, C.so_item_id, A.doc_type_id, A.do_receipt_id, B.do_receipt_item_id,
		B.qty_return * D.qty_so / D.qty_int, D.so_uom_id, B.qty_return, B.uom_id, B.remark,
		0, vDatetime, vUserId, vDatetime, vUserId
	FROM in_do_receipt A, in_do_receipt_item B, in_balance_do_item C, sl_so_item D
	WHERE A.do_receipt_id = vDoReceiptId AND
	      A.do_receipt_id = B.do_receipt_id AND
	      B.ref_id = C.do_item_id AND
	      C.so_item_id = D.so_item_id AND
	      B.qty_return > 0;

	/*
	 * buat data sl_so_balance_Invoice
	 * supaya dapat ditarik saat buat sales invoice
	 */
	WITH insert_balance_invoice AS (
		SELECT B.product_id, D.qty_so,
				-- before
				ROUND(D.gross_sell_price * (D.qty_so)) AS gross_amount_before,
				D.nett_item_amount AS nett_item_amount_before, D.tax_amount AS tax_amount_after_disc_before,
				f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount,
				D.qty_so, f_get_digit_decimal_doc_curr(vSoDocTypeId, D.curr_code)) AS gaad_before,
				-- after
				ROUND(D.gross_sell_price * (D.qty_so - B.qty_return)) AS gross_amount_after,
				f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount,
				(D.qty_so - B.qty_return), f_get_digit_decimal_doc_curr(vSoDocTypeId, D.curr_code)) AS gaad_after,
				ROUND(f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount,
				(D.qty_so - B.qty_return), f_get_digit_decimal_doc_curr(vSoDocTypeId, D.curr_code)), flg_tax_amount, tax_percentage, f_get_digit_decimal_doc_curr(vSoDocTypeId, D.curr_code))) AS tax_amount_after_disc_after,
				-- other data
				A.tenant_id, A.ou_id, E.partner_bill_to_id, C.so_id,
				A.ref_doc_type_id, A.ref_id, F.doc_no, F.doc_date, B.ref_id AS ref_item_id, ROUND(B.qty_return * D.qty_so / D.qty_int) AS qty,
				D.so_uom_id, D.curr_code, D.nett_sell_price, D.flg_tax_amount, D.tax_percentage,
				B.do_receipt_item_id
		FROM in_do_receipt A, in_do_receipt_item B, in_balance_do_item C, sl_so_item D, sl_so E, sl_do F, sl_so_balance_invoice G
		WHERE A.do_receipt_id = vDoReceiptId AND
		      A.do_receipt_id = B.do_receipt_id AND
		      B.ref_id = C.do_item_id AND
		      C.so_item_id = D.so_item_id AND
		      D.so_id = E.so_id AND
		      A.ref_id = F.do_id AND
		      G.ref_id = F.do_id AND
		      G.ref_item_id = C.do_item_id AND
		      B.qty_return > 0
	), diff_summary AS (
		SELECT A.product_id, tax_amount_after_disc_before - tax_amount_after_disc_after AS tax_diff,
			f_get_dpp_after_discount(A.gaad_before-A.gaad_after, A.flg_tax_amount, A.tax_amount_after_disc_before - A.tax_amount_after_disc_after) AS dpp_after_disc_diff,
			(gross_amount_before - gaad_before) - (gross_amount_after - gaad_after) AS disc_amount_diff
		FROM insert_balance_invoice A
	)
	INSERT INTO sl_so_balance_invoice
		(tenant_id, ou_id, partner_id, so_id,
		ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id,
		curr_code, price_so, item_amount, flg_invoice, invoice_id,
		regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
		"version", create_datetime, create_user_id, update_datetime, update_user_id,
		do_receipt_item_id)
	SELECT tenant_id, ou_id, partner_bill_to_id, so_id,
		ref_doc_type_id, ref_id, doc_no, doc_date, ref_item_id, -1 * qty, so_uom_id,
		curr_code, nett_sell_price,
		-1 * (B.dpp_after_disc_diff + B.disc_amount_diff),
		vFlagInvoice, vEmptyId,
		-1 * B.disc_amount_diff, 0, 0, 0,
		0, vDatetime, vUserId, vDatetime, vUserId,
		do_receipt_item_id
	FROM insert_balance_invoice A
	JOIN diff_summary B ON A.product_id = B.product_id;


	/*
	 * buat data sl_so_balance_invoice_tax
	 * supaya dapat ditarik saat buat sales invoice
	 */
	WITH insert_balance_invoice_tax AS (
		SELECT B.product_id,
				-- BEFORE
				ROUND(D.gross_sell_price * (D.qty_so)) AS gross_amount_before,
				f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount,
				D.qty_so, f_get_digit_decimal_doc_curr(vSoDocTypeId, D.curr_code)) AS gaad_before,
				D.tax_amount AS tax_amount_after_disc_before,
				-- AFTER
				ROUND(D.gross_sell_price * (D.qty_so - B.qty_return)) AS gross_amount_after,
				f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount,
				(D.qty_so - B.qty_return), f_get_digit_decimal_doc_curr(vSoDocTypeId, D.curr_code)) AS gaad_after,
				ROUND(f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount,
				(D.qty_so - B.qty_return), f_get_digit_decimal_doc_curr(vSoDocTypeId, D.curr_code)), flg_tax_amount, tax_percentage, f_get_digit_decimal_doc_curr(vSoDocTypeId, D.curr_code))) AS tax_amount_after_disc_after,
				-- OTHER
				A.tenant_id, A.ou_id, F.partner_bill_to_id, C.so_id,
				A.ref_doc_type_id, A.ref_id, B.ref_id AS ref_item_id, D.tax_id, E.flg_amount,
				D.tax_percentage, D.curr_code, D.flg_tax_amount,
				B.do_receipt_item_id
		FROM in_do_receipt A, in_do_receipt_item B, in_balance_do_item C, sl_so_item D, m_tax E, sl_so F
		WHERE A.do_receipt_id = vDoReceiptId AND
		      A.do_receipt_id = B.do_receipt_id AND
		      B.ref_id = C.do_item_id AND
		      C.so_item_id = D.so_item_id AND
		      D.tax_id = E.tax_id AND
		      D.so_id = F.so_id AND
		      B.qty_return > 0
	), diff_summary AS (
		SELECT A.product_id, tax_amount_after_disc_before - tax_amount_after_disc_after AS tax_diff,
			f_get_dpp_after_discount(A.gaad_before-A.gaad_after, A.flg_tax_amount, A.tax_amount_after_disc_before - A.tax_amount_after_disc_after) AS dpp_after_disc_diff,
			(gross_amount_before - gaad_before) - (gross_amount_after - gaad_after) AS disc_amount_diff
		FROM insert_balance_invoice_tax A
	)
	INSERT INTO sl_so_balance_invoice_tax
	(tenant_id, ou_id, partner_id, so_id,
  	ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  	tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
	"version", create_datetime, create_user_id, update_datetime, update_user_id,
	do_receipt_item_id)
--	SELECT tenant_id, ou_id, partner_bill_to_id, so_id,
--		ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
--		tax_percentage, curr_code,
--		-1 * (f_get_dpp_after_discount(gross_after_disc, flg_tax_amount, f_get_tax_amount_after_discount(gross_after_disc, flg_tax_amount, tax_percentage, 0)) + (gross_amount - gross_after_disc)),
--		-1 * f_get_tax_amount_after_discount(gross_after_disc, flg_tax_amount, tax_percentage, 0),
--		vFlagInvoice, vEmptyId,
--		0, vDatetime, vUserId, vDatetime, vUserId,
--		do_receipt_item_id
	SELECT tenant_id, ou_id, partner_bill_to_id, so_id,
			ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
			tax_percentage, curr_code,
			-1 * (B.dpp_after_disc_diff + B.disc_amount_diff),
			-1 * tax_diff,
		 	vFlagInvoice, vEmptyId,
		 	0, vDatetime, vUserId, vDatetime, vUserId,
			do_receipt_item_id
	FROM insert_balance_invoice_tax A
	JOIN diff_summary B ON A.product_id = B.product_id;

	/*
	 * update sl_so_balance_invoice, dan sl_so_balance_invoice_tax
	 * yang sudah dibuat oleh sales invoice temporer
	 */
	UPDATE sl_so_balance_invoice SET flg_invoice = vFlagInvoice, flg_invoice_temp = vFlagInvoice, invoice_id = vEmptyId
	WHERE ref_id = vDoId AND
		ref_doc_type_id = vDoDocTypeId;

	UPDATE sl_so_balance_invoice_tax SET flg_invoice = vFlagInvoice, invoice_id = vEmptyId
	WHERE ref_id = vDoId AND
		ref_doc_type_id = vDoDocTypeId;

	/*
	 * void data sales invoice temporer yang menggunakan DO Item id yang sama dng Do Receipt :
	 * 1. cari invoice temp id yang memiliki data DO Item yang sama dengan DO Receipt
	 * 2. update status doc invoice temp sesuai id yang diperoleh di langkah ke 1
	 */
	SELECT A.invoice_temp_id INTO vSlsInvTempId
	FROM sl_invoice_temp_item A, in_do_receipt_item B, in_do_receipt C, sl_invoice_temp D
	WHERE A.ref_doc_type_id = B.ref_doc_type_id AND
		A.ref_id = vDoId AND
		A.ref_item_id = B.ref_id AND
		B.do_receipt_id = C.do_receipt_id AND
		C.ref_id = A.ref_id AND
		B.do_receipt_id = vDoReceiptId AND
		A.invoice_temp_id = D.invoice_temp_id AND
		D.status_doc <> vStatusVoid;

	UPDATE sl_invoice_temp SET status_doc = vStatusVoid, update_datetime = vDatetime, update_user_id = vUserId
	WHERE invoice_temp_id = vSlsInvTempId;

	/*
	 * advance invoice  harus diupdate juga supaya bisa dipakai lagi
	 */
	UPDATE sl_so_balance_advance_invoice B SET flg_invoice = vFlagInvoice, flg_invoice_temp = vFlagInvoice, invoice_id = vEmptyId
	WHERE B.so_id = vSoId
	AND EXISTS (
		SELECT 1 FROM sl_invoice_temp_advance A
		WHERE A.ref_id = B.ref_id
		AND A.ref_doc_type_id = B.ref_doc_type_id
		AND A.invoice_temp_id = vSlsInvTempId
	);


	/*
	 * nomor faktur pajak boleh digunakan kembali untuk temp sales invoice yang di void gara2 DO Receipt
	 *
	 */
	UPDATE m_gen_tax_number A
	SET invoice_id = vEmptyId, invoice_doc_type_id = vEmptyId, invoice_doc_no = vEmptyValue, invoice_doc_date = vEmptyValue
	FROM sl_invoice_temp B
	WHERE A.invoice_doc_no = B.inv_doc_no
	AND A.invoice_doc_date = B.inv_doc_date
	AND A.invoice_doc_type_id = B.doc_type_id
	AND A.invoice_id = vSlsInvTempId
	AND B.invoice_temp_id = vSlsInvTempId;

	/*
	 * delete data sales invoice temporer dari fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
	 */

	DELETE FROM fi_invoice_tax_ar_balance A
	WHERE A.tenant_id = pTenantId AND
	EXISTS (
		SELECT 1 FROM fi_invoice_ar_balance B
		WHERE A.tenant_id = B.tenant_id
		AND A.invoice_ar_balance_id = B.invoice_ar_balance_id
		AND B.doc_type_id = vSlsInvTempDocTypeId
		AND B.invoice_ar_id = vSlsInvTempId
	);

	DELETE FROM fi_invoice_ar_balance A
	WHERE A.tenant_id = pTenantId AND
		  A.doc_type_id = vSlsInvTempDocTypeId AND
		  A.invoice_ar_id = vSlsInvTempId;


	/*
	 * buat data log product balance stock
	 * ref item id = do_receipt_id
	 */
	INSERT INTO in_log_product_balance_stock
	(tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
	 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
	"version", create_datetime, create_user_id, update_datetime, update_user_id)
	SELECT A.tenant_id, E.ou_id, A.doc_type_id, A.do_receipt_id, A.doc_no, A.doc_date, D.partner_ship_to_id,
		C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.uom_id, SUM(C.qty_return),
		0, vDatetime, vUserId, vDatetime, vUserId
	FROM in_do_receipt A, in_do_receipt_item B, in_do_receipt_product C, sl_do D, m_warehouse_ou E
	WHERE A.do_receipt_id = vDoReceiptId AND
		A.do_receipt_id = B.do_receipt_id AND
		B.do_receipt_item_id = C.do_receipt_item_id AND
		A.ref_id = D.do_id AND
		A.warehouse_id = E.warehouse_id
	GROUP BY A.tenant_id, E.ou_id, A.doc_type_id, A.do_receipt_id, A.doc_no, A.doc_date, D.partner_ship_to_id,
			C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.uom_id;

	/*
	 * update in_product_balance_stock
	 * mod by Didit, 30 Des 2016
	 * perbaiki jika ada 1 produk yang sama (product dan product balance) tapi ada lebih dari 1 record di tabel itemnya akan menjadi salah
	 */
	WITH tt_in_product_balance_summary AS (
		SELECT 	C.product_id, C.tenant_id, A.warehouse_id, C.product_balance_id, C.product_status, SUM(C.qty_return) AS qty_return
		FROM 	in_do_receipt A, in_do_receipt_item B, in_do_receipt_product C
		WHERE 	A.do_receipt_id = vDoReceiptId
				AND A.do_receipt_id = B.do_receipt_id
				AND B.do_receipt_item_id = C.do_receipt_item_id
				AND C.qty_return > 0
		GROUP BY C.product_id, C.tenant_id, A.warehouse_id, C.product_balance_id, C.product_status
	)
	UPDATE in_product_balance_stock SET qty = qty + A.qty_return, update_datetime = vDatetime, update_user_id = vUserId, version = version + 1
	FROM tt_in_product_balance_summary A
	WHERE in_product_balance_stock.product_id = A.product_id AND
		in_product_balance_stock.tenant_id = A.tenant_id AND
		in_product_balance_stock.warehouse_id = A.warehouse_id AND
		in_product_balance_stock.product_balance_id = A.product_balance_id AND
		in_product_balance_stock.product_status = A.product_status;

	/*
	 * update data balance do item sehubungan dengan product yang dikembalian/tidak diterima oleh customer,
	 * sehingga saat akan membuat return note, hanya barang yang memang diterima oleh customer
	 */
	UPDATE in_balance_do_item SET qty_dlv_int = qty_dlv_int - A.qty_return,
								qty_dlv = in_balance_do_item.qty_dlv - (A.qty_return * in_balance_do_item.qty_dlv / in_balance_do_item.qty_dlv_int),
								status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
	FROM in_do_receipt_item A
	WHERE A.do_receipt_id = vDoReceiptId AND
		in_balance_do_item.do_item_id = A.ref_id AND
		A.qty_return > 0;

	UPDATE in_balance_do_item
	SET status_item = vStatusRelease,
		update_datetime = vDatetime,
		update_user_id = vUserId
	FROM in_do_receipt A
	WHERE A.do_receipt_id = vDoReceiptId AND
		in_balance_do_item.do_id = A.ref_id AND
		A.ref_doc_type_id = vDoDocTypeId;

	/*
	 * update status SO seperti semula
	 */
	UPDATE sl_so
	SET status_doc = flg_old_status,
		flg_old_status = vEmptyValue,
		update_datetime = vDatetime,
		update_user_id = vUserId,
		version = version + 1
	WHERE so_id = vSoId;

	/*
	 * update flg invoice so balance inv
	 */
	UPDATE sl_so_balance_invoice
	SET flg_invoice = vNo,
		update_datetime = vDatetime,
		update_user_id = vUserId,
		version = version + 1
	WHERE so_id = vSoId
		AND ref_id = vDoId;

	/*
	 * update status so balance item
	 */
	UPDATE sl_so_balance_item B
	SET status_item = B.flg_old_status,
		flg_old_status = vEmptyValue,
		update_datetime = vDatetime,
		update_user_id = vUserId,
		version = B.version + 1
	FROM sl_so A
	INNER JOIN sl_so_item C ON A.so_id = C.so_id
	INNER JOIN sl_do_item D ON D.ref_id = C.so_item_id
	WHERE B.so_item_id = C.so_item_id
		AND A.so_id = vSoId
		AND D.do_id = vDoId;

	/*
	 * status item SO akan berubah menjadi Release, karena ada nya barang yang dikembalikan oleh customer
	 */
	UPDATE sl_so_balance_item SET qty_dlv = sl_so_balance_item.qty_dlv - (A.qty_return * sl_so_balance_item.qty_so / sl_so_balance_item.qty_so_int),
								qty_dlv_int = sl_so_balance_item.qty_dlv_int - A.qty_return,
								status_item = vStatusRelease
	FROM in_do_receipt_item A, in_balance_do_item B
	WHERE A.do_receipt_id = vDoReceiptId AND
		A.ref_id = B.do_item_id AND
		sl_so_balance_item.so_item_id = B.so_item_id AND
		A.qty_return > 0;

    UPDATE sl_so SET status_doc = vStatusRelease
    WHERE so_id = vSoId;

    -- Buat invoice temp
    SELECT sl_automatic_create_temporary_sales_invoice_from_do_receipt(pSessionId, pTenantId, vUserId, vDatetime, vSoId, vDoId) INTO vResult;

    IF NOT EXISTS (
    	SELECT 1
		FROM sl_invoice_temp A
		WHERE A.ref_id = vSoId
		AND A.status_doc <> 'V'
		AND A.gross_amount = 0
    )
    THEN
	    -- Buat invoice dari invoice temp bila nilai temp SI > 0
		SELECT sl_automatic_create_sales_invoice_from_temp_sales_invoice(pSessionId, pTenantId, vUserId, vDatetime, vSoId, vDoId) INTO vResult;

		SELECT A.invoice_id into vInvoiceId
		FROM sl_invoice A
		WHERE A.ref_id = vSoId
		AND A.ref_doc_type_id = vSoDocTypeId;

		UPDATE fi_ar_edc_settlement A
		SET invoice_amount = B.total_amount,
		invoice_tax_amount = B.tax_amount,
		reward_amount = A.amount_payment - B.total_amount,
		invoice_id = vInvoiceId
		FROM sl_invoice B
		WHERE A.so_id = B.ref_id
		AND B.ref_doc_type_id = vSoDocTypeId
		AND A.so_id = vSoId;

		PERFORM cb_add_cn_ar_from_balance_partstation(pSessionId, pTenantId, vSoId, vUserId, vDatetime);

		PERFORM cb_add_dn_ar_from_balance_partstation(pSessionId, pTenantId, vSoId, vUserId, vDatetime)
		FROM fi_ar_edc_settlement A
		WHERE A.so_id = vSoId
		AND A.reward_amount > 0;

		PERFORM fi_alloc_sls_inv_edc(pTenantId, pSessionId, A.cn_ar_id, A.dn_ar_id, A.invoice_id, B.ou_id, A.partner_id, vUserId, vDatetime)
		FROM fi_ar_edc_settlement A
		INNER JOIN sl_so B ON B.so_id = A.so_id
		WHERE A.so_id = vSoId;

	END IF;

    /*
	 * @author TKP, 9 Jun 2016
	 * Cek jika OU pada warehouse sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id =-99
	 * jika OU pada warehouse tidak sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id didapat pada f_get_ou_bu_structure;
	 */

	SELECT A.ou_id, B.ou_id INTO vOuId, vOuWarehouseId
	FROM in_do_receipt A
	INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
	WHERE A.do_receipt_id = vDoReceiptId;

	IF (vOuId <> vOuWarehouseId) THEN
		SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
		vOuStructureJournalItem := result.ou_structure;
	ELSE
		vOuStructureJournalItem := ROW(-99, -99, -99);
	END IF;

	/*
	 * membuat data transaksi jurnal :
	 * 1. buat admin
	 * 2. buat temlate jurnal
	 */

	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), 'MONTHLY', vDatetime, vUserId)
	FROM in_do_receipt A
	WHERE A.do_receipt_id = vDoReceiptId;

	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.do_receipt_id, A.doc_no, A.doc_date,
  		(vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, B.partner_ship_to_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
  		A.ref_doc_type_id, A.ref_id, A.doc_date, C.curr_code, A.remark, vStatusDraft, 'DRAFT',
  		0, vDatetime, vUserId, vDatetime, vUserId
  	FROM in_do_receipt A, sl_do B, sl_so C
  	WHERE A.do_receipt_id = vDoReceiptId AND
  		A.ref_id = B.do_id AND
  		B.ref_id = C.so_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, B.do_receipt_item_id,
		C.partner_ship_to_id, B.product_id, vEmptyId, vEmptyId,
		vEmptyId, vSignDebit, vProductCOA, vEmptyId,
		f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_return, B.uom_id,
		0, A.doc_date, vTypeRate,
		1, 1, 'PRODUCT_STOCK', B.remark
	FROM in_do_receipt A, in_do_receipt_item B, sl_do C
	WHERE A.do_receipt_id = vDoReceiptId AND
		A.do_receipt_id = B.do_receipt_id AND
		A.ref_id = C.do_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,
	ou_branch_id, ou_sub_bu_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,
		(vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
	FROM tt_journal_trx_item A
	WHERE A.session_id = pSessionId;

	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 ),
		vEmptyId, vEmptyId,
		vEmptyId, vEmptyId, vEmptyId, vEmptyId,
		vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
		f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
		0, A.journal_date, A.type_rate,
		1, 1, 'COGS', vEmptyValue,
		0, vDatetime, vUserId, vDatetime, vUserId
	FROM tt_journal_trx_item A
	WHERE A.session_id = pSessionId
	GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;

	-- PROSES modify item dan create SO --
	-- Ambil autonum id Cancel SO
	SELECT A.process_parameter_value::bigint INTO vAutonumIdModifyItemSo
	FROM t_process_parameter A
	WHERE A.process_message_id = vProcessId
	AND	A.process_parameter_key = 'autonumIdModifyItemSo';

	-- Ambil doc no Cancel SO
	SELECT A.process_parameter_value INTO vDocNoModifyItemSo
	FROM t_process_parameter A
	WHERE A.process_message_id = vProcessId
	AND	A.process_parameter_key = 'autonumModifyItemSo';

	IF EXISTS(
		SELECT 1
		FROM sl_do A
		INNER JOIN sl_so_item C ON A.ref_id = C.so_id
		INNER JOIN sl_so D ON C.so_id = D.so_id
		WHERE A.do_id = vDoId AND D.ref_doc_type_id = vDocTypeCart
	)
	THEN

	PERFORM sl_add_cancel_item_so(pTenantId, pSessionId, vDatetime, vSoId, vUserId);

	ELSE
	-- Buatkan modify item SO ( APPROVED ), Function akan mengembalikan cancel so id yang akan di gunakan di pembuatan SO baru
	SELECT sl_automatic_modify_item_so_for_do_receipt(pSessionId, pTenantId, vUserId, vRoleId, vFlgUserRole, vDatetime, vSoId, vOuId, vAutonumIdModifyItemSo, vDocNoModifyItemSo, vDocDate, vRemarkModifyItemSo) INTO vModifyItemSoId;

	SELECT A.process_parameter_value::bigint INTO vAutonumIdSo
	FROM t_process_parameter A
	WHERE A.process_message_id = vProcessId
	AND	A.process_parameter_key = 'autonumIdSo';

	-- Ambil doc no SO baru
	SELECT A.process_parameter_value INTO vDocNoNewSo
	FROM t_process_parameter A
	WHERE A.process_message_id = vProcessId
	AND	A.process_parameter_key = 'autonumSo';

	-- Buatkan SO baru ( APPROVED )
	PERFORM sl_automatic_so_for_do_receipt(pSessionId, pTenantId, vUserId, vRoleId, vFlgUserRole, vDatetime, vSoId, vDoReceiptId, vAutonumIdSo, vDocNoNewSo, vDocDate, vModifyItemSoId);

	END IF;

	DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /