cancel submit cost allocation

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

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

CREATE OR REPLACE FUNCTION in_cancel_submit_cost_alloc_to_product(
    bigint,
    character varying,
    bigint,
    character varying,
    bigint,
    character varying,
    character varying)
  RETURNS void AS
$BODY$
DECLARE
	pTenantId							ALIAS FOR $1;
    pSessionId                          ALIAS FOR $2;
    pUserId                             ALIAS FOR $3;
    pDatetime                           ALIAS FOR $4;
    pId									ALIAS FOR $5;
    pRemarkApproval                 	ALIAS FOR $6;
    pPrevProcessNo                  	ALIAS FOR $7;

	vFunctionSubmit						character varying := 'in_submit_cost_alloc_to_product';
	vStatusDraft					 	character varying := 'D';
	vStatusRelease						character varying := 'R';
	vStatusInProgress				 	character varying := 'I';
	vWorkflowStatus					 	character varying := 'DRAFT';
	vInventoryLedgerCode				character varying := 'INV';
	vStatusLedgerNotDone				character varying := '0';

	vDocNo								character varying;
	vScheme								character varying;
	vDocDate							character varying;
    vDocJournal							DOC_JOURNAL%ROWTYPE;
    vOuStructure						OU_BU_STRUCTURE%ROWTYPE;
    result								RECORD;

    vJournalTrxId						bigint;
    vOuId								bigint;
	vDocTypeIdCostAllocToProduct		bigint := 528;

BEGIN

	/*
	 * 1. get data
	 * 2. Cek status ledger not yet closed
	 * 		2.1 DELETE from in_product_price_balance
	 * 		2.2 Update status doc in_inventory menjadi 'D' dan workflow_status nya menjadi DRAFT
	 * 		2.3 PERFORM cancel gl_cancel_admin_journal_trx
	 * 		2.4 Ambil ID dari gl_journal_trx
	 * 		2.5 DELETE FROM gl_journal_trx
	 * 		2.6 DELETE FROM gl_journal_trx_item
	 * 		2.7 DELETE FROM gl_journal_trx_mapping
	 * 		2.8. PERFORM f_reset_approval_to_draft
	 * 		2.9. UPDATE t_process_message
	 */

	-- 1. get data
	select f_get_ou_bu_structure(A.ou_from_id) AS ou, A.ou_from_id, A.doc_no, A.doc_date,
			f_get_document_journal(A.doc_type_id) as doc, C.scheme
	FROM in_inventory A, in_inventory_item B, m_document C
	WHERE A.inventory_id = B.inventory_id AND
		A.doc_type_id = C.doc_type_id AND
		A.inventory_id = pId AND
		A.doc_type_id = vDocTypeIdCostAllocToProduct
		AND A.status_doc = vStatusRelease INTO result;

	IF FOUND THEN
    	vOuStructure := result.ou;
		vOuId := result.ou_from_id;
		vDocNo := result.doc_no;
		vDocDate := result.doc_date;
		vDocJournal := result.doc;
		vScheme := result.scheme;

		-- 2. Cek status ledger not yet closed
		IF EXISTS (	SELECT 1
					FROM m_admin_process_ledger e, m_ou_structure f, t_ou g
					WHERE e.tenant_id = pTenantId
						AND e.ou_id = f.ou_bu_id
						AND f.ou_id = vOuId
						AND e.date_year_month = SUBSTR(vDocDate, 1, 6)
						AND e.ledger_code = vInventoryLedgerCode AND
						f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone) THEN

			-- 2.1 DELETE from in_product_price_balance
			DELETE from in_product_price_balance
			WHERE EXISTS(
				SELECT 1
				FROM in_inventory A, in_inventory_item B
				WHERE A.inventory_id = B.inventory_id AND
					A.inventory_id = pId AND
					in_product_price_balance.product_id = B.product_id AND
					in_product_price_balance.product_balance_id = B.product_balance_id AND
					in_product_price_balance.doc_type_id = A.doc_type_id AND
					in_product_price_balance.ref_id = B.inventory_item_id );

			-- 2.2 Update status doc in_inventory menjadi 'D' dan workflow_status nya menjadi DRAFT
			UPDATE in_inventory SET status_doc = vStatusDraft, workflow_status = vWorkflowStatus,
				version = version - 1, update_datetime = pDatetime, update_user_id = pUserId
			WHERE inventory_id = pId;

			-- 2.3 PERFORM cancel gl_cancel_admin_journal_trx
			PERFORM gl_cancel_admin_journal_trx(pTenantId, (vOuStructure).ou_bu_id, vOuId, (vDocJournal).journal_type, f_get_year_month_date(vDocDate), 'MONTHLY', pDatetime, pUserId);

			-- 2.4 Ambil ID dari gl_journal_trx
			SELECT journal_trx_id INTO vJournalTrxId
			FROM gl_journal_trx
			WHERE doc_type_id = vDocTypeIdCostAllocToProduct AND doc_id = pId;

			-- 2.5 DELETE FROM gl_journal_trx
			DELETE FROM gl_journal_trx WHERE journal_trx_id = vJournalTrxId;

			-- 2.6 DELETE FROM gl_journal_trx_item
			DELETE FROM gl_journal_trx_item WHERE journal_trx_id = vJournalTrxId;

			-- 2.7 DELETE FROM gl_journal_trx_mapping
			DELETE FROM gl_journal_trx_mapping WHERE journal_trx_id = vJournalTrxId;

			-- 2.8. PERFORM f_reset_approval_to_draft
			PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pId, vDocNo, pDatetime, pRemarkApproval);

			-- 2.9. UPDATE t_process_message
			UPDATE t_process_message
			SET process_no = pPrevProcessNo,
				update_datetime = pDatetime,
				update_user_id = pUserId,
				version = version + 1
			WHERE tenant_id = pTenantId
				AND process_name = vFunctionSubmit
				AND process_no = pId || '_' || vDocNo;

		ELSE
			RAISE EXCEPTION 'Admin Process Ledger for Inventory in year month % is already closed', SUBSTR(vDocDate, 1, 6);
		END IF;

	ELSE
    	RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pClaimNoteId;
	END IF;


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