HOW-TO: Switching to new books across companies

[zvanmeter (Zach Van Meter)]

Hello everyone,

This guide was built on top of much of the information garnered from this site, and with the assistance of @KSK who was invaluable. Without Kimberly, and without the wisdom of others on this site, this documentation would not be nearly so detailed.

Prelude

We have a multi-company environment, but we are not yet using Global/multi-company functionality. Our first preparation for that is to get all four companies (two implemented, two not yet live) all to a uniform Chart of Accounts. This isn’t exactly going to start out as Global, but it will hopefully once we power on global functionality we can tie these records together.

This all started when we bought the FP&A subscription. Did you know that if you’re multi-company, and those companies overlap natural GLA numbers with different definitions, FP&A takes one definition and applies it to both companies? It was a frustrating lesson to learn after we had already purchased it. Still, it motivated us to migrate both of those companies (and all future companies) to a standard COA.

With that said, here are the steps we intend to take in order to execute that change! Note that our implementation date for both companies is different, the first is 10/1 and the second is 10/19. We chose these dates because one member of our team wanted to implement cleanly on month end, while the other wanted to ensure they had plenty of time before month end to configure closing reports.

In the process below, we list the intention, the menu item, and a BAQ DMT template we used to pull down and upload the hand entered data. Our testing process was to build it by hand in pilot, import into test using these DMT BAQs and then run tests. If you choose to do the same, I have included each of our BAQs below, as well as the three checking BAQs and one updatable BAQ (for logging) at the bottom of this post. Note that these BAQs are configured for a three segment COA, so if you’re xx-xxx-xxx-xxxx or more, you’ll need to open each of these BAQs and add SegValue4 + so that it grabs your full listing.

Even while using these BAQs, we still needed to do a little manual manipulation. Our four companies are 0000-10-000, 0000-50-000, 0000-53-000, and 0000-56-000, so we replaced the -10- with the right number for each import. We brought definitions for each of the four divisions into each company, but not the account numbers themselves.

New Book Process

Caution, while running these DMTs I sometimes got Record Not Found Table: COA error. I had it on GL Account and GL Control. Refreshing the DMT load seemed to fix it. Not sure why this happened.

Also, we turned on Logging on all of our GL Transaction Type revisions. for a couple weeks. We figure, if we run into anything funny, it will be nice to have a record of the logs acting normally, and once things settle down we’ll turn them back off

1. Add new Chart of accounts
   1. Chart of Account or DMTCoA
2. Add new CoA Categories
   1. COA Category or DMTCoACategories
   2. Note! I am not clever enough to structure this DMT Export template such that it will import smoothly. Please run it 4 or 5 times to account for parent / child relationships.
3. Add new Account Segment Values
   1. Account Segment Values or DMTCoASegValues
4. Add new GL Accounts
   1. General Ledger Entry or DMTGLAcc
   2. Fun fact, every time I DMT these, they all fail. I reload the sheet after a failure, and they go in easy. The error is Record Not Found.
   3. We asked about Effective dates of GL accounts, and we were told we didn’t need to mess with them, so we didn’t set starting effective dates of our new accounts or ending effective dates of our old accounts.
5. Add GL Control Code Type
   1. Currently our systems all have the same Control Code Types except that one company has two codes which the other does not. We didn’t think we needed to add those.
6. Add New Book
   1. Book or DMTGLBook
   2. We believe that the GLBook.MainBook field doesn’t matter. Set it to true if you like, but it had no impact on our testing. It does not affect the Posting Rules or Master COA on Company Config.
   3. Does your new/old book have rounding account (suspense) and a rounding tolerance of .01 in it? It only matters in consolidating books, but it doesn’t hurt anything to add it in. We added it in.
   4. We had one company with Standard posting rules and one with Extended. The new book has one set of posting rules, but we could’ve left this separate if we wanted to.
7. Add GL Controls
   1. GL Control Type or DMTGLControl
   2. Your new controls might have ID “UTIL” when you already had ID “UTILITIES”, both might have the new Description of “UTILITIES”. Duplicate descriptions aren’t allowed even though UTIL and UTILITY are different, the descriptions must also be different. In this case, rename the description “UTILITIES – Legacy” or similar.
   3. Our system did something funny where sometimes the GL control code type is part class and sometimes its Part Class. Change the capitalization of each failed record and re-import
8. Ensure that all Open Purchase Orders and Sales Orders are bound to Part Classes or Product Groups with an active GL Code. Those without, which have a GL Code entered by hand need to be corrected.
   1. CHKPurchaseOrderGLs & CHKSalesOrderGLs

Now all data is primed in the system. Time can pass, as this data doesn’t hurt anything. When ready, we can implement the changeover. During the change over, it is advised to limit the number of transactions in the system. Halt operations if possible.

Cutover Steps

1. Invoice all orders / receive all shipments, and build them into AP / AR groups
2. Post all AP / AR Groups
3. Capture COS/WIP Activity
4. Verify no review Journals exist
5. Double check that Inventory/WIP Reconciliation shows no transactions unposted
   1. This is just to get as clean of a change over as possible. We will have to monitor the old book and reverse entries as they appear.
6. Check the Chart of Accounts menu item and see if it shows the big yellow warning “run rebuild balances” warning. Rerun it if so.
7. Take your trail balance, reverse it and make journal entry to old book and make all -0- then put in the trial balance to the new book to establish the new accounts. Note that transactions might be happening during this time. You might need a second pass between now and month end to collect any stranded transactions in the legacy book.
8. Go into Earliest Apply Date Entry, set “Select Earliest Apply Date” to the date of the new book.

Point of No Return / Go-Live

1. In Company Configuration, set the COA Master to the new book (GLSyst.MasterCOA)
2. Import new GL Transaction Types
   1. In GL Transaction Type menu click the overflow menu and press Import GL Transaction Type. Choose your Posting Rule Type (Package) and set Transaction Type to
      1. Side note here, the discussion of whether you should use Standard or extended is well documented on this website. We chose to use Standard.
   2. Select all relevant companies, and set Import All, Update Mode All, Change All Rev Status to Active, and Force Manual Selection of Books to True.
   3. You can rename your revisions as well. We chose to do so, so that we could have a rev level record of the transition.
   4. Click the gears to process, and then on the slide out menu tick the box that says “Use for all transaction types” Ignore the source book, it isnt looking at your source book. Ensure that the target book lists your new desired book on each company.
   5. Hit OKAY and wait for the process to complete. Check your system monitor, as it can take a couple minutes
      
      

Congrats, you’re live on the new book! Now, maybe wait a week or so before marking your old book as inactive.

So, anyway, thats our plan. See you guys on 10/2 after we launch this and hopefully launch smoothly. If you guys know anything we don’t, we’d sure appreciate a heads up!

The goods
CHKSalesOrderGLs.baq (11.6 KB)
DMTCoA.baq (9.1 KB)
DMTCoACategories.baq (8.0 KB)
DMTCoASegValues.baq (10.0 KB)
DMTGLAcc.baq (8.2 KB)
DMTGLBook.baq (9.9 KB)
DMTGLControl.baq (9.1 KB)
UptPostingRules.baq (27.9 KB)
CHKGLTransTypeRev.baq (8.2 KB)
CHKPurchaseOrderGLs.baq (11.3 KB)

[Randy (Randy Stulce)]

Oh this is a keeper! Thank you for making this how-to.

[zvanmeter (Zach Van Meter)]

So, 9/30, day before cutover, and we still cant get a good test in.

We ran a simple AR Cash Receipt Entry test, and while running the edit list we only saw the debit to our bank, but no credit to our sales. Within the PE Log Viewer, we are seeing that a GL Account cannot be found.

Details for reference

processing Book Invoice Payment Credit rule. Version 3.
Select Amount Invoice Amount
Select Name Invoice Amount Amount Currency CurrencyType Transactional Calculationmethod ConversionDate
Amount has been found Invoice Amount Value 527.00 Currency USD CurrencyType Transactional
Select Amount Invoice Amount
Select Name Invoice Amount Amount Currency USD CurrencyType Calculationmethod ConversionDate
Amount has been found Invoice Amount Value 527.00 Currency USD CurrencyType Reporting

Getting account from GL Control Cash Receipt Header–Line–AR Invoice–GL Control Type AR Invoice for context Receivables book GLOBAL GLControlCode 11314167 Reusable Ref GLRef InvcHead|300002
Unable to get account GLControlCode 11314167 Reusable Ref GLRef InvcHead|300002
[Warning!] Booking rule has been executed. Amount found, but resulting accounts are empty! Check GLControls setup

I go into Book Invoice Payment Credit and confirm that it is the base rules. Note that the rules are trying to get the GL Control Code from Cash Receipt Header-Line-AR Invoice-GL Control. I am pretty sure this is supposed to be our Product Group (mandatory field via BPM on all parts, SO Lines) which is on those orders, which is on those invoices. Invoice # 300002 has product group 10, which has GL 43000|50|000
Double check me StupidInvoiceBAQ.baq (12.7 KB)

Okay so two lines right, one for each Book / COA combo. Lets see whats up in the GL Transaction Types (ACTType / ACTRevision)
StupidInvoiceBAQ V2.baq (15.1 KB)

So I think everything looks good! I’ve checked and triple checked my GL Codes. I cant help but think that the issue is somehow related to 11314167 Reusable Ref GLRef which is referenced in the PE Log.

For reference, this is our GL Control

If anyone has any ideas on why this is happening, please let me know!

[zvanmeter (Zach Van Meter)]

More on this- just about every transaction in the posting engine is failing. Here is a side by side.

The exact same DMT import and GL Transaction imports populated both datasets, but only the company without an existing book/COA is affected.

BAD

GOOD

[zvanmeter (Zach Van Meter)]

So, I took logs from the PE Viewer, and asked copilot what was up. That finally helped me understand that the code that I thought was a Reusable Ref GLRef was really a TranGLC.SysGLControlCode, which is sort of like the financial equivalent of PartTran.

Logs:
PostEditList-Bad.xml (26.5 KB)
PostEditList-Good.xml (28.0 KB)

I updated my BAQ to include TranGLC, and you can see that the posting rules I thought were happening, were definitely not happening. The TranGLC clearly indicates the old GL Account!
SEE! StupidInvoiceBAQ V3.baq (17.7 KB)

So that means that even though SysGLControlCode 11314167 clearly references GL Acct 1060|00|00, the posting rules are failing.

This must mean that we have not completed a step in our cut over somewhere? Our Posting rules Can’t find our original COA?

How do we need to handle receiving payment of an invoice which was posted in our previous book? But its more than just that question, we also need to know why it seems that none of our Posting Engine Rules are able to find the previous COA at all.