USP_DATAFORMTEMPLATE_PROSPECTPLANSECONDARYMANAGER_EDITSAVE

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_PROSPECTPLANSECONDARYMANAGER_EDITSAVE 
          (
            @ID uniqueidentifier,
            @SECONDARYMANAGERID uniqueidentifier,
            @SECONDARYMANAGERSTARTDATE datetime,
            @SECONDARYMANAGERENDDATE 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                

              declare @PREVIOUSSECONDARYMANAGERFUNDRAISERID uniqueidentifier, @PREVIOUSSECONDARYMANAGERENDDATE datetime, @PREVIOUSSECONDARYMANAGERSTARTDATE datetime, @PREVIOUSMANAGERHISTORYID uniqueidentifier;

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

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

              if @SECONDARYMANAGERID is not null
              begin
                --Bug#244263 - Arun Saini - Update latest history record enddate if new manager startdate is greater than that
                -- Get previous manager history so that we can update its end date if its greater than new manager start date
                select top 1 
                  @PREVIOUSMANAGERHISTORYID = ID
                from PROSPECTPLANMANAGERHISTORY 
                where 
                  PROSPECTPLANID  =  @ID 
                  and ISPRIMARYMANAGER = 0
                order by 
                  DATETO desc,
                  DATECHANGED desc

                -- Update history record end date if that is greater than new manager start date
                if(@PREVIOUSMANAGERHISTORYID is not null)
                  update PROSPECTPLANMANAGERHISTORY
                  set DATETO = @SECONDARYMANAGERSTARTDATE 
                  where 
                    ID = @PREVIOUSMANAGERHISTORYID 
                    and DATETO > @SECONDARYMANAGERSTARTDATE

                select 
                  @PREVIOUSSECONDARYMANAGERFUNDRAISERID = SECONDARYMANAGERFUNDRAISERID,
                  @PREVIOUSSECONDARYMANAGERENDDATE = SECONDARYMANAGERENDDATE,
                  @PREVIOUSSECONDARYMANAGERSTARTDATE = SECONDARYMANAGERSTARTDATE
                from dbo.PROSPECTPLAN
                where ID = @ID;

                update 
                  dbo.PROSPECTPLAN
                set 
                  SECONDARYMANAGERFUNDRAISERID = @SECONDARYMANAGERID,
                  SECONDARYMANAGERSTARTDATE = @SECONDARYMANAGERSTARTDATE,
                  SECONDARYMANAGERENDDATE = @SECONDARYMANAGERENDDATE,
                  DATECHANGED  = @CURRENTDATE,
                  CHANGEDBYID = @CHANGEAGENTID
                where 
                  ID = @ID

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

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

                --Create history for previous manager. 
                --This is required only when previous manager end date was not specified and previous manager start date was not a future date
                if(@PREVIOUSSECONDARYMANAGERFUNDRAISERID <> @SECONDARYMANAGERID 
                  and @PREVIOUSSECONDARYMANAGERENDDATE is null 
                  and ( @PREVIOUSSECONDARYMANAGERSTARTDATE is null or dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSSECONDARYMANAGERSTARTDATE) <= dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)))
                begin

                  --Calculate previous manager history end date
                  declare @HISTORYENDDATE as datetime = case
                          when @SECONDARYMANAGERSTARTDATE is not null  -- new manager start date is provided
                            then @SECONDARYMANAGERSTARTDATE            -- set it to new manager start date
                          when @SECONDARYMANAGERENDDATE < dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)   -- New manager end date is a past date
                            then @SECONDARYMANAGERENDDATE                                              -- Set it to new manager end date
                          else                                          --For all other cases
                            dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)  -- set it to current date
                          end

                  --Calculate previous manager history start date
                  declare @HISTORYSTARTDATE as datetime = case
                          -- A record already exists in history table and previous manager start date  is not provided
                          when (@MANAGERHISTORYID is not null and @PREVIOUSSECONDARYMANAGERSTARTDATE is null)  
                            then @HISTORYENDDATE  -- We need to set the startdate same as enddate
                          else 
                            @PREVIOUSSECONDARYMANAGERSTARTDATE 
                          end

                  insert into dbo.PROSPECTPLANMANAGERHISTORY(ID, PROSPECTPLANID, FUNDRAISERID,ISPRIMARYMANAGER, DATEFROM, DATETO, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                  values(newid(), @ID, @PREVIOUSSECONDARYMANAGERFUNDRAISERID,0, @HISTORYSTARTDATE, @HISTORYENDDATE, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)

                end     

                if(@SECONDARYMANAGERID is not null and @MANAGERHISTORYID is not null and @SECONDARYMANAGERENDDATE is null and @HISTORYFUNDRAISERID = @SECONDARYMANAGERID and dbo.UFN_DATE_GETEARLIESTTIME(@SECONDARYMANAGERSTARTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@HISTORYDATETO))
                begin
                  -- if history is already present for this manager and user has cleared the end date delete history record
                  delete 
                  from PROSPECTPLANMANAGERHISTORY 
                  where ID = @MANAGERHISTORYID

                  --update the prospect with the history's start date to make it a continuous active record
                  update dbo.PROSPECTPLAN
                  set SECONDARYMANAGERSTARTDATE = @HISTORYDATEFROM
                  where ID = @ID
                end     
                else if( @HISTORYFUNDRAISERID = @SECONDARYMANAGERID and dbo.UFN_DATE_GETEARLIESTTIME(@SECONDARYMANAGERSTARTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@HISTORYDATETO) and
                    @SECONDARYMANAGERID is not null and @MANAGERHISTORYID is not null and 
                    ((@PREVIOUSSECONDARYMANAGERSTARTDATE is null and  @SECONDARYMANAGERSTARTDATE is not null) or
                    (@PREVIOUSSECONDARYMANAGERSTARTDATE is not null and  @SECONDARYMANAGERSTARTDATE is null) or
                    dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSSECONDARYMANAGERENDDATE) <> dbo.UFN_DATE_GETEARLIESTTIME(@SECONDARYMANAGERENDDATE) or
                    dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSSECONDARYMANAGERSTARTDATE) <> dbo.UFN_DATE_GETEARLIESTTIME(@SECONDARYMANAGERSTARTDATE))
                    )
                --if history is already present for this manager and end date or start date has changed and the start date overlaps with the history's end date then update the history . we don't require endates to check for null as no update is required for those
                begin
                  update PROSPECTPLANMANAGERHISTORY
                  set
                    DATEFROM = case when @SECONDARYMANAGERSTARTDATE < DATEFROM then @SECONDARYMANAGERSTARTDATE end,
                    DATETO = @SECONDARYMANAGERENDDATE,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                  where ID = @MANAGERHISTORYID
                end  
                else if( @SECONDARYMANAGERID is not null and (@MANAGERHISTORYID is null or  @HISTORYFUNDRAISERID <> @SECONDARYMANAGERID) and @SECONDARYMANAGERENDDATE is not null)  
                begin

                  --Calculate new manager history start date
                  declare @NEWMANAGERHISTORYSTARTDATE as datetime = case
                            -- A record already exists in history table and new manager start date  is not provided
                            when (exists (select 1 from PROSPECTPLANMANAGERHISTORY where PROSPECTPLANID =  @ID and ISPRIMARYMANAGER = 0) and @SECONDARYMANAGERSTARTDATE is null and @SECONDARYMANAGERENDDATE >=  dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)  )  
                              then dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)  -- We need to set the startdate same as currentdate
                            when (exists (select 1 from PROSPECTPLANMANAGERHISTORY where PROSPECTPLANID =  @ID and ISPRIMARYMANAGER = 0) and @SECONDARYMANAGERSTARTDATE is null and @SECONDARYMANAGERENDDATE <  dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)  )  
                              then @SECONDARYMANAGERENDDATE
                            else
                              @SECONDARYMANAGERSTARTDATE 
                            end

                  -- create history if history is not already present and end date is supplied
                  insert into dbo.PROSPECTPLANMANAGERHISTORY(ID, PROSPECTPLANID, FUNDRAISERID,ISPRIMARYMANAGER, DATEFROM, DATETO, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                  values(newid(), @ID, @SECONDARYMANAGERID,0, @NEWMANAGERHISTORYSTARTDATE, @SECONDARYMANAGERENDDATE, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
                end 
              end    
              else
              begin
                update 
                  dbo.PROSPECTPLAN
                set 
                  SECONDARYMANAGERFUNDRAISERID = null,
                  SECONDARYMANAGERSTARTDATE = null,
                  SECONDARYMANAGERENDDATE = null,
                  DATECHANGED  = @CURRENTDATE,
                  CHANGEDBYID = @CHANGEAGENTID
                where 
                  ID = @ID
              end

              if (@PREVIOUSSECONDARYMANAGERFUNDRAISERID <> @SECONDARYMANAGERID) or (@SECONDARYMANAGERID is null)
                  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 = 1

              declare @PREVIOUSPRIMARYFUNDRAISERID uniqueidentifier;
              declare @PREVIOUSSECONDARYFUNDRAISERID uniqueidentifier; 

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

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

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

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

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

            return 0;