USP_CLOSINGREQUIREMENTDETAIL_UPDATEFROMXML

CREATE procedure dbo.USP_CLOSINGREQUIREMENTDETAIL_UPDATEFROMXML(
    @CLOSINGREQUIREMENTID uniqueidentifier ,
    @XML xml,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    set nocount on;
    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
    -- build a temporary table containing the values from the XML

    declare @TempTbl table (
       [ID] uniqueidentifier,
       [ACCOUNTSTRUCTUREID] uniqueidentifier,
       [PRESERVEDETAILSDURINGCLOSING] tinyint,
       [REQUIRETOBALANCE] tinyint,
       [ACCOUNTREQUIREMENTSCODE] tinyint,
       [CLOSINGREQUIREMENTID] uniqueidentifier
       )

  insert into @TempTbl select 
  coalesce(nullif([ID],'00000000-0000-0000-0000-000000000000'),newid()) ,
  [ACCOUNTSTRUCTUREID] ,
  [PRESERVEDETAILSDURINGCLOSING],
  [REQUIRETOBALANCE] ,
  [ACCOUNTREQUIREMENTSCODE] ,
  @CLOSINGREQUIREMENTID from
  dbo.UFN_CLOSINGREQUIREMENTDETAILS_FROMITEMLISTXML(@XML)

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

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

    delete from dbo.[CLOSINGREQUIREMENTDETAIL] where 
    ([CLOSINGREQUIREMENTDETAIL].ID not in (select ID from @TempTbl)) and ([CLOSINGREQUIREMENTDETAIL].CLOSINGREQUIREMENTID = @CLOSINGREQUIREMENTID)    

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

  update [CLOSINGREQUIREMENTDETAIL] set 
      [CLOSINGREQUIREMENTDETAIL].[ID]=temp.[ID],
      [CLOSINGREQUIREMENTDETAIL].[ACCOUNTSTRUCTUREID]=temp.[ACCOUNTSTRUCTUREID],
      [CLOSINGREQUIREMENTDETAIL].[PRESERVEDETAILSDURINGCLOSING]=temp.[PRESERVEDETAILSDURINGCLOSING],
      [CLOSINGREQUIREMENTDETAIL].[REQUIRETOBALANCE]=temp.[REQUIRETOBALANCE],
      [CLOSINGREQUIREMENTDETAIL].[ACCOUNTREQUIREMENTSCODE]=temp.[ACCOUNTREQUIREMENTSCODE],
      [CLOSINGREQUIREMENTDETAIL].CHANGEDBYID = @CHANGEAGENTID,
      [CLOSINGREQUIREMENTDETAIL].DATECHANGED = getdate()
    from dbo.[CLOSINGREQUIREMENTDETAIL] inner join @TempTbl as temp on [CLOSINGREQUIREMENTDETAIL].ID = temp.ID
  where (
      ([CLOSINGREQUIREMENTDETAIL].[ACCOUNTSTRUCTUREID]<>temp.[ACCOUNTSTRUCTUREID]) or 
      ([CLOSINGREQUIREMENTDETAIL].[ACCOUNTSTRUCTUREID] is null and temp.[ACCOUNTSTRUCTUREID] is not null) or 
      ([CLOSINGREQUIREMENTDETAIL].[ACCOUNTSTRUCTUREID] is not null and temp.[ACCOUNTSTRUCTUREID] is null) or                         
      ([CLOSINGREQUIREMENTDETAIL].[PRESERVEDETAILSDURINGCLOSING]<>temp.[PRESERVEDETAILSDURINGCLOSING]) or 
      ([CLOSINGREQUIREMENTDETAIL].[PRESERVEDETAILSDURINGCLOSING] is null and temp.[PRESERVEDETAILSDURINGCLOSING] is not null) or 
      ([CLOSINGREQUIREMENTDETAIL].[PRESERVEDETAILSDURINGCLOSING] is not null and temp.[PRESERVEDETAILSDURINGCLOSING] is null)or                         
      ([CLOSINGREQUIREMENTDETAIL].[REQUIRETOBALANCE]<>temp.[REQUIRETOBALANCE]) or 
      ([CLOSINGREQUIREMENTDETAIL].[REQUIRETOBALANCE] is null and temp.[REQUIRETOBALANCE] is not null) or 
      ([CLOSINGREQUIREMENTDETAIL].[REQUIRETOBALANCE] is not null and temp.[REQUIRETOBALANCE] is null)or                         
      ([CLOSINGREQUIREMENTDETAIL].[ACCOUNTREQUIREMENTSCODE]<>temp.[ACCOUNTREQUIREMENTSCODE]) or 
      ([CLOSINGREQUIREMENTDETAIL].[ACCOUNTREQUIREMENTSCODE] is null and temp.[ACCOUNTREQUIREMENTSCODE] is not null) or 
      ([CLOSINGREQUIREMENTDETAIL].[ACCOUNTREQUIREMENTSCODE] is not null and temp.[ACCOUNTREQUIREMENTSCODE] is null))


        -- insert new items

        insert into [CLOSINGREQUIREMENTDETAIL](
            [ID],
            [ACCOUNTSTRUCTUREID] ,
      [PRESERVEDETAILSDURINGCLOSING],
      [REQUIRETOBALANCE] ,
      [ACCOUNTREQUIREMENTSCODE] ,
      [CLOSINGREQUIREMENTID],
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED)
        select 
            [ID],
            [ACCOUNTSTRUCTUREID] ,
        [PRESERVEDETAILSDURINGCLOSING],
        [REQUIRETOBALANCE] ,
        [ACCOUNTREQUIREMENTSCODE] ,
          @CLOSINGREQUIREMENTID,
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            getdate(), 
            getdate()
        from @TempTbl as temp
        where not exists (select ID from dbo.[CLOSINGREQUIREMENTDETAIL] as data where data.ID = temp.ID)

end