USP_REVENUEBATCH_ADDBUSINESSUNITS

CREATE procedure dbo.USP_REVENUEBATCH_ADDBUSINESSUNITS(
  @BATCHREVENUEID uniqueidentifier,
  @APPLICATIONBUSINESSUNITS xml,
  @TYPECODE tinyint
)
as
begin
  declare @CHANGEAGENTID uniqueidentifier;
  declare @APPLICATIONID uniqueidentifier;
  declare @BATCHREVENUEAPPLICATIONID uniqueidentifier;
  declare @BUSINESSUNITS xml;
  declare @REASON uniqueidentifier;
  declare @OVERRIDEBUSINESSUNITS bit;

  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  --clear any existing business unit data for this batch row - NOTE - just because the row is a payment now, doesn't mean it wasn't a pledge before, so make no assumptions

  delete from dbo.BATCHREVENUEAPPLICATIONBUSINESSUNIT where ID in (SELECT BRABU.ID FROM dbo.BATCHREVENUEAPPLICATIONBUSINESSUNIT BRABU inner join dbo.BATCHREVENUEAPPLICATION ON BRABU.BATCHREVENUEAPPLICATIONID=BATCHREVENUEAPPLICATION.ID where BATCHREVENUEAPPLICATION.BATCHREVENUEID = @BATCHREVENUEID);
  delete from dbo.BATCHREVENUEADDITIONALAPPLICATIONBUSINESSUNIT where ID in (SELECT BRABU.ID FROM dbo.BATCHREVENUEADDITIONALAPPLICATIONBUSINESSUNIT BRABU inner join dbo.BATCHREVENUEADDITIONALAPPLICATIONS ON BRABU.BATCHREVENUEADDITIONALAPPLICATIONID=BATCHREVENUEADDITIONALAPPLICATIONS.ID where BATCHREVENUEADDITIONALAPPLICATIONS.BATCHREVENUEID = @BATCHREVENUEID);
  delete from dbo.BATCHREVENUESPLITBUSINESSUNIT where ID in (SELECT BRABU.ID FROM dbo.BATCHREVENUESPLITBUSINESSUNIT BRABU inner join dbo.BATCHREVENUESPLIT ON BRABU.BATCHREVENUESPLITID=BATCHREVENUESPLIT.ID where BATCHREVENUESPLIT.BATCHREVENUEID = @BATCHREVENUEID);
  delete from dbo.BATCHREVENUEBUSINESSUNIT WHERE BATCHREVENUEID=@BATCHREVENUEID;
  update dbo.BATCHREVENUEAPPLICATION set OVERRIDEBUSINESSUNITS = 0, REVENUESPLITBUSINESSUNITOVERRIDECODEID=null where BATCHREVENUEID=@BATCHREVENUEID
  update dbo.BATCHREVENUEADDITIONALAPPLICATIONS set OVERRIDEBUSINESSUNITS = 0, REVENUESPLITBUSINESSUNITOVERRIDECODEID=null where BATCHREVENUEID=@BATCHREVENUEID
  update dbo.BATCHREVENUESPLIT set OVERRIDEBUSINESSUNITS = 0, REVENUESPLITBUSINESSUNITOVERRIDECODEID=null where BATCHREVENUEID=@BATCHREVENUEID
  update dbo.BATCHREVENUE set OVERRIDEBUSINESSUNITS = 0, REVENUESPLITBUSINESSUNITOVERRIDECODEID=null where ID=@BATCHREVENUEID

    declare @TempTbl table (
       [APPLICATIONID] uniqueidentifier,
       [BUSINESSUNITS] xml,
       [REASON] uniqueidentifier,
       [OVERRIDEBUSINESSUNITS] bit,
       [REVENUESPLITID] uniqueidentifier)

    insert into @TempTbl 
    select 
      case when T.c.value('(APPLICATIONID)[1]','uniqueidentifier') is null then null else T.c.value('(APPLICATIONID)[1]','uniqueidentifier') end as APPLICATIONID,
      cast(T.c.query('BUSINESSUNITS') as xml) as BUSINESSUNITS,
      case when T.c.value('(REASON)[1]','uniqueidentifier') is null then null else T.c.value('(REASON)[1]','uniqueidentifier') end as REASON,
      T.c.value('(OVERRIDEBUSINESSUNITS)[1]','bit') as OVERRIDEBUSINESSUNITS,
      case when T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') is null then null else T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') end as REVENUESPLITID
    from @APPLICATIONBUSINESSUNITS.nodes('/APPLICATIONBUSINESSUNITS/ITEM') T(c)

  if exists(select TOP 1 APPLICATIONID FROM @TempTbl) 
  begin

    if @TYPECODE = 0
    begin

      declare REVENUEAPPLICATIONS_PAYMENT cursor local fast_forward for 
      select APPS.ID, NEWITEMS.BUSINESSUNITS, NEWITEMS.OVERRIDEBUSINESSUNITS, NEWITEMS.REASON
      from dbo.UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN(@BATCHREVENUEID) as APPS inner join
      @TempTbl AS NEWITEMS ON (NEWITEMS.REVENUESPLITID is not null and APPS.REVENUESPLITID=NEWITEMS.REVENUESPLITID) or (NEWITEMS.REVENUESPLITID is null and APPS.APPLICATIONID=NEWITEMS.APPLICATIONID)

      open REVENUEAPPLICATIONS_PAYMENT
      fetch next from REVENUEAPPLICATIONS_PAYMENT into @BATCHREVENUEAPPLICATIONID, @BUSINESSUNITS, @OVERRIDEBUSINESSUNITS, @REASON

      while @@FETCH_STATUS = 0
      begin

            if @OVERRIDEBUSINESSUNITS is null set @OVERRIDEBUSINESSUNITS = 0; 
            if @OVERRIDEBUSINESSUNITS = 0 set @REASON = null;              

            update dbo.BATCHREVENUEAPPLICATION set
              OVERRIDEBUSINESSUNITS = @OVERRIDEBUSINESSUNITS,
              REVENUESPLITBUSINESSUNITOVERRIDECODEID=@REASON
            where ID=@BATCHREVENUEAPPLICATIONID 

            exec dbo.USP_BATCHREVENUEAPPLICATIONBUSINESSUNIT_GETBUSINESSUNITS_ADDFROMXML @BATCHREVENUEAPPLICATIONID, @BUSINESSUNITS, @CHANGEAGENTID;      

            fetch next from REVENUEAPPLICATIONS_PAYMENT into @BATCHREVENUEAPPLICATIONID, @BUSINESSUNITS, @OVERRIDEBUSINESSUNITS, @REASON

      end

      close REVENUEAPPLICATIONS_PAYMENT
      deallocate REVENUEAPPLICATIONS_PAYMENT

      declare ADDITIONALAPPLICATIONS_PAYMENT cursor local fast_forward for 
      select APPS.ID, NEWITEMS.BUSINESSUNITS, NEWITEMS.OVERRIDEBUSINESSUNITS, NEWITEMS.REASON
      from dbo.BATCHREVENUEADDITIONALAPPLICATIONS as APPS inner join
      @TempTbl AS NEWITEMS ON APPS.ID=NEWITEMS.APPLICATIONID WHERE APPS.BATCHREVENUEID=@BATCHREVENUEID

      open ADDITIONALAPPLICATIONS_PAYMENT
      fetch next from ADDITIONALAPPLICATIONS_PAYMENT into @BATCHREVENUEAPPLICATIONID, @BUSINESSUNITS, @OVERRIDEBUSINESSUNITS, @REASON

      while @@FETCH_STATUS = 0
      begin

            if @OVERRIDEBUSINESSUNITS is null set @OVERRIDEBUSINESSUNITS = 0; 
            if @OVERRIDEBUSINESSUNITS = 0 set @REASON = null;              

            update dbo.BATCHREVENUEADDITIONALAPPLICATIONS set
              OVERRIDEBUSINESSUNITS = @OVERRIDEBUSINESSUNITS,
              REVENUESPLITBUSINESSUNITOVERRIDECODEID=@REASON
            where ID=@BATCHREVENUEAPPLICATIONID 

            exec dbo.USP_BATCHREVENUEADDITIONALAPPLICATIONBUSINESSUNIT_GETBUSINESSUNITS_ADDFROMXML @BATCHREVENUEAPPLICATIONID, @BUSINESSUNITS, @CHANGEAGENTID;      

            fetch next from ADDITIONALAPPLICATIONS_PAYMENT into @BATCHREVENUEAPPLICATIONID, @BUSINESSUNITS, @OVERRIDEBUSINESSUNITS, @REASON

      end

      close ADDITIONALAPPLICATIONS_PAYMENT
      deallocate ADDITIONALAPPLICATIONS_PAYMENT

    end    
   else
    begin
      -- splits

      declare APPLICATION_SPLITS cursor local fast_forward for 
      select APPS.ID, NEWITEMS.BUSINESSUNITS, NEWITEMS.OVERRIDEBUSINESSUNITS, NEWITEMS.REASON
      from
        (
          select
            APPS.ID, APPS.DESIGNATIONID
          from
            dbo.BATCHREVENUESPLIT as APPS
          inner join
            @TempTbl as NEWITEMS on NEWITEMS.APPLICATIONID = APPS.DESIGNATIONID
          where
            APPS.BATCHREVENUEID = @BATCHREVENUEID

          union

          select
            APPS.ID, NEWITEMS.APPLICATIONID
          from
            dbo.BATCHREVENUESPLIT as APPS
          inner join
            @TempTbl as NEWITEMS on NEWITEMS.REVENUESPLITID = APPS.REVENUESPLITID
          where
            APPS.BATCHREVENUEID = @BATCHREVENUEID
        )
        as APPS
      inner join
        @TempTbl AS NEWITEMS ON APPS.DESIGNATIONID=NEWITEMS.APPLICATIONID

      open APPLICATION_SPLITS
      fetch next from APPLICATION_SPLITS into @APPLICATIONID, @BUSINESSUNITS, @OVERRIDEBUSINESSUNITS, @REASON

      while @@FETCH_STATUS = 0
      begin
        declare @BATCHREVENUESPLITID uniqueidentifier;

            if @OVERRIDEBUSINESSUNITS is null set @OVERRIDEBUSINESSUNITS = 0; 
            if @OVERRIDEBUSINESSUNITS = 0 set @REASON = null;              

        update dbo.BATCHREVENUESPLIT set
          OVERRIDEBUSINESSUNITS = @OVERRIDEBUSINESSUNITS,
          REVENUESPLITBUSINESSUNITOVERRIDECODEID=@REASON
        where BATCHREVENUESPLIT.ID = @APPLICATIONID

        exec dbo.USP_BATCHREVENUESPLITBUSINESSUNIT_GETBUSINESSUNITS_ADDFROMXML @APPLICATIONID, @BUSINESSUNITS, @CHANGEAGENTID;   

        fetch next from APPLICATION_SPLITS into @APPLICATIONID, @BUSINESSUNITS, @OVERRIDEBUSINESSUNITS, @REASON
      end
      close APPLICATION_SPLITS
      deallocate APPLICATION_SPLITS
    end

  end

end