Go-Live Inventory and Accounting Import (upload/establish opening balances)

The purpose of this page is to help you establish your go-live inventory and accounting balances. I highly recommend you allow for 4 weeks to achieve success. Depending on the results of your testing, the process could take longer. See Record Import to better understand upload and import basics.

Import Open Documents - Getting Ready for Go Live

Import Open Documents - Types of Imports

Import Open Documents - The Parasitic Effects

Notes:

In these videos, I refer to "Temp Go-Live accounts". I recommend you create accounts like "99992" for example.
In later year, I now recommend that you use a different name for the accounts. Instead of something arbitrary like "99992", you instead create an account whose number is similar to the account that it offsets.
New recommendation: to create a temp go live account for inventory (Inventory is account 14000), create an Inventory Temp Go Live Account named "14000-999". Doing so helps better describe why the account exists. If you import historical periods (periods before you go live), you will need to include these accounts in your Financial Reports. This naming convention is much more obvious as to why they exist.

Import Open Documents - The Solution Part 1 - Doc Posting

Import Open Documents - The Solution Part 2 - GL Journal Posting

Import Open Documents - Inventory

One topic the video did not cover was how to establish costs. Preferred method:

Step 1: Read and execute this post about creating all $0 costing records. Direct link to Product Costing page with SQL to create costing records.
Step 2: Import your costs via the Cost Adjustment window. There does not exist an old-school import like the 'Import Inventory' for the Cost Adjustment window; therefore, you simply use the CSV import tool (new-school). When this step posts, it will post zero to the GL because not inventory exists. Note this step is dependent on step one. If the costing records do not exists, the Cost Adjustment will fail.
Step 3: Import inventory using the Import Inventory window without modifying the costs. When your resulting Physical Inventory records complete and post, they will establish your open inventory levels and GL balances.

Alternative method: As part of the inventory import process, you can import your product costs. If you choose this option, you will need to specify the following:

AD_Org_ID - this field will be pulled from inventory import record. If you manage different costs for different orgs (Accounting Schema => Costing Level = Organization), then you need to make sure your CSV import also represents this fact. Make sure the Org field and the Warehouse's Org match.
M_Product_ID - this field is populated by the import process.
M_AttributeSetInstance_ID - By default, this field will be "0". You have the option to create Lot/Serial (ASI - M_AttributeSetInstance_ID) as part of the import process.
C_AcctSchema_ID - You will need to choose this field when you run the Import Inventory process.
M_CostType_ID - You have the ability to have more than one cost type. This is commonly used in manufacturing where you have (1) Actual, (2) Hypothetical01, (3) Hypothetical02, etc... In most cases, you will have only have one cost type.
M_CostElement_ID - this is where you specify which cost element you want to update. In the case of standard costing, you will update the "Material Cost" Cost Element.

Important Note: Be aware there is a bug when importing inventory (Inventory Import window) with costs. You should not allow the Import Inventory process to complete your Physical Inventory documents if you are setting your costs. This means the Document Action field should be empty. You will need to go to each of the resulting Physical Inventory documents and complete them manually. If you allow the Import Inventory to complete the Physical Inventories, the following will happen (1) the Physical Inventory will post $0 to the GL, and (2) product's cost record will not show the desired Qty values.

Updating Costs

In the previous section, we discussed how to update costs as part of the inventory import process. You can also change costs outside of the import process. You have two options:

iDempiere introduces a Cost Adjustment window (preferred)
You can update M_Cost manually via SQL

The manual update concept is similar to the above BOM Import explanation where we used an XLS spreadsheet to create the individual SQL statements (see "Import Records Manually - Demonstrated with BOM Import" section). When updating the cost of inventory, you are updating the SubLedger inventory valuation. In the case of Standard Costing, you should be able to tie the value of your Inventory (sub-ledger report) to the value of your Inventory GL value. If you update a costing record whose product qty on hand is greater than zero, you might consider creating a GL Journal to update the Inventory GL account. Since this account is document controlled, you might consider creating an Inventory Offset GL account to hold such updates. You can also remove the Document Controlled flag from the inventory account; however, you should consult your accountant before doing so. Removing the Document Controlled flag is generally frowned upon for balance sheet accounts.

Import Open Documents - AP Invoice (and AR)

Import Open Documents - AP Payment (and AR)

Import Open Documents - Un-Reconciled Payments and Receipts

Import Open Documents - Reconciled Receipts - Opening Bank Balances

Import Open Documents - Not Invoiced Receipts (NIR or GRNI or Accrued Purchases)

2014-05-06 AM - Import Open Documents

I hosted this discussion before creating the above videos. Watching it may help re-enforce the concepts; however, protect your time. I was not particularly happy with how this discussion went.

GL Journal Import - using both new and old import tools

The import tables (I_Order, I_Journal, I_Invoice, etc..) have many added benefits including:

grouping entries and into separate documents based on date, BP, etc...
Splitting a single record into header-line records
Finding references when all that is supplied is a string
Resolving complex business logic challenges

When people go live, they often want to import historical trial balances across many years. Importing this many GL Journals by hand (or even with the new CSV style import) is tedious and time consuming. Imaging importing 15 years (180 periods) across 40 organizations. That is 7200 GL Journal documents. This is the goal of one of my customers. Luckily, you can use the new style CSV import into an import table. This CSV example shows how to format a I_Journal file for importing all entries across periods across all entities within a single import process.

References

http://wiki.adempiere.net/Establish_Opening_Balances