USP_FINANCIALTRANSACTION_1099DISTRIBUTION_UPDATEFROMXML

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

  if @VENDORID is not null
    begin
      declare @IS1099 bit;
      select @IS1099 = V.INCLUDEIN1099 from dbo.VENDOR V where V.ID = @VENDORID;

      if @IS1099 = 0
        return 0;
    end

  if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

  declare @TempTbl table(
    [ID] uniqueidentifier,
    [BOXNUMBER1099ID] uniqueidentifier,
    [STATEID] uniqueidentifier,
    [PERCENT] numeric(20,4),
    [AMOUNT] money)

  insert into @TempTbl select
    [ID],
    [BOXNUMBER1099ID],
    [STATEID],
    [PERCENT],
    [AMOUNT]
  from dbo.UFN_FINANCIALTRANSACTION_1099DISTRIBUTION_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.FINANCIALTRANSACTION1099DISTRIBUTION where FINANCIALTRANSACTION1099DISTRIBUTION.ID in
    (select ID from dbo.FINANCIALTRANSACTION1099DISTRIBUTION where FINANCIALTRANSACTION1099DISTRIBUTION.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID
    EXCEPT select ID from @TempTbl)

  --reset CONTEXT_INFO to previous value

  if not @contextCache is null
    set CONTEXT_INFO @contextCache

  if @@error <> 0
    return 2;

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

  if @@error <> 0
    return 3;

  insert into dbo.FINANCIALTRANSACTION1099DISTRIBUTION(
    ID,
    FINANCIALTRANSACTIONID,
    BOXNUMBER1099ID,
    STATEID,
    [PERCENT],
    AMOUNT,
    ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED)
  select
    ID,
    @FINANCIALTRANSACTIONID,
    BOXNUMBER1099ID,
    STATEID,
    [PERCENT],
    AMOUNT,
    @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CHANGEDATE,
        @CHANGEDATE
    from @TempTbl as temp
    where not exists (select ID from dbo.FINANCIALTRANSACTION1099DISTRIBUTION as data where data.ID = temp.ID)

    if @@Error <> 0
      return 4;

    return 0;