USP_BANKACCOUNTTRANSACTION_GLDISTRIBUTION_UPDATEFROMXML

CREATE procedure dbo.USP_BANKACCOUNTTRANSACTION_GLDISTRIBUTION_UPDATEFROMXML
(
  @BANKACCOUNTTRANSACTIONID uniqueidentifier,
  @XML xml,
  @POSTDATE datetime,
  @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 (
    [ACCOUNT] nvarchar(100),
    [GLACCOUNTID] uniqueidentifier,
    [AMOUNT] money,
    [ID] uniqueidentifier,
    [PROJECT] nvarchar(100),
    [REFERENCE] nvarchar(100),
    [TRANSACTIONTYPECODE] tinyint,
    [GLTRANSACTIONID] uniqueidentifier);

  insert into @TempTbl
  select A.ACCOUNTNUMBER [ACCOUNT], D.GLACCOUNTID, D.AMOUNT, D.ID, D.PROJECT, D.REFERENCE, D.TRANSACTIONTYPECODE, NEWID()
  from dbo.UFN_BANKACCOUNTTRANSACTION_GETGLDISTRIBUTION_FROMITEMLISTXML(@XML) D
  inner join dbo.GLACCOUNT A on A.ID = D.GLACCOUNTID

  update @TempTbl set ID = newid() where (ID is null) or (ID = '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 any items that no longer exist in the XML

  delete from dbo.GLTRANSACTION where ID in
    (select ED.GLTRANSACTIONID from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION ED where ED.BANKACCOUNTTRANSACTIONID = @BANKACCOUNTTRANSACTIONID
     EXCEPT select T.ID from @TempTbl T);

  delete from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION where ID in
    (select ED.ID from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION ED where ED.BANKACCOUNTTRANSACTIONID = @BANKACCOUNTTRANSACTIONID
     EXCEPT select T.ID from @TempTbl T);

  select @e=@@error;

  -- reset CONTEXT_INFO to previous value

  if not @contextCache is null
    set CONTEXT_INFO @contextCache;

  if @e <> 0
    return 2;

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

  /* kwb Change to ANSI syntax
  update dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION set
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ACCOUNT = T.ACCOUNT,
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.AMOUNT = T.AMOUNT,
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.PROJECT = T.PROJECT,
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.REFERENCE = T.REFERENCE,
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONTYPECODE = T.TRANSACTIONTYPECODE,
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.CHANGEDBYID = @CHANGEAGENTID,
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.DATECHANGED = @CHANGEDATE
  from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION
  inner join @TempTbl T on BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID = T.ID
  where (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ACCOUNT <> T.ACCOUNT) or
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ACCOUNT is null and T.ACCOUNT is not null) or
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ACCOUNT is not null and T.ACCOUNT is null) or
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.AMOUNT <> T.AMOUNT) or
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.AMOUNT is null and T.AMOUNT is not null) or
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.AMOUNT is not null and T.AMOUNT is null) or 
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.PROJECT<>T.PROJECT) or 
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.PROJECT is null and T.PROJECT is not null) or 
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.PROJECT is not null and T.PROJECT is null) or 
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.REFERENCE<>T.REFERENCE) or 
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.REFERENCE is null and T.REFERENCE is not null) or 
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.REFERENCE is not null and T.REFERENCE is null) or 
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONTYPECODE<>T.TRANSACTIONTYPECODE) or 
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONTYPECODE is null and T.TRANSACTIONTYPECODE is not null) or 
        (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONTYPECODE is not null and T.TRANSACTIONTYPECODE is null);

  update dbo.GLTRANSACTION set
    GLTRANSACTION.ACCOUNT = T.ACCOUNT,
    GLTRANSACTION.GLACCOUNTID = T.GLACCOUNTID,
    GLTRANSACTION.AMOUNT = T.AMOUNT,
    GLTRANSACTION.PROJECT = T.PROJECT,
    GLTRANSACTION.REFERENCE = T.REFERENCE,
    GLTRANSACTION.TRANSACTIONTYPECODE = T.TRANSACTIONTYPECODE,
    GLTRANSACTION.CHANGEDBYID = @CHANGEAGENTID,
    GLTRANSACTION.DATECHANGED = @CHANGEDATE
  from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D
  inner join @TempTbl T on D.ID = T.ID
  inner join dbo.GLTRANSACTION on D.GLTRANSACTIONID = GLTRANSACTION.ID;
  */
  update dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION
  set BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ACCOUNT = (select ACCOUNT from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.AMOUNT = (select AMOUNT from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.PROJECT = (select PROJECT from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.REFERENCE = (select REFERENCE from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONTYPECODE = (select TRANSACTIONTYPECODE from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.CHANGEDBYID = @CHANGEAGENTID,
    BANKACCOUNTTRANSACTIONGLDISTRIBUTION.DATECHANGED = @CHANGEDATE
  where ID in (select ID from @TempTbl)

  update dbo.GLTRANSACTION
  set GLTRANSACTION.ACCOUNT = (select T.ACCOUNT from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D inner join @TempTbl T on D.ID = T.ID where D.GLTRANSACTIONID = GLTRANSACTION.ID),
    GLTRANSACTION.GLACCOUNTID = (select T.GLACCOUNTID from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D inner join @TempTbl T on D.ID = T.ID where D.GLTRANSACTIONID = GLTRANSACTION.ID),
    GLTRANSACTION.AMOUNT = (select T.AMOUNT from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D inner join @TempTbl T on D.ID = T.ID where D.GLTRANSACTIONID = GLTRANSACTION.ID),
    GLTRANSACTION.PROJECT = (select T.PROJECT from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D inner join @TempTbl T on D.ID = T.ID where D.GLTRANSACTIONID = GLTRANSACTION.ID),
    GLTRANSACTION.REFERENCE = (select T.REFERENCE from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D inner join @TempTbl T on D.ID = T.ID where D.GLTRANSACTIONID = GLTRANSACTION.ID),
    GLTRANSACTION.TRANSACTIONTYPECODE = (select T.TRANSACTIONTYPECODE from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D inner join @TempTbl T on D.ID = T.ID where D.GLTRANSACTIONID = GLTRANSACTION.ID),
    GLTRANSACTION.CHANGEDBYID = @CHANGEAGENTID,
    GLTRANSACTION.DATECHANGED = @CHANGEDATE
  where ID in (select D.GLTRANSACTIONID from BANKACCOUNTTRANSACTIONGLDISTRIBUTION D inner join @TempTbl T on D.ID = T.ID)

  if @@Error <> 0
    return 3;

  --insert new rows

  insert into dbo.GLTRANSACTION(ID, TRANSACTIONTYPECODE, ACCOUNT, GLACCOUNTID, AMOUNT, PROJECT, REFERENCE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    GLTRANSACTIONID,
    TRANSACTIONTYPECODE,
    ACCOUNT,
    GLACCOUNTID,
    AMOUNT,
    PROJECT,
    REFERENCE,
    @POSTDATE,
    'Blackbaud Enterprise',
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE
  from @TempTbl T
  where not exists (select ID from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D where D.ID = T.ID);

  insert into dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION
    (ID, GLTRANSACTIONID, BANKACCOUNTTRANSACTIONID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    ID,
    GLTRANSACTIONID,
    @BANKACCOUNTTRANSACTIONID,
    PROJECT,
    REFERENCE,
    AMOUNT,
    ACCOUNT,
    TRANSACTIONTYPECODE,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE
  from @TempTbl T
  where not exists (select ID from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D where D.ID = T.ID);

  if @@Error <> 0
    return 4;

  return 0;