m_inout_create.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE M_InOut_Create
(
	p_PInstance_ID		IN	NUMBER,
	p_Order_ID		IN	NUMBER	DEFAULT NULL,
	p_Invoice_ID		IN	NUMBER	DEFAULT NULL,
	p_ForceDelivery	IN	CHAR	DEFAULT 'N',
	p_InOut_ID		OUT	NUMBER,
	p_Locator_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+CPM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: M_InOut_Create.sql,v 1.12 2003/04/24 06:11:51 jjanke Exp $
 ***
 * Title:	Create Shipment from Order	
 * Description:
 *	Order Loop goes though all open orders, where we would need to ship something
 *		if forced or if there is a line to ship
 *			create InOut document header
 *			for all qualifying order lines
 *				check every locator availability and if qty available
 *					create InOut line
 *
 *	Order and reservation is updated when posting 
 *	as there should not be a delay between creating + posting it
 *
 *	For each Warehouse create lines (with exception if Direct Ship's),
 *	create also lines for non-stocked, ad_hoc products or comments
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr					VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	--	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
	p_AD_Org_ID					NUMBER;
	p_Record_ID					NUMBER := NULL;
	p_Selection					VARCHAR2(1) := 'N';

	--	Orders to process		- one per warehouse
	CURSOR Cur_Order (Order_ID NUMBER, Org_ID NUMBER, Selection VARCHAR2) IS
		SELECT o.AD_Client_ID, o.AD_Org_ID, o.C_Order_ID, o.IsSOTrx, o.Description,
			o.DocumentNo, o.C_DocType_ID,
			o.C_BPartner_ID, o.C_BPartner_Location_ID, o.C_BPartner_Contact_ID,
			l.M_Warehouse_ID, o.POReference, o.DateOrdered, o.DeliveryRule, 
			o.FreightCostRule, o.FreightAmt,
			o.DeliveryViaRule, o.M_Shipper_ID, o.C_Charge_ID, o.ChargeAmt, o.PriorityRule
		FROM C_Order o
		  INNER JOIN C_OrderLine l ON (o.C_Order_ID=l.C_Order_ID)
		--		Orders are IP 	or CO if Standard Orders
		WHERE	(o.DocStatus = 'IP' OR (o.DocStatus = 'CO' AND o.IsDelivered='N' AND Order_ID = 0))
		  --	Sales Orders Only and not Offers
		  AND	o.IsSOTrx='Y'
		  AND o.C_DocType_ID IN (SELECT C_DocType_ID FROM C_DocType
			WHERE DocBaseType='SOO' AND DocSubTypeSO NOT IN ('ON','OB'))
		  --	Manually Selected
		  AND (	(Selection = 'Y' AND o.IsSelected='Y')
			OR	(Selection <> 'Y'
			  --	Specific or individual organization
			  AND	(Org_ID IS NULL OR Org_ID=o.AD_Org_ID)
			  --	Specific order or all open orders
			  AND	(o.C_Order_ID=Order_ID			--	Parameter
					OR (	Order_ID = 0 AND EXISTS
						(SELECT * FROM C_OrderLine ll
						WHERE	o.C_Order_ID=ll.C_Order_ID 
						  AND	ll.QtyOrdered <> ll.QtyDelivered 
						  AND	ll.DirectShip='N' AND ll.M_Product_ID IS NOT NULL)
					   )
					)
				)
			 )
		GROUP BY o.AD_Client_ID, o.AD_Org_ID, o.C_Order_ID, o.IsSOTrx, o.Description,  
			o.DocumentNo, o.C_DocType_ID,
			o.C_BPartner_ID, o.C_BPartner_Location_ID, o.C_BPartner_Contact_ID,
			l.M_Warehouse_ID, o.POReference, o.DateOrdered, o.DeliveryRule, 
			o.FreightCostRule, o.FreightAmt,
			o.DeliveryViaRule, o.M_Shipper_ID, o.C_Charge_ID, o.ChargeAmt, o.PriorityRule
		ORDER BY o.PriorityRule;

	--	Order Lines per Warehouse
	CURSOR Cur_OrderLine (Order_ID NUMBER, Warehouse_ID NUMBER, IsForced CHAR) IS
		SELECT	*
		FROM	C_OrderLine l
		WHERE	C_Order_ID = Order_ID			--	Parameter
		  AND	M_Warehouse_ID = Warehouse_ID		--	Parameter
		  --	Incomplete lines and comments
		  AND	(QtyOrdered <> QtyDelivered OR (QtyOrdered=0 AND M_Product_ID IS NULL))
		  --	Don't include Direct Ship's
		  AND	DirectShip='N'
		  AND 
			--	We ship it anyway
			(IsForced = 'Y'							-- Parameter
			OR 
			--	we have it on stock
			(EXISTS (SELECT * FROM M_Storage s, M_Locator loc, C_Order o
				WHERE s.M_Product_ID=l.M_Product_ID
				  AND s.M_Locator_ID=loc.M_Locator_ID
				  AND loc.M_Warehouse_ID=l.M_Warehouse_ID
				  AND l.C_Order_ID=o.C_Order_ID	
					--	Delivery Rule:	(L)ine, (O)rder
				  AND ((o.DeliveryRule IN ('L', 'O') AND s.QtyOnHand >= l.QtyOrdered-l.QtyDelivered)
					--	Delivery Rule:	(A)vailability
					OR s.QtyOnHand > 0 ) )
					-- 	Delivery Rule (R)eceipt ** NOT HANDELED **
			--	Service
			OR EXISTS (SELECT * FROM M_Product p 
				WHERE l.M_Product_ID=p.M_Product_ID AND (p.IsStocked='N' OR p.ProductType<>'I'))
			--	Comment + AdHoc
			OR l.M_Product_ID IS NULL )
			)	--	
		ORDER BY Line;
	ol	Cur_OrderLine%ROWTYPE;
	--
	v_NextNo						NUMBER;
	v_DocType_ID					NUMBER;
	v_DocumentNo					VARCHAR2(40);
	v_Qty						NUMBER;
	--
	v_lines						NUMBER := 0;
BEGIN
	--	Process Parameters
	IF (p_PInstance_ID IS NOT NULL) THEN
		--  Update AD_PInstance
		DBMS_OUTPUT.PUT_LINE('M_InOut_Create - 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 = 'AD_Org_ID') THEN
				p_AD_Org_ID := p.P_Number;
				DBMS_OUTPUT.PUT_LINE('  AD_Org_ID=' || p_AD_Org_ID);
			ELSIF (p.ParameterName = 'Selection') THEN
				p_Selection := p.P_String;
				DBMS_OUTPUT.PUT_LINE('  Selection=' || p_Selection);
			ELSE
				DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
			END IF;
			p_Record_ID := p.Record_ID;
		END LOOP;	--	Get Parameter
		DBMS_OUTPUT.PUT_LINE('  p_Record_ID=' || p_Record_ID);
	ELSIF (p_Invoice_ID IS NOT NULL) THEN
		GOTO CREATE_FROM_INVOICE;
	ELSE
		p_Record_ID := p_Order_ID;
		DBMS_OUTPUT.PUT_LINE('<<M_InOut_Create>> Order_ID=' || p_Record_ID);
	END IF;
	--	May be NULL or 0
	IF (p_Record_ID IS NULL) THEN
		p_Record_ID := 0;
	END IF;


	/**************************************************************************
	 *	Order Loop goes though all open orders, where we would need to ship something
	 *	(if p_Record_ID = 0) 
	 *************************************************************************/
	FOR o IN Cur_Order (p_Record_ID, p_AD_Org_ID, p_Selection) LOOP
		DBMS_OUTPUT.PUT_LINE('Order ' || o.DocumentNo || '/' || o.C_Order_ID
			|| ', Wh=' || o.M_Warehouse_ID
			|| ', Force=' || p_ForceDelivery || ', Delivery=' || o.DeliveryRule);
		--	Delivery Rules
		--		(A)vailability
		--		Complete (L)ine
		--		Complete (O)rder
		--		After (R)eceipt

		--	(A)vailability	(L)ine			--	Do we have something to ship ?
		IF (o.DeliveryRule IN ('A', 'L', 'R')) THEN
			v_ResultStr := 'CheckSomethingToShip';
			OPEN Cur_OrderLine (o.C_Order_ID, o.M_Warehouse_ID, p_ForceDelivery);
			v_ResultStr := 'Fetching';
			FETCH Cur_OrderLine INTO ol;
			IF (Cur_OrderLine%NOTFOUND) THEN
				DBMS_OUTPUT.PUT_LINE('  -no lines-');	
				GOTO Next_Order;
			END IF;
		ELSE
			DBMS_OUTPUT.PUT_LINE('** DeliveryRule=' || o.DeliveryRule || ' not implemented');
			GOTO Next_Order;
		END IF;

		/**
		 *	Create Order Header
		 *		if forced or if there is a line to ship
		 */
		v_ResultStr := 'CreateInOut-S';
		SAVEPOINT sp_new_shipment;
		v_lines := 0;
		--
		AD_Sequence_Next('M_InOut', o.C_Order_ID, p_InOut_ID);
		v_ResultStr := 'CreateInOut DocType=' || o.C_DocType_ID;
		--	Get Shipment Doc Number
		SELECT	C_DocTypeShipment_ID
		  INTO	v_DocType_ID
		FROM		C_DocType
		WHERE	C_DocType_ID=o.C_DocType_ID;
		AD_Sequence_DocType (v_DocType_ID, o.C_Order_ID, v_DocumentNo);
		--
		DBMS_OUTPUT.PUT_LINE('  InOut_ID=' || p_InOut_ID || ', DocumentNo=' || v_DocumentNo
			|| ', Ship_DocType_ID=' || v_DocType_ID || ', Order_DocType_ID=' || o.C_DocType_ID);
		v_ResultStr := 'InsertInOut ' || p_InOut_ID;
		INSERT INTO M_InOut
			(M_InOut_ID, C_Order_ID, IsSOTrx,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			DocumentNo, C_DocType_ID, Description, IsPrinted, 
			MovementType, MovementDate, DateAcct,
			C_BPartner_ID, C_BPartner_Location_ID, C_BPartner_Contact_ID,
			M_Warehouse_ID, POReference, DateOrdered, DeliveryRule, 
			FreightCostRule, FreightAmt,
			DeliveryViaRule, M_Shipper_ID, C_Charge_ID, ChargeAmt, PriorityRule,
			DocStatus, DocAction, Processing, Processed)
		VALUES
			(p_InOut_ID, o.C_Order_ID, o.IsSOTrx,
			o.AD_Client_ID, o.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			v_DocumentNo, v_DocType_ID, o.Description, 'N', 
			'C-', SysDate, SysDate,
			o.C_BPartner_ID, o.C_BPartner_Location_ID, o.C_BPartner_Contact_ID,
			o.M_Warehouse_ID, o.POReference, o.DateOrdered, o.DeliveryRule, 
			o.FreightCostRule, o.FreightAmt,
			o.DeliveryViaRule, o.M_Shipper_ID, o.C_Charge_ID, o.ChargeAmt, o.PriorityRule,
			'DR', 'CO', 'N', 'N');

		/**
		 *	Create InOut Lines
		 *		for all qualifying order lines
		 */
		LOOP
			--	Check Availability
			v_Qty := ol.QtyOrdered - ol.QtyDelivered;