gl_journal_post.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE GL_Journal_Post
(
	p_PInstance_ID    		IN NUMBER,
	p_GL_Journal_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+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: GL_Journal_Post.sql,v 1.2 2002/05/22 02:48:28 jjanke Exp $
 ***
 * Title:	GL Journal Post (preparation)
 * Description:
 *	- Check Control Amount
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr						VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Result						NUMBER := 1;	-- 0=failure
	v_Record_ID						NUMBER;
	v_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
		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
	--	Other Variables
	v_DocStatus						GL_Journal.DocStatus%TYPE;
	v_DocAction						GL_Journal.DocAction%TYPE;
	v_Processing					GL_Journal.Processing%TYPE;
	v_TotalDr						GL_Journal.TotalDr%TYPE;
	v_ControlAmt					GL_Journal.ControlAmt%TYPE;

BEGIN
    --  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || p_PInstance_ID);
	IF (p_PInstance_ID IS NOT NULL) THEN
		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
			v_Record_ID := p.Record_ID;
			v_AD_User_ID := p.AD_User_ID;
		END LOOP;	--	Get Parameter
	ELSE
		v_Record_ID := p_GL_Journal_ID;
	END IF;
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);

	/**
	 *	Status	-	Actions
	 *	DR			CO, VO
	 *	CO			CL
	 */

	v_ResultStr := 'ReadingJournal';
	SELECT	DocStatus, DocAction, Processing
	  INTO	v_DocStatus, v_DocAction, v_Processing
	FROM	GL_Journal
	WHERE	GL_Journal_ID = v_Record_ID
	FOR UPDATE;

	/**
	 *	Closed, Voided or Reversed - No action possible
	 */
	IF (v_DocStatus IN ('CL', 'VO', 'RE')) THEN
		v_Message := '@AlreadyPosted@';
		v_Result := 0;
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Unlock
	 */
	IF (v_DocAction = 'XL') THEN
		UPDATE	GL_Journal
	 	  SET	Processing = 'N',
		  		DocAction = '--',
				Updated = SysDate
		WHERE	GL_Journal_ID = v_Record_ID;
		GOTO FINISH_PROCESS;
	END IF;
	IF (v_Processing = 'Y') THEN
		v_Message := '@OtherProcessActive@';
		v_Result := 0;
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Close Order
	 */
	IF (v_DocAction = 'CL') THEN
		UPDATE 	GL_Journal
		  SET	DocStatus = 'CL',
		  		DocAction = '--',
				Processed = 'Y',
		  		Updated = SysDate
		WHERE	GL_Journal_ID = v_Record_ID;
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Void Order
	 */
	IF (v_DocAction = 'VO' AND v_DocStatus = 'DR') THEN
		--	Cancel all Lines
		UPDATE 	GL_JournalLine
		  SET	IsActive = 'N',
		  		Updated = SysDate
		WHERE	GL_Journal_ID = v_Record_ID;
		UPDATE 	GL_Journal
		  SET	DocStatus = 'VO',
		  		DocAction = '--',
				Processed = 'Y',
		  		Updated = SysDate
		WHERE	GL_Journal_ID = v_Record_ID;
		GOTO FINISH_PROCESS;
	END IF;

	--	 Lock journal comes here

	/**
	 *	Process Journal
	 */
	--	Recalculate Totals
	v_ResultStr := 'RecalculateTotals';
	UPDATE	GL_Journal
	  SET	TotalDr = (SELECT NVL(SUM(AmtSourceDr),0) FROM GL_JournalLine 
	  				WHERE GL_Journal_ID = v_Record_ID AND IsActive='Y'),
			TotalCr = (SELECT NVL(SUM(AmtSourceCr),0) FROM GL_JournalLine 
					WHERE GL_Journal_ID = v_Record_ID AND IsActive='Y')
   	WHERE	GL_Journal_ID = v_Record_ID;

	v_ResultStr := 'ReadingAmounts';
	SELECT	ControlAmt, TotalDr
	  INTO	v_ControlAmt, v_TotalDr
	FROM	GL_Journal
	WHERE	GL_Journal_ID = v_Record_ID
	FOR UPDATE;

	--	Check ControlAmt
	IF (v_ControlAmt <> 0 AND (v_ControlAmt <> v_TotalDr)) THEN
		v_Message := '@ControlAmtError@';
		v_Result := 0;
	ELSE
		UPDATE 	GL_Journal
		  SET	DocStatus = 'CO',
		  		DocAction = '--',
				Processed = 'Y',
		  		Updated = SysDate
		WHERE	GL_Journal_ID = v_Record_ID;
	END IF;


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

END GL_Journal_Post;
/