GP 2010 IV Transaction tables relationships

This question is answered
We had a sales batch that had trouble posting. I know which items were affected and can fix most of them using SSMS. However, there are a couple that are defying logic. Can anyone describe the relationship between the IV10200, IV10201 and IV30300 tables? I must be missing a key concept.

Richard E. Wheeler 2013 and 2014 MVP

Member Microsoft Academic Alliance

www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10

Verified Answer
  • A small correction, IV10201 contains the "out" transactions for each record in IV10200, it simply represents the details of QTYSOLD field of IV10200.

    IV10200 holds all the receiving's with all available cost layers and all the updates over the cost layers across the days like "Cost Adjustments" and "Landed Cost" updates.

    IV30300 is the inventory history, it holds all the transactions that affects the inventory modules with "historical figures" to keep your documents synched with your archive, it shows the transactions figure as of the day of occurrence.

    A simple workflow, once you create a receiving one record will be created in IV10200 for each line and one record will be created in IV30300, once you create a sales transaction a record will be created in IV10201 and the corresponding record in IV10200 will be updated to reflect the exported quantities; the relation between tables are exactly as explained by Richard, finally another record for each item will be created in IV30300 to keep the history of the transaction.

    Hope that this helps.

    *** If my answer resolved you issue, please verify the answer to make it easy for other users who has the same case ***

    Regards,

     

    Mohammad R. Daoud MVP-MCT

    Have you seen the GP Excel Paste yet? http://di.jo/GPExcelPaste.aspx

All Replies
  • IV10200 is the inventory Purchase Receipts table.  It contains a record for each receipt of each item.  One of the fields is Quantity Consumed (or something like that) Posting a sales invoice would increase this number based on the valuation scheme (FIFO vs LIFO)

    IV10201 contains detail records for receipts like lot numbers/quantities, serial numbers,

    IV30300 is an inventory transactions amounts history table.  When Inventory is adjusted, records are written here.

    Don't you have a copy of our Information Flow and Posting book?  It has all of this detailed our.  It should be on every support person's desk

    Richard L. Whaley Author, Publisher, Consultant

    http://www.AccoladePublications.com

    Enhancing your Dynamics Knowledge!

  • What the bad posting did was not complete and some of the items went through correctly and others did not. The trick is finding where the posting stopped. I am finding incomplete records amongst these three tables. I appear to have the IV30300 corrected but I am finding updated IV10200.QTYSOLD with missing corresponding records in the IV10201 tables. These bad numbers are displayed when running Item Stock Quantity Inquiry. Can you give me a hint of how that screen works behind the scenes?

    Otherwise, hopefully we will run into each other at Convergence and you can show me this book.

    Richard E. Wheeler 2013 and 2014 MVP

    Member Microsoft Academic Alliance

    www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10

  • I think I found the missing link. I had to make sure I pulled the matching receipt number.

    IV10201.SRCRCTSEQNM=IV10200.RCTSEQNM

    If the receipt numbers did not match, IV reconcile would recreate the records.

    Richard E. Wheeler 2013 and 2014 MVP

    Member Microsoft Academic Alliance

    www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10

  • A small correction, IV10201 contains the "out" transactions for each record in IV10200, it simply represents the details of QTYSOLD field of IV10200.

    IV10200 holds all the receiving's with all available cost layers and all the updates over the cost layers across the days like "Cost Adjustments" and "Landed Cost" updates.

    IV30300 is the inventory history, it holds all the transactions that affects the inventory modules with "historical figures" to keep your documents synched with your archive, it shows the transactions figure as of the day of occurrence.

    A simple workflow, once you create a receiving one record will be created in IV10200 for each line and one record will be created in IV30300, once you create a sales transaction a record will be created in IV10201 and the corresponding record in IV10200 will be updated to reflect the exported quantities; the relation between tables are exactly as explained by Richard, finally another record for each item will be created in IV30300 to keep the history of the transaction.

    Hope that this helps.

    *** If my answer resolved you issue, please verify the answer to make it easy for other users who has the same case ***

    Regards,

     

    Mohammad R. Daoud MVP-MCT

    Have you seen the GP Excel Paste yet? http://di.jo/GPExcelPaste.aspx

  • What I need to correct is IV10200.QTYSOLD. Somehow with this bad batch quantities were removed from this field because it doubled up the invoices that posted. I have cleaned up the IV30300 table and now I am cleaning up the IV10201 table.

    Richard E. Wheeler 2013 and 2014 MVP

    Member Microsoft Academic Alliance

    www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10

  • Nothing more valuable to add up, just in case the reconciliation didn't get it solved, you need to check the corrupted cost layers on the back end. A script and further illustration is provided below;

    Inventory Reconciliation - Purchase Receipt Work and Details

    Hope this helps, 

    Best Regards,

    Mahmoud M. AlSaadi

    Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

  • Very useful script which confirmed what I have been doing is correct. When the batch duplicated the sales orders it updated both the IV10200 and IV10201 tables. I need to remove the duplicate transaction records from IV10201 while at the same time adjust the IV10200.QTYSOLD field. That is what I have been doing and I am about halfway through. It messed up about 125 items.

    Richard E. Wheeler 2013 and 2014 MVP

    Member Microsoft Academic Alliance

    www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10

  • By the way, that script found about 200 other items that have absolutely nothing to do with this issue. I will need to visit these items later.

    Richard E. Wheeler 2013 and 2014 MVP

    Member Microsoft Academic Alliance

    www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10

  • Good luck Sir Richard,

    The main essence of the script is that several times, the reconciliation doesn't get purchase receipt work and details tied as supposed to, several reasons could be behind that. The script ensures that all corrupted cost layers are retrieved and well handled.

    One more thing to consider, after ensuring that IV10200 and IV10201 are tied correctly, the next step is to compare both of them with IV30300 and SEE30303 (if deployed). That's all part of "internal" inventory reconciliation which should be a prerequisite for inventory versus GL reconciliation.

    Your feedback is highly appreciated,

    Best Regards,

    Mahmoud M. AlSaadi

    Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

  • Very good points. What happened here was the in the IV30300 table it as easy to remove these bogus records. All the sales order numbers had a 001 after them. One good thing is that nothing every made it down to the ledger or the SEE30303 tables. So it messed up the IV10200, IV10201 and IV30300 tables. Thanks to your script it has given me reassurance that I am proceeding correctly.

    Richard E. Wheeler 2013 and 2014 MVP

    Member Microsoft Academic Alliance

    www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10

  • 'We will be at Convergence in a booth straight back, slightly to the left.  Stop by and say hello at least.

    Richard L. Whaley Author, Publisher, Consultant

    http://www.AccoladePublications.com

    Enhancing your Dynamics Knowledge!

  • I will do that. By the way, in this case the IV10201.CMPSEQNM went to one for all of these bogus transactions. Just another example of how messed there transactions are. None of these items are components of anything and never have been.

    Richard E. Wheeler 2013 and 2014 MVP

    Member Microsoft Academic Alliance

    www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10