Skip to Content
0
Former Member
Jul 14, 2009 at 07:33 PM

Query problem, lot administered and non lot administered items

25 Views

First some background on the issue: I created a user field (U_UbicCuu) for the physical location of every item on our warehouse, this field consists of 4 parts/characters, first one is the area (P, S, and R), 2nd and 3rd are the rack number (01, 02, 03, etc) 4th is the level (A, B, C, etc)...

Now my problem:

I created a query to tell me the inventory for each location (P12 for the complete 12th "P" rack, or S03A for the first level of the 3rd "S" Rack, etc.), but this gave me only the code, description, qty and location of the items and now I also need the lot number and expiration date (when applicable), here is where the real issue began, as soon as I included OIBT fields the results only included those items that were controled by lot/exp date, and excluded the rest...

Here's the first query (this one does work, it's simple enough)

SELECT

T0.[ItemCode], T0.[ItemName], T1.[OnHand], T0.[U_UbicCUU]

FROM

OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode

WHERE

T1.[WhsCode] = '01-01' and T1.[OnHand] > '0' and T0.[U_UbicCUU] like '%%(%0)%%'

ORDER BY

T0.[U_UbicCUU], T0.[ItemCode]

Here's the second one (the one I have problems with)

SELECT

T0.[ItemCode], T0.[ItemName], T1.[OnHand], T2.[Quantity], T2.[BatchNum], T2.[ExpDate], T0.[U_UbicCUU]

FROM

OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OIBT T2 ON T0.ItemCode = T2.ItemCode

WHERE

T1.[WhsCode] = '01-01' and T0.[U_UbicCUU] like '%%(%0)%%' and T1.[OnHand] > '0'

ORDER BY

T0.[U_UbicCUU], T0.[ItemCode]

I've tried several conditions but I haven't been able to get the second query to show me every item at X location, including both lot/exp date controled items and "regular" items

Has anyone run into this problem? what can I do to include both types of items on the results of this query?

Thanks in Advance

Hugo