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
Yes you are spot on with your query.
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'
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):
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.
Tim, how can I insert and picture in my response?
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?
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?
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.
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,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (