ad_window_copy.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE AD_Window_Copy
(
	PInstance_ID			IN NUMBER
)
/*************************************************************************
 * 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+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: AD_Window_Copy.sql,v 1.5 2003/02/07 05:49:59 jjanke Exp $
 ***
 * Title:	Copy Window Content (i.e Tabs and Fields)
 * Description:
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr						VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Record_ID						NUMBER;
	--	Parameter
	CURSOR Cur_Parameter (PInstance NUMBER) IS
		SELECT i.Record_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=PInstance
		AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
		ORDER BY p.SeqNo;
	--	Parameter Variables
	v_AD_Window_ID					NUMBER;
	v_NextNo_T						NUMBER;
	v_NextNo_F						NUMBER;
	v_NoOfTabs						NUMBER := 0;
	v_NoOfFields					NUMBER := 0;
	v_EntityType					CHAR(1);

	CURSOR Cur_Tabs (Window_ID NUMBER) IS
		SELECT	*
		FROM	AD_Tab
		WHERE	AD_Window_ID=Window_ID;

	CURSOR Cur_Fields (Tab_ID NUMBER) IS
		SELECT	* 
		FROM	AD_Field
		WHERE	AD_Tab_ID=Tab_ID;

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

	--	Get Parameters
	v_ResultStr := 'ReadingParameters';
	FOR p IN Cur_Parameter (PInstance_ID) LOOP
		v_Record_ID := p.Record_ID;
		IF (p.ParameterName = 'AD_Window_ID') THEN
			v_AD_Window_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  AD_Window_ID=' || v_AD_Window_ID);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
		END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);

	v_ResultStr := 'GetEntityType';
	SELECT	EntityType
	  INTO	v_EntityType
	FROM	AD_Window
	WHERE	AD_Window_ID = v_Record_ID;

	--	Record_ID is the Window_ID to copy to
	FOR tt IN Cur_Tabs (v_AD_Window_ID) LOOP
		--	Get next no
		AD_Sequence_Next('AD_Tab', tt.AD_Client_ID, v_NextNo_T);
		--	Insert
		INSERT INTO AD_Tab
			(AD_TAB_ID, AD_Window_ID,
			AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY,
			NAME, DESCRIPTION, HELP, AD_TABLE_ID, TabLevel,
			SEQNO, ISSINGLEROW, ISINFOTAB, ISTRANSLATIONTAB, ISREADONLY, 
			AD_COLUMN_ID, HASTREE, WHERECLAUSE, ORDERBYCLAUSE, COMMITWARNING,
			EntityType, AD_PROCESS_ID, PROCESSING)
		VALUES
			(v_NextNo_T, v_Record_ID,
			tt.AD_CLIENT_ID, tt.AD_ORG_ID, tt.ISACTIVE, SysDate, tt.CREATEDBY, SysDate, tt.UPDATEDBY,
			tt.NAME, tt.DESCRIPTION, tt.HELP, tt.AD_TABLE_ID, tt.TabLevel,
			tt.SEQNO, tt.ISSINGLEROW, tt.ISINFOTAB, tt.ISTRANSLATIONTAB, tt.ISREADONLY, 
			tt.AD_COLUMN_ID, tt.HASTREE, tt.WHERECLAUSE, tt.ORDERBYCLAUSE, tt.COMMITWARNING,
			v_EntityType, tt.AD_PROCESS_ID, tt.PROCESSING);
		-- 	Translate
		UPDATE AD_Tab_Trl t
		  SET Name = (SELECT Name FROM AD_Tab_Trl s WHERE s.AD_Tab_ID=tt.AD_Tab_ID
					AND s.AD_Language=t.AD_Language),
			Description = (SELECT Description FROM AD_Tab_Trl s WHERE s.AD_Tab_ID=tt.AD_Tab_ID
					AND s.AD_Language=t.AD_Language),
			Help = (SELECT Help FROM AD_Tab_Trl s WHERE s.AD_Tab_ID=tt.AD_Tab_ID
					AND s.AD_Language=t.AD_Language)
		WHERE t.AD_Tab_ID=v_NextNo_T;

		--	Copy Fields
		FOR f IN Cur_Fields (tt.AD_Tab_ID) LOOP
			--	Get next no
			AD_Sequence_Next('AD_Field', f.AD_Client_ID, v_NextNo_F);
			--	Insert
			INSERT INTO AD_Field
				(AD_Field_ID, AD_Tab_ID,
				AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
				Name, Description, Help, IsCentrallyMaintained, AD_Column_ID,
				ISDISPLAYED, DISPLAYLOGIC, DISPLAYLENGTH, ISREADONLY, SEQNO, SORTNO, 
				ISSAMELINE, ISHEADING, ISFIELDONLY, ISENCRYPTED, EntityType)
			VALUES
				(v_NextNo_F, v_NextNo_T,
				f.AD_Client_ID, f.AD_Org_ID, f.IsActive, SysDate, 0, SysDate, 0,
				f.Name, f.Description, f.Help, f.IsCentrallyMaintained, f.AD_Column_ID,
				f.ISDISPLAYED, f.DISPLAYLOGIC, f.DISPLAYLENGTH, f.ISREADONLY, f.SEQNO, f.SORTNO, 
				f.ISSAMELINE, f.ISHEADING, f.ISFIELDONLY, f.ISENCRYPTED, v_EntityType);
			-- update translation
			UPDATE AD_Field_Trl t
			  SET Name = (SELECT Name FROM AD_Field_Trl s WHERE s.AD_Field_ID=f.AD_Field_ID
						AND s.AD_Language=t.AD_Language),
				Description = (SELECT Description FROM AD_Field_Trl s WHERE s.AD_Field_ID=f.AD_Field_ID
						AND s.AD_Language=t.AD_Language),
				Help = (SELECT Help FROM AD_Field_Trl s WHERE s.AD_Field_ID=f.AD_Field_ID
						AND s.AD_Language=t.AD_Language)
			WHERE t.AD_Field_ID=v_NextNo_F;
			--
			v_NoOfFields := v_NoOfFields + 1;
		END LOOP;	--	Field
		v_NoOfTabs := v_NoOfTabs + 1;
	END LOOP;	--	Tab
	
	v_Message := '@Copied@=' || v_NoOfTabs || '/' || v_NoOfFields;

<<FINISH_PROCESS>>
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
	UPDATE	AD_PInstance
	SET Updated = SysDate,
		IsProcessing = 'N',
		Result = 1,					-- success
		ErrorMsg = v_Message
	WHERE	AD_PInstance_ID=PInstance_ID;
	COMMIT;
	RETURN;

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

END AD_Window_Copy;
/