USP_DATAFORMTEMPLATE_EDIT_RESEARCHGROUPFUNDRAISERASSIGNMENT_2

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RESEARCHGROUPFUNDRAISERASSIGNMENT_2 (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @PROSPECTMANAGERFUNDRAISERID uniqueidentifier,
                        @OVERWRITEEXISTINGPROSPECTMANAGER bit,
                        @PREVIOUSMANAGERENDDATE date,
                        @NEWMANAGERSTARTDATE date
                    ) as
                        set nocount on;

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

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

                        --For Enterprise and Advanced Development product flags, update Prospect Manager History and Interactions
                        if exists(Select top 1 1 from INSTALLEDPRODUCTLIST where ID in ('3117D2C8-7F46-42F2-ABEB-B654F2F63046','a84df439-5a75-4a3b-b3f5-b47035ddd3ff'))
                        begin

                          begin try

                              --JamesWill WI185347 2012-01-26 In order to make the constraints work out, we have to update the PROSPECT table 
                              --before inserting into the PROSPECTMANAGERHISTORY table. In order to do this, we have to keep track of what the previous prospect 
                              --managers looked like before we updated them with the new current values. 
                              declare @CURRENTPROSPECTMANAGERS table 
                              (
                                  PROSPECTID uniqueidentifier,
                                  CURRENTMANAGER uniqueidentifier,
                                  MANAGERSTARTDATE datetime
                              );

                              insert into @CURRENTPROSPECTMANAGERS(PROSPECTID, CURRENTMANAGER, MANAGERSTARTDATE)
                                  select 
                                      [PROSPECT].ID,
                                      [PROSPECT].[PROSPECTMANAGERFUNDRAISERID],
                                      [PROSPECT].[PROSPECTMANAGERSTARTDATE]
                                  from 
                                      dbo.[PROSPECT]
                                  inner join 
                                      dbo.[RESEARCHGROUPMEMBER] RGM on RGM.CONSTITUENTID = [PROSPECT].[ID]
                                  where RGM.[RESEARCHGROUPID] = @ID and
                                  [PROSPECT].[PROSPECTMANAGERFUNDRAISERID] is not null and 
                                  @OVERWRITEEXISTINGPROSPECTMANAGER = 1;


                              update
                                  dbo.[PROSPECT]
                              set
                                  [PROSPECTMANAGERFUNDRAISERID] = @PROSPECTMANAGERFUNDRAISERID,
                                  [PROSPECTMANAGERSTARTDATE] = @NEWMANAGERSTARTDATE,
                                  [PROSPECTMANAGERENDDATE] = null,
                                  [CHANGEDBYID] = @CHANGEAGENTID,
                                  [DATECHANGED] = @CURRENTDATE
                              from
                                  dbo.[PROSPECT] P
                              inner join
                                  dbo.[RESEARCHGROUPMEMBER] RGM on RGM.[CONSTITUENTID] = P.[ID]
                              where
                                  RGM.[RESEARCHGROUPID] = @ID and
                                  (P.[PROSPECTMANAGERFUNDRAISERID] is null or @OVERWRITEEXISTINGPROSPECTMANAGER = 1);

                              insert into dbo.[PROSPECT] (
                                  [ID],
                                  [PROSPECTMANAGERFUNDRAISERID],
                                  [ADDEDBYID],
                                  [CHANGEDBYID],
                                  [DATEADDED],
                                  [DATECHANGED]
                              )
                              select
                                  RGM.[CONSTITUENTID],
                                  @PROSPECTMANAGERFUNDRAISERID,
                                  @CHANGEAGENTID,
                                  @CHANGEAGENTID,
                                  @CURRENTDATE,
                                  @CURRENTDATE
                              from
                                  dbo.[RESEARCHGROUPMEMBER] RGM
                              left join
                                  dbo.[PROSPECT] P on P.[ID] = RGM.[CONSTITUENTID]
                              where
                                  RGM.[RESEARCHGROUPID] = @ID and
                                  P.[ID] is null;

                              --JamesWill WI185347 2012-01-09 When overwriting existing prospect managers, update the prospect manager history with the change
                              insert into dbo.[PROSPECTMANAGERHISTORY] (
                                  [ID],
                                  [PROSPECTID],
                                  [FUNDRAISERID],
                                  [DATEFROM],
                                  [DATETO],
                                  [DATEADDED],
                                  [DATECHANGED],
                                  [ADDEDBYID],
                                  [CHANGEDBYID]
                              )
                              select
                                  newid(),
                                  [CURRENT].[PROSPECTID],
                                  [CURRENT].[CURRENTMANAGER],
                                  [CURRENT].[MANAGERSTARTDATE],
                                  @PREVIOUSMANAGERENDDATE,
                                  @CURRENTDATE,
                                  @CURRENTDATE,
                                  @CHANGEAGENTID,
                                  @CHANGEAGENTID
                              from 
                                  @CURRENTPROSPECTMANAGERS [CURRENT]
                              where @OVERWRITEEXISTINGPROSPECTMANAGER = 1; --Because @CURRENTPROSPECTMANAGERS uses this when it gets its data, the where clause is redundant. But it makes it clearer what's happening.



                              update
                                  dbo.[INTERACTION]
                              set
                                  [FUNDRAISERID] = @PROSPECTMANAGERFUNDRAISERID,
                                  [CHANGEDBYID] = @CHANGEAGENTID,
                                  [DATECHANGED] = @CURRENTDATE
                              from 
                                  dbo.[INTERACTION] I
                              inner join
                                  dbo.[PROSPECTPLAN] PP on PP.[ID] = I.[PROSPECTPLANID]
                              inner join
                                  dbo.[PROSPECT] P on P.[ID] = PP.[PROSPECTID]
                              inner join
                                  dbo.[RESEARCHGROUPMEMBER] RGM on RGM.[CONSTITUENTID] = P.[ID]
                              left outer join
                                  dbo.[PLANOUTLINESTEP] SI on SI.[ID] = I.[PLANOUTLINESTEPID]
                              where
                                  I.[FUNDRAISERID] is null and
                                  P.[PROSPECTMANAGERFUNDRAISERID] = @PROSPECTMANAGERFUNDRAISERID and
                                  SI.[FUNDRAISERROLECODE] = 0 and
                                  RGM.[RESEARCHGROUPID] = @ID;
                          end try
                          begin catch
                              exec dbo.USP_RAISE_ERROR
                              return 1
                          end catch

                        end else begin
                            update
                                dbo.[PROSPECT]
                            set
                                [PROSPECTMANAGERFUNDRAISERID] = @PROSPECTMANAGERFUNDRAISERID,
                                [CHANGEDBYID] = @CHANGEAGENTID,
                                [DATECHANGED] = @CURRENTDATE
                            from
                                dbo.[PROSPECT] P
                            inner join
                                dbo.[RESEARCHGROUPMEMBER] RGM on RGM.[CONSTITUENTID] = P.[ID]
                            where
                                RGM.[RESEARCHGROUPID] = @ID and
                                (P.[PROSPECTMANAGERFUNDRAISERID] is null or @OVERWRITEEXISTINGPROSPECTMANAGER = 1);

                            insert into dbo.[PROSPECT] (
                                [ID],
                                [PROSPECTMANAGERFUNDRAISERID],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED]
                            )
                            select
                                RGM.[CONSTITUENTID],
                                @PROSPECTMANAGERFUNDRAISERID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            from
                                dbo.[RESEARCHGROUPMEMBER] RGM
                            left join
                                dbo.[PROSPECT] P on P.[ID] = RGM.[CONSTITUENTID]
                            where
                                RGM.[RESEARCHGROUPID] = @ID and
                                P.[ID] is null;
                        end

                    return 0;