UFN_REVENUEBATCH_GETAPPLICATIONBUSINESSUNITS

CREATE function dbo.UFN_REVENUEBATCH_GETAPPLICATIONBUSINESSUNITS(
  @BATCHREVENUEID uniqueidentifier,
  @USESPLITS bit
)
returns xml
with execute as caller
as begin
  declare @APPLICATIONBUSINESSUNITS table(APPLICATIONID uniqueidentifier, BUSINESSUNITS xml , OVERRIDEBUSINESSUNITS bit, REASON uniqueidentifier, REVENUESPLITID uniqueidentifier, ADDITIONALAPPLICATIONDECLINESGIFTAID bit, ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier);
  declare @REVENUEID uniqueidentifier;

  select @REVENUEID = REVENUEID
  from dbo.BATCHREVENUE
  where ID = @BATCHREVENUEID

    if @USESPLITS = 0
  begin
    if exists(select ID from BATCHREVENUEAPPLICATION where BATCHREVENUEID = @BATCHREVENUEID)    
    begin
      -- Multiple applications 

      -- NOTE: magic ID '9B9C1DC8-7960-4D31-A0BC-8199AB7F94DA' is used for pledges added from the apply dialog - this allows business units to work since it is dependent on the APPLICATIONID and new pledges get assigned new IDs every time they are saved

      insert into @APPLICATIONBUSINESSUNITS (APPLICATIONID, BUSINESSUNITS, OVERRIDEBUSINESSUNITS, REASON, REVENUESPLITID)
      select
          coalesce(REVENUEID, 
                 REGISTRANTID, 
                 case when BATCHREVENUEAPPLICATIONPLEDGEID is null then null else '9B9C1DC8-7960-4D31-A0BC-8199AB7F94DA' end,
                 BATCHREVENUEREGISTRANTID) as APPLICATIONID,
          (select BUSINESSUNITCODEID, AMOUNT from dbo.BATCHREVENUEAPPLICATIONBUSINESSUNIT where BATCHREVENUEAPPLICATIONID = BATCHREVENUEAPPLICATION.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
          OVERRIDEBUSINESSUNITS,
          REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
        REVENUESPLITID
        from dbo.BATCHREVENUEAPPLICATION 
      where BATCHREVENUEID = @BATCHREVENUEID
    end
    else
    begin
      -- Single applications

      if exists(select ID from dbo.BATCHREVENUESPLIT where BATCHREVENUEID = @BATCHREVENUEID)
      begin
        insert into @APPLICATIONBUSINESSUNITS (APPLICATIONID, BUSINESSUNITS, OVERRIDEBUSINESSUNITS, REASON, REVENUESPLITID)
        select
          DESIGNATIONID as APPLICATIONID,
          (select BUSINESSUNITCODEID, AMOUNT from dbo.BATCHREVENUESPLITBUSINESSUNIT where BATCHREVENUESPLITID = BATCHREVENUESPLIT.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
          OVERRIDEBUSINESSUNITS,
          REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
          REVENUESPLITID
        from dbo.BATCHREVENUESPLIT where BATCHREVENUEID = @BATCHREVENUEID
      end
      else
      begin
        if exists(select TYPECODE from dbo.BATCHREVENUE where ID = @BATCHREVENUEID and TYPECODE=0)  and (select len(APPLICATIONINFO) from dbo.BATCHREVENUE where ID = @BATCHREVENUEID and TYPECODE=0) > 16
        begin
         insert into @APPLICATIONBUSINESSUNITS (APPLICATIONID, BUSINESSUNITS, OVERRIDEBUSINESSUNITS, REASON, REVENUESPLITID)
         select
            convert(uniqueidentifier,SUBSTRING(BATCHREVENUE.APPLICATIONINFO,0,CHARINDEX(':',BATCHREVENUE.APPLICATIONINFO))) as APPLICATIONID,
            (select BUSINESSUNITCODEID, AMOUNT from dbo.BATCHREVENUEBUSINESSUNIT where BATCHREVENUEID = BATCHREVENUE.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
            OVERRIDEBUSINESSUNITS,
            REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
          REVENUESPLITID
           from dbo.BATCHREVENUE where ID = @BATCHREVENUEID
        end
      end
    end

    -- Additional applications

    insert into @APPLICATIONBUSINESSUNITS (APPLICATIONID, BUSINESSUNITS, OVERRIDEBUSINESSUNITS, REASON, REVENUESPLITID, ADDITIONALAPPLICATIONDECLINESGIFTAID, ADDITIONALAPPLICATIONSPONSORSHIPID)
      select
          ID as APPLICATIONID,
          (select BUSINESSUNITCODEID, AMOUNT from dbo.BATCHREVENUEADDITIONALAPPLICATIONBUSINESSUNIT  where BATCHREVENUEADDITIONALAPPLICATIONBUSINESSUNIT.BATCHREVENUEADDITIONALAPPLICATIONID = BATCHREVENUEADDITIONALAPPLICATIONS.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
          OVERRIDEBUSINESSUNITS,
          REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
          REVENUESPLITID,
          DECLINESGIFTAID,
        SPONSORSHIPID
        from dbo.BATCHREVENUEADDITIONALAPPLICATIONS where BATCHREVENUEID = @BATCHREVENUEID
  end
  else
  begin
    --Commitment splits

   insert into @APPLICATIONBUSINESSUNITS (APPLICATIONID, BUSINESSUNITS, OVERRIDEBUSINESSUNITS, REASON, REVENUESPLITID)
    select
      DESIGNATIONID as APPLICATIONID,
      (select BUSINESSUNITCODEID, AMOUNT from dbo.BATCHREVENUESPLITBUSINESSUNIT where BATCHREVENUESPLITID = BATCHREVENUESPLIT.ID for xml raw('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64),
      OVERRIDEBUSINESSUNITS,
      REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
      REVENUESPLITID
    from dbo.BATCHREVENUESPLIT where BATCHREVENUEID = @BATCHREVENUEID
  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,
                          ADDITIONALAPPLICATIONDECLINESGIFTAID,
                          ADDITIONALAPPLICATIONSPONSORSHIPID
                   from @APPLICATIONBUSINESSUNITS
                   where APPLICATIONID is not null -- If APPLICATIONID isn't set, the collection will cause an error during save unless its updated since APPLICATIONID is a required field.

                   for xml raw('ITEM'), type, elements, root('APPLICATIONBUSINESSUNITS'), binary base64)


    return @RESULT;
end