-- ******************************************************************
-- *
-- * 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 = 'CIGLACSM' AND type = 'P')
   DROP PROCEDURE RPTUSER.CIGLACSM
GO

CREATE PROCEDURE CIGLACSM
@P_USER_ID CHAR(8),
@P_ACCT_PERIOD CHAR(11),
@P_CHAR_TYPE CHAR(8)

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_CHAR_TYPE CHAR(8)
	DECLARE @V_PERIOD_DESCR	VARCHAR(60)

	SELECT @V_LANGUAGE_CD = 'ENG'	
	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
      	BEGIN 
	  SELECT @V_RETURN_CD = 1003
      	END

      	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

      	SELECT @V_CHAR_TYPE=CHAR_TYPE_CD
	FROM   CI_CHAR_TYPE
	WHERE  CHAR_TYPE_CD = @P_CHAR_TYPE;
	IF @@ROWCOUNT = 0 
		SELECT @V_RETURN_CD = 1007

   	IF @V_RETURN_CD = 0
	BEGIN
      		SELECT @V_RETURN_CD RETURN_CODE
	  	       , CAST (NULL AS VARCHAR) MESSAGE_TEXT
	  	       ,CI_GL_DIVISION_L.DESCR GL_DIVISION
		       ,CI_CIS_DIVISION_L.DESCR CIS_DIVISION
		       ,FT_QUERY.ACCOUNT_TYPE
		       ,CI_SVC_TYPE_L.DESCR SVC_TYPE
		       ,CI_DST_CODE_L.DESCR DST
		       ,FT_QUERY.GL_ACCT
		       ,FT_QUERY.CURRENCY_CD CURRENCY
		       ,FT_QUERY.STATISTICS_CD
		       ,FT_QUERY.SUM_AMOUNT
		       ,FT_QUERY.SUM_ST_AMOUNT
		       ,CI_CURRENCY_CD.CUR_SYMBOL
		       ,CI_CURRENCY_CD.CUR_POS_FLG
		       ,CI_CURRENCY_CD.DECIMAL_POSITIONS
		       ,CI_CURRENCY_CD.SCALE_POSITIONS
		       ,@V_PERIOD_DESCR V_PERIOD_DESCR
		       ,CI_CURRENCY_CD_L.DESCR CURRENCY_DESC
		       ,CI_CHAR_VAL_L.DESCR ACCOUNT_TYPE_DESC 		 
      		FROM  CI_SVC_TYPE_L
		      ,CI_GL_DIVISION_L
		      ,CI_CIS_DIVISION_L  
		      ,CI_DST_CODE_L	  
		      ,CI_CURRENCY_CD	
		      ,CI_CURRENCY_CD_L
		      ,CI_CHAR_VAL_L
		      ,(SELECT CI_DST_CD_CHAR.CHAR_VAL ACCOUNT_TYPE
		 	       ,CI_DST_CODE_EFF.GL_ACCT
		 	       ,CI_DST_CODE_EFF.STATISTICS_CD
			       ,CI_SA_TYPE.SVC_TYPE_CD
			       ,CI_FT.CURRENCY_CD
			       ,CI_FT_GL.DST_ID
			       ,CI_FT.CIS_DIVISION
			       ,CI_FT.GL_DIVISION
		 	       ,SUM (CI_FT_GL.AMOUNT) SUM_AMOUNT
		 	       ,SUM (CI_FT_GL.STATISTIC_AMOUNT) SUM_ST_AMOUNT
           		FROM   CI_FT	  		 
		 	       ,CI_FT_GL		 
		 	       ,CI_DST_CD_CHAR 
		 	       ,CI_DST_CODE_EFF 
		 	       ,CI_SA			 
		 	       ,CI_SA_TYPE	 		 
            		WHERE  
		       	       CI_FT.ACCOUNTING_DT >= @V_BEGIN_DT AND CI_FT.ACCOUNTING_DT <= @V_END_DT AND
   	  		       CI_FT.FREEZE_SW = 'Y'
			       AND CI_FT.REDUNDANT_SW = 'N' 
   	  		       AND CI_FT.FT_ID = CI_FT_GL.FT_ID
		 /******************************************************************************
		 Select row from CI_DST_CD_CHAR with most recent effective date
		 ******************************************************************************/
      	 		       AND CI_FT_GL.DST_ID = CI_DST_CD_CHAR.DST_ID
  	  	 	       AND CI_DST_CD_CHAR.CHAR_TYPE_CD = @V_CHAR_TYPE
   	  	 	       AND CI_DST_CD_CHAR.EFFDT = (SELECT MAX (T1.EFFDT)
		 		  			   FROM	CI_DST_CD_CHAR T1
							   WHERE	T1.CHAR_TYPE_CD = CI_DST_CD_CHAR.CHAR_TYPE_CD
							   AND		T1.DST_ID = CI_DST_CD_CHAR.DST_ID 
							   AND     T1.EFFDT <= CI_FT.ACCOUNTING_DT)
		 /******************************************************************************
		 Select row from CCI_DST_CODE_EFF with most recent effective date
		 ******************************************************************************/
      	    		       AND CI_FT_GL.DST_ID = CI_DST_CODE_EFF.DST_ID
   	  		       AND CI_DST_CD_CHAR.EFFDT = (SELECT MAX (T1.EFFDT)
		 		  			   FROM	CI_DST_CD_CHAR T1
							   WHERE T1.DST_ID = CI_DST_CODE_EFF.DST_ID
							         AND T1.EFFDT <= CI_FT.ACCOUNTING_DT)
      			       AND CI_FT.SA_ID = CI_SA.SA_ID
   	  		       AND CI_SA.SA_TYPE_CD = CI_SA_TYPE.SA_TYPE_CD
            		GROUP BY	 
			       CI_DST_CD_CHAR.CHAR_VAL 
		 	       ,CI_DST_CODE_EFF.GL_ACCT
		 	       ,CI_DST_CODE_EFF.STATISTICS_CD
			       ,CI_SA_TYPE.SVC_TYPE_CD
			       ,CI_FT.CURRENCY_CD
			       ,CI_FT_GL.DST_ID
			       ,CI_FT.CIS_DIVISION
			       ,CI_FT.GL_DIVISION
			       ,CI_DST_CD_CHAR.CHAR_TYPE_CD) FT_QUERY
      		WHERE FT_QUERY.SVC_TYPE_CD = CI_SVC_TYPE_L.SVC_TYPE_CD
   	  	      AND CI_SVC_TYPE_L.LANGUAGE_CD = @V_LANGUAGE_CD
   	  	      AND FT_QUERY.GL_DIVISION = CI_GL_DIVISION_L.GL_DIVISION
   	  	      AND CI_GL_DIVISION_L.LANGUAGE_CD = @V_LANGUAGE_CD
   	  	      AND FT_QUERY.CIS_DIVISION = CI_CIS_DIVISION_L.CIS_DIVISION
   	  	      AND CI_CIS_DIVISION_L.LANGUAGE_CD = @V_LANGUAGE_CD
   	  	      AND FT_QUERY.DST_ID = CI_DST_CODE_L.DST_ID
   	  	      AND CI_DST_CODE_L.LANGUAGE_CD = @V_LANGUAGE_CD
   	  	      AND FT_QUERY.CURRENCY_CD = CI_CURRENCY_CD.CURRENCY_CD
		      AND CI_CURRENCY_CD.CURRENCY_CD = CI_CURRENCY_CD_L.CURRENCY_CD
		      AND CI_CURRENCY_CD_L.LANGUAGE_CD = @V_LANGUAGE_CD
		      AND CI_CHAR_VAL_L.CHAR_TYPE_CD = @V_CHAR_TYPE
		      AND CI_CHAR_VAL_L.CHAR_VAL = FT_QUERY.ACCOUNT_TYPE
		      AND CI_CHAR_VAL_L.LANGUAGE_CD = @V_LANGUAGE_CD
		;	
	END	 				 		
   	ELSE
	BEGIN
      		SELECT	 @V_RETURN_CD RETURN_CODE
	  		 ,MESSAGE_TEXT
	  		 ,CAST (NULL AS VARCHAR) GL_DIVISION
		   	 ,CAST (NULL AS VARCHAR) CIS_DIVISION
		 	 ,CAST (NULL AS CHAR) ACCOUNT_TYPE
		 	 ,CAST (NULL AS VARCHAR) SVC_TYPE
		 	 ,CAST (NULL AS VARCHAR) DST
		 	 ,CAST (NULL AS VARCHAR) GL_ACCT
		 	 ,CAST (NULL AS CHAR) CURRENCY
		 	 ,CAST (NULL AS CHAR) STATISTICS_CD
		 	 ,CAST (NULL AS NUMERIC) SUM_AMOUNT
		 	 ,CAST (NULL AS NUMERIC) SUM_ST_AMOUNT
		         ,CAST (NULL AS CHAR) CUR_SYMBOL
			 ,CAST (NULL AS CHAR) CUR_POS_FLG
		         ,CAST (NULL AS NUMERIC) DECIMAL_POSITIONS
			 ,CAST (NULL AS NUMERIC) SCALE_POSITIONS
 			 ,CAST (NULL AS VARCHAR) V_PERIOD_DESCR
			 ,CAST (NULL AS VARCHAR) CURRENCY_DESC
			 ,CAST (NULL AS VARCHAR) ACCOUNT_TYPE_DESC
      		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
