| inventory on hand quantity [message #270005] |
Tue, 25 September 2007 07:02  |
Abeer30
Messages: 110
Registered: March 2007
|
Senior Member |
|
|
hello,
i have developed a query that that is supposed to bring me back inventory on hand quantities on any sdate range that i specify, but it always bring me the quantity upto date. can u please check it out and tell me what's wrong with it?
SELECT
qoh.SUBINVENTORY_CODE as "Subinventory"
,msi.INVENTORY_ITEM_ID
,qoh.LOCATOR_ID
,il.SEGMENT2 as "row"
,il.SEGMENT3 as "rack"
,il.SEGMENT4 as "bin"
,msi.segment1 as "item"
,msi.DESCRIPTION as "description"
,msi.PRIMARY_UNIT_OF_MEASURE as "unit of measure"
,sum(qoh.transaction_quantity)
,cic.item_cost as "item cost"
,cic.ITEM_COST*(sum(qoh.transaction_quantity)) as total
,qoh.DATE_RECEIVED as "date received"
FROM
mtl_system_items_b msi
,mtl_onhand_quantities qoh
,cst_item_costs cic
,MTL_ITEM_LOCATIONS il
,MTL_SECONDARY_INVENTORIES ms
WHERE
msi.inventory_item_id = qoh.inventory_item_id
and msi.inventory_item_id = cic.inventory_item_id
and il.INVENTORY_LOCATION_ID(+)=qoh.LOCATOR_ID
and cic.ITEM_COST <>0
and cic.organization_id = msi.organization_id
and cic.COST_TYPE_ID=2
and ms.ORGANIZATION_ID=qoh.ORGANIZATION_ID
and ms.SECONDARY_INVENTORY_NAME=qoh.SUBINVENTORY_CODE
and qoh.organization_id = nvl(:P_ORG_ID, qoh.organization_id)
AND ms.SECONDARY_INVENTORY_NAME=NVL(:P_Sub,ms.SECONDARY_INVENTORY_NAME)
AND trunc(qoh.DATE_RECEIVED) between nvl(:p_date_from, trunc(qoh.DATE_RECEIVED))
AND nvl(:p_date_to, trunc(qoh.DATE_RECEIVED))
and qoh.SUBINVENTORY_CODE='MWarehouse' --- record count 58
GROUP BY
qoh.SUBINVENTORY_CODE
,msi.segment1
,qoh.DATE_RECEIVED
,msi.DESCRIPTION
,msi.PRIMARY_UNIT_OF_MEASURE
,msi.organization_id
,cic.item_cost
,msi.INVENTORY_ITEM_ID
,qoh.transaction_quantity
,qoh.CREATE_TRANSACTION_ID
,qoh.UPDATE_TRANSACTION_ID
,qoh.LOCATOR_ID
,il.SEGMENT2
,il.SEGMENT3
,il.SEGMENT4
,qoh.DATE_RECEIVED
order by
msi.INVENTORY_ITEM_ID
|
|
|
|
| Re: inventory on hand quantity [message #270209 is a reply to message #270005] |
Wed, 26 September 2007 02:46  |
vin_odks
Messages: 153
Registered: July 2006
Location: -
|
Senior Member |
|
|
MTL_ONHAND_QUANTITIES is a view which contains consolidate information of On hand quantity
Use MTL_MATERIAL_TRANSACTIONS table , which contains records of all Material transactions (Issues,Receipts)as of the TRANSACTION_DATES.
Regards
Vinod
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
