USP_ORGANIZATIONHIERARCHY_UPDATEFROMXML_2

create procedure dbo.USP_ORGANIZATIONHIERARCHY_UPDATEFROMXML_2
(
  @XML xml,
  @CHANGEAGENTID uniqueidentifier = null,
  @CHANGEDATE datetime = null
)
as
  set nocount on;

  /*This is a customized version of the UPDATEFROMXML to accommodate the same relations structure.
    Be warned that the insert/update/delete order is different
  */

  if @CHANGEAGENTID is null
    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

  declare @IDOC int;
  exec sp_xml_preparedocument @IDOC output, @XML;

  declare @ORGHIERARCHY table
  (
    ID uniqueidentifier,
    PARENTID uniqueidentifier,
    NAME nvarchar(100),
    SITEID uniqueidentifier,
    BUSINESSUNITCODEID uniqueidentifier,
    SEQUENCE int
  );

  insert into
    @ORGHIERARCHY
  select
    ID,
    PARENTID,
    NAME,
    SITEID,
    BUSINESSUNITCODEID,
    SEQUENCE
  from
    openxml(@IDOC, '/ORGANIZATIONHIERARCHY/ITEM')
    with
    (
      ID uniqueidentifier 'ID',
      PARENTID uniqueidentifier 'PARENTID',
      NAME nvarchar(100) 'NAME',
      SITEID uniqueidentifier 'SITEID',
      BUSINESSUNITCODEID uniqueidentifier 'BUSINESSUNITCODEID',
      SEQUENCE int 'SEQUENCE'
    );

  declare @ORGPOSITIONHOLDERS table
  (
    ID uniqueidentifier,
    POSITIONID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    DATEFROM datetime,
    DATETO datetime,
    BUSINESSUNITCODEID uniqueidentifier
  );

  insert into
    @ORGPOSITIONHOLDERS
  select
    ID,
    POSITIONID,
    CONSTITUENTID,
    DATEFROM,
    DATETO,
    BUSINESSUNITCODEID
  from
    openxml(@IDOC, '/ORGANIZATIONHIERARCHY/ITEM/POSITIONHOLDERS/ITEM')
    with
    (
      ID uniqueidentifier 'ID',
      POSITIONID uniqueidentifier '../../ID',
      CONSTITUENTID uniqueidentifier 'CONSTITUENTID',
      DATEFROM datetime 'DATEFROM',
      DATETO datetime 'DATETO',
      BUSINESSUNITCODEID uniqueidentifier 'BUSINESSUNITCODEID'
    );

  exec sp_xml_removedocument @IDOC;

  update
    @ORGHIERARCHY
  set
    ID = newid()
  where
    (ID is null) or
    (ID = '00000000-0000-0000-0000-000000000000');

  if @@Error <> 0
    return 1;

  update
    @ORGPOSITIONHOLDERS
  set
    ID = newid()
  where
    (ID is null) or
    (ID = '00000000-0000-0000-0000-000000000000');

  insert into dbo.ORGANIZATIONHIERARCHY
  (
    ID,
    PARENTID,
    SEQUENCE,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED
  )
  select
    ID,
    PARENTID,
    SEQUENCE,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE
  from
    @ORGHIERARCHY as TEMP
  where
    not exists (select ID from dbo.ORGANIZATIONHIERARCHY as DATA where DATA.ID = TEMP.ID);

  if @@Error <> 0
    return 2;

  update
    ORGANIZATIONHIERARCHY
  set
    ORGANIZATIONHIERARCHY.PARENTID = TEMP.PARENTID,
    ORGANIZATIONHIERARCHY.SEQUENCE = TEMP.SEQUENCE,
    ORGANIZATIONHIERARCHY.CHANGEDBYID = @CHANGEAGENTID,
    ORGANIZATIONHIERARCHY.DATECHANGED = @CHANGEDATE
  from
    dbo.ORGANIZATIONHIERARCHY
  inner join
    @ORGHIERARCHY as TEMP on ORGANIZATIONHIERARCHY.ID = TEMP.ID
  where
    (ORGANIZATIONHIERARCHY.PARENTID <> TEMP.PARENTID) or 
    (ORGANIZATIONHIERARCHY.PARENTID is null and TEMP.PARENTID is not null) or 
    (ORGANIZATIONHIERARCHY.PARENTID is not null and TEMP.PARENTID is null) or 
    (ORGANIZATIONHIERARCHY.SEQUENCE <> TEMP.SEQUENCE) or 
    (ORGANIZATIONHIERARCHY.SEQUENCE is null and TEMP.SEQUENCE is not null) or 
    (ORGANIZATIONHIERARCHY.SEQUENCE is not null and TEMP.SEQUENCE is null)

  if @@Error <> 0
    return 3;    

  declare @CONTEXTCACHE varbinary(128);
  declare @LOCALERROR int;

  set @CONTEXTCACHE = context_info();

  if not @CHANGEAGENTID is null
    set context_info @CHANGEAGENTID;

  -- delete any items that no longer exist in the XML table

  delete from
    dbo.ORGANIZATIONHIERARCHY
  where
    ORGANIZATIONHIERARCHY.ID not in (select ID from @ORGHIERARCHY);

  delete from
    dbo.ORGANIZATIONPOSITIONHOLDER
  where
    ORGANIZATIONPOSITIONHOLDER.ID not in (select ID from @ORGPOSITIONHOLDERS);

  select @LOCALERROR = @@error;

  if not @CONTEXTCACHE is null
    set context_info @CONTEXTCACHE;

  if @LOCALERROR <> 0
    return 4;

  update
    ORGANIZATIONPOSITION
  set
    ORGANIZATIONPOSITION.NAME = TEMP.NAME,
    ORGANIZATIONPOSITION.SITEID = TEMP.SITEID,
    ORGANIZATIONPOSITION.BUSINESSUNITCODEID = TEMP.BUSINESSUNITCODEID,
    ORGANIZATIONPOSITION.CHANGEDBYID = @CHANGEAGENTID,
    ORGANIZATIONPOSITION.DATECHANGED = @CHANGEDATE
  from
    dbo.ORGANIZATIONPOSITION
  inner join
    @ORGHIERARCHY as TEMP on TEMP.ID = ORGANIZATIONPOSITION.ID
  where
    (ORGANIZATIONPOSITION.NAME <> TEMP.NAME) or
    (ORGANIZATIONPOSITION.NAME is null and TEMP.NAME is not null) or
    (ORGANIZATIONPOSITION.NAME is not null and TEMP.NAME is null) or
    (ORGANIZATIONPOSITION.SITEID <> TEMP.SITEID) or
    (ORGANIZATIONPOSITION.SITEID is null and TEMP.SITEID is not null) or
    (ORGANIZATIONPOSITION.SITEID is not null and TEMP.SITEID is null) or
    (ORGANIZATIONPOSITION.BUSINESSUNITCODEID <> TEMP.BUSINESSUNITCODEID) or
    (ORGANIZATIONPOSITION.BUSINESSUNITCODEID is null and TEMP.BUSINESSUNITCODEID is not null) or
    (ORGANIZATIONPOSITION.BUSINESSUNITCODEID is not null and TEMP.BUSINESSUNITCODEID is null)

  if @@Error <> 0
    return 3;    

  insert into dbo.ORGANIZATIONPOSITION
  (
    ID,
    NAME,
    SITEID,
    BUSINESSUNITCODEID,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED
  )
  select
    ID,
    NAME,
    SITEID,
    BUSINESSUNITCODEID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE
  from
    @ORGHIERARCHY as TEMP
  where
    not exists (select ID from dbo.ORGANIZATIONPOSITION as DATA where DATA.ID = TEMP.ID)

  -- update the items that exist in the XML table and the db

  -- we must update items based on how the dates are changed

  -- decreases in [DATETO] first

  -- changes to [DATEFROM] next

  -- increases in [DATETO] last

  -- changes to position and constituent are not allowed

  declare @UPDATEID uniqueidentifier;
  declare @DATEFROM datetime;
  declare @DATETO datetime;

  declare CUR_UPDATES cursor local fast_forward for

  select
    TEMP.ID,
    TEMP.DATEFROM,
    TEMP.DATETO
  from
    dbo.ORGANIZATIONPOSITIONHOLDER
  inner join
    @ORGPOSITIONHOLDERS as TEMP on TEMP.ID = ORGANIZATIONPOSITIONHOLDER.ID
  where
    (ORGANIZATIONPOSITIONHOLDER.DATEFROM <> TEMP.DATEFROM) or
    (ORGANIZATIONPOSITIONHOLDER.DATEFROM is null and TEMP.DATEFROM is not null) or
    (ORGANIZATIONPOSITIONHOLDER.DATEFROM is not null and TEMP.DATEFROM is null) or
    (ORGANIZATIONPOSITIONHOLDER.DATETO <> TEMP.DATETO) or
    (ORGANIZATIONPOSITIONHOLDER.DATETO is null and TEMP.DATETO is not null) or
    (ORGANIZATIONPOSITIONHOLDER.DATETO is not null and TEMP.DATETO is null)
  order by 
    case
      when TEMP.DATETO < ORGANIZATIONPOSITIONHOLDER.DATETO
        then 0
      else
        case
          when TEMP.DATETO > ORGANIZATIONPOSITIONHOLDER.DATETO
            then 2
          else 1
        end 
    end,
    TEMP.DATEFROM;

  open CUR_UPDATES;

  fetch next from CUR_UPDATES into @UPDATEID, @DATEFROM, @DATETO;
  while @@FETCH_STATUS = 0
  begin
    update
      dbo.ORGANIZATIONPOSITIONHOLDER
    set
      DATEFROM = @DATEFROM,
      DATETO = @DATETO,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CHANGEDATE
    where
      ORGANIZATIONPOSITIONHOLDER.ID = @UPDATEID;

    fetch next from CUR_UPDATES into @UPDATEID, @DATEFROM, @DATETO;
  end

  close CUR_UPDATES;
  deallocate CUR_UPDATES;

  if @@Error <> 0
    return 3;

  -- insert new items

  insert into dbo.ORGANIZATIONPOSITIONHOLDER
  (
    ID,
    POSITIONID,
    CONSTITUENTID,
    DATEFROM,
    DATETO,
    ADDEDBYID, 
    CHANGEDBYID, 
    DATEADDED, 
    DATECHANGED
  )
  select
    ID,
    POSITIONID,
    CONSTITUENTID,
    DATEFROM,
    DATETO,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE
  from
    @ORGPOSITIONHOLDERS as TEMP
  where
    not exists (select ID from dbo.ORGANIZATIONPOSITIONHOLDER as DATA where DATA.ID = TEMP.ID)

  if @@Error <> 0
    return 4;

  return 0;