USP_REVENUE_UPDATEAPPLICATIONS_AUCTIONPACKAGES

      CREATE procedure dbo.USP_REVENUE_UPDATEAPPLICATIONS_AUCTIONPACKAGES
      (
        @REVENUEID uniqueidentifier,
        @REVENUESTREAMS xml output
      )
      as
      begin
            --Handle auction package as revenuestream item here.  The @ID of the revenuestream is the auctionitem id.

          --An auction package does not have a corresponding revenue/revenuesplit record.

          --However, all of its items do.  We need to swap out the auction package for all

          --of the auction item revenuesplits before it gets processed.


          --All of the applications/streams that are not auction purchases will the same.


          declare @APPLICATIONS table
          (
            ITEM xml,
            APPLICATIONID uniqueidentifier,
            APPLIED money
          )

          declare @AUCTIONITEMPACKAGES table
          (
            ID uniqueidentifier,
            APPLIED money,
            ORIGINALVALUE money
          )

          -- Parse out the applications

          insert into @APPLICATIONS(ITEM,APPLICATIONID,APPLIED)
            select T.c.query('./*'), T.c.value('(ID)[1]','uniqueidentifier') as 'ID', T.c.value('(APPLIED)[1]','money') as 'APPLIED'
            from @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') as T(c)

          -- Find the applications that are auction packages

          insert into @AUCTIONITEMPACKAGES(ID,APPLIED,ORIGINALVALUE)
            select AUCTIONITEM.ID, [Applications].APPLIED, dbo.UFN_AUCTIONITEM_GETVALUE(AUCTIONITEM.ID)
            from 
            @APPLICATIONS [APPLICATIONS] 
            inner join dbo.AUCTIONITEM on [APPLICATIONS].APPLICATIONID = AUCTIONITEM.ID
            inner join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
            inner join dbo.REVENUE on AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = REVENUE.ID 
            where REVENUE.ID = @REVENUEID and AUCTIONITEM.TYPECODE = 1

          -- Delete the link to revenue from packages that have been removed from the applications

          delete dbo.AUCTIONITEMREVENUEPURCHASE
          from 
            dbo.AUCTIONITEM inner join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
          where
            AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = @REVENUEID
            and
            (
                (AUCTIONITEM.TYPECODE = 1 and AUCTIONITEM.ID not in (select ID from @AUCTIONITEMPACKAGES))
                or
                (AUCTIONITEM.TYPECODE = 0 and AUCTIONITEM.PACKAGEID is not null and AUCTIONITEM.PACKAGEID not in (select ID from @AUCTIONITEMPACKAGES))
            )

        -- Delete the link to revenue split from items in packages that have been removed from the applications

          delete dbo.AUCTIONITEMPURCHASE
          from
            dbo.AUCTIONITEM inner join dbo.AUCTIONITEMPURCHASE  on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
          where 
            AUCTIONITEMPURCHASE.PURCHASEID in (select ID from dbo.REVENUESPLIT where REVENUEID = @REVENUEID)
            and
            (    
                (AUCTIONITEM.TYPECODE = 1 and AUCTIONITEM.ID not in (select ID from @AUCTIONITEMPACKAGES))
                or
                (AUCTIONITEM.TYPECODE = 0 and AUCTIONITEM.PACKAGEID is not null and AUCTIONITEM.PACKAGEID not in (select ID from @AUCTIONITEMPACKAGES))
            )

          -- Make sure there are actually auction packages in the applications

          if (select count(*) from @AUCTIONITEMPACKAGES) < 1
            return;

          declare @PACKAGEID uniqueidentifier;
          declare @ORIGINALPACKAGEVALUE money;
          declare @PACKAGEPURCHASEPRICE money;
          declare @APPLICATIONXML xml;
          declare PACKAGECURSOR cursor local fast_forward
          for select ID,APPLIED,ORIGINALVALUE from @AUCTIONITEMPACKAGES

          --This cursor is used to iterate over the auction packages that are in the applications

          --so we can add all items that are in the package correctly to the application collection

          open PACKAGECURSOR
          fetch next from PACKAGECURSOR into @PACKAGEID,@PACKAGEPURCHASEPRICE,@ORIGINALPACKAGEVALUE

          while @@FETCH_STATUS = 0
          begin

            --Keep track of the items in the package, as we will have to re-prorate the amounts

            declare @PACKAGEITEMS table
            (
                REVENUESPLITID uniqueidentifier,
                APPLICATIONID uniqueidentifier,
                REVENUESPLITAPPLICATIONCODE tinyint,
                CURRENTAPPLIED money,
                UPDATEDAMOUNT decimal(30,10),
                ORIGINALVALUE money
            )

            insert into @PACKAGEITEMS(REVENUESPLITID,APPLICATIONID,CURRENTAPPLIED,REVENUESPLITAPPLICATIONCODE,ORIGINALVALUE)
                select REVENUESPLIT.ID, AUCTIONITEM.ID, REVENUESPLIT.AMOUNT, REVENUESPLIT.APPLICATIONCODE, AUCTIONITEM.VALUE
                    from  
                        dbo.AUCTIONITEM
                        inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                        inner join dbo.REVENUESPLIT on AUCTIONITEMPURCHASE.PURCHASEID = REVENUESPLIT.ID
                    where
                        REVENUESPLIT.REVENUEID = @REVENUEID
                        and
                        AUCTIONITEM.PACKAGEID = @PACKAGEID

            --Prorate the splits in case the amount for the package changed

            update @PACKAGEITEMS
              set UPDATEDAMOUNT = ROUND(CAST(ORIGINALVALUE as decimal(30,10))/CAST(@ORIGINALPACKAGEVALUE as decimal(30,10)) * CAST(@PACKAGEPURCHASEPRICE as decimal(30,10)),2)

              declare @PRORATEDPURCHASEAMOUNT money = 0;
              declare @ROUNDINGDIFFERENCE money = 0;
              select @PRORATEDPURCHASEAMOUNT = SUM(UPDATEDAMOUNT) from @PACKAGEITEMS
              set @ROUNDINGDIFFERENCE = @PACKAGEPURCHASEPRICE - @PRORATEDPURCHASEAMOUNT

              --the difference should be very small, if there is one at all

              --add the difference to the largest valued item

              if @ROUNDINGDIFFERENCE <> 0
                update @PACKAGEITEMS
                  set UPDATEDAMOUNT = UPDATEDAMOUNT + @ROUNDINGDIFFERENCE
                where APPLICATIONID = (select top 1 APPLICATIONID from @PACKAGEITEMS where ORIGINALVALUE = (select MAX(ORIGINALVALUE) from @PACKAGEITEMS))


              declare @APPLICATIONID uniqueidentifier;
              declare @REVENUESPLITID uniqueidentifier;
              declare @APPLIED money;
              declare @REVENUESPLITAPPLICATIONCODE tinyint;
              declare ITEMCURSOR cursor local fast_forward
              for select REVENUESPLITID, APPLICATIONID, UPDATEDAMOUNT, REVENUESPLITAPPLICATIONCODE from @PACKAGEITEMS

            --This cursor is used to add each item into the application/revenuestreams collection as its own item

              open ITEMCURSOR
              fetch next from ITEMCURSOR into @REVENUESPLITID, @APPLICATIONID, @APPLIED, @REVENUESPLITAPPLICATIONCODE

              while @@FETCH_STATUS = 0
              begin
                  select @APPLICATIONXML = (select @REVENUESPLITID as ID, @APPLICATIONID as APPLICATIONID,@APPLIED as APPLIED,@REVENUESPLITAPPLICATIONCODE as APPLICATIONCODE
                                              for xml raw(''),type,elements,BINARY BASE64)

                  insert into @APPLICATIONS(ITEM)
                  values(@APPLICATIONXML)

                  fetch next from ITEMCURSOR into @REVENUESPLITID, @APPLICATIONID, @APPLIED, @REVENUESPLITAPPLICATIONCODE
              end

              CLOSE ITEMCURSOR;
            DEALLOCATE ITEMCURSOR;

              fetch next from PACKAGECURSOR into @PACKAGEID,@PACKAGEPURCHASEPRICE,@ORIGINALPACKAGEVALUE

          end              

            CLOSE PACKAGECURSOR;
            DEALLOCATE PACKAGECURSOR;  

            --delete the auction packages from the applications that we started with

            delete @APPLICATIONS
            where APPLICATIONID in (select ID from @AUCTIONITEMPACKAGES)


            --update the revenue streams collection

            select @REVENUESTREAMS = (select ITEM
                                                      from @APPLICATIONS
                                                      for xml raw(''),type,elements,root('REVENUESTREAMS'),BINARY BASE64)

      end