Netsuite Subsidiary Hierarchy Modification
Problem
Netsuite allows modifications of the subsidiary hierarchy, but is very cautious about doing so. There are LOTS of warnings.
Our heirarchy was in a strict ownership order. However, this was not the reality of how various businesses in the hierarchy were organised. Netsuite expects an elimination entity at every level of ownership to create the eliminations correctly, whereas in reality we only needed to have a single elimination entity for group reporting.
I intended to have a wide ranging restructure, moving to a regional reporting model. The main risks of changing the hierarchy are:
- Settings that "include children"
- Financial roll-ups / consolidation
In various places (accounts, classifictions, items, etc) subsidaries are set and then may have the check box for "include children". Chaning parents can then cause different elements to have the subsidiary restrictions change. Fortunately for me, most situations had either the top-level selected and include children, or the individual subsidiaries chosen.
The bigger risk is that your financial statements change. Again, I was fortunate that the org had not used Netsuite for consolidation. So elimination entities had minimal entries. This means that financial statements are unlikley to change because there are no eliminations.
Testing
Not wanting to leave it to chance, I tested in sandbox. As there was a lot of data to check, I used a combination of SuiteQL and git to find and review changes.
I've got a general purpose Python program to connect to Netsuite API and perform various tasks: one of which is running SuiteQL queries and storing the result. I ran queries to get all of the data from the following tables:
- Items
- Subsidiary
- Subsidiary Hierarchy
- Currency Rate
- Consolidated Exchange Rate
- Transaction Line
- Department
- Department Subsidiary Map
- Classification
- Contact Subsidiary Relationship
- Customer Subsdiairy Relationship
- Employee Subsidiary Relationship
- Entity Subsidiary Relationship
- Project Subsidiary Relationship
- Vendor Subsidiary Relationship
- Account
- Role
- Effective Role Subsidiary Restriction Map
- Location
- Company Feature
- Employee
Essentially, I was trying to capture tables where the changing of subsidiaries would have an effect. Git would do the hard work of comparing the API responses. In most cases, I selected all rows and a few fields to check key bits of data. However, there were some exceptions:
- Currency Exchange Rate - This was a huge amount of data, so I restricted to end of month for last year, then every six months before that.
- Consolidated Exchange Rate - only the last 12 months.
- Transaction Line - The data here was likely to move in production, so summarized the data up to the end of a closed posting period. This data should not move
The Python scripts took ~30 mins to run to get all the data. Git did struggle on some of the file comparisons, but that might be a VSCode thing. On the whole, the data didn't change. Consolidated exchange rates were hard to compare, because my subsidiary restructure ended up with a lot of rate deletions, so there was a lot of data that appeared to be missing. There were also so slight changes to Vendor / Customer balances, but that appeared to be down to consolidations. The balances had changed by a very small amount, however, the underlying transactions were fine.
Moving the subsidiaries in Netsuite mostly worked, however, Netsuite did throw errors a few times. They either did work, or didn't work. When the move didn't work, it helped to wait a while, sometimes it needed to be re-triggered, sometimes not. I'd recommend to take it slow and wait to make sure that subsidiaries move OK.
Outcome
As the testing in sandbox went fine, we agreed a schedule in production. We were concerned about Netsuite performance being impacted whilst the data was being moved, so planned to move a few subsidiaries a day over the course of a week. We did not force users to stop work, as we want to get feedback whilst we were making the changes.
Before moving the subsidiaries in production, I ran my Python scripts to get the initial data. And then run it again after each move of a block of subsidiaries, then monitored for changes.