ad_syncronize.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE AD_Synchronize
(
	p_PInstance_ID			IN NUMBER --	DEFAULT NULL
)
/*************************************************************************
 * 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: AD_Syncronize.sql,v 1.11 2003/03/14 06:11:21 jjanke Exp $
 ***
 * Title:	Syncronize Application Dictionary
 * Description:
 *		Synchronize Elements
 *		Update Column and Field with Names from Element and Process
 *		Update Process Parameters from Elements
 *		Update Workflow Notes from Windows
 *		Update Menu from Window/Form/Process/Task
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr					VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Result						NUMBER := 1;	-- 0=failure
	v_Record_ID					NUMBER;
	v_AD_User_ID					NUMBER;
	--	Parameter
	CURSOR Cur_Parameter (pp_PInstance NUMBER) IS
		SELECT i.Record_ID, i.AD_User_ID,
			p.ParameterName, p.P_String, p.P_Number, p.P_Date
		FROM AD_PInstance i, AD_PInstance_Para p
		WHERE i.AD_PInstance_ID=pp_PInstance
		AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
		ORDER BY p.SeqNo;
	--	Parameter Variables

BEGIN
	IF (p_PInstance_ID IS NOT NULL) THEN
		--  Update AD_PInstance
		DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || p_PInstance_ID);
		v_ResultStr := 'PInstanceNotFound';
		UPDATE AD_PInstance
		SET Created = SysDate,
			IsProcessing = 'Y'
		WHERE AD_PInstance_ID=p_PInstance_ID;
		COMMIT;

		--	Get Parameters
		v_ResultStr := 'ReadingParameters';
		FOR p IN Cur_Parameter (p_PInstance_ID) LOOP
			v_Record_ID := p.Record_ID;
			v_AD_User_ID := p.AD_User_ID;
		END LOOP;	--	Get Parameter
		DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);
	END IF;

	---------------------------------------------------------------------------

	DBMS_OUTPUT.PUT_LINE('Adding missing Elements');
	DECLARE
		NextNo		NUMBER;
		CURSOR Cur_Column	IS
			SELECT DISTINCT ColumnName, Name, Description, Help
			FROM	AD_Column c
			WHERE NOT EXISTS 
				(SELECT * FROM AD_Element e
				WHERE UPPER(c.ColumnName)=UPPER(e.ColumnName));
		CURSOR Cur_Process	IS
			SELECT DISTINCT ColumnName, Name, Description, Help
			FROM	AD_Process_Para p
			WHERE NOT EXISTS 
				(SELECT * FROM AD_Element e
				WHERE UPPER(p.ColumnName)=UPPER(e.ColumnName));
		CC	Cur_Column%ROWTYPE;
	BEGIN
		DBMS_OUTPUT.PUT_LINE('Column:');
		FOR CC IN Cur_Column LOOP
			AD_Sequence_Next('AD_Element', 0, NextNo);	--	get ID
			INSERT INTO AD_ELEMENT
				(AD_ELEMENT_ID, AD_CLIENT_ID, AD_ORG_ID,
				ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY,
				ColumnName, Name, PrintName, Description, Help)
			VALUES
				(NextNo, 0, 0,
				'Y', SysDate, 0, SysDate, 0,
				CC.ColumnName, CC.Name, CC.Name, CC.Description, CC.Help);
			DBMS_OUTPUT.PUT_LINE('  added ' || cc.ColumnName);
			COMMIT;
		END LOOP;
		DBMS_OUTPUT.PUT_LINE('Parameter:');	
		FOR CC IN Cur_Process LOOP
			AD_Sequence_Next('AD_Element', 0, NextNo);	--	get ID
			INSERT INTO AD_ELEMENT
				(AD_ELEMENT_ID, AD_CLIENT_ID, AD_ORG_ID,
				ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY,
				ColumnName, Name, PrintName, Description, Help)
			VALUES
				(NextNo, 0, 0,
				'Y', SysDate, 0, SysDate, 0,
				CC.ColumnName, CC.Name, CC.Name, CC.Description, CC.Help);
			DBMS_OUTPUT.PUT_LINE('  added ' || cc.ColumnName);
			COMMIT;
		END LOOP;		
	END;


	DBMS_OUTPUT.PUT_LINE('Adding missing Element Translations');
	INSERT INTO AD_Element_Trl (AD_Element_ID, AD_Language, AD_Client_ID, AD_Org_ID,
		IsActive, Created, CreatedBy, Updated, UpdatedBy,
		Name, PrintName, Description, Help, IsTranslated)
	SELECT m.AD_Element_ID, l.AD_Language, m.AD_Client_ID, m.AD_Org_ID,
		m.IsActive, m.Created, m.CreatedBy, m.Updated, m.UpdatedBy,
		m.Name, m.PrintName, m.Description, m.Help, 'N'
	FROM	AD_Element m, AD_Language l
	WHERE	l.IsActive = 'Y' AND l.IsSystemLanguage = 'Y'
	  AND	AD_Element_ID || AD_Language NOT IN 
		(SELECT AD_Element_ID || AD_Language FROM AD_Element_Trl);
	DBMS_OUTPUT.PUT_LINE('  rows added: ' || SQL%ROWCOUNT);


	DBMS_OUTPUT.PUT_LINE('Deleting unused Elements');
	DELETE	AD_Element_Trl
	WHERE	AD_Element_ID IN
		(SELECT AD_Element_ID FROM AD_Element e 
		WHERE NOT EXISTS
			(SELECT * FROM AD_Column c WHERE UPPER(e.ColumnName)=UPPER(c.ColumnName))
		AND NOT EXISTS
			(SELECT * FROM AD_Process_Para p WHERE UPPER(e.ColumnName)=UPPER(p.ColumnName)));

	DELETE	AD_Element e
	WHERE NOT EXISTS
		(SELECT * FROM AD_Column c WHERE UPPER(e.ColumnName)=UPPER(c.ColumnName))
	AND NOT EXISTS
		(SELECT * FROM AD_Process_Para p WHERE UPPER(e.ColumnName)=UPPER(p.ColumnName));
	DBMS_OUTPUT.PUT_LINE('  rows deleted: ' || SQL%ROWCOUNT);


	DBMS_OUTPUT.PUT_LINE('Creating link from Element to Column');
	UPDATE	AD_Column c
	SET		AD_Element_id = 
				(SELECT AD_Element_ID FROM AD_Element e 
				WHERE UPPER(c.ColumnName)=UPPER(e.ColumnName))
	WHERE AD_Element_ID IS NULL;
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	---------------------------------------------------------------------------

	--	Columns
	DBMS_OUTPUT.PUT_LINE('Synchronize Column');
	UPDATE AD_Column c
		SET	ColumnName = (SELECT ColumnName FROM AD_Element e WHERE c.AD_Element_ID=e.AD_Element_ID),
			Name = (SELECT Name FROM AD_Element e WHERE c.AD_Element_ID=e.AD_Element_ID),
			Description = (SELECT Description FROM AD_Element e WHERE c.AD_Element_ID=e.AD_Element_ID),
			Help = (SELECT Help FROM AD_Element e WHERE c.AD_Element_ID=e.AD_Element_ID),
			Updated = SysDate
	WHERE EXISTS (SELECT * FROM AD_Element e 
				WHERE c.AD_Element_ID=e.AD_Element_ID
				  AND (c.ColumnName <> e.ColumnName OR c.Name <> e.Name 
					OR NVL(c.Description,' ') <> NVL(e.Description,' ') OR NVL(c.Help,' ') <> NVL(e.Help,' ')));
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	--	Fields should now be syncronized
	DBMS_OUTPUT.PUT_LINE('Synchronize Field');
	UPDATE AD_Field f
		SET Name = (SELECT e.Name FROM AD_Element e, AD_Column c
					WHERE e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=f.AD_Column_ID),
			Description = (SELECT e.Description FROM AD_Element e, AD_Column c
					WHERE e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=f.AD_Column_ID),
			Help = (SELECT e.Help FROM AD_Element e, AD_Column c
					WHERE e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=f.AD_Column_ID),
			Updated = SysDate
	WHERE f.IsCentrallyMaintained='Y' AND f.IsActive='Y'
	  AND EXISTS (SELECT * FROM AD_Element e, AD_Column c
				WHERE f.AD_Column_ID=c.AD_Column_ID
				  AND c.AD_Element_ID=e.AD_Element_ID AND c.AD_Process_ID IS NULL
				  AND (f.Name <> e.Name OR NVL(f.Description,' ') <> NVL(e.Description,' ') OR NVL(f.Help,' ') <> NVL(e.Help,' ')));
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	--	Field Translations
	DBMS_OUTPUT.PUT_LINE('Synchronize Field Translations');
	UPDATE AD_Field_trl trl
		SET Name = (SELECT e.Name FROM AD_Element_trl e, AD_Column c, AD_Field f
					WHERE e.AD_Language=trl.AD_Language AND e.AD_Element_ID=c.AD_Element_ID 
					  AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),
			Description = (SELECT e.Description FROM AD_Element_trl e, AD_Column c, AD_Field f
					WHERE e.AD_Language=trl.AD_Language AND e.AD_Element_ID=c.AD_Element_ID 
					  AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),
			Help = (SELECT e.Help FROM AD_Element_trl e, AD_Column c, AD_Field f
					WHERE e.AD_Language=trl.AD_Language AND e.AD_Element_ID=c.AD_Element_ID 
					  AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),
			IsTranslated = (SELECT e.IsTranslated FROM AD_Element_trl e, AD_Column c, AD_Field f
					WHERE e.AD_Language=trl.AD_Language AND e.AD_Element_ID=c.AD_Element_ID 
					  AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),
			Updated = SysDate
	WHERE EXISTS (SELECT * FROM AD_Field f, AD_Element_trl e, AD_Column c
				WHERE trl.AD_Field_ID=f.AD_Field_ID
				  AND f.AD_Column_ID=c.AD_Column_ID
				  AND c.AD_Element_ID=e.AD_Element_ID AND c.AD_Process_ID IS NULL
				  AND trl.AD_Language=e.AD_Language
				  AND f.IsCentrallyMaintained='Y' AND f.IsActive='Y'
				  AND (trl.Name <> e.Name OR NVL(trl.Description,' ') <> NVL(e.Description,' ') OR NVL(trl.Help,' ') <> NVL(e.Help,' ')));
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	--	Fields should now be syncronized
	DBMS_OUTPUT.PUT_LINE('Synchronize PO Field');
	UPDATE AD_Field f
		SET Name = (SELECT e.PO_Name FROM AD_Element e, AD_Column c
					WHERE e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=f.AD_Column_ID),
			Description = (SELECT e.PO_Description FROM AD_Element e, AD_Column c
					WHERE e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=f.AD_Column_ID),
			Help = (SELECT e.PO_Help FROM AD_Element e, AD_Column c
					WHERE e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=f.AD_Column_ID),
			Updated = SysDate
	WHERE f.IsCentrallyMaintained='Y' AND f.IsActive='Y'
	  AND EXISTS (SELECT * FROM AD_Element e, AD_Column c
				WHERE f.AD_Column_ID=c.AD_Column_ID
				  AND c.AD_Element_ID=e.AD_Element_ID AND c.AD_Process_ID IS NULL
				  AND (f.Name <> e.PO_Name OR NVL(f.Description,' ') <> NVL(e.PO_Description,' ') OR NVL(f.Help,' ') <> NVL(e.PO_Help,' '))
				  AND e.PO_Name IS NOT NULL)
	  AND EXISTS (SELECT * FROM AD_Tab t, AD_Window w
				WHERE f.AD_Tab_ID=t.AD_Tab_ID