select
moq.item_num料號
,moq.SUBINVENTORY_CODE 倉庫
,sum(moq.qty) 庫存量
,moq.p_uom單位
,c.item_cost成本
,sum(moq.qty)*c.item_costTotal
,moq.descr料號品名描述
from CST_ITEM_COST_TYPE_Vc,
-- ,MTL_SYSTEM_ITEMS_Bmsi,
(
selectmsi.organization_id organization_id
,msi.inventory_item_id inventory_item_id
,msi.segment1item_num
,msi.description descr
,PRIMARY_UOM_CODE p_uom
,j.SUBINVENTORY_CODE SUBINVENTORY_CODE
,j.LOCATOR_IDLOCATOR_ID
,nvl(mil.segment1 ,'') loc
,nvl(j.TRANSACTION_QUANTITY,0) qty
from mtl_onhand_quantities j
,mtl_material_transactions mmt
,MTL_SYSTEM_ITEMS_Bmsi
,MTL_ITEM_LOCATIONS mil
WHEREmsi.organization_id=j.ORGANIZATION_ID(+)
and msi.organization_id=12
AND MSI.ITEM_TYPE in('1-成品','3-原料','2-半成品','9-下腳/水口料','6-委外件','7-總務用品')
and msi.inventory_item_id=j.INVENTORY_ITEM_ID(+)
and j.CREATE_TRANSACTION_ID=mmt.TRANSACTION_ID(+)
and trunc(mmt.TRANSACTION_DATE) between to_date( '31-dec-2004', 'DD-MON-YYYY') and to_date( '31-dec-2005', 'DD-MON-YYYY')
and j.LOCATOR_ID=mil.inventory_location_id(+)
) moq
WHEREmoq.inventory_item_id=c.INVENTORY_ITEM_ID
and c.DEFAULT_COST_TYPE_ID = 1
and c.ORGANIZATION_ID=moq.ORGANIZATION_ID
and c.ORGANIZATION_ID=12
group by moq.item_num,moq.SUBINVENTORY_CODE,moq.loc,moq.descr,moq.p_uom,moq.inventory_item_id,c.item_cost
order by 1
|
