Just upgraded from GP8 to GP10
Ran a query on the IV10200 table...
select * from iv10200 where rcptsold = 1 and qtyonhnd > 0. I get 1,131 rows returned. Should the IV10200 look this way?
Thanks
*This post is locked for comments
Hi,
I have one more question regarding this discussion...
Actually when you return the invoice , it is not decrementing the existing QtySold column.
It is creating new row with QtyReceived. Problem here is ...
I posted finish goods and raised invoice against that MO .
Now Qty Sold and Received are tallyed. We found that prodcution cost is not proper.
So we made return transactions for ivoice..Now I want to reverse the finish good..But due to IV10200 Qtysold column it saysing" item sold , you cannot reverse". Actually I returned that sold items.
To resolve this problem I manully updating the qtysold= 0 and reversing the finish goods...once again i update the iv10200 qtysold with old value..
I dont know whther my approach is corect or not..Please advice me
I never use the qtyonhand column from the IV10200 for anything at all. On my local laptop the column is set to zero for all records regardless of the 'balance' of the layer. QTYRECVD = 200 QTYSOLD = '0' QTYONHND = '0'.....
QTYONHND is always derived from the IV00102 table. You can do the following:
1. Write SQL script to see if flag is correct in IV10200.
2. Write SQL script to compare 'balance' in IV10200 table to the QTYONHND in IV00102 (this tricky will need to sum records and compare by LOCNCODE).....
Good Luck
I understand the layering part. I am not comparing the qty on hand to anything other than what is in the IV10200 table. I understand the one to many. That was not my focus of my question. All I am trying to understand is if for the above example where qtyrecvd=200, qtysold=200 qtyonhnd=200, and rcptsold =1 is why the row indicates we received 200, sold 200 and within the same table in the same row the qtyonhnd is 200, when the row clearly is indicating we already sold 200 out of this one receipt layer. I am not focusing on the IV00102 at all. It was not a part of my question.
It is my supposition that for this one layer the qtyonhnd column in the iv10200 should decrease as the qtysold goes up, so that if we sold 200 out of this layer, that the qtyonhnd (in the iv10200 table on the same row) should be zero. Then the rcptsold flag should flip from 0 to 1. The only other question following that is when exactly the rcptsold flag is flipped to 1. Is it when the layer is completely sold out? Meaning that qtyrecvd = qtysold (in this case 200 vs 200) or is it when just 1 is sold out of this particular layer?
This is how I think it SHOULD work. Say we reciept a layer for XYZ item, this is a new product so no other layers exist. We receipt 200 in. Don't sell any for one week. My thinking is that the iv10200 would show qtyrecvd = 200 qtysold=0 qtyonhnd=200 rcptsold = 0. Then we sell 50 out of this layer so that the layer would show qtyrecvd = 200 qtysold = 50 qtyonhnd =150 rcptsold = 0. Then we sell the remaining 150 out of this layer so that the layer would show qtyrecvd = 200 qtysold = 200 rcptsold = 1.
If this is how it is supposed to work, my question is why does my system show that we have qtyonhnd remaining in the layer when qtysold = qtyrevd. Again, this is not refering to the iv00102.
My deepest apologizes for not elucidating my question correctly in my previous replies. I hope this is sufficient to convey my question correctly.
This is a 1 (item) to many (receipt layers) relationship. The item only has 1 on hand while it could be composed of many layers. So a query comparing the on hand to each layer will cause confusion such as this question you posed. The query you would want would be to compare the qty rec'd vs the qty sold for each layer. If you want to compare the on hand in the IV10200 with the qty on hand in the IV00102, then it makes sense. What were you trying to determine with the query. I am an OK reader of queries, but a bit of novice writing them.
Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com
Perhaps some more specifics for everyone would help me understand.
For example, on one row what I am encountering is a row/ layer with qtyrecvd=200, qtysold=200, qtyonhnd=200, and rcptsold = 1. I did not expect the qtyonhnd to have a value greater than zero in it with the rcptsold flag set to true when qtyrecvd and qtysold equal each other.
I have multiple rows for various part numbers like this.
There can be many receipt layers per item so there could be 10 layers and 9 are completely sold, with the 10th layer all available. Your query would return that to be true. I better query would be to compare the qty rec'd and the qty sold and see if any of those are 0 when they do not equal each other.
Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com
But the thing I don't understand is why the flag for rcptsold is set to true when I still have quantity left in the layer? That is what the query is addressing, I have rcptsold flags for layers set to true, yet quantity remaining in the layer. Is this how it is suppose to look? My thought is the rcptsold should be set to false until the layer is completely sold.
Dan is correct (of course). The IV10200 table is the layer table. Each receipt is written to this table and as items are sold the quantity sold is incremented on the appropriate layer until the quantity sold is equal to the quantity received. Then the flag is set. Issues are posted to the records depending on whether your valuation method is FIFO or LIFO
Yes. What that statement will do is show you all the sold receipt layers. If you brought in a PO of 50 items, and sold all 50 items, then the rcptsold gets changed from a 0 to a1.
Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (