Help with a query.

[Former Member]

Hi There,

I've set up a query for a customer so he can run a report over a particular warehouse, showing Item code, Item description, Item Cost and Last Purchase price.

The query I've got is SELECT OITM.ITEMCODE, OITM.ITEMNAME, OITM.AVGPRICE, OITW.WHSCODE,OITM.LASTPURPRC FROM OITM,OITW WHERE OITW.WHSCODE='10'

Firstly, The above query ends with a report showing 6400 lines. Not sure why. Warehouse 10 only has 8 items in it but it seems to be showing all items.

Secondly, this only shows the item cost if the item has a valuation method of standard with a value set on the item master data record. I need it to see the item cost against the item in warehouse 10.

Secondly, rather than Last purchase price I wish to be able to use the price for various price lists. Could I set a condition whereby I need to select a price list before the query is executed?

Thanks in advance.

Steve.

[vasileiosfasolis · Accepted solution]

Hi Lowery

try this

SELECT 
a.ITEMCODE, 
a.ITEMNAME, 
a.AVGPRICE, 
b.WHSCODE,
a.LASTPURPRC 
FROM OITM a
inner join OITW b on a.itemcode=b.itemcode
inner join itm1 c on C.ITEMCODE=A.ITEMCODE
WHERE b.WHSCODE='10' and c.pricelist='[%0]''

for entering manually the pricelist

[Former Member · Accepted solution]

Hi there,

Thanks for the quick response.

This isn't quite what I was after. The selection it allows me to make doesn't seem to do anything.

I'm after a query that prompts me to select a price list by name i.e. Base Price, Regular Purchase Price, Regular Sales Price etc.

After selecting the price list I'd like the report to show Item Name, Item Description, Item Cost and price depending upon which price list was selected initially.

This would be for all items in Warehouse 10 only

Regards

Steve.

[former_member541807 · Accepted solution]

hi Steve,

try this

SELECT T0.[ItemCode], T0.[ItemName], T0.[AvgPrice] as 'ItemCost', T1.[Price] FROM OITM T0  INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OPLN T3 ON T1.PriceList = T3.ListNum WHERE T3.[ListName]  = '[%0]' and  T2.[WhsCode] = '[%1]'

regards,

Fidel

[Former Member · Accepted solution]

Hi,

Just tried the last query and that's more like what I was after.

Still having trouble with the item cost field. It only displays if there is a value in the item cost field when the valuation method is set to standard. I using moving average which seems holds the item cost at warehouse level rather than Item level.

Any ideas how to display this?

Regards

Steve.

[former_member541807 · Accepted solution]

Steve,

how about this,

SELECT T0.[ItemCode], T0.[ItemName], T0.[AvgPrice] as 'ItemCost_ItemLevel', T1.[Price], T2.[AvgPrice] as 'WH_Level' 
FROM OITM T0  INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OPLN T3 ON T1.PriceList = T3.ListNum 
WHERE T3.[ListName]  = '[%0]' and  T2.[WhsCode] = '[%1]'

regards,

Fidel

[former_member541807]

hi Steve,

try this query

SELECT T0.[ItemCode], T0.[ItemName], T0.[AvgPrice], T0.[LastPurPrc], T2.[WhsCode] FROM OITM T0  INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T0.ItemCode = T2.ItemCode WHERE T1.[PriceList]  = '[%0]' and  T2.[OnHand] <> 0

regards,

Fidel