c_acctschema_element_trg.sql

来自「Java写的ERP系统」· SQL 代码 · 共 88 行

CREATE OR REPLACE TRIGGER C_AcctSchema_Element_Trg
AFTER INSERT OR DELETE OR UPDATE
	ON C_AcctSchema_Element
FOR EACH ROW
DECLARE
/******************************************************************************
 * ** Compiere Product **             Copyright (c) 1999-2001 Accorto, Inc. USA
 * Open  Source  Software        Provided "AS IS" without warranty or liability
 * When you use any parts (changed or unchanged), add  "Powered by Compiere" to
 * your product name;  See license details http://www.compiere.org/license.html
 ******************************************************************************
 *	Update Valid Combination
 *	when element becomes mandatory
 **/
	v_UpdateIt			BOOLEAN := FALSE;
BEGIN
	--	Do we have to update?
	IF (INSERTING OR UPDATING) THEN
		IF (:new.IsMandatory = 'Y') THEN
			v_UpdateIt := TRUE;
	  	END IF;			
	END IF;
	--
	IF (v_UpdateIt) THEN
		IF (:new.ElementType IN ('OO', 'AC')) THEN
			NULL;	--	Mandatory - no need

		ELSIF (:new.ElementType = 'PR' AND :new.M_Product_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	M_Product_ID = :new.M_Product_ID
	   		WHERE	M_Product_ID IS NULL;

	   	ELSIF (:new.ElementType = 'BP' AND :new.C_BPartner_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	C_BPartner_ID = :new.C_BPartner_ID
	   		WHERE	C_BPartner_ID IS NULL;
	  	
		ELSIF (:new.ElementType = 'PJ' AND :new.C_Project_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	C_Project_ID = :new.C_Project_ID
	   		WHERE	C_Project_ID IS NULL;

		ELSIF (:new.ElementType = 'OT' AND :new.Org_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	AD_OrgTrx_ID = :new.Org_ID
	   		WHERE	AD_OrgTrx_ID IS NULL;

		ELSIF (:new.ElementType = 'LT' AND :new.C_Location_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	C_LocTo_ID = :new.C_Location_ID
	   		WHERE	C_LocTo_ID IS NULL;

		ELSIF (:new.ElementType = 'LF' AND :new.C_Location_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	C_LocFrom_ID = :new.C_Location_ID
	   		WHERE	C_LocFrom_ID IS NULL;

		ELSIF (:new.ElementType = 'SR' AND :new.C_SalesRegion_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	C_SalesRegion_ID = :new.C_SalesRegion_ID
	   		WHERE	C_SalesRegion_ID IS NULL;

		ELSIF (:new.ElementType = 'MC' AND :new.C_Campaign_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	C_Campaign_ID = :new.C_Campaign_ID
	   		WHERE	C_Campaign_ID IS NULL;

		ELSIF (:new.ElementType = 'AY' AND :new.C_Activity_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	C_Activity_ID = :new.C_Activity_ID
	   		WHERE	C_Activity_ID IS NULL;

		ELSIF (:new.ElementType = 'U1' AND :new.C_ElementValue_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	User1_ID = :new.C_ElementValue_ID
	   		WHERE	User1_ID IS NULL;

		ELSIF (:new.ElementType = 'U2' AND :new.C_ElementValue_ID IS NOT NULL) THEN
			UPDATE	C_ValidCombination
			  SET	User2_ID = :new.C_ElementValue_ID
	   		WHERE	User2_ID IS NULL;

		END IF;
	END IF;
	--
END C_AcctSchema_Element_Trg;
/