USP_DATAFORMTEMPLATE_ADD_STEWARDSHIPPLAN

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_STEWARDSHIPPLAN
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,    
  @CONSTITUENTID uniqueidentifier,
  @MANAGERID uniqueidentifier = null,
  @STEWARDS xml = null,
  @NAME nvarchar(100),
  @SITES xml = null,
  @CURRENTAPPUSERID uniqueidentifier,
  @PLANTYPEID uniqueidentifier = null,
  @PLANSUBTYPEID uniqueidentifier = null,
  @STARTDATE datetime = null,
  @STEPS xml = null,
  @MANAGERSTARTDATE date = null
)
as
set nocount on;

  begin try

    if @SITES is null
    begin 
      if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1 
      begin
        raiserror('BBERR_STEWARDSHIPPLANSITE_SITEID', 13, 1);
        return 1;
      end
    end

    declare @CHANGEDATE datetime = getdate();

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

    if @ID is null
      set @ID = newid();

    insert into dbo.STEWARDSHIPPLAN 
    (
      ID,
      CONSTITUENTID,
      NAME,
      MANAGERID,
      BASECURRENCYID,
      STARTDATE,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED,
      PLANTYPECODEID,
      PLANSUBTYPECODEID
    )
    values
    (
      @ID,
      @CONSTITUENTID,
      @NAME,
      @MANAGERID,
      dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID),
      @STARTDATE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CHANGEDATE,
      @CHANGEDATE,
      @PLANTYPEID,
      @PLANSUBTYPEID
    );    

    if not @MANAGERID is null
        update dbo.STEWARDSHIPPLAN set MANAGERSTARTDATE = @MANAGERSTARTDATE where ID = @ID

    if not @STEWARDS is null
      exec dbo.USP_STEWARDSHIPPLAN_STEWARDS_ADDFROMXML @ID, @STEWARDS, @CHANGEAGENTID, @CHANGEDATE; 

    --If a manager has been selected, add them as plan steward
    if not @MANAGERID is null
      exec dbo.USP_STEWARDSHIPPLANSTEWARD_ADD @ID, @MANAGERID, @CHANGEAGENTID, @CHANGEDATE, @MANAGERSTARTDATE;

    if not @SITES is null
      exec dbo.USP_STEWARDSHIPPLAN_GETSITES_ADDFROMXML @ID, @SITES, @CHANGEAGENTID, @CHANGEDATE;

    set @STEPS=(
      select 
        newID() as ID,
        @ID as PLANID,
        CATEGORYCODEID,
        OBJECTIVE,
        TARGETDATE,
        DATELOCKED,
        null as ACTUALDATE,
        TARGETDATE as STARTDATE,
        ENDDATE,
        CONTACTMETHODCODEID,
        CONSTITUENTID,
        case when ASSOCIATEDPLANS is null then null else ASSOCIATEDPLANS.query('(ASSOCIATEDPLANS/ITEM)') end as ASSOCIATEDPLANS,
        coalesce(TEMPLATE, '') as TEMPLATE,
        RECURSCODE,
        0 as STATUSCODE,
        null as NEXTTARGETDATE,
        TARGETSTARTTIME,
        TARGETENDTIME,
        TIMEZONEENTRYID,
        ISALLDAYEVENT,
        CONTACTPERSONID,
        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
      from
        dbo.UFN_STEWARDSHIPPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML(@STEPS)
      for xml raw('ITEM'), type, elements, root('STEPS'), binary base64
    )

    exec dbo.USP_STEWARDSHIPPLAN_STEPSWITHCHILDREN_ADDFROMXML @ID, @STEPS, @CHANGEAGENTID, @CHANGEDATE;        
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;