USP_REVENUEBATCH_CONSTITUENTCOMMITMENT

CREATE procedure dbo.USP_REVENUEBATCH_CONSTITUENTCOMMITMENT
(
  @CONSTITUENTID1 uniqueidentifier = null,
  @CONSTITUENTLOOKUPID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @PAYINGPENDINGREVENUEID uniqueidentifier = null,
  @COMMITMENTID nvarchar(60) = null,
  @PDACCOUNTSYSTEMID uniqueidentifier = null,
  @BATCHREVENUEROWID uniqueidentifier = null
)
as
  set nocount on;

  if @PDACCOUNTSYSTEMID is null
    set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID);

  -- Fall back to the system default if the above doesn't work

  if @PDACCOUNTSYSTEMID is null
    set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';

  declare @APPUSERISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

  --If the constituent pays on another constituents commitments we need to get the

  --ID of the other constituent so we can get their commitment information

  declare @CONSTITUENTID uniqueidentifier;
  declare @PAYERID uniqueidentifier;
  declare @SINGLEAPPLICATIONID uniqueidentifier = null;

  set @PAYERID = coalesce(@CONSTITUENTID1, @CONSTITUENTLOOKUPID);

  if len(@COMMITMENTID) >= 39
  begin
    set @CONSTITUENTID = dbo.UFN_REVENUEBATCH_GETCONSTITUENTFOROTHERCOMMITEMENT(@COMMITMENTID);

    if @CONSTITUENTID is null
      set @CONSTITUENTID = coalesce(@CONSTITUENTID1, @CONSTITUENTLOOKUPID);

    select
      @SINGLEAPPLICATIONID = SINGLEAPPLICATIONID
    from
      dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(@COMMITMENTID);
  end
  else
  begin
    set @CONSTITUENTID = coalesce(@CONSTITUENTID1, @CONSTITUENTLOOKUPID);
  end

  if object_id('tempdb..#REVENUEBATCH_CONSTITUENTCOMMITMENT_CONSTITIDS') is not null
    drop table #REVENUEBATCH_CONSTITUENTCOMMITMENT_CONSTITIDS;

  create table #REVENUEBATCH_CONSTITUENTCOMMITMENT_CONSTITIDS 
  (
    ID uniqueidentifier
  );

  declare @SITES table (SITEID uniqueidentifier);

  if @APPUSERISSYSADMIN = 1 
    or exists(
      select SYSTEMROLEAPPUSER.ID 
      from dbo.SYSTEMROLEAPPUSER 
      where SYSTEMROLEAPPUSER.APPUSERID = @CURRENTAPPUSERID and SYSTEMROLEAPPUSER.SECURITYMODECODE = 0
    )
  begin
    insert into @SITES(SITEID)
    values(null);

    insert into @SITES(SITEID) 
    select SITE.ID from dbo.SITE;
  end
  else
  begin
    if exists(select 1 from dbo.SYSTEMROLEAPPUSER where APPUSERID = @CURRENTAPPUSERID and SECURITYMODECODE = 1)
    begin
      insert into @SITES(SITEID)
      values(null);
    end

    insert into @SITES(SITEID)
    select distinct SITEID 
    from dbo.SITEPERMISSION 
    where SITEPERMISSION.APPUSERID = @CURRENTAPPUSERID;
  end

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());            

    declare @HOUSEHOLDSCANBEDONORS bit;
    set @HOUSEHOLDSCANBEDONORS = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS();

    declare @HOUSEHOLDID uniqueidentifier;
    select top(1) 
      @HOUSEHOLDID = GROUPMEMBER.GROUPID
    from dbo.GROUPMEMBER
    left join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
    where GROUPMEMBER.MEMBERID = @CONSTITUENTID
    and GROUPDATA.GROUPTYPECODE = 0
    and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1
    and @HOUSEHOLDSCANBEDONORS = 1;            

    declare @BASECONSTITUENTID uniqueidentifier;
    select
      @BASECONSTITUENTID =
      case
        when dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1 
          then
          case 
            when @HOUSEHOLDSCANBEDONORS = 1 
              then @CONSTITUENTID 
          else
            null
          end
        else
          @CONSTITUENTID
      end;

    -- include (1) the constituent, (2) the household a constituent is a member of, (3) members of that household, (4) members of the constituent if it is a household

    insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_CONSTITIDS(ID)
    (
      select @HOUSEHOLDID as ID
      where @HOUSEHOLDID is not null

      union all

      select @BASECONSTITUENTID as ID
      where @BASECONSTITUENTID is not null

      union all

      select 
        GROUPMEMBER.MEMBERID as ID
      from dbo.GROUPMEMBER
      left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
      where
        (GROUPMEMBER.MEMBERID <> @CONSTITUENTID) and
        (
          GROUPMEMBER.GROUPID = @HOUSEHOLDID or
          GROUPMEMBER.GROUPID = @CONSTITUENTID
        )
        and
          (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATE)
        and
          (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @CURRENTDATE)
    );

  /*
      The VALUE column holds a string that contains a coded application type as an integer.
      Those application types are:
      0 - Not an application (Donation, Other, Unapplied matching gift payment, etc.)
      1 - Sponsorship
      2 - Membership
      3 - Order/Reservation
      4 - Recurring gift
      5 - Pledge
      6 - Planned gift
      7 - Event registration
      8 - Matching gift claim
      9 - Grant award
      10 - Donor challenge
      These are also defined by the ApplicationInfo class in RevenueBatchHelper.vb.
  */

  if object_id('tempdb..#REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL') is not null
    drop table #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL;

  create table #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL
  (
    ID nvarchar(60) collate database_default,
    TYPECODE tinyint,
    CONSTITUENTID uniqueidentifier,
    CONSTITUENTNAME nvarchar(255) collate database_default,
    DATE date,
    AMOUNT money,
    CURRENCYID uniqueidentifier,
    DESIGNATIONLIST nvarchar(max) collate database_default,
    APPLICATIONCONSTITUENTID uniqueidentifier,
    APPLICATIONCONSTITUENTNAME nvarchar(255) collate database_default,
    MEMBERSHIPPROGRAMNAME nvarchar(255) collate database_default,
    MEMBERSHIPLEVELNAME nvarchar(255) collate database_default,
    EVENTNAME nvarchar(255) collate database_default,
    ISSPONSORSHIPRECURRINGGIFT bit,
    SEQUENCE tinyint
  );

  -- 1. Sponsorships

  if object_id('tempdb..#REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALSPONSORSHIPREVENUE') is not null
    drop table #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALSPONSORSHIPREVENUE;

  create table #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALSPONSORSHIPREVENUE
  (    
    ID uniqueidentifier, 
    TRANSACTIONAMOUNT money,
    CONSTITUENTID uniqueidentifier,
    TRANSACTIONCURRENCYID uniqueidentifier
  )

  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALSPONSORSHIPREVENUE (ID, TRANSACTIONAMOUNT, CONSTITUENTID, TRANSACTIONCURRENCYID)
  select REVENUE.ID, TRANSACTIONAMOUNT, CONSTITUENTID, TRANSACTIONCURRENCYID
  from dbo.FINANCIALTRANSACTION as REVENUE
  inner join #REVENUEBATCH_CONSTITUENTCOMMITMENT_CONSTITIDS C on C.ID = REVENUE.CONSTITUENTID
  where 
    REVENUE.TYPECODE = 2 and
    REVENUE.DELETEDON is null and
    REVENUE.TRANSACTIONAMOUNT > 0 and
    exists(select 'x' from dbo.FINANCIALTRANSACTIONLINEITEM ftli inner join dbo.REVENUESPLIT_EXT rse on rse.ID = ftli.ID where ftli.FINANCIALTRANSACTIONID = REVENUE.ID and rse.TYPECODE = 9)

  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL(ID, TYPECODE, CONSTITUENTID, DATE, AMOUNT, CURRENCYID, APPLICATIONCONSTITUENTNAME)
  select
    REVENUE.ID,
    1,
    REVENUE.CONSTITUENTID,
    REVENUESCHEDULE.NEXTTRANSACTIONDATE,
    REVENUE.TRANSACTIONAMOUNT,
    REVENUE.TRANSACTIONCURRENCYID,
    (
      select top 1 
        coalesce(
          (select NAME from dbo.SPONSORSHIPOPPORTUNITYCHILD where ID=S.SPONSORSHIPOPPORTUNITYID),
          (select NAME from dbo.SPONSORSHIPOPPORTUNITYPROJECT where ID = S.SPONSORSHIPOPPORTUNITYID)
        ) SPONSORSHIPOPPORTUNITY
      from dbo.FINANCIALTRANSACTIONLINEITEM SP
      inner join dbo.SPONSORSHIP S on S.REVENUESPLITID = SP.ID
      inner join dbo.SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID
      where SP.FINANCIALTRANSACTIONID = REVENUE.ID
      and S.STATUSCODE = 1
    )
  from #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALSPONSORSHIPREVENUE as REVENUE
  inner join
    dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
  where
    REVENUESCHEDULE.STATUSCODE in (0,5) and
    REVENUESCHEDULE.ISPENDING = 0 and
    (
      @APPUSERISSYSADMIN = 1 or
      exists
      (
        select 1 from @SITES S
        left outer join dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) SITE on SITE.SITEID = S.SITEID 
        where S.SITEID=SITE.SITEID or (S.SITEID is null and SITE.SITEID is null)
      )
    );

  -- 3. Order/Reservation, 5. Pledge, 6. Planned gift, 8. Matching gift claim, 9. Grant award, 10 Donor challenge

  if object_id('tempdb..#REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALPLEDGEREVENUE') is not null
    drop table #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALPLEDGEREVENUE;

  create table #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALPLEDGEREVENUE
  (
    ID uniqueidentifier,
    TYPECODE tinyint,
    TRANSACTIONCURRENCYID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    PDACCOUNTSYSTEMID uniqueidentifier,
    BALANCE money,
    DATE datetime,
    INSTALLMENTID uniqueidentifier
  )

    --For performance reasons, use a temp table for all potential constituent revenue.


    insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALPLEDGEREVENUE (ID, TYPECODE, TRANSACTIONCURRENCYID, CONSTITUENTID, PDACCOUNTSYSTEMID, BALANCE, DATE, INSTALLMENTID)
    select
        RES.ID, RES.TYPECODE, RES.TRANSACTIONCURRENCYID, RES.CONSTITUENTID, RES.PDACCOUNTSYSTEMID, NEXTINSTALLMENT.BALANCE, NEXTINSTALLMENT.DATE, NEXTINSTALLMENT.ID
    from
    (
        select
            FT.ID,
            C.ID CONSTITUENTID,
            FT.TYPECODE,
            FT.TRANSACTIONCURRENCYID,
            FT.PDACCOUNTSYSTEMID,
            FT.TRANSACTIONAMOUNT - isnull(sum(SPLITS.AMOUNT),0) BALANCE
        from
            #REVENUEBATCH_CONSTITUENTCOMMITMENT_CONSTITIDS C
        inner join 
            dbo.FINANCIALTRANSACTION FT    on C.ID = FT.CONSTITUENTID
        left join 
            dbo.INSTALLMENT INSTALLMENTS on INSTALLMENTS.REVENUEID = FT.ID
        left join (
            select 
                isnull(sum(ISP.AMOUNT),0) AMOUNT,
                ISPLIT.INSTALLMENTID
            from 
                dbo.INSTALLMENTSPLIT ISPLIT 
            inner join 
                dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.INSTALLMENTSPLITID = ISPLIT.ID
            group by
                ISPLIT.INSTALLMENTID
            union all
            select 
                isnull(sum(ISW.AMOUNT),0) AMOUNT,
                ISPLIT.INSTALLMENTID
            from 
                dbo.INSTALLMENTSPLIT ISPLIT 
            inner join 
                dbo.INSTALLMENTSPLITWRITEOFF ISW on ISW.INSTALLMENTSPLITID = ISPLIT.ID
            group by
                ISPLIT.INSTALLMENTID) as SPLITS on SPLITS.INSTALLMENTID = INSTALLMENTS.ID
        where 
        FT.DELETEDON is null
        group by FT.ID, C.ID, TYPECODE, FT.TRANSACTIONCURRENCYID, FT.PDACCOUNTSYSTEMID, FT.TRANSACTIONAMOUNT 

    ) RES

    outer apply (
        select
            top 1 INSTALLMENTS.ID,
            INSTALLMENTS.[DATE],
            sum(INSTALLMENTS.TRANSACTIONAMOUNT) - isnull(sum(SUMMEDSPLITS.AMOUNT),0) BALANCE
        from
            dbo.INSTALLMENT INSTALLMENTS
        left join (
            select
                sum(SPLITS.AMOUNT) as AMOUNT,
                SPLITS.ID 
                from 
                (
                    select 
                        isnull(sum(ISP.AMOUNT),0) as AMOUNT,
                        ISPLIT.INSTALLMENTID as ID
                    from 
                        dbo.INSTALLMENTSPLIT ISPLIT 
                    inner join 
                        dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.INSTALLMENTSPLITID = ISPLIT.ID
                    group by
                        ISPLIT.INSTALLMENTID
                    union all
                    select 
                        isnull(sum(ISW.AMOUNT),0) as AMOUNT,
                        ISPLIT.INSTALLMENTID as ID
                    from 
                        dbo.INSTALLMENTSPLIT ISPLIT 
                    inner join 
                        dbo.INSTALLMENTSPLITWRITEOFF ISW on ISW.INSTALLMENTSPLITID = ISPLIT.ID
                    group by
                        ISPLIT.INSTALLMENTID
                ) SPLITS
                inner join
                    dbo.INSTALLMENT on SPLITS.ID = INSTALLMENT.ID
                group by SPLITS.ID 
        ) as SUMMEDSPLITS on SUMMEDSPLITS.ID = INSTALLMENTS.ID
        where 
            INSTALLMENTS.REVENUEID = RES.ID
        group by 
            INSTALLMENTS.ID, INSTALLMENTS.[DATE]
        having 
            (sum(INSTALLMENTS.TRANSACTIONAMOUNT) - isnull(sum(SUMMEDSPLITS.AMOUNT),0)) > 0
        order by 
            INSTALLMENTS.[DATE] asc
    ) as NEXTINSTALLMENT

    where RES.BALANCE > 0 or RES.ID = @SINGLEAPPLICATIONID;


  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL(ID, TYPECODE, CONSTITUENTID, DATE, AMOUNT, CURRENCYID, APPLICATIONCONSTITUENTID)
  select
    REVENUE.ID,
    case REVENUE.TYPECODE 
      when 5 then 3
      when 1 then 5
      when 4 then 6
      when 3 then 8
      when 6 then 9
      when 8 then 10
    end,
    REVENUE.CONSTITUENTID,
    REVENUE.DATE,
    REVENUE.BALANCE,
    REVENUE.TRANSACTIONCURRENCYID,
    FT.CONSTITUENTID
  from
    #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALPLEDGEREVENUE as REVENUE
  left join
    dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
  left join
    dbo.REVENUEMATCHINGGIFT  on REVENUE.ID = REVENUEMATCHINGGIFT.ID        
  left join 
    dbo.FINANCIALTRANSACTION FT on FT.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID 
  where
    (
      REVENUE.TYPECODE = 5 or
      (
        REVENUE.INSTALLMENTID is not null and 
        REVENUESCHEDULE.ISPENDING = 0
      ) 
    ) and
    (REVENUEMATCHINGGIFT.ISACTIVE = 1 or REVENUE.TYPECODE <> 3) and
    REVENUE.TYPECODE in (1,3,4,5,6,8) and -- Pledge, Matching gift claim, Planned gift, Order, Grant award, Donor challenge claim

    ((REVENUE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) or (dbo.UFN_VALID_BASICGL_INSTALLED() = 0) or REVENUE.TYPECODE in (3,8)) and -- MGC and Donor Challenge

    (
      @APPUSERISSYSADMIN = 1 or
      exists
      (
        select 1 from @SITES S
        left outer join dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) SITE on SITE.SITEID = S.SITEID 
        where S.SITEID=SITE.SITEID or (S.SITEID is null and SITE.SITEID is null)
      )
    );

  -- 8 (again). Subsidiary matching gift claims (JamesWill WI76028 2011-02-01)

  if object_id('tempdb..#REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALSUBSIDIARYMGCLAIMREVENUE') is not null
    drop table #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALSUBSIDIARYMGCLAIMREVENUE;

  create table #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALSUBSIDIARYMGCLAIMREVENUE
  (
    ID uniqueidentifier,
    TYPECODE tinyint,
    TRANSACTIONCURRENCYID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    PDACCOUNTSYSTEMID uniqueidentifier,
    BALANCE money,
    DATE datetime
  )

    --For performance reasons, use a temp table for all potential constituent revenue.


    insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALSUBSIDIARYMGCLAIMREVENUE (ID, TYPECODE, TRANSACTIONCURRENCYID, CONSTITUENTID, PDACCOUNTSYSTEMID, BALANCE, DATE)
    select
        RES.ID, RES.TYPECODE, RES.TRANSACTIONCURRENCYID, RES.CONSTITUENTID, RES.PDACCOUNTSYSTEMID, NEXTINSTALLMENT.BALANCE, NEXTINSTALLMENT.DATE
    from
    (
        select
            FT.ID,
            C.ID CONSTITUENTID,
            FT.TYPECODE,
            FT.TRANSACTIONCURRENCYID,
            FT.PDACCOUNTSYSTEMID,
            FT.TRANSACTIONAMOUNT - isnull(sum(SPLITS.AMOUNT),0) BALANCE
        from
            #REVENUEBATCH_CONSTITUENTCOMMITMENT_CONSTITIDS C
        inner join 
            dbo.ORGANIZATIONDATA on C.ID = ORGANIZATIONDATA.PARENTCORPID
        inner join 
            dbo.FINANCIALTRANSACTION FT on ORGANIZATIONDATA.ID = FT.CONSTITUENTID
        left join 
            dbo.INSTALLMENT INSTALLMENTS on INSTALLMENTS.REVENUEID = FT.ID
        left join (
            select 
                isnull(sum(ISP.AMOUNT),0) AMOUNT,
                ISPLIT.INSTALLMENTID
            from 
                dbo.INSTALLMENTSPLIT ISPLIT 
            inner join 
                dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.INSTALLMENTSPLITID = ISPLIT.ID
            group by
                ISPLIT.INSTALLMENTID
            union all
            select 
                isnull(sum(ISW.AMOUNT),0) AMOUNT,
                ISPLIT.INSTALLMENTID
            from 
                dbo.INSTALLMENTSPLIT ISPLIT 
            inner join 
                dbo.INSTALLMENTSPLITWRITEOFF ISW on ISW.INSTALLMENTSPLITID = ISPLIT.ID
            group by
                ISPLIT.INSTALLMENTID) as SPLITS on SPLITS.INSTALLMENTID = INSTALLMENTS.ID
        where 
            FT.DELETEDON is null
        group by 
            FT.ID, C.ID, TYPECODE, FT.TRANSACTIONCURRENCYID, FT.PDACCOUNTSYSTEMID, FT.TRANSACTIONAMOUNT 

    ) RES
    outer apply (
        select
            top 1 INSTALLMENTS.ID,
            INSTALLMENTS.[DATE],
            sum(INSTALLMENTS.TRANSACTIONAMOUNT) - isnull(sum(SUMMEDSPLITS.AMOUNT),0) BALANCE
        from
            dbo.INSTALLMENT INSTALLMENTS
        left join (
            select
                sum(SPLITS.AMOUNT) as AMOUNT,
                SPLITS.ID 
            from 
            (
                select 
                    isnull(sum(ISP.AMOUNT),0) as AMOUNT,
                    ISPLIT.INSTALLMENTID as ID
                from
                    dbo.INSTALLMENTSPLIT ISPLIT 
                inner join
                    dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.INSTALLMENTSPLITID = ISPLIT.ID
                group by 
                    ISPLIT.INSTALLMENTID
                union all
                select 
                    isnull(sum(ISW.AMOUNT),0) as AMOUNT,
                    ISPLIT.INSTALLMENTID as ID
                from 
                    dbo.INSTALLMENTSPLIT ISPLIT 
                inner join
                    dbo.INSTALLMENTSPLITWRITEOFF ISW on ISW.INSTALLMENTSPLITID = ISPLIT.ID
                group by
                    ISPLIT.INSTALLMENTID
            ) SPLITS
            inner join 
                INSTALLMENT on SPLITS.ID = INSTALLMENT.ID
            group by SPLITS.ID 
        ) as SUMMEDSPLITS on SUMMEDSPLITS.ID = INSTALLMENTS.ID
        where 
            INSTALLMENTS.REVENUEID = RES.ID
        group by 
            INSTALLMENTS.ID, INSTALLMENTS.[DATE]
        having 
            (sum(INSTALLMENTS.TRANSACTIONAMOUNT) - isnull(sum(SUMMEDSPLITS.AMOUNT),0)) > 0
        order by 
            INSTALLMENTS.[DATE] asc
    ) as NEXTINSTALLMENT

    where RES.BALANCE > 0 or RES.ID = @SINGLEAPPLICATIONID


  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL(ID, TYPECODE, CONSTITUENTID, DATE, AMOUNT, CURRENCYID, APPLICATIONCONSTITUENTID)
  select
    REVENUE.ID,
    8,
    REVENUE.CONSTITUENTID,
    REVENUE.DATE,
    REVENUE.BALANCE,
    REVENUE.TRANSACTIONCURRENCYID,
    FT.CONSTITUENTID
  from #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALSUBSIDIARYMGCLAIMREVENUE as REVENUE
  inner join
    dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
  left join
    dbo.REVENUEMATCHINGGIFT on REVENUE.ID = REVENUEMATCHINGGIFT.ID
  left join 
    dbo.FINANCIALTRANSACTION FT on FT.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID 
  left join
    dbo.PDACCOUNTSYSTEMFORREVENUE PDAS on REVENUE.ID = PDAS.ID
  where
    REVENUESCHEDULE.ISPENDING = 0 and
    (REVENUEMATCHINGGIFT.ISACTIVE = 1 or REVENUE.TYPECODE <> 3) and
    REVENUE.TYPECODE = 3 and -- Matching gift claim

    (
      @APPUSERISSYSADMIN = 1 or
      exists
      (
        select 1 from @SITES S
        left outer join dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) SITE on SITE.SITEID = S.SITEID 
        where S.SITEID=SITE.SITEID or (S.SITEID is null and SITE.SITEID is null)
      )
    );

  -- 4. Recurring gift

  if object_id('tempdb..#REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALRECURRINGGIFTREVENUE') is not null
    drop table #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALRECURRINGGIFTREVENUE;

  create table #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALRECURRINGGIFTREVENUE
  (
    ID uniqueidentifier, 
    TRANSACTIONAMOUNT money, 
    TRANSACTIONCURRENCYID uniqueidentifier, 
    CONSTITUENTID uniqueidentifier
  )

  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALRECURRINGGIFTREVENUE (ID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, CONSTITUENTID)
  select REVENUE.ID, REVENUE.TRANSACTIONAMOUNT, REVENUE.TRANSACTIONCURRENCYID, CONSTITUENTID
  from dbo.FINANCIALTRANSACTION REVENUE      
  inner join #REVENUEBATCH_CONSTITUENTCOMMITMENT_CONSTITIDS C on C.ID = REVENUE.CONSTITUENTID  
  where 
    REVENUE.TYPECODE = 2 and 
    REVENUE.TRANSACTIONAMOUNT > 0 and
    not exists(select 'x' from dbo.FINANCIALTRANSACTIONLINEITEM ftli inner join dbo.REVENUESPLIT_EXT rse on rse.ID = ftli.ID where ftli.FINANCIALTRANSACTIONID = REVENUE.ID and rse.TYPECODE = 9)

  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL(ID, TYPECODE, CONSTITUENTID, DATE, AMOUNT, CURRENCYID, ISSPONSORSHIPRECURRINGGIFT)
  select
    REVENUE.ID,
    4,
    REVENUE.CONSTITUENTID,
    REVENUESCHEDULE.NEXTTRANSACTIONDATE,
    REVENUE.TRANSACTIONAMOUNT,
    REVENUE.TRANSACTIONCURRENCYID,
    case
      when exists(select 'x' from dbo.FINANCIALTRANSACTIONLINEITEM ftli inner join dbo.REVENUESPLIT_EXT rse on rse.ID = ftli.ID where ftli.FINANCIALTRANSACTIONID = REVENUE.ID and rse.TYPECODE = 17)
        then 1
      else
        0
    end
  from
    #REVENUEBATCH_CONSTITUENTCOMMITMENT_POTENTIALRECURRINGGIFTREVENUE as REVENUE
  inner join
    dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
  where
    REVENUESCHEDULE.STATUSCODE in (0,5) and
    REVENUESCHEDULE.ISPENDING = 0 and
    (
      @APPUSERISSYSADMIN = 1 or
      exists
      (
        select 1 from @SITES S
        left outer join dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) SITE on SITE.SITEID = S.SITEID
        where S.SITEID=SITE.SITEID or (S.SITEID is null and SITE.SITEID is null)
      )
    );


  -- 7. Event registration

  if object_id('tempdb..#POTENTIALEVENTREGISTRATIONREVENUE') is not null
    drop table #POTENTIALEVENTREGISTRATIONREVENUE;

  create table #POTENTIALEVENTREGISTRATIONREVENUE
  (
    REGISTRANTID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    EVENTSTARTDATE date,
    EVENTNAME nvarchar(100) collate database_default,
    EVENTBASECURRENCYID uniqueidentifier
  );

  insert into #POTENTIALEVENTREGISTRATIONREVENUE
  (
    REGISTRANTID,
    CONSTITUENTID,
    EVENTSTARTDATE,
    EVENTNAME,
    EVENTBASECURRENCYID
  )
  select
    REGISTRANT.ID,
    REGISTRANT.CONSTITUENTID,
    [EVENT].STARTDATE,
    [EVENT].NAME,
    [EVENT].BASECURRENCYID
  from
    dbo.REGISTRANT
  inner join
    #REVENUEBATCH_CONSTITUENTCOMMITMENT_CONSTITIDS C on C.ID = REGISTRANT.CONSTITUENTID
  inner join
    dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID;

  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL
  (
    ID,
    TYPECODE,
    CONSTITUENTID,
    [DATE],
    AMOUNT,
    CURRENCYID,
    EVENTNAME
  )
  select
    EVENTREVENUE.REGISTRANTID,
    7,
    EVENTREVENUE.CONSTITUENTID,
    EVENTREVENUE.EVENTSTARTDATE,
    BALANCE.AMOUNT,
    EVENTREVENUE.EVENTBASECURRENCYID,
    EVENTREVENUE.EVENTNAME
  from
    #POTENTIALEVENTREGISTRATIONREVENUE EVENTREVENUE
  inner join
    (
      select
        REGISTRANTID,
        sum(AMOUNT) as AMOUNT
      from
        (
          --baseline

          select
            ID as REGISTRANTID,
            0 as AMOUNT
          from
            dbo.REGISTRANT

          union all

          --outstanding registrations

          select
            REGISTRANTID,
            AMOUNT
          from
            dbo.REGISTRANTREGISTRATION

          union all

          --payments on registrations

          select
            REGISTRANTID,
            -AMOUNT
          from
            dbo.EVENTREGISTRANTPAYMENT

          union all

          --credits toward registrations

          select
            CREDITITEMEVENTREGISTRATION.REGISTRANTID,
            CREDITITEM.TOTAL
          from
            dbo.CREDITITEM
          inner join
            dbo.CREDITITEMEVENTREGISTRATION on CREDITITEMEVENTREGISTRATION.ID = CREDITITEM.ID
        ) as BALANCEITEMS
      group by
        REGISTRANTID
    ) as BALANCE on BALANCE.REGISTRANTID = EVENTREVENUE.REGISTRANTID
  where 
  (
    BALANCE.AMOUNT > 0 or
    --Bug 133370 - AdamBu - 1/10/11 - Include paid off commitment if it is what this revenue was applied to.

    EVENTREVENUE.REGISTRANTID = @SINGLEAPPLICATIONID
  )
  and
  (
    @APPUSERISSYSADMIN = 1 or
    exists
    (
      select 1 from @SITES S
      left outer join dbo.UFN_SITEID_MAPFROM_REGISTRANTID(EVENTREVENUE.REGISTRANTID) SITE on SITE.SITEID = S.SITEID
      where S.SITEID=SITE.SITEID or (S.SITEID is null and SITE.SITEID is null)
    )
  );

  --If this batch row is paying a pending transaction, insert it into the list

  if @PAYINGPENDINGREVENUEID is not null and not exists (select ID from #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL where ID = @PAYINGPENDINGREVENUEID)
  begin
    insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL(ID, TYPECODE, CONSTITUENTID, DATE, AMOUNT, CURRENCYID)
    select
      REVENUE.ID,
      case REVENUE.TYPECODE
        when 2
          then 4
        when 1
          then 5
        else
          99
      end,
      REVENUE.CONSTITUENTID,
      case REVENUE.TYPECODE
        when 2
          then REVENUESCHEDULE.NEXTTRANSACTIONDATE
        when 1
          then INSTALLMENT.DATE
        else
          ''
      end,
      case REVENUE.TYPECODE
        when 2
          then REVENUE.TRANSACTIONAMOUNT
        when 1
          then dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID)
        else
          ''
      end,
      REVENUE.TRANSACTIONCURRENCYID
    from
      dbo.FINANCIALTRANSACTION as REVENUE
    inner join
      dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
    left join
      dbo.INSTALLMENT on REVENUE.TYPECODE = 1 and INSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REVENUE.ID)
    where
      REVENUE.ID = @PAYINGPENDINGREVENUEID;
  end

  if object_id('tempdb..#REVENUEBATCH_CONSTITUENTCOMMITMENT_DESIGNATIONS') is not null
    drop table #REVENUEBATCH_CONSTITUENTCOMMITMENT_DESIGNATIONS;

  -- Gather all the designation names for each row in results table

  create table #REVENUEBATCH_CONSTITUENTCOMMITMENT_DESIGNATIONS
  (
    ID uniqueidentifier,
    DESIGNATION varchar(max) collate database_default
  )

  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_DESIGNATIONS (ID, DESIGNATION)
    select
      REVENUECOMMITMENTS.ID,
      dbo.UDA_BUILDLIST(DESIGNATION.USERID)
    from 
      #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL REVENUECOMMITMENTS
    inner join 
      FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID  = REVENUECOMMITMENTS.ID
    inner join 
      dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
    inner join 
      dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
    where
      FTLI.DELETEDON is null
    group by 
      REVENUECOMMITMENTS.ID;

  -- Update all derived names at once, rather than each insert for performance reasons

  update REVENUECOMMITMENTS
  set
  DESIGNATIONLIST = (
    select
      D.DESIGNATION
    from 
      #REVENUEBATCH_CONSTITUENTCOMMITMENT_DESIGNATIONS D
    where 
      D.ID = REVENUECOMMITMENTS.ID
  )
  from 
    #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL REVENUECOMMITMENTS;

  update REVENUECOMMITMENTS
  set
  CONSTITUENTNAME = 
  (
    select NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUECOMMITMENTS.CONSTITUENTID) 
  )
  from 
    #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL REVENUECOMMITMENTS
  where
    REVENUECOMMITMENTS.CONSTITUENTNAME is null;

  update REVENUECOMMITMENTS
  set
  APPLICATIONCONSTITUENTNAME = 
  (
    select NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUECOMMITMENTS.APPLICATIONCONSTITUENTID) 
  )
  from 
    #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL REVENUECOMMITMENTS
  where
    REVENUECOMMITMENTS.APPLICATIONCONSTITUENTNAME is null;

  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL(ID, TYPECODE)
  values(0, 20)

  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL(ID, TYPECODE)
  values(0, 21)

  insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL(ID, TYPECODE)
  values(0, 22)

  --if the payer is an active sponsor, add 'Sponsorship additional donation'

  if exists
  (
    select 1 from dbo.CONSTITUENT where CONSTITUENT.ID = @PAYERID and exists
    (
      select 1 from dbo.FINANCIALTRANSACTIONLINEITEM
      inner join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
      where FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
      and SPONSORSHIP.STATUSCODE in (0,1)
    )
  )
    insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL(ID, TYPECODE)
    values(0, 23);

  if @BATCHREVENUEROWID is not null
  begin
    declare @APPLICATIONINFO nvarchar(60);
    select top 1 @APPLICATIONINFO = APPLICATIONINFO from dbo.BATCHREVENUE where ID = @BATCHREVENUEROWID;
    if @APPLICATIONINFO is not null and len(@APPLICATIONINFO) > 0
    begin
      declare @SPLITINDEX int = charindex(':', @APPLICATIONINFO);
      declare @APPLICATIONID nvarchar(36) = substring(@APPLICATIONINFO, 1, @SPLITINDEX - 1);

      if not exists (select ID from #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL where ID = @APPLICATIONID)
        insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL(ID, TYPECODE)
        values(@APPLICATIONINFO, 0);
    end
  end

  select
    ID,
    TYPECODE,
    CONSTITUENTNAME,
    DATE,
    AMOUNT,
    CURRENCYID,
    DESIGNATIONLIST,
    APPLICATIONCONSTITUENTNAME,
    MEMBERSHIPPROGRAMNAME,
    MEMBERSHIPLEVELNAME,
    EVENTNAME,
    ISSPONSORSHIPRECURRINGGIFT
  from
    #REVENUEBATCH_CONSTITUENTCOMMITMENT_RETVAL
  order by
    TYPECODE;