USP_BANKACCOUNTDEPOSITCORRECTION_GLDISTRIBUTION_MULTICURRENCY_UPDATEFROMXML

CREATE procedure dbo.USP_BANKACCOUNTDEPOSITCORRECTION_GLDISTRIBUTION_MULTICURRENCY_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,
    [GLTRANSACTIONID] uniqueidentifier,
    [PROJECT] nvarchar(100),
    [REFERENCE] nvarchar(255),
    [TRANSACTIONTYPECODE] tinyint,
    [ORGANIZATIONAMOUNT] money,
    [BASECURRENCYID] uniqueidentifier,
    [TRANSACTIONAMOUNT] money,
    [TRANSACTIONCURRENCYID] uniqueidentifier,
    [BASEEXCHANGERATEID] uniqueidentifier,
    [ORGANIZATIONEXCHANGERATEID] uniqueidentifier)

  insert into @TempTbl select 
        A.ACCOUNTNUMBER [ACCOUNT],
    D.[GLACCOUNTID],
        D.[AMOUNT],
        D.[ID],
    D.[GLTRANSACTIONID],
        D.[PROJECT],
        D.[REFERENCE],
        D.[TRANSACTIONTYPECODE],
        D.[ORGANIZATIONAMOUNT],
        D.[BASECURRENCYID],
        D.[TRANSACTIONAMOUNT],
        D.[TRANSACTIONCURRENCYID],
        D.[BASEEXCHANGERATEID],
        D.[ORGANIZATIONEXCHANGERATEID]
  from dbo.UFN_BANKACCOUNTDEPOSITCORRECTION_GETGLDISTRIBUTION_FROMITEMLISTXML(@XML) D
  inner join dbo.GLACCOUNT A on A.ID = D.GLACCOUNTID;

  declare @GLACCOUNTID uniqueidentifier;
  declare @ACCOUNTCODEID uniqueidentifier;
  declare @PDACCOUNTSYSTEMID uniqueidentifier;
  declare @CHECKDEBITSIDE bit;
  declare @DCTRANSACTIONCURRENCYID uniqueidentifier;
  declare @BATRANSACTIONCURRENCYID uniqueidentifier;

  select @GLACCOUNTID = BA.GLACCOUNTID
    ,@ACCOUNTCODEID = BA.PDACCOUNTSEGMENTVALUEID
    ,@CHECKDEBITSIDE = CASE WHEN C.CORRECTIONTYPECODE in (0, 2) THEN 0 ELSE 1 END
    ,@PDACCOUNTSYSTEMID = BA.PDACCOUNTSYSTEMID
    ,@DCTRANSACTIONCURRENCYID = D.TRANSACTIONCURRENCYID
    ,@BATRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
  from dbo.BANKACCOUNTDEPOSITCORRECTION C
  inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = C.DEPOSITID
  inner join dbo.BANKACCOUNTTRANSACTION T on C.DEPOSITID = T.ID
  inner join dbo.BANKACCOUNT BA on BA.ID = T.BANKACCOUNTID
  where C.ID = @BANKACCOUNTDEPOSITCORRECTIONID

  if @DCTRANSACTIONCURRENCYID = @BATRANSACTIONCURRENCYID 
    begin
      if @GLACCOUNTID is not null
      begin
        if exists(select ID from @TempTbl where GLACCOUNTID <> @GLACCOUNTID and @CHECKDEBITSIDE = 1 and TRANSACTIONTYPECODE = 0)
          raiserror('ERR_DEBITACCOUNT_MUST_BE_SAME_AS_BANK.', 13, 1)
        else if exists(select ID from @TempTbl where GLACCOUNTID <> @GLACCOUNTID and @CHECKDEBITSIDE = 0 and TRANSACTIONTYPECODE = 1)
          raiserror('ERR_CREDITACCOUNT_MUST_BE_SAME_AS_BANK.', 13, 1)
      end
      else
      begin
        declare @ACCOUNT nvarchar(100);
        select @ACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@ACCOUNTCODEID,@PDACCOUNTSYSTEMID);
        select @GLACCOUNTID = ID from dbo.GLACCOUNT where ACCOUNTNUMBER = @ACCOUNT and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;
        if exists(select ID from @TempTbl where GLACCOUNTID <> @GLACCOUNTID and @CHECKDEBITSIDE = 1 and TRANSACTIONTYPECODE = 0)
          raiserror('ERR_DEBITACCOUNT_MUST_BE_SAME_AS_BANK.', 13, 1)
        else if exists(select ID from @TempTbl where GLACCOUNTID <> @GLACCOUNTID and @CHECKDEBITSIDE = 0 and TRANSACTIONTYPECODE = 1)
          raiserror('ERR_CREDITACCOUNT_MUST_BE_SAME_AS_BANK.', 13, 1)
      end
    end

  update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
  update @TempTbl set GLTRANSACTIONID = newid() where (GLTRANSACTIONID is null) or (GLTRANSACTIONID = '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;

  declare @transactions table (ID uniqueidentifier)
  insert into @transactions
  select D.GLTRANSACTIONID 
  from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION D 
  where D.BANKACCOUNTDEPOSITCORRECTIONID = @BANKACCOUNTDEPOSITCORRECTIONID
    EXCEPT select T.GLTRANSACTIONID from @TempTbl T

  -- delete any items that no longer exist in the XML from the GLTRANSACTION table

  delete from dbo.GLTRANSACTION where SYSTEMDISTRIBUTION = 0 and ID in
    (select T.ID from @transactions 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 as T on BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ID = T.ID
  when matched then update set
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ACCOUNT = T.ACCOUNT,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.AMOUNT = T.AMOUNT,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.PROJECT = T.PROJECT,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.REFERENCE = T.REFERENCE,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.TRANSACTIONTYPECODE = T.TRANSACTIONTYPECODE,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ORGANIZATIONAMOUNT = T.ORGANIZATIONAMOUNT,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.BASECURRENCYID = T.BASECURRENCYID,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.TRANSACTIONAMOUNT = T.TRANSACTIONAMOUNT,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.TRANSACTIONCURRENCYID = T.TRANSACTIONCURRENCYID,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.BASEEXCHANGERATEID = T.BASEEXCHANGERATEID,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ORGANIZATIONEXCHANGERATEID = T.ORGANIZATIONEXCHANGERATEID,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.CHANGEDBYID = @CHANGEAGENTID,
    BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.DATECHANGED = @CHANGEDATE
  ;  


  /*  kwb Switch to ANSI syntax
  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.POSTDATE = @POSTDATE,
    GLTRANSACTION.ORGANIZATIONAMOUNT = T.ORGANIZATIONAMOUNT,
        GLTRANSACTION.BASECURRENCYID = T.BASECURRENCYID,
        GLTRANSACTION.TRANSACTIONAMOUNT = T.TRANSACTIONAMOUNT,
        GLTRANSACTION.TRANSACTIONCURRENCYID = T.TRANSACTIONCURRENCYID,
        GLTRANSACTION.BASEEXCHANGERATEID = T.BASEEXCHANGERATEID,
        GLTRANSACTION.ORGANIZATIONEXCHANGERATEID = T.ORGANIZATIONEXCHANGERATEID,
        GLTRANSACTION.CHANGEDBYID = @CHANGEAGENTID,
        GLTRANSACTION.DATECHANGED = @CHANGEDATE
  from dbo.GLTRANSACTION
  inner join @TempTbl T on GLTRANSACTION.ID = T.GLTRANSACTIONID
  */

  update dbo.GLTRANSACTION set
    GLTRANSACTION.ACCOUNT = (select ACCOUNT from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
    GLTRANSACTION.GLACCOUNTID = (select GLACCOUNTID from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.AMOUNT = (select AMOUNT from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.PROJECT = (select PROJECT from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.REFERENCE = (select REFERENCE from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.TRANSACTIONTYPECODE = (select TRANSACTIONTYPECODE from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.POSTDATE = @POSTDATE,
    GLTRANSACTION.ORGANIZATIONAMOUNT = (select ORGANIZATIONAMOUNT from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.BASECURRENCYID = (select BASECURRENCYID from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.TRANSACTIONAMOUNT = (select TRANSACTIONAMOUNT from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.TRANSACTIONCURRENCYID = (select TRANSACTIONCURRENCYID from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.BASEEXCHANGERATEID = (select BASEEXCHANGERATEID from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.ORGANIZATIONEXCHANGERATEID = (select ORGANIZATIONEXCHANGERATEID from @TempTbl where GLTRANSACTIONID = GLTRANSACTION.ID),
        GLTRANSACTION.CHANGEDBYID = @CHANGEAGENTID,
        GLTRANSACTION.DATECHANGED = @CHANGEDATE
    where GLTRANSACTION.ID in (select GLTRANSACTIONID from @TempTbl)

  if @@Error <> 0
    return 3;

  insert into dbo.GLTRANSACTION(
    ID
    ,TRANSACTIONTYPECODE
    ,ACCOUNT
    ,GLACCOUNTID
    ,AMOUNT
    ,PROJECT
    ,REFERENCE
    ,POSTDATE
    ,JOURNAL
    ,ORGANIZATIONAMOUNT
    ,BASECURRENCYID
    ,TRANSACTIONAMOUNT
    ,TRANSACTIONCURRENCYID
    ,BASEEXCHANGERATEID
    ,ORGANIZATIONEXCHANGERATEID
    ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    GLTRANSACTIONID
        ,TRANSACTIONTYPECODE
        ,ACCOUNT
    ,GLACCOUNTID
        ,AMOUNT
        ,PROJECT
        ,REFERENCE
        ,@POSTDATE
        ,'Blackbaud Enterprise'
        ,ORGANIZATIONAMOUNT
        ,BASECURRENCYID
        ,TRANSACTIONAMOUNT
        ,TRANSACTIONCURRENCYID
        ,BASEEXCHANGERATEID
        ,ORGANIZATIONEXCHANGERATEID
    ,@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE
    from @TempTbl temp
  where not exists(select ID from dbo.GLTRANSACTION T where T.ID = temp.GLTRANSACTIONID)

  insert into dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION(
    ID
    ,BANKACCOUNTDEPOSITCORRECTIONID
    ,PROJECT
    ,REFERENCE
    ,AMOUNT
    ,ACCOUNT
    ,TRANSACTIONTYPECODE
    ,GLTRANSACTIONID
    ,ORGANIZATIONAMOUNT
    ,BASECURRENCYID
    ,TRANSACTIONAMOUNT
    ,TRANSACTIONCURRENCYID
    ,BASEEXCHANGERATEID
    ,ORGANIZATIONEXCHANGERATEID
    ,ADDEDBYID,CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    ID
    ,@BANKACCOUNTDEPOSITCORRECTIONID
    ,PROJECT
    ,REFERENCE
    ,AMOUNT
    ,ACCOUNT
    ,TRANSACTIONTYPECODE
    ,GLTRANSACTIONID
    ,ORGANIZATIONAMOUNT
    ,BASECURRENCYID
    ,TRANSACTIONAMOUNT
    ,TRANSACTIONCURRENCYID
    ,BASEEXCHANGERATEID
    ,ORGANIZATIONEXCHANGERATEID
    ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
  from @TempTbl temp
  where not exists(select ID from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION T where T.ID = temp.ID)

  if @@Error <> 0
    return 4;

  return 0;