One of the common issues facing people who deal with third-party logistics providers (3PL) is that they’re rarely using the same software to manage their inventory.
Which puts inventory balances out of sync.
What should you do when dealing with those situations?
First, start fresh!
Ideally, wait until your 3PL does their annual count so you know you have the ‘latest and greatest’ numbers. If you notice too many discrepancies, request their latest inventory levels.
That report should give you what Sage X3 needs on its end; for example, if you’re using lot numbers, expiration dates, or bin locations matching those of your 3PL, the report you’ll use to sync your inventory in Sage Enterprise Management with your 3PL should contain those pieces of information.
When you have the file from your 3PL, the following steps should reduce risks to a minimum.
The plan should have two distinct phases: a test phase executed in a test environment and an execution phase in the live environment (after everything was verified in the test platform).
Ideally, the file to import (the data from the 3PL software to bring into Sage X3) should be in the proper format when you get it from the 3PL provider. Enterprise Management relies on import templates to bring information in, so all logic is applied during the transfer, ensuring complete data accuracy and formatting. In other words, a date will be a date and a product code will be a product code. If that can’t happen, you need to be familiar with the import process (or let Southeast Computer Solutions help).
The file should exclude items that won’t be imported because they are either obsolete or ready to be destroyed.
If the 3PL can’t provide a clean file, someone must manually remove these products from the list, which increases the risk for errors. The 3PL will usually use an SQL script to export the inventory, which offers a higher-quality final import file.
Never assume that all products to be imported in the file are created in Sage X3.
Before importing, ensure that all products in the 3PL software already exist in Enterprise Management; otherwise, Sage X3 will reject the file and exponentially increase the time it takes to process the files because you’ll need to manually remove the lines that don’t exist in the log file.
Make sure all items requiring a lot number have the information in the file provided by the 3PL. If you require additional lot attributes, like expiration date or use-by date, this information should be part of the file. It’s important to note that if the lot number exists in Sage X3, when the quantity refresh is done, the original lot complement information will be used like expiration date or use-by date for example. If the lot complement information is incorrect in Sage X3, you don’t have any way of knowing this.
You should assume that the lot numbers you receive are the correct lot numbers. If you identify some that are inaccurate, modify them in the 3PL software before getting the report and review why they were wrong.
Always export what’s in Sage X3 and create a snapshot of the current product, lot, quantities, and cost in Enterprise Management before the quantities are set to zero. A database extraction should do the trick.
It’s now about reducing inventory levels to zero in Sage X3. It’s preferable to do so during a time you can afford not to ship. Weekends are best, but if you’re willing to work through the night, a weeknight would also work.
Get the latest inventory report from Sage Enterprise Management, massage it into the Enterprise Management import template format, and use those values to do a miscellaneous issue of all of the items. Turn off the accounting auto journals for these transactions so they don’t affect your GL (unless you want them to).
Do a data extraction in case something goes wrong.
Execute an import to a Miscellaneous issue transaction to get all inventory to zero.
Import the file provided by the 3PL and laid out the way Enterprise Management needs it as a Miscellaneous receipt transaction. Once those transactions are in, make sure they match what you expected by running your usual reports.
Check the new values in Sage X3 and make updates as required. If a lot number needed to be created but the dates don’t match what you were expecting, use the lot modification function to adjust the values.
If all went well and you went to bed at a reasonable hour or didn’t waste your weekend, pat yourself on the back and also give the green light to your 3PL so they can ship your inventory items again. If something went wrong during the import, identify it (if you executed the test phase at full scale you should have identified all issues) and execute the data integration to restore the folder and go back to step 1 of the execution phase.
When you’re comfortable doing this, create an automatic process to adjust the inventory so it always matches. Southeast Computer Solutions can develop those to facilitate your job regardless of the system your 3PL is using. Feel free to reach out!