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

CREATE PROCEDURE RPTUSER.CIBILREV
@P_USER_ID CHAR(8),
@P_ACCT_PERIOD CHAR(24),
@P_CHAR_TYPE CHAR(8),
@P_REV_ACCTY_CHAR CHAR(16)
AS

/*********************************************************************************
   NAME:       CIBILREV
   PURPOSE:    Calculates Billed Revenues by Rate
*********************************************************************************/

   DECLARE   @V_LANGUAGE_CD   CHAR(3)
   DECLARE   @V_BEGIN_DT      DATETIME
   DECLARE   @V_END_DT        DATETIME
   DECLARE   @V_FISCAL_YEAR   NUMERIC(4)
   DECLARE   @V_ACCT_PERIOD   NUMERIC(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_CHAR_VAL      CHAR(16)
   DECLARE   @V_PERIOD_DESCR  CHAR(60)
   DECLARE   @V_RETURN_CD     INT

   SET @V_RETURN_CD = 0

BEGIN


/******************************************************************************
   Checking for nulls in parameters and setting default values for select 
   statements
******************************************************************************/

   IF @P_ACCT_PERIOD IS NULL OR @P_ACCT_PERIOD = ' '
      SET @V_RETURN_CD = 1004
   ELSE
      BEGIN  
 	 SET @V_POUND_1 = CHARINDEX('|', @P_ACCT_PERIOD, 1)
	 SET @V_POUND_2 = CHARINDEX('|', @P_ACCT_PERIOD, @V_POUND_1 + 1)
         
         SET @V_CALENDAR_ID = RTRIM(LTRIM(SUBSTRING(@P_ACCT_PERIOD, 1, @V_POUND_1 - 1)))
         IF @V_CALENDAR_ID = NULL
            SET @V_RETURN_CD = 1002 
 
         SET @V_FISCAL_YEAR = CAST(LTRIM(RTRIM(SUBSTRING(@P_ACCT_PERIOD, @V_POUND_1 + 1, @V_POUND_2 - @V_POUND_1 - 1))) AS NUMERIC)
         IF @V_FISCAL_YEAR = NULL
            SET @V_RETURN_CD = 1005
         
         SET @V_ACCT_PERIOD = CAST(LTRIM(RTRIM(SUBSTRING(@P_ACCT_PERIOD, @V_POUND_2 + 1, DATALENGTH(@P_ACCT_PERIOD)))) AS NUMERIC)
         IF @V_ACCT_PERIOD = NULL
            SET @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
         SET @V_LANGUAGE_CD = 'ENG'
         SET @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
         SET @V_BEGIN_DT = NULL
         SET @V_END_DT = NULL
         SET @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
      SET @V_RETURN_CD = 1007


   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_CHAR_VAL = CHAR_VAL
  	 FROM	CI_CHAR_VAL
	 WHERE  CHAR_TYPE_CD = @V_CHAR_TYPE
         	AND CHAR_VAL = @P_REV_ACCTY_CHAR
      
         IF @@ROWCOUNT = 0
            SET @V_RETURN_CD = 1008
      END
   
   IF @V_RETURN_CD = 0
      BEGIN
         SELECT  @V_RETURN_CD RETURN_CODE
	  	 ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
		 ,SUBQUERY.AMOUNT
		 ,SUBQUERY.STATISTIC_AMOUNT
		 ,CI_CIS_DIVISION_L.DESCR CIS_DIVISION
		 ,SUBQUERY.CIS_DIVISION CIS_DIVISION_CD
		 ,CI_GL_DIVISION_L.DESCR GL_DIVISION
		 ,SUBQUERY.GL_DIVISION GL_DIVISION_CD
		 ,SUBQUERY.CURRENCY_CD
		 ,SUBQUERY.DST_ID
		 ,CI_DST_CODE_L.DESCR GL_ACCT_NAME
		 ,SUBQUERY.GL_ACCT
		 ,SUBQUERY.RS_CD
		 ,SUBQUERY.STATISTICS_CD
		 ,CI_CURRENCY_CD.CUR_SYMBOL
		 ,CI_RS_L.DESCR RS
		 ,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
         FROM	 CI_CIS_DIVISION_L
		 ,CI_GL_DIVISION_L
		 ,CI_DST_CODE_L
		 ,CI_CURRENCY_CD
		 ,CI_RS_L
		 ,CI_CURRENCY_CD_L
	  	 ,(SELECT CI_FT.CURRENCY_CD
		 	  ,CI_FT.CIS_DIVISION
		 	  ,CI_FT.GL_DIVISION
		 	  ,CI_FT_GL.DST_ID
			  ,CI_DST_CODE_EFF.GL_ACCT
			  ,CI_DST_CODE_EFF.STATISTICS_CD
			  ,CI_SA_RS_HIST.RS_CD
	  	 	  ,(-1) * SUM (CI_FT_GL.AMOUNT) AMOUNT
	  	 	  ,SUM (CI_FT_GL.STATISTIC_AMOUNT) STATISTIC_AMOUNT
	  	   FROM   CI_FT
		   	  ,CI_FT_GL
		   	  ,CI_DST_CODE_EFF
			  ,CI_DST_CD_CHAR
			  ,CI_SA_RS_HIST
			  ,CI_RS
	  	   WHERE  CI_FT.FREEZE_SW = 'Y'
         	          AND CI_FT.REDUNDANT_SW = 'N' 
		          AND CI_FT.ACCOUNTING_DT BETWEEN @V_BEGIN_DT AND @V_END_DT
	  	          AND CI_FT.FT_ID = CI_FT_GL.FT_ID
		          AND CI_FT_GL.DST_ID = CI_DST_CODE_EFF.DST_ID
		          AND CI_DST_CODE_EFF.EFFDT = (SELECT MAX (CI_DST_CODE_EFF.EFFDT)
		   				       FROM   CI_DST_CODE_EFF 
						       WHERE  CI_DST_CODE_EFF.EFFDT <= CI_FT.ACCOUNTING_DT
                                                              AND CI_DST_CODE_EFF.DST_ID = CI_FT_GL.DST_ID)
                          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.CHAR_VAL = @V_CHAR_VAL
		          AND CI_DST_CD_CHAR.EFFDT = (SELECT MAX (CI_DST_CD_CHAR.EFFDT)
		   				      FROM   CI_DST_CD_CHAR 
						      WHERE  CI_DST_CD_CHAR.EFFDT <= CI_FT.ACCOUNTING_DT
							     AND CI_DST_CD_CHAR.DST_ID = CI_FT_GL.DST_ID
							     AND CI_DST_CD_CHAR.CHAR_TYPE_CD = @V_CHAR_TYPE)
		          AND CI_SA_RS_HIST.SA_ID = CI_FT.SA_ID
		          AND CI_SA_RS_HIST.EFFDT = (SELECT MAX (CI_SA_RS_HIST.EFFDT)
		   				     FROM CI_SA_RS_HIST 
						     WHERE CI_SA_RS_HIST.EFFDT <= CI_FT.ACCOUNTING_DT
    						           AND CI_SA_RS_HIST.SA_ID = CI_FT.SA_ID)
		          AND CI_RS.RS_CD = CI_SA_RS_HIST.RS_CD
	  	    GROUP BY CI_FT.CURRENCY_CD
		             ,CI_FT.CIS_DIVISION
			     ,CI_FT.GL_DIVISION
     		             ,CI_FT_GL.DST_ID
			     ,CI_DST_CODE_EFF.GL_ACCT
			     ,CI_DST_CODE_EFF.STATISTICS_CD
                             ,CI_SA_RS_HIST.RS_CD) SUBQUERY

         WHERE SUBQUERY.AMOUNT <> 0   --- Do not show lines with the net total = zero
  	       /*------------------------------------------------*/
	       AND SUBQUERY.CIS_DIVISION = CI_CIS_DIVISION_L.CIS_DIVISION
	       AND CI_CIS_DIVISION_L.LANGUAGE_CD = @V_LANGUAGE_CD
	       /*------------------------------------------------*/
	       AND SUBQUERY.GL_DIVISION = CI_GL_DIVISION_L.GL_DIVISION
	       AND CI_GL_DIVISION_L.LANGUAGE_CD = @V_LANGUAGE_CD
       	       /*------------------------------------------------*/
	       AND SUBQUERY.DST_ID = CI_DST_CODE_L.DST_ID
	       AND CI_DST_CODE_L.LANGUAGE_CD = @V_LANGUAGE_CD
	       /*------------------------------------------------*/
               AND SUBQUERY.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 SUBQUERY.RS_CD = CI_RS_L.RS_CD
   	       AND CI_RS_L.LANGUAGE_CD = @V_LANGUAGE_CD
      END
  
   ELSE  
      BEGIN
         SELECT  MESSAGE_NBR RETURN_CODE
	  	 ,MESSAGE_TEXT
		 ,CAST (NULL AS NUMERIC)AMOUNT
		 ,CAST (NULL AS NUMERIC) STATISTIC_AMOUNT
		 ,CAST (NULL AS VARCHAR) CIS_DIVISION
		 ,CAST (NULL AS CHAR) CIS_DIVISION_CD
		 ,CAST (NULL AS VARCHAR) GL_DIVISION
		 ,CAST (NULL AS CHAR) GL_DIVISION_CD
		 ,CAST (NULL AS CHAR) CURRENCY_CD
		 ,CAST (NULL AS CHAR) DST_ID
		 ,CAST (NULL AS VARCHAR) GL_ACCT_NAME
		 ,CAST (NULL AS VARCHAR) GL_ACCT
		 ,CAST (NULL AS CHAR) RS_CD
		 ,CAST (NULL AS CHAR) STATISTICS_CD
		 ,CAST (NULL AS CHAR) CUR_SYMBOL
		 ,CAST (NULL AS VARCHAR) RS
		 ,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
         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

