Dynamics GP IV to GL reconciliation - Pt 1

Anyone ever try to reconcile the Dynamics GP Inventory (IV) sub-ledger to the General Ledger (GL)?

Oh I'm sure it's possible somewhere, just not at my client.

At this client they have a mix of items, some they track in IV some not.  They ones they DO track as IV items, where counts and value matter, are configured so their default IV account posts the GL IV account range, call it 14x.  Other items that do not (in reality not supposed to) carry a value are coded to post Cost of Goods Sold (COGS) accounts instead.

So problem number one is how to segregate the valued inventory from the no-value inventory?  Well a quick review of the IV Class designation reveals they are not really using it.  So we created a STOCK class and assigned it (via SQL programming magic) to all the items that have the 14x accounts assigned as their Inventory GL account.

Now we have a basis for selecting only stock inventory on the various GP reports available in IV.

Problem two is that they post one natural GL account for IV but many different sub-accounts.  This is so they can track values by department.  What this means is the value of their stock inventory is spread across almost 80 different accounts.  The implication of this is that the "Reconcile to GL" function in Financial Routines is almost useless.

So now we have a means of selecting only stock inventory for reporting but have almost 80 GL account balances to compare that to.  Sweet.

If you have any experience at all with GP IV you know there are few options available.  Creating custom reports is perhaps the only viable solution.  In future articles I will explore how to create reports using SQL queries from the GP database that will assist in this matter.

Just to dispense with particulars here is what they don't use: bins, serial items, lots.  Features they do use are kits and sites.