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.REVENUE R on R.ID = I.ID
where (D.POSTSTATUSCODE = 2 and R.DONOTPOST <> 1) or (D.POSTSTATUSCODE = 1 and R.DONOTPOST <> 0)) BEGIN
RAISERROR ('ERR_BANKACCOUNTDEPOSITPAYMENT_POSTSTATUS', 16, 1)
ROLLBACK
END
if exists(select I.DEPOSITID
from INSERTED I
inner join dbo.REVENUE 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.REVENUE 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.TRANSACTIONTYPECODE != 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;
--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 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
update dbo.BANKACCOUNTTRANSACTION
set
TRANSACTIONAMOUNT = BANKACCOUNTTRANSACTION.TRANSACTIONAMOUNT + T1.TRANSACTIONAMOUNT
,AMOUNT = BANKACCOUNTTRANSACTION.AMOUNT + T1.BASEAMOUNT
,ORGANIZATIONAMOUNT = BANKACCOUNTTRANSACTION.ORGANIZATIONAMOUNT + T1.ORGAMOUNT
from dbo.BANKACCOUNTTRANSACTION
inner join (
select
SUM(isnull(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end, 0)
) as [TRANSACTIONAMOUNT]
,SUM(isnull(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end, 0)
) as [BASEAMOUNT]
,SUM(isnull(
case when @ORGAMOUNTORIGINCODE = 1
then
case when BA.TRANSACTIONCURRENCYID = @ORGCURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
else
case when BAT.BASECURRENCYID = @ORGCURRENCYID
then
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, 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.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
end, 0)) as [ORGAMOUNT]
,D.ID
from INSERTED DP
inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = DP.DEPOSITID
inner join dbo.REVENUE 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
group by D.ID
) T1 on BANKACCOUNTTRANSACTION.ID = T1.ID
--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(COALESCE(REVENUE.TRANSACTIONAMOUNT,0)) AMOUNT, DELETED.CHANGEDBYID, DELETED.DATECHANGED
FROM DELETED
INNER JOIN dbo.BANKACCOUNTTRANSACTION DEPOSIT1 ON DELETED.DEPOSITID=DEPOSIT1.ID
INNER JOIN 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.BANKACCOUNTTRANSACTION
set
TRANSACTIONAMOUNT = BANKACCOUNTTRANSACTION.TRANSACTIONAMOUNT - T1.TRANSACTIONAMOUNT
,AMOUNT = BANKACCOUNTTRANSACTION.AMOUNT - T1.BASEAMOUNT
,ORGANIZATIONAMOUNT = BANKACCOUNTTRANSACTION.ORGANIZATIONAMOUNT - T1.ORGAMOUNT
from dbo.BANKACCOUNTTRANSACTION
inner join(
select
SUM(isnull(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end, 0)
) as [TRANSACTIONAMOUNT]
,SUM(isnull(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end, 0)
) as [BASEAMOUNT]
,SUM(isnull(
case when @ORGAMOUNTORIGINCODE = 1
then
case when BA.TRANSACTIONCURRENCYID = @ORGCURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
else
case when BAT.BASECURRENCYID = @ORGCURRENCYID
then
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, 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.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
end, 0)) as [ORGAMOUNT]
,D.ID
from DELETED DP
inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = DP.DEPOSITID
inner join dbo.REVENUE 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
group by D.ID
) T1 on BANKACCOUNTTRANSACTION.ID = T1.ID
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
