ad_syncronize.sql

Java写的ERP系统

				  AND t.AD_Window_ID=w.AD_Window_ID
				  AND w.IsSOTrx='N');
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	--	Field Translations
	DBMS_OUTPUT.PUT_LINE('Synchronize PO Field Translations');
	UPDATE AD_Field_trl trl
		SET Name = (SELECT e.PO_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.PO_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.PO_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.PO_Name OR NVL(trl.Description,' ') <> NVL(e.PO_Description,' ') OR NVL(trl.Help,' ') <> NVL(e.PO_Help,' '))
				  AND e.PO_Name IS NOT NULL)
	  AND EXISTS (SELECT * FROM AD_Field f, AD_Tab t, AD_Window w
				WHERE trl.AD_Field_ID=f.AD_Field_ID
				  AND f.AD_Tab_ID=t.AD_Tab_ID
				  AND t.AD_Window_ID=w.AD_Window_ID
				  AND w.IsSOTrx='N');
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);


	--	Fields from Process
	DBMS_OUTPUT.PUT_LINE('Synchronize Field from Process');
	UPDATE AD_Field f
		SET Name = (SELECT p.Name FROM AD_Process p, AD_Column c WHERE p.AD_Process_ID=c.AD_Process_ID
					AND c.AD_Column_ID=f.AD_Column_ID),
			Description = (SELECT p.Description FROM AD_Process p, AD_Column c WHERE p.AD_Process_ID=c.AD_Process_ID
					AND c.AD_Column_ID=f.AD_Column_ID),
			Help = (SELECT p.Help FROM AD_Process p, AD_Column c WHERE p.AD_Process_ID=c.AD_Process_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_Process p, AD_Column c
				WHERE c.AD_Process_ID=p.AD_Process_ID AND f.AD_Column_ID=c.AD_Column_ID
				AND (f.Name<>p.Name OR NVL(f.Description,' ')<>NVL(p.Description,' ') OR NVL(f.Help,' ')<>NVL(p.Help,' ')));
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

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

	--	Sync Parameter ColumnName
	UPDATE	AD_Process_Para f
		SET	ColumnName = (SELECT e.ColumnName FROM AD_Element e
					WHERE UPPER(e.ColumnName)=UPPER(f.ColumnName))
	WHERE f.IsCentrallyMaintained='Y' AND f.IsActive='Y'
	  AND EXISTS (SELECT * FROM AD_Element e
		WHERE UPPER(e.ColumnName)=UPPER(f.ColumnName)
		AND e.ColumnName<>f.ColumnName);


	--	Paramenter Fields
	UPDATE	AD_Process_Para p
	  SET	IsCentrallyMaintained = 'N'
	WHERE	IsCentrallyMaintained <> 'N'
	  AND NOT EXISTS (SELECT * FROM AD_Element e WHERE p.ColumnName=e.ColumnName); 

	--	Parameter Fields
	DBMS_OUTPUT.PUT_LINE('Synchronize Process Parameter');
	UPDATE AD_Process_Para f
		SET Name = (SELECT e.Name FROM AD_Element e
					WHERE e.ColumnName=f.ColumnName),
			Description = (SELECT e.Description FROM AD_Element e
					WHERE e.ColumnName=f.ColumnName),
			Help = (SELECT e.Help FROM AD_Element e
					WHERE e.ColumnName=f.ColumnName),
			Updated = SysDate
	WHERE f.IsCentrallyMaintained='Y' AND f.IsActive='Y'
	  AND EXISTS (SELECT * FROM AD_Element e
				WHERE e.ColumnName=f.ColumnName
				  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);

	--	Parameter Translations
	DBMS_OUTPUT.PUT_LINE('Synchronize Process Parameter Trl');
	UPDATE AD_Process_Para_Trl trl
		SET Name = (SELECT et.Name FROM AD_Element_Trl et, AD_Element e, AD_Process_Para f
					WHERE et.AD_Language=trl.AD_Language AND et.AD_Element_ID=e.AD_Element_ID
					  AND e.ColumnName=f.ColumnName AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID),
			Description = (SELECT et.Description FROM AD_Element_Trl et, AD_Element e, AD_Process_Para f
					WHERE et.AD_Language=trl.AD_Language AND et.AD_Element_ID=e.AD_Element_ID
					  AND e.ColumnName=f.ColumnName AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID),
			Help = (SELECT et.Help FROM AD_Element_Trl et, AD_Element e, AD_Process_Para f
					WHERE et.AD_Language=trl.AD_Language AND et.AD_Element_ID=e.AD_Element_ID
					  AND e.ColumnName=f.ColumnName AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID),
			IsTranslated = (SELECT et.IsTranslated FROM AD_Element_Trl et, AD_Element e, AD_Process_Para f
					WHERE et.AD_Language=trl.AD_Language AND et.AD_Element_ID=e.AD_Element_ID
					  AND e.ColumnName=f.ColumnName AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID),
			Updated = SysDate
	WHERE EXISTS (SELECT * FROM AD_Element_Trl et, AD_Element e, AD_Process_Para f
					WHERE et.AD_Language=trl.AD_Language AND et.AD_Element_ID=e.AD_Element_ID
					  AND e.ColumnName=f.ColumnName AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID
					  AND f.IsCentrallyMaintained='Y' AND f.IsActive='Y'
					  AND (trl.Name <> et.Name OR NVL(trl.Description,' ') <> NVL(et.Description,' ') OR NVL(trl.Help,' ') <> NVL(et.Help,' ')));
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);


	--	Workflow Node - Window
	DBMS_OUTPUT.PUT_LINE('Synchronize Workflow Node from Window');
	UPDATE AD_WF_Node n
		SET Name = (SELECT w.Name FROM AD_Window w
					WHERE w.AD_Window_ID=n.AD_Window_ID),
			Description = (SELECT w.Description FROM AD_Window w
					WHERE w.AD_Window_ID=n.AD_Window_ID),
			Help = (SELECT w.Help FROM AD_Window w
					WHERE w.AD_Window_ID=n.AD_Window_ID)
	WHERE n.IsCentrallyMaintained = 'Y'
	  AND EXISTS  (SELECT * FROM AD_Window w
				WHERE w.AD_Window_ID=n.AD_Window_ID
				  AND (w.Name <> n.Name OR NVL(w.Description,' ') <> NVL(n.Description,' ') OR NVL(w.Help,' ') <> NVL(n.Help,' ')));
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	--	Workflow Translations - Window
	DBMS_OUTPUT.PUT_LINE('Synchronize Workflow Node Trl from Window Trl');
	UPDATE AD_WF_Node_Trl trl
		SET Name = (SELECT t.Name FROM AD_Window_trl t, AD_WF_Node n
					WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Window_ID=t.AD_Window_ID
					  AND trl.AD_Language=t.AD_Language),
			Description = (SELECT t.Description FROM AD_Window_trl t, AD_WF_Node n
					WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Window_ID=t.AD_Window_ID
					  AND trl.AD_Language=t.AD_Language),
			Help = (SELECT t.Help FROM AD_Window_trl t, AD_WF_Node n
					WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Window_ID=t.AD_Window_ID
					  AND trl.AD_Language=t.AD_Language)
	WHERE EXISTS (SELECT * FROM AD_Window_Trl t, AD_WF_Node n
				WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Window_ID=t.AD_Window_ID
				  AND trl.AD_Language=t.AD_Language AND n.IsCentrallyMaintained='Y' AND n.IsActive='Y'
				  AND (trl.Name <> t.Name OR NVL(trl.Description,' ') <> NVL(t.Description,' ') OR NVL(trl.Help,' ') <> NVL(t.Help,' ')));
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	--	Workflow Node - Form
	DBMS_OUTPUT.PUT_LINE('Synchronize Workflow Node from Form');
	UPDATE AD_WF_Node n
		SET (Name, Description, Help) = (SELECT f.Name, f.Description, f.Help 
				FROM AD_Form f
				WHERE f.AD_Form_ID=n.AD_Form_ID)
	WHERE n.IsCentrallyMaintained = 'Y'
	  AND EXISTS  (SELECT * FROM AD_Form f
				WHERE f.AD_Form_ID=n.AD_Form_ID
				  AND (f.Name <> n.Name OR NVL(f.Description,' ') <> NVL(n.Description,' ') OR NVL(f.Help,' ') <> NVL(n.Help,' ')));
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	--	Workflow Translations - Form
	DBMS_OUTPUT.PUT_LINE('Synchronize Workflow Node Trl from Form Trl');
	UPDATE AD_WF_Node_Trl trl
		SET (Name, Description, Help) = (SELECT t.Name, t.Description, t.Help
			FROM AD_Form_trl t, AD_WF_Node n
			WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Form_ID=t.AD_Form_ID
			  AND trl.AD_Language=t.AD_Language)
	WHERE EXISTS (SELECT * FROM AD_Form_Trl t, AD_WF_Node n
				WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Form_ID=t.AD_Form_ID
				  AND trl.AD_Language=t.AD_Language AND n.IsCentrallyMaintained='Y' AND n.IsActive='Y'
				  AND (trl.Name <> t.Name OR NVL(trl.Description,' ') <> NVL(t.Description,' ') OR NVL(trl.Help,' ') <> NVL(t.Help,' ')));
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);


	DBMS_OUTPUT.PUT_LINE('Synchronize PrintFormatItem Name from Element');
	UPDATE AD_PrintFormatItem pfi
	  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=pfi.AD_Column_ID)
	WHERE EXISTS (SELECT * 
		FROM AD_Element e, AD_Column c
		WHERE e.AD_Element_ID=c.AD_Element_ID
		  AND c.AD_Column_ID=pfi.AD_Column_ID
		  AND e.Name<>pfi.Name)
	  AND EXISTS (SELECT * FROM AD_Client 
		WHERE AD_Client_ID=pfi.AD_Client_ID AND IsMultiLingualDocument='Y');
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	DBMS_OUTPUT.PUT_LINE('Synchronize PrintFormatItem PrintName from Element');
	UPDATE AD_PrintFormatItem pfi
	  SET PrintName = (SELECT e.PrintName 
		FROM AD_Element e, AD_Column c
		WHERE e.AD_Element_ID=c.AD_Element_ID
		  AND c.AD_Column_ID=pfi.AD_Column_ID)
	WHERE EXISTS (SELECT * 
		FROM AD_Element e, AD_Column c, AD_PrintFormat pf
		WHERE e.AD_Element_ID=c.AD_Element_ID
		  AND c.AD_Column_ID=pfi.AD_Column_ID
		  AND LENGTH(pfi.PrintName) > 0
		  AND e.PrintName<>pfi.PrintName
		  AND pf.AD_PrintFormat_ID=pfi.AD_PrintFormat_ID
		  AND pf.IsForm='N' AND IsTableBased='Y')
	  AND EXISTS (SELECT * FROM AD_Client 
		WHERE AD_Client_ID=pfi.AD_Client_ID AND IsMultiLingualDocument='Y');
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);