0_cleanupad.sql

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

WHERE	IsUpdateable='Y' AND (IsKey='Y' OR IsParent='Y');


SELECT '<<< Parent Fields Read/Only >>>' FROM DUAL;

-- Make Parent Fields r/o for tabs with two parents
UPDATE AD_Field
  SET IsReadOnly='Y'
WHERE IsReadOnly='N'
  AND AD_Column_ID IN (SELECT AD_Column_ID FROM AD_Column WHERE IsParent='Y');

-- Make Parent Fields r/w for tabs with two parents and the defined actual parent column
UPDATE	AD_Field f
  SET	IsReadOnly='N'
WHERE	IsReadOnly='Y' 
  AND EXISTS
	(SELECT * FROM AD_Column c WHERE c.AD_Column_ID=f.AD_Column_ID AND c.IsParent='Y')
  AND AD_Tab_ID IN
	(SELECT AD_Tab_ID
	FROM AD_Tab
	WHERE AD_Column_ID IS NOT NULL
	-- tables with two parent fields
	AND AD_Table_ID IN
		(SELECT AD_Table_ID FROM AD_Table t
		WHERE (SELECT COUNT(*) FROM AD_Column c WHERE c.AD_Table_ID=t.AD_Table_ID AND IsParent='Y')>1)
	);

-- Make Actual Parent fields r/o in tabs with two parent fields
UPDATE	AD_Field f
  SET	IsReadOnly='Y'
WHERE	IsReadOnly='N' 
  AND (AD_Tab_ID, AD_Column_ID) IN
	(SELECT AD_Tab_ID, AD_Column_ID
	FROM AD_Tab
	WHERE AD_Column_ID IS NOT NULL
	-- tables with two parent fields
	AND AD_Table_ID IN
		(SELECT AD_Table_ID FROM AD_Table t
		WHERE (SELECT COUNT(*) FROM AD_Column c WHERE c.AD_Table_ID=t.AD_Table_ID AND IsParent='Y')>1)
	);

--  Exceptions
UPDATE AD_Field
  SET IsReadOnly='N'
WHERE IsReadOnly='Y' AND AD_Field_ID IN (
    4394,   -- role/org
    6523    -- cycle/phase
    );

SELECT '<<< COMMIT >>>' FROM DUAL;
COMMIT;

--
SELECT '<<< Set Validation Rules for TableDir Client & Org >>>' FROM DUAL;

UPDATE AD_Column
SET AD_Val_Rule_ID=104
WHERE UPPER(ColumnName)='AD_ORG_ID' AND AD_Val_Rule_ID IS NULL AND AD_Reference_ID=19;

UPDATE AD_Column
SET IsUpdateable='Y'
WHERE IsUpdateable IS NULL;

UPDATE AD_Column
SET IsUpdateable = 'N'
WHERE (UPPER(ColumnName)='AD_CLIENT_ID' OR UPPER(ColumnName)='AD_ORG_ID')
AND IsUpdateable <> 'N';

---
SELECT '<<< Set Client/Org Login default of primary Tab >>>' FROM DUAL;

UPDATE AD_Column
SET DefaultValue='@#AD_Client_ID@'
WHERE ColumnName = 'AD_Client_ID' AND DefaultValue IS NULL
AND AD_Table_ID IN (SELECT AD_Table_ID FROM AD_Tab WHERE TabLevel = 0);

UPDATE AD_Column
SET DefaultValue='@#AD_Org_ID@'
WHERE ColumnName = 'AD_Org_ID' AND DefaultValue IS NULL
AND AD_Table_ID IN (SELECT AD_Table_ID FROM AD_Tab WHERE TabLevel = 0);


SELECT '<<< Set Client/Org default of dependent Tabs >>>' FROM DUAL;

UPDATE AD_Column
SET DefaultValue='@AD_Client_ID@'
WHERE ColumnName = 'AD_Client_ID' AND DefaultValue IS NULL
AND AD_Table_ID IN (SELECT AD_Table_ID FROM AD_Tab WHERE TabLevel > 0);

UPDATE AD_Column
SET DefaultValue='@AD_Org_ID@'
WHERE ColumnName = 'AD_Org_ID' AND DefaultValue IS NULL
AND AD_Table_ID IN (SELECT AD_Table_ID FROM AD_Tab WHERE TabLevel > 0);

--
SELECT '<<< Set Updateable if Views >>>' FROM DUAL;

UPDATE AD_Column
SET IsUpdateable='N'
WHERE IsUpdateable='Y'
AND AD_Table_ID IN (SELECT AD_Table_ID FROM AD_Table WHERE IsView='Y');

UPDATE AD_Column c
SET IsAlwaysUpdateable='N'
WHERE IsUpdateable='N' AND (IsAlwaysUpdateable='Y' OR IsAlwaysUpdateable IS NULL);

-- 
SELECT '<<< Set System ownership >>>' FROM DUAL;

UPDATE AD_Table SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Column SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
--
UPDATE AD_Window SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Tab SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Field SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Window_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Tab_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Field_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
--
UPDATE AD_Reference SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Ref_List SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Ref_Table SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Reference_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Ref_List_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Val_Rule SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
--
UPDATE AD_Element SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Element_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
--
UPDATE AD_Menu SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Menu_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
--

SELECT '<<< COMMIT >>>' FROM DUAL;
COMMIT;

--
SELECT '<<< Sync ColumnName & TableName_ID for TableDir >>>' FROM DUAL;

UPDATE AD_Element e
SET ColumnName = (SELECT t.TableName||'_ID' FROM AD_Table t 
	WHERE UPPER(e.ColumnName)=UPPER(t.TableName)||'_ID' 
	 AND NOT e.ColumnName=t.TableName||'_ID')
WHERE EXISTS (SELECT * FROM AD_Table t 
	WHERE UPPER(e.ColumnName)=UPPER(t.TableName)||'_ID' 
	 AND NOT e.ColumnName=t.TableName||'_ID');

SELECT '<<< COMMIT >>>' FROM DUAL;
COMMIT;

--
-- Update all: String length
SELECT '<<< Update Field length >>>' FROM DUAL;

-- sets all numeric to 22 and date to 7
UPDATE	AD_Column c
SET		FieldLength = (SELECT DataLength FROM temp_db_columns db 
						WHERE c.ad_column_id=db.ad_column_id)
WHERE	FieldLength <> (SELECT DataLength FROM temp_db_columns db 
						WHERE c.ad_column_id=db.ad_column_id);

SELECT '<<< Set Display Length >>>' FROM DUAL;

update ad_field set displaylength = 1		-- Checks, Radio
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id in (20, 24))
and displaylength != 1;

update ad_field set displaylength = 11		-- integer
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 11)
and displaylength != 11;

update ad_field set displaylength = 14		-- date fields
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 15)
and displaylength != 14;

update ad_field set displaylength = 20		-- datetime
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 16)
and displaylength != 20;

update ad_field set displaylength = 26		-- amount, number, quantity
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id in (12, 22, 29, 37))
and displaylength != 26;

update ad_field set displaylength = 26		-- account, location, search
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id in (25, 21, 30, 35))
and displaylength != 26;

update ad_field set displaylength = 14		-- starting point for picks
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id in (13,17,18,19,26,27))
and displaylength != 14;

update ad_field set displaylength = 60		-- Text /Long
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id IN (14,36))
and displaylength NOT IN (20,60);

update ad_field set displaylength = 60		-- Long Strings
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 10)
and displaylength > 60;

update ad_field set displaylength = 23		-- starting point for button
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 28)
and displaylength != 23;

update ad_field set displaylength = 20		-- document no
where name = 'Document No' and displaylength != 20;

update ad_field set displaylength = 20		-- value, password, alias
where (name like 'Value%' or name = 'Password' or name = 'Alias')
and displaylength > 20;


-- Normalize Length

update ad_field set displaylength = 5
where displaylength != 5 and (displaylength > 1 and displaylength < 6);

update ad_field set displaylength = 11		-- integers = 11
where displaylength is null or displaylength = 0 or displaylength = 10 or displaylength = 12;

update ad_field set displaylength = 17
where displaylength = 16 or displaylength = 18;

update ad_field set displaylength = 20
where displaylength = 19 or displaylength = 21;

update ad_field set displaylength = 23
where displaylength = 22 or displaylength = 24;

update ad_field set displaylength = 26		-- numbers = 26
where displaylength = 25 or displaylength = 27;

update ad_field set displaylength = 29
where displaylength > 29 and displaylength < 35;

select DisplayLength "Odd DisplayLength", Name from ad_field
where displaylength not in (1, 5, 6, 8, 11,14,15,17,20,23,26,29, 40, 60, 2000,4000)
order by 1;
--

--  Set to List where not list/button
UPDATE AD_Column SET AD_Reference_ID=17 
WHERE AD_Reference_ID NOT IN (17,28) AND ColumnName IN ('Posted','DocAction');
--  Set to _Posted Status
UPDATE AD_Column SET AD_Reference_Value_ID=234
WHERE AD_Reference_Value_ID IS NULL AND ColumnName='Posted';

--  Set parameters to empty selection
UPDATE AD_Process_Para
  SET DefaultValue = '-1'
WHERE ColumnName = 'AD_User_ID'
  AND DefaultValue IS NULL;
UPDATE AD_Process_Para
  SET DefaultValue = '0'
WHERE ColumnName IN ('C_BP_Group_ID','M_Product_Category_ID','M_Locator_ID') 
  AND DefaultValue IS NULL;

SELECT '<<< COMMIT >>>' FROM DUAL;
COMMIT;

-- Fini
SELECT '<<< FINI - Dropping Views >>>' FROM DUAL;
DROP VIEW Temp_db_columns;
--
COMMIT;