USP_ACCOUNTSTRUCTURE_UPDATEFROMXML

CREATE procedure dbo.USP_ACCOUNTSTRUCTURE_UPDATEFROMXML
(
  @XML xml,
  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
  set nocount on;

  declare @PDACCOUNTSYSTEMID uniqueidentifier = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';

  if @CHANGEAGENTID is null
    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

  -- build a temporary table containing the values from the XML

  declare @CURRENTDATE datetime = getdate();

  declare @TempTbl table
  (
    [ID] uniqueidentifier,
    [DESCRIPTION] nvarchar(100),
    [LENGTH] tinyint,
    [SEQUENCE] tinyint,
    [ELEMENTTYPECODE] tinyint,
    [ELEMENTDEFINITIONCODE] tinyint
  )

  insert into @TempTbl select 
    isnull(nullif([ID], '00000000-0000-0000-0000-000000000000'), newid()) ,
    [DESCRIPTION],
    isnull([LENGTH],0),
    [SEQUENCE],
    [ELEMENTTYPECODE],
    [ELEMENTDEFINITIONCODE]
  from dbo.UFN_ACCOUNTSTRUCTURE_FROMITEMLISTXML(@XML)

  ;merge into dbo.[PDACCOUNTSTRUCTURE]
  using @TempTbl T1
  on T1.ID = PDACCOUNTSTRUCTURE.ID
  when matched then
    update set
      PDACCOUNTSTRUCTURE.[DESCRIPTION] = T1.[DESCRIPTION],
      PDACCOUNTSTRUCTURE.[LENGTH] = T1.[LENGTH],
      PDACCOUNTSTRUCTURE.SEQUENCE = T1.SEQUENCE,
      PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = T1.ELEMENTTYPECODE,
      PDACCOUNTSTRUCTURE.ELEMENTDEFINITIONCODE = T1.ELEMENTDEFINITIONCODE,
      PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
      PDACCOUNTSTRUCTURE.ISBASICGL = 0,
      PDACCOUNTSTRUCTURE.CHANGEDBYID = @CHANGEAGENTID,
      PDACCOUNTSTRUCTURE.DATECHANGED = @CURRENTDATE
  when not matched by target then
    insert ([ID], [DESCRIPTION], [LENGTH], [SEQUENCE], [SEGMENTSEQUENCE], [ELEMENTTYPECODE], [ELEMENTDEFINITIONCODE], [PDACCOUNTSYSTEMID], [ISBASICGL], ADDEDBYID, DATEADDED, CHANGEDBYID, DATECHANGED)
    values (T1.ID, T1.[DESCRIPTION], T1.[LENGTH], T1.[SEQUENCE], 0, T1.[ELEMENTTYPECODE], T1.[ELEMENTDEFINITIONCODE], @PDACCOUNTSYSTEMID, 0, @CHANGEAGENTID, @CURRENTDATE, @CHANGEAGENTID, @CURRENTDATE)
  when not matched by source
    and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID then
    delete;

  update dbo.PDACCOUNTSTRUCTURE set SEGMENTSEQUENCE = 0, DEFAULTDESCRIPTIONSEQUENCE = 0, DEFAULTDESCRIPTIONLENGTH = 0 where  ELEMENTTYPECODE = 2

  update PDACCOUNTSTRUCTURE set 
    SEGMENTSEQUENCE = 
    (select V1.RowNumber from
      (select id,row_number() over (order by case SEGMENTSEQUENCE when 0 then 31 else SEGMENTSEQUENCE end)as RowNumber 
      from dbo.PDACCOUNTSTRUCTURE where ELEMENTTYPECODE = 1) V1
    where v1.id = PDACCOUNTSTRUCTURE.id)
  where ELEMENTTYPECODE = 1
    and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID

end