gl_period_open.sql

Java写的ERP系统

CREATE OR REPLACE FUNCTION GL_Period_Open
(
	p_AD_Client_ID		IN	NUMBER,
	p_DateAcct		IN	DATE,
	p_DocBaseType		IN	CHAR,
	p_C_AcctSchema_ID	IN	NUMBER	-- DEFAULT NULL
)
RETURN 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: GL_Period_Open.sql,v 1.5 2003/02/07 05:49:58 jjanke Exp $
 ***
 * Title:	Period Control
 * Description:
 *	Determine if period is open for DateAcct and DocBaseType (API, ..)
 *	If C_AcctSchema_ID is specified, check only for this schema, 
 *	otherwise for the first of AD_Client_ID
 *	Return 
 *		C_Period_ID if period is open ot 0 if auto control
 *		-1 if period is not open
 *
 *	Test:
 *		SELECT GL_Period_Open (11, '01-JAN-2002', 'ARI') FROM DUAL
 ************************************************************************/
AS
	v_C_Calendar_ID		NUMBER(10);
	v_C_Period_ID			NUMBER(10) := -1;	--	not found
	v_Temp_Period			NUMBER(10);
	v_PeriodStatus			C_PeriodControl.PeriodStatus%TYPE;
	--
	CURSOR	CUR_AS	IS
		SELECT	AutoPeriodControl, Period_OpenHistory, Period_OpenFuture
		FROM		C_AcctSchema
		WHERE	AD_Client_ID = p_AD_Client_ID
		  AND	(p_C_AcctSchema_ID IS NULL OR C_AcctSchema_ID = p_C_AcctSchema_ID)
		  AND	ROWNUM=1;
BEGIN
	--	Get Calendar
	SELECT	MAX(C_Calendar_ID)
	  INTO	v_C_Calendar_ID
	FROM		AD_ClientInfo
	WHERE	AD_Client_ID = p_AD_Client_ID;
	IF (v_C_Calendar_ID IS NULL) THEN
		DBMS_OUTPUT.PUT_LINE('NO Calendar');
		RETURN NULL;
	END IF;
	--
	FOR a IN CUR_AS LOOP
		DBMS_OUTPUT.PUT_LINE('AcctSchema - Auto=' || a.AutoPeriodControl);
		v_C_Period_ID := 1;
		IF (a.AutoPeriodControl = 'Y') THEN
			IF (p_DateAcct
					BETWEEN (TRUNC(SysDate,'MM')-a.Period_OpenHistory) 
					AND (ADD_MONTHS(TRUNC(SysDate,'MM'),1)+a.Period_OpenFuture)) THEN
				DBMS_OUTPUT.PUT_LINE('Open(A) ' || (TRUNC(SysDate,'MM')-a.Period_OpenHistory) 
					|| ' - ' || (ADD_MONTHS(TRUNC(SysDate,'MM'),1)+a.Period_OpenFuture));
				v_C_Period_ID := 0;		--	open
			ELSE
				DBMS_OUTPUT.PUT_LINE('Not in Limits (A) ' || (TRUNC(SysDate,'MM')-a.Period_OpenHistory) 
					|| ' - ' || (ADD_MONTHS(TRUNC(SysDate,'MM'),1)+a.Period_OpenFuture));
				v_C_Period_ID := -1;	--	closed
			END IF;
		END IF;	--	No AutoPeriodControl --

		--	Get Period if not AutoPeriodControl aoutside limits
		IF (v_C_Period_ID <> -1) THEN
			SELECT	MAX(p.C_Period_ID) 
			  INTO	v_C_Period_ID
			FROM		C_Period p, C_Year y
			WHERE	TRUNC(p_DateAcct) BETWEEN TRUNC(p.StartDate) AND TRUNC(p.EndDate)
			  AND	p.C_Year_ID=y.C_Year_ID
			  AND	y.C_Calendar_ID = v_C_Calendar_ID
			  AND	p.PeriodType = 'S';		--	standard period

			--	We have no Period defined
			IF (v_C_Period_ID IS NULL) THEN
				DBMS_OUTPUT.PUT_LINE('No Period found for Date ' || p_DateAcct);
				v_C_Period_ID := -1;
			ELSIF (a.AutoPeriodControl <> 'Y') THEN
				--	Check PeriodControl for Period and Document Type
				SELECT	MAX(PeriodStatus)
				  INTO	v_PeriodStatus
				FROM		C_PeriodControl
				WHERE	C_Period_ID = v_C_Period_ID
				  AND	DocBaseType = p_DocBaseType;

				IF (v_PeriodStatus IS NULL) THEN
					DBMS_OUTPUT.PUT_LINE('No PeriodStatus found for Period ' || v_C_Period_ID);
					v_C_Period_ID := -1;
				ELSIF (v_PeriodStatus = 'O') THEN
					DBMS_OUTPUT.PUT_LINE('Open');
				ELSE
					DBMS_OUTPUT.PUT_LINE('NOT Open: PeriodStatus=' || v_PeriodStatus);
					v_C_Period_ID := -1;
				END IF;
			END IF;
		END IF;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('Period_ID=' || v_C_Period_ID);
	RETURN v_C_Period_ID;
END GL_Period_Open;
/