USP_STEWARDSHIPPLAN_STEPSWITHCHILDREN_ADDFROMXML

CREATE procedure dbo.USP_STEWARDSHIPPLAN_STEPSWITHCHILDREN_ADDFROMXML
(
    @STEWARDSHIPPLANID uniqueidentifier,
    @STEPS xml,
    @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();

    insert into dbo.STEWARDSHIPPLANSTEP
        (ID,
        PLANID, 
        ACTUALDATE,
        CATEGORYCODEID,
        CONSTITUENTID,
        CONTACTPERSONID,
        CONTACTMETHODCODEID,
        DATELOCKED,
        ENDDATE,
        OBJECTIVE,
        RECURSCODE,
        STARTDATE,
        STATUSCODE,
        TARGETDATE,
        TEMPLATE,
        TARGETSTARTTIME,
        TARGETENDTIME,
        TIMEZONEENTRYID,
        ISALLDAYEVENT,
        ACTUALSTARTTIME,
        ACTUALENDTIME,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED,
        EVENTID,
        MAILINGID,
        BENEFITID)
    select 
        ID,
        @STEWARDSHIPPLANID, 
        ACTUALDATE,
        CATEGORYCODEID,
        CONSTITUENTID,
        CONTACTPERSONID,
        CONTACTMETHODCODEID,
        DATELOCKED,
        ENDDATE,
        OBJECTIVE,
        RECURSCODE,
        STARTDATE,
        STATUSCODE,
        TARGETDATE,
        TEMPLATE,
        coalesce(TARGETSTARTTIME,''),
        coalesce(TARGETENDTIME,''),
        TIMEZONEENTRYID,        
        case when TARGETSTARTTIME is null then 1 when TARGETSTARTTIME = '' then 1 else 0 end,
        coalesce(ACTUALSTARTTIME,''),
        coalesce(ACTUALENDTIME,''),
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CHANGEDATE, 
        @CHANGEDATE,
        EVENTID,
        MAILINGID,
        BENEFITID
    from dbo.UFN_STEWARDSHIPPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML(@STEPS);

    -- Take care of the associated plans for each step

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

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

    open STEPCURSOR

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

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

        -- Add assigned to constituent as a plan steward, if it doesn't already exist

        if not @CONSTITUENTID is null
            exec dbo.USP_STEWARDSHIPPLANSTEWARD_ADD @STEWARDSHIPPLANID, @CONSTITUENTID, @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;                        

        if not @STEPPARTICIPANTS is null
            exec dbo.USP_STEWARDSHIPPLANSTEP_PARTICIPANTS_UPDATEFROMXML @STEPID, @STEPPARTICIPANTS, @CHANGEAGENTID, @CHANGEDATE;

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

    close STEPCURSOR
    deallocate STEPCURSOR

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