Struggling - Please help the DocNum from OWOR is not what I am expecting!

[Former Member]

Hi all,

Thanks for checking this out, I can't seem to get the *actual* DocNumber no matter how hard I try so I think I'm probably looking at the wrong table, not OWOR or I'm not joining it correctly? Is there anyone who can possibly work it into the below query or give some help / advice..?

SELECT
T4.DistNumber AS [BatchNum],
T1.SL1Code AS [LocationTXT],
(SELECT TOP 1 OWOR.DOCNUM FROM [NASAP01].[CP_UK_LTD].[dbo].OWOR OWOR WHERE OWOR.ItemCode = T0.ItemCode ORDER BY OWOR.DOCNUM DESC) AS PPO,
GETDATE() AS [ReceiptDT],
NULL AS [IssueDT],
T2.[OnHandQty] AS [Qty]

FROM
[NASAP01].[CP_UK_LTD].[dbo].OIBQ T0
INNER JOIN [NASAP01].[CP_UK_LTD].[dbo].OBIN T1 ON T0.BinAbs = T1.AbsEntry and T0.onHandQty <> 0
LEFT JOIN [NASAP01].[CP_UK_LTD].[dbo].OBBQ T2 ON T0.BinAbs = T2.BinAbs and T0.ItemCode = T2.ItemCode and T2.onHandQty <> 0
LEFT JOIN [NASAP01].[CP_UK_LTD].[dbo].OSBQ T3 ON T0.BinAbs = T3.BinAbs and T0.ItemCode = T3.ItemCode and T3.onHandQty <> 0
LEFT JOIN [NASAP01].[CP_UK_LTD].[dbo].OBTN T4 ON T2.SnBMDAbs = T4.AbsEntry and T2.ItemCode = T4.ItemCode
LEFT JOIN [NASAP01].[CP_UK_LTD].[dbo].OSRN T5 ON T3.SnBMDAbs = T5.AbsEntry and T3.ItemCode = T5.ItemCode

Many thanks!

Phil

[Former Member]

PS None of the matching DocNums matching the DistNumber are correct although at first glance they have the same number of digits and begin with 4000 which initially made me believe I was on the right track with OWOR.

[Former Member]

Thanks very much Johan! I apologise for being vague, I'm just confused about this.

Funnily enough, although the DocNum 'appears' the same length and in the same range of numbers none of them match up to what I am expecting in conjunction with the DistNumber. I have the wrong table but I cant seem to find the right one that appears as the DocNum in the front-end as associated with the DistNumber.

Are there other 'O' tables that have DocNum's I can try against DistNumbers do you know?

[Johan_H]

H Phillip,

Question is, what is the "...*actual* DocNumber..." to you, the newest, the oldest (that is probably what you are getting now), or a specific one?

For the newest try using MAX(OWOR.DocNum) instead of TOP 1

For a specific one, you need to tell us specific how?

Regards,

Johan