USP_BANKACCOUNTDEPOSITCORRECTION_GLDISTRIBUTION_UPDATEFROMXML

CREATE procedure dbo.USP_BANKACCOUNTDEPOSITCORRECTION_GLDISTRIBUTION_UPDATEFROMXML
(
  @BANKACCOUNTDEPOSITCORRECTIONID 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 D.GLTRANSACTIONID from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION D where D.BANKACCOUNTDEPOSITCORRECTIONID = @BANKACCOUNTDEPOSITCORRECTIONID
     EXCEPT select T.ID from @TempTbl T);

  delete from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION where ID in
    (select D.ID from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION D where D.BANKACCOUNTDEPOSITCORRECTIONID = @BANKACCOUNTDEPOSITCORRECTIONID
     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

  merge into dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION
  using @TempTbl T on BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ID = T.ID
  when matched and 
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ACCOUNT <> T.ACCOUNT) or
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ACCOUNT is null and T.ACCOUNT is not null) or
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ACCOUNT is not null and T.ACCOUNT is null) or
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.AMOUNT <> T.AMOUNT) or
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.AMOUNT is null and T.AMOUNT is not null) or
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.AMOUNT is not null and T.AMOUNT is null) or 
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.PROJECT<>T.PROJECT) or 
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.PROJECT is null and T.PROJECT is not null) or 
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.PROJECT is not null and T.PROJECT is null) or 
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.REFERENCE<>T.REFERENCE) or 
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.REFERENCE is null and T.REFERENCE is not null) or 
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.REFERENCE is not null and T.REFERENCE is null) or 
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.TRANSACTIONTYPECODE<>T.TRANSACTIONTYPECODE) or 
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.TRANSACTIONTYPECODE is null and T.TRANSACTIONTYPECODE is not null) or 
        (BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.TRANSACTIONTYPECODE is not null and T.TRANSACTIONTYPECODE is null)
  then update set
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ACCOUNT = T.ACCOUNT,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.AMOUNT = T.AMOUNT,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.PROJECT = T.PROJECT,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.REFERENCE = T.REFERENCE,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.TRANSACTIONTYPECODE = T.TRANSACTIONTYPECODE,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.CHANGEDBYID = @CHANGEAGENTID,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.DATECHANGED = @CHANGEDATE
  ;


  merge into dbo.GLTRANSACTION 
  using dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as D
  inner join @TempTbl as T on D.ID = T.ID
  on  D.GLTRANSACTIONID = GLTRANSACTION.ID   
  when matched then update 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
  ;

  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.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION D where D.ID = T.ID);

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

  if @@Error <> 0
    return 4;

  return 0;