CREATE trigger [dbo].[TR_GLACCOUNT_UPD] ON [dbo].[GLACCOUNT]
for update
as
begin
set nocount on
declare @IsBasicGL bit
declare @ChangeAgentID uniqueidentifier
declare @AcctSysID uniqueidentifier
declare @AccountNumber nvarchar(100);
declare @AccountDescription nvarchar(400);
if (select count(*) from deleted) > 0 and (select count(*) from inserted) = 0
select top 1 @IsBasicGL = ISBASICGL from deleted t1 join PDACCOUNTSYSTEM t2 on t1.PDACCOUNTSYSTEMID = t2.ID
else
select top 1 @IsBasicGL = ISBASICGL from inserted t1 join PDACCOUNTSYSTEM t2 on t1.PDACCOUNTSYSTEMID = t2.ID
select top 1 @ChangeAgentID = ADDEDBYID, @AcctSysID=PDACCOUNTSYSTEMID, @AccountNumber = ACCOUNTNUMBER, @AccountDescription = ACCOUNTDESCRIPTION from inserted order by DATEADDED
if @IsBasicGL = 1
begin
if update(ACCOUNTNUMBER)
begin
if exists (select t1.* from inserted t1 inner join deleted t2 on t1.ID = t2.ID where t1.ACCOUNTNUMBER != t2.ACCOUNTNUMBER)
begin
if dbo.UFN_GLACCOUNT_VERIFYACCOUNTNUMBER2(@AccountNumber,@AcctSysID) = 0
BEGIN
RAISERROR ('CK_GLACCOUNT_ACCOUNTNUMBERVALID', 16, 1)
ROLLBACK
END
if len(@AccountNumber) = 0 or @AccountNumber is null
BEGIN
RAISERROR ('CK_GLACCOUNT_ACCOUNTNUMBER', 16, 1)
ROLLBACK
END
if len(@AccountDescription) = 0 or @AccountDescription is null
BEGIN
RAISERROR ('CK_GLACCOUNT_ACCOUNTDESCRIPTION', 16, 1)
ROLLBACK
END
if exists(select COUNT(ID) from GLACCOUNT where len(ACCOUNTNUMBER) > 0 group by PDACCOUNTSYSTEMID,ACCOUNTNUMBER having COUNT(ID) > 1)
BEGIN
RAISERROR ('UIX_GLACCOUNT_PDACCOUNTSYSTEMID_ACCOUNTNUMBER', 16, 1)
ROLLBACK
END
update dbo.GLTRANSACTION set ACCOUNT = inserted.ACCOUNTNUMBER , GLACCOUNTID = inserted.ID, CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from dbo.GLTRANSACTION inner join inserted on GLTRANSACTION.GLACCOUNTID = inserted.ID
update dbo.REVENUEGLDISTRIBUTION set ACCOUNT = inserted.ACCOUNTNUMBER , CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from inserted inner join dbo.GLTRANSACTION on inserted.id = GLTRANSACTION.GLACCOUNTID inner join dbo.REVENUEGLDISTRIBUTION on GLTRANSACTION.ID = REVENUEGLDISTRIBUTION.GLTRANSACTIONID
update dbo.CREDITGLDISTRIBUTION set ACCOUNT = inserted.ACCOUNTNUMBER , CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from inserted inner join dbo.GLTRANSACTION on inserted.id = GLTRANSACTION.GLACCOUNTID inner join dbo.CREDITGLDISTRIBUTION on GLTRANSACTION.ID = CREDITGLDISTRIBUTION.GLTRANSACTIONID
update dbo.GIFTAIDGLDISTRIBUTION set ACCOUNT = inserted.ACCOUNTNUMBER , CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from inserted inner join dbo.GLTRANSACTION on inserted.id = GLTRANSACTION.GLACCOUNTID inner join dbo.GIFTAIDGLDISTRIBUTION on GLTRANSACTION.ID = GIFTAIDGLDISTRIBUTION.GLTRANSACTIONID
update dbo.PROPERTYDETAILGLDISTRIBUTION set ACCOUNT = inserted.ACCOUNTNUMBER , CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from inserted inner join dbo.GLTRANSACTION on inserted.id = GLTRANSACTION.GLACCOUNTID inner join dbo.PROPERTYDETAILGLDISTRIBUTION on GLTRANSACTION.ID = PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID
update dbo.STOCKSALEGLDISTRIBUTION set ACCOUNT = inserted.ACCOUNTNUMBER , CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from inserted inner join dbo.GLTRANSACTION on inserted.id = GLTRANSACTION.GLACCOUNTID inner join dbo.STOCKSALEGLDISTRIBUTION on GLTRANSACTION.ID = STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID
update dbo.WRITEOFFGLDISTRIBUTION set ACCOUNT = inserted.ACCOUNTNUMBER , CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from inserted inner join dbo.GLTRANSACTION on inserted.id = GLTRANSACTION.GLACCOUNTID inner join dbo.WRITEOFFGLDISTRIBUTION on GLTRANSACTION.ID = WRITEOFFGLDISTRIBUTION.GLTRANSACTIONID
update dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION set ACCOUNT = inserted.ACCOUNTNUMBER , CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from inserted inner join dbo.GLTRANSACTION on inserted.id = GLTRANSACTION.GLACCOUNTID inner join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION on GLTRANSACTION.ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.GLTRANSACTIONID
update dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION set ACCOUNT = inserted.ACCOUNTNUMBER , CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from inserted inner join dbo.GLTRANSACTION on inserted.id = GLTRANSACTION.GLACCOUNTID inner join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION on GLTRANSACTION.ID = BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.GLTRANSACTIONID
update dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION set ACCOUNT = inserted.ACCOUNTNUMBER , CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from inserted inner join dbo.GLTRANSACTION on inserted.id = GLTRANSACTION.GLACCOUNTID inner join dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION on GLTRANSACTION.ID = PLANNEDGIFTPAYOUTGLDISTRIBUTION.GLTRANSACTIONID
update dbo.BENEFITGLDISTRIBUTION set ACCOUNT = inserted.ACCOUNTNUMBER , CHANGEDBYID = @ChangeAgentID, DATECHANGED = getdate() from inserted inner join dbo.GLTRANSACTION on inserted.id = GLTRANSACTION.GLACCOUNTID inner join dbo.BENEFITGLDISTRIBUTION on GLTRANSACTION.ID = BENEFITGLDISTRIBUTION.GLTRANSACTIONID
insert into dbo.PDACCOUNTSEGMENTVALUE (PDACCOUNTSTRUCTUREID, SHORTDESCRIPTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select distinct ID, SegValue, @ChangeAgentID, @ChangeAgentID, getdate() AS DATEADDED, getdate() AS DATECHANGED
from
(select t1.ID,
case t1.SEQUENCE
when 1 then t2.SEG1VALUE
when 2 then t2.SEG2VALUE
when 3 then t2.SEG3VALUE
when 4 then t2.SEG4VALUE
when 5 then t2.SEG5VALUE
when 6 then t2.SEG6VALUE
when 7 then t2.SEG7VALUE
when 8 then t2.SEG8VALUE
when 9 then t2.SEG9VALUE
when 10 then t2.SEG10VALUE
when 11 then t2.SEG11VALUE
when 12 then t2.SEG12VALUE
when 13 then t2.SEG13VALUE
when 14 then t2.SEG14VALUE
when 15 then t2.SEG15VALUE
when 16 then t2.SEG16VALUE
when 17 then t2.SEG17VALUE
when 18 then t2.SEG18VALUE
when 19 then t2.SEG19VALUE
when 20 then t2.SEG20VALUE
when 21 then t2.SEG21VALUE
when 22 then t2.SEG22VALUE
when 23 then t2.SEG23VALUE
when 24 then t2.SEG24VALUE
when 25 then t2.SEG25VALUE
when 26 then t2.SEG26VALUE
when 27 then t2.SEG27VALUE
when 28 then t2.SEG28VALUE
when 29 then t2.SEG29VALUE
when 30 then t2.SEG30VALUE
end as SegValue
from (select * from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @AcctSysID) t1 cross join inserted t2 ) V1
where not exists (select * from dbo.PDACCOUNTSEGMENTVALUE where PDACCOUNTSTRUCTUREID = V1.ID and SHORTDESCRIPTION = V1.SegValue)
update dbo.PDACCOUNTSEGMENTVALUE set TYPECODE = 1 where PDACCOUNTSTRUCTUREID in (select T1.ID from PDACCOUNTSTRUCTURE T1 join inserted T2 on T1.PDACCOUNTSYSTEMID = T2.PDACCOUNTSYSTEMID where SEGMENTTYPE = 1)
merge dbo.PDACCOUNTSEGMENT as Target
using (select V1.GLACCOUNTID, V1.PDACCOUNTSTRUCTUREID, t3.ID, @ChangeAgentID AS ADDEDBYID, @ChangeAgentID AS CHANGEDBYID, getdate() as DATEADDED, getdate() as DATECHANGED
from
(select t1.ID as PDACCOUNTSTRUCTUREID, t2.ID as GLACCOUNTID,
case t1.SEQUENCE
when 1 then t2.SEG1VALUE
when 2 then t2.SEG2VALUE
when 3 then t2.SEG3VALUE
when 4 then t2.SEG4VALUE
when 5 then t2.SEG5VALUE
when 6 then t2.SEG6VALUE
when 7 then t2.SEG7VALUE
when 8 then t2.SEG8VALUE
when 9 then t2.SEG9VALUE
when 10 then t2.SEG10VALUE
when 11 then t2.SEG11VALUE
when 12 then t2.SEG12VALUE
when 13 then t2.SEG13VALUE
when 14 then t2.SEG14VALUE
when 15 then t2.SEG15VALUE
when 16 then t2.SEG16VALUE
when 17 then t2.SEG17VALUE
when 18 then t2.SEG18VALUE
when 19 then t2.SEG19VALUE
when 20 then t2.SEG20VALUE
when 21 then t2.SEG21VALUE
when 22 then t2.SEG22VALUE
when 23 then t2.SEG23VALUE
when 24 then t2.SEG24VALUE
when 25 then t2.SEG25VALUE
when 26 then t2.SEG26VALUE
when 27 then t2.SEG27VALUE
when 28 then t2.SEG28VALUE
when 29 then t2.SEG29VALUE
when 30 then t2.SEG30VALUE
end as SegValue
from (select * from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @AcctSysID) t1 cross join inserted t2 ) V1
inner join dbo.PDACCOUNTSEGMENTVALUE t3 on V1.SegValue = t3.SHORTDESCRIPTION and V1.PDACCOUNTSTRUCTUREID = t3.PDACCOUNTSTRUCTUREID) as Source
on (Target.PDACCOUNTSTRUCTUREID = Source.PDACCOUNTSTRUCTUREID and Target.GLACCOUNTID = Source.GLACCOUNTID)
when matched then
update set Target.PDACCOUNTSEGMENTVALUEID = Source.ID,
Target.CHANGEDBYID = Source.CHANGEDBYID,
Target.DATECHANGED = Source.DATECHANGED
when not matched by Target then
insert (GLACCOUNTID, PDACCOUNTSTRUCTUREID, PDACCOUNTSEGMENTVALUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (Source.GLACCOUNTID, Source.PDACCOUNTSTRUCTUREID, Source.ID, Source.ADDEDBYID, Source.CHANGEDBYID, Source.DATEADDED, Source.DATECHANGED);
delete t1
from dbo.PDACCOUNTSEGMENTVALUE t1
where not exists (select * from dbo.PDACCOUNTSEGMENT where PDACCOUNTSEGMENTVALUEID = t1.id)
end
end
end
else
begin
if not ((charindex('*',@AccountDescription) = 0) and (charindex('?',@AccountDescription) = 0))
begin
raiserror('CK_GLACCOUNT_DESCRIPTIONVALIDCHARACTERS', 16, 1)
rollback
end
declare
@CURRENTDATE datetime = getdate(),
@SEPARATOR nvarchar(1);
if exists(SELECT I.ID FROM INSERTED I WHERE I.ACCOUNTDESCRIPTION IN (SELECT ACCOUNTDESCRIPTION
FROM dbo.GLACCOUNT WHERE GLACCOUNT.PDACCOUNTSYSTEMID = @AcctSysID and GLACCOUNT.ID <> I.ID)
)
BEGIN
RAISERROR ('UIC_LEDGERACCOUNT_DESCRIPTION', 16, 1)
ROLLBACK
END
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
select top 1 @SEPARATOR = SEPARATOR from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @AcctSysID;
;with [ELEMENTS] as
(
select unpvt.ID, DATAELEMENTID, SHORTDESCRIPTION, SEGMENTSEQUENCE
from
(select ID, DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID
from inserted) as P
unpivot
(DATAELEMENTID for dColumn in
(DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)
) as unpvt
inner join dbo.PDACCOUNTSEGMENTVALUE on unpvt.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
),
CTE as
(
select
ID,
ACCT.list.value('.','nvarchar(100)') as ACCTSTR
from
inserted t1
cross apply
(
select isnull(SHORTDESCRIPTION+@SEPARATOR,'')
from [ELEMENTS]
where t1.ID = [ELEMENTS].ID
order by SEGMENTSEQUENCE
for xml path(''), type
) ACCT (list)
)
update GLACCOUNT set
ACCOUNTSTRING = left(T1.acctstr, len(T1.acctstr)-1),
ACCOUNTNUMBER = left(T1.acctstr, len(T1.acctstr)-1),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
CTE T1
where T1.ID = GLACCOUNT.ID
;
end
end
|
Trigger Definition
