CREATE trigger [dbo].[TR_GLACCOUNT_INS] on [dbo].[GLACCOUNT]
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);
select top 1 @ChangeAgentID = ADDEDBYID, @AcctSysID=PDACCOUNTSYSTEMID , @AccountNumber = ACCOUNTNUMBER, @AccountDescription = ACCOUNTDESCRIPTION from inserted order by DATEADDED
select @IsBasicGL = ISBASICGL 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
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)
end
else
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
