c_validcombination_trg.sql

Java写的ERP系统

CREATE OR REPLACE TRIGGER C_ValidCombination_Trg
BEFORE INSERT OR UPDATE
	ON C_ValidCombination
FOR EACH ROW
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2003 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_ValidCombination_Trg.sql,v 1.6 2003/02/19 06:49:07 jjanke Exp $
 ***
 * Title:	Update Account Combination/Decsription/IsFullyQualified
 * Description:
 *  ValidCombination
 *		For an Account Combination
 *				Creates Combination	(Value)	100*100*1000		
 *				Creates Description	(Name)	XOrg*HQ*Cash
 *				Checks, if fully qualified + raises error if not
 *	Fully qualidied account combination is when all active 
 *	mandatory elements have values.
 ************************************************************************/
DECLARE
	Combination		VARCHAR(60) := '';
	Description		VARCHAR(255) := '';
	IsFullyQualified	CHAR(1) := 'Y';
	v_Separator		Char(1);
	TempValue			VARCHAR(60) := '_';
	TempName			VARCHAR(60) := '_';

	CURSOR	Cur_Elements IS
		SELECT	* 
		FROM	C_AcctSchema_Element
		WHERE	C_AcctSchema_ID=:new.C_AcctSchema_ID
		  AND	IsActive='Y' 
		ORDER BY SeqNo;

	Not_Fully_Qualified EXCEPTION;

	--  Individual Statements
	CURSOR	Cur_OO IS
		SELECT Value, Name
		FROM AD_Org WHERE AD_Org_ID=:new.AD_Org_ID AND IsActive='Y';
	CURSOR	Cur_AC IS
		SELECT Value, Name
		FROM C_ElementValue WHERE C_ElementValue_ID=:new.Account_ID AND IsActive = 'Y';
	CURSOR	Cur_PR IS
		SELECT Value, Name
		FROM M_Product WHERE M_Product_ID=:new.M_Product_ID AND IsActive='Y';
	CURSOR	Cur_BP IS
		SELECT Value, Name
		FROM C_BPartner WHERE C_BPartner_ID=:new.C_BPartner_ID AND IsActive = 'Y';
	CURSOR	Cur_TO IS
		SELECT Value, Name
		FROM AD_Org WHERE AD_Org_ID=:new.AD_OrgTrx_ID AND IsActive='Y';
	CURSOR	Cur_LF IS
		SELECT Postal, City
		FROM C_Location WHERE C_Location_ID=:new.C_LocFrom_ID AND IsActive='Y';
	CURSOR	Cur_LT IS
		SELECT Postal, City
		FROM C_Location WHERE C_Location_ID=:new.C_LocTo_ID AND IsActive='Y';
	CURSOR	Cur_SR IS
		SELECT Value, Name
		FROM C_SalesRegion WHERE C_SalesRegion_ID=:new.C_SalesRegion_ID AND IsActive='Y';
	CURSOR	Cur_PJ IS
		SELECT Value, Name
		FROM C_Project WHERE C_Project_ID=:new.C_Project_ID AND IsActive='Y';
	CURSOR	Cur_MC IS
		SELECT Value, Name
		FROM C_Campaign WHERE C_Campaign_ID=:new.C_Campaign_ID AND IsActive='Y';
	CURSOR	Cur_AY IS
		SELECT Value, Name
		FROM C_Activity WHERE C_Activity_ID=:new.C_Activity_ID AND IsActive='Y';
	CURSOR	Cur_U1 IS
		SELECT Value, Name
		FROM C_ElementValue WHERE C_ElementValue_ID=:new.User1_ID AND IsActive = 'Y';
	CURSOR	Cur_U2 IS
		SELECT Value, Name
		FROM C_ElementValue WHERE C_ElementValue_ID=:new.User2_ID AND IsActive = 'Y';
	--
	v_change	   BOOLEAN;
BEGIN
	--  Load C_AcctSchema Info	(if not valid: 01403 no data found)
	SELECT	Separator 
	  INTO	v_Separator
	FROM	C_AcctSchema
	WHERE	C_AcctSchema_ID=:new.C_AcctSchema_ID;

	--  Loop for C_AcctSchema_Elements
	FOR CE IN Cur_Elements LOOP
		IF (CE.ElementType = 'OO') THEN
			OPEN Cur_OO;
			FETCH Cur_OO INTO TempValue, TempName;
			IF (Cur_OO%NOTFOUND) THEN	--	mandatory
				IsFullyQualified := 'N'; 
			END IF;
			CLOSE Cur_OO;
		ELSIF (CE.ElementType = 'AC') THEN
			OPEN Cur_AC;
			FETCH Cur_AC INTO TempValue, TempName;
			IF (Cur_AC%NOTFOUND) THEN	--	mandatory
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_AC;
		ELSIF (CE.ElementType = 'PR') THEN
			OPEN Cur_PR;
			FETCH Cur_PR INTO TempValue, TempName;
			IF (Cur_PR%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_PR;
		ELSIF (CE.ElementType = 'BP') THEN
			OPEN Cur_BP;
			FETCH Cur_BP INTO TempValue, TempName;
			IF (Cur_BP%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_BP;
		ELSIF (CE.ElementType = 'TO') THEN
			OPEN Cur_TO;
			FETCH Cur_TO INTO TempValue, TempName;
			IF (Cur_TO%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_TO;
		ELSIF (CE.ElementType = 'LF') THEN
			OPEN Cur_LF;
			FETCH Cur_LF INTO TempValue, TempName;
			IF (Cur_LF%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_LF;
		ELSIF (CE.ElementType = 'LT') THEN
			OPEN Cur_LT;
			FETCH Cur_LT INTO TempValue, TempName;
			IF (Cur_LT%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_LT;
		ELSIF (CE.ElementType = 'SR') THEN
			OPEN Cur_SR;
			FETCH Cur_SR INTO TempValue, TempName;
			IF (Cur_SR%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_SR;
		ELSIF (CE.ElementType = 'PJ') THEN
			OPEN Cur_PJ;
			FETCH Cur_PJ INTO TempValue, TempName;
			IF (Cur_PJ%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_PJ;
		ELSIF (CE.ElementType = 'MC') THEN
			OPEN Cur_MC;
			FETCH Cur_MC INTO TempValue, TempName;
			IF (Cur_MC%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_MC;
		ELSIF (CE.ElementType = 'AY') THEN
			OPEN Cur_AY;
			FETCH Cur_AY INTO TempValue, TempName;
			IF (Cur_AY%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_AY;
		ELSIF (CE.ElementType = 'U1') THEN
			OPEN Cur_U1;
			FETCH Cur_U1 INTO TempValue, TempName;
			IF (Cur_U1%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_U1;
		ELSIF (CE.ElementType = 'U2') THEN
			OPEN Cur_U2;
			FETCH Cur_U2 INTO TempValue, TempName;
			IF (Cur_U2%NOTFOUND AND CE.IsMandatory='Y') THEN
				IsFullyQualified := 'N';
			END IF;
			CLOSE Cur_U2;
		END IF;

		--	Construct Info Fields
		Combination := Combination || TempValue || v_Separator;
		Description := Description || TempName || v_Separator;
		TempValue := '_';
		TempName := '_';
	END LOOP;

	--  Fully Qualified?
	IF (:new.IsFullyQualified='Y' AND IsFullyQualified='N') THEN
		RAISE Not_Fully_Qualified;
	END IF;

	--  Finish
	:new.Combination := SUBSTR(Combination,1,LENGTH(Combination)-1);
	:new.Description := SUBSTR(Description,1,LENGTH(Description)-1);
	:new.IsFullyQualified := IsFullyQualified;
	IF NOT (:new.Combination=:old.Combination AND :new.Description=:old.Description) THEN
		 DBMS_OUTPUT.PUT_LINE (:new.Combination || ' - ' || :new.Description);
	END IF;

EXCEPTION
	WHEN Not_Fully_Qualified THEN
		RAISE_APPLICATION_ERROR (-20101, 'Not fully Qualified: ' 
			|| Combination || '(' || :new.C_ValidCombination_ID || ')');

END C_ValidCombination_Trg;
/