CREATE trigger [dbo].[TR_BANKACCOUNTDEPOSITPAYMENT_IUD] ON [dbo].[BANKACCOUNTDEPOSITPAYMENT] FOR INSERT,UPDATE,DELETE NOT FOR REPLICATION AS
--NOTE: if making changes to this trigger see also the trigger(s) on the revenue table
-- to and make sure they are in synch
BEGIN
SET NOCOUNT ON
--see if the original deposit is locked - if so, raise an error
if exists(Select DEPOSITID from DELETED
INNER JOIN DBO.BANKACCOUNTDEPOSIT ON BANKACCOUNTDEPOSIT.ID=DELETED.DEPOSITID
INNER JOIN DBO.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID=DELETED.DEPOSITID
where DELETED.DEPOSITID is not null AND BANKACCOUNTDEPOSIT.STATUSCODE=0 AND BANKACCOUNTTRANSACTION.POSTSTATUSCODE != 0) BEGIN
RAISERROR ('Payments cannot be removed from locked deposits.', 16, 1)
ROLLBACK
END
--see if the new deposit is locked - if so, raise an error
if exists(Select DEPOSITID from INSERTED INNER JOIN DBO.BANKACCOUNTDEPOSIT ON BANKACCOUNTDEPOSIT.ID=INSERTED.DEPOSITID where
INSERTED.DEPOSITID is not null AND BANKACCOUNTDEPOSIT.STATUSCODE=0) BEGIN
RAISERROR ('Payments cannot be added to locked deposits.', 16, 1)
ROLLBACK
END
--see if the original bank is closed - if so, raise an error
if exists(Select DEPOSITID from DELETED INNER JOIN DBO.BANKACCOUNTTRANSACTION DEPOSIT1 ON
DELETED.DEPOSITID=DEPOSIT1.ID INNER JOIN DBO.BANKACCOUNT ON DEPOSIT1.BANKACCOUNTID=BANKACCOUNT.ID where
BANKACCOUNT.STATUSCODE=0) BEGIN
RAISERROR ('Payments cannot be removed from deposits associated with closed bank accounts.', 16, 1)
ROLLBACK
END
--see if the new bank is closed - if so, raise an error
if exists(Select DEPOSITID from INSERTED INNER JOIN DBO.BANKACCOUNTTRANSACTION DEPOSIT1 ON
INSERTED.DEPOSITID=DEPOSIT1.ID INNER JOIN DBO.BANKACCOUNT ON DEPOSIT1.BANKACCOUNTID=BANKACCOUNT.ID where
BANKACCOUNT.STATUSCODE=0) BEGIN
RAISERROR ('Payments cannot be added to deposits associated with closed bank accounts.', 16, 1)
ROLLBACK
END
--see if the payment method is invalid for the new deposit - if so, raise an error
if exists(Select DEPOSITID from INSERTED INNER JOIN DBO.BANKACCOUNTTRANSACTION DEPOSIT1 ON
INSERTED.DEPOSITID=DEPOSIT1.ID where [dbo].[UFN_BANKACCOUNTDEPOSIT_VALIDPAYMENTTYPE](INSERTED.DEPOSITID, INSERTED.ID)=0) BEGIN
RAISERROR ('The selected payment method is not allowed for the deposit specified.', 16, 1)
ROLLBACK
END
--Verify that the deposit and payment have the same post status
if exists(select I.DEPOSITID
from INSERTED I
inner join dbo.BANKACCOUNTTRANSACTION D on D.ID = I.DEPOSITID
inner join dbo.FINANCIALTRANSACTION R on R.ID = I.ID
where (D.POSTSTATUSCODE = 2 and R.POSTSTATUSCODE <> 3) or (D.POSTSTATUSCODE = 1 and R.POSTSTATUSCODE = 3)) BEGIN
RAISERROR ('ERR_BANKACCOUNTDEPOSITPAYMENT_POSTSTATUS', 16, 1)
ROLLBACK
END
if exists(select I.DEPOSITID
from INSERTED I
inner join dbo.FINANCIALTRANSACTION R on R.ID = I.ID
inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = I.DEPOSITID
where R.TRANSACTIONCURRENCYID <> D.TRANSACTIONCURRENCYID)
BEGIN
RAISERROR ('ERR_BANKACCOUNTDEPOSITPAYMENT_TRANSACTIONCURRENCYIDMUSTMATCH', 16, 1)
ROLLBACK
END
if exists(select I.ID
from INSERTED I
inner join dbo.FINANCIALTRANSACTION R on R.ID = I.ID
inner join dbo.REVENUEPAYMENTMETHOD P on R.ID = P.REVENUEID
left outer join dbo.OTHERPAYMENTMETHODDETAIL O on P.ID = O.ID
left outer join dbo.NONDEPOSITABLEPAYMENTMETHOD N on O.OTHERPAYMENTMETHODCODEID = N.ID
where (R.TYPECODE != 0) or P.PAYMENTMETHODCODE not in (0,1,2,10) or
(P.PAYMENTMETHODCODE = 10 and N.ID is not null)
)
BEGIN
RAISERROR ('The selected payment method is not allowed for deposits.', 16, 1)
ROLLBACK
END
if exists(select I.ID
from INSERTED I
inner join dbo.PDACCOUNTSYSTEMFORREVENUE R on R.ID = I.ID
inner join dbo.BANKACCOUNTTRANSACTION BT on I.DEPOSITID = BT.ID
inner join dbo.BANKACCOUNT B on BT.BANKACCOUNTID = B.ID
where R.PDACCOUNTSYSTEMID != B.PDACCOUNTSYSTEMID
)
BEGIN
RAISERROR ('Payment and deposit must be in the same accounting system.', 16, 1)
ROLLBACK
END
--*********************************************
--place all error conditions ABOVE this comment
--*********************************************
BEGIN TRY
declare @ORGAMOUNTORIGINCODE tinyint;
declare @ORGCURRENCYID uniqueidentifier;
select @ORGAMOUNTORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
select @ORGCURRENCYID = ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;
if exists ( select * from inserted)
begin
--update the new deposit's payment amount
update dbo.BANKACCOUNTDEPOSIT
set
TOTALPAYMENTAMOUNT = DEPOSIT2.TOTALPAYMENTAMOUNT + T1.AMOUNT
,CHANGEDBYID = T1.CHANGEDBYID
,DATECHANGED = T1.DATECHANGED
from (select INSERTED.DEPOSITID, SUM(COALESCE(REVENUE.TRANSACTIONAMOUNT,0)) AMOUNT, INSERTED.CHANGEDBYID, INSERTED.DATECHANGED
from INSERTED
inner join dbo.BANKACCOUNTTRANSACTION DEPOSIT1 on INSERTED.DEPOSITID=DEPOSIT1.ID
inner join FINANCIALTRANSACTION REVENUE on INSERTED.ID=REVENUE.ID
group by INSERTED.DEPOSITID, INSERTED.CHANGEDBYID, INSERTED.DATECHANGED) T1
inner join dbo.BANKACCOUNTDEPOSIT DEPOSIT2 on T1.DEPOSITID =DEPOSIT2.ID
--update the new deposit's multicurrency amounts
declare @DEPOSITAMOUNTS table (
DEPOSITID uniqueidentifier,
DEPOSITTRANSACTIONAMOUNT money,
DEPOSITBASEAMOUNT money,
DEPOSITORGANIZATIONAMOUNT money,
BANKACCOUNTTRANSACTIONCURRENCYID uniqueidentifier,
BANKACCOUNTTRANSACTIONBASECURRENCYID uniqueidentifier,
BANKACCOUNTTRANSACTIONBASEEXCHANGERATEID uniqueidentifier,
BANKACCOUNTTRANSACTIONORGANIZATIONEXCHANGERATEID uniqueidentifier,
ISINSERTEDRECORD bit
)
--retrieve currency data and convert the payment transaction amounts to the deposit transaction currency.
insert into @DEPOSITAMOUNTS(DEPOSITID,DEPOSITTRANSACTIONAMOUNT,BANKACCOUNTTRANSACTIONCURRENCYID,BANKACCOUNTTRANSACTIONBASECURRENCYID,BANKACCOUNTTRANSACTIONBASEEXCHANGERATEID,BANKACCOUNTTRANSACTIONORGANIZATIONEXCHANGERATEID,ISINSERTEDRECORD)
select
D.ID,
case
when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID) --This will return 0 when the exchange rate is null.
end,
BA.TRANSACTIONCURRENCYID,
BAT.BASECURRENCYID,
BAT.BASEEXCHANGERATEID,
BAT.ORGANIZATIONEXCHANGERATEID,
1
from
INSERTED DP
inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = DP.DEPOSITID
inner join dbo.FINANCIALTRANSACTION R on R.ID = DP.ID
inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = D.ID
inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
union all
select
D.ID,
case
when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID) --This will return 0 when the exchange rate is null.
end,
BA.TRANSACTIONCURRENCYID,
BAT.BASECURRENCYID,
BAT.BASEEXCHANGERATEID,
BAT.ORGANIZATIONEXCHANGERATEID,
0
from
DELETED DP
inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = DP.DEPOSITID
inner join dbo.FINANCIALTRANSACTION R on R.ID = DP.ID
inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = D.ID
inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID;
--Convert the deposit transaction amounts to deposit base currency.
update @DEPOSITAMOUNTS
set DEPOSITBASEAMOUNT =
case
when BANKACCOUNTTRANSACTIONCURRENCYID = BANKACCOUNTTRANSACTIONBASECURRENCYID then DEPOSITTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DEPOSITTRANSACTIONAMOUNT, BANKACCOUNTTRANSACTIONBASEEXCHANGERATEID) --This will return 0 when the exchange rate is null.
end
from
@DEPOSITAMOUNTS;
if @ORGAMOUNTORIGINCODE = 1
begin
--Convert the deposit transaction amounts to org currency.
update @DEPOSITAMOUNTS
set DEPOSITORGANIZATIONAMOUNT =
case
when BANKACCOUNTTRANSACTIONCURRENCYID = @ORGCURRENCYID then DEPOSITTRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DEPOSITTRANSACTIONAMOUNT, BANKACCOUNTTRANSACTIONORGANIZATIONEXCHANGERATEID) --This will return 0 when the exchange rate is null.
end
from
@DEPOSITAMOUNTS;
end
else
begin
--Convert the deposit base amounts to org currency.
update @DEPOSITAMOUNTS
set DEPOSITORGANIZATIONAMOUNT =
case
when BANKACCOUNTTRANSACTIONBASECURRENCYID = @ORGCURRENCYID then DEPOSITBASEAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DEPOSITBASEAMOUNT, BANKACCOUNTTRANSACTIONORGANIZATIONEXCHANGERATEID) --This will return 0 when the exchange rate is null.
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 = FINANCIALTRANSACTION.DATECHANGED
,CHANGEDBYID = FINANCIALTRANSACTION.CHANGEDBYID
from
dbo.FINANCIALTRANSACTION
inner join (
select
sum(DEPOSITTRANSACTIONAMOUNT) as DEPOSITTRANSACTIONAMOUNT,
sum(DEPOSITBASEAMOUNT) as DEPOSITBASEAMOUNT,
sum(DEPOSITORGANIZATIONAMOUNT) as DEPOSITORGANIZATIONAMOUNT,
DEPOSITID
from
@DEPOSITAMOUNTS
where
ISINSERTEDRECORD = 1
group by
DEPOSITID
)DEPOSITS on FINANCIALTRANSACTION.ID = DEPOSITS.DEPOSITID
--update the new deposit's multicurrency amounts
update dbo.FINANCIALTRANSACTIONLINEITEM
set
TRANSACTIONAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT
,BASEAMOUNT = FINANCIALTRANSACTION.BASEAMOUNT
,ORGAMOUNT = FINANCIALTRANSACTION.ORGAMOUNT
-- Boilerplage
,DATECHANGED = FINANCIALTRANSACTION.DATECHANGED
,CHANGEDBYID = FINANCIALTRANSACTION.CHANGEDBYID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join (
select
D.ID
from INSERTED DP
inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = DP.DEPOSITID
group by D.ID
) T1 on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = T1.ID
end
if exists (select * from deleted)
begin
--update the old deposit's payment amount
update dbo.BANKACCOUNTDEPOSIT
set
TOTALPAYMENTAMOUNT = DEPOSIT2.TOTALPAYMENTAMOUNT - T1.AMOUNT
,CHANGEDBYID = T1.CHANGEDBYID
,DATECHANGED = T1.DATECHANGED
from (select DELETED.DEPOSITID, sum(isnull(REVENUE.TRANSACTIONAMOUNT,0)) AMOUNT, DELETED.CHANGEDBYID, DELETED.DATECHANGED
from DELETED
inner join dbo.BANKACCOUNTTRANSACTION DEPOSIT1 on DELETED.DEPOSITID=DEPOSIT1.ID
inner join FINANCIALTRANSACTION REVENUE on DELETED.ID=REVENUE.ID
group by DELETED.DEPOSITID, DELETED.CHANGEDBYID, DELETED.DATECHANGED) T1
inner join dbo.BANKACCOUNTDEPOSIT DEPOSIT2 on T1.DEPOSITID =DEPOSIT2.ID
--update the old deposit's multicurrency amounts
update dbo.FINANCIALTRANSACTION
set
TRANSACTIONAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT - DEPOSITS.DEPOSITTRANSACTIONAMOUNT
,BASEAMOUNT = FINANCIALTRANSACTION.BASEAMOUNT - DEPOSITS.DEPOSITBASEAMOUNT
,ORGAMOUNT = FINANCIALTRANSACTION.ORGAMOUNT - DEPOSITS.DEPOSITORGANIZATIONAMOUNT
-- Boilerplate
,DATECHANGED = FINANCIALTRANSACTION.DATECHANGED
,CHANGEDBYID = FINANCIALTRANSACTION.CHANGEDBYID
from
dbo.FINANCIALTRANSACTION
inner join (
select
sum(DEPOSITTRANSACTIONAMOUNT) as DEPOSITTRANSACTIONAMOUNT,
sum(DEPOSITBASEAMOUNT) as DEPOSITBASEAMOUNT,
sum(DEPOSITORGANIZATIONAMOUNT) as DEPOSITORGANIZATIONAMOUNT,
DEPOSITID
from
@DEPOSITAMOUNTS
where
ISINSERTEDRECORD = 0
group by
DEPOSITID
)DEPOSITS on FINANCIALTRANSACTION.ID = DEPOSITS.DEPOSITID
--update the old deposit's multicurrency amounts
update dbo.FINANCIALTRANSACTIONLINEITEM
set
TRANSACTIONAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT
,BASEAMOUNT = FINANCIALTRANSACTION.BASEAMOUNT
,ORGAMOUNT = FINANCIALTRANSACTION.ORGAMOUNT
-- Boilerplage
,DATECHANGED = FINANCIALTRANSACTION.DATECHANGED
,CHANGEDBYID = FINANCIALTRANSACTION.CHANGEDBYID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join(
select
D.ID
from DELETED DP
inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = DP.DEPOSITID
group by D.ID
) T1 on FINANCIALTRANSACTION.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
|
Trigger Definition
