r_request_process.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE R_Request_Process
(
	p_PInstance_ID    		IN NUMBER,
	p_R_Request_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: R_Request_Process.sql,v 1.5 2002/10/21 04:49:46 jjanke Exp $
 ***
 * Title:	Request Processing
 * Description:
 *			Copy Request into RequestAction
 *			Prepare for next action
 ************************************************************************/
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;
	--	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
	--
	v_NextNo						NUMBER(10);
	v_Rows							NUMBER(10);
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_R_Request_ID;
	END IF;
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);

	/**
	 *	Copy Request into RequestAction
	 */
	AD_Sequence_Next ('R_RequestAction', v_Record_ID, v_NextNo);
	INSERT INTO R_RequestAction (R_RequestAction_ID, R_Request_ID,
		AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
		AD_User_ID, ActionType,
		C_BPartner_ID, C_BPartner_Contact_ID,
		C_Order_ID, C_Invoice_ID, C_Payment_ID, M_Product_ID, C_Project_ID,
		R_MailText_ID, MailText, Result, DateNextAction)
	SELECT v_NextNo, R_Request_ID,
		AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
		AD_User_ID, ActionType,
		C_BPartner_ID, C_BPartner_Contact_ID,
		C_Order_ID, C_Invoice_ID, C_Payment_ID, M_Product_ID, C_Project_ID,
		R_MailText_ID, MailText, Result, DateNextAction
	FROM 	R_Request
	WHERE	R_Request_ID=v_Record_ID;

	v_Rows := SQL%ROWCOUNT;
	v_Message := '@R_Request_ID@ @Updated@ ' || v_Rows;

	IF (v_Rows <> 1) THEN
		v_Result := 0;
		GOTO FINISH_PROCESS;
	END IF;

	--	Close Request
	UPDATE	R_Request
	  SET	DateLastAction = Updated,
	  		LastResult = Result,
			DueType = R_Request_DueType (DateNextAction),
			Processed = 'Y',
			--
			Result = NULL,
			MailText = NULL,
			R_MailText_ID = NULL,
			ActionType = NULL
	WHERE	R_Request_ID=v_Record_ID
	  AND	ActionType = 'X';

	v_Rows := SQL%ROWCOUNT;
	v_Message := '@R_Request_ID@ @Updated@ ' || v_Rows;
	IF (v_Rows = 1) THEN
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Prepare for next action
	 */
	UPDATE	R_Request
	  SET	DateLastAction = Updated,
	  		LastResult = Result,
			DueType = R_Request_DueType (DateNextAction),
			--
			Result = NULL,
			MailText = NULL,
			R_MailText_ID = NULL,
			ActionType = NULL
	WHERE	R_Request_ID=v_Record_ID;

	v_Rows := SQL%ROWCOUNT;
	v_Message := '@R_Request_ID@ @Updated@ ' || v_Rows;

	IF (v_Rows <> 1) THEN
		v_Result := 0;
	END IF;


<<FINISH_PROCESS>>
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
	IF (p_PInstance_ID IS NOT NULL) THEN
	    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 R_Request_Process;
/