USP_DATAFORMTEMPLATE_VIEW_SPONSORSUMMARY

    CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SPONSORSUMMARY (
        @ID uniqueidentifier,
        @DATALOADED bit = 0 output,
        @SPONSORSINCE datetime = null output,
        @LASTSPONSORSHIP nvarchar(100) = null output,
        @SPONSORSHIPCOUNT int = null output,
        @SPONSORSHIPTOTALAMOUNT money = null output,
        @DONOTTERMINATE bit = null output,
        @PASTDUETOTAL money = null output,
        @SHOWPASTDUE bit = null output,
        @SPONSORTYPECODE tinyint = null output,
        @UNIQUEOPPORTUNITIESFORGIFTDONOR bit = null output,
        @ISFORMERSPONSOR bit = null output,
        @BASECURRENCYID uniqueidentifier = null output,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @HASRECEIVEDFUNDS bit = null output,
        @FINANCIALCOMMITMENTCOUNT int = null output,
        @SINGLESPONSORSHIP bit = null output
        )
    as
    set nocount on;
    set @DATALOADED = 0;

    declare @CURRENTDATE datetime;

    set @CURRENTDATE = getdate();

    declare @CURRENTDATEEARLIESTTIME date;

    set @CURRENTDATEEARLIESTTIME = @CURRENTDATE;

    declare @CONSTITUENTISSPONSOR bit = 0;
    declare @CONSTITUENTISFORMERSPONSOR bit = 0;

    exec dbo.USP_SPONSOR_GETINFORMATION @CONSTITUENTID = @ID,
        @ISSPONSOR = @CONSTITUENTISSPONSOR output,
        @ISFORMERSPONSOR = @CONSTITUENTISFORMERSPONSOR output;

    set @BASECURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)

    select @DATALOADED = 1
    from dbo.CONSTITUENT
    where CONSTITUENT.ID = @ID
        and (
            @CONSTITUENTISSPONSOR = 1
            or @CONSTITUENTISFORMERSPONSOR = 1
            );

    --SPONSOR SINCE
    select @SPONSORSINCE = min(DATEFROM)
    from dbo.SPONSORDATERANGE
    where CONSTITUENTID = @ID

    --LAST SPONSORSHIP, SPONSORTYPECODE
    select top 1 @LASTSPONSORSHIP = case 
            when nullif(max(DATETO), CURRENT_TIMESTAMP) >= CURRENT_TIMESTAMP
                then 'Current'
            else max(DATETO)
            end,
        @SPONSORTYPECODE = case 
            when min(SPONSORTYPECODE) = max(SPONSORTYPECODE)
                then min(SPONSORTYPECODE)
            else 0
            end
    from dbo.SPONSORDATERANGE
    where CONSTITUENTID = @ID
    group by case 
            when DATEFROM <= @CURRENTDATEEARLIESTTIME
                then 0
            else 1
            end,
        case 
            when (
                    DATETO is null
                    or DATETO > @CURRENTDATEEARLIESTTIME
                    )
                then 0
            else 1
            end
    order by case 
            when DATEFROM <= @CURRENTDATEEARLIESTTIME
                then 0
            else 1
            end,
        case 
            when (
                    DATETO is null
                    or DATETO > @CURRENTDATEEARLIESTTIME
                    )
                then 0
            else 1
            end;

    --SPONSORSHIP COUNT
    set @SPONSORSHIPCOUNT = dbo.UFN_SPONSORSHIP_GETACTIVESPONSORSHIPSFORSPONSOR(@ID);
    --Financial Commitment Count
    set @FINANCIALCOMMITMENTCOUNT = dbo.UFN_SPONSORSHIP_GETACTIVEFINANCIALCOMMITMENTFORSPONSOR(@ID);

    --TOTAL AMOUNT OF ALL SPONSORSHIP payments
    select @SPONSORSHIPTOTALAMOUNT = isnull(sum(case REVENUESPLIT.TRANSACTIONCURRENCYID
                    when @BASECURRENCYID
                        then REVENUESPLIT.TRANSACTIONAMOUNT
                    else dbo.UFN_CURRENCY_CONVERT(REVENUESPLIT.TRANSACTIONAMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(REVENUESPLIT.TRANSACTIONCURRENCYID, @BASECURRENCYID, REVENUE.date, 1, null))
                    end), 0)
    from dbo.REVENUESPLIT
    inner join dbo.REVENUE
        on REVENUESPLIT.REVENUEID = REVENUE.ID
    where REVENUESPLIT.TYPECODE = 9
        and REVENUE.TRANSACTIONTYPECODE = 0
        and REVENUE.CONSTITUENTID = @ID;

    --TOTAL AMOUNT DUE
    create table #SPONSORSHIPREVENUE (
        ID uniqueidentifier,
        [DATE] datetime,
        TRANSACTIONCURRENCYID uniqueidentifier
        )

    insert into #SPONSORSHIPREVENUE (
        ID,
        [DATE],
        TRANSACTIONCURRENCYID
        )
    select REVENUE.ID,
        REVENUE.date,
        REVENUESPLIT.TRANSACTIONCURRENCYID
    from dbo.REVENUE
    inner join dbo.REVENUESPLIT
        on REVENUESPLIT.REVENUEID = REVENUE.ID
    inner join dbo.SPONSORSHIP
        on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.id
    where REVENUE.CONSTITUENTID = @ID
        and SPONSORSHIP.STATUSCODE = 1;

    select @PASTDUETOTAL = isnull(sum(case r.TRANSACTIONCURRENCYID
                    when @BASECURRENCYID
                        then PASTDUEAMOUNT.AMOUNT
                    else dbo.UFN_CURRENCY_CONVERT(PASTDUEAMOUNT.AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(R.TRANSACTIONCURRENCYID, @BASECURRENCYID, R.date, 1, null))
                    end), 0)
    from #SPONSORSHIPREVENUE r
    inner join dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT_BULK_2(getdate(), 0) PASTDUEAMOUNT
        on r.ID = PASTDUEAMOUNT.ID;

    --CONSTITUENCIES
    select @ISFORMERSPONSOR = dbo.UFN_CONSTITUENT_ISFORMERSPONSOR(@ID)
    from dbo.CONSTITUENT as C
    where C.ID = @ID;

    -- sponsor termination code
    select @DONOTTERMINATE = DONOTTERMINATE,
        @UNIQUEOPPORTUNITIESFORGIFTDONOR = UNIQUEOPPORTUNITIESFORGIFTDONOR
    from dbo.SPONSOR S
    where S.ID = @ID;

    -- Show past due?
    --Always showing past due now that the payment handling rules replace the RECURRINGGIFTSETTING
    set @SHOWPASTDUE = 1
    set @HASRECEIVEDFUNDS = dbo.UFN_CONSTITUENT_HASRECEIVEDFUNDS(@ID)

    declare @RECORDSCOUNT int;

    select @RECORDSCOUNT = count(ID)
    from dbo.SPONSORSHIPCOMMITMENT
    where CONSTITUENTID = @ID;

    set @SINGLESPONSORSHIP = 0;

    if @RECORDSCOUNT = 1
        set @SINGLESPONSORSHIP = 1;

    return 0;