GP 2010 IV Transaction tables relationships

(0) Share

Report

Posted on by Richard Wheeler

75,850 Moderator

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.

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (1)

Suggested answer

Richard Whaley 25,195 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

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,

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report

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,

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No