SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CIUBLREV' AND type = 'P')
   DROP PROCEDURE RPTUSER.CIUBLREV
GO


CREATE PROCEDURE RPTUSER.CIUBLREV
@P_USER_ID CHAR(8),
@P_ACCT_PERIOD CHAR(11),
@P_CHAR_TYPE CHAR(8),
@P_REV_ACCTY_CHAR CHAR(16)

AS

/******************************************************************************
   NAME:       CIUBLREV
   PURPOSE:    Calculates the estimated unbilled revenue portion for each month.

******************************************************************************/

DECLARE @V_FIELD_NAME CHAR(20)
DECLARE @V_LANGUAGE_CD CHAR(3)
DECLARE @V_BEGIN_DT	   DATETIME
DECLARE @V_END_DT	   DATETIME
DECLARE @V_RETURN_CD   DECIMAL(5)
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_CHAR_VAL	   CHAR(16)
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 (SELECT @@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 (SELECT @@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 (SELECT @@ERROR) <> 0
                SELECT @V_RETURN_CD = 1006
        END

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

        IF (SELECT @@ROWCOUNT) = 0
            BEGIN
                SELECT @V_LANGUAGE_CD = 'ENG'
                SELECT @V_RETURN_CD = 1003
            END
    END


    BEGIN
        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 (SELECT @@ROWCOUNT) = 0
            BEGIN
                SELECT @V_BEGIN_DT = NULL
                SELECT @V_END_DT = NULL
                SELECT @V_RETURN_CD = 1001
            END
    END


    BEGIN
        SELECT @V_CHAR_TYPE = CHAR_TYPE_CD
        FROM CI_CHAR_TYPE
        WHERE CHAR_TYPE_CD = @P_CHAR_TYPE
        IF (SELECT @@ROWCOUNT) = 0
 	  SELECT @V_RETURN_CD = 1007
    END


    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 (SELECT @@ROWCOUNT) = 0
                SELECT @V_RETURN_CD = 1008
        END

    IF @V_RETURN_CD = 0
        SELECT  
            @V_RETURN_CD RETURN_CODE,
            CAST (NULL AS VARCHAR) MESSAGE_TEXT,
            @V_PERIOD_DESCR V_PERIOD_DESCR, 
            REV_PER_DAY.UBILL_REV UBILL_REV,
            CI_SVC_TYPE_L.DESCR SVC_TYPE,
            REV_PER_DAY.SVC_TYPE_CD,
            CI_CIS_DIVISION_L.DESCR CIS_DIVISION,
            REV_PER_DAY.CIS_DIVISION CIS_DIVISION_CD,
            CI_GL_DIVISION_L.DESCR GL_DIVISION,
            REV_PER_DAY.GL_DIVISION GL_DIVISION_CD,
            REV_PER_DAY.CURRENCY_CD,
            REV_PER_DAY.DST_ID,
            CI_DST_CODE_L.DESCR GL_ACCT_NAME,
            REV_PER_DAY.GL_ACCT,
            CI_CURRENCY_CD.CUR_SYMBOL,
            CI_CURRENCY_CD.DECIMAL_POSITIONS,
            CI_CURRENCY_CD.SCALE_POSITIONS,
            CI_CURRENCY_CD.CUR_POS_FLG,
            CI_CURRENCY_CD_L.DESCR CURR_DESC
        FROM
            CI_SVC_TYPE_L,
            CI_CIS_DIVISION_L,
            CI_GL_DIVISION_L,
            CI_DST_CODE_L,
            CI_CURRENCY_CD,
            CI_CURRENCY_CD_L,
            (SELECT  
                A.CURRENCY_CD, 
                A.CIS_DIVISION, 
                A.GL_DIVISION, 
                A.DST_ID, 
                B.SVC_TYPE_CD, 
                A.GL_ACCT,
                SUM(A.REV_PER_DAY * CAST((@V_END_DT - A.END_DT) AS DECIMAL) ) UBILL_REV 
            FROM (
                SELECT   
                    BS.SA_ID,
                    MAX(FT.CURRENCY_CD) CURRENCY_CD, 
                    MAX(FT.CIS_DIVISION) CIS_DIVISION,
                    MAX(FT.GL_DIVISION) GL_DIVISION,
                    MAX(FTGL.DST_ID) DST_ID,
                    MAX(DCED.GL_ACCT) GL_ACCT,
                    MAX(BS.END_DT) END_DT,
                    (SUM 
                        ((FTGL.AMOUNT * (-1) ) / 
                        CAST(
                            CASE (BS.END_DT - BS.START_DT)
                                WHEN 0 THEN 1
                                ELSE (BS.END_DT - BS.START_DT)
                            END
                        AS DECIMAL)
                        )
                    ) REV_PER_DAY
                FROM
                    CI_BSEG BS,
                    CI_FT FT,
                    CI_FT_GL FTGL,
                    CI_DST_CODE_EFF DCED
                WHERE
                    ((BS.START_DT <= @V_BEGIN_DT AND BS.END_DT > @V_BEGIN_DT)
                    OR
                    (BS.START_DT >= @V_BEGIN_DT AND BS.START_DT < @V_END_DT))
                    AND BS.END_DT < @V_END_DT 
                    AND FT.SIBLING_ID = BS.BSEG_ID 
                    AND FT.FT_TYPE_FLG IN ('BS', 'BX') 
                    AND FT.FREEZE_SW = 'Y'
                    AND FT.REDUNDANT_SW = 'N' 
                    AND FTGL.FT_ID = FT.FT_ID 
                    AND DCED.DST_ID = FTGL.DST_ID
                    AND DCED.EFFDT = (
                        SELECT  MAX (T.EFFDT)
                        FROM CI_DST_CODE_EFF T
                        WHERE
                            T.DST_ID = DCED.DST_ID
                            AND     T.EFFDT <= FT.ACCOUNTING_DT)
                    AND EXISTS (
                        SELECT 'X' 
                        FROM CI_DST_CD_CHAR DCCH 
                        WHERE DCCH.DST_ID = FTGL.DST_ID
                            AND DCCH.CHAR_TYPE_CD = @V_CHAR_TYPE
                            AND DCCH.CHAR_VAL = @V_CHAR_VAL
                            AND DCCH.EFFDT = (
                            SELECT MAX (T.EFFDT)
                            FROM CI_DST_CD_CHAR T
                            WHERE
                                T.EFFDT <= FT.ACCOUNTING_DT
                                AND T.DST_ID = DCCH.DST_ID
                                AND T.CHAR_TYPE_CD = DCCH.CHAR_TYPE_CD))
                GROUP BY BS.SA_ID, 
                DCED.DST_ID,
                DCED.EFFDT) A, 
                CI_SA_TYPE B, 
                CI_SA C  
            WHERE
                C.SA_ID = A.SA_ID 
                AND B.CIS_DIVISION = C.CIS_DIVISION 
                AND B.SA_TYPE_CD = C.SA_TYPE_CD 
            GROUP BY A.CURRENCY_CD, 
                A.CIS_DIVISION, 
                A.GL_DIVISION, 
                A.DST_ID, 
                B.SVC_TYPE_CD, 
                A.GL_ACCT
            ) REV_PER_DAY 
        WHERE
            REV_PER_DAY.SVC_TYPE_CD = CI_SVC_TYPE_L.SVC_TYPE_CD
            AND CI_SVC_TYPE_L.LANGUAGE_CD = @V_LANGUAGE_CD
            AND REV_PER_DAY.CIS_DIVISION = CI_CIS_DIVISION_L.CIS_DIVISION
            AND CI_CIS_DIVISION_L.LANGUAGE_CD = @V_LANGUAGE_CD
            AND REV_PER_DAY.GL_DIVISION = CI_GL_DIVISION_L.GL_DIVISION
            AND CI_GL_DIVISION_L.LANGUAGE_CD = @V_LANGUAGE_CD
            AND REV_PER_DAY.DST_ID = CI_DST_CODE_L.DST_ID
            AND CI_DST_CODE_L.LANGUAGE_CD = @V_LANGUAGE_CD
            AND REV_PER_DAY.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
    ELSE
        SELECT
            MESSAGE_NBR RETURN_CODE,
            MESSAGE_TEXT, 
            CAST (NULL AS VARCHAR) V_PERIOD_DESCR,
            CAST (NULL AS DECIMAL) UBILL_REV,
            CAST (NULL AS VARCHAR) SVC_TYPE,
            CAST (NULL AS CHAR) SVC_TYPE_CD,
            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) CUR_SYMBOL,
            CAST (NULL AS DECIMAL) DECIMAL_POSITIONS,
            CAST (NULL AS DECIMAL) SCALE_POSITIONS,
            CAST (NULL AS CHAR) CUR_POS_FLG,
            CAST (NULL AS VARCHAR) CURR_DESC
        FROM 
            CI_MSG_L
        WHERE   
            MESSAGE_CAT_NBR = 30
            AND MESSAGE_NBR = @V_RETURN_CD
            AND LANGUAGE_CD = @V_LANGUAGE_CD;


			 
RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

