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



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

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

   NAME:       CITOSSAS
   PURPOSE:    Supply report extract for Crystal Report - 
               TOS SA Summary 

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

BEGIN

    SELECT     
        A.TOS_ID,  
        B.SA_ID,
        RPTUSER.CISAINFO(B.SA_ID,'ENG') SA_INFO,
        K.ACCT_ID,
        L.ENTITY_NAME,
        J.UA_ACCT_BEGIN_DT,
        J.UA_ACCT_END_DT,
        E.BILL_ID,
        E.BILL_DT,
        ISNULL(D.TOT_AMT,0) BILLED_REVENUE,
        ISNULL(D.CUR_AMT,0) CALCULATED_REVENUE,
        D.CURRENCY_CD,
        F.CUR_SYMBOL,
        F.CUR_POS_FLG,
        F.DECIMAL_POSITIONS,
        F.SCALE_POSITIONS
    FROM 
        CI_TOS A
            INNER JOIN 
                (SELECT 
                    H.UA_ID,
                    MIN(
                    CASE 
                        WHEN H.START_DT > I.BEGIN_DT THEN I.BEGIN_DT
                        WHEN H.START_DT < I.BEGIN_DT THEN H.START_DT
                        ELSE H.START_DT
                    END) AS UA_ACCT_BEGIN_DT,
                    MAX(
                    CASE 
                        WHEN H.START_DT < I.BEGIN_DT THEN I.BEGIN_DT
                        WHEN H.START_DT > I.BEGIN_DT THEN H.START_DT
                        ELSE H.START_DT
                    END) AS UA_ACCT_END_DT
                FROM 
                    CI_CAL_PERIOD I,
                    CI_UA H
                WHERE 
                    (I.BEGIN_DT BETWEEN H.START_DT AND H.END_DT OR I.END_DT BETWEEN H.START_DT AND H.END_DT)
                    AND H.UA_ID = @P_UA_ID
                GROUP BY H.UA_ID ) J
                ON A.UA_ID = J.UA_ID
                AND A.TOS_ID = @P_TOS_ID
            INNER JOIN CI_TOS_SA B
                ON A.TOS_ID = B.TOS_ID
            INNER JOIN CI_SA C
                ON B.SA_ID = C.SA_ID
                AND ((C.SPECIAL_USAGE_FLG <> 'PROP' 
                    AND C.SA_STATUS_FLG <> '70')
                    OR (C.SPECIAL_USAGE_FLG = 'PROP' 
                    AND C.PROP_SA_STAT_FLG NOT IN ('40','50'))
                    )
            LEFT OUTER JOIN CI_FT D
                ON D.SA_ID = C.SA_ID
                AND (D.FT_TYPE_FLG IS NULL OR D.FT_TYPE_FLG NOT IN ('PS','PX'))
            LEFT OUTER JOIN CI_BILL E
                ON E.BILL_ID = D.BILL_ID
            LEFT OUTER JOIN CI_CURRENCY_CD F
                ON F.CURRENCY_CD = D.CURRENCY_CD
            INNER JOIN CI_ACCT_PER K
                ON K.ACCT_ID = C.ACCT_ID
                AND K.MAIN_CUST_SW = 'Y'
            INNER JOIN CI_PER_NAME L
                ON L.PER_ID = K.PER_ID
                AND L.PRIM_NAME_SW = 'Y'

RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

