c_validcombbination_constraints.sql

Java写的ERP系统

/*************************************************************************
 * 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_ValidCombbination_Constraints.sql,v 1.2 2003/02/04 06:02:06 jjanke Exp $
 ***
 * Title:     Create Account Constraints
 * Description:
 ************************************************************************/
DECLARE
	CURSOR CUR_Accts IS
		 SELECT t.TableName, c.ColumnName
		 FROM AD_Table t
		   INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)
		 WHERE c.AD_Reference_ID=25
		   AND NOT EXISTS (SELECT * FROM USER_Cons_Columns cc
			  WHERE UPPER(t.TableName)=cc.Table_Name 
				AND UPPER(c.ColumnName)=cc.Column_Name
				AND cc.Position=1);
	--
	v_cmd		   VARCHAR2 (256);
	v_name		   VARCHAR2 (256);
BEGIN
	FOR a IN CUR_Accts LOOP
		 v_name := SUBSTR ('VC_' || REPLACE(a.ColumnName, '_', '') 
			  || '_' || REPLACE(a.TableName, '_', ''),1,30);
		 DBMS_OUTPUT.PUT_LINE (a.TableName || '.' || a.ColumnName || ' - ' || v_name);
		 v_cmd := 'ALTER TABLE ' || a.TableName
			  || ' ADD CONSTRAINT ' || v_name
			  || ' FOREIGN KEY (' || a.ColumnName 
			  || ') REFERENCES C_ValidCombination(C_ValidCombination_ID)';
		 BEGIN
			  EXECUTE IMMEDIATE v_cmd;
		 EXCEPTION
			  WHEN OTHERS THEN
				   DBMS_OUTPUT.PUT_LINE ('** ' || SQLERRM);
				   DBMS_OUTPUT.PUT_LINE ('   ' || v_cmd);
				   DBMS_OUTPUT.PUT_LINE ('   SELECT * FROM ' || a.TableName 
						|| ' x WHERE NOT EXISTS (SELECT * FROM C_ValidCombination vc'
						|| ' WHERE vc.C_ValidCombination_ID=x.' || a.ColumnName 
						|| ' AND vc.AD_Client_ID=x.AD_Client_ID)');
		 END;
	END LOOP;

END;
/