What table is the account segment description in?

(0) Share

Report

Posted on by Community Member

Writing a report (via sql) and need to include the description for the GL account segment - I know the table for the category. We have a 4 segment GL account structure. What sql table holds these descriptions?

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Verified answer

Richard Wheeler 75,848 Moderator on at

Like (0)

Report

GL40200

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I see that table but so many accounts are blank - is there any place else that this info is stored?

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

That is because the option was turned off or because users did not enter a value. That is the only table. You can manipulate those value using SQL and they will appear in GP.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I was wondering if there is a table that records the User who created a new GL Segment. We have some new segments that have suddenly appeared out of nowhere and we are trying to figure out who created the segments.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

If users have privileges they can enter a segment "on the fly" without descriptions in GP when adding a new GL account.

Was this reply helpful? Yes No
Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

Jeff, GP does not record the user who created the segment.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Frank - that is what I suspected.

I appreciate your time.

Thanks,

Jeff

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Hi,

Activity tracker doesn't even track that information. You can use the Support Debugging tool to easily create a trigger that would fire when someone created a new segment.

Kind regards,

Leslie

Was this reply helpful? Yes No
Victoria Yudin 22,769 on at

Like (0)

Report

Many of our customers choose to limit who can create new GL accounts to one or two people to keep their GL Chart of Accounts clean and to make sure new accounts follow the correct methodology/numbering scheme. Otherwise you end up with a mess that has to be cleaned up. :-(

Also, most companies do not choose to set up segment names for the natural accounts, only for departments, business units, etc.

I wrote this code a while ago to a get a listing of the natural account and the first name found for it that you might find useful:

select a.account, b.[description]

from (select distinct ACTNUMBR_3 account from GL00100) a

left outer join

(select rtrim(min(ACTDESCR)) [description], ACTNUMBR_3 account

from GL00100

group by ACTNUMBR_3) b

on b.account = a.account

order by a.account

The code above will work for segment 3, but you can search and replace ACTNUMBR_3 with whatever segment you need this for - there should be 3 instances of it replaced.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

We added an insert trigger on the GL00100 table to test each new GL account to verify the segments are the correct length, that segment 1 is all numeric (in our case) and that the posting type for balance sheet or income statement matches the GL account number. You could do something similar to capture who is creating new GL accounts or new Segments. SQL Server supports multiple triggers. We need to make sure we reinstall the trigger after upgrade operations. We also need to remember to add the trigger when we create a new company.

Keith McConnell

Was this reply helpful? Yes No