0_cleanupad.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+CPM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: 0_CleanUpAD.sql,v 1.12 2003/04/22 16:27:06 jjanke Exp $
 ***
 * Title:	Clean up Application Dictionary
 * Description:
 *		=> Run TempTables_Create/Drop <=	first
 ************************************************************************/
SELECT 'Clean up Application Dictionary' FROM DUAL;

-- Create temp table of all colums in AD and DB
SELECT '<<< Create temp table of all colums in AD and DB >>>' FROM DUAL;

CREATE OR REPLACE VIEW temp_db_columns AS
	SELECT t.TableName, c.ColumnName, t.AD_Table_ID, c.AD_Column_ID, 
		uc.Data_Type, COALESCE(uc.Char_Col_Decl_Length,uc.Data_Length) AS DataLength,
		uc.Data_Precision, uc.Data_Scale, uc.Nullable
	FROM AD_Table t, AD_Column c, User_Tab_Columns uc
	WHERE t.AD_Table_ID=c.AD_Table_ID
		AND uc.Table_Name=UPPER(t.TableName)
		AND uc.Column_Name=UPPER(c.ColumnName);

-- Delete columns not in DB = translated fields
SELECT	Name "Delete columns not in DB", AD_Field_ID
FROM	AD_Field f
WHERE NOT EXISTS 
	(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=f.AD_Column_ID);

DELETE	AD_Field_Trl
WHERE	AD_Field_ID IN 
	(SELECT AD_Field_ID FROM AD_Field f WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=f.AD_Column_ID));

-- Delete fields not in DB
DELETE	AD_Field
WHERE	AD_Field_ID IN 
	(SELECT AD_Field_ID FROM AD_Field f WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=f.AD_Column_ID));

-- Delete columns not in DB
SELECT	Name "Delete columns not in DB", AD_Column_ID
FROM	AD_Column c
WHERE NOT EXISTS 
	(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID); 

-- column used in table reference
DELETE	AD_Ref_Table
WHERE	AD_Key IN 
	(SELECT AD_Column_ID FROM AD_Column c WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID));
DELETE	AD_Ref_Table
WHERE	AD_Display IN
	(SELECT AD_Column_ID FROM AD_Column c WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID));

-- remove references
UPDATE	AD_Tab
SET		AD_Column_ID = NULL
WHERE	AD_Column_ID IN
	(SELECT AD_Column_ID FROM AD_Column c WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID));

DELETE	AD_PrintFormatItem
WHERE	AD_Column_ID IN 
	(SELECT AD_Column_ID FROM AD_Column c WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID));

DELETE	AD_Column
WHERE	AD_Column_ID IN 
	(SELECT AD_Column_ID FROM AD_Column c WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID));

-- Delete all AD_Tab rows of tables not in DB = translated tabs
SELECT	Name "Delete Tabs w tables not in DB", AD_Tab_ID
FROM	AD_Tab 
WHERE	AD_Table_ID IN
	(SELECT AD_Table_ID FROM AD_Table t WHERE NOT EXISTS
		(SELECT * FROM temp_db_columns db WHERE db.AD_Table_ID=t.AD_Table_ID));

DELETE	AD_Tab_Trl
WHERE	AD_Tab_ID IN 
	(SELECT AD_Tab_ID FROM AD_Tab
	WHERE AD_Table_ID IN
		(SELECT AD_Table_ID FROM AD_Table t WHERE NOT EXISTS
			(SELECT * FROM temp_db_columns db WHERE db.AD_Table_ID=t.AD_Table_ID)));

DELETE	AD_Tab
WHERE	AD_Table_ID IN
	(SELECT AD_Table_ID FROM AD_Table t WHERE NOT EXISTS
		(SELECT * FROM temp_db_columns db WHERE db.AD_Table_ID=t.AD_Table_ID));


-- Delete all AD_Table rows of tables not in DB
SELECT	Name "Delete Tables not in DB", AD_Table_ID
FROM	AD_Table t
WHERE NOT EXISTS
	(SELECT * FROM temp_db_columns db WHERE db.AD_Table_ID=t.AD_Table_ID);

DELETE	AD_Table t
WHERE NOT EXISTS
	(SELECT * FROM temp_db_columns db WHERE db.AD_Table_ID=t.AD_Table_ID);

-- Update: IsMandatory
SELECT '<<< Update: IsMandatory >>>' FROM DUAL;

UPDATE	AD_Column c
SET		IsMandatory = 'Y'
WHERE	IsMandatory = 'N' 
	AND EXISTS 
		(SELECT * FROM temp_db_columns db 
		WHERE c.AD_Column_ID=db.AD_Column_ID AND Nullable='N');

--	set AD_Client_ID/AD_Org_ID to TableDirect
SELECT '<<< set AD_Client_ID, AD_Org_ID to TableDir >>>' FROM DUAL;

UPDATE	AD_Column
SET		AD_Reference_ID = 19
WHERE	(UPPER(ColumnName) = 'AD_CLIENT_ID' OR UPPER(ColumnName) = 'AD_ORG_ID') 
	AND AD_Reference_ID NOT IN (13, 18, 19);

--	set _ID to Type ID
SELECT '<<< set _ID to Type ID >>>' FROM DUAL;

Update	AD_Column
  Set	AD_Reference_ID = 13
WHERE	ColumnName LIKE '%\_ID' ESCAPE '\' 
	AND AD_Reference_ID not in (13, 17, 18, 19, 21, 25, 27, 30, 31, 32, 33)
	AND ColumnName NOT IN ('Record_ID', 'Find_ID');

--	set Record_ID/Find_ID
UPDATE	AD_Column
  SET	AD_Reference_ID = 22	--	Number
WHERE	ColumnName = 'Find_ID'
  AND AD_Reference_ID <> 22;
UPDATE	AD_Column
  SET	AD_Reference_ID = 28	-- Button
WHERE	AD_Reference_ID NOT IN (28, 11)	--	Integer 
  AND	ColumnName = 'Record_ID';

--	set UpdatedBy, CreatedBy to Table
SELECT '<<< set UpdatedBy, CreatedBy to Table & User >>>' FROM DUAL;

UPDATE	AD_Column
  SET	AD_Reference_ID = 18,
		AD_Reference_Value_ID = 110
WHERE	UPPER(ColumnName) IN ('CREATEDBY', 'UPDATEDBY') 
	AND AD_Reference_ID != 18;

--	set Updated, Created to DateTime
SELECT '<<< set Updated, Created to DateTime >>>' FROM DUAL;

UPDATE	AD_Column
SET		AD_Reference_ID = 16
WHERE	ColumnName IN ('Created', 'Updated') 
	AND AD_Reference_ID != 16;

--	set Updated, Created to not updateable
SELECT '<<< set Updated, Created to not updateable >>>' FROM DUAL;

UPDATE	AD_Column
  SET	IsUpdateable = 'N'
WHERE	UPPER(ColumnName) IN ('CREATEDBY', 'UPDATEDBY','CREATED', 'UPDATED')
	AND IsUpdateable != 'N';


--	set _Acct
SELECT '<<< set _Acct >>>' FROM DUAL;

UPDATE	AD_Column
SET		AD_Reference_ID = 25,
		AD_Reference_Value_ID = NULL
WHERE	ColumnName LIKE '%\_Acct' ESCAPE '\'
	AND AD_Reference_ID != 25;

--	set IsActive
SELECT '<<< set IsActive >>>' FROM DUAL;

UPDATE	AD_Column
SET		DefaultValue = 'Y'
WHERE	ColumnName = 'IsActive' and DefaultValue = Null;

--  Set Replication Strategy
SELECT '<<< Set Replication Strategy to None >>>' FROM DUAL;

UPDATE	AD_Table
SET		RepStrategy = 'N'
WHERE	RepStrategy IS Null;

--  Clean up Field SortNo
SELECT '<<< Clean up Field SortNo >>>' FROM DUAL;

UPDATE	AD_Field
SET		SortNo = Null
WHERE	SortNo = 0;

--
SELECT '<<< Create Identifiers for Column Name for tables w/o identifier >>>' FROM DUAL;
UPDATE	AD_Column c
SET		IsIdentifier = 'Y',
		SeqNo = 1
WHERE	ColumnName = 'Name'
AND	NOT EXISTS
	(SELECT * FROM AD_Table t, AD_Column c2
	 WHERE t.AD_Table_ID=c2.AD_Table_ID
		AND c2.IsIdentifier='Y');

SELECT '<<< Create Identifiers for Column DocumentNo for tables w/o identifier >>>' FROM DUAL;
UPDATE	AD_Column c
SET		IsIdentifier = 'Y',
		SeqNo = 1
WHERE	ColumnName = 'DocumentNo'
AND	NOT EXISTS
	(SELECT * FROM AD_Table t, AD_Column c2
	 WHERE t.AD_Table_ID=c2.AD_Table_ID
		AND c2.IsIdentifier='Y');

SELECT '<<< Create Identifiers for Column Line for tables w/o identifier >>>' FROM DUAL;
UPDATE	AD_Column c
SET		IsIdentifier = 'Y',
		SeqNo = 1
WHERE	ColumnName = 'Line'
AND	NOT EXISTS
	(SELECT * FROM AD_Table t, AD_Column c2
	 WHERE t.AD_Table_ID=c2.AD_Table_ID
		AND c2.IsIdentifier='Y');