m_movement_post.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE M_Movement_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+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: M_Movement_Post.sql,v 1.2 2002/05/22 02:48:28 jjanke Exp $
 ***
 * Title:	Post Movements
 * Description:
 ************************************************************************/
AS
	--	Logistice
	ResultStr						VARCHAR2(2000);
	Message							VARCHAR2(2000);
	Record_ID						NUMBER;
	--	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;
	--	Parameter Variables
	IsProcessing					CHAR(1);
	IsProcessed						CHAR(1);
	NoProcessed						NUMBER := 0;
	MoveDate						DATE;
	Client_ID						NUMBER (10);

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);

	--	Reading Movement
	SELECT	MovementDate, Processing, Processed, AD_Client_ID
	  INTO	MoveDate, IsProcessing, IsProcessed, Client_ID
	FROM	M_Movement
	WHERE	M_Movement_ID = Record_ID
	FOR UPDATE;

	IF (IsProcessing = 'Y') THEN
		Message := '@OtherProcessActive@';
		GOTO END_PROCESS;
	END IF;
	IF (IsProcessed = 'Y') THEN
		Message := '@AlreadyPosted@';
		GOTO END_PROCESS;
	END IF;

	--	Start Processing ------------------------------------------------------
	ResultStr := 'LockingMovement';
	UPDATE	M_Movement
	  SET	Processing = 'Y',
	  		Processed = 'Y'
	WHERE	M_Movement_ID = Record_ID;
	COMMIT;	  		

	/**
	 *	Update Storage
	 */
	-- FROM Create missing zero qty record to update later
	ResultStr := 'InsertStorageFROM';
	INSERT INTO M_Storage
		(M_Product_ID, M_Locator_ID,
		AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
		QtyOnHand, QtyReserved, QtyOrdered)
	SELECT 	l.M_Product_ID, l.M_Locator_ID,
		loc.AD_Client_ID, loc.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
		0, 0, 0
	FROM 	M_Locator loc, M_MovementLine l
	WHERE	loc.M_Locator_ID=l.M_Locator_ID
	 AND	l.M_Movement_ID = Record_ID 
	 AND NOT EXISTS 
			(SELECT * FROM M_Storage s 
			WHERE s.M_Product_ID=l.M_Product_ID
			  AND s.M_Locator_ID=l.M_Locator_ID);
	Message := '@Inserted@= ' || SQL%ROWCOUNT;

	-- TO Create missing zero qty record to update later
	ResultStr := 'InsertStorageTO';
	INSERT INTO M_Storage
		(M_Product_ID, M_Locator_ID,
		AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
		QtyOnHand, QtyReserved, QtyOrdered)
	SELECT 	l.M_Product_ID, l.M_LocatorTo_ID,
		loc.AD_Client_ID, loc.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
		0, 0, 0
	FROM 	M_Locator loc, M_MovementLine l
	WHERE	loc.M_Locator_ID=l.M_LocatorTo_ID
	 AND	l.M_Movement_ID = Record_ID 
	 AND NOT EXISTS 
			(SELECT * FROM M_Storage s 
			WHERE s.M_Product_ID=l.M_Product_ID
			  AND s.M_Locator_ID=l.M_LocatorTo_ID);
	Message := Message || ' -> ' || SQL%ROWCOUNT;

	--	FROM
	ResultStr := 'UpdateStorageFROM';
	UPDATE 	M_Storage s
	  SET	QtyOnHand = (SELECT QtyOnHand - MovementQty FROM M_MovementLine l
					WHERE l.M_Movement_ID = Record_ID
					  AND l.M_Product_ID = s.M_Product_ID
					  AND l.M_Locator_ID = s.M_Locator_ID
					  AND l.IsActive = 'Y'),
	  		Updated = SysDate,
			UpdatedBy = 0
	WHERE EXISTS	(SELECT * FROM M_MovementLine l
	  				WHERE l.M_Movement_ID = Record_ID
					  AND l.M_Product_ID = s.M_Product_ID
					  AND l.M_Locator_ID = s.M_Locator_ID
					  AND l.IsActive = 'Y');
	Message := Message || ', @Updated@=' || SQL%ROWCOUNT;
	--	TO
	ResultStr := 'UpdateStorageTO';
	UPDATE 	M_Storage s
	  SET	QtyOnHand = (SELECT QtyOnHand + MovementQty FROM M_MovementLine l
					WHERE l.M_Movement_ID = Record_ID
					  AND l.M_Product_ID = s.M_Product_ID
					  AND l.M_LocatorTo_ID = s.M_Locator_ID
					  AND l.IsActive = 'Y'),
	  		Updated = SysDate,
			UpdatedBy = 0
	WHERE EXISTS	(SELECT * FROM M_MovementLine l
	  				WHERE l.M_Movement_ID = Record_ID
					  AND l.M_Product_ID = s.M_Product_ID
					  AND l.M_LocatorTo_ID = s.M_Locator_ID
					  AND l.IsActive = 'Y');
	Message := Message || ' -> ' || SQL%ROWCOUNT;
	
	/**
	 *	Accounting first step
	 */
	DECLARE
		CURSOR Cur_MoveLine	IS
			SELECT	*
			FROM	M_MovementLine
			WHERE	M_Movement_ID = Record_ID
			ORDER BY Line;
	  	NextNo					NUMBER;
   	BEGIN
		FOR ml IN Cur_MoveLine LOOP
			ResultStr := 'Transaction for line' || ml.Line;
			--	FROM
			AD_Sequence_Next('M_Transaction', Client_ID, NextNo);
			INSERT INTO M_Transaction
				(M_Transaction_ID,
				AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
				MovementType, M_Locator_ID, M_Product_ID,
				MovementDate, MovementQty,
				M_MovementLine_ID)
			VALUES
				(NextNo,
				ml.AD_Client_ID, ml.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
				'M-', ml.M_Locator_ID, ml.M_Product_ID,
				MoveDate, (ml.MovementQty * -1),
				ml.M_MovementLine_ID);
			--	TO
			AD_Sequence_Next('M_Transaction', Client_ID, NextNo);
			INSERT INTO M_Transaction
				(M_Transaction_ID,
				AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
				MovementType, M_Locator_ID, M_Product_ID,
				MovementDate, MovementQty,
				M_MovementLine_ID)
			VALUES
				(NextNo,
				ml.AD_Client_ID, ml.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
				'M+', ml.M_LocatorTo_ID, ml.M_Product_ID,
				MoveDate, ml.MovementQty,
				ml.M_MovementLine_ID);
		END LOOP;
	END;

	--	End Processing --------------------------------------------------------
<<END_PROCESSING>>
	ResultStr := 'UnLockingMovement';
	UPDATE	M_Movement
	  SET	Processing = 'N'
	WHERE	M_Movement_ID = Record_ID;
	COMMIT;

<<END_PROCESS>>
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || Message);
    UPDATE  AD_PInstance
    SET Updated = SysDate,
        IsProcessing = 'N',
        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 M_Movement_Post;
/