r_request_reopen.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE R_Request_ReOpen
(
	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-2002 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: R_Request_ReOpen.sql,v 1.2 2003/01/12 04:17:33 jjanke Exp $
 ***
 * Title:	Reopen Requests
 * 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
	p_R_Request_ID					NUMBER(10);
	v_count							NUMBER(10) := 0;
	v_R_RequestAction_ID			NUMBER(10);

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 = 'R_Request_ID') THEN
 			p_R_Request_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  R_Request_ID=' || p_R_Request_ID);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
	 	END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || p_Record_ID);

	IF (p_R_Request_ID IS NOT NULL) THEN
		v_ResultStr := 'Updating';
		UPDATE 	R_Request 
		  SET 	Processed = 'N' 
		WHERE R_Request_ID = p_R_Request_ID
		  AND 	Processed = 'Y';
		v_count := SQL%ROWCOUNT;
		IF (v_count = 1) THEN
			v_ResultStr := 'Inserting Action';
			AD_Sequence_Next ('R_RequestAction', p_R_Request_ID, v_R_RequestAction_ID);
			DBMS_OUTPUT.PUT_LINE('Inserting R_RequestAction_ID=' || v_R_RequestAction_ID);
			INSERT INTO R_RequestAction
				(R_RequestAction_ID, AD_Client_ID,AD_Org_ID,
				IsActive,CREATED,CREATEDBY,UPDATED,UPDATEDBY,
				R_Request_ID,AD_USER_ID,ACTIONTYPE,RESULT)
			SELECT
				v_R_RequestAction_ID, AD_Client_ID,AD_Org_ID,
				'Y',SysDate,p_AD_User_ID,SysDate,p_AD_User_ID,
			 	p_R_Request_ID, p_AD_User_ID, 'X', '** ReOpended **'
		   	FROM R_Request
			WHERE R_Request_ID = p_R_Request_ID;
		END IF;
	END IF;

	v_Message := '@Updated@=' || v_count;


<<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 R_Request_ReOpen;
/