UFN_PAYMENTSPLITSAPPLIEDTOCOMMITMENT_BUILDSITELIST

Returns a list of sites for a pledge or recurring gift commitment based on the revenue splits

Return

Return Type
nvarchar(1024)

Parameters

Parameter	Parameter Type	Mode
@PAYMENTID	uniqueidentifier	IN
@COMMITMENTID	uniqueidentifier	IN
@COMMITMENTTYPE	tinyint	IN

Definition

Copy



CREATE function dbo.UFN_PAYMENTSPLITSAPPLIEDTOCOMMITMENT_BUILDSITELIST(
    @PAYMENTID uniqueidentifier,
    @COMMITMENTID uniqueidentifier,
    @COMMITMENTTYPE tinyint        -- 0=Pledge, 1=Recurring gift

)
returns nvarchar(1024)
with execute as caller
as begin
    declare @SITES nvarchar(1024);

    if @COMMITMENTTYPE = 0
        set @SITES = (
            select dbo.UDA_BUILDLIST(NAME)
            from dbo.SITE
            where ID in(
                select REVENUESPLITSITE.SITEID
                from dbo.REVENUE
                inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID=REVENUE.ID
                inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
                cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVENUESPLITSITE
                where REVENUE.ID = @PAYMENTID
                and INSTALLMENTSPLITPAYMENT.PLEDGEID = @COMMITMENTID
            )
        );
    else
        set @SITES = (
            select dbo.UDA_BUILDLIST(NAME)
            from dbo.SITE
            where ID in(
                select REVENUESPLITSITE.SITEID
                from dbo.REVENUE
                inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID=REVENUE.ID
                inner join dbo.RECURRINGGIFTACTIVITY on RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = REVENUESPLIT.ID
                cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVENUESPLITSITE
                where REVENUE.ID = @PAYMENTID
                and RECURRINGGIFTACTIVITY.SOURCEREVENUEID = @COMMITMENTID
            )
        );

    return @SITES;            
end