web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / IV10200 rcptsold vs qt...
Microsoft Dynamics GP (Archived)

IV10200 rcptsold vs qtyonhand

(0) ShareShare
ReportReport
Posted on by Community Member

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

I have the same question (0)
  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: IV10200 rcptsold vs qtyonhand

    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

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    Re: IV10200 rcptsold vs qtyonhand

    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

  • Community Member Profile Picture
    Community Member on at
    Re: IV10200 rcptsold vs qtyonhand

    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 Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: IV10200 rcptsold vs qtyonhand

    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

  • Community Member Profile Picture
    Community Member on at
    Re: IV10200 rcptsold vs qtyonhand

    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.

  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: IV10200 rcptsold vs qtyonhand

    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

  • Community Member Profile Picture
    Community Member on at
    Re: IV10200 rcptsold vs qtyonhand

    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.

  • Bron Profile Picture
    Bron 4 on at
    Re: IV10200 rcptsold vs qtyonhand

    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

  • Jeganeedhi Krishnan Profile Picture
    Jeganeedhi Krishnan 2,500 on at
    Re: IV10200 rcptsold vs qtyonhand

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans