m_inout_create.sql

Java写的ERP系统

			--	Skip zero product lines (comments are zero qty)
			IF (v_Qty = 0 AND ol.M_Product_ID IS NOT NULL) THEN
				GOTO Next_Line;
			END IF;
			
			--				
			DECLARE
				CURSOR Cur_Storage IS
					SELECT	s.QtyOnHand, s.QtyReserved, s.M_Locator_ID 
					FROM	M_Storage s, M_Locator l
					WHERE	s.M_Product_ID=ol.M_Product_ID
					  AND	s.M_Locator_ID=l.M_Locator_ID
					  AND	l.M_Warehouse_ID=ol.M_Warehouse_ID
					ORDER BY l.PriorityNo;
				v_LocatorQty				NUMBER;
				v_IsStocked					NUMBER := 0;

			BEGIN
				--	Is it a standard stocked product?
				IF (ol.M_Product_ID IS NOT NULL) THEN
					SELECT	COUNT(*) 
					  INTO	v_IsStocked
					FROM	M_Product
					WHERE	M_Product_ID=ol.M_Product_ID 
					  AND	IsStocked = 'Y' AND ProductType = 'I';
				END IF;

				--	Item is stocked - check availability
				IF (v_IsStocked <> 0) THEN
					--	check every locator availability and if qty available
					--		create InOut line, update storage reservation + qty
					FOR s IN Cur_Storage LOOP
						--	How much do we deliver from here?
						v_ResultStr := 'Deliver';
						IF (p_ForceDelivery = 'Y') THEN
							v_LocatorQty := v_Qty;
						ELSIF (v_Qty > s.QtyOnHand) THEN
							v_LocatorQty := s.QtyOnHand;
						ELSE
							v_LocatorQty := v_Qty;
						END IF;

						IF (v_LocatorQty <> 0) THEN
							--	Create InOut Line
							AD_Sequence_Next('M_InOutLine', o.C_Order_ID, v_NextNo);
							--	
							DBMS_OUTPUT.PUT_LINE('    Line ' || ol.Line || '  Qty=' || v_LocatorQty);
							v_ResultStr := 'CreateInOutLine';
							INSERT INTO M_InOutLine
								(M_InOutLine_ID, Line, M_InOut_ID, C_OrderLine_ID,
								AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
								M_Product_ID, C_UOM_ID, M_Locator_ID,
								MovementQty, Description, IsInvoiced, Lot, SerNo)
							VALUES
								(v_NextNo, ol.Line, p_InOut_ID, ol.C_OrderLine_ID,
								ol.AD_Client_ID, ol.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
								ol.M_Product_ID, ol.C_UOM_ID, s.M_Locator_ID,
								v_LocatorQty, ol.Description, 'N', ol.Lot, ol.SerNo);
							v_lines := v_lines + 1;
						END IF;
					
						v_Qty := v_Qty - v_LocatorQty;
						EXIT WHEN v_Qty = 0;
					END LOOP;	--	Storage
				
				--	Copy Ad-hoc lines, Comments OR Service Items
				ELSE
					--	Create InOut Line
					AD_Sequence_Next('M_InOutLine', o.C_Order_ID, v_NextNo);
					--	
					DBMS_OUTPUT.PUT_LINE('    Line* ' || ol.Line || '  Qty=' || v_Qty);
					v_ResultStr := 'CreateInOutLine*';
					INSERT INTO M_InOutLine
						(M_InOutLine_ID, Line, M_InOut_ID, C_OrderLine_ID,
						AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
						M_Product_ID, C_UOM_ID, M_Locator_ID,
						MovementQty, Description, IsInvoiced, Lot, SerNo)
					VALUES
						(v_NextNo, ol.Line, p_InOut_ID, ol.C_OrderLine_ID,
						ol.AD_Client_ID, ol.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
						ol.M_Product_ID, ol.C_UOM_ID, NULL,
						v_Qty, ol.Description, 'N', ol.Lot, ol.SerNo);
					v_lines := v_lines + 1;
				END IF;
			END;

			<<Next_Line>>
			FETCH Cur_OrderLine INTO ol;
			EXIT WHEN Cur_OrderLine%NOTFOUND;
		END LOOP;	--	Order Line Loop

		IF (v_lines = 0) THEN
			ROLLBACK TO SAVEPOINT sp_new_shipment;
		ELSE
			v_Message := v_Message || '@C_Order_ID@ ' || o.DocumentNo || ' -> @M_InOut_ID@ ' || v_DocumentNo || '; ';
			IF (p_PInstance_ID IS NOT NULL) THEN
				v_ResultStr := 'InsertLog';
				INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID, P_ID, P_Msg)
				  VALUES (p_PInstance_ID, AD_PInstance_Seq.NextVal, p_InOut_ID, v_DocumentNo);
			END IF;
			--	Post Shipment
			M_InOut_Post(NULL, p_InOut_ID);
		END IF;

		<<Next_Order>>
		IF (Cur_OrderLine%ISOPEN) THEN
			CLOSE Cur_OrderLine;
		END IF;
	END LOOP;	-- Order Header Loop
	GOTO FINISH_PROCESS;



	/**************************************************************************
	 *	Create Shipment from Invoice
	 *************************************************************************/
<<CREATE_FROM_INVOICE>>
	DECLARE
		CURSOR	Cur_Invoice IS
			SELECT	* FROM C_Invoice 
			WHERE	C_Invoice_ID=p_Invoice_ID;
		CURSOR	Cur_Lines	IS
			SELECT	* FROM C_InvoiceLine
			WHERE	C_Invoice_ID=p_Invoice_ID
			  AND	M_Product_ID IS NOT NULL;
		v_Warehouse_ID							NUMBER;
		v_IsDocNoControlled						CHAR(1);
	BEGIN
		FOR i IN Cur_Invoice LOOP
			v_ResultStr := 'CreateInOut';
			--	Get Warehouse from Locator
			SELECT	M_Warehouse_ID
			  INTO	v_Warehouse_ID
			FROM		M_Locator
			WHERE	M_Locator_ID = p_Locator_ID;
			--	Get Shipment Doc Number
			SELECT	C_DocType_ID, IsDocNoControlled
			  INTO	v_DocType_ID, v_IsDocNoControlled
			FROM		C_DocType 
			WHERE	DocBaseType='MMR' 
			  AND	AD_Client_ID=i.AD_Client_ID AND ROWNUM=1;
			IF (v_IsDocNoControlled = 'Y') THEN
				AD_Sequence_DocType(v_DocType_ID, i.C_Invoice_ID, v_DocumentNo);
			ELSE
				v_DocumentNo := i.DocumentNo;
			END IF;
			AD_Sequence_Next('M_InOut', i.C_Invoice_ID, p_InOut_ID);
			--
			DBMS_OUTPUT.PUT_LINE('  InOut_ID=' || p_InOut_ID || ' DocumentNo=' || v_DocumentNo);
			v_ResultStr := 'InsertInOut ' || p_InOut_ID;
			INSERT INTO M_InOut
				(M_InOut_ID, C_Order_ID, IsSOTrx, C_Invoice_ID,
				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, NULL, i.IsSoTrx, i.C_Invoice_ID,
				i.AD_Client_ID, i.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
				v_DocumentNo, v_DocType_ID, i.Description, 'N', 
				'V+', i.DateInvoiced, i.DateInvoiced,
				i.C_BPartner_ID, i.C_BPartner_Location_ID, i.C_BPartner_Contact_ID,
				v_Warehouse_ID, i.POReference, i.DateInvoiced, 'A',	--	Available
				'I', 0,	--	Freight included
				'P', NULL, i.C_Charge_ID, i.ChargeAmt, '3',	--	PickUp, High Priority
				'DR', 'CO', 'N', 'N');
			--
			IF (p_PInstance_ID IS NOT NULL AND p_Selection='Y') THEN
				INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID, P_ID, P_Msg)
				  VALUES (p_PInstance_ID, i.C_Invoice_ID, p_InOut_ID, '@Created@ @Shipment@ ' || v_DocumentNo);
			END IF;
			--	Lines
			FOR il IN Cur_Lines LOOP
				AD_Sequence_Next('M_InOutLine', i.C_Invoice_ID, v_NextNo);
				--	
				DBMS_OUTPUT.PUT_LINE('    Line* ' || il.Line);
				v_ResultStr := 'CreateInOutLine*';
				INSERT INTO M_InOutLine
					(M_InOutLine_ID, Line, M_InOut_ID, C_OrderLine_ID,
					AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
					M_Product_ID, C_UOM_ID, M_Locator_ID,
					MovementQty, Description, IsInvoiced, Lot, SerNo)
				VALUES
					(v_NextNo, il.Line, p_InOut_ID, il.C_OrderLine_ID,
					il.AD_Client_ID, il.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
					il.M_Product_ID, il.C_UOM_ID, p_Locator_ID,
					il.QtyInvoiced, il.Description, 'N', NULL, NULL);
			END LOOP;	--	InvoiceLines
		END LOOP;	--	Invoices
		--	Post Shipment
		M_InOut_Post (NULL, p_InOut_ID);
	END;



	/*************************************************************************/	
<<FINISH_PROCESS>>
	IF (p_PInstance_ID IS NOT NULL) THEN
		--  Update AD_PInstance
		DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
		UPDATE	AD_PInstance
		  SET	Updated = SysDate,
				IsProcessing = 'N',
				Result = 1,					-- success
				ErrorMsg = v_Message
		WHERE	AD_PInstance_ID=p_PInstance_ID;
		COMMIT;
	ELSE
		DBMS_OUTPUT.PUT_LINE('<<M_InOut_Create finished>> ' || v_Message);
	END IF;
	--
	RETURN;

EXCEPTION
	WHEN	 OTHERS THEN
		v_ResultStr := v_ResultStr || ': ' || SQLERRM || ' - ' || v_Message;
		DBMS_OUTPUT.PUT_LINE(v_ResultStr);
		ROLLBACK;
		IF (p_PInstance_ID IS NOT NULL) THEN
			UPDATE	AD_PInstance
			  SET	Updated = SysDate,
					IsProcessing = 'N',
					Result = 0,				-- failure
					ErrorMsg = v_ResultStr
			WHERE	AD_PInstance_ID=p_PInstance_ID;
			COMMIT;
		ELSE
			DECLARE
				v_Code	NUMBER			:= SQLCode;
			BEGIN
				INSERT INTO DBA_ErrorLog (DBA_ErrorLog_ID,Created,Code,Msg,Info)
				VALUES (DBA_ErrorLog_Seq.NextVal,SysDate,v_Code,v_ResultStr,'M_InOut_Create');
			END;
		END IF;
		p_InOut_ID := 0;				--	Error Indicator
		RETURN;

END M_InOut_Create;
/