CREATE trigger [dbo].[TR_GLACCOUNT_INS] on [dbo].[GLACCOUNT]
with execute as owner
for insert
as
set nocount on
declare @ChangeAgentID uniqueidentifier
declare @AcctSysID uniqueidentifier
declare @IsBasicGL bit;
declare @AccountNumber nvarchar(100);
declare @AccountDescription nvarchar(400);
declare @CURRENTDATE datetime = getdate()
declare @SEPARATOR nvarchar(1);
declare @DEFAULTGLACCOUNTID uniqueidentifier;
declare @ACCOUNTALIAS nvarchar(100);
select top 1 @ChangeAgentID = ADDEDBYID, @AcctSysID=PDACCOUNTSYSTEMID , @AccountNumber = ACCOUNTNUMBER, @AccountDescription = ACCOUNTDESCRIPTION, @ACCOUNTALIAS = ACCOUNTALIAS from inserted order by DATEADDED
select @IsBasicGL = ISBASICGL, @DEFAULTGLACCOUNTID = DEFAULTGLACCOUNTID from PDACCOUNTSYSTEM where PDACCOUNTSYSTEM.ID = @AcctSysID
if @IsBasicGL = 1
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
if (len(@ACCOUNTALIAS) > 100)
BEGIN
RAISERROR ('CK_GLACCOUNT_ACCOUNTALIASLENGTH', 16, 1)
ROLLBACK
END
insert into dbo.PDACCOUNTSEGMENTVALUE (PDACCOUNTSTRUCTUREID, SHORTDESCRIPTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select distinct ID, SegValue, @ChangeAgentID, @ChangeAgentID, getdate(), getdate()
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)
insert into dbo.PDACCOUNTSEGMENT (GLACCOUNTID, PDACCOUNTSTRUCTUREID, PDACCOUNTSEGMENTVALUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select V1.GLACCOUNTID, V1.PDACCOUNTSTRUCTUREID, t3.ID, @ChangeAgentID, @ChangeAgentID, getdate(), getdate()
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
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)
declare @GLACCOUNTID uniqueidentifier;
select top 1 @GLACCOUNTID = ID from inserted order by dateadded desc;
declare @SQL nvarchar(4000)
select @SQL = (select 'DATAELEMENT' + cast(T2.SEQUENCE as nvarchar(2))+ 'ID =' + '''' + convert(varchar(36),T1.PDACCOUNTSEGMENTVALUEID) + '''' + ','
from dbo.PDACCOUNTSEGMENT T1
join dbo.PDACCOUNTSTRUCTURE T2 on T1.PDACCOUNTSTRUCTUREID = T2.ID
where T1.GLACCOUNTID = @GLACCOUNTID
order by T2.SEQUENCE for XML PATH(''))
set @SQL = @SQL + 'CHANGEDBYID = ' + '''' + convert(varchar(36), @CHANGEAGENTID) + '''' + ', DATECHANGED = ' + '''' + convert(varchar(36),@CURRENTDATE,110) + '''' + ' where GLACCOUNT.ID = ' + '''' + convert(varchar(36),@GLACCOUNTID) + ''''
set @SQL = 'update GLACCOUNT set ' + @SQL
exec (@SQL)
------------------------------------------------------------------------------------------------
-- Hash Function --
------------------------------------------------------------------------------------------------
;with account_cte(ID, MD5HASHVALUE)
as
(
select
id,
dbo.UFN_GLACCOUNT_MD5_HASH_SEGMENTS(
(
select
pvt.[1] SEGMENT01ID, pvt.[2] SEGMENT02ID, pvt.[3] SEGMENT03ID, pvt.[4] SEGMENT04ID, pvt.[5] SEGMENT05ID, pvt.[6] SEGMENT06ID, pvt.[7] SEGMENT07ID, pvt.[8] SEGMENT08ID, pvt.[9] SEGMENT09ID, pvt.[10] SEGMENT10ID, pvt.[11] SEGMENT11ID, pvt.[12] SEGMENT12ID, pvt.[13] SEGMENT13ID, pvt.[14] SEGMENT14ID, pvt.[15] SEGMENT15ID, pvt.[16] SEGMENT16ID, pvt.[17] SEGMENT17ID, pvt.[18] SEGMENT18ID, pvt.[19] SEGMENT19ID, pvt.[20] SEGMENT20ID, pvt.[21] SEGMENT21ID, pvt.[22] SEGMENT22ID, pvt.[23] SEGMENT23ID, pvt.[24] SEGMENT24ID, pvt.[25] SEGMENT25ID, pvt.[26] SEGMENT26ID, pvt.[27] SEGMENT27ID, pvt.[28] SEGMENT28ID, pvt.[29] SEGMENT29ID, pvt.[30] SEGMENT30ID
for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64
)) ACCOUNTHASH
from
(
select g.id, cast(s.PDACCOUNTSEGMENTVALUEID as nvarchar(36)) PDACCOUNTSEGMENTVALUEID, ROW_NUMBER() over(partition by g.id order by s.PDACCOUNTSEGMENTVALUEID) segment
from dbo.GLACCOUNT g inner join
dbo.PDACCOUNTSEGMENT s on g.ID = s.GLACCOUNTID inner join
inserted i on i.ID = g.ID
) source
pivot
(
max(PDACCOUNTSEGMENTVALUEID)
for segment in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30])
) pvt
)
update dbo.GLACCOUNT
set MD5HASHVALUE = h.MD5HASHVALUE
from account_cte h inner join
dbo.GLACCOUNT g on g.ID = h.ID
------------------------------------------------------------------------------------------------
if @DEFAULTGLACCOUNTID is not null
delete from dbo.PDACCOUNTLOOKUPCACHE where ERRORNUMBER != 0 and PDACCOUNTSYSTEMID = @AcctSysID
end
else if (select count(*) from INSERTED) > 0
begin
if not ((charindex('*',@AccountDescription) = 0) and (charindex('?',@AccountDescription) = 0))
begin
raiserror('CK_GLACCOUNT_DESCRIPTIONVALIDCHARACTERS', 16, 1)
rollback
end
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
|
Trigger Definition
