Desenvolvemos um relatório onde trás itens fabricados/modelo por exemplo :
ANO_FAB | ANO_MOD
2018. 2019
2019. 2020
2020. 2020
Porem o saldo que vem esta buscando somente de um período.
Como esta :

Como tem que ser :

Para isso estamos usando a seguinte quero :
select DISTINCT mmt.inventory_item_id ,
-----
rtrim ( msib.segment1,' ') MATERIAL,
rtrim ( msib.description, ' ') DESCRICAO,
-----
msn.attribute2 ANO_FAB,
mde.element_value ANO_MOD,
(SELECT SUM(NVL(mmt1.transaction_quantity,0))
FROM MTL_ONHAND_QUANTITIES mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id
AND mmt1.subinventory_code = 'KMB_EXP') SUB_EXP,
(SELECT SUM(NVL(mmt1.reservation_quantity,0))
FROM mtl_reservations mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id) RESERVA,
(SELECT SUM(NVL(mmt1.transaction_quantity,0))
FROM MTL_ONHAND_QUANTITIES mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id
AND mmt1.subinventory_code = 'KMB_EXP') -
(SELECT SUM(NVL(mmt1.reservation_quantity,0))
FROM mtl_reservations mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id) SALDO_EXP,
(SELECT SUM(NVL(mmt1.transaction_quantity,0))
FROM MTL_ONHAND_QUANTITIES mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id
AND mmt1.subinventory_code = 'KMB_CTMA') SUB_CTMA,
(SELECT SUM(NVL(mmt1.transaction_quantity,0))
FROM MTL_ONHAND_QUANTITIES mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id
AND mmt1.subinventory_code = 'KMB_CQTB') SUB_CQTB,
( (SELECT SUM(NVL(mmt1.transaction_quantity,0))
FROM MTL_ONHAND_QUANTITIES mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id
AND mmt1.subinventory_code = 'KMB_EXP') -
NVL((SELECT SUM(NVL(mmt1.reservation_quantity,0))
FROM mtl_reservations mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id) ,0)+
NVL( (SELECT SUM(NVL(mmt1.transaction_quantity,0))
FROM MTL_ONHAND_QUANTITIES mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id
AND mmt1.subinventory_code = 'KMB_CTMA'),0)+
NVL( (SELECT SUM(NVL(mmt1.transaction_quantity,0))
FROM MTL_ONHAND_QUANTITIES mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id
AND mmt1.subinventory_code = 'KMB_CQTB'),0)) TOTAL
FROM
mtl_onhand_quantities mmt
, mtl_system_items_b msib
, mtl_serial_numbers msn
, mtl_descr_element_values mde
where mmt.inventory_item_id = msib.inventory_item_id
and mmt.organization_id = msib.organization_id
and mmt.inventory_item_id = msn.inventory_item_id
and mmt.inventory_item_id = mde.inventory_item_id
and mde.element_name = 'ANO/MOD'
--and mmt.inventory_item_id in (16132 , 16120, 16121)
ORDER BY 1