USP_VENDOR_1099ADJUSTMENTS_UPDATEFROMXML

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

  if @CHANGEAGENTID is null
      exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

  declare @TempTbl table(
    [ID] uniqueidentifier,
    [EFFECTIVEDATE] datetime,
    [BOXNUMBER1099ID] uniqueidentifier,
    [STATEID] uniqueidentifier,
    [AMOUNT] money,
    [REFERENCE] nvarchar(60))

  insert into @TempTbl select
    [ID],
    [EFFECTIVEDATE],
    [BOXNUMBER1099ID],
    [STATEID],
    [AMOUNT],
    [REFERENCE]
  from dbo.UFN_VENDOR_1099ADJUSTMENTS_FROMITEMLISTXML(@XML)

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

  if @@Error <> 0
    return 1;

  declare @contextCache varbinary(128);
  declare @e int;

  -- cache current context information

  set @contextCache = CONTEXT_INFO();

  -- set CONTEXT_INFO to @CHANGEAGENTID

  if not @CHANGEAGENTID is null
    set CONTEXT_INFO @CHANGEAGENTID;

  delete from dbo.VENDOR1099ADJUSTMENT where VENDOR1099ADJUSTMENT.ID in
    (select ID from dbo.VENDOR1099ADJUSTMENT where VENDOR1099ADJUSTMENT.VENDOR1099SETTINGID = @VENDORID
      EXCEPT select ID from @TempTbl)

  select @e = @@error;

  -- reset CONTEXT_INFO to previous value 

  if not @contextCache is null
      set CONTEXT_INFO @contextCache;

  if @e <> 0
      return 2;

  update dbo.VENDOR1099ADJUSTMENT set
    VENDOR1099ADJUSTMENT.ID = temp.ID,
    VENDOR1099ADJUSTMENT.EFFECTIVEDATE = temp.EFFECTIVEDATE,
    VENDOR1099ADJUSTMENT.BOXNUMBER1099ID = temp.BOXNUMBER1099ID,
    VENDOR1099ADJUSTMENT.STATEID = temp.STATEID,
    VENDOR1099ADJUSTMENT.AMOUNT = temp.AMOUNT,
    VENDOR1099ADJUSTMENT.REFERENCE = temp.REFERENCE,
    VENDOR1099ADJUSTMENT.CHANGEDBYID = @CHANGEAGENTID,
    VENDOR1099ADJUSTMENT.DATECHANGED = @CHANGEDATE
  from dbo.VENDOR1099ADJUSTMENT
  inner join @TempTbl as temp on VENDOR1099ADJUSTMENT.ID = temp.ID
  where (VENDOR1099ADJUSTMENT.ID <> temp.ID) or
        (VENDOR1099ADJUSTMENT.ID is null and temp.ID is not null) or
        (VENDOR1099ADJUSTMENT.ID is not null and temp.ID is null) or
        (VENDOR1099ADJUSTMENT.EFFECTIVEDATE <> temp.EFFECTIVEDATE) or
        (VENDOR1099ADJUSTMENT.EFFECTIVEDATE is null and temp.EFFECTIVEDATE is not null) or
        (VENDOR1099ADJUSTMENT.EFFECTIVEDATE is not null and temp.EFFECTIVEDATE is null) or
        (VENDOR1099ADJUSTMENT.BOXNUMBER1099ID <> temp.BOXNUMBER1099ID) or
        (VENDOR1099ADJUSTMENT.BOXNUMBER1099ID is null and temp.BOXNUMBER1099ID is not null) or
        (VENDOR1099ADJUSTMENT.BOXNUMBER1099ID is not null and temp.BOXNUMBER1099ID is null) or
        (VENDOR1099ADJUSTMENT.STATEID <> temp.STATEID) or
        (VENDOR1099ADJUSTMENT.STATEID is null and temp.STATEID is not null) or
        (VENDOR1099ADJUSTMENT.STATEID is not null and temp.STATEID is null) or
        (VENDOR1099ADJUSTMENT.AMOUNT <> temp.AMOUNT) or
        (VENDOR1099ADJUSTMENT.AMOUNT is null and temp.AMOUNT is not null) or
        (VENDOR1099ADJUSTMENT.AMOUNT is not null and temp.AMOUNT is null) or
        (VENDOR1099ADJUSTMENT.REFERENCE <> temp.REFERENCE) or
        (VENDOR1099ADJUSTMENT.REFERENCE is null and temp.REFERENCE is not null) or
        (VENDOR1099ADJUSTMENT.REFERENCE is not null and temp.REFERENCE is null)

  if @@Error <> 0
    return 3;

  insert into dbo.VENDOR1099ADJUSTMENT(
    ID,
    VENDOR1099SETTINGID,
    EFFECTIVEDATE,
    BOXNUMBER1099ID,
    STATEID,
    AMOUNT,
    REFERENCE,
    ADDEDBYID, 
      CHANGEDBYID, 
      DATEADDED, 
      DATECHANGED)
  select
    ID,
    @VENDORID,
    EFFECTIVEDATE,
    BOXNUMBER1099ID,
    STATEID,
    AMOUNT,
    REFERENCE,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE
  from @TempTbl as temp
  where not exists (select ID from dbo.VENDOR1099ADJUSTMENT as data where data.ID = temp.ID)

  if @@Error <> 0
    return 4;

  return 0;