m_inventory_listupdate.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE M_Inventory_ListUpdate
(
	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: M_Inventory_ListUpdate.sql,v 1.3 2003/03/14 06:11:21 jjanke Exp $
 ***
 * Title:	Update Physical Inventory Count
 * Description:
 *	- from M_Storage.QtyOnHand
 ************************************************************************/
AS
	--	Logistice
	ResultStr						VARCHAR2(2000);
	Message						VARCHAR2(2000);
	Record_ID						NUMBER;
	v_Result 						NUMBER := 0;	--	failure

	InProcess						CHAR(1);
	Done							CHAR(1);
	--	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

BEGIN
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing');
	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);

	ResultStr := 'ReadingInventory';
	BEGIN
		SELECT	Processing, Processed
		  INTO	InProcess, Done
		FROM	M_Inventory
		WHERE	M_Inventory_ID = Record_ID
		FOR UPDATE;
	EXCEPTION WHEN OTHERS THEN
		Message := '@SaveErrorRowNotFound@';
		GOTO END_PROCESS;
	END;
	

	IF (InProcess = 'Y') THEN
		Message := '@OtherProcessActive@';
		GOTO END_PROCESS;
	END IF;
	IF (Done = 'Y') THEN
		Message := '@AlreadyPosted@';
		GOTO END_PROCESS;
	END IF;


	/**
	 *	Update
	 */
	ResultStr := 'UpdatingLine';
	UPDATE	M_InventoryLine l
	  SET	QtyBook =	(SELECT QtyOnHand FROM M_Storage s
						WHERE s.M_Product_ID=l.M_Product_ID 
						  AND s.M_Locator_ID=l.M_Locator_ID),		
			QtyCount =	(SELECT QtyOnHand FROM M_Storage s
						WHERE s.M_Product_ID=l.M_Product_ID 
						  AND s.M_Locator_ID=l.M_Locator_ID), 
			Updated = SysDate,
			UpdatedBy = 0
	WHERE	M_Inventory_ID = Record_ID
	  AND EXISTS		(SELECT * FROM M_Storage s
						WHERE s.M_Product_ID=l.M_Product_ID 
						  AND s.M_Locator_ID=l.M_Locator_ID);

	Message := '@Updated@=' || SQL%ROWCOUNT;

	COMMIT;

	--	Check for products in multiple lines
	UPDATE	M_InventoryLine
	  SET	IsActive='N'
	WHERE	M_Inventory_ID = Record_ID
	  AND	(M_Product_ID, M_Locator_ID) IN 
				(SELECT M_Product_ID, M_Locator_ID FROM M_InventoryLine
				WHERE M_Inventory_ID = Record_ID
				GROUP BY M_Product_ID, M_Locator_ID HAVING COUNT(*) > 1); 
	IF (SQL%ROWCOUNT <> 0) THEN
		Message := Message || ' @InventoryProductMultiple@';
	END IF;

	COMMIT;
	v_Result := 1;		-- success


<<END_PROCESS>>
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished');
	DBMS_OUTPUT.PUT_LINE(Message);
	UPDATE	AD_PInstance
	SET Updated = SysDate,
		IsProcessing = 'N',
		Result = v_Result,
		ErrorMsg = Message
	WHERE	AD_PInstance_ID=PInstance_ID;
	COMMIT;
	RETURN;

EXCEPTION
	WHEN  OTHERS THEN
		DBMS_OUTPUT.PUT_LINE(ResultStr || ': ' || SQLERRM);
		DBMS_OUTPUT.PUT_LINE(Message);
		ResultStr := ResultStr || ':' || SUBSTR(SQLERRM,1,1978-LENGTH(ResultStr));
		UPDATE	AD_PInstance
		SET Updated = SysDate,
			IsProcessing = 'N',
			Result = 0,				-- failure
			ErrorMsg = ResultStr
		WHERE	AD_PInstance_ID=PInstance_ID;
		COMMIT;
		RETURN;

END M_Inventory_ListUpdate;
/