The SQL script FRx_SL2011_93Fix (below) is extracted from the zip file B2551470_FRx_SL2011_93Fix. I can provide the zip file if needed. As noted before, the triggers have SET NOCOUNT ON added to the start of each, there didn't appear to be any other changes to the triggers being updated. Adding that command to other user created triggers may be the desired solution to fix error 93 when releasing a batch..
--delete from frl_acct_code
alter trigger frx_frl_acct_code_insert
on frl_acct_code
For Insert
as
Begin
SET NOCOUNT ON
If ((select count(*) from inserted) = 0) goto eof
DECLARE @MaxSegs int
DECLARE @NatSegLen tinyint
DECLARE @SegLen2 tinyint
DECLARE @SegLen3 tinyint
DECLARE @SegLen4 tinyint
DECLARE @SegLen5 tinyint
DECLARE @SegLen6 tinyint
DECLARE @SegLen7 tinyint
DECLARE @SegLen8 tinyint
DECLARE @SegLen9 tinyint
DECLARE @SubLen tinyint
SELECT @MaxSegs = NumberSegments + 1,
@SegLen2 = SegLength00,
@SegLen3 = SegLength01,
@SegLen4 = SegLength02,
@SegLen5 = SegLength03,
@SegLen6 = SegLength04,
@SegLen7 = SegLength05,
@SegLen8 = SegLength06,
@SegLen9 = SegLength07,
@SubLen = SegLength00 + SegLength01 + SegLength02 + SegLength03 +
SegLength04 + SegLength05 + SegLength06 + SegLength07
FROM FlexDef
WHERE FieldClassName = 'SUBACCOUNT'
select @NatSegLen = nat_seg_len from frl_glx_ctrl
update frl_acct_code set
Acct_Code = substring(Acct, 1, @NatSegLen) + substring(Sub, 1, @SubLen)
,nat_seg_code = substring(acct, 1, @NatSegLen)
,seg01_code =substring(Acct, 1, @NatSegLen)
,seg02_code = substring(Sub, 1, @SegLen2)
,seg03_code = substring(Sub, 1 + @SegLen2, @SegLen3)
,seg04_code =substring(Sub, 1 + @SegLen2 + @SegLen3, @SegLen4)
,seg05_code =substring(Sub, 1 + @SegLen2 + @SegLen3 + @SegLen4, @SegLen5)
,seg06_code = substring(Sub, 1 + @SegLen2 + @SegLen3 + @SegLen4 + @SegLen5, @SegLen6)
,seg07_code = substring(Sub, 1 + @SegLen2 + @SegLen3 + @SegLen4 + @SegLen5 + @SegLen6, @SegLen7)
,seg08_code = substring(Sub, 1 + @SegLen2 + @SegLen3 + @SegLen4 + @SegLen5 + @SegLen6 + @SegLen7, @SegLen8)
,seg09_code = substring(Sub, 1 + @SegLen2 + @SegLen3 + @SegLen4 + @SegLen5 + @SegLen6 + @SegLen7 + @SegLen8, @SegLen9)
from frl_acct_code where entity_num = -255
if (select top 1 ValidateAcctSub from GLSetup WITH (nolock)) = 1
begin
update frl_acct_code set
acct_status =
CASE
WHEN active = 1 THEN 0
WHEN active = 0 THEN 1
END,
acct_desc = descr
FROM frl_acctsub_view
WHERE frl_acctsub_view.acct = frl_acct_code.acct
AND frl_acctsub_view.sub = frl_acct_code.sub
AND frl_acctsub_view.cpnyid = frl_acct_code.cpnyid
AND frl_acct_code.entity_num = -255
-- try and update fields from frl_acctsub_view
update frl_acct_code set
acct_status =
CASE
WHEN active = 1 THEN 0
WHEN active = 0 THEN 1
END,
acct_desc = descr
FROM account
WHERE account.acct = frl_acct_code.acct
and frl_acct_code.acct_status = 255
and entity_num = -255
--now for account group/normal_bal rule
update frl_acct_code set
normal_bal =
case
when substring(accttype, 2, 1) = 'A' THEN 1
when substring(accttype, 2, 1) = 'E' THEN 1
when substring(accttype, 2, 1) = 'L' THEN 2
when substring(accttype, 2, 1) = 'I' THEN 2
end,
acct_group=
case
when substring(accttype, 2, 1) = 'A' THEN 1
when substring(accttype, 2, 1) = 'L' THEN 2
when substring(accttype, 2, 1) = 'E' THEN 3
when substring(accttype, 2, 1) = 'I' THEN 4
end
from account
where account.acct = frl_acct_code.acct
and frl_acct_code.entity_num = -255
update frl_acct_code set acct_group = 1,normal_bal = 1 where frl_acct_code.normal_bal =0
END
else --if (select ValidateAcctSub from GLSetup WITH (nolock)) = 1
begin
UPDATE frl_acct_code set
acct_status =
CASE
WHEN active = 1 THEN 0
WHEN active = 0 THEN 1
END,
acct_desc = descr,
normal_bal =
CASE
WHEN substring(accttype, 2, 1) = 'A' THEN 1
WHEN substring(accttype, 2, 1) = 'E' THEN 1
WHEN substring(accttype, 2, 1) = 'L' THEN 2
WHEN substring(accttype, 2, 1) = 'I' THEN 2
END,
acct_group =
CASE
WHEN substring(accttype, 2, 1) = 'A' THEN 1
WHEN substring(accttype, 2, 1) = 'L' THEN 2
WHEN substring(accttype, 2, 1) = 'E' THEN 3
WHEN substring(accttype, 2, 1) = 'I' THEN 4
END
from account
where account.acct = frl_acct_code.acct and entity_num = -255 and acct_status = 255
end
delete from frl_acct_code where len(rtrim(sub)) > @SubLen and entity_num = -255
update frl_acct_code set entity_num =isnull(( select entity_num from frl_entity where frl_entity.cpnyid = frl_acct_code.CpnyID),-1) where entity_num = -255
eof:
--SET NOCOUNT OFF
END
go
alter trigger frx_gltran_insert
on gltran
for insert, update
as
begin
SET NOCOUNT ON
insert frl_acct_code (
acct,acct_code,acct_desc,acct_group,acct_status,cpnyid,
entity_num,nat_seg_code,normal_bal,
seg01_code,seg02_code,seg03_code,seg04_code,seg05_code,seg06_code,seg07_code,seg08_code,seg09_code,sub)
select distinct t1.acct,' ', ' ' , 0 , 255
, t1.cpnyid
, -255 , ' ' , 0 , ' ' , ' '
, ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , t1.sub from inserted t1
left join frl_acct_code t2
on t1.cpnyid = t2.cpnyid and t1.acct = t2.acct and t1.sub = t2.sub
WHERE t1.posted = 'U' and rlsed = 1 and t2.acct is null
--SET NOCOUNT OFF
END
Share
Report
1,642
All responses (
Answers (
