USP_KPI_DESIGNATIONLEVEL_REVENUECOUNT_INTERNAL

            CREATE procedure dbo.USP_KPI_DESIGNATIONLEVEL_REVENUECOUNT_INTERNAL

            @ASOFDATE datetime,
            @TOTAL int = 0 output,
            @DESIGNATIONLEVELID uniqueidentifier,
            @APPEALID uniqueidentifier = null,
            @BUSINESSUNITCODEID uniqueidentifier = null,
            @APPEALREPORTCODE1ID uniqueidentifier = null,
            @STARTDATE datetime = null

            as    

            set nocount on;

            with DESIGNATIONS_CTE as (
                select ID 
                from dbo.DESIGNATION D
                where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
                      (D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID) or 
                      (D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID) or 
                      (D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID) or 
                      (D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID) 
                )

            select @TOTAL = count(distinct R.ID)
                from dbo.REVENUESPLIT RDS
                inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
                inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                left join dbo.APPEAL A on R.APPEALID = A.ID
                where
                    (R.DATE >= @STARTDATE or @STARTDATE is null) and
                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                    (A.ID = @APPEALID or @APPEALID is null) and
                    (A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
                    (A.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                    ((R.TRANSACTIONTYPECODE in (1,3)
                    or
                    (R.TRANSACTIONTYPECODE = 0 and RDS.APPLICATIONCODE in (0, 1, 3))));