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.