USP_STEWARDSHIPPLAN_STEPSWITHCHILDREN_UPDATEFROMXML

CREATE procedure dbo.USP_STEWARDSHIPPLAN_STEPSWITHCHILDREN_UPDATEFROMXML
(
    @STEWARDSHIPPLANID uniqueidentifier,
    @STATUSCODE smallint,
    @STEPS xml,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = 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 @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;                    

    if @CHANGEDATE is null
        set @CHANGEDATE = getdate();

    set @STEPS=(
        select 
            -- Set the ID to a value known in this SP so the additional fundraisers can be inserted/updated as well

            case when ID is null then newid() else ID end as ID,
            case when PLANID is null then @STEWARDSHIPPLANID else PLANID end as PLANID,
            CATEGORYCODEID,
            OBJECTIVE,
            TARGETDATE,
            DATELOCKED,
            ACTUALDATE,
            case when @STATUSCODE = 0 then TARGETDATE else STARTDATE end as STARTDATE,
            ENDDATE,
            CONTACTPERSONID,
            CONTACTMETHODCODEID,
            CONSTITUENTID,
            case when ASSOCIATEDPLANS is null then null else ASSOCIATEDPLANS.query('(ASSOCIATEDPLANS/ITEM)') end as ASSOCIATEDPLANS,
            coalesce(TEMPLATE, '') as TEMPLATE,
            RECURSCODE,
            STATUSCODE,
            TARGETSTARTTIME,
            TARGETENDTIME,
            TIMEZONEENTRYID,
            ISALLDAYEVENT,
            ACTUALSTARTTIME,
            ACTUALENDTIME,
            case when LINKTYPECODE is null or LINKTYPECODE = 0 then BENEFITID else null end as BENEFITID,
            case when LINKTYPECODE is null or LINKTYPECODE = 1 then EVENTID else null end as EVENTID,
            case when LINKTYPECODE is null or LINKTYPECODE = 2 then MAILINGID else null end as MAILINGID,
            case when STEPPARTICIPANTS is null then null else STEPPARTICIPANTS.query('(STEPPARTICIPANTS/ITEM)') end as STEPPARTICIPANTS,
            NEXTTARGETDATE
        from
            dbo.UFN_STEWARDSHIPPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML(@STEPS)
        for xml raw('ITEM'), type, elements, root('STEPS'), binary base64
    )

    exec dbo.USP_STEWARDSHIPPLAN_FILTEREDSTEPS_UPDATEFROMXML @STEWARDSHIPPLANID, @STATUSCODE, @STEPS, @CURRENTAPPUSERID, @CHANGEAGENTID, @CHANGEDATE;

    -- Take care of the associated plans for each step

    declare @STEPID uniqueidentifier;
    declare @PLANID uniqueidentifier;
    declare @CONSTITUENTID uniqueidentifier;
    declare @ASSOCIATEDPLANS xml;
    declare @CONTACTPERSONID uniqueidentifier;
    declare @STEPPARTICIPANTS xml;
    declare @NEXTTARGETDATE datetime;

    declare STEPCURSOR cursor local fast_forward for 
    select
        ID,
        PLANID,
        CONSTITUENTID,
        ASSOCIATEDPLANS,
        CONTACTPERSONID,
        STEPPARTICIPANTS,
        NEXTTARGETDATE,
        STATUSCODE
    from
        dbo.UFN_STEWARDSHIPPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML(@STEPS)

    open STEPCURSOR

    fetch next from STEPCURSOR into @STEPID, @PLANID, @CONSTITUENTID, @ASSOCIATEDPLANS, @CONTACTPERSONID, @STEPPARTICIPANTS, @NEXTTARGETDATE, @STATUSCODE;

    while @@FETCH_STATUS = 0
    begin
        exec dbo.USP_STEWARDSHIPPLANSTEP_ASSOCIATEDPLANS_UPDATEFROMXML @STEPID, null, @ASSOCIATEDPLANS, @CHANGEAGENTID, @CHANGEDATE;

        -- Add assigned to constituent as a plan steward to the plan which owns the step, 

        --        if it doesn't already exist

        if not @CONSTITUENTID is null and not @PLANID is null
            exec dbo.USP_STEWARDSHIPPLANSTEWARD_ADD @PLANID, @CONSTITUENTID, @CHANGEAGENTID, @CHANGEDATE;

        exec dbo.USP_STEWARDSHIPPLANSTEP_PARTICIPANTS_UPDATEFROMXML @STEPID, @STEPPARTICIPANTS, @CHANGEAGENTID, @CHANGEDATE; 

        -- Create pending step for completed steps added that recur

        if @STATUSCODE = 1 and not @NEXTTARGETDATE is null
            exec dbo.USP_STEWARDSHIPPLAN_CLONERECURRINGCOMPLETEDSTEP @STEPID, @NEXTTARGETDATE, @CHANGEAGENTID, @CHANGEDATE;

        fetch next from STEPCURSOR into @STEPID, @PLANID, @CONSTITUENTID, @ASSOCIATEDPLANS, @CONTACTPERSONID, @STEPPARTICIPANTS, @NEXTTARGETDATE, @STATUSCODE;
    end

    close STEPCURSOR
    deallocate STEPCURSOR

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