TR_FINANCIALTRANSACTION_BANK_CURRENCYCORRECTIONS
Definition
Copy
CREATE trigger [dbo].[TR_FINANCIALTRANSACTION_BANK_CURRENCYCORRECTIONS] on [dbo].[FINANCIALTRANSACTION] after insert, update, delete not for replication as
begin
set nocount on;
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORGANIZATIONAMOUNTORIGINCODE tinyint;
select @ORGANIZATIONAMOUNTORIGINCODE = @ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
begin try
if exists (select * from DELETED D
full outer join inserted as I on D.ID = I.ID
left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as BADP on D.ID = BADP.ID
inner join dbo.BANKACCOUNTDEPOSIT BAD on BAD.ID = coalesce(I.PARENTID, D.PARENTID, BADP.DEPOSITID)
where (
isnull(D.TRANSACTIONAMOUNT,0) != isnull(I.TRANSACTIONAMOUNT,0)
or isnull(D.TYPECODE,0)<>isnull(I.TYPECODE,0)
)
and D.TYPECODE in (24, 25, 0) or I.TYPECODE in (24, 25) -- Only look for BADepositCorrections, or revenue records.
--Inserted revenue can be ignored until it gets linked to a deposit
)
begin
update dbo.BANKACCOUNTDEPOSIT set
TOTALPAYMENTAMOUNT = TOTALPAYMENTAMOUNT - T1.AMOUNT
-- Boilerplate
,CHANGEDBYID = BANKACCOUNTDEPOSIT.CHANGEDBYID
,DATECHANGED = getdate()
from dbo.BANKACCOUNTDEPOSIT
inner join
(
select
coalesce(I.PARENTID, D.PARENTID, BADP.DEPOSITID) as DEPOSITID
,isnull(sum(case D.TYPECODE when 24 then -D.TRANSACTIONAMOUNT else D.TRANSACTIONAMOUNT end), 0)
- isnull(sum(case I.TYPECODE when 24 then -I.TRANSACTIONAMOUNT else I.TRANSACTIONAMOUNT end), 0) as AMOUNT
from inserted as I
full outer join deleted as D on I.ID = D.ID
left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as BADP on D.ID = BADP.ID -- If it's inserted it does not exist, on an update it could not have changed, so I join to deleted only
where I.TYPECODE in (24,25,0) or D.TYPECODE in (24,25,0)
group by coalesce(I.PARENTID, D.PARENTID, BADP.DEPOSITID)
) as T1 on T1.DEPOSITID = BANKACCOUNTDEPOSIT.ID
;
declare @DEPOSITAMOUNTS table (
DEPOSITID uniqueidentifier,
CURRENTDEPOSITTRANSACTIONAMOUNT money,
PREVIOUSDEPOSITTRANSACTIONAMOUNT money,
CURRENTDEPOSITBASEAMOUNT money,
PREVIOUSDEPOSITBASEAMOUNT money,
CURRENTDEPOSITORGANIZATIONAMOUNT money,
PREVIOUSDEPOSITORGANIZATIONAMOUNT money,
BANKACCOUNTTRANSACTIONCURRENCYID uniqueidentifier,
CURRENCYSETBASECURRENCYID uniqueidentifier,
BANKACCOUNTTRANSACTIONBASEEXCHANGERATEID uniqueidentifier,
BANKACCOUNTTRANSACTIONORGANIZATIONEXCHANGERATEID uniqueidentifier
)
--retrieve currency data and convert the payment transaction amounts to the deposit transaction currency.
insert into @DEPOSITAMOUNTS(DEPOSITID,CURRENTDEPOSITTRANSACTIONAMOUNT,PREVIOUSDEPOSITTRANSACTIONAMOUNT,BANKACCOUNTTRANSACTIONCURRENCYID,CURRENCYSETBASECURRENCYID,BANKACCOUNTTRANSACTIONBASEEXCHANGERATEID,BANKACCOUNTTRANSACTIONORGANIZATIONEXCHANGERATEID)
select
BANKACCOUNTDEPOSIT.ID,
case
when BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID then
case
when R.TYPECODE = 24 then -R.TRANSACTIONAMOUNT
else R.TRANSACTIONAMOUNT
end
else
case
when R.TYPECODE = 24 then dbo.UFN_CURRENCY_CONVERT(-R.TRANSACTIONAMOUNT, BANKACCOUNTDEPOSIT.TRANSACTIONEXCHANGERATEID)
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, BANKACCOUNTDEPOSIT.TRANSACTIONEXCHANGERATEID)
end
end as CURRENTDEPOSITTRANSACTIONAMOUNT,
case
when BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID then
case
when D.TYPECODE = 24 then -D.TRANSACTIONAMOUNT
else D.TRANSACTIONAMOUNT
end
else
case
when D.TYPECODE = 24 then dbo.UFN_CURRENCY_CONVERT(-D.TRANSACTIONAMOUNT, BANKACCOUNTDEPOSIT.TRANSACTIONEXCHANGERATEID)
else dbo.UFN_CURRENCY_CONVERT(D.TRANSACTIONAMOUNT, BANKACCOUNTDEPOSIT.TRANSACTIONEXCHANGERATEID)
end
end as PREVIOUSDEPOSITTRANSACTIONAMOUNT,
BANKACCOUNT.TRANSACTIONCURRENCYID,
CURRENCYSET.BASECURRENCYID,
FINANCIALTRANSACTION.BASEEXCHANGERATEID,
FINANCIALTRANSACTION.ORGEXCHANGERATEID
from
INSERTED R
full outer join deleted as D on R.ID = D.ID
left outer join dbo.BANKACCOUNTDEPOSITPAYMENT on D.ID = BANKACCOUNTDEPOSITPAYMENT.ID
inner join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSIT.ID = coalesce(R.PARENTID, D.PARENTID, BANKACCOUNTDEPOSITPAYMENT.DEPOSITID)
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = BANKACCOUNTDEPOSIT.ID
inner join dbo.BANKACCOUNTTRANSACTION_EXT on BANKACCOUNTDEPOSIT.ID = BANKACCOUNTTRANSACTION_EXT.ID
inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
inner join dbo.BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTTRANSACTION_EXT.BANKACCOUNTID
where
D.TYPECODE in (24,25,0) or R.TYPECODE in (24,25,0);
--Convert the deposit transaction amounts to deposit base currency.
update @DEPOSITAMOUNTS
set
CURRENTDEPOSITBASEAMOUNT =
case
when BANKACCOUNTTRANSACTIONCURRENCYID = CURRENCYSETBASECURRENCYID then CURRENTDEPOSITTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(CURRENTDEPOSITTRANSACTIONAMOUNT, BANKACCOUNTTRANSACTIONBASEEXCHANGERATEID)
end,
PREVIOUSDEPOSITBASEAMOUNT =
case
when BANKACCOUNTTRANSACTIONCURRENCYID = CURRENCYSETBASECURRENCYID then PREVIOUSDEPOSITTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(PREVIOUSDEPOSITTRANSACTIONAMOUNT, BANKACCOUNTTRANSACTIONBASEEXCHANGERATEID)
end
from
@DEPOSITAMOUNTS;
if @ORGANIZATIONAMOUNTORIGINCODE = 1
begin
--Convert the deposit transaction amounts to org currency.
update @DEPOSITAMOUNTS
set
CURRENTDEPOSITORGANIZATIONAMOUNT =
case
when BANKACCOUNTTRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID then CURRENTDEPOSITTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(CURRENTDEPOSITTRANSACTIONAMOUNT, BANKACCOUNTTRANSACTIONORGANIZATIONEXCHANGERATEID)
end,
PREVIOUSDEPOSITORGANIZATIONAMOUNT =
case
when BANKACCOUNTTRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID then PREVIOUSDEPOSITTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(PREVIOUSDEPOSITTRANSACTIONAMOUNT, BANKACCOUNTTRANSACTIONORGANIZATIONEXCHANGERATEID)
end
from
@DEPOSITAMOUNTS;
end
else
begin
--Convert the deposit base amounts to org currency.
update @DEPOSITAMOUNTS
set
CURRENTDEPOSITORGANIZATIONAMOUNT =
case
when CURRENCYSETBASECURRENCYID = @ORGANIZATIONCURRENCYID then CURRENTDEPOSITBASEAMOUNT
else dbo.UFN_CURRENCY_CONVERT(CURRENTDEPOSITBASEAMOUNT, BANKACCOUNTTRANSACTIONORGANIZATIONEXCHANGERATEID)
end,
PREVIOUSDEPOSITORGANIZATIONAMOUNT =
case
when CURRENCYSETBASECURRENCYID = @ORGANIZATIONCURRENCYID then PREVIOUSDEPOSITBASEAMOUNT
else dbo.UFN_CURRENCY_CONVERT(PREVIOUSDEPOSITBASEAMOUNT, BANKACCOUNTTRANSACTIONORGANIZATIONEXCHANGERATEID)
end
from
@DEPOSITAMOUNTS;
end
--update the deposit records
update dbo.FINANCIALTRANSACTION
set
TRANSACTIONAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT + DEPOSITS.DEPOSITTRANSACTIONAMOUNT,
BASEAMOUNT = FINANCIALTRANSACTION.BASEAMOUNT + DEPOSITS.DEPOSITBASEAMOUNT,
ORGAMOUNT = FINANCIALTRANSACTION.ORGAMOUNT + DEPOSITS.DEPOSITORGANIZATIONAMOUNT,
-- Boilerplate
DATECHANGED = getdate(),
CHANGEDBYID = FINANCIALTRANSACTION.CHANGEDBYID
from
dbo.FINANCIALTRANSACTION
inner join (
select
sum(isnull(CURRENTDEPOSITTRANSACTIONAMOUNT, 0) - isnull(PREVIOUSDEPOSITTRANSACTIONAMOUNT, 0)) as DEPOSITTRANSACTIONAMOUNT,
sum(isnull(CURRENTDEPOSITBASEAMOUNT, 0) - isnull(PREVIOUSDEPOSITBASEAMOUNT, 0)) as DEPOSITBASEAMOUNT,
sum(isnull(CURRENTDEPOSITORGANIZATIONAMOUNT, 0) - isnull(PREVIOUSDEPOSITORGANIZATIONAMOUNT, 0)) as DEPOSITORGANIZATIONAMOUNT,
DEPOSITID
from
@DEPOSITAMOUNTS
group by
DEPOSITID
) DEPOSITS on FINANCIALTRANSACTION.ID = DEPOSITS.DEPOSITID;
update dbo.FINANCIALTRANSACTIONLINEITEM
set
BASEAMOUNT = FT.BASEAMOUNT
,ORGAMOUNT = FT.ORGAMOUNT
,TRANSACTIONAMOUNT = FT.TRANSACTIONAMOUNT
-- Boilerplate
,CHANGEDBYID = FT.CHANGEDBYID
,DATECHANGED = getdate()
from
(select BAD.ID
from inserted R
full outer join deleted as D on R.ID = D.ID
left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as BADP on D.ID = BADP.ID
inner join dbo.BANKACCOUNTDEPOSIT BAD on BAD.ID = coalesce(R.PARENTID, D.PARENTID, BADP.DEPOSITID)
inner join dbo.BANKACCOUNTTRANSACTION_EXT BAT on BAT.ID = BAD.ID
inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
where D.TYPECODE in (24,25,0) or R.TYPECODE in (24,25,0)
group by BAD.ID
) as IDS
inner join dbo.FINANCIALTRANSACTION as FT on IDS.ID = FT.ID-- Deposits have only one line item!
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.FINANCIALTRANSACTIONID = FT.ID
where
FTLI.TYPECODE = 0
and FTLI.DELETEDON is null;
-- delete links to deposits for payments that have been deleted
delete BANKACCOUNTDEPOSITPAYMENT
from dbo.BANKACCOUNTDEPOSITPAYMENT
inner join deleted as D on BANKACCOUNTDEPOSITPAYMENT.ID = D.ID
left outer join inserted as I on D.ID = I.ID
where (I.ID is null or D.DELETEDON is not null) and D.TYPECODE = 0
end;
end try
begin catch
--Catch the arithmetic overflow error and re-raise a prettier one
if error_number() = 8115
begin
--NOTE: the following error message needs to be revisited
raiserror ('The maximum supported deposit amount has been exceeded.', 16, 1);
rollback transaction;
end
else
exec dbo.USP_RAISE_ERROR;
end catch
end