c_order_po_create.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_Order_PO_Create
(
	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+CPM
 * Copyright (C) 1999-2002 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_Order_PO_Create.sql,v 1.3 2003/03/31 00:02:01 jjanke Exp $
 ***
 * Title:	Create PO from SO
 * Description:
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr					VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Result						NUMBER := 1;	-- 0=failure
	p_Record_ID					NUMBER;
	p_AD_User_ID					NUMBER;
	--	Parameter
	CURSOR Cur_Parameter (pp_PInstance NUMBER) IS
		SELECT i.Record_ID, i.AD_User_ID,
			p.ParameterName, p.P_String, p.P_Number, p.P_Date, p.P_Date_To
		FROM AD_PInstance i, AD_PInstance_Para p
		WHERE i.AD_PInstance_ID=pp_PInstance
		AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
		ORDER BY p.SeqNo;
	--	Parameter Variables
	p_DateOrdered_From				DATE;
	p_DateOrdered_To				DATE;
	p_C_BPartner_ID				NUMBER;
	p_Vendor_ID					NUMBER;
	p_C_Order_ID					NUMBER;
	--
	v_Created						NUMBER := 0;

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
		p_Record_ID := p.Record_ID;
		p_AD_User_ID := p.AD_User_ID;
		IF (p.ParameterName = 'DateOrdered') THEN
			p_DateOrdered_From := p.P_Date;
			p_DateOrdered_To := p.P_Date_To;
			DBMS_OUTPUT.PUT_LINE('  DateOrdered=' || p_DateOrdered_From || '-' || p_DateOrdered_To);
		ELSIF (p.ParameterName = 'C_BPartner_ID') THEN
			p_C_BPartner_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  C_BPartner_ID=' || p_C_BPartner_ID);
		ELSIF (p.ParameterName = 'Vendor_ID') THEN
			p_Vendor_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  Vendor_ID=' || p_Vendor_ID);
		ELSIF (p.ParameterName = 'C_Order_ID') THEN
			p_C_Order_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  C_Order_ID=' || p_C_Order_ID);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
		END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || p_Record_ID);

	--	Check that we have some restrictions
	v_ResultStr := 'CheckRestriction';
	IF (p_DateOrdered_From IS NULL AND p_DateOrdered_To IS NULL
		AND p_C_BPartner_ID IS NULL AND p_Vendor_ID IS NULL 
		AND p_C_Order_ID IS NULL) THEN
		v_Message := 'You need to restrict selection!';
		v_Result := 0;
		GOTO FINISH_PROCESS;
	END IF;


	DECLARE
		CURSOR Cur_SO IS
			SELECT so.AD_Client_ID,so.AD_Org_ID,so.UpdatedBy,so.Description,
				so.SalesRep_ID, so.DateOrdered, so.DatePromised, so.DateAcct,
				so.C_Order_ID, so.DocumentNo, so.PriorityRule, so.M_WareHouse_ID,
				so.C_Campaign_ID, so.C_Project_ID, so.C_Activity_ID,
				sol.C_OrderLine_ID,
				po.C_BPartner_ID, v.PO_PaymentTerm_ID, v.PaymentRulePO, v.POReference,
				pl.M_PriceList_ID, pl.C_Currency_ID 
			FROM C_Order so
			  INNER JOIN C_OrderLine sol ON (so.C_Order_ID=sol.C_Order_ID)
			  INNER JOIN M_Product_PO po ON (sol.M_Product_ID=po.M_Product_ID)
			  INNER JOIN C_BPartner v ON (po.C_BPartner_ID=v.C_BPartner_ID)
			  INNER JOIN M_PriceList pl ON (v.PO_PriceList_ID=pl.M_PriceList_ID)
			WHERE po.IsCurrentVendor='Y'
			  -- Optional Order No
			  AND (p_C_Order_ID IS NULL OR so.C_Order_ID=p_C_Order_ID)
			  -- Optional Sales BPartner
			  AND (p_C_BPartner_ID IS NULL OR so.C_BPartner_ID=p_C_BPartner_ID)
			  -- Optional Purchase BPartner
			  AND (p_Vendor_ID IS NULL OR po.C_BPartner_ID=p_Vendor_ID)
			  -- Optional Date Range
			  AND (p_DateOrdered_From IS NULL OR so.DateOrdered BETWEEN p_DateOrdered_From AND p_DateOrdered_To)
			  -- Don't create twice
			  AND sol.Ref_OrderLine_ID IS NULL
			GROUP BY so.AD_Client_ID,so.AD_Org_ID,so.UpdatedBy,so.Description,
				so.SalesRep_ID, so.DateOrdered, so.DatePromised, so.DateAcct,
				so.C_Order_ID, so.DocumentNo, so.PriorityRule, so.M_WareHouse_ID,
				so.C_Campaign_ID, so.C_Project_ID, so.C_Activity_ID,
				sol.C_OrderLine_ID,
				po.C_BPartner_ID, v.PO_PaymentTerm_ID, v.PaymentRulePO, v.POReference,
				pl.M_PriceList_ID, pl.C_Currency_ID
			ORDER BY po.C_BPartner_ID;
		--
		CURSOR Cur_SOL (OrderLine_ID NUMBER, PLV_ID NUMBER) IS
			SELECT sol.AD_Client_ID,sol.AD_Org_ID,
				sol.C_OrderLine_ID, sol.Line, sol.DateOrdered, sol.DatePromised, 
				sol.M_Product_ID, sol.C_UOM_ID, sol.QtyOrdered, sol.M_Warehouse_ID, sol.DirectShip,
				sol.Description,
				pp.PriceList, pp.PriceStd, pp.PriceLimit
			FROM	C_OrderLine sol
			  INNER JOIN M_ProductPrice pp ON (sol.M_Product_ID=pp.M_Product_ID AND M_PriceList_Version_ID=PLV_ID)	-- #2
			WHERE	sol.C_OrderLine_ID = OrderLine_ID	-- #1
			ORDER BY sol.Line;
		--
		v_C_PaymentTerm_ID			NUMBER := NULL;
		v_C_DocType_ID				NUMBER := NULL;
		v_M_PriceList_Version_ID		NUMBER := NULL;
		v_C_Tax_ID				NUMBER := NULL;
		--
		v_BillTo_ID				NUMBER;
		v_C_BPartner_Location_ID		NUMBER;
		--
		v_PO_Order_ID				NUMBER;
		v_DocumentNo				NVARCHAR2(30);
		v_NextID					NUMBER;
		v_Lines					NUMBER := 0;
		--
	BEGIN
		v_ResultStr := 'StartLoop';
		FOR so IN Cur_SO LOOP

			--	All Payment Types are the same for the same Client
			IF (v_C_PaymentTerm_ID IS NULL) THEN
				v_ResultStr := 'DefaultPaymentTerm';
				SELECT	MAX(C_PaymentTerm_ID)
				  INTO	v_C_PaymentTerm_ID
				FROM	C_PaymentTerm
				WHERE	IsDefault='Y' AND AD_Client_ID=so.AD_Client_ID;
				IF (v_C_PaymentTerm_ID IS NULL) THEN
					v_Message := 'No Default Payment Term found!';
					v_Result := 0;
					GOTO FINISH_PROCESS;
				END IF;
			END IF;

			--	All Document Types are the same for the same Client
			IF (v_C_DocType_ID IS NULL) THEN
				v_ResultStr := 'DocType';
				SELECT	MAX(C_DocType_ID)
				  INTO	v_C_DocType_ID
				FROM	C_DocType
				WHERE	DocBaseType='POO' AND AD_Client_ID=so.AD_Client_ID;
				IF (v_C_DocType_ID IS NULL) THEN
					v_Message := 'No PO Document Type found';
					v_Result := 0;
					GOTO FINISH_PROCESS;
				END IF;
			END IF;

			--	Tax Default for Client
			IF (v_C_Tax_ID IS NULL) THEN
				v_ResultStr := 'DefaultTax';
				SELECT	MAX(C_Tax_ID)
				  INTO	v_C_Tax_ID
				FROM	C_Tax 
				WHERE	IsDefault='Y' AND AD_Client_ID=so.AD_Client_ID;
				IF (v_C_Tax_ID IS NULL) THEN
					v_Message := 'No Default Tax found';
					v_Result := 0;
					GOTO FINISH_PROCESS;
				END IF;
			END IF;

			v_ResultStr := 'BPLocation_B';
			SELECT	MAX(C_BPartner_Location_ID)
			  INTO	v_BillTo_ID
			FROM	C_BPartner_Location
			WHERE	IsBillTo='Y' AND C_BPartner_ID=so.C_BPartner_ID;
			v_ResultStr := 'BPLocation_S';
			SELECT	MAX(C_BPartner_Location_ID)
			  INTO	v_C_BPartner_Location_ID
			FROM	C_BPartner_Location
			WHERE	IsShipTo='Y' AND C_BPartner_ID=so.C_BPartner_ID;


			-- What PO PriceListVersion to use (first record only) 
			v_ResultStr := 'PriceListVersion';
			DECLARE
				CURSOR Cur_PLV IS
					SELECT	plv.M_PriceList_Version_ID 
					FROM	C_OrderLine sol
					  INNER JOIN M_Product_PO po ON (sol.M_Product_ID=po.M_Product_ID)
					  INNER JOIN C_BPartner v ON (po.C_BPartner_ID=v.C_BPartner_ID)
					  INNER JOIN M_PriceList_Version plv ON (v.PO_PriceList_ID=plv.M_PriceList_ID AND plv.ValidFrom < sol.DateOrdered)
					WHERE sol.C_Order_ID = so.C_Order_ID
					  AND v.C_BPartner_ID = so.C_BPartner_ID
					ORDER BY plv.ValidFrom DESC;
			BEGIN
				FOR plv IN Cur_PLV LOOP
					IF (v_M_PriceList_Version_ID IS NULL) THEN
						v_M_PriceList_Version_ID := plv.M_PriceList_Version_ID;
					END IF;
				END LOOP;
			END;
			IF (v_M_PriceList_Version_ID IS NULL) THEN
				v_Message := 'No valid Price List Version for Order ' || so.DocumentNo;
				v_Result := 0;
				GOTO FINISH_PROCESS;
			END IF;

			--
			v_ResultStr := 'ID/DocNo';
			AD_Sequence_Next ('C_Order', so.AD_Client_ID, v_PO_Order_ID);
			AD_Sequence_DocType (v_C_DocType_ID, so.AD_Client_ID, v_DocumentNo);
			--
			v_ResultStr := 'InsertPOrder';
			INSERT INTO C_Order
				(AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
				C_Order_ID, IsSOTrx, DocumentNo, DocStatus, DocAction, Processing,Processed,
				C_DocType_ID, C_DocTypeTarget_ID, Description, IsApproved, IsCreditApproved,
				IsDelivered, IsInvoiced, IsPrinted, IsTransferred, IsSelected,
				SalesRep_ID, DateOrdered, DatePromised, DatePrinted, DateAcct,
				C_BPartner_ID, BillTo_ID, C_BPartner_Location_ID, C_BPartner_Contact_ID,
				POReference, IsDiscountPrinted, C_Currency_ID, PaymentRule, C_PaymentTerm_ID,
				InvoiceRule, DeliveryRule, FreightCostRule, FreightAmt, DeliveryViaRule, M_Shipper_ID,
				C_Charge_ID, ChargeAmt, PriorityRule, TotalLines, GrandTotal,
				M_WareHouse_ID, M_PriceList_ID, IsTaxIncluded, 
				C_Campaign_ID, C_Project_ID, C_Activity_ID, Posted, C_Payment_ID, C_CashLine_ID)
			VALUES
				(so.AD_Client_ID,so.AD_Org_ID,'Y',SysDate,so.UpdatedBy,SysDate,so.UpdatedBy,
				v_PO_Order_ID, 'N', v_DocumentNo, 'DR', 'CO', 'N','N',
				0, v_C_DocType_ID, so.DocumentNo || ': ' || so.Description, 'Y', 'N',	-- 0=new
				'N', 'N', 'N', 'N', 'N',
				so.SalesRep_ID, so.DateOrdered, so.DatePromised, null, so.DateAcct,
				so.C_BPartner_ID, v_BillTo_ID, v_C_BPartner_Location_ID, null,
				so.POReference, 'Y', so.C_Currency_ID, COALESCE(so.PaymentRulePO,'P'), COALESCE(so.PO_PaymentTerm_ID,v_C_PaymentTerm_ID),
				'D', 'A', 'I', 0, 'P', null,
				null, 0, so.PriorityRule, 0, 0,
			--	M_WareHouse_ID, M_PriceList_ID, IsTaxIncluded, 
				so.M_WareHouse_ID, so.M_PriceList_ID, 'N', 
			--	C_Campaign_ID, C_Project_ID, C_Activity_ID, Posted, C_Payment_ID, C_CashLine_ID)
				so.C_Campaign_ID, so.C_Project_ID, so.C_Activity_ID, 'N', null, null);


			v_ResultStr := 'InsertPOrderLine';
			v_Lines := 0;	--	should ne one line ony.
			FOR sol IN Cur_SOL (so.C_OrderLine_ID, v_M_PriceList_Version_ID) LOOP
				AD_Sequence_Next ('C_OrderLine', so.AD_Client_ID, v_NextID);
				INSERT INTO C_OrderLine
					(AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
					C_Order_ID, C_OrderLine_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, S_ResourceAssignment_ID, Ref_OrderLine_ID)
				VALUES
					(sol.AD_Client_ID,sol.AD_Org_ID,'Y',SysDate,so.UpdatedBy,SysDate,so.UpdatedBy,
					v_PO_Order_ID, v_NextID, sol.Line,
					so.C_BPartner_ID, v_C_BPartner_Location_ID, 
					sol.DateOrdered, sol.DatePromised, null, null,
					sol.Description, sol.M_Product_ID, sol.M_WareHouse_ID, sol.DirectShip,
					sol.C_UOM_ID, sol.QtyOrdered, 0, 0, 0,
					null, so.C_Currency_ID, sol.PriceList, sol.PriceStd, sol.PriceLimit,
					ROUND(sol.QtyOrdered*sol.PriceStd,2), null, 0, null, 0,
					v_C_Tax_ID, null, null, null, sol.C_OrderLine_ID);
				--
				UPDATE	C_OrderLine
				  SET	Ref_OrderLine_ID = v_NextID
				WHERE	C_OrderLine_ID = sol.C_OrderLine_ID;
				--
				v_Lines := v_Lines + 1;
			END LOOP;	--	For all SO Lines


			--	Log info - Log_ID must be unique in instance
			INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID, 
				P_ID, P_Number, P_Msg)
			  VALUES (p_PInstance_ID, AD_PInstance_Seq.NextVal, 
				v_PO_Order_ID, v_Lines, so.DocumentNo || ': @Created@ ' || v_DocumentNo);
			v_Created := v_Created + 1;
		END LOOP;	--	For all SO
	END;
	v_Message := '@Created@=' || v_Created;



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