c_order_post.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_Order_Post
(
	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-2001 Jorg Janke, Compiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_Order_Post.sql,v 1.19 2003/04/22 20:44:31 jjanke Exp $
 ***
 * Title:	 Order Processing
 * Description:
 *	Order Processing
 *		- Convert to Target DocType
 *		- Calculate Taxes and Totals
 *		- Reserve Inventory
 *		- Process
 ************************************************************************/
AS
	--	Logistics
	ResultStr						VARCHAR2(2000);
	Message						VARCHAR2(2000);
	Record_ID						NUMBER;
	IsProcessing					CHAR(1);
	IsProcessed					CHAR(1);
	v_Result						NUMBER := 1;	--	Success
	--	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;
	--	Record Info
	v_Client_ID					NUMBER;
	v_Org_ID						NUMBER;
	UpdatedBy						NUMBER;
	DocAction						CHAR(2);
	DocStatus						CHAR(2);
	v_DocType_ID					NUMBER;
	v_DocTypeTarget_ID				NUMBER;
	v_DocSubTypeSO					CHAR(2);
	v_IsBinding					CHAR(1) := 'Y';
	IsApproved					CHAR(1);
	--
	ToDeliver						NUMBER;
	ToInvoice						NUMBER;
	--
	InOut_ID						NUMBER;
	Invoice_ID					NUMBER;

BEGIN
	--  Update AD_PInstance
	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);

	/**
	 *	Read Order
	 */
	ResultStr := 'ReadingOrder';
	SELECT	Processing, Processed, DocAction, DocStatus, 
		C_DocType_ID, C_DocTypeTarget_ID, IsApproved,
		AD_Client_ID, AD_Org_ID, UpdatedBy
	  INTO	IsProcessing, IsProcessed, DocAction, DocStatus, 
		v_DocType_ID, v_DocTypeTarget_ID, IsApproved,
		v_Client_ID, v_Org_ID, UpdatedBy
	FROM		C_Order
	WHERE	C_Order_ID=Record_ID
	FOR UPDATE;

	--	Get current DocSubTypeSO
	SELECT	DocSubTypeSO 
	  INTO	v_DocSubTypeSO
	FROM		C_DocType
	WHERE	C_DocType_ID = v_DocType_ID;


	DBMS_OUTPUT.PUT_LINE('DocAction=' || DocAction || ', DocStatus=' || DocStatus ||
		', DocType_ID=' || v_DocType_ID || ', DocTypeTarget_ID=' || v_DocTypeTarget_ID ||
		', IsApproved=' || IsApproved || ', DocSubTypeSO=' || v_DocSubTypeSO);


	/**
	 *	Order Closed, Voided or Reversed - No action possible
	 */
	IF (DocStatus IN ('CL', 'VO', 'RE')) THEN
		Message := '@AlreadyPosted@';
		GOTO FINISH_PROCESS;
	END IF;


	/**
	 *	Waiting on Prepayment 	can only be closed
	 */
	IF (DocStatus = 'WP' AND DocAction <> 'CL') THEN
		Message := '@WaitingPayment@';
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Unlock
	 */
	IF (DocAction = 'XL') THEN
		UPDATE	C_Order
		  SET	Processing = 'N',
				DocAction = '--',
				Updated = SysDate
		WHERE	C_Order_ID = Record_ID;
		GOTO FINISH_PROCESS;
	END IF;
	IF (IsProcessing = 'Y') THEN
		Message := '@OtherProcessActive@';
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Re-activate
	 */
	IF (DocAction = 'RE') THEN
		DBMS_OUTPUT.PUT_LINE('Re-Activating ' || v_DocSubTypeSO || ': ' || Record_ID);
		IF (v_DocSubTypeSO IN ('WI', 'WP', 'WR')) THEN
			--	Cancel existing Delivery + Invoice Documents
			M_InOut_Cancel(NULL, Record_ID);			
			C_Invoice_Cancel(NULL, Record_ID);
		END IF;
		--	Update Order
		ResultStr := 'ReActivate';
		UPDATE	C_Order
		  SET	DocStatus = 'IP',		--	In Progress
				DocAction = 'CO',
				C_CashLine_ID = NULL,	--	is reversed
				Processing = 'N',
				Processed = 'N',
				Updated = SysDate
		WHERE	C_Order_ID = Record_ID;
		GOTO FINISH_PROCESS;
	END IF;


	/**
	 *	Close Order - prepare
	 */
	IF (DocAction = 'CL') THEN
		--	Cancel undelivered Items
		UPDATE	C_OrderLine
		  SET	QtyOrdered = QtyDelivered,
				Updated = SysDate
		WHERE	C_Order_ID = Record_ID
		  AND	QtyOrdered <> QtyDelivered;
		-- if there is no change, the tax calculation, etc. is not needed.
		-- potential problem, if posted (i.e. encumbered) for full amount
		-- and the rest then cancelled out.
	END IF;

	/**
	 *	Void Order - prepare
	 */
	IF (DocAction = 'VO') THEN
		--	Cancel all Items
		UPDATE	C_OrderLine
		  SET	QtyOrdered = 0,
				LineNetAmt = 0,
				Updated = SysDate
		WHERE	C_Order_ID = Record_ID
		  AND	QtyOrdered <> 0;
	END IF;


	/**************************************************************************
	 *	Start Processing ------------------------------------------------------
	 *************************************************************************/
	ResultStr := 'LockingOrder';
	UPDATE	C_Order
	  SET	Processing = 'Y'
	WHERE	C_Order_ID = Record_ID;
	COMMIT;			
	--	Now, needs to go to END_PROCESSING to unlock

	/**
	 *	Allowed Actions: 	AProve, COmplete, PRocess, CLose, VOid
	 */
	IF (DocAction IN ('AP', 'CO', 'PR', 'CL', 'VO')) THEN
		NULL;
	ELSE
		Message := '@ActionNotAllowedHere@ (O-' || DocAction || ')';
		GOTO END_PROCESSING;
	END IF;

	/**
	 *	Convert to Target DocType
	 */
	DECLARE
		v_DocSubTypeSO_Target		CHAR(2);
	BEGIN
		ResultStr := 'ConvertingDocType';
		IF (v_DocType_ID <> v_DocTypeTarget_ID) THEN
			--	New
			IF (DocStatus = 'DR' OR v_DocType_ID = 0) THEN
				--	Update to Target Document Type				
				WHILE (v_DocType_ID <> v_DocTypeTarget_ID) LOOP
					BEGIN
						ResultStr := 'UpdateDocType';
						UPDATE	C_Order
						  SET	C_DocType_ID = v_DocTypeTarget_ID
						WHERE	C_Order_ID = Record_ID;
						v_DocType_ID := v_DocTypeTarget_ID;
					EXCEPTION WHEN OTHERS THEN
						ResultStr := 'UpdateDocumentNo';
						UPDATE	C_Order
						  SET	DocumentNo = DocumentNo || '.'
						WHERE	C_Order_ID = Record_ID;
					END;
				END LOOP;
			ELSE
				ResultStr := 'GetTargetDocType';
				SELECT	DocSubTypeSO 
				  INTO	v_DocSubTypeSO_Target
				FROM		C_DocType
				WHERE	C_DocType_ID = v_DocTypeTarget_ID;
				DBMS_OUTPUT.PUT_LINE('Changing DocType from ' || v_DocSubTypeSO || ' to ' || v_DocSubTypeSO_Target);
				--	Change Offer to anything, Change InProcess to anything
				IF (v_DocSubTypeSO IN ('ON', 'OB') OR DocStatus = 'IP') THEN
					--	Update to Target Document Type				
					WHILE (v_DocType_ID <> v_DocTypeTarget_ID) LOOP
						BEGIN
							ResultStr := 'UpdateDocType';
							UPDATE	C_Order
							  SET	C_DocType_ID = v_DocTypeTarget_ID
							WHERE	C_Order_ID = Record_ID;
							v_DocType_ID := v_DocTypeTarget_ID;
						EXCEPTION WHEN OTHERS THEN
							ResultStr := 'UpdateDocumentNo';
							UPDATE	C_Order
							  SET	DocumentNo = DocumentNo || '.'
							WHERE	C_Order_ID = Record_ID;
						END;
					END LOOP;
				ELSE
					--	Change Back
					UPDATE	C_Order
					  SET	C_DocTypeTarget_ID = v_DocType_ID
					WHERE	C_Order_ID = Record_ID;
					Message := '@CannotChangeDocType@';
					GOTO END_PROCESSING;
				END IF;
			END IF;
		END IF;	-- C_DocType_ID <> C_DocTypeTarget_ID
	END;	--	Conversion

	/**
	 *	Get DocSubTypeSO + Is it Binding ??
	 */
	ResultStr := 'TestBinding DocType_ID=' || v_DocType_ID;
	SELECT	DECODE (DocSubTypeSO,'ON','N','Y'), DocSubTypeSO
	  INTO	v_IsBinding, v_DocSubTypeSO
	FROM	C_DocType
	WHERE	C_DocType_ID = v_DocType_ID;
	DBMS_OUTPUT.PUT_LINE('DocSubTypeSO=' || v_DocSubTypeSO || ' IsBinding=' || v_IsBinding);


	/**************************************************************************
	 *	Resolve not-stocked BOMs
	 *************************************************************************/
	DECLARE
		--	Order Lines with non-stocked BOMs
		CURSOR CUR_BOM_Line	IS
			SELECT	*
			FROM	C_OrderLine l
			WHERE	l.C_Order_ID=Record_ID
			  AND	IsActive='Y'
			  AND EXISTS (SELECT * FROM M_Product p WHERE l.M_Product_ID=p.M_Product_ID
				  AND	p.IsBOM='Y' AND p.IsStocked='N')
			ORDER BY l.Line
			FOR UPDATE;

		--	BOM Product List
		CURSOR CUR_BOM (Product_ID NUMBER)	IS
			SELECT	b.M_ProductBOM_ID, p.C_UOM_ID, b.BOMQty, b.Description
			FROM	M_Product_BOM b
			  INNER JOIN M_Product p ON (b.M_ProductBOM_ID=p.M_Product_ID)
			WHERE	b.M_Product_ID=Product_ID
			ORDER BY Line;
		--	
		CountNo					NUMBER;
		PriceList_Version_ID		NUMBER;
		NextNo					NUMBER;
		Line						NUMBER;
		FreightAmt				NUMBER;
		ChargeAmt					NUMBER;
		--
	BEGIN
		LOOP
			--	How many BOMs do we have?
			SELECT	COUNT(*)
			  INTO	CountNo
			FROM	C_OrderLine l
			WHERE	l.C_Order_ID=Record_ID
			  AND EXISTS (SELECT * FROM M_Product p WHERE l.M_Product_ID=p.M_Product_ID
				  AND	p.IsBOM='Y' AND p.IsStocked='N');
			--	Nothing to do?
			EXIT WHEN CountNo = 0;
			DBMS_OUTPUT.PUT_LINE('  BOMs to resolve=' || CountNo);

			--	Get Price List Version
			SELECT	NVL(SUM(v.M_PriceList_Version_ID), 0)
			  INTO	PriceList_Version_ID
			FROM	M_PriceList_Version v, C_Order o
			WHERE	v.M_PriceList_ID=o.M_PriceList_ID
			  AND	v.ValidFrom <= o.DateOrdered
			  AND	v.IsActive='Y'
			  AND	o.C_Order_ID=Record_ID
			  AND	RowNum=1
			ORDER BY v.ValidFrom DESC;

			--	Replace Lines
			FOR l IN CUR_BOM_Line LOOP
				Line := l.Line;
				--	One Time variables
				FreightAmt := l.FreightAmt;
				ChargeAmt := l.ChargeAmt;
				--	Create New Lines
				FOR b IN CUR_BOM (l.M_Product_ID) LOOP
					AD_Sequence_Next('C_OrderLine', l.AD_Client_ID, NextNo);
					Line := Line + 1;
					INSERT INTO C_OrderLine
						(C_OrderLine_ID,
						AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
						C_Order_ID,Line,C_BPartner_ID,C_BPartner_Location_ID,
						DateOrdered,DatePromised,DateDelivered,DateInvoiced,Description,
						M_Product_ID,M_Warehouse_ID,DirectShip,C_UOM_ID,
						QtyOrdered,QtyReserved,QtyDelivered,QtyInvoiced,
						M_Shipper_ID,C_Currency_ID,
						PriceList,PriceActual,PriceLimit,LineNetAmt,
						Discount,FreightAmt,
						C_Charge_ID,ChargeAmt,
						C_Tax_ID,
						Lot,SerNo)
					VALUES
						(NextNo,
						l.AD_Client_ID,l.AD_Org_ID,l.IsActive,SysDate,0,SysDate,0,
						l.C_Order_ID,Line,l.C_BPartner_ID,l.C_BPartner_Location_ID,
						l.DateOrdered,l.DatePromised,l.DateDelivered,l.DateInvoiced,b.Description,
						b.M_ProductBOM_ID,l.M_Warehouse_ID,l.DirectShip,b.C_UOM_ID,
						l.QtyOrdered*b.BOMQty,l.QtyReserved*b.BOMQty,l.QtyDelivered*b.BOMQty,l.QtyInvoiced*b.BOMQty,
						l.M_Shipper_ID,l.C_Currency_ID,
						BOM_PriceList(b.M_ProductBOM_ID, PriceList_Version_ID),BOM_PriceStd(b.M_ProductBOM_ID, PriceList_Version_ID),
						BOM_PriceLimit(b.M_ProductBOM_ID, PriceList_Version_ID),
						BOM_PriceStd(b.M_ProductBOM_ID, PriceList_Version_ID) * l.QtyOrdered*b.BOMQty,
						l.Discount,FreightAmt,
						l.C_Charge_ID,ChargeAmt,
						l.C_Tax_ID,
						null,null);			-- no Lot/SerNo for BOMs
					--	One Time variables
					FreightAmt := 0;
					ChargeAmt := 0;
				END LOOP;	--	Create New Lines
				--	Convert into Comment Line
				UPDATE	C_OrderLine ol
				  SET	M_Product_ID = NULL,
						PriceList = 0, PriceActual = 0, PriceLimit = 0, LineNetAmt = 0,
						FreightAmt = 0, ChargeAmt = 0,
						Description = (SELECT p.Name || ' ' || ol.Description FROM M_Product p 
							WHERE p.M_Product_ID=l.M_Product_ID)
				WHERE	C_OrderLine_ID=l.C_OrderLine_ID;
			END LOOP;	-- Replace Lines
		END LOOP;	--	BOM Loop
	END;


	/**************************************************************************
	 *	Always check and (un) Reserve Inventory		(counterpart: M_InOut_Post)
	 *************************************************************************/
	DECLARE
		CURSOR Cur_ResLine (Order_ID NUMBER, Binding CHAR) IS
			SELECT l.M_Warehouse_ID, l.M_Product_ID, l.C_OrderLine_ID,
				-- Target Level = 0 if DirectShip='Y' or Binding='N'
				DECODE(l.DirectShip,'Y',0,DECODE(Binding,'N',0,l.QtyOrdered))
					-l.QtyReserved-l.QtyDelivered AS Qty, 
				l.DatePromised
			FROM C_OrderLine l, M_Product p 
			WHERE l.C_Order_ID=Order_ID
			  -- Reserve Products (not: services, null products) --
			  AND l.M_Product_ID=p.M_Product_ID 
			  AND p.IsStocked='Y' AND p.ProductType='I'
			  -- Target Level = 0 if DirectShip='Y' or Binding='N'
			  AND DECODE(l.DirectShip,'Y',0,DECODE(Binding,'N',0,l.QtyOrdered))
					-l.QtyReserved-l.QtyDelivered <> 0
			FOR UPDATE;
		CURSOR Cur_ResStorage (Warehouse_ID NUMBER, Product_ID NUMBER) IS
			SELECT	s.M_Locator_ID --, s.QtyOnHand, s.QtyReserved
			FROM	M_Locator l, M_Storage s
			WHERE	l.M_Locator_ID=s.M_Locator_ID
			  AND	l.M_Warehouse_ID=Warehouse_ID
			  AND	s.M_Product_ID=Product_ID
			ORDER BY l.PriorityNo;
		v_Locator_ID					NUMBER;
		CURSOR Cur_ResLocation (Warehouse_ID NUMBER) IS
			SELECT	M_Locator_ID, AD_Client_ID, AD_Org_ID
			FROM	M_Locator
			WHERE	M_Warehouse_ID=Warehouse_ID
			ORDER BY PriorityNo;
		v_Client_ID						NUMBER;
		v_Org_ID						NUMBER;
		v_QtySO							NUMBER;	--	Reserved
		v_QtyPO							NUMBER;	--	Ordered
	BEGIN
		ResultStr := 'ReserveInventory';
		--	For all lines needing reservation
		FOR l IN Cur_ResLine (Record_ID, v_IsBinding) LOOP
			--	Qty corrected for SO/PO
			IF (v_DocSubTypeSO IS NOT NULL) THEN
				v_QtySO := l.Qty;
				v_QtyPO := 0;
			ELSE	--	PO
				v_QtySO := 0;
				v_QtyPO := l.Qty;
			END IF;
			--	Check for existing storage record
			OPEN Cur_ResStorage (l.M_Warehouse_ID, l.M_Product_ID);
			FETCH Cur_ResStorage INTO v_Locator_ID;