USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPPAYMENT

    CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPPAYMENT (
    @ID uniqueidentifier, --sponsorship id

    @DATALOADED bit = 0 output,
    @NAME nvarchar(700) = null output,
    @COST money = null output,
    @FREQUENCY nvarchar(100) = null output,
    @STARTDATE date = null output,
    @ENDDATE date = null output,
    --@PMTMETHOD nvarchar(20)=null output,

    --@PMTTYPE varchar(20)=null output,

    --@CCNUM varchar(30)=null output,

    --@EXPDATE dbo.UDT_FUZZYDATE = null output,

    @REVENUEID uniqueidentifier = null output,
    @DESIGNATION nvarchar(100) = null output,
    @PASTDUE money = null output,
    @NEXTTRANSACTION date = null output,
    @SHOWPASTDUE bit = null output,
    @REVENUELOOKUPID nvarchar(100) = null output,
    @TOTALCOMMITMENT money = null output,
    @TOTALSPONSORSHIP money = null output,
    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
    @BASECURRENCYID uniqueidentifier = null output,
    @BASEAMOUNT money = null output,
    @ISPENDING bit = null output,
    @PENDINGBATCHNUMBER nvarchar(100) = null output
    )
    as
    set nocount on;
    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.

    declare @REVENUESPLITID uniqueidentifier = null
    declare @DESIGNATIONID uniqueidentifier = null
    declare @CONSTITUENTID uniqueidentifier = null

    --Get Revenuesplitid from sponsorship table.

    select @DATALOADED = 1,
    @REVENUESPLITID = S.revenuesplitid,
    @CONSTITUENTID = R.constituentid,
    @TRANSACTIONCURRENCYID = (
        case 
            when RS.TRANSACTIONCURRENCYID is null
                then RS.BASECURRENCYID
            else RS.TRANSACTIONCURRENCYID
            end
        ),
    @REVENUELOOKUPID = R.LOOKUPID,
    @BASEAMOUNT = RS.AMOUNT,
    @BASECURRENCYID = RS.BASECURRENCYID
    from dbo.SPONSORSHIP S
    left outer join REVENUESPLIT RS
    on S.REVENUESPLITID = RS.ID
    left outer join REVENUE R
    on RS.REVENUEID = R.ID
    where S.ID = @ID

    --Get donor name

    select @NAME = name
    from dbo.UFN_CONSTITUENT_DISPLAYNAME(@CONSTITUENTID)

    --Get Revenueid, cost designationid from Revenuesplit table.

    select @REVENUEID = revenueid,
    @COST = transactionamount,
    @DESIGNATIONID = designationid
    from dbo.REVENUESPLIT
    where ID = @REVENUESPLITID

    --Get start date, end date and frequency from RevenueSchedule table.

    select @STARTDATE = startdate,
    @ENDDATE = enddate,
    @FREQUENCY = frequency,
    @ISPENDING = REVENUESCHEDULE.ISPENDING
    from dbo.REVENUESCHEDULE
    where ID = @REVENUEID

    --Get Designation name from designation table.

    select @DESIGNATION = name
    from dbo.DESIGNATION
    where ID = @DESIGNATIONID

    -- SET PAST DUE AND NEXT TRANSACTION DATE (#45863)

    set @PASTDUE = dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT_2(@REVENUEID, null, 0);

    select @NEXTTRANSACTION = case 
        when NEXTTRANSACTIONDATE > ENDDATE
            then null
        else NEXTTRANSACTIONDATE
        end
    from dbo.REVENUESCHEDULE
    where ID = @REVENUEID;

    --Total commitment paid

    select @TOTALCOMMITMENT = ISNULL(sum(RECURRINGGIFTACTIVITY.amount), 0.00),
    @TOTALSPONSORSHIP = ISNULL(sum(case 
                when sp.sponsorshipid = @ID
                    then RECURRINGGIFTACTIVITY.amount
                else 0.00
                end), 0.00)
    from dbo.RECURRINGGIFTACTIVITY
    inner join dbo.REVENUESPLIT
    on REVENUESPLIT.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
    left join sponsorshippayment sp
    on sp.id = REVENUESPLIT.ID
    where SOURCEREVENUEID = @REVENUEID

    --Total sponsorship paid

    /*
    select 
            @TOTALSPONSORSHIP = ISNULL(sum(rga.amount),0.00)
    from
            sponsorshippayment sp
            inner join REVENUESPLIT rs on rs.ID = sp.id
        inner join RECURRINGGIFTACTIV
    where
            sp.sponsorshipid = @ID
            */
    --Always showing past due now that the payment handling rules replace the RECURRINGGIFTSETTING

    set @SHOWPASTDUE = 1

    if @ISPENDING = 1
    begin
    select top 1 @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER
    from dbo.BATCH
    inner join dbo.BATCHREVENUE
        on BATCHREVENUE.BATCHID = BATCH.ID
    left join dbo.BATCHREVENUEAPPLICATION as [BATCHAPP]
        on [BATCHAPP].BATCHREVENUEID = BATCHREVENUE.ID
    where BATCH.STATUSCODE not in (
            1,
            2
            )
        and (
            (
                [BATCHAPP].REVENUEID = @REVENUEID
                and BATCHREVENUE.PAYINGPENDINGREVENUEID is null
                and [BATCHAPP].WASGENERATED = 1
                )
            or (
                [BATCHAPP].REVENUEID is null
                and BATCHREVENUE.PAYINGPENDINGREVENUEID = @REVENUEID
                )
            or (
                [BATCHAPP].REVENUEID = @REVENUEID
                and BATCHREVENUE.PAYINGPENDINGREVENUEID = @REVENUEID
                )
            )
    end

    return 0;