OHQty Report with Sales Order Number

Yahoo Archive
1
OK, here’s a challenge: I need an OnHand Qty report that lists the Sales Order, which created a Job to put this in stock.

I’m hoping I’m tired and overthinking this...

So we have PartBin. I can obviously get my OH Qty’s and Bin from there. Luckily, we’re lot controlled so the Lot is also in PartBin.

So, now I take the LotNum and PartNum from PartBin and join to PartTran where PartTran.TranType = ‘MFG-STK’

So that gives me the record in PartTran on which we did a Job Receipt to Inventory. So now I have the JobNum that created the Lot for the PartBin record.

With the JobNum I now can tie into JobProd to link to the Sales Order Num that created that Job.

Is there any other obvious way I’m overlooking to accomplish this?

My problem is that JobProd is not a one-to-one relationship by using only the JobNum, so I get some duplication in my query when there is one SO with multiple lines with the same Part Number.

The reason for the Left Join on PartTran is sometimes we have parts in stock not lot controlled.

Here’s my query.... anyone have thoughts? Thanks in advance. It’s worth two drinks at Perspectives. :-)

SELECT
PB.PartNum,
PB.LotNum AS 'LOT',
PB.BinNum AS 'BIN',
PB.OnhandQty AS 'BIN QTY',
PB.DimCode AS 'DIM',
PT.JOBNUM AS 'JOB',
JP.ORDERNUM AS 'ORDER',
JP.ORDERLINE AS 'ORDLINE',
JP.ORDERRELNUM AS 'ORDREL'
FROM PUB.PartBin PB
LEFT JOIN PUB.PARTTRAN PT ON PB.PARTNUM = PT.PARTNUM AND PB.LOTNUM = PT.LOTNUM AND PT.TRANTYPE = 'MFG-STK'
INNER JOIN PUB.JOBPROD JP ON PT.JOBNUM = JP.JOBNUM




Vic

[Non-text portions of this message have been removed]