UFN_DESIGNATIONLEVEL_REVENUESPLITRECORDSINCURRENCY

Returns the revenue split records associated with a designation level (for fundraising reporting).
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@DESIGNATIONLEVELID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CAMPAIGNHIERARCHYPATH	hierarchyid	IN
@APPEALOPTION	tinyint	IN
@CURRENCYID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_DESIGNATIONLEVEL_REVENUESPLITRECORDSINCURRENCY
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @STARTDATE datetime,
                @ENDDATE datetime,
                @CAMPAIGNHIERARCHYPATH hierarchyid,
                @APPEALOPTION tinyint,
                @CURRENCYID uniqueidentifier
            )
            returns table
            as
            return(
                select
                    REVENUESPLIT.ID as [REVENUESPLITID],
                    case when @APPEALOPTION =1 
                        then dbo.UFN_REVENUESPLITBUSINESSUNIT_GETAMOUNTINCURRENCY(REVENUESPLITBUSINESSUNIT.ID,@CURRENCYID)
                        else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID,@CURRENCYID)
                    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)
                        )
                    )
            )