UFN_REVENUESPLIT_CONSTITUENTREVENUEHISTORYDETAIL

Returns the appropriate value for the Detail column of the Constituent Revenue History List for a given revenue split row.
Return

Return Type
nvarchar(1024)
Parameters

Parameter	Parameter Type	Mode
@REVENUESPLITID	uniqueidentifier	IN
@APPLICATIONCODE	tinyint	IN
@TYPECODE	tinyint	IN
@DESIGNATIONID	uniqueidentifier	IN
Definition

Copy


CREATE function dbo.UFN_REVENUESPLIT_CONSTITUENTREVENUEHISTORYDETAIL(
    @REVENUESPLITID uniqueidentifier,
    @APPLICATIONCODE tinyint,
    @TYPECODE tinyint,
    @DESIGNATIONID uniqueidentifier
)
returns nvarchar(1024)
with execute as caller
as begin
    declare @DETAIL nvarchar(1024);

    if @TYPECODE = 1
        select @DETAIL = EVENT.NAME
        from dbo.EVENTREGISTRANTPAYMENT
        inner join dbo.REGISTRANT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
        inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
        where EVENTREGISTRANTPAYMENT.PAYMENTID = @REVENUESPLITID;
    else 
    begin
        if @TYPECODE = 2
            select @DETAIL = MEMBERSHIPPROGRAM.NAME + ' - ' + MEMBERSHIPLEVEL.NAME
            from dbo.MEMBERSHIPTRANSACTION
            inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
            inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
            where MEMBERSHIPTRANSACTION.REVENUESPLITID = @REVENUESPLITID;
        else 
        begin
            if @TYPECODE = 9
            begin
                if @APPLICATIONCODE = 0
                begin
                    select top 1 @DETAIL =
                     dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) +
                     case when SPONSORSHIP.CONSTITUENTID <> REVENUE.CONSTITUENTID then (select ' (Gift to ' + NAME + ')' from dbo.CONSTITUENT where ID = SPONSORSHIP.CONSTITUENTID) else '' end
                    from dbo.REVENUESPLIT
                    inner join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.ID 
                    inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                    where REVENUESPLIT.ID = @REVENUESPLITID
                    and SPONSORSHIP.ISMOSTRECENTFORCOMMITMENT = 1;

                    if @DETAIL is null or @DETAIL = ''
                        set @DETAIL = dbo.UFN_DESIGNATION_GETNAME(@DESIGNATIONID);
                end
                else
                    select @DETAIL = 
                     dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) +
                     case when SPONSORSHIPPAYMENT.GIFTFINANCIALSPONSORID is not null then ' (Gift to ' + dbo.UFN_CONSTITUENT_BUILDNAME(SPONSORSHIP.CONSTITUENTID) + ')' else '' end
                    from dbo.REVENUESPLIT
                    inner join dbo.SPONSORSHIPPAYMENT on SPONSORSHIPPAYMENT.ID = REVENUESPLIT.ID
                    inner join dbo.SPONSORSHIP on SPONSORSHIP.ID = SPONSORSHIPPAYMENT.SPONSORSHIPID
                    where REVENUESPLIT.ID = @REVENUESPLITID;
            end
            else
                set @DETAIL = dbo.UFN_DESIGNATION_GETNAME(@DESIGNATIONID)
        end
    end

    return @DETAIL;
end