c_invoice_createreceipt.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_Invoice_CreateReceipt
(
	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+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_Invoice_CreateReceipt.sql,v 1.2 2002/05/22 02:48:28 jjanke Exp $
 ***
 * Title:	Create Receipt (Shipment) From Invoice
 * Description:
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr						VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_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
	p_M_Locator_ID					NUMBER;
	--
	v_DocStatus						CHAR(2);
	v_InOut_ID						NUMBER;

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
		IF (p.ParameterName = 'M_Locator_ID') THEN
 			p_M_Locator_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  M_Locator_ID=' || p_M_Locator_ID);
		END IF;
		v_Record_ID := p.Record_ID;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);



	--	Read Information from Record
	v_ResultStr := 'Reading Record';
	SELECT	DocStatus
	  INTO	v_DocStatus
  	FROM 	C_Invoice
	WHERE	C_Invoice_ID = v_Record_ID;

	--	Invoice must be complete
	IF (v_DocStatus NOT IN ('CO','CL')) THEN
		v_Message := '@InvoiceCreateDocNotCompleted@';
		GOTO FINISH_PROCESS;
   	END IF;

	--	Check, if we have a Shipment already
	DECLARE
		v_DocumentNo				VARCHAR2(60);
	BEGIN
	   	v_ResultStr := 'Check Receipt exists';	
		SELECT 	M_InOut_ID, DocumentNo
		  INTO	v_InOut_ID, v_DocumentNo
	 	FROM 	M_InOut 
		WHERE C_Invoice_ID=v_Record_ID AND ROWNUM=1;
		v_Message := '@InvoiceCreateDocAlreadyExists@ = ' || v_DocumentNo || ' (' || v_InOut_ID || ')';
		GOTO FINISH_PROCESS;
	EXCEPTION
	    WHEN OTHERS THEN NULL;
	END;

	--	Create (and post) the shipment
	v_ResultStr := 'Create Shipment';
	M_InOut_Create (NULL, NULL, v_Record_ID, 'Y', v_InOut_ID, p_M_Locator_ID);
	DBMS_OUTPUT.PUT_LINE('  Shipment - ' || v_InOut_ID);
	IF (v_InOut_ID = 0) THEN
		v_Message := '@InOutCreateFailed@';
		GOTO FINISH_PROCESS;
	END IF;


<<FINISH_PROCESS>>
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
    UPDATE  AD_PInstance
    SET Updated = SysDate,
        IsProcessing = 'N',
        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 C_Invoice_CreateReceipt;
/