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



CREATE PROCEDURE RPTUSER.CIUABILL
@P_UA_ID CHAR(10), 
@P_TOS_ID CHAR(10),
@P_LANGUAGE_CD CHAR(3)
AS

/******************************************************************************
   NAME:       CIUABILL
   PURPOSE:    Supply report extract for Crystal Report - 
               Umbrella Agreement / Terms of Service Billing History 

******************************************************************************/
BEGIN
			 

    IF @P_TOS_ID IS NULL OR DATALENGTH( LTRIM(RTRIM(@P_TOS_ID)) ) = 0
        BEGIN

            SELECT 
                A.UA_ID,
                B.TOS_ID,
                H.CALENDAR_ID,
                H.FISCAL_YEAR, 
                H.ACCOUNTING_PERIOD,
                H.PERIOD_DESCR,
                E.CURRENCY_CD,
                I.CUR_POS_FLG,
                I.CUR_SYMBOL,
                I.DECIMAL_POSITIONS,
                I.SCALE_POSITIONS,
                SUM(ISNULL(E.TOT_AMT,0)) BILLED_REVENUE,
                SUM(ISNULL(E.CUR_AMT,0)) CALCULATED_REVENUE
            FROM CI_UA A,
                CI_TOS B, 
                CI_TOS_SA C,
                CI_SA D,
                CI_FT E,
                CI_BILL F,
                CI_CAL_PERIOD G,
                CI_CAL_PERIOD_L H,
                CI_CURRENCY_CD I,
                CI_GL_DIVISION J
            WHERE 
                A.UA_ID = @P_UA_ID 
                AND B.UA_ID = A.UA_ID
                AND F.BILL_DT BETWEEN G.BEGIN_DT AND G.END_DT
                AND C.TOS_ID = B.TOS_ID
                AND D.SA_ID = C.SA_ID
                AND D.SA_STATUS_FLG <> '70'
                AND E.SA_ID = D.SA_ID
                AND E.FT_TYPE_FLG NOT IN ('PS','PX')
                AND F.BILL_ID = E.BILL_ID
                AND (
                    G.BEGIN_DT BETWEEN A.START_DT AND A.END_DT 
                    OR G.END_DT BETWEEN A.START_DT AND A.END_DT )
                AND G.CALENDAR_ID = J.CALENDAR_ID
                AND J.GL_DIVISION = E.GL_DIVISION
                AND H.ACCOUNTING_PERIOD = G.ACCOUNTING_PERIOD
                AND H.CALENDAR_ID = G.CALENDAR_ID
                AND H.FISCAL_YEAR = G.FISCAL_YEAR
                AND H.LANGUAGE_CD = @P_LANGUAGE_CD
                AND I.CURRENCY_CD = E.CURRENCY_CD
            GROUP BY
                A.UA_ID,
                B.TOS_ID,
                H.CALENDAR_ID,
                H.FISCAL_YEAR, 
                H.ACCOUNTING_PERIOD,
                H.PERIOD_DESCR,
                E.CURRENCY_CD,
                I.CUR_POS_FLG,
                I.CUR_SYMBOL,
                I.CURRENCY_CD,
                I.DECIMAL_POSITIONS,
                I.SCALE_POSITIONS 
            ORDER BY 
                H.CALENDAR_ID,
                H.FISCAL_YEAR,
                H.ACCOUNTING_PERIOD

        END

    ELSE
        BEGIN

            SELECT A.UA_ID,
                B.TOS_ID,
                H.CALENDAR_ID,
                H.FISCAL_YEAR, 
                H.ACCOUNTING_PERIOD,
                H.PERIOD_DESCR,
                E.CURRENCY_CD,
                I.CUR_POS_FLG,
                I.CUR_SYMBOL,
                I.DECIMAL_POSITIONS,
                I.SCALE_POSITIONS,
                SUM(ISNULL(E.TOT_AMT,0)) BILLED_REVENUE,
                SUM(ISNULL(E.CUR_AMT,0)) CALCULATED_REVENUE
              FROM CI_UA A,
                CI_TOS B, 
                CI_TOS_SA C,
                CI_SA D,
                CI_FT E,
                CI_BILL F,
                CI_CAL_PERIOD G,
                CI_CAL_PERIOD_L H,
                CI_CURRENCY_CD I,
                CI_GL_DIVISION J
             WHERE 
                A.UA_ID = @P_UA_ID
                AND B.TOS_ID = @P_TOS_ID
                AND B.UA_ID = A.UA_ID
                AND F.BILL_DT BETWEEN G.BEGIN_DT AND G.END_DT
                AND C.TOS_ID = B.TOS_ID
                AND D.SA_ID = C.SA_ID
                AND D.SA_STATUS_FLG <> '70'
                AND E.SA_ID = D.SA_ID
                AND E.FT_TYPE_FLG NOT IN ('PS','PX')
                AND F.BILL_ID = E.BILL_ID
                AND (
                    G.BEGIN_DT BETWEEN A.START_DT AND A.END_DT 
                    OR G.END_DT BETWEEN A.START_DT AND A.END_DT )
                AND G.CALENDAR_ID = J.CALENDAR_ID
                AND J.GL_DIVISION = E.GL_DIVISION
                AND H.ACCOUNTING_PERIOD = G.ACCOUNTING_PERIOD
                AND H.CALENDAR_ID = G.CALENDAR_ID
                AND H.FISCAL_YEAR = G.FISCAL_YEAR
                AND H.LANGUAGE_CD = @P_LANGUAGE_CD
                AND I.CURRENCY_CD = E.CURRENCY_CD
            GROUP BY
                A.UA_ID,
                B.TOS_ID,
                H.CALENDAR_ID,
                H.FISCAL_YEAR, 
                H.ACCOUNTING_PERIOD,
                H.PERIOD_DESCR,
                E.CURRENCY_CD,
                I.CUR_POS_FLG,
                I.CUR_SYMBOL,
                I.CURRENCY_CD,
                I.DECIMAL_POSITIONS,
                I.SCALE_POSITIONS 
            ORDER BY 
                H.CALENDAR_ID,
                H.FISCAL_YEAR,
                H.ACCOUNTING_PERIOD

        END

RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

