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


CREATE PROCEDURE RPTUSER.CITOSSAC
@P_UA_ID CHAR(10)
AS

/******************************************************************************
   NAME:       CITOSSAC
   PURPOSE:    Supply report extract for Crystal Report - 
               TOS SA Count for Given UA ID
******************************************************************************/

BEGIN

    BEGIN

        SELECT 
            A.UA_ID,
            A.TOS_ID, 
            A.DESCR,
            COUNT(C.SA_ID) TOTAL_SA,
            COUNT(D.SA_ID) TOTAL_PROP_SA
        FROM   
            CI_TOS A 
                INNER JOIN CI_TOS_SA B
                    ON B.TOS_ID = A.TOS_ID
                    AND A.TOS_STATUS_FLG <> '30' 
                LEFT OUTER JOIN CI_SA C
                    ON C.SA_ID = B.SA_ID
                    AND C.SPECIAL_USAGE_FLG <> 'PROP'
                    AND C.SA_STATUS_FLG <> '70'
                LEFT OUTER JOIN CI_SA D
                    ON D.SA_ID = B.SA_ID
                    AND D.SPECIAL_USAGE_FLG = 'PROP' 
                    AND D.PROP_SA_STAT_FLG <> '40' 
                    AND D.PROP_SA_STAT_FLG <> '50' 
        WHERE
            A.UA_ID = @P_UA_ID
        GROUP  BY 
            A.UA_ID,
            A.TOS_ID,
            A.DESCR
        ORDER  BY 
            A.TOS_ID

	END

RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

