USP_CONSTITUENTRECOGNITION_GETDECLINEDLEVELS_UPDATEFROMXML

      CREATE procedure dbo.USP_CONSTITUENTRECOGNITION_GETDECLINEDLEVELS_UPDATEFROMXML
      (
      @CONSTITUENTID uniqueidentifier,
      @RECOGNITIONPROGRAMID uniqueidentifier,
      @XML xml,
      @CHANGEAGENTID uniqueidentifier = null,
      @CHANGEDATE datetime = null,
      @REASONCODE uniqueidentifier = null,
      @RECOGNITIONLEVELID uniqueidentifier = null,
      @CHECKFORDECLINEDPROGRAM bit = 0
      )

      as

      set nocount on;

      if @CHANGEAGENTID is null
          exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

      if @CHANGEDATE is null 
          set @CHANGEDATE = getdate()


       declare @xmlTable table ([DECLINED] bit, 
                                [DECLINEDLEVELID] uniqueidentifier,
                                [RECOGNITIONLEVELID] uniqueidentifier)

      insert into @xmlTable SELECT T.c.value('(DECLINED)[1]','bit') AS 'DECLINED',
            T.c.value('(DECLINEDLEVELID)[1]','uniqueidentifier') AS 'DECLINEDLEVELID',
            T.c.value('(RECOGNITIONLEVELID)[1]','uniqueidentifier') as RECOGNITIONLEVELID
            FROM @XML.nodes('/DECLINEDRECOGNITIONLEVELS/ITEM') T(c)

      -- build a temporary table containing the values from the XML

      declare @TempTbl table ([DECLINED] bit, 
                                [DECLINEDLEVELID] uniqueidentifier,
                                [RECOGNITIONLEVELID] uniqueidentifier)


      insert into @TempTbl select 
          [DECLINED],
          [DECLINEDLEVELID],
          [RECOGNITIONLEVELID]
      from @xmlTable

      update @TempTbl set DECLINEDLEVELID = newid() where (DECLINEDLEVELID is null) or (DECLINEDLEVELID = '00000000-0000-0000-0000-000000000000');

      if (@RECOGNITIONLEVELID is not null)
      begin

        if exists(select RECOGNITIONLEVELID from @TempTbl TT where TT.DECLINED = 1 and TT.RECOGNITIONLEVELID = @RECOGNITIONLEVELID)
        begin
          raiserror('BBERR_RECOGNITIONLEVELISDECLINED',13,1);
        end

      end

      if (@CHECKFORDECLINEDPROGRAM = 1)
      begin

        if exists(select RECOGNITIONPROGRAMID from dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM where RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID and CONSTITUENTID = @CONSTITUENTID)
        begin
          raiserror('BBERR_RECOGNITIONPROGRAMISDECLINED',13,1);
        end

      end


      if @@Error <> 0
          return 1;

      declare @contextCache varbinary(128);
      declare @e int;

      -- cache current context information 

      set @contextCache = CONTEXT_INFO();

      -- set CONTEXT_INFO to @CHANGEAGENTID 

      if not @CHANGEAGENTID is null
          set CONTEXT_INFO @CHANGEAGENTID;

      -- delete any items that are no longer declined

      delete from dbo.[CONSTITUENTRECOGNITIONDECLINEDLEVEL] where [CONSTITUENTRECOGNITIONDECLINEDLEVEL].ID in 
          (select DECLINEDLEVELID from dbo.UFN_CONSTITUENTRECOGNITION_GETDECLINEDLEVELS
          (
            @CONSTITUENTID,
          @RECOGNITIONPROGRAMID
          )

          intersect select DECLINEDLEVELID from @TempTbl where DECLINED = 0)    

      select @e=@@error;

      -- reset CONTEXT_INFO to previous value 

      if not @contextCache is null
          set CONTEXT_INFO @contextCache;

      if @e <> 0
          return 2;

      -- insert new items

      insert into [CONSTITUENTRECOGNITIONDECLINEDLEVEL] 
          ([CONSTITUENTID], 
          [ID],
          [RECOGNITIONLEVELID],    
          [RECOGNITIONPROGRAMID],
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED,
        REASONCODEID)
      select @CONSTITUENTID, 
          [DECLINEDLEVELID],
          [RECOGNITIONLEVELID], 
          @RECOGNITIONPROGRAMID,
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          @CHANGEDATE, 
          @CHANGEDATE,
        @REASONCODE
      from @TempTbl as [temp]
      where [temp].declined = 1 and not exists (select ID from dbo.[CONSTITUENTRECOGNITIONDECLINEDLEVEL] as data where data.ID = [temp].DECLINEDLEVELID)


      update dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL set REASONCODEID=@REASONCODE where CONSTITUENTID = @CONSTITUENTID and RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID

      if @@Error <> 0
          return 4;

     --Handle the scenario of declining a level a constituent is already in and is currently active. We will drop them down to the next "lower" level. Lower means 

     -- the minimum amount to enter the level is <= the level they are leaving. 


      --First store which RecognitionLevels are being declined that are currently active and the constituent is a member of

      declare @DECLINEDOWNEDLEVELS table (CONSTITUENTRECOGNITIONID uniqueidentifier, MINAMOUNT money)
      insert into @DECLINEDOWNEDLEVELS 
      select CR.ID,RL.AMOUNT from dbo.CONSTITUENTRECOGNITION CR
      inner join dbo.RECOGNITIONLEVEL RL on RL.ID = CR.RECOGNITIONLEVELID
      inner join @TempTbl CRDL on CR.RECOGNITIONLEVELID = CRDL.RECOGNITIONLEVELID and CRDL.DECLINED = 1
      where CR.CONSTITUENTID = @CONSTITUENTID 
      and CR.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID 
      and CR.STATUSCODE = 0



      --Grab and store the highest amount related data from CONSTITUENTRECOGNITION for the new level to have

       declare @CURRENTACTIVELEVELID uniqueidentifier = (select top 1 ID from dbo.CONSTITUENTRECOGNITION where RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID and CONSTITUENTID = @CONSTITUENTID order by JOINDATE DESC)
       declare @TOTALAMOUNT money
       declare @TOTALPLANNEDGIFTAMOUNT money
       declare @ORGANIZATIONTOTALAMOUNT money
       declare @ORGANIZATIONTOTALPLANNEDGIFTAMOUNT money
       declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier
       declare @BASECURRENCYID uniqueidentifier
       select top 1 
       @TOTALAMOUNT=TOTALAMOUNT,
       @TOTALPLANNEDGIFTAMOUNT=TOTALPLANNEDGIFTAMOUNT,
       @ORGANIZATIONTOTALAMOUNT=ORGANIZATIONTOTALAMOUNT,
       @ORGANIZATIONTOTALPLANNEDGIFTAMOUNT=ORGANIZATIONTOTALPLANNEDGIFTAMOUNT,
       @ORGANIZATIONEXCHANGERATEID=ORGANIZATIONEXCHANGERATEID,
       @BASECURRENCYID=BASECURRENCYID
       from dbo.CONSTITUENTRECOGNITION
       where RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID and CONSTITUENTID = @CONSTITUENTID
       order by CONSTITUENTRECOGNITION.JOINDATE DESC

         delete from dbo.CONSTITUENTRECOGNITION 
                        where ID in (select CONSTITUENTRECOGNITIONID from @DECLINEDOWNEDLEVELS)

         delete from dbo.CONSTITUENTRECOGNITIONREVENUE
          where RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
          and REVENUERECOGNITIONID in(select ID
                                      from dbo.REVENUERECOGNITION
                                      where CONSTITUENTID = @CONSTITUENTID);


     --Next attempt to find any levels lower than the level(s) they're leaving that aren't declined and place them into that level.


      if @CURRENTACTIVELEVELID in (select CONSTITUENTRECOGNITIONID from @DECLINEDOWNEDLEVELS)
      begin


      declare @MAXPREVIOUSAMOUNT money = (select max(MINAMOUNT) from @DECLINEDOWNEDLEVELS)
      declare @EXISTINGRECOGNITIONID uniqueidentifier
      declare @NEWRECOGNITIONLEVELID uniqueidentifier = (select top 1 RL.ID from dbo.RECOGNITIONLEVEL RL
                                                         where RL.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID and RL.AMOUNT <= @MAXPREVIOUSAMOUNT
                                                         and RL.ID not in (select CRDL.RECOGNITIONLEVELID from @TempTbl CRDL where RL.ID = CRDL.RECOGNITIONLEVELID and CRDL.DECLINED = 1)
                                                         and RL.ID not in (select CR.RECOGNITIONLEVELID from dbo.CONSTITUENTRECOGNITION CR where CR.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID and CR.CONSTITUENTID = @CONSTITUENTID)
                                                         order by RL.AMOUNT desc )

      --If they're already in an active level, don't re-add just update the totals.

      set @EXISTINGRECOGNITIONID = (select top 1 CR.ID from dbo.CONSTITUENTRECOGNITION CR 
                                         where CR.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID and CR.CONSTITUENTID = @CONSTITUENTID and CR.STATUSCODE = 0
                                         order by CR.JOINDATE DESC)

        if @EXISTINGRECOGNITIONID is not null and  @NEWRECOGNITIONLEVELID  is not null
        begin
          update dbo.CONSTITUENTRECOGNITION set 
          TOTALAMOUNT=@TOTALAMOUNT,
          TOTALPLANNEDGIFTAMOUNT=@TOTALPLANNEDGIFTAMOUNT,
          ORGANIZATIONTOTALAMOUNT= @ORGANIZATIONTOTALAMOUNT,
          ORGANIZATIONTOTALPLANNEDGIFTAMOUNT=@ORGANIZATIONTOTALPLANNEDGIFTAMOUNT,
          ORGANIZATIONEXCHANGERATEID=@ORGANIZATIONEXCHANGERATEID ,
          BASECURRENCYID=@BASECURRENCYID
          where ID = @EXISTINGRECOGNITIONID
        end

        if @NEWRECOGNITIONLEVELID is not null and @EXISTINGRECOGNITIONID is null
        begin
          declare @NEWID uniqueidentifier = newid()
          exec USP_DATAFORMTEMPLATE_ADD_CONSTITUENTRECOGNITION @NEWID,@CHANGEAGENTID,@CONSTITUENTID,@RECOGNITIONPROGRAMID,@NEWRECOGNITIONLEVELID,0,@CHANGEDATE,'',0,null
          update dbo.CONSTITUENTRECOGNITION set
          TOTALAMOUNT=@TOTALAMOUNT,
          TOTALPLANNEDGIFTAMOUNT=@TOTALPLANNEDGIFTAMOUNT,
          ORGANIZATIONTOTALAMOUNT= @ORGANIZATIONTOTALAMOUNT,
          ORGANIZATIONTOTALPLANNEDGIFTAMOUNT=@ORGANIZATIONTOTALPLANNEDGIFTAMOUNT,
          ORGANIZATIONEXCHANGERATEID=@ORGANIZATIONEXCHANGERATEID ,
          BASECURRENCYID=@BASECURRENCYID
          where ID=@NEWID 
        end
     end

     --If the program no longer has any recognition, delete the revenue

     if not exists ( select ID from dbo.CONSTITUENTRECOGNITION CR where CR.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID and CR.CONSTITUENTID = @CONSTITUENTID)
     begin
       delete from dbo.CONSTITUENTRECOGNITIONREVENUE
            where RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
            and REVENUERECOGNITIONID in(select ID
                                        from dbo.REVENUERECOGNITION
                                        where CONSTITUENTID = @CONSTITUENTID);
     end
     if @@Error <> 0
      return 5;

      return 0;