USP_BANKACCOUNTTRANSACTION_GLDISTRIBUTION_MULTICURRENCY_UPDATEFROMXML

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

  begin try        

    if @CHANGEAGENTID is null
      exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

    declare @TempTbl table (
      [ID] uniqueidentifier,
      [TRANSACTIONTYPECODE] tinyint,
      [ACCOUNT] nvarchar(100),
      [GLACCOUNTID] uniqueidentifier,
      [PROJECT] nvarchar(100),
      [AMOUNT] money,
      [REFERENCE] nvarchar(100),
      [ORGANIZATIONAMOUNT] money,
      [BASECURRENCYID] uniqueidentifier,
      [TRANSACTIONAMOUNT] money,
      [TRANSACTIONCURRENCYID] uniqueidentifier,
      [BASEEXCHANGERATEID] uniqueidentifier,
      [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,    
      [GLTRANSACTIONID] uniqueidentifier);

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

    declare @GLACCOUNTID uniqueidentifier;
    declare @GLACCOUNT1ID uniqueidentifier;
    declare @ACCOUNTCODEID uniqueidentifier;
    declare @ACCOUNTCODE1ID uniqueidentifier;
    declare @PDACCOUNTSYSTEMID uniqueidentifier;
    declare @CHECKDEBITSIDE bit;
    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @TRANSACTIONCURRENCY1ID uniqueidentifier;
    declare @BATTYPECODE tinyint;

    select @GLACCOUNTID = BA.GLACCOUNTID
        ,@GLACCOUNT1ID = BA1.GLACCOUNTID
        ,@ACCOUNTCODEID = BA.PDACCOUNTSEGMENTVALUEID
        ,@ACCOUNTCODE1ID = BA1.PDACCOUNTSEGMENTVALUEID
        ,@CHECKDEBITSIDE = CASE WHEN BAT.TRANSACTIONTYPECODE in (17, 32) THEN 0 ELSE 1 END
        ,@PDACCOUNTSYSTEMID = BA.PDACCOUNTSYSTEMID
        ,@TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
        ,@TRANSACTIONCURRENCY1ID = BA1.TRANSACTIONCURRENCYID
        ,@BATTYPECODE = BAT.TRANSACTIONTYPECODE
    from dbo.BANKACCOUNTTRANSACTION as BAT 
    inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
    left outer join dbo.BANKACCOUNT BA1 on BA1.ID = BAT.TRANSFERBANKACCOUNTID
    where BAT.ID = @BANKACCOUNTTRANSACTIONID

    declare @ACCOUNT nvarchar(100);
    if @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCY1ID
    begin
        select @ACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@ACCOUNTCODE1ID,@PDACCOUNTSYSTEMID);
        select @GLACCOUNT1ID = ID from dbo.GLACCOUNT where ACCOUNTNUMBER = @ACCOUNT and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;
    end
    else
        set @GLACCOUNT1ID = null;

    if @GLACCOUNTID is not null
    begin
        if exists(select ID from @TempTbl where (GLACCOUNTID <> @GLACCOUNTID and @CHECKDEBITSIDE = 1 and TRANSACTIONTYPECODE = 0) or (@GLACCOUNT1ID is not null and GLACCOUNTID <> @GLACCOUNT1ID and @CHECKDEBITSIDE = 0 and TRANSACTIONTYPECODE = 0))
            if @BATTYPECODE in (16, 17)
                raiserror('ERR_DEBITACCOUNT_MUST_BE_SAME_AS_BANK.', 13, 1)
            else
                raiserror('ERR_DEBITACCOUNT_TRAN_MUST_BE_SAME_AS_BANK.', 13, 1)
        else if exists(select ID from @TempTbl where (GLACCOUNTID <> @GLACCOUNTID and @CHECKDEBITSIDE = 0 and TRANSACTIONTYPECODE = 1) or (@GLACCOUNT1ID is not null and GLACCOUNTID <> @GLACCOUNT1ID and @CHECKDEBITSIDE = 1 and TRANSACTIONTYPECODE = 1))
            if @BATTYPECODE in (16, 17)
                raiserror('ERR_CREDITACCOUNT_MUST_BE_SAME_AS_BANK.', 13, 1)
            else
                raiserror('ERR_CREDITACCOUNT_TRAN_MUST_BE_SAME_AS_BANK.', 13, 1)
    end
    else
    begin
        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) or (@GLACCOUNT1ID is not null and GLACCOUNTID <> @GLACCOUNT1ID and @CHECKDEBITSIDE = 0 and TRANSACTIONTYPECODE = 0))
            if @BATTYPECODE in (16, 17)
                raiserror('ERR_DEBITACCOUNT_MUST_BE_SAME_AS_BANK.', 13, 1)
            else
                raiserror('ERR_DEBITACCOUNT_TRAN_MUST_BE_SAME_AS_BANK.', 13, 1)
        else if exists(select ID from @TempTbl where (GLACCOUNTID <> @GLACCOUNTID and @CHECKDEBITSIDE = 0 and TRANSACTIONTYPECODE = 1) or (@GLACCOUNT1ID is not null and GLACCOUNTID <> @GLACCOUNT1ID and @CHECKDEBITSIDE = 1 and TRANSACTIONTYPECODE = 1))
            if @BATTYPECODE in (16, 17)
                raiserror('ERR_CREDITACCOUNT_MUST_BE_SAME_AS_BANK.', 13, 1)
            else
                raiserror('ERR_CREDITACCOUNT_TRAN_MUST_BE_SAME_AS_BANK.', 13, 1)
    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;

    -- 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 D.GLTRANSACTIONID from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D where D.BANKACCOUNTTRANSACTIONID = @BANKACCOUNTTRANSACTIONID
       EXCEPT select T.GLTRANSACTIONID 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.ORGANIZATIONAMOUNT = T.ORGANIZATIONAMOUNT,
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BASECURRENCYID = T.BASECURRENCYID,
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONAMOUNT = T.TRANSACTIONAMOUNT,
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONCURRENCYID = T.TRANSACTIONCURRENCYID,
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BASEEXCHANGERATEID = T.BASEEXCHANGERATEID,
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ORGANIZATIONEXCHANGERATEID = T.ORGANIZATIONEXCHANGERATEID,
      --BANKACCOUNTTRANSACTIONGLDISTRIBUTION.GLTRANSACTIONID = T.GLTRANSACTIONID,    
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.CHANGEDBYID = @CHANGEAGENTID,
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.DATECHANGED = @CHANGEDATE
    from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION
    inner join @TempTbl T on BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID = T.ID

    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.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.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.ORGANIZATIONAMOUNT = (select ORGANIZATIONAMOUNT from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BASECURRENCYID = (select BASECURRENCYID from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONAMOUNT = (select TRANSACTIONAMOUNT from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONCURRENCYID = (select TRANSACTIONCURRENCYID from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BASEEXCHANGERATEID = (select BASEEXCHANGERATEID from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ORGANIZATIONEXCHANGERATEID = (select ORGANIZATIONEXCHANGERATEID from @TempTbl where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.CHANGEDBYID = @CHANGEAGENTID,
      BANKACCOUNTTRANSACTIONGLDISTRIBUTION.DATECHANGED = @CHANGEDATE
     where BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID in (select ID from @TempTbl)

    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.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 ID in (select GLTRANSACTIONID from @TempTbl)

    if @@Error <> 0
      return 3;

    --insert new rows

    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 GL where GL.ID = temp.GLTRANSACTIONID);

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

    if @@Error <> 0
      return 4;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

  return 0;