m_inventory_listcreate.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE M_Inventory_ListCreate
(
	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_ListCreate.sql,v 1.5 2003/03/15 07:03:49 jjanke Exp $
 ***
 * Title:	Create Price Inventory Count
 * Description:
 *	- get info from Storage.QtyOnHand
 *	- if line exist, update it
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr					VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Record_ID					NUMBER;
	v_Result						NUMBER := 0;	--	failure

	InProcess						CHAR(1);
	Done							CHAR(1);
	NoInserted					NUMBER := 0;
	NoUpdated						NUMBER := 0;
	--	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
	ProductValue					VARCHAR2(40);
	M_Locator_ID					NUMBER;
	M_Warehouse_ID					NUMBER;
	M_Product_Category_ID			NUMBER;
	QtyRange						CHAR(1);
	--
	Client_ID						NUMBER;
	Org_ID						NUMBER;
	--
	NextNo						NUMBER;
	NextLine						NUMBER;
	--	Selection
	CURSOR Cur_Storage (ProductValue VARCHAR2, Locator_ID NUMBER, Warehouse_ID NUMBER, 
			PCategory_ID NUMBER, Client_ID NUMBER) IS
		SELECT	s.M_Product_ID, s.M_Locator_ID, s.QtyOnHand
		FROM	M_Storage s, M_Product p
		WHERE	p.AD_Client_ID = Client_ID
		  AND	s.M_Product_ID=p.M_Product_ID
		--	only ..
		  AND	(ProductValue IS NULL OR UPPER(p.Value) LIKE ProductValue)
		  AND	(Locator_ID IS NULL OR s.M_Locator_ID=Locator_ID)
		  AND	(Warehouse_ID IS NULL OR s.M_Locator_ID IN 
					(SELECT M_Locator_ID FROM M_Locator WHERE M_Warehouse_ID=Warehouse_ID))
		  AND	(PCategory_ID IS NULL OR p.M_Product_Category_ID=PCategory_ID)
		ORDER BY s.M_Locator_ID, p.Value;

BEGIN
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing');
	v_ResultStr := 'PInstanceNotFound';
	UPDATE AD_PInstance
	SET Created = SysDate,
		IsProcessing = 'Y'
	WHERE AD_PInstance_ID=PInstance_ID;
	COMMIT;

	--	Get Parameters
	v_ResultStr := 'ReadingParameters';
	FOR p IN Cur_Parameter (PInstance_ID) LOOP
		v_Record_ID := p.Record_ID;
		IF (p.ParameterName = 'QtyRange') THEN
			QtyRange := p.P_String;
			DBMS_OUTPUT.PUT_LINE('  QtyRange=' || QtyRange);
		ELSIF (p.ParameterName = 'ProductValue') THEN
			ProductValue := p.P_String;
			DBMS_OUTPUT.PUT_LINE('  ProductValue=' || ProductValue);
		ELSIF (p.ParameterName = 'M_Warehouse_ID') THEN
			M_Warehouse_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  M_Warehouse_ID=' || M_Warehouse_ID);
		ELSIF (p.ParameterName = 'M_Locator_ID') THEN
			M_Locator_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  M_Locator_ID=' || M_Locator_ID);
		ELSIF (p.ParameterName = 'M_Product_Category_ID') THEN
			M_Product_Category_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  M_Product_Category_ID=' || M_Product_Category_ID);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
		END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);

	v_ResultStr := 'ReadingInventory - (Record not saved)';
	BEGIN
		SELECT	Processing, Processed, AD_Client_ID, AD_Org_ID, M_Warehouse_ID
		  INTO	InProcess, Done, Client_ID, Org_ID, M_Warehouse_ID
		FROM		M_Inventory
		WHERE	M_Inventory_ID = v_Record_ID;
	EXCEPTION WHEN OTHERS THEN
		v_Message := '@SaveErrorRowNotFound@';
		GOTO END_PROCESS;
	END;

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

	v_ResultStr := 'Setting ProductValue';
	ProductValue := TRIM(ProductValue);
	IF (LENGTH(ProductValue) = 0) THEN
		ProductValue := NULL;
	END IF;
	IF (ProductValue IS NOT NULL AND SUBSTR(ProductValue, LENGTH(ProductValue), 1) <> '%') THEN
		ProductValue := ProductValue || '%';
	END IF;
	IF (ProductValue IS NOT NULL) THEN
		ProductValue := UPPER (ProductValue);
	END IF;

	v_ResultStr := 'ReadingInventoryLine';
	SELECT	NVL(MAX(Line), 10)
	  INTO	NextLine
	FROM	M_InventoryLine
	WHERE	M_Inventory_ID=v_Record_ID;
	
	FOR s IN Cur_Storage (ProductValue, M_Locator_ID, M_Warehouse_ID, M_Product_Category_ID, Client_ID) LOOP
		v_ResultStr := 'CheckingInventoryLine';
		--
		IF ((QtyRange = '>' AND s.QtyOnHand > 0) OR (QtyRange = '<' AND s.QtyOnHand < 0)
			OR (QtyRange = '=' AND s.QtyOnHand = 0) OR (QtyRange = 'N' AND s.QtyOnHand <> 0)) THEN
			--	DO we have this record already?
			SELECT	MAX(QtyBook)	
			  INTO	NextNo
			FROM	M_InventoryLine
			WHERE	M_Inventory_ID=v_Record_ID
			  AND	M_Product_ID=s.M_Product_ID
			  AND	M_Locator_ID=s.M_Locator_ID;
			--
		--	DBMS_OUTPUT.PUT_LINE('  QtyRange=' || QtyRange || ', OnHand=' || s.QtyOnHand || ', NextNo=' || NextNo);
		
			IF (NextNo IS NULL) THEN
				v_ResultStr := 'InsertLine';	 
				AD_Sequence_Next('M_InventoryLine', Client_ID, NextNo);
				INSERT INTO M_InventoryLine
					(M_InventoryLine_ID, Line,
					AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
					M_Inventory_ID, M_Locator_ID,
					M_Product_ID, QtyBook, QtyCount)
				VALUES
					(NextNo, NextLine,
					Client_ID, Org_ID, 'Y', SysDate, 0, SysDate, 0,
					v_Record_ID, s.M_Locator_ID,
					s.M_Product_ID, s.QtyOnHand, s.QtyOnHand);
				NextLine := NextLine + 10;
				NoInserted := NoInserted + 1;
			ELSE
				v_ResultStr := 'UpdateLine';
				UPDATE	M_InventoryLine
				  SET	QtyBook=s.QtyOnHand,
						QtyCount=s.QtyOnHand,
						Updated=SysDate,
						UpdatedBy=0
				WHERE	M_Inventory_ID=v_Record_ID
				  AND	M_Product_ID=s.M_Product_ID
				  AND	M_Locator_ID=s.M_Locator_ID;
				NoUpdated := NoUpdated + 1;
			END IF;

		END IF;	--	
	END LOOP;	--	Cur_Storage

	COMMIT;
	v_Message := '@Inserted@=' || NoInserted || ', @Updated@=' || NoUpdated;
	v_Result := 1;		--	success

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

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

END M_Inventory_ListCreate;
/