c_order_post.sql

Java写的ERP系统

			IF (Cur_ResStorage%NOTFOUND) THEN
				--	Get location info
				OPEN Cur_ResLocation(l.M_Warehouse_ID);
				FETCH Cur_ResLocation INTO v_Locator_ID, v_Client_ID, v_Org_ID;
				IF (Cur_ResLocation%NOTFOUND) THEN
					ROLLBACK;
					Message := 'NoLocation';
					v_Result := 0;
					GOTO END_PROCESSING;
				END IF;
				--	Create new storage record
				INSERT INTO M_Storage
					(M_Product_ID, M_Locator_ID,
					AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
					QtyOnHand, QtyReserved, QtyOrdered)
				VALUES
					(l.M_Product_ID, v_Locator_ID,
					v_Client_ID, v_Org_ID, 'Y', SysDate, 0, SysDate, 0,
					0, v_QtySO, v_QtyPO);
				--
				IF (SQL%ROWCOUNT <> 1) THEN
					ROLLBACK;
					ResultStr := 'LockingOrder';
					UPDATE	C_Order
					  SET	Processing = 'N'
					WHERE	C_Order_ID = Record_ID;
					COMMIT;
					RAISE_APPLICATION_ERROR(-20011, 'Did not insert reservation line');
				END IF;
				DBMS_OUTPUT.PUT_LINE('Reserved New Location=' || v_Locator_ID ||
					', Product=' || l.M_Product_ID || ', Qty=' || v_QtySO || '/' || v_QtyPO);
				CLOSE Cur_ResLocation;
			ELSE
				--	Update current storage record
				UPDATE	M_Storage
				  SET	QtyReserved = QtyReserved + v_QtySO,
						QtyOrdered = QtyOrdered + v_QtyPO
				WHERE	M_Locator_ID = v_Locator_ID
				  AND	M_Product_ID = l.M_Product_ID;
				--
				IF (SQL%ROWCOUNT <> 1) THEN
					ROLLBACK;
					ResultStr := 'LockingOrder';
					UPDATE	C_Order
					  SET	Processing = 'N'
					WHERE	C_Order_ID = Record_ID;
					COMMIT;			
					RAISE_APPLICATION_ERROR(-20011, 'Did not update reservation line');
				END IF;
				DBMS_OUTPUT.PUT_LINE('Reserved Update Location=' || v_Locator_ID ||
					', Product=' || l.M_Product_ID || ', Qty=' || v_QtySO || '/' || v_QtyPO);
			END IF;
			CLOSE Cur_ResStorage;

			--	Update Order Line
			UPDATE	C_OrderLine
			  SET	QtyReserved = QtyReserved + v_QtySO + v_QtyPO
			WHERE	C_OrderLine_ID = l.C_OrderLine_ID;
			IF (SQL%ROWCOUNT <> 1) THEN
				ROLLBACK;
				ResultStr := 'LockingOrder';
				UPDATE	C_Order
				  SET	Processing = 'N'
				WHERE	C_Order_ID = Record_ID;
				COMMIT;			
				RAISE_APPLICATION_ERROR(-20011, 'Did not update Line');
			END IF;
		END LOOP;	--	For all lines needing reservation
	END;	-- Reserve Inventory


	/**************************************************************************
	 *	Calculate Taxes and Totals
	 *************************************************************************/
	DECLARE
		CURSOR Cur_Tax IS 
			SELECT l.C_Tax_ID, o.IsTaxIncluded, 
				SUM(l.LineNetAmt) + SUM(l.FreightAmt) + SUM(l.ChargeAmt) AS LineNet,
				SUM(o.FreightAmt) + SUM(o.ChargeAmt) AS HeaderNet,
				t.Rate, t.IsSummary, c.StdPrecision
			FROM C_Order o, C_OrderLine l, C_Tax t, C_Currency c
			WHERE o.C_Order_ID=l.C_Order_ID
			  AND o.C_Order_ID=Record_ID		-- Parameter
			  AND l.C_Tax_ID=t.C_Tax_ID
			  AND o.C_Currency_ID=c.C_Currency_ID
			GROUP BY l.C_Tax_ID, o.IsTaxIncluded, t.Rate, t.IsSummary, c.StdPrecision
			ORDER BY 3 DESC;
		CURSOR Cur_MultiTax (Parent_ID NUMBER) IS
			SELECT	C_Tax_ID, Rate
			FROM	C_Tax
			WHERE	Parent_Tax_ID=Parent_ID;

		xTotalLines						NUMBER := 0;
		v_TaxBaseAmt						NUMBER := 0;
		xTaxAmt							NUMBER := 0;
		xGrandTotal						NUMBER := 0;
		HeaderNotAdded					BOOLEAN := TRUE;
	BEGIN
		ResultStr := 'DeleteOldTaxes';
		DELETE FROM C_OrderTax
		WHERE C_Order_ID = Record_ID;
		--	For all Tax Rates
		ResultStr := 'InsertNewTaxes';
		FOR t IN Cur_Tax LOOP
			v_TaxBaseAmt := t.LineNet; 
			xTotalLines := xTotalLines + v_TaxBaseAmt;		--	w/o Header Freight/Charge
			IF (HeaderNotAdded) THEN						-- 	add header net to first tax
				HeaderNotAdded := FALSE;
				v_TaxBaseAmt := v_TaxBaseAmt + t.HeaderNet;
			END IF;
			IF (t.IsSummary = 'N') THEN
				xTaxAmt := ROUND(v_TaxBaseAmt * t.Rate / 100, t.StdPrecision);
				xGrandTotal := xGrandTotal + v_TaxBaseAmt + xTaxAmt;
				--
				INSERT INTO C_OrderTax
					(C_Order_ID, C_Tax_ID,
					AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
					TaxBaseAmt, TaxAmt)
				VALUES 
					(Record_ID, t.C_Tax_ID,
					v_Client_ID, v_Org_ID, 'Y', SysDate, UpdatedBy, SysDate, UpdatedBy,
					v_TaxBaseAmt, xTaxAmt);
			ELSE	--	Multiple Taxes
				xGrandTotal := xGrandTotal + v_TaxBaseAmt;
				FOR mt IN Cur_MultiTax (t.C_Tax_ID) LOOP
					xTaxAmt := ROUND(v_TaxBaseAmt * mt.Rate / 100, t.StdPrecision);
					xGrandTotal := xGrandTotal + xTaxAmt;
					--
					INSERT INTO C_OrderTax
						(C_Order_ID, C_Tax_ID,
						AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
						TaxBaseAmt, TaxAmt)
					VALUES 
						(Record_ID, mt.C_Tax_ID,
						v_Client_ID, v_Org_ID, 'Y', SysDate, UpdatedBy, SysDate, UpdatedBy,
						v_TaxBaseAmt, xTaxAmt);			
				END LOOP;
			END IF;
		END LOOP;	--	Insert New Taxes
		--	Update Header
		UPDATE	C_Order
		  SET	TotalLines = xTotalLines,
				GrandTotal = xGrandTotal
		WHERE	C_Order_ID=Record_ID;
		DBMS_OUTPUT.PUT_LINE('GrandTotal=' || xGrandTotal);
	END;	--	Calculate Tax and Totals

	--	Synchronize Client/Org Ownership
	UPDATE	C_OrderLine
	  SET	AD_Client_ID = v_Client_ID,
			AD_Org_ID = v_Org_ID
	WHERE	C_Order_ID = Record_ID
	  AND	(AD_Client_ID <> v_Client_ID OR AD_Org_ID <> v_Org_ID);


	/**************************************************************************
	 *	Order Complete? - Something to do?
	 */
	BEGIN
		ResultStr := 'OrderCompleteCheck';
		SELECT	SUM(QtyOrdered*C_OrderLine_ID)-SUM(QtyDelivered*C_OrderLine_ID), 
			SUM(QtyOrdered*C_OrderLine_ID)-SUM(QtyInvoiced*C_OrderLine_ID)
		  INTO	ToDeliver, ToInvoice
		FROM	C_OrderLine
		WHERE	C_Order_ID = Record_ID;

		--	If no lines, ToDeliver is NULL
		IF ((ToDeliver = 0 AND ToInvoice = 0) OR (ToDeliver IS NULL AND ToInvoice IS NULL)) THEN
			DBMS_OUTPUT.PUT_LINE('OrderComplete');
			IF (DocAction = 'CL') THEN
				UPDATE	C_Order
				  SET	DocStatus = 'CL',
						DocAction = '--',
						DateAcct = DateOrdered,
						Processed = 'Y',
						Updated = SysDate
				WHERE	C_Order_ID = Record_ID;
			ELSIF (DocAction = 'VO') THEN 
				UPDATE	C_Order
				  SET	DocStatus = 'VO',
						DocAction = '--',
						Processed = 'Y',
						Updated = SysDate
				WHERE	C_Order_ID = Record_ID;
			ELSE
				UPDATE	C_Order
				  SET	DocStatus = 'CO',
						DocAction = '--',
						Processed = 'Y',
						Updated = SysDate
				WHERE	C_Order_ID = Record_ID;
			END IF;
			Message := '@AlreadyPosted@';
			GOTO END_PROCESSING;
		END IF;
	END;


	/**
	 *	In Progress	-----------------------------------------------------------
	 */
	UPDATE	C_Order
	  SET	DocStatus = 'IP',
			DateAcct = DateOrdered,
			Updated = SysDate
	WHERE	C_Order_ID = Record_ID;
	COMMIT;

	/************
	 *	Is Approved?
	 ***********/
	DBMS_OUTPUT.PUT_LINE('Approval - IsApproved=' || IsApproved);
	ResultStr := 'Approval';
	IF (IsApproved = 'N') THEN
		--	Check if it needs to be approved 
		ResultStr := 'TestApproval DocType_ID=' || v_DocType_ID;
		SELECT	IsApproved
		  INTO	IsApproved
		FROM	C_DocType
		WHERE	C_DocType_ID = v_DocType_ID;
		DBMS_OUTPUT.PUT_LINE('IsApproved=' || IsApproved);
		IF (IsApproved <> 'N') THEN		--	Approval needed
			IF (DocAction <> 'AP') THEN
				UPDATE	C_Order
				  SET	DocStatus = 'NA',
						DocAction = 'AP',
						Processed = 'N',
						Updated = SysDate
				WHERE	C_Order_ID = Record_ID;
				Message := 'NotApproved';
				DBMS_OUTPUT.PUT_LINE('Approval needed');
				GOTO END_PROCESSING;
			END IF;
		ELSE
			UPDATE	C_Order
			  SET	IsApproved = 'Y'
			WHERE	C_Order_ID = Record_ID;
		END IF;
	END IF;

	/**
	 *	Finished with processing
	 */
	IF (DocAction = 'PR') THEN
		ResultStr := 'FinishProcessing';
		UPDATE	C_Order
		  SET	DocStatus = 'IP',
				DocAction = 'CO',
				Processed = 'N',
				Updated = SysDate
		WHERE	C_Order_ID = Record_ID;
	--	C_Order_PickList(NULL, Record_ID);		--	Print PickList
		GOTO END_PROCESSING;
	END IF;


	/**************************************************************************
	 *	Prepayment Order 	Create Invoice
	 *************************************************************************/
	IF (v_DocSubTypeSO = 'PR' AND DocStatus <> 'WP' ) THEN
		DBMS_OUTPUT.PUT_LINE('Create PreInvoice - ' || Record_ID);
		ResultStr := 'CreatePreInvoice';
		C_Invoice_Create(NULL, Record_ID, Invoice_ID);
		DBMS_OUTPUT.PUT_LINE('  PreInvoice - ' || Invoice_ID);
		IF (Invoice_ID = 0) THEN
			Message := 'PreInvoiceCreateFailed';
			GOTO END_PROCESSING;
		END IF;
		C_Invoice_Post(NULL, Invoice_ID);
		--
		UPDATE	C_Order
		  SET	DocStatus = 'WP',
				DocAction = '--',
				Processed = 'Y',
				Updated = SysDate
		WHERE	C_Order_ID = Record_ID;
		--
		GOTO END_PROCESSING;
	END IF;


	/**
	 *	Deliver Direct Shipments
	 */
	ResultStr := 'NonInventoryDelivery';
	UPDATE	C_OrderLine
	  SET	QtyDelivered = QtyOrdered
	WHERE	DirectShip='Y'
	  AND	C_Order_ID=Record_ID;


	/**************************************************************************
	 *	Will-Call + Walk In Processing
	 *	--
	 *	(W)illCall(I)nvoice - (W)illCall(P)ickup - (W)alkIn(R)eceipt
	 *	--
	 *************************************************************************/
	IF (v_DocSubTypeSO IN ('WI', 'WP', 'WR')) THEN
		/************
		 *	Shipment
		 */
		DBMS_OUTPUT.PUT_LINE('Create Shipment - ' || Record_ID);
		ResultStr := 'CreateShipment';
		M_InOut_Create(NULL, Record_ID, NULL, 'Y', InOut_ID);	--	Force Delivery
		DBMS_OUTPUT.PUT_LINE('  Shipment - ' || InOut_ID);
		IF (InOut_ID = 0) THEN
			Message := 'InOutCreateFailed';
			GOTO END_PROCESSING;
		END IF;

		IF (v_DocSubTypeSO IN ('WI', 'WR')) THEN
			/************
			 *	Invoice
			 */
			DBMS_OUTPUT.PUT_LINE('Create Invoice - ' || Record_ID);
			ResultStr := 'CreateInvoice';
			C_Invoice_Create(NULL, Record_ID, Invoice_ID);
			DBMS_OUTPUT.PUT_LINE('  Invoice - ' || Invoice_ID);
			IF (Invoice_ID = 0) THEN
				Message := 'InvoiceCreateFailed';
				GOTO END_PROCESSING;
			END IF;
		END IF;
	END IF;


	/**
	 *	Final Completeness check
	 */
	SELECT	SUM(QtyOrdered*C_OrderLine_ID)-SUM(QtyDelivered*C_OrderLine_ID), 
		SUM(QtyOrdered*C_OrderLine_ID)-SUM(QtyInvoiced*C_OrderLine_ID)
	  INTO	ToDeliver, ToInvoice
	FROM	C_OrderLine
	WHERE	C_Order_ID = Record_ID;

	--	Nothing to Deliver + Invoice for (W)illCall(I)nvoice and (W)alkIn(R)eceipt
	IF (v_DocSubTypeSO IN ('WI', 'WR') AND ToDeliver = 0 AND ToInvoice = 0) THEN
		UPDATE	C_Order
		  SET	DocStatus = 'CO',
				DocAction = '--',
				IsDelivered = 'Y',
				IsInvoiced = 'Y',
				Processed = 'Y',
				Updated = SysDate
		WHERE	C_Order_ID = Record_ID;
		IF (DocAction = 'VO') THEN
			UPDATE	C_Order
			  SET	DocStatus = 'VO'
			WHERE	C_Order_ID = Record_ID;
		END IF;
	END IF;
	--	Nothing to Deliver for (W)illCall(P)ickup (Invoice generated independently)
	IF (v_DocSubTypeSO = 'WP' AND ToDeliver = 0) THEN
		UPDATE	C_Order
		  SET	DocStatus = 'CO',
				DocAction = '--',
				IsDelivered = 'Y',
				Processed = 'Y',
				Updated = SysDate
		WHERE	C_Order_ID = Record_ID;
		IF (DocAction = 'VO') THEN
			UPDATE	C_Order
			  SET	DocStatus = 'VO'
			WHERE	C_Order_ID = Record_ID;
		END IF;
	END IF;

	--	We are done with standard sales orders
	IF (DocAction IN ('CO', 'CL', 'VO') AND v_DocSubTypeSO = 'SO') THEN
		UPDATE	C_Order
		  SET	DocStatus = 'CO',
				DocAction = '--',
				Processed = 'Y',
				Updated = SysDate
		WHERE	C_Order_ID = Record_ID;
	END IF;

	--	Purchase Orders
	IF (DocAction IN ('CO', 'CL', 'VO') AND v_DocSubTypeSO IS NULL) THEN
		UPDATE	C_Order
		  SET	DocStatus = 'CO',
				DocAction = '--',
				Processed = 'Y',
				Updated = SysDate
		WHERE	C_Order_ID = Record_ID;
	END IF;



	--	End Processing --------------------------------------------------------
<<END_PROCESSING>>
	--	Cloase Order
	IF (DocAction = 'CL') THEN
		UPDATE	C_Order
		  SET	DocStatus = 'CL',
				DocAction = '--',
				Processed = 'Y'
		WHERE	C_Order_ID = Record_ID;
	END IF;

	ResultStr := 'UnLockingOrder';
	UPDATE	C_Order
	  SET	Processing = 'N',
			Updated = SysDate
	WHERE	C_Order_ID = Record_ID;
	COMMIT;

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

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

END C_Order_Post;
/