-- ******************************************************************
-- *
-- * Copyright (c) 2000, 2007, Oracle. All rights reserved.
-- *
-- ******************************************************************
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CIPMTREA' AND type = 'P')
   DROP PROCEDURE RPTUSER.CIPMTREA
GO

CREATE PROCEDURE CIPMTREA
@P_USER_ID CHAR(8),
@P_ACCT_PERIOD CHAR(11)
AS

	DECLARE @V_LANGUAGE_CD CHAR(3)
	DECLARE	@V_BEGIN_DT DATETIME
	DECLARE @V_END_DT DATETIME
	DECLARE @V_RETURN_CD INT
	DECLARE @V_FISCAL_YEAR DECIMAL(4)
	DECLARE @V_ACCT_PERIOD DECIMAL(3)
	DECLARE @V_CALENDAR_ID CHAR(2)
	DECLARE @V_POUND_1     INT
	DECLARE @V_POUND_2     INT
	DECLARE @V_PERIOD_DESCR VARCHAR(60)

	SELECT @V_RETURN_CD = 0
BEGIN
	IF @P_ACCT_PERIOD IS NULL OR @P_ACCT_PERIOD = ' '
		SELECT @V_RETURN_CD = 1004
   	ELSE
	BEGIN
		SELECT @V_POUND_1 = CHARINDEX ('|', @P_ACCT_PERIOD)
		SELECT @V_POUND_2 = CHARINDEX ('|', SUBSTRING(@P_ACCT_PERIOD, @V_POUND_1+1, DATALENGTH(@P_ACCT_PERIOD))) + @V_POUND_1

            	SELECT @V_CALENDAR_ID = RTRIM(LTRIM (SUBSTRING (@P_ACCT_PERIOD, 1,  @V_POUND_1 - 1)))
            	IF @@ERROR <> 0
                	SELECT @V_RETURN_CD = 1002

            	SELECT @V_FISCAL_YEAR = 
		     CAST( (LTRIM(RTRIM(SUBSTRING (@P_ACCT_PERIOD, @V_POUND_1 + 1,  @V_POUND_2 - @V_POUND_1 - 1)))) AS DECIMAL)
            	IF @@ERROR <> 0
                	SELECT @V_RETURN_CD = 1005

            	SELECT @V_ACCT_PERIOD =    
                	CAST( (LTRIM(RTRIM(SUBSTRING (@P_ACCT_PERIOD, @V_POUND_2 + 1, DATALENGTH(@P_ACCT_PERIOD))))) AS DECIMAL)
            	IF @@ERROR <> 0
                	SELECT @V_RETURN_CD = 1006
	END

	SELECT	@V_LANGUAGE_CD=LANGUAGE_CD
	FROM	SC_USER
	WHERE  SC_USER.USER_ID = @P_USER_ID;

	IF @@ROWCOUNT = 0
		SELECT @V_LANGUAGE_CD = 'ENG'
	
      	SELECT	
		@V_BEGIN_DT=BEGIN_DT 
		, @V_END_DT=END_DT 
		, @V_PERIOD_DESCR=PERIOD_DESCR
	FROM	   
		CI_CAL_PERIOD
	  	, CI_CAL_PERIOD_L
	WHERE   
		CI_CAL_PERIOD.FISCAL_YEAR = @V_FISCAL_YEAR
	  	AND CI_CAL_PERIOD.ACCOUNTING_PERIOD = @V_ACCT_PERIOD
	  	AND CI_CAL_PERIOD.CALENDAR_ID = @V_CALENDAR_ID
	  	AND CI_CAL_PERIOD.FISCAL_YEAR = CI_CAL_PERIOD_L.FISCAL_YEAR
	  	AND CI_CAL_PERIOD.ACCOUNTING_PERIOD = CI_CAL_PERIOD_L.ACCOUNTING_PERIOD
	  	AND CI_CAL_PERIOD.CALENDAR_ID = CI_CAL_PERIOD_L.CALENDAR_ID
	  	AND CI_CAL_PERIOD_L.LANGUAGE_CD = @V_LANGUAGE_CD;
	IF @@ROWCOUNT = 0
      	BEGIN 
      		SELECT @V_BEGIN_DT = NULL
	  	SELECT @V_END_DT = NULL 
	  	SELECT @V_RETURN_CD = 1001
      	END

   	IF @V_RETURN_CD = 0
	BEGIN
      		SELECT	 @V_RETURN_CD RETURN_CODE
	  		 ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
			 ,@V_PERIOD_DESCR V_PERIOD_DESCR;
	END
   	ELSE
	BEGIN
      		SELECT	 MESSAGE_NBR RETURN_CODE
	  	         ,MESSAGE_TEXT
 			 ,CAST (NULL AS VARCHAR) V_PERIOD_DESCR
      		FROM    CI_MSG_L
	  	WHERE   MESSAGE_CAT_NBR = 30
	  		AND	  MESSAGE_NBR = @V_RETURN_CD
	  		AND	  LANGUAGE_CD = @V_LANGUAGE_CD;
	END
RETURN
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
