ad_impformat_copy.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE AD_ImpFormat_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_ImpFormat_Copy.sql,v 1.2 2002/05/22 02:48:28 jjanke Exp $
 ***
 * Title:	Copy Import Format content (i.e. Rows) to another
 * Description:
 ************************************************************************/
AS
	--	Logistice
	ResultStr						VARCHAR2(2000);
	Message							VARCHAR2(2000);
	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
	AD_ImpFormat_ID					NUMBER;
	NoOfFields						NUMBER := 0;
	NextNo							NUMBER;

	CURSOR Cur_Rows (ImpFormat_ID NUMBER) IS
		SELECT 	* 
		FROM 	AD_ImpFormat_Row
		WHERE 	AD_ImpFormat_ID=ImpFormat_ID;

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

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

	--	Record_ID is the ImpFormat_ID to copy to
	FOR r IN Cur_Rows (AD_ImpFormat_ID) LOOP
		--	Get next no
		AD_Sequence_Next('AD_ImpFormat_Row', r.AD_Client_ID, NextNo);
		--	Insert
		INSERT INTO AD_ImpFormat_Row
			(AD_ImpFormat_Row_ID,AD_ImpFormat_ID,
			AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
			Name,SeqNo,AD_Column_ID,StartNo,EndNo,DataType,DataFormat,
			DecimalPoint,DivideBy100,ConstantValue,Callout)
		VALUES
			(NextNo, Record_ID,
			r.AD_Client_ID, r.AD_Org_ID, r.IsActive, SysDate, 0, SysDate, 0,
			r.Name,r.SeqNo,r.AD_Column_ID,r.StartNo,r.EndNo,r.DataType,r.DataFormat,
			r.DecimalPoint,r.DivideBy100,r.ConstantValue,r.Callout);
		--
		NoOfFields := NoOfFields + 1;
	END LOOP;
	Message := '@Copied@=' || NoOfFields;

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

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

END AD_ImpFormat_Copy;
/