TR_BANKACCOUNTDEPOSITCORRECTION_CURRENCY
Definition
Copy
CREATE trigger dbo.TR_BANKACCOUNTDEPOSITCORRECTION_CURRENCY on dbo.BANKACCOUNTDEPOSITCORRECTION after insert, update, delete not for replication as
begin
set nocount on;
if exists(Select 1
from INSERTED I
inner join dbo.BANKACCOUNTDEPOSIT D ON I.DEPOSITID=D.ID
where DEFAULTALLOWEDPAYMENTMETHODS & (CASE I.PAYMENTMETHODCODE WHEN 0 THEN 2 WHEN 1 THEN 1 WHEN 2 THEN 8 WHEN 3 THEN 32 END) = 0)
begin
RAISERROR ('ERR_BANKACCOUNTDEPOSITCORRECTION_ALLOWEDPAYMENTMETHOD', 16, 1)
ROLLBACK
end
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORGANIZATIONAMOUNTORIGINCODE tinyint;
select @ORGANIZATIONAMOUNTORIGINCODE = @ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
-- For multicurrency you need a corporate rate to populate the organization amount. If a
-- corporate rate does not exist then your organization amount will be zero which is data corruption
-- so we will not allow you to enter a transaction without a corporate rate.
if exists
(select * from INSERTED as I
inner join dbo.BANKACCOUNTDEPOSIT as BAD on I.DEPOSITID = BAD.ID
where
((BAD.TRANSACTIONCURRENCYID is not null and @ORGANIZATIONAMOUNTORIGINCODE = 1 and BAD.TRANSACTIONCURRENCYID <> @ORGANIZATIONCURRENCYID)
or
(I.BASECURRENCYID is not null and @ORGANIZATIONAMOUNTORIGINCODE = 0 and I.BASECURRENCYID <> @ORGANIZATIONCURRENCYID))
and
I.ORGANIZATIONEXCHANGERATEID is null)
begin
raiserror('ERR_CORPORATERATE_RATE_REQUIRED', 16, 1)
rollback transaction
end
if update(AMOUNT)
begin
update dbo.BANKACCOUNTDEPOSITCORRECTION
set
BANKACCOUNTDEPOSITCORRECTION.TRANSACTIONAMOUNT = BADC.AMOUNT
,BANKACCOUNTDEPOSITCORRECTION.ORGANIZATIONAMOUNT = BADC.AMOUNT
,BANKACCOUNTDEPOSITCORRECTION.BASECURRENCYID = @ORGANIZATIONCURRENCYID
,BANKACCOUNTDEPOSITCORRECTION.CHANGEDBYID = BADC.CHANGEDBYID
,BANKACCOUNTDEPOSITCORRECTION.DATECHANGED = BADC.DATECHANGED
from inserted I
inner join dbo.BANKACCOUNTDEPOSITCORRECTION as BADC on I.ID = BADC.ID
inner join dbo.BANKACCOUNTDEPOSIT as BAD on BAD.ID = I.DEPOSITID
where
(BADC.ORGANIZATIONEXCHANGERATEID is null)
and (BADC.BASECURRENCYID is null or BADC.BASECURRENCYID = @ORGANIZATIONCURRENCYID)
and (BADC.BASEEXCHANGERATEID is null)
and (BAD.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID)
and
(
(BADC.TRANSACTIONAMOUNT is null or BADC.TRANSACTIONAMOUNT <> BADC.AMOUNT)
or (BADC.ORGANIZATIONAMOUNT is null or BADC.ORGANIZATIONAMOUNT <> BADC.AMOUNT)
or BADC.AMOUNT = 0
)
end
BEGIN TRY
If exists (select * from DELETED D
full outer join BANKACCOUNTDEPOSITCORRECTION I on D.ID = I.ID
left outer join INSERTED on INSERTED.ID = I.ID
where isnull(D.SIGNEDTRANSACTIONAMOUNT,0) != isnull(I.SIGNEDTRANSACTIONAMOUNT,0))
BEGIN
update dbo.BANKACCOUNTDEPOSIT set
TOTALPAYMENTAMOUNT = TOTALPAYMENTAMOUNT - T1.AMOUNT
,CHANGEDBYID = BANKACCOUNTDEPOSIT.CHANGEDBYID
,DATECHANGED = BANKACCOUNTDEPOSIT.DATECHANGED
from dbo.BANKACCOUNTDEPOSIT
inner join (select isnull(I.DEPOSITID, D.DEPOSITID) as DEPOSITID, isnull(D.SIGNEDTRANSACTIONAMOUNT, 0) - isnull(I.SIGNEDTRANSACTIONAMOUNT, 0) as AMOUNT
from BANKACCOUNTDEPOSITCORRECTION I
inner join INSERTED on INSERTED.ID = I.ID
full outer join DELETED D on I.ID = D.ID) T1 on T1.DEPOSITID = BANKACCOUNTDEPOSIT.ID
update dbo.BANKACCOUNTTRANSACTION set
TRANSACTIONAMOUNT = BANKACCOUNTTRANSACTION.TRANSACTIONAMOUNT + T1.TRANSACTIONAMOUNT
,AMOUNT = BANKACCOUNTTRANSACTION.AMOUNT + T1.BASEAMOUNT
,ORGANIZATIONAMOUNT = BANKACCOUNTTRANSACTION.ORGANIZATIONAMOUNT + T1.ORGAMOUNT
,CHANGEDBYID = BANKACCOUNTTRANSACTION.CHANGEDBYID
,DATECHANGED = BANKACCOUNTTRANSACTION.DATECHANGED
from dbo.BANKACCOUNTTRANSACTION
inner join (
select
SUM(isnull(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end, 0)
- isnull(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end, 0)
) as [TRANSACTIONAMOUNT]
,SUM(isnull(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end, 0)
- isnull(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end, 0)
) as [BASEAMOUNT]
,SUM(isnull(
case when @ORGANIZATIONAMOUNTORIGINCODE = 1
then
case when BA.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
else
case when BAT.BASECURRENCYID = @ORGANIZATIONCURRENCYID
then
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
end, 0)
- isnull(
case when @ORGANIZATIONAMOUNTORIGINCODE = 1
then
case when BA.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
else
case when BAT.BASECURRENCYID = @ORGANIZATIONCURRENCYID
then
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.SIGNEDTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.SIGNEDTRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
end, 0)) as [ORGAMOUNT]
,D.ID
from INSERTED R
full outer join DELETED on R.ID = DELETED.ID
inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = isnull(R.DEPOSITID, DELETED.DEPOSITID)
inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = D.ID
inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
group by D.ID
) T1 on BANKACCOUNTTRANSACTION.ID = T1.ID
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