USP_KPI_DESIGNATIONLEVEL_REVENUECOUNT

                CREATE procedure dbo.USP_KPI_DESIGNATIONLEVEL_REVENUECOUNT

                @VALUE int output,
                @DESIGNATIONLEVELID uniqueidentifier,
                @APPEALID uniqueidentifier = null,
                @BUSINESSUNITCODEID uniqueidentifier = null,
                @APPEALREPORTCODE1ID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ASOFDATE datetime,
                @ORGPOSITIONSSELECTIONID uniqueidentifier = null

                as

                set nocount on;

                if @ORGPOSITIONSSELECTIONID is null
                begin
                    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 @VALUE = 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
            left join dbo.REVENUESPLITBUSINESSUNIT RSB on RSB.REVENUESPLITID = RDS.ID and RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID                                        
                        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
                            (RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                            ((R.TRANSACTIONTYPECODE in (1,3,6,8)
                            or
                            (R.TRANSACTIONTYPECODE = 0 and RDS.APPLICATIONCODE in (0, 1, 3, 6))));

                end
                else
                begin
                    declare @IDS as table(ID uniqueidentifier);
                    insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, null;

                    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 @VALUE = 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
                        inner join dbo.REVENUESOLICITOR RSOL on RDS.ID = RSOL.REVENUESPLITID
                        inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH 
                            on OPH.CONSTITUENTID = RSOL.CONSTITUENTID and R.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, R.DATE)
                        inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
                        left join dbo.APPEAL A on R.APPEALID = A.ID
            left join dbo.REVENUESPLITBUSINESSUNIT RSB on RSB.REVENUESPLITID = RDS.ID and RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID                            
                        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
                            (RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                            ((R.TRANSACTIONTYPECODE in (1,3,6,8)
                            or
                            (R.TRANSACTIONTYPECODE = 0 and RDS.APPLICATIONCODE in (0, 1, 3, 6))));
                end