SELECT
segment1 item,
organization_code,
reserved_qty,
order_number,
line_number,
locator_id,
location
FROM
(
SELECT
msib.segment1,
mp.organization_code,
msib.organization_id,
msib.purchasing_item_flag,
msib.shippable_item_flag,
msib.inventory_item_flag,
lot_control_code,
decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',
5,
'At Receipt',
6,
'At Issue') "Serial Control",
mc.segment1 "PPL",
mc.segment2 "SPL",
mc.segment3 "ITEM CATEGORY",
mr.reservation_quantity reserved_qty,
mr.demand_source_header_id,
h.order_number,
l.line_number,
mr.locator_id,
milk.CONCATENATED_SEGMENTS location
-- (
-- SELECT
-- nvl(SUM(reservation_quantity), 0)
-- FROM
-- mtl_reservations
-- WHERE
-- inventory_item_id = msib.inventory_item_id
-- AND organization_id = msib.organization_id
-- ) reserved_qty
FROM
mtl_system_items_b msib,
mtl_item_categories mic,
mtl_categories mc,
mtl_parameters mp,
mtl_reservations mr,
oe_order_headers_all h,
oe_order_lines_all l,
apps.mtl_item_locations_kfv milk
WHERE
msib.inventory_item_id = mic.inventory_item_id
AND msib.organization_id = mic.organization_id
AND msib.organization_id = mp.organization_id
AND mic.category_id = mc.category_id
AND mic.category_set_id = 1
AND mc.structure_id = 101
AND msib.enabled_flag = 'Y'
AND msib.lot_control_code = 1
-- AND msib.segment1='NTTC90ABE6'
AND nvl(msib.end_date_active, sysdate) >= sysdate
AND mc.disable_date IS NULL
AND mp.organization_code IN ('MAS') --Org CODE
AND mr.inventory_item_id = msib.inventory_item_id
AND mr.organization_id = msib.organization_id
AND h.header_id = l.header_id
AND nvl(l.cancelled_flag, 'N') <> 'Y'
AND l.line_id = mr.demand_source_line_id
AND mr.locator_id=milk.INVENTORY_LOCATION_ID(+)
AND mr.organization_id = milk.organization_id(+)
AND msib.item_type='P'
ORDER BY
mc.segment1
)
WHERE
reserved_qty <> 0
No comments:
Post a Comment