ad_table_import.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE AD_Table_Import
(
	p_PInstance_ID    		IN NUMBER,
	p_AD_Table_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_Table_Import.sql,v 1.8 2003/01/18 05:34:25 jjanke Exp $
 ***
 * Title:	Import Table Column Definition
 * Description:
 *			Create Columns of Table not existing as a Dictionary Column
 ************************************************************************/
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 := 0;
	--	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
	--	
   	CURSOR Cur_Column (tableID NUMBER)	IS
		SELECT  Column_Name, Data_Type, Data_Length, Nullable, AD_Table_ID, 
			Table_Name, t.EntityType
        FROM    User_Tab_Columns uc, AD_Table t
        WHERE   uc.Table_Name=UPPER(t.TableName)
        	AND NOT EXISTS 
				(SELECT * FROM AD_Table t, AD_Column c 
				WHERE t.AD_Table_ID=c.AD_Table_ID
				  AND uc.Table_Name=UPPER(t.TableName)
				  AND uc.Column_Name=UPPER(c.ColumnName))
			AND (tableID=t.AD_Table_ID OR tableID IS NULL);
   	--
	v_NextNo		NUMBER(10);
	v_count			NUMBER(10) := 0;

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
	ELSE
		v_Record_ID := p_AD_Table_ID;
	END IF;
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);

	FOR CC IN Cur_Column (v_Record_ID) LOOP
		AD_Sequence_Next('AD_Column', 0, v_NextNo);	--	get ID
		INSERT INTO AD_COLUMN
			(AD_COLUMN_ID, AD_CLIENT_ID, AD_ORG_ID,
			ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY,
			NAME, VERSION, COLUMNNAME,
			AD_TABLE_ID, AD_REFERENCE_ID,
			FIELDLENGTH, ISKEY, ISPARENT, ISMANDATORY, ISIDENTIFIER,
			SEQNO, ISTRANSLATED, ISENCRYPTED, EntityType)
		VALUES
			(v_NextNo, 0, 0,
			'Y', SysDate, 0, SysDate, 0,
			InitCap(CC.Column_Name), 1, InitCap(CC.Column_Name),
			CC.AD_Table_ID, 
			DECODE(CC.Data_Type, 'NUMBER', 11,	--	Integer 
				'CHAR', 20, 					--	YesNo
				'DATE', 15, 					--	Date
				10),							--	String
			CC.Data_Length, 'N', 'N', DECODE(CC.Nullable, 'Y', 'N', 'Y'), 'N',
			0, 'N', 'N', CC.EntityType);
		--
		v_count := v_count + 1;
		IF (p_PInstance_ID IS NOT NULL) THEN
			INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID, 
				P_Msg)
			  VALUES (p_PInstance_ID, AD_PInstance_Seq.NextVal, 
			  	'@Created@ @AD_Column_ID@ ' || InitCap(CC.Table_Name) || '.' || InitCap(CC.Column_Name));
		ELSE
            DBMS_OUTPUT.PUT_LINE('adding Table ' || InitCap(CC.Table_Name) || ' Column ' || InitCap(CC.Column_Name));
		END IF;
	END LOOP;   --  All new columns

	--	Summary info
	v_Message := '@Created@ = ' || v_count;


<<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;
	ELSE
		DBMS_OUTPUT.PUT_LINE('Finished ' || v_Message);
	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;
		END IF;
        COMMIT;
        RETURN;

END AD_Table_Import;
/