SELECT
a.subinventory_code
子库代码
,d.DESCRIPTION
子库描述
,b.segment1
物料编码
,b.description
物料描述
,b.primary_unit_of_measure
单位
,SUM(a.primary_transaction_quantity)
数量
,C.material_cost
成本
,TO_CHAR(SUM(a.primary_transaction_quantity)*C.material_cost,‘99999999999999.9999‘)
金额
,to_char(SYSDATE,‘YYYY-MM-DD
HH:MM:SS‘) 报表日期
FROM
INV.MTL_ONHAND_QUANTITIES_DETAIL
A,
INV.Mtl_System_Items_b B
,
BOM.CST_ITEM_COSTS C
,
INV.MTL_SECONDARY_INVENTORIES d
WHERE
a.ORGANIZATION_ID = X
AND
a.organization_id=b.organization_id
AND
C.inventory_item_id(+)=b.inventory_item_id
AND a.inventory_item_id=b.inventory_item_id
--AND
a.subinventory_code LIKE ‘T%‘
AND
C.cost_type_id(+)=1000
AND
d.SECONDARY_INVENTORY_NAME=a.subinventory_code
AND d.organization_id=b.organization_id
GROUP
BY
b.segment1
,b.description
,a.subinventory_code
,b.primary_unit_of_measure
,C.material_cost
,d.DESCRIPTION
order
by
a.subinventory_code
原文:http://www.cnblogs.com/st-sun/p/3778461.html