submit cost allocation

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

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

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

	vProcessId			bigint;
	vCostAllocToProductId		bigint;
	vUserId				bigint;
	vDatetime			character varying(14);
	vFlagNo		 		character varying(1);
	vEmptyId			bigint;
	vEmptyValue			character varying(1);
	vActivityCOA 		character varying(20);
	vStatusRelease  	character varying(1);
	vStatusDraft	  	character varying(1);
	vJournalType		character varying(20);
	vSignDebit			character varying(1);
	vSignCredit			character varying(1);
	vTypeRate			character varying(3);
	vProductCOA			character varying(10);
	vParentOuId			bigint;
	vJournalTrxId		bigint;

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

BEGIN

	vEmptyId := -99;
	vFlagNo := 'N';
	vActivityCOA := 'ACTIVITY';
	vStatusRelease := 'R';
	vStatusDraft := 'D';
	vSignDebit := 'D';
	vSignCredit := 'C';
	vTypeRate := 'COM';
	vProductCOA := 'PRODUCT';
	vEmptyValue := ' ';

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

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

	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;
	DELETE FROM tt_in_product_price_balance WHERE session_id = pSessionId;

	/*
	 * mengambil data dari in_inventory_item tanpa melakukan group by untuk ditampung ke tt_in_product_price_balance
	 */
	INSERT INTO tt_in_product_price_balance
	(session_id, tenant_id, ou_id, doc_type_id, doc_no, doc_date, partner_id, warehouse_id,
	inventory_id, inventory_item_id, product_balance_id, product_id, product_status,
	 serial_number, product_expired_date, product_year_made, lot_number, qty_realization, base_uom_id,
	 product_price_balance_id, curr_code, amount)
	SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id, A.doc_no, A.doc_date, A.partner_id, A.warehouse_from_id,
		A.inventory_id, B.inventory_item_id, B.product_balance_id, B.product_id, B.product_status,
		B.serial_number, B.product_expired_date, B.product_year_made, B.lot_number, B.qty_realization, B.base_uom_id,
		vEmptyId, B.curr_code, B.amount
	FROM in_inventory A, in_inventory_item B
	WHERE A.inventory_id = B.inventory_id AND
		A.inventory_id = vCostAllocToProductId;

	UPDATE tt_in_product_price_balance SET product_balance_id = A.product_balance_id
	FROM in_product_balance A
	WHERE tt_in_product_price_balance.session_id = pSessionId AND
		tt_in_product_price_balance.product_id = A.product_id AND
		tt_in_product_price_balance.tenant_id = A.tenant_id AND
		tt_in_product_price_balance.serial_number = A.serial_number AND
		tt_in_product_price_balance.lot_number = A.lot_number AND
		tt_in_product_price_balance.product_balance_id = vEmptyId;

	INSERT INTO in_product_price_balance
	(tenant_id, ou_id, product_id, product_balance_id,
  	product_buy_date, partner_id, doc_type_id, ref_id, doc_no, doc_date,
  	curr_code, amount, qty, uom_id,
  	"version", create_datetime, create_user_id, update_datetime, update_user_id)
  	SELECT C.tenant_id, C.ou_id, C.product_id, C.product_balance_id,
  		C.doc_date, C.partner_id, C.doc_type_id, C.inventory_item_id, C.doc_no, C.doc_date,
  		C.curr_code, SUM(C.amount), 0, C.base_uom_id,
  		0, vDatetime, vUserId, vDatetime, vUserId
  	FROM tt_in_product_price_balance C
  	WHERE C.inventory_id = vCostAllocToProductId AND
  		C.session_id = pSessionId
  	GROUP BY C.tenant_id, C.ou_id, C.product_id, C.product_balance_id,
  		C.doc_date, C.partner_id, C.doc_type_id, C.inventory_item_id, C.doc_no, C.doc_date,
  		C.curr_code, C.base_uom_id;

	/*
	 * 1. update status doc in_inventory
	 * 2. buat data jurnal
	 */

	SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
	FROM in_inventory A
	WHERE A.inventory_id = vCostAllocToProductId INTO result;

	vOuStructure := result.ou;
	vDocJournal := result.doc;

	UPDATE in_inventory SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
	WHERE inventory_id = vCostAllocToProductId;

	/*
	 * journal adjusment qty
	 * Debit Inventory, jika qty > 0
	 * Credit Inventory, jika qty < 0
	 *
	 * Mapping ke account activity gl
	 */
	/*
	 * 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_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
	FROM in_inventory A
	WHERE A.inventory_id = vCostAllocToProductId;

	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.inventory_id, A.doc_no, A.doc_date,
  		(vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_from_id, A.ext_doc_no, A.ext_doc_date,
  		A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
  		0, vDatetime, vUserId, vDatetime, vUserId
  	FROM in_inventory A
  	WHERE A.inventory_id = vCostAllocToProductId;

	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,
	gl_curr_code, gl_amount)
	SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
		A.doc_type_id, B.inventory_item_id,
		A.partner_id, B.product_id, vEmptyId, vEmptyId,
		vEmptyId, vSignDebit, vProductCOA, vEmptyId,
		f_get_product_coa_group_product(A.tenant_id, B.product_id), B.curr_code, B.qty_realization, B.base_uom_id,
		B.amount , A.doc_date, vTypeRate,
		1, 1, 'PRODUCT_STOCK', B.remark,
		B.curr_code, B.amount
	FROM in_inventory A, in_inventory_item B
	WHERE A.inventory_id = vCostAllocToProductId AND
		A.inventory_id = B.inventory_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,
	gl_curr_code, gl_amount)
	SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
		vEmptyId, vEmptyId,
		vEmptyId, vEmptyId, vEmptyId, B.ou_rc_id,
		B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
		D.coa_id, B.curr_code, 0, vEmptyId,
		B.amount, A.doc_date, vTypeRate,
		1, 1, 'COST_ALLOC_PRODUCT', vEmptyValue,
		B.gl_curr_code, B.gl_amount
	FROM in_inventory A, in_inventory_alloc_cost_detail B, m_activity_gl D
	WHERE A.inventory_id = vCostAllocToProductId AND
		A.inventory_id = B.inventory_id AND
		B.activity_gl_id = D.activity_gl_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,
	gl_curr_code, gl_amount)
	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,
		A.gl_curr_code, A.gl_amount
	FROM tt_journal_trx_item A
	WHERE A.session_id = pSessionId AND
		A.journal_desc = 'PRODUCT_STOCK';

	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,
	gl_curr_code, gl_amount)
	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,
		A.gl_curr_code, A.gl_amount
	FROM tt_journal_trx_item A
	WHERE A.session_id = pSessionId AND
		A.journal_desc = 'COST_ALLOC_PRODUCT';

	DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
	DELETE FROM tt_in_product_price_balance WHERE session_id = pSessionId;

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