CREATE trigger TR_FINANCIALTRANSACTIONLINEITEM_IUD on dbo.FINANCIALTRANSACTIONLINEITEM for insert, update, delete not for replication
as begin
set nocount on;
-------------------------------------
-- UPDATES TO DATA
-------------------------------------
if exists(select 1 from deleted) and not exists(select 1 from inserted)
begin
-------------------------------
-- DELETED
-------------------------------
delete from dbo.BBNCREVENUESPLITIDMAP
where REVENUESPLITID in (select ID from deleted)
end;
-------------------------------------
-- VALIDATION
-------------------------------------
if exists ( select 1 from inserted ) and not exists ( select 1 from deleted )
begin
-- No new line items are allowed on a deleted transaction
if exists(
select inserted.ID
from inserted
inner join dbo.FINANCIALTRANSACTION
on inserted.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where (not FINANCIALTRANSACTION.DELETEDON is null)
)
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_NONEWLINEITEMSONDELETEDTRANSACTION', 13,1);
end;
--Validate that the line items total to the financial transaction amount.
if exists(
select FT.ID
from dbo.FINANCIALTRANSACTION FT
left outer join dbo.FINANCIALTRANSACTIONLINEITEM FTLI
on FT.ID = FTLI.FINANCIALTRANSACTIONID and (FTLI.TYPECODE = 0) and (FTLI.DELETEDON IS NULL)
where FT.TYPECODE>100
and FT.DELETEDON is NULL
and (
FT.ID in (select FINANCIALTRANSACTIONID from INSERTED)
or FT.ID in (select FINANCIALTRANSACTIONID from DELETED)
)
group by FT.ID, FT.TRANSACTIONAMOUNT
having FT.TRANSACTIONAMOUNT != SUM(isnull(FTLI.TRANSACTIONAMOUNT, 0))
)
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_TOTALAMOUNT', 13,1);
-- Verify that when no currency exchange has been specified, the amounts are the same.
if update(TRANSACTIONAMOUNT) or update(ORGAMOUNT) or update(BASEAMOUNT)
begin
declare @ORGORIGIN tinyint; -- 1 means use transaction amount, other means use base
select @ORGORIGIN = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
if exists(
select I.ID
from inserted as I
inner join dbo.FINANCIALTRANSACTION as FT on I.FINANCIALTRANSACTIONID = FT.ID
where
FT.TYPECODE>100 and
(
(FT.BASEEXCHANGERATEID is null
and I.TRANSACTIONAMOUNT<>I.BASEAMOUNT
)
or
(FT.ORGEXCHANGERATEID is null
and (
(@ORGORIGIN=1 and I.TRANSACTIONAMOUNT <> I.ORGAMOUNT)
or (@ORGORIGIN<>1 and I.BASEAMOUNT <> I.ORGAMOUNT)
)
)
)
)
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_AMOUNTMISMATCH',16,1);
if exists ( select 1 from inserted )
begin
-------------------------------------
-- UPDATED, INSERTED
-------------------------------------
-- Verify that the total for the BASEAMOUNT matches what it should be...
if update(BASEAMOUNT) and exists(
select FT.ID from
(
select FT.ID, FT.TRANSACTIONCURRENCYID, FT.BASEEXCHANGERATEID, FT.TRANSACTIONAMOUNT,
SUM(FTLI.BASEAMOUNT) LIBASEAMOUNT
from dbo.FINANCIALTRANSACTION FT
left outer join dbo.FINANCIALTRANSACTIONLINEITEM FTLI
on FT.ID = FTLI.FINANCIALTRANSACTIONID and (FTLI.TYPECODE = 0) and (FTLI.DELETEDON IS NULL)
where FT.TYPECODE>100
and (
FT.ID in (select FINANCIALTRANSACTIONID from INSERTED)
or FT.ID in (select FINANCIALTRANSACTIONID from DELETED)
)
group by FT.ID, FT.TRANSACTIONAMOUNT, FT.BASEEXCHANGERATEID, FT.TRANSACTIONCURRENCYID
) as FT
inner join dbo.CURRENCY as C on FT.TRANSACTIONCURRENCYID = C.ID
left outer join dbo.CURRENCYEXCHANGERATE as X on FT.BASEEXCHANGERATEID = X.ID
where
dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(FT.TRANSACTIONAMOUNT,coalesce(X.RATE,1)),C.DECIMALDIGITS,C.ROUNDINGTYPECODE) <> FT.LIBASEAMOUNT
)
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_TOTALBASEAMOUNT',16,1);
-- Verify that the total for ORGAMOUNT matches what it should be...
if update(ORGAMOUNT) and exists(
select FT.ID from
(
select FT.ID, FT.TRANSACTIONCURRENCYID, FT.ORGEXCHANGERATEID, FT.TRANSACTIONAMOUNT, SUM(FTLI.ORGAMOUNT) LIORGAMOUNT, SUM(FTLI.BASEAMOUNT) LIBASEAMOUNT
from dbo.FINANCIALTRANSACTION FT
left outer join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID and (FTLI.TYPECODE = 0) and (FTLI.DELETEDON IS NULL)
where FT.TYPECODE>100 and (FT.ID in (select FINANCIALTRANSACTIONID from INSERTED) or FT.ID in (select FINANCIALTRANSACTIONID from DELETED))
group by FT.ID, FT.TRANSACTIONAMOUNT, FT.ORGEXCHANGERATEID, FT.TRANSACTIONCURRENCYID
) as FT
inner join dbo.CURRENCY as C on FT.TRANSACTIONCURRENCYID = C.ID
left outer join dbo.CURRENCYEXCHANGERATE as X on FT.ORGEXCHANGERATEID = X.ID
where
(@ORGORIGIN = 1 and dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(FT.TRANSACTIONAMOUNT,coalesce(X.RATE,1)),C.DECIMALDIGITS,C.ROUNDINGTYPECODE) <> FT.LIORGAMOUNT)
or
(@ORGORIGIN<> 1 and dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(FT.LIBASEAMOUNT,coalesce(X.RATE,1)),C.DECIMALDIGITS,C.ROUNDINGTYPECODE) <> FT.LIORGAMOUNT)
)
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_TOTALORGAMOUNT',16,1);
end;
--Post date is required under certain conditions
if exists(
select FT.ID
from INSERTED I
inner join dbo.FINANCIALTRANSACTION FT on I.FINANCIALTRANSACTIONID = FT.ID
where (I.POSTDATE is null) and (FT.TYPECODE <> 103) and ((I.POSTSTATUSCODE = 1) or (I.POSTSTATUSCODE = 2))
)
raiserror('CK_FINANCIALTRANSACTIONLINEITEM_POSTDATE_REQUIRED', 13,1);
-- Validate post date in an open period
if update(POSTDATE) or update(POSTSTATUSCODE)
begin
if not exists(select ID from dbo.INSTALLEDPRODUCTLIST where ID = '8D162D9F-D8E6-47D2-AA49-22BA137AAA48') --Legacy off, so we have to be using BasicGL
begin
if exists( select * from
inserted as I
left outer join dbo.GLFISCALPERIOD P on I.POSTDATE <= P.ENDDATE and I.POSTDATE>=P.STARTDATE
where I.POSTSTATUSCODE = 1 and I.POSTDATE is not null
and ISNULL(P.CLOSED,1)<>0
)
begin
declare @postDate as datetime;
select top 1
@postDate = I.POSTDATE
from inserted as I
left outer join dbo.GLFISCALPERIOD P
on I.POSTDATE <= P.ENDDATE and I.POSTDATE>=P.STARTDATE
where
I.POSTSTATUSCODE = 1 and I.POSTDATE is not null
and ISNULL(P.CLOSED,1)<>0
;
declare @error as nvarchar(max);
set @error = 'Post date (' + convert(nvarchar,@postDate,101) + ') must be in an open period.';
raiserror (@error, 16, 1);
end;
end;
end;
-- Validate that the reversed line item is valid
if exists(
select inserted.ID
from inserted
left outer join dbo.FINANCIALTRANSACTIONLINEITEM
on inserted.REVERSEDLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
where ((inserted.ID != FINANCIALTRANSACTIONLINEITEM.ID) or (FINANCIALTRANSACTIONLINEITEM.ID is null))
and -- Linked line item is not part of the same transaction (error case)
(
((inserted.FINANCIALTRANSACTIONID != FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID)
and (not FINANCIALTRANSACTIONLINEITEM.ID is null)
) or
-- not a reversal with a linked line item (error case)
-- ((inserted.TYPECODE != 1) and (not FINANCIALTRANSACTIONLINEITEM.ID is null)) or
-- A reversal without a linked line item, or the linked item is not deleted, or the linked item is of type reversal (you cannot reverse a reversal)
-- ((inserted.TYPECODE = 1) and ((FINANCIALTRANSACTIONLINEITEM.ID is null) or (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) or (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 1))))
-- we allow linked item to be not deleted
-- A reversal without a linked line item, or the linked item is of type reversal (you cannot reverse a reversal)
((inserted.TYPECODE = 1) and ((FINANCIALTRANSACTIONLINEITEM.ID is null) or (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 1)))
)
)
raiserror('ERR_FINANCIALTRANSACTIONLINEITEM_REVERSEDLINEITEMID', 13,1);
-- Validate that the applications for this line item are valid
-- If line item is of type application and is being marked as posted without the source and target line item being posted.
if (exists(
select inserted.ID
from inserted
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_SOURCE
on inserted.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM_SOURCE.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_TARGET
on inserted.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM_TARGET.ID
where
inserted.TYPECODE = 2 and --TypeCode 2 = Application
(inserted.POSTSTATUSCODE = 2)
and ((FINANCIALTRANSACTIONLINEITEM_SOURCE.POSTSTATUSCODE != 2) or (FINANCIALTRANSACTIONLINEITEM_TARGET.POSTSTATUSCODE != 2))))
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_APPLICATIONSOURCEORTARGETNOTPOSTED', 13,1);
-- if the amount of the applications is larger than the amount of the source line item
if (exists(
select inserted.ID
from inserted inner join dbo.FINANCIALTRANSACTIONLINEITEM on inserted.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI2 on inserted.SOURCELINEITEMID = FTLI2.SOURCELINEITEMID and FTLI2.DELETEDON is null
where inserted.TYPECODE = 2
group by inserted.ID, FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
having sum(FTLI2.TRANSACTIONAMOUNT) > FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT))
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_APPLICATIONSTOTALVALID', 13,1);
-- if the amount of the applications is larger than the amount of the target line item
if (exists(
select inserted.ID
from inserted inner join dbo.FINANCIALTRANSACTIONLINEITEM on inserted.TARGETLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI2 on inserted.TARGETLINEITEMID = FTLI2.TARGETLINEITEMID and FTLI2.DELETEDON is null
where inserted.TYPECODE = 2
group by inserted.ID, FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
having sum(FTLI2.TRANSACTIONAMOUNT) > FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT))
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_APPLICATIONSTOTALVALID', 13,1);
end;
if exists( select 1 from deleted) and exists ( select 1 from inserted )
begin
-------------------------------------
-- UPDATED
-------------------------------------
if update(POSTSTATUSCODE) or update(POSTDATE) -- Cannot change POSTSTATUSCODE, POSTDATE on posted items unless we are reversing
if exists(
select D.ID
from INSERTED I
inner join DELETED D ON I.ID = D.ID
left join FINANCIALTRANSACTIONLINEITEM LI ON LI.REVERSEDLINEITEMID = I.ID
where
(( /*it's posted*/
D.POSTSTATUSCODE=2 and
(D.POSTSTATUSCODE <> I.POSTSTATUSCODE
or D.POSTDATE <> I.POSTDATE)
) and (LI.ID is null))
)
raiserror('ERR_FINANCIALTRANSACTION_LOCKEDFIELDS', 13,1);
if update(DELETEDON)
begin
if exists (select 1 from dbo.INSTALLMENTSPLITPAYMENT inner join inserted on INSTALLMENTSPLITPAYMENT.PAYMENTID = inserted.ID where DELETEDON is not null)
delete INSTALLMENTSPLITPAYMENT from dbo.INSTALLMENTSPLITPAYMENT inner join inserted on INSTALLMENTSPLITPAYMENT.PAYMENTID = inserted.ID where DELETEDON is not null
if exists (select 1 from inserted where DELETEDON is not null and POSTSTATUSCODE = 2 and REVERSEDLINEITEMID is null and not exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM where REVERSEDLINEITEMID = inserted.ID and TYPECODE = 1))
begin
insert into dbo.FINANCIALTRANSACTIONLINEITEM (ID, FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, VISIBLE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TYPECODE, POSTDATE, POSTSTATUSCODE, REVERSEDLINEITEMID, DELETEDON, BASEAMOUNT, ORGAMOUNT, QUANTITY, UNITVALUE, FINANCIALTRANSACTIONLINEITEMADJUSTMENTID)
select newid(), FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, VISIBLE, CHANGEDBYID, CHANGEDBYID, getdate(), getdate(), 1, getdate(), 1, ID, null, BASEAMOUNT, ORGAMOUNT, QUANTITY, UNITVALUE, FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
from inserted
where DELETEDON is not null
and POSTSTATUSCODE = 2
and REVERSEDLINEITEMID is null
and not exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM where REVERSEDLINEITEMID = inserted.ID and TYPECODE = 1)
insert into dbo.JOURNALENTRY (ID, FINANCIALTRANSACTIONLINEITEMID, TRANSACTIONTYPECODE, TRANSACTIONAMOUNT, PERCENTAGE, COMMENT, POSTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, GLACCOUNTID, SUBLEDGERTYPECODE, BASEAMOUNT, ORGAMOUNT, TRANSACTIONCURRENCYID, TYPECODE)
select newid(), FINANCIALTRANSACTIONLINEITEM.ID, case JOURNALENTRY.TRANSACTIONTYPECODE when 0 then 1 else 0 end, JOURNALENTRY.TRANSACTIONAMOUNT, JOURNALENTRY.PERCENTAGE, JOURNALENTRY.COMMENT, FINANCIALTRANSACTIONLINEITEM.POSTDATE, FINANCIALTRANSACTIONLINEITEM.ADDEDBYID,
FINANCIALTRANSACTIONLINEITEM.CHANGEDBYID, getdate(), getdate(), JOURNALENTRY.GLACCOUNTID, case JOURNALENTRY.TRANSACTIONTYPECODE when 0 then 1 else 0 end, JOURNALENTRY.BASEAMOUNT, JOURNALENTRY.ORGAMOUNT, JOURNALENTRY.TRANSACTIONCURRENCYID, 0
from inserted inner join FINANCIALTRANSACTIONLINEITEM on inserted.ID = FINANCIALTRANSACTIONLINEITEM.REVERSEDLINEITEMID
inner join dbo.JOURNALENTRY on inserted.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
where inserted.DELETEDON is not null
and inserted.POSTSTATUSCODE = 2
and inserted.REVERSEDLINEITEMID is null
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 1
and not exists (select 1 from dbo.JOURNALENTRY as JE2 where JE2.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID)
end
end
end;
if exists ( select 1 from deleted) and not exists ( select 1 from inserted )
begin
-------------------------------------
-- DELETED
-------------------------------------
if exists(
select D.ID
from Deleted D
where D.POSTSTATUSCODE = 2)
begin
raiserror('The financial transaction line item cannot be deleted because it has been posted', 13,1)
end
--FINANCIALTRANSACTIONLINEITEMADJUSTMENT records that don't need to exist anymore
delete FINANCIALTRANSACTIONLINEITEMADJUSTMENT
from deleted inner join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT on deleted.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = FINANCIALTRANSACTIONLINEITEMADJUSTMENT.ID
where not exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = deleted.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID)
end;
end;
|
Trigger Definition
