USP_PROSPECTPLAN_STEPSWITHCHILDREN_UPDATEFROMXML

      CREATE procedure dbo.USP_PROSPECTPLAN_STEPSWITHCHILDREN_UPDATEFROMXML
      (
        @PROSPECTPLANID uniqueidentifier,
        @STEPS xml,
        @CHANGEAGENTID uniqueidentifier = null
      )
      as
        set nocount on;

        declare @contextCache varbinary(128);

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

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

        /* set CONTEXT_INFO to @CHANGEAGENTID */
        if not @CHANGEAGENTID is null
          set CONTEXT_INFO @CHANGEAGENTID;

        declare @PROSPECTID uniqueidentifier;
        select @PROSPECTID = PROSPECTID from dbo.PROSPECTPLAN where ID = @PROSPECTPLANID;

        set @STEPS=
        (
          select 
            @PROSPECTID CONSTITUENTID,
            F_STEPS.ACTUALDATE,
            F_STEPS.EXPECTEDDATE,
            F_ID.FUNDRAISERID,
            -- Set the ID to a value known in this SP so the additional fundraisers can be inserted/updated as well

            case
              when F_STEPS.ID is null then newid()
              else F_STEPS.ID
            end ID,
            F_STEPS.OBJECTIVE,
            F_STEPS.PROSPECTPLANSTATUSCODEID,
            coalesce(F_STEPS.STATUSCODE, 0) as STATUSCODE,
            F_STEPS.PLANOUTLINESTEPID,
            F_STEPS.INTERACTIONTYPECODEID,
            F_STEPS.INTERACTIONSUBCATEGORYID,
            -- Have to perform the query so that the xml generated by this query isn't <ADDITIONALFUNDRAISERS><ADDITIONALFUNDRAISERS><ITEM>...

            -- which would then break UFN_PROSPECTPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML the next time it's called

            case
              when F_STEPS.ADDITIONALFUNDRAISERS is null then null
              else F_STEPS.ADDITIONALFUNDRAISERS.query('(ADDITIONALFUNDRAISERS/ITEM)')
            end as ADDITIONALFUNDRAISERS,
            case
              when F_STEPS.PARTICIPANTS is null then null
              else F_STEPS.PARTICIPANTS.query('(PARTICIPANTS/ITEM)')
            end as PARTICIPANTS,
            coalesce(F_STEPS.COMMENT, '') as COMMENT,
            coalesce(F_STEPS.EXPECTEDSTARTTIME,'') as EXPECTEDSTARTTIME,
            coalesce(F_STEPS.EXPECTEDENDTIME, '') as EXPECTEDENDTIME,
            F_STEPS.TIMEZONEENTRYID,
            F_STEPS.ISALLDAYEVENT,
            coalesce(F_STEPS.ACTUALSTARTTIME,'') as ACTUALSTARTTIME,
            coalesce(F_STEPS.ACTUALENDTIME,'') as ACTUALENDTIME,
            case
              -- No location has been specified. Use the existing location on the step.

              when F_STEPS.LOCATIONID is null then coalesce(F_STEPS.LOCATION,'')
              else dbo.UFN_INTERACTION_GETSINGLELINELOCATION(F_STEPS.LOCATIONID, F_STEPS.OTHERLOCATION)
            end as LOCATION,
            F_STEPS.LOCATIONID,
            F_STEPS.OTHERLOCATION
          from dbo.UFN_PROSPECTPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML(@STEPS) F_STEPS
            cross apply dbo.UFN_PROSPECTPLAN_GETFUNDRAISERID_FROM_OWNERID(F_STEPS.FUNDRAISERID , @PROSPECTPLANID) F_ID
          for xml raw('ITEM'), type, elements, root('STEPS'), binary base64 
        );

        -- Clear current additional fundraisers for the steps in @STEPS.  If the values weren't cleared and 

        -- the new owner was in the additional fundraisers table but was removed, a constraint would be violated.

        delete from dbo.INTERACTIONADDITIONALFUNDRAISER
        where INTERACTIONID in (select ID from dbo.UFN_PROSPECTPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML(@STEPS));

        exec dbo.USP_PROSPECTPLAN_STEPS_2_UPDATEFROMXML @PROSPECTPLANID, @STEPS, @CHANGEAGENTID;

        -- Take care of the additional solicitors for each step

        declare @INTERACTIONID uniqueidentifier, @INTERACTIONTYPECODEID uniqueidentifier, @ADDITIONALFUNDRAISERS xml, @PARTICIPANTS xml

        declare STEPCURSOR cursor local fast_forward for 
        select
          ID,
          INTERACTIONTYPECODEID,
          ADDITIONALFUNDRAISERS,
          PARTICIPANTS
        from dbo.UFN_PROSPECTPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML(@STEPS);

        open STEPCURSOR;
        fetch next from STEPCURSOR into @INTERACTIONID, @INTERACTIONTYPECODEID, @ADDITIONALFUNDRAISERS, @PARTICIPANTS;

        while @@FETCH_STATUS = 0
        begin
          exec dbo.USP_INTERACTION_ADDITIONALFUNDRAISERS_UPDATEFROMXML @INTERACTIONID, @ADDITIONALFUNDRAISERS, @CHANGEAGENTID;

          -- Clear participants if the step is no longer an interaction

          if @INTERACTIONTYPECODEID is null
            delete from dbo.INTERACTIONPARTICIPANT where INTERACTIONID = @INTERACTIONID;
          else
            exec dbo.USP_INTERACTION_PARTICIPANTS_UPDATEFROMXML @INTERACTIONID, @PARTICIPANTS, @CHANGEAGENTID;

          fetch next from STEPCURSOR into @INTERACTIONID, @INTERACTIONTYPECODEID, @ADDITIONALFUNDRAISERS, @PARTICIPANTS;
        end

        close STEPCURSOR;
        deallocate STEPCURSOR;

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