Microsoft Dynamics GP (Archived)

Table and field for On Hand for In Transit

(0) Share

Report

Posted on by Community Member

I found on Victoria Yudin's site that I can use table IV10200 and condition PCHSRCTY = 8.

I created this SQL, but it works for some items, but for other items it doesn't work. Is it right table and field?

select TOP 1 QTYSOLD,

from IV10200

where ITEMNMBR = 'D3028'

and TRXLOCTN = 'TRANSIT'

and PCHSRCTY = 8

order by DATERECD desc

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (3)

Tim Wappat 5,703 on at

Like (0)

Report

RE: Table and field for On Hand for In Transit

You do not explain entirely what you hope to get from the query, what you are trying to achieve.

I'll take a guess and say you are only looking at purchase receipts in this table so if you have stock movements that are a result of a stock adjustment, for example a transfer from location to another, or an adjustment transaction adjusting stock in without going through the purchasing process and goods receipt, then these may not be picked up by what you are doing.

Could this be the issue?

Tim.

Was this reply helpful? Yes No
Verified answer

Victoria Yudin 22,768 on at

Like (0)

Report

RE: Table and field for On Hand for In Transit

Tim's right, we need some more information. 2 other things:

You mention "on hand", that can also be found in table IV00102.

Also, not clear why you are using TOP 1 in your code?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Table and field for On Hand for In Transit

Here is the screen from which I want to show On Hand in Transit on my report. This Qty - 16 - what table and field is this?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Table and field for On Hand for In Transit

Tim, how can I insert and picture in my response?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Table and field for On Hand for In Transit

I used Top 1 to select the latest record. It was many records in this table for this Item Number and Transaction Location = 'TRANSIT'.

It looks like table IV00102 works for my report (I need to validate for more items):

select QTYONHND,*

from IV00102

where locncode = 'TRANSIT'

AND ITEMNMBR = 'EMB302542'

Victoria, is it possible to insert a picture (print screen) in this forum? It would be easier to show what field I'm looking for.

Was this reply helpful? Yes No
Verified answer

Tim Wappat 5,703 on at

Like (0)

Report

RE: Table and field for On Hand for In Transit

Switch to rich editor view using the hyperlink (if the reply is not already in that mode), then click the portrait icon, use the browse button to find the screen shot, uploads then lick the insert button. See screen shots below.

IV00102 the query you have will replicate what you see in this window (excuse that I've modified ours slightly that how yours will look):

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Table and field for On Hand for In Transit

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Table and field for On Hand for In Transit

Tim, thanks! I posted a screen below. Could you validate that this sql is right for this on-hand qty at the screen:

select QTYONHND

from IV00102

where locncode = 'TRANSIT'

AND ITEMNMBR = 'EMB302542'

Was this reply helpful? Yes No
Verified answer

Tim Wappat 5,703 on at

Like (0)

Report

RE: Table and field for On Hand for In Transit

Yes you are spot on with your query.

Was this reply helpful? Yes No