USP_REVENUEBATCH_SPONSORSHIPRECURRINGGIFTCOMMITMENT

CREATE procedure dbo.USP_REVENUEBATCH_SPONSORSHIPRECURRINGGIFTCOMMITMENT
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @CONSTITUENTNAME nvarchar(255) = null output,
  @SPONSORSHIPNAME nvarchar(255) = null output,
  @NEXTTRANSACTIONDATE date = null output,
  @DESIGNATIONLIST nvarchar(max) = null output,
  @TRANSACTIONAMOUNT money = null output,
  @TRANSACTIONCURRENCYID uniqueidentifier = null output
)
as begin
  set nocount on;

  begin try

    select
      @DATALOADED = 1,
      @CONSTITUENTNAME = CONSTITUENT_NF.NAME,
      @SPONSORSHIPNAME =
      (
        select top 1 
          coalesce(
            (select NAME from SPONSORSHIPOPPORTUNITYCHILD where ID=S.SPONSORSHIPOPPORTUNITYID),
            (select NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID = S.SPONSORSHIPOPPORTUNITYID)
          ) SPONSORSHIPOPPORTUNITY
        from REVENUESPLIT SP
        inner join SPONSORSHIP S on S.REVENUESPLITID = SP.ID
        inner join SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID
        where SP.REVENUEID = REVENUE.ID
      ),
      @NEXTTRANSACTIONDATE = REVENUESCHEDULE.NEXTTRANSACTIONDATE,
      @DESIGNATIONLIST = 
      (
        select top 1
          dbo.UDA_BUILDLIST(DESIGNATION.USERID)
        from
          dbo.REVENUESPLIT
        inner join
          dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
        where
          REVENUESPLIT.REVENUEID = REVENUE.ID
      ),
      @TRANSACTIONAMOUNT = REVENUE.TRANSACTIONAMOUNT,
      @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
    from
      dbo.REVENUE
    left join
      dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
    cross apply
      dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) CONSTITUENT_NF
    where
      dbo.REVENUE.ID = @ID;

  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;

end;