VI can use an ODBC data source, so a middle step is not always necessary.

Re-visiting this.

Our plan is to export all items and their whse locations into a csv file and import those. We may have to do two exports, then join the two tables to get item_code and whse location quantities/allocations.

If we can Import the on_hand/allocated quantities into the new company and the new locations.

Then zero the on_hand/allocated quantities in the csv file and import them back into the original sage company using a new specific gl_account so we can make a journal entry to dispose the inventory value.

Is any of this even possible?

There's a lot of inventory that is being transferred to a new distribution center, so making po's and invoice to do the transfers seems like a little to much work.

Well, what do the experts think?

Have you thought of the accounting implications of this. When you say "new company" is it a new company code or a division within the existing company? If you remove the inventory from one company you are going to have a huge hit on the financials. How will you account for the dollars? In the new company importing the warehouse locations is dink simple. To bring in the quantities you import into Inventory Adjustments and post.

Hey BL, Yes I have moved other ERP systems data into new Sage companies, primarily inventory, AR and AP, but not this. I've never relieved inventory from a Sage company. Just have to try I guess.

It is a new Company in Sage, not a division of an existing.

Our plan was to make two specific GL accounts (one for what Company A owes Company B) and vice-versa for the new company (what Company B owes Company A). Our cpa thinks they can do a journal entry to cover the value of the inventory.

I'm going to try a couple single items; do the export, then the import into the new company and see how the transactions play, then zero out those items and import those values back into the old company.

I'd rather be doing just about anything else.

You can "ship" the inventory from the source company if you want the AR booked, or an Inventory Issue (to the GL account of your choosing) if you don't want the AR booked.

As BL said, importing an inventory receipt is easy (...using a PO RoG with invoice number is best to create the payable entry).  Without PO, just using an IM receipt, the flip side of that transaction will be Purchases Clearing, so you can later invoice in AP against that account.

Oh, and Excel queries to pull the data (from the various inventory tables) is much easier than using VI exports.

Can you describe what you mean by "ship"?

The way I'm looking at this is as follows.

1. Company A (new dist company) is a new Sage company.

2. Old Sage company (inventory owner Company B)

3. Export Company B current inventory/on_hand/allocated etc into new Company A as beginning inventory balances.

4. Make adjustment into im_transactionhistory table to offset the current on_hand/allocated values in the old Company B inventory. eg; if on_hand was originally 4, import and adjustment of -4

There are a lot of transactions here. I've suggested creating a po and selling the products to the new company.

Thanks for any additional comments

Ship = SO invoice.  Inventory goes down, COGS up, AR up, Revenue up...

Basically, you treat the transfer as a sale / purchase from one company to the other.  BAU transactions.

Inventory adjustments have many complications and should be avoided unless there is no viable alternative.