USP_KPI_REVENUE_APPEALCOOUNTTOTAL

        CREATE procedure dbo.USP_KPI_REVENUE_APPEALCOOUNTTOTAL 

                @VALUE int output,
                @DESIGNATIONID uniqueidentifier = null,
                @APPEALID uniqueidentifier,
                @ASOFDATE datetime,
        @SELECTIONID uniqueidentifier = null

        as

                set nocount on;

                declare @RECEIVED money;
                declare @PLEDGEBALANCE money;

        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 the designation id is null, this procedure returns the revenue records that apply to an appeal

        --else it joins the revenue split table with a table(DESIGNATIONS_CTE) that contains the designation ids

        --for the input designation and its children designations


        if @SELECTIONID is null
          begin
            if @DESIGNATIONID is null
              begin
                select @VALUE = count(distinct R.ID)
                            from dbo.REVENUESPLIT RDS
                            inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                  where
                                (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                                (R.APPEALID = @APPEALID) and
                                ((R.TRANSACTIONTYPECODE in (1,3))
                                or
                                (R.TRANSACTIONTYPECODE = 0 and RDS.APPLICATIONCODE in (0, 3, 6))
                                or
                                (R.TRANSACTIONTYPECODE = 0 and RDS.APPLICATIONCODE = 1 and RDS.TYPECODE = 0));
              end
            else
              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 dbo.REVENUE R on R.ID = RDS.REVENUEID
                  inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
                  where
                                (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                                (R.APPEALID = @APPEALID) and
                                ((R.TRANSACTIONTYPECODE in (1,3))
                                or
                                (R.TRANSACTIONTYPECODE = 0 and RDS.APPLICATIONCODE in (0, 1, 3, 6)));
              end
                    end

          else
            begin
              if @DESIGNATIONID is null
                begin
                  select @VALUE = count(distinct R.ID)
                              from dbo.REVENUESPLIT RDS
                      inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                                inner join UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_F4C1477102BF45CAB7A0E05AF9D6B83D on IDSET_F4C1477102BF45CAB7A0E05AF9D6B83D.ID = RDS.REVENUEID
                                where
                                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                                    (R.APPEALID = @APPEALID) and
                                        ((R.TRANSACTIONTYPECODE in (1,3))
                                        or
                                        (R.TRANSACTIONTYPECODE = 0 and RDS.APPLICATIONCODE in (0, 3, 6))
                                        or
                                        (R.TRANSACTIONTYPECODE = 0 and RDS.APPLICATIONCODE = 1 and RDS.TYPECODE = 0));
                end
              else
                begin
                  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 UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_F4C1477102BF45CAB7A0E05AF9D6B83D on IDSET_F4C1477102BF45CAB7A0E05AF9D6B83D.ID = RDS.REVENUEID
                              where
                                  (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                                  (R.APPEALID = @APPEALID) and
                                    ((R.TRANSACTIONTYPECODE in (1,3))
                                    or
                                    (R.TRANSACTIONTYPECODE = 0 and RDS.APPLICATIONCODE in (0, 1, 3, 6)));
                end
            end