Trabalho em uma industria (motocicletas) onde onde utilizamos ANO/MOD (modelo) e ANO/FAB (fabricação), porem em um relatorio esta saindo o seguinte :
Modelo 2021 : 50 produzidos
Desses 50 são :
5 Modelos 2021 Fabricados 2020
45 Modelos 2021 Fabricados 2021
Porem o resultado esta vindo como :
50 Modelos 2021 Fabricados 2021
Possuo a seguinte query :
SELECT DISTINCT mmt.inventory_item_id,
-----
rtrim ( msib.segment1,' ') MATERIAL,
rtrim ( msib.description, ' ') DESCRICAO,
-----
(select msn1.attribute2 from MTL_serial_numbers msn1
where 1=1
and msn1.inventory_item_id = mmt.inventory_item_id
and msn1.ship_date is null
and rownum = 1 ) 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') -
NVL((SELECT SUM(NVL(mmt1.reservation_quantity,0))
FROM mtl_reservations mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id),0) SALDO_EXP,
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) SUB_CTMA,
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) SUB_CQTB,
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_GER'),0) SUB_GER,
( 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_EXP'),0) -
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_GER'),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 = mde.inventory_item_id
and mde.element_name = 'ANO/MOD'
and msib.organization_id IN ( 149,150,154, 151)
ORDER BY 1;
(select msn1.attribute2 from apps.MTL_serial_numbers msn1
where 1=1
and msn1.inventory_item_id = mmt.inventory_item_id
and msn1.ship_date is null
and rownum = 1 ) ANO_FAB,
Como tem 2 resultados a informação deve ser utilizada fora da consulta.
Porém como você tem 2 anos de fabricação e usa uma somatória para a quantidade em estoque por item.
(SELECT SUM(NVL(mmt1.transaction_quantity,0))
FROM apps.MTL_ONHAND_QUANTITIES mmt1
WHERE mmt1.inventory_item_id = mmt.inventory_item_id
AND mmt1.subinventory_code = 'KMB_EXP') SUB_EXP,
Será retornado o ano de fabricação mas a quantidade total para cada linha.
Alguém consegue me ajudar a resolver isso ?