ad_syncronize.sql

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

	DBMS_OUTPUT.PUT_LINE('Reset PrintFormatItem Trl where not used in base table');
	UPDATE AD_PrintFormatItem_Trl trl
	  SET PrintName = NULL
	WHERE PrintName IS NOT NULL
	  AND EXISTS (SELECT *
		FROM AD_PrintFormatItem pfi
		WHERE pfi.AD_PrintFormatItem_ID=trl.AD_PrintFormatItem_ID 
          AND pfi.IsCentrallyMaintained='Y'
		  AND (LENGTH (pfi.PrintName) = 0 OR pfi.PrintName IS NULL));
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

/**
SELECT 	e.PrintName "Element", pfi.PrintName "FormatItem", trl.AD_Language, trl.PrintName "Trl"
FROM 	AD_Element e
  INNER JOIN AD_Column c ON (e.AD_Element_ID=c.AD_Element_ID)
  INNER JOIN AD_PrintFormatItem pfi ON (c.AD_Column_ID=pfi.AD_Column_ID)
  INNER JOIN AD_PrintFormatItem_Trl trl ON (pfi.AD_PrintFormatItem_ID=trl.AD_PrintFormatItem_ID)
WHERE pfi.AD_PrintFormatItem_ID=?
**/

	--	Sync Names - Window
	DBMS_OUTPUT.PUT_LINE('Synchronizing Menu with Window');
	UPDATE	AD_Menu m
	SET		Name = (SELECT Name FROM AD_Window w WHERE m.AD_Window_ID=w.AD_Window_ID),
			Description = (SELECT Description FROM AD_Window w WHERE m.AD_Window_ID=w.AD_Window_ID)
	WHERE	AD_Window_ID IS NOT NULL
		AND Action = 'W';
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	UPDATE	AD_Menu_Trl mt
	SET		Name = (SELECT wt.Name FROM AD_Window_Trl wt, AD_Menu m 
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Window_ID=wt.AD_Window_ID 
					AND mt.AD_Language=wt.AD_Language),
			Description = (SELECT wt.Description FROM AD_Window_Trl wt, AD_Menu m 
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Window_ID=wt.AD_Window_ID 
					AND mt.AD_Language=wt.AD_Language),
			IsTranslated = (SELECT wt.IsTranslated FROM AD_Window_Trl wt, AD_Menu m 
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Window_ID=wt.AD_Window_ID 
					AND mt.AD_Language=wt.AD_Language)
	WHERE EXISTS (SELECT * FROM AD_Window_Trl wt, AD_Menu m 
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Window_ID=wt.AD_Window_ID 
					AND mt.AD_Language=wt.AD_Language
					AND m.AD_Window_ID IS NOT NULL
					AND m.Action = 'W');
	DBMS_OUTPUT.PUT_LINE('  trl rows updated: ' || SQL%ROWCOUNT);

	--	Sync Names - Process
	DBMS_OUTPUT.PUT_LINE('Synchronizing Menu with Processes');
	UPDATE	AD_Menu m
	SET		Name = (SELECT p.Name FROM AD_Process p WHERE m.AD_Process_ID=p.AD_Process_ID),
			Description = (SELECT p.Description FROM AD_Process p WHERE m.AD_Process_ID=p.AD_Process_ID)
	WHERE	m.AD_Process_ID IS NOT NULL
	  AND	m.Action IN ('R', 'P');
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	UPDATE	AD_Menu_Trl mt
	SET		Name = (SELECT pt.Name FROM AD_Process_Trl pt, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Process_ID=pt.AD_Process_ID
					AND mt.AD_Language=pt.AD_Language),
			Description = (SELECT pt.Description FROM AD_Process_Trl pt, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Process_ID=pt.AD_Process_ID
					AND mt.AD_Language=pt.AD_Language),
			IsTranslated = (SELECT pt.IsTranslated FROM AD_Process_Trl pt, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Process_ID=pt.AD_Process_ID
					AND mt.AD_Language=pt.AD_Language)
	WHERE EXISTS (SELECT * FROM AD_Process_Trl pt, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Process_ID=pt.AD_Process_ID
					AND mt.AD_Language=pt.AD_Language
					AND m.AD_Process_ID IS NOT NULL
					AND	Action IN ('R', 'P'));
	DBMS_OUTPUT.PUT_LINE('  trl rows updated: ' || SQL%ROWCOUNT);

	--	Sync Names = Form
	DBMS_OUTPUT.PUT_LINE('Synchronizing Menu with Forms');
	UPDATE	AD_Menu m
	SET		Name = (SELECT Name FROM AD_Form f WHERE m.AD_Form_ID=f.AD_Form_ID),
			Description = (SELECT Description FROM AD_Form f WHERE m.AD_Form_ID=f.AD_Form_ID)
	WHERE	AD_Form_ID IS NOT NULL
	  AND	Action = 'X';
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	UPDATE	AD_Menu_Trl mt
	SET		Name = (SELECT ft.Name FROM AD_Form_Trl ft, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Form_ID=ft.AD_Form_ID
					AND mt.AD_Language=ft.AD_Language),
			Description = (SELECT ft.Description FROM AD_Form_Trl ft, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Form_ID=ft.AD_Form_ID
					AND mt.AD_Language=ft.AD_Language),
			IsTranslated = (SELECT ft.IsTranslated FROM AD_Form_Trl ft, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Form_ID=ft.AD_Form_ID
					AND mt.AD_Language=ft.AD_Language)
	WHERE EXISTS (SELECT * FROM AD_Form_Trl ft, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Form_ID=ft.AD_Form_ID
					AND mt.AD_Language=ft.AD_Language
					AND m.AD_Form_ID IS NOT NULL
					AND	Action = 'X');
	DBMS_OUTPUT.PUT_LINE('  trl rows updated: ' || SQL%ROWCOUNT);

	--	Sync Names - Workflow
	DBMS_OUTPUT.PUT_LINE('Synchronizing Menu with Workflows');
	UPDATE	AD_Menu m
	SET		Name = (SELECT p.Name FROM AD_Workflow p WHERE m.AD_Workflow_ID=p.AD_Workflow_ID),
			Description = (SELECT p.Description FROM AD_Workflow p WHERE m.AD_Workflow_ID=p.AD_Workflow_ID)
	WHERE	m.AD_Workflow_ID IS NOT NULL
	  AND	m.Action = 'F';
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	UPDATE	AD_Menu_Trl mt
	SET		Name = (SELECT pt.Name FROM AD_Workflow_Trl pt, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Workflow_ID=pt.AD_Workflow_ID
					AND mt.AD_Language=pt.AD_Language),
			Description = (SELECT pt.Description FROM AD_Workflow_Trl pt, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Workflow_ID=pt.AD_Workflow_ID
					AND mt.AD_Language=pt.AD_Language),
			IsTranslated = (SELECT pt.IsTranslated FROM AD_Workflow_Trl pt, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Workflow_ID=pt.AD_Workflow_ID
					AND mt.AD_Language=pt.AD_Language)
	WHERE EXISTS (SELECT * FROM AD_Workflow_Trl pt, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Workflow_ID=pt.AD_Workflow_ID
					AND mt.AD_Language=pt.AD_Language
					AND m.AD_Workflow_ID IS NOT NULL
					AND	Action = 'F');
	DBMS_OUTPUT.PUT_LINE('  trl rows updated: ' || SQL%ROWCOUNT);

	--	Sync Names = Task
	DBMS_OUTPUT.PUT_LINE('Synchronizing Menu with Tasks');
	UPDATE	AD_Menu m
	SET		Name = (SELECT Name FROM AD_Task f WHERE m.AD_Task_ID=f.AD_Task_ID),
			Description = (SELECT Description FROM AD_Task f WHERE m.AD_Task_ID=f.AD_Task_ID)
	WHERE	AD_Task_ID IS NOT NULL
	  AND	Action = 'T';
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);

	UPDATE	AD_Menu_Trl mt
	SET		Name = (SELECT ft.Name FROM AD_Task_Trl ft, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Task_ID=ft.AD_Task_ID
					AND mt.AD_Language=ft.AD_Language),
			Description = (SELECT ft.Description FROM AD_Task_Trl ft, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Task_ID=ft.AD_Task_ID
					AND mt.AD_Language=ft.AD_Language),
			IsTranslated = (SELECT ft.IsTranslated FROM AD_Task_Trl ft, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Task_ID=ft.AD_Task_ID
					AND mt.AD_Language=ft.AD_Language)
	WHERE EXISTS (SELECT * FROM AD_Task_Trl ft, AD_Menu m
					WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Task_ID=ft.AD_Task_ID
					AND mt.AD_Language=ft.AD_Language
					AND m.AD_Task_ID IS NOT NULL
					AND	Action = 'T');
	DBMS_OUTPUT.PUT_LINE('  trl rows updated: ' || SQL%ROWCOUNT);

    --  Column Name + Element
	DBMS_OUTPUT.PUT_LINE('Synchronizing Column with Element');
    UPDATE AD_Column c
      SET (Name,Description,Help) = 
        (SELECT e.Name,e.Description,e.Help 
        FROM AD_Element e WHERE c.AD_Element_ID=e.AD_Element_ID)
    WHERE EXISTS 
        (SELECT * FROM AD_Element e 
        WHERE c.AD_Element_ID=e.AD_Element_ID
          AND c.Name<>e.Name);
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);
    UPDATE AD_Column_Trl ct
      SET Name = (SELECT e.Name
        FROM AD_Column c INNER JOIN AD_Element_Trl e ON (c.AD_Element_ID=e.AD_Element_ID)
        WHERE ct.AD_Column_ID=c.AD_Column_ID AND ct.AD_Language=e.AD_Language)
    WHERE EXISTS 
        (SELECT * FROM AD_Column c INNER JOIN AD_Element_Trl e ON (c.AD_Element_ID=e.AD_Element_ID)
        WHERE ct.AD_Column_ID=c.AD_Column_ID AND ct.AD_Language=e.AD_Language
          AND ct.Name<>e.Name);
	DBMS_OUTPUT.PUT_LINE('  trl rows updated: ' || SQL%ROWCOUNT);
    
    
    --  Table Name + Element
	DBMS_OUTPUT.PUT_LINE('Synchronizing Table with Element');
    UPDATE AD_Table t
      SET (Name,Description) = (SELECT e.Name,e.Description FROM AD_Element e 
        WHERE t.TableName||'_ID'=e.ColumnName)
    WHERE EXISTS (SELECT * FROM AD_Element e 
        WHERE t.TableName||'_ID'=e.ColumnName
          AND t.Name<>e.Name);
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);
    UPDATE AD_Table_Trl tt
      SET Name = (SELECT e.Name 
        FROM AD_Table t INNER JOIN AD_Element ex ON (t.TableName||'_ID'=ex.ColumnName)
          INNER JOIN AD_Element_Trl e ON (ex.AD_Element_ID=e.AD_Element_ID)
        WHERE tt.AD_Table_ID=t.AD_Table_ID AND tt.AD_Language=e.AD_Language)
    WHERE EXISTS (SELECT * 
        FROM AD_Table t INNER JOIN AD_Element ex ON (t.TableName||'_ID'=ex.ColumnName)
          INNER JOIN AD_Element_Trl e ON (ex.AD_Element_ID=e.AD_Element_ID)
        WHERE tt.AD_Table_ID=t.AD_Table_ID AND tt.AD_Language=e.AD_Language
          AND tt.Name<>e.Name);
	DBMS_OUTPUT.PUT_LINE('  trl rows updated: ' || SQL%ROWCOUNT);

    --  Trl Table Name + Element
    UPDATE AD_Table t
      SET (Name,Description) = (SELECT e.Name||' Trl', e.Description 
        FROM AD_Element e 
        WHERE SUBSTR(t.TableName,1,LENGTH(t.TableName)-4)||'_ID'=e.ColumnName)
    WHERE TableName LIKE '%_Trl'
      AND EXISTS (SELECT * FROM AD_Element e 
        WHERE SUBSTR(t.TableName,1,LENGTH(t.TableName)-4)||'_ID'=e.ColumnName
          AND t.Name<>e.Name);
	DBMS_OUTPUT.PUT_LINE('  rows updated: ' || SQL%ROWCOUNT);
    UPDATE AD_Table_Trl tt
      SET Name = (SELECT e.Name || ' **'
        FROM AD_Table t INNER JOIN AD_Element ex ON (SUBSTR(t.TableName,1,LENGTH(t.TableName)-4)||'_ID'=ex.ColumnName)
          INNER JOIN AD_Element_Trl e ON (ex.AD_Element_ID=e.AD_Element_ID)
        WHERE tt.AD_Table_ID=t.AD_Table_ID AND tt.AD_Language=e.AD_Language)
    WHERE EXISTS (SELECT * 
        FROM AD_Table t INNER JOIN AD_Element ex ON (SUBSTR(t.TableName,1,LENGTH(t.TableName)-4)||'_ID'=ex.ColumnName)
          INNER JOIN AD_Element_Trl e ON (ex.AD_Element_ID=e.AD_Element_ID)
        WHERE tt.AD_Table_ID=t.AD_Table_ID AND tt.AD_Language=e.AD_Language
          AND t.TableName LIKE '%_Trl'
          AND tt.Name<>e.Name);
	DBMS_OUTPUT.PUT_LINE('  trl rows updated: ' || SQL%ROWCOUNT);

    /** Remaining tables
    SELECT Name, TableName FROM AD_Table t WHERE Name=TableName ORDER BY 1
    **/


<<FINISH_PROCESS>>
	IF (p_PInstance_ID IS NOT NULL) THEN
		--  Update AD_PInstance
		DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
		UPDATE	AD_PInstance
		SET Updated = SysDate,
			IsProcessing = 'N',
			Result = v_Result,			-- 1=success
			ErrorMsg = v_Message
		WHERE	AD_PInstance_ID=p_PInstance_ID;
	END IF;
	COMMIT;
	RETURN;

EXCEPTION
	WHEN  OTHERS THEN
		v_ResultStr := v_ResultStr || ': ' || SQLERRM || ' - ' || v_Message;
		DBMS_OUTPUT.PUT_LINE(v_ResultStr);
		ROLLBACK;
		IF (p_PInstance_ID IS NOT NULL) THEN
			UPDATE	AD_PInstance
			SET Updated = SysDate,
				IsProcessing = 'N',
				Result = 0,				-- failure
				ErrorMsg = v_ResultStr
			WHERE	AD_PInstance_ID=p_PInstance_ID;
			COMMIT;
		END IF;
		RETURN;

END AD_Synchronize;
/