m_inout_cancel.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE M_InOut_Cancel
(
	PInstance_ID    		IN NUMBER,
	Order_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+CRM
 * Copyright (C) 1999-2002 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: M_InOut_Cancel.sql,v 1.2 2002/08/13 05:27:36 jjanke Exp $
 ***
 * Title:   Cancel Delivery of Order by reversing it
 * 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

	--
	CURSOR Cur_InOut (OrderID NUMBER) IS
		SELECT	*
		FROM	M_InOut
		WHERE	C_Order_ID = OrderID
		  AND	DocStatus <> 'RE'		-- Not the existing reversal trx
		FOR UPDATE;

BEGIN
    --  Update AD_PInstance
	IF (PInstance_ID IS NOT NULL) THEN
		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;
		END LOOP;	--	Get Parameter
		DBMS_OUTPUT.PUT_LINE('  Record_ID=' || Record_ID);
	ELSE
		Record_ID := Order_ID;
	END IF;


	--	For all Deliveries of Order
	FOR s IN Cur_InOut (Record_ID) LOOP
		DBMS_OUTPUT.PUT_LINE('Cancel Shipment ' || s.DocumentNo || ', ID=' || s.M_InOut_ID);

		--	Cancel Order
		UPDATE	M_InOut
		  SET	DocAction = 'RC',		--	Reverse Correction
		  		Processed = 'N',
		  		Updated = SysDate
		WHERE M_InOut_ID = s.M_InOut_ID;
		M_InOut_Post (NULL, s.M_InOut_ID);

	END LOOP;



<<FINISH_PROCESS>>
	IF (PInstance_ID IS NOT NULL) THEN
		--  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;
	END IF;
    RETURN;

EXCEPTION
    WHEN  OTHERS THEN
		ResultStr := ResultStr || ': ' || SQLERRM || ' - ' || Message;
		DBMS_OUTPUT.PUT_LINE(ResultStr);
		IF (PInstance_ID IS NOT NULL) THEN
    	    UPDATE  AD_PInstance
        	SET Updated = SysDate,
	            IsProcessing = 'N',
    	        Result = 0,             -- failure
        	    ErrorMsg = ResultStr
	        WHERE   AD_PInstance_ID=PInstance_ID;
    	    COMMIT;
		ELSE
			DECLARE
				v_Code 	NUMBER 			:= SQLCode;
			BEGIN
				INSERT INTO DBA_ErrorLog (DBA_ErrorLog_ID,Created,Code,Msg,Info)
				VALUES (DBA_ErrorLog_Seq.NextVal,SysDate,v_Code,ResultStr,'M_InOut_Cancel');
			END;
		END IF;
        RETURN;

END M_InOut_Cancel;
/