You’re offline. This is a read only version of the page.
2,947
I have account/subaccount combination setup in Account/Subaccount Maintenance screen.... but still when i enter voucher in voucher/adjustment entry screen i am able to select any subaccount for a account that i have setup in account/subaccount Maintenance screen.
At what point the combination is checked or validated?
Any comment is appreciated.
*This post is locked for comments
Did you remember to check the Valid Combo Required checkbox on the Flexkey Definition screen under Field Type SUBACCOUNT?
I think you can set up all the combinations you need first, but the enforcement comes from the Flexkey Definition screen.
Hi Bab,
You should be able to use the acct/sub combo as soon as it is saved.
I would go this route.
Run the following script against your system dbase.
Make a small change to a description in COA Maintenance, subacct Maintenance and Acct/sub Maintenance and Save. Then what happens.
-- Drop all windows authentication triggers.
-- If using windows authentication, it will also recreate the triggers
-- and cleans up any stray vs_acctsub or vs_acctxref records
--
-- 1 - Make a good database backup
-- 2 - Run against your SL System database in Management Reporter
-- Step 1: Drop all ACCTSUB and ACCTXREF triggers
declare @triggername as char(100)
declare @execString as char(200)
DECLARE trigger_cursor CURSOR FOR
select name from sysobjects where type='TR' and (
name like 'sDeleteAcctSub_%' or
name like 'sInsertAcctSub_%' or
name like 'sUpdateAcctSub_%' or
name like 'sDeleteAcctXref_%' or
name like 'sInsertAcctXref_%' or
name like 'sUpdateAcctXref_%')
OPEN trigger_cursor
FETCH NEXT FROM trigger_cursor INTO @triggername
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString = 'drop trigger ' + @triggername
print @execString
exec (@execString)
print 'Done'
FETCH NEXT FROM trigger_cursor INTO @triggername
END
CLOSE trigger_cursor
DEALLOCATE trigger_cursor
-- only do step 2 and 3 if windows auth
if (select top 1 text from syscomments where ID in (select ID from sysobjects where name='getauthenticationtype' and type='P'))
like '%Windows%'
begin
-- Step 2: Recreate a new set of 6 triggers for each app database listed in the company table
declare @dbname as char(100)
declare @execString2 as char(1000)
DECLARE db_cursor CURSOR FOR
select distinct databasename from company where databasename<>''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString2 = 'CREATE TRIGGER sDeleteAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER DELETE '
+' AS Delete '+rtrim(@dbname)+'..vs_AcctSub from '+rtrim(@dbname)+'..vs_acctsub v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid and v.sub = deleted.sub'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sInsertAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER INSERT '
+' AS Insert into '+rtrim(@dbname)+'..vs_AcctSub select acct,active,cpnyid,crtd_datetime,crtd_prog,crtd_user,descr,lupd_datetime,lupd_prog,lupd_user,noteid,s4future01,s4future02,s4future03,s4future04,s4future05,s4future06,s4future07,s4future08,s4future09,s4future10,'
+'s4future11,s4future12,sub,user1,user2,user3,user4,user5,user6,user7,user8,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sUpdateAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER UPDATE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctsub from '+rtrim(@dbname)+'..vs_acctsub v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid and v.sub = deleted.sub'
+' Insert into '+rtrim(@dbname)+'..vs_acctsub select acct,active,cpnyid,crtd_datetime,crtd_prog,crtd_user,descr,lupd_datetime,lupd_prog,lupd_user,noteid,s4future01,s4future02,s4future03,s4future04,s4future05,s4future06,s4future07,s4future08,s4future09,s4future10,'
+'s4future11,s4future12,sub,user1,user2,user3,user4,user5,user6,user7,user8,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sDeleteAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER DELETE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctxref from '+rtrim(@dbname)+'..vs_acctxref v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sInsertAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER INSERT '
+' AS Insert into '+rtrim(@dbname)+'..vs_acctXref select acct,accttype,active,cpnyid,descr,user1,user2,user3,user4,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sUpdateAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER UPDATE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctxref from '+rtrim(@dbname)+'..vs_acctxref v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid'
+' Insert into '+rtrim(@dbname)+'..vs_acctXref select acct,accttype,active,cpnyid,descr,user1,user2,user3,user4,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- Step 3: Cleanup any stray vs_acctxref or vs_acctsub records
declare @dbName3 as char(85)
declare @execString3 as char(200)
DECLARE db_cursor3 CURSOR FOR
select distinct databasename from company where databasename<>''
OPEN db_cursor3
FETCH NEXT FROM db_cursor3 INTO @dbName3
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString3 = 'delete a from ' + QUOTENAME(rtrim(@dbName3)) + '..vs_acctxref a left join acctxref b on a.acct=b.acct and a.cpnyid=b.cpnyid where b.acct is null'
print @execString3
exec (@execString3)
set @execString3 = 'delete a from ' + QUOTENAME(rtrim(@dbName3)) + '..vs_acctsub a left join acctsub b on a.acct=b.acct and a.sub=b.sub and a.cpnyid=b.cpnyid where b.acct is null'
print @execString3
exec (@execString3)
FETCH NEXT FROM db_cursor3 INTO @dbName3
END
CLOSE db_cursor3
DEALLOCATE db_cursor3
END
-- END
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (