USP_DATAFORMTEMPLATE_ADD_RESEARCHGROUPASSIGNPROSPECTMANAGER

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RESEARCHGROUPASSIGNPROSPECTMANAGER
          (
            @ID uniqueidentifier = null output,
            @CHANGEAGENTID uniqueidentifier = null,
            @PROSPECTMANAGERFUNDRAISERID uniqueidentifier = null,
            @OVERWRITEEXISTINGPROSPECTMANAGER bit,
            @PREVIOUSMANAGERENDDATE date = null,
            @NEWMANAGERSTARTDATE date = null,
            @RESEARCHGROUPMEMBERS xml = null
          ) 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
                );

                if @OVERWRITEEXISTINGPROSPECTMANAGER = 1
                begin
                  insert into @CURRENTPROSPECTMANAGERS
                  (
                    PROSPECTID,
                    CURRENTMANAGER,
                    MANAGERSTARTDATE
                  )
                  select
                    [PROSPECT].ID,
                    [PROSPECT].[PROSPECTMANAGERFUNDRAISERID],
                    [PROSPECT].[PROSPECTMANAGERSTARTDATE]
                  from dbo.[PROSPECT]
                    inner join dbo.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS on MEMBERS.CONSTITUENTID = [PROSPECT].[ID]
                  where [PROSPECT].[PROSPECTMANAGERFUNDRAISERID] is not null;

                  update dbo.[PROSPECT]
                  set
                    [PROSPECTMANAGERFUNDRAISERID] = @PROSPECTMANAGERFUNDRAISERID,
                    [PROSPECTMANAGERSTARTDATE] = @NEWMANAGERSTARTDATE,
                    [PROSPECTMANAGERENDDATE] = null,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                  from dbo.[PROSPECT] P
                    inner join dbo.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS on MEMBERS.[CONSTITUENTID] = P.[ID];
                end
                else
                begin
                  update dbo.[PROSPECT]
                  set
                    [PROSPECTMANAGERFUNDRAISERID] = @PROSPECTMANAGERFUNDRAISERID,
                    [PROSPECTMANAGERSTARTDATE] = @NEWMANAGERSTARTDATE,
                    [PROSPECTMANAGERENDDATE] = null,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                  from dbo.[PROSPECT] P
                    inner join dbo.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS on MEMBERS.[CONSTITUENTID] = P.[ID]
                  where P.[PROSPECTMANAGERFUNDRAISERID] is null;
                end

                insert into dbo.[PROSPECT]
                (
                  [ID],
                  [PROSPECTMANAGERFUNDRAISERID],
                  [PROSPECTMANAGERSTARTDATE],
         [ADDEDBYID],
                  [CHANGEDBYID],
                  [DATEADDED],
                  [DATECHANGED]
                )
                select
                  MEMBERS.[CONSTITUENTID],
                  @PROSPECTMANAGERFUNDRAISERID,
                  @NEWMANAGERSTARTDATE,
                  @CHANGEAGENTID,
                  @CHANGEAGENTID,
                  @CURRENTDATE,
                  @CURRENTDATE
                from dbo.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS
                  left join dbo.[PROSPECT] P on P.[ID] = MEMBERS.[CONSTITUENTID]
                where P.[ID] is null;

                if @OVERWRITEEXISTINGPROSPECTMANAGER = 1
                begin
                  --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];
                end

                if @PROSPECTMANAGERFUNDRAISERID is not null
                begin
                  insert into dbo.PROSPECTDATERANGE
                  (
                    CONSTITUENTID,
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED
                  ) 
                  select
                    MEMBERS.[CONSTITUENTID],
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                  from dbo.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS
                    left join dbo.[PROSPECTDATERANGE] PDR on PDR.[CONSTITUENTID] = MEMBERS.[CONSTITUENTID]
                  where PDR.[ID] is null;
                end

                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.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS on MEMBERS.[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;
              end try
              begin catch
                exec dbo.USP_RAISE_ERROR
                return 1
              end catch
            end
            else
            begin
              if @OVERWRITEEXISTINGPROSPECTMANAGER = 1
              begin
                update dbo.[PROSPECT]
                set
                  [PROSPECTMANAGERFUNDRAISERID] = @PROSPECTMANAGERFUNDRAISERID,
                  [CHANGEDBYID] = @CHANGEAGENTID,
                  [DATECHANGED] = @CURRENTDATE
                from dbo.[PROSPECT] P
                  inner join dbo.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS on MEMBERS.[CONSTITUENTID] = P.[ID];
              end
              else
              begin
                update dbo.[PROSPECT]
                set
                  [PROSPECTMANAGERFUNDRAISERID] = @PROSPECTMANAGERFUNDRAISERID,
                  [CHANGEDBYID] = @CHANGEAGENTID,
                  [DATECHANGED] = @CURRENTDATE
                from dbo.[PROSPECT] P
                  inner join dbo.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS on MEMBERS.[CONSTITUENTID] = P.[ID]
                where P.[PROSPECTMANAGERFUNDRAISERID] is null;
              end

              insert into dbo.[PROSPECT]
              (
                [ID],
                [PROSPECTMANAGERFUNDRAISERID],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED]
              )
              select
                MEMBERS.[CONSTITUENTID],
                @PROSPECTMANAGERFUNDRAISERID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
              from dbo.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS
                left join dbo.[PROSPECT] P on P.[ID] = MEMBERS.[CONSTITUENTID]
              where P.[ID] is null;

              if @PROSPECTMANAGERFUNDRAISERID is not null
              begin
                insert into dbo.PROSPECTDATERANGE
                (
                  CONSTITUENTID,
                  ADDEDBYID,
                  CHANGEDBYID,
                  DATEADDED,
                  DATECHANGED
                ) 
                select
                  MEMBERS.[CONSTITUENTID],
                  @CHANGEAGENTID,
                  @CHANGEAGENTID,
                  @CURRENTDATE,
                  @CURRENTDATE
                from dbo.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS
                  left join dbo.[PROSPECTDATERANGE] PDR on PDR.[CONSTITUENTID] = MEMBERS.[CONSTITUENTID]
                where PDR.[ID] is null;
              end
            end

            select top 1 @ID = CONSTITUENTID
            from UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS);

            return 0;