template.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE Template
(
	p_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+CPM
 * Copyright (C) 1999-2003 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: Template.sql,v 1.9 2003/02/18 03:33:22 jjanke Exp $
 ***
 * Title:	
 * Description:
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr					VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Result						NUMBER := 1;	-- 0=failure
	p_Record_ID					NUMBER;
	p_AD_User_ID					NUMBER;
	--	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

BEGIN
	--  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
		p_Record_ID := p.Record_ID;
		p_AD_User_ID := p.AD_User_ID;
	--	IF (p.ParameterName = 'xx') THEN
	--		p_xx := p.P_String;
	--		DBMS_OUTPUT.PUT_LINE('  xx=' || p_xx);
	--	ELSE
	--		DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
	-- 	END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || p_Record_ID);


	--	Log info - Log_ID must be unique in instance
	--		P_Date/P_ID/P_Number/P_Msg are optional. 
	INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID, P_Msg)
	  VALUES (p_PInstance_ID, AD_PInstance_Seq.NextVal, '@Created@');

	--	Summary info	SQL%ROWCOUNT
	-- 	DBMS_OUTPUT.PUT_LINE('  Count=' || SQL%ROWCOUNT);
	v_Message := '@Totals@ @Created@ @Updated@';


<<FINISH_PROCESS>>
	--  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;
	COMMIT;
	RETURN;

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

END Template;
/