changing account format that falls within account framework

(0) Share

Report

Posted on by Community Member

Good Morning,

I want to streamline/rationalize our account structure in GP. The pic shows the current account format setup. I want to make it ?-?-???-???. I have gone thru the steps in a post by Andrea Melroe(Is is Possible to Change the Account Format for the Chart of Accounts?) by first shortening the account by dropping the last two segments (?-?-??) so i can then increase the length of the third segment and add a 4th segment back. I am stopped dead when I go thru the step of reconciling the account format after I run PSTL account modifier to drop the segments; it shortens the segment max length to 1-2-2 and i can't increase it. Is there any way without purchasing the renaissance add on to increase the max. segment length so I can get a account format of ?-?-???-???.

Michael Ramsey

Controller

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (0)

Community Member on at

Like (0)

Report

Pic didn't post. The current account framework is max account length 8, max segment length 6. The current account setup is

                            Account Format Setup List

Segment ID    Segment Name        Length   Display Width       Sorting Position
     1         Division               1    (max length 1)
     2         Primary                1     (max length 2)
     3         Main Account           2     (max length 4)
     4         Account                2     (max length 5)
     5         Sub-Account            2    (max length 4)

Total Number of Segments:   5              Account Length:    8

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

Like (0)

Report

There is no picture associated with this thread. I can give a more definitive answer once I see how your base framework is set.

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

Like (0)

Report

This new layout is odd as you do not see all the responses only the one to which you are responding. Anyway, what you seek is possible. However, it will require back-end corrections. Do you have access to SSMS?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, I have full access.

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

Like (0)

Report

First, run this script. You cannot exceed either of these limitations. You can just as easily see these in GP by going Tool->Setup->Company->Account Format.

SELECT [MXLACNUM],[MXNUMSEG],[DEX_ROW_ID]FROM [DYNAMICS].[dbo].[SY003001]

Next run this script against your company database.

SELECT [SGMTNUMB],[SGMTNAME],[LOFSGMNT],[MXLENSEG],[USDFSGKY],[MNSEGIND],[SegmentWidth],[DEX_ROW_ID]

FROM [TWO].[dbo].[SY00300]

This will be your account format for this company.

Now this is where it gets tricky. You delete all records out of your GL00105 and GL40200 tables

Then you run an update query on your GL00100 table with the new structure and also update the SY00300 with the same new structure.

Then you go back into GP for this company and run Check Links on the Financial series. You will get a lengthy report but that is to be expected.

The most important thing to do before you do anything is to make a backup!

We can certainly take this discussion offline if you want further details and/or assistance. We can also finish the thread here if you would like.

This will work because to GP, everything for the GL is done using the ACTINDX field which you are not touching. All you are doing is changing the GL accounts associated with those ACTINDX values. After you do all of this, exit GP and then come back into this company.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the breakdown. Instead of running an update query on Gl00100 table, could I update the sy00300 table by increasing the [MXLENSEG] field to the length I want and then use PSTL to import the new account structure from the account modifier/combiner tool; to save having to build a query to update gl00100?

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

Like (0)

Report

If all the changes mean a widening of all the segments then the answer is YES. If any have gotten smaller the answer would be NO.

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

Like (0)

Report

Hi Michael,

You got some great advice from Richard, but I want to add one little thing. Be sure to check for any areas where you have designated a segment number instead of an account number. For instance, the inventory setup asks you to identify the segment which you are using for account segment substitution as seen below:

None of the changes you have made will make any adjustment to that segment designation. Also be sure to check the ACTNUMST field of the GL00105 table. This field is used (among other places) during the year-end close to make sure you have a RE account set up for divisional closing (if you are doing that).

Also, you can make the segments smaller by changing your accounts and then running the system reconcile. You can drop segments as well as shrink segments using system reconcile.

I'm giving a GPUG Webinar next week on the chart of accounts. http://www.gpug.com/events/gpugsignewtogp201408..

You might want to listen in if you are a GPUG member.

Good luck!

Kind regards,

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the additional information. Looking at the gl0100 table design, I seem to be limited to the char length of the [ACTNUMBR_1] field, correct(i.e. - I can't make segment 3 longer than 3 characters even though I wouldn't violate the max account / segment length? Or can I change the table design and lengthen those fields without breaking other parts of the DB?

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

Like (0)

Report

Please provide the same screen shot of your DYNAMCIS..SY300001 table.

Was this reply helpful? Yes No