UFN_REVENUEUPDATEBATCH_GETBUSINESSUNITS

CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETBUSINESSUNITS(
  @REVENUEID uniqueidentifier,
  @TYPECODE smallint
)
returns xml
with execute as caller
as begin

  declare @APPLICATIONBUSINESSUNITS table(APPLICATIONID uniqueidentifier, BUSINESSUNITS xml , OVERRIDEBUSINESSUNITS bit, REASON uniqueidentifier, REVENUESPLITID uniqueidentifier);

    if @TYPECODE <> 0 --RG/Pledges

    begin
     insert into @APPLICATIONBUSINESSUNITS
      select
        DESIGNATIONID as APPLICATIONID,
        (select BUSINESSUNITCODEID, AMOUNT, BASECURRENCYID, ID as REVENUESPLITBUSINESSUNITID from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = RS.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
        OVERRIDEBUSINESSUNITS,
        REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
        RS.ID as REVENUESPLITID
      from dbo.REVENUESPLIT RS where RS.REVENUEID = @REVENUEID
    end
  else --Payments

    begin
      insert into @APPLICATIONBUSINESSUNITS      
        --additional applications (donations, other, matching gift claim, sponsorship addtl. donation)

        select
          '00000000-0000-0000-0000-000000000000' as APPLICATIONID,
          (select BUSINESSUNITCODEID, AMOUNT, BASECURRENCYID, ID as REVENUESPLITBUSINESSUNITID from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = RS.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
          OVERRIDEBUSINESSUNITS,
          REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
          RS.ID as REVENUESPLITID
        from dbo.REVENUESPLIT RS 
        left outer join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = RS.ID
        where RS.REVENUEID = @REVENUEID
        and APPLICATIONCODE in (0,4,7)
        and TYPECODE in (0,4,9,17)
        and INSTALLMENTSPLITPAYMENT.ID is null    

        union all

        --Pledge (2), Planned Gift (6), Matching Gift (7), Grant Award (8), Donor Challenge (13)

        select
          INSTALLMENTSPLITPAYMENT.PLEDGEID as APPLICATIONID,
          (select BUSINESSUNITCODEID, AMOUNT, BASECURRENCYID, ID as REVENUESPLITBUSINESSUNITID from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = RS.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
          OVERRIDEBUSINESSUNITS,
          REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
          RS.ID as REVENUESPLITID
        from dbo.REVENUESPLIT RS 
        inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = RS.ID
        where RS.REVENUEID = @REVENUEID
        and APPLICATIONCODE in (2, 6, 7, 8, 13)
        group by INSTALLMENTSPLITPAYMENT.PLEDGEID, RS.ID, OVERRIDEBUSINESSUNITS, REVENUESPLITBUSINESSUNITOVERRIDECODEID

        union all

        --Recurring gift(3)

        select
          RECURRINGGIFTACTIVITY.SOURCEREVENUEID as APPLICATIONID,
          (select BUSINESSUNITCODEID, AMOUNT, BASECURRENCYID, ID as REVENUESPLITBUSINESSUNITID from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = RS.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
          OVERRIDEBUSINESSUNITS,
          REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
          RS.ID as REVENUESPLITID
        from dbo.REVENUESPLIT RS 
        inner join dbo.RECURRINGGIFTACTIVITY on PAYMENTREVENUEID = RS.ID
        where RS.REVENUEID = @REVENUEID
        and APPLICATIONCODE = 3

        union all

        --membership(5)

        select
          RS.ID as APPLICATIONID,
          (select BUSINESSUNITCODEID, AMOUNT, BASECURRENCYID, ID as REVENUESPLITBUSINESSUNITID from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = RS.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
          OVERRIDEBUSINESSUNITS,
          REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
          RS.ID as REVENUESPLITID
        from dbo.REVENUESPLIT RS 
        inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = RS.ID
        where RS.REVENUEID = @REVENUEID
        and APPLICATIONCODE = 5

        union all

        --event registration (1)

        select
          RS.ID as APPLICATIONID,
          (select BUSINESSUNITCODEID, AMOUNT, BASECURRENCYID, ID as REVENUESPLITBUSINESSUNITID from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = RS.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
          OVERRIDEBUSINESSUNITS,
          REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
          RS.ID as REVENUESPLITID
        from dbo.REVENUESPLIT RS 
            inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = RS.ID
        where RS.REVENUEID = @REVENUEID
        and APPLICATIONCODE = 1
    end



  declare @RESULT xml;
  set @RESULT  =   (select APPLICATIONID, 
                          cast((select T.c.query('BUSINESSUNITS/ITEM') from BUSINESSUNITS.nodes('/') T(c)) as xml) as BUSINESSUNITS,
                          OVERRIDEBUSINESSUNITS,
                          REASON, 
                          REVENUESPLITID                         
                   from @APPLICATIONBUSINESSUNITS
                   for xml raw('ITEM'), type, elements, root('APPLICATIONBUSINESSUNITS'), binary base64)

    return @RESULT;
end