USP_KPI_DESIGNATION_REVENUECOUNT

                CREATE procedure dbo.USP_KPI_DESIGNATION_REVENUECOUNT

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

                as

                set nocount on;

                declare @DL1ID uniqueidentifier;
                declare @DL2ID uniqueidentifier;
                declare @DL3ID uniqueidentifier;
                declare @DL4ID uniqueidentifier;
                declare @DL5ID uniqueidentifier;

                select @DL1ID = DESIGNATIONLEVEL1ID,
                        @DL2ID = DESIGNATIONLEVEL2ID,
                        @DL3ID = DESIGNATIONLEVEL3ID,
                        @DL4ID = DESIGNATIONLEVEL4ID,
                        @DL5ID = DESIGNATIONLEVEL5ID
                from dbo.DESIGNATION
                where ID = @DESIGNATIONID;

                if @ORGPOSITIONSSELECTIONID is null
                begin
                    with DESIGNATIONS_CTE as (
                        select ID 
                        from dbo.DESIGNATION D
                        where D.DESIGNATIONLEVEL1ID = @DL1ID and
                              (D.DESIGNATIONLEVEL2ID = @DL2ID or @DL2ID is null) and 
                              (D.DESIGNATIONLEVEL3ID = @DL3ID or @DL3ID is null) and 
                              (D.DESIGNATIONLEVEL4ID = @DL4ID or @DL4ID is null) and 
                              (D.DESIGNATIONLEVEL5ID = @DL5ID or @DL5ID is null)
                        )

                    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 = @DL1ID and
                              (D.DESIGNATIONLEVEL2ID = @DL2ID or @DL2ID is null) and 
                              (D.DESIGNATIONLEVEL3ID = @DL3ID or @DL3ID is null) and 
                              (D.DESIGNATIONLEVEL4ID = @DL4ID or @DL4ID is null) and 
                              (D.DESIGNATIONLEVEL5ID = @DL5ID or @DL5ID is null)
                        )

                    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