UFN_DESIGNATIONLEVEL_REVENUESPLITRECORDS

            CREATE function dbo.UFN_DESIGNATIONLEVEL_REVENUESPLITRECORDS
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @STARTDATE datetime,
                @ENDDATE datetime,
                @CAMPAIGNHIERARCHYPATH hierarchyid,
                @APPEALOPTION tinyint
            )
            returns table
            as
            return(
                    select
                        REVENUESPLIT.ID as [REVENUESPLITID],
            case when @APPEALOPTION =1 then
              REVENUESPLITBUSINESSUNIT.AMOUNT
            else
                          REVENUESPLIT.AMOUNT 
            end as [REVENUESPLITAMOUNT],
                        REVENUE.ID as [REVENUEID],
                        DESIGNATION.ID as [DESIGNATIONID],
                        case 
                            when not @APPEALOPTION in (0, 1, 2) then null 
                            else 
                                case @APPEALOPTION 
                                    when 0 then APPEAL.ID
                                    when 1 then REVENUESPLITBUSINESSUNIT.BUSINESSUNITCODEID
                                    when 2 then APPEAL.APPEALREPORT1CODEID
                                end
                        end as [APPEALID],
                        REVENUE.TRANSACTIONTYPECODE,
                        REVENUESPLIT.TYPECODE SPLITTYPECODE,
                        REVENUESPLIT.APPLICATIONCODE
                    from dbo.REVENUESPLIT with (nolock)
                    inner join dbo.REVENUE with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
                    inner join dbo.DESIGNATION with (nolock) on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
          left join dbo.REVENUESPLITBUSINESSUNIT on REVENUESPLITBUSINESSUNIT.REVENUESPLITID= REVENUESPLIT.ID and @APPEALOPTION=1
                    left join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
                    left join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID 
                    left join dbo.APPEAL on APPEAL.ID = REVENUE.APPEALID
                    where    (@CAMPAIGNHIERARCHYPATH is null or (CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH) = 1))
                        and (@STARTDATE is null or REVENUE.DATE >= @STARTDATE)
                        and (@ENDDATE is null or REVENUE.DATE <= @ENDDATE)
                        and (
                                @DESIGNATIONLEVELID is null or 
                                (
                                        DESIGNATION.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
                                    or    DESIGNATION.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID
                                    or    DESIGNATION.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID
                                    or    DESIGNATION.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID
                                    or    DESIGNATION.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
                                )
                            )
                        and (
                                @APPEALOPTION is null or 
                                (
                                    ((@APPEALOPTION = 0 and APPEAL.ID is not null) or @APPEALOPTION <> 0) and 
                  ((@APPEALOPTION = 1 and REVENUESPLITBUSINESSUNIT.BUSINESSUNITCODEID is not null) or @APPEALOPTION <> 1) and 
                                    ((@APPEALOPTION = 2 and APPEAL.APPEALREPORT1CODEID is not null) or @APPEALOPTION <> 2)
                                )
                            )
            )