USP_DATAFORMTEMPLATE_PROSPECTPLANREPLACEPRIMARYMANAGER_EDITSAVE

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_PROSPECTPLANREPLACEPRIMARYMANAGER_EDITSAVE 
          (
            @ID uniqueidentifier,
            @PREVIOUSMANAGERID uniqueidentifier,
            @PREVIOUSMANAGERSTARTDATE datetime,
            @PREVIOUSMANAGERENDDATE datetime,
            @NEWMANAGERID uniqueidentifier,
            @NEWMANAGERSTARTDATE datetime,
            @NEWMANAGERENDDATE datetime,
            @CHANGEAGENTID uniqueidentifier = null
          ) as
            set nocount on;

            declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();

            if @CHANGEAGENTID is null
              exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;


            begin try                                                    
              /*
                If replacing primary manager, insert the old record into the history table.
                Also, clear the record out to prevent constraint violations.
              */
              if @PREVIOUSMANAGERID is not null
              begin

                declare @ORIGINALPREVIOUSMANAGERID uniqueidentifier, @ORIGINALPREVIOUSMANAGERSTARTDATE datetime, @ORIGINALPREVIOUSMANAGERENDDATE datetime

                select 
                  @ORIGINALPREVIOUSMANAGERID = PRIMARYMANAGERFUNDRAISERID,
                  @ORIGINALPREVIOUSMANAGERSTARTDATE = PRIMARYMANAGERSTARTDATE,
                  @ORIGINALPREVIOUSMANAGERENDDATE = PRIMARYMANAGERENDDATE
                from dbo.PROSPECTPLAN
                where
                  ID = @ID

                                update 
                  dbo.PROSPECTPLAN
                set 
                  PRIMARYMANAGERFUNDRAISERID = null,
                  PRIMARYMANAGERSTARTDATE = null,
                  PRIMARYMANAGERENDDATE = null,
                  DATECHANGED  = @CURRENTDATE,
                  CHANGEDBYID = @CHANGEAGENTID
                where 
                  ID = @ID

                                /* cache current context information */
                    declare @contextCache varbinary(128);
                    set @contextCache = CONTEXT_INFO();

                                /* set CONTEXT_INFO to @CHANGEAGENTID */                        
                    set CONTEXT_INFO @CHANGEAGENTID

                                delete 
                                    dbo.OPPORTUNITYSOLICITOR
                                from
                                    dbo.OPPORTUNITYSOLICITOR
                                    inner join dbo.OPPORTUNITY on OPPORTUNITYSOLICITOR.OPPORTUNITYID = OPPORTUNITY.ID
                                    inner join dbo.PROSPECTPLAN on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
                                where
                                    PROSPECTPLAN.ID = @ID
                                    and FUNDRAISERTYPECODE = 0

                                /* reset CONTEXT_INFO to previous value */
                    if not @contextCache is null
                      set CONTEXT_INFO @contextCache

                declare @MANAGERHISTORYID as uniqueidentifier, @HISTORYFUNDRAISERID  as uniqueidentifier, @HISTORYDATETO as datetime;

                select top 1 
                  @MANAGERHISTORYID = ID,
                  @HISTORYFUNDRAISERID = FUNDRAISERID, 
                  @HISTORYDATETO = DATETO
                from 
                  PROSPECTPLANMANAGERHISTORY 
                where 
                  PROSPECTPLANID =  @ID and
                  ISPRIMARYMANAGER = 1
                order by 
                  DATECHANGED desc

                --Insert in history only when such record in not already there or when the dates do not overlap
                if( @MANAGERHISTORYID is null or @HISTORYFUNDRAISERID <> @ORIGINALPREVIOUSMANAGERID or ( @HISTORYFUNDRAISERID = @PREVIOUSMANAGERID and dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSMANAGERSTARTDATE) <> dbo.UFN_DATE_GETEARLIESTTIME(@HISTORYDATETO)))
                begin
                  insert into dbo.PROSPECTPLANMANAGERHISTORY(ID, PROSPECTPLANID, FUNDRAISERID, ISPRIMARYMANAGER, DATEFROM, DATETO, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                  values(newid(), @ID, @PREVIOUSMANAGERID, 1, @PREVIOUSMANAGERSTARTDATE, @PREVIOUSMANAGERENDDATE, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
                end
                -- update the history record if the dates overlap and if it is the same fundraiser
                else if (@HISTORYFUNDRAISERID = @PREVIOUSMANAGERID and
                        (dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSMANAGERSTARTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@HISTORYDATETO) or
                        dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSMANAGERENDDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@HISTORYDATETO))
                )
                  update PROSPECTPLANMANAGERHISTORY
                  set
                    DATEFROM = case when @PREVIOUSMANAGERSTARTDATE > DATEFROM then DATEFROM else @PREVIOUSMANAGERSTARTDATE end, 
                    DATETO = @PREVIOUSMANAGERENDDATE,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                  where  
                    ID = @MANAGERHISTORYID
                end

                if @NEWMANAGERID is not null
                  update 
                    dbo.PROSPECTPLAN
                  set 
                    PRIMARYMANAGERFUNDRAISERID = @NEWMANAGERID,
                    PRIMARYMANAGERSTARTDATE = @NEWMANAGERSTARTDATE,
                    PRIMARYMANAGERENDDATE = @NEWMANAGERENDDATE,
                    DATECHANGED  = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                  where 
                    ID = @ID

                if(@NEWMANAGERENDDATE is not null)
                  insert into dbo.PROSPECTPLANMANAGERHISTORY
                  (
                    ID,
                    PROSPECTPLANID,
                    FUNDRAISERID,
                    ISPRIMARYMANAGER,
                    DATEFROM,
                    DATETO,
                    DATEADDED,
                    DATECHANGED,
                    ADDEDBYID,
                    CHANGEDBYID
                  )
                  values
                  (
                    newid(),
                    @ID,
                    @NEWMANAGERID,1,
                    @NEWMANAGERSTARTDATE,
                    @NEWMANAGERENDDATE,
                    @CURRENTDATE,
                    @CURRENTDATE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID
                  )

                declare @PREVIOUSSECONDARYFUNDRAISERID uniqueidentifier;

                select 
                  @PREVIOUSSECONDARYFUNDRAISERID = SECONDARYMANAGERFUNDRAISERID
                from
                  dbo.PROSPECTPLAN
                where
                  ID = @ID;

                exec dbo.USP_PROSPECTPLANASSIGNEDALERT_SEND @PREVIOUSMANAGERID, @PREVIOUSSECONDARYFUNDRAISERID, @ID;

                update dbo.INTERACTION set
                  FUNDRAISERID = @NEWMANAGERID,
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @CURRENTDATE
                from
                  dbo.PLANOUTLINESTEP SI
                where
                  SI.ID = PLANOUTLINESTEPID
                  and SI.FUNDRAISERROLECODE = 1
                  and FUNDRAISERID is null
                  and PROSPECTPLANID = @ID;        

              end try
              begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
              end catch

            return 0;