on 12-01-2011 2:24 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
96 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.