Oracle WMS Exception Report

This query is for finding the users who has raised the exception while completing a task.



SELECT oodt.organization_code,
       milt.subinventory_code,
       milt.concatenated_segments,
       msit.segment1 item_number,
       mtr.reason_name,
       ppf.full_name
  FROM apps.wms_exceptions we,
       apps.mtl_item_locations_kfv milt,
       apps.org_organization_definitions oodt,
       apps.mtl_system_items_b msit,
       apps.mtl_transaction_reasons mtr,
       per_people_f ppf
 WHERE     1 = 1
       AND ppf.person_id = we.person_id
       AND ppf.effective_end_date > SYSDATE
       AND mtr.reason_id = we.reason_id
       AND msit.inventory_item_id = we.inventory_item_id
       AND msit.organization_id = we.organization_id
       AND oodt.organization_id = we.organization_id
       AND we.creation_date LIKE SYSDATE
       AND milt.organization_id = we.organization_id
       AND milt.subinventory_code = we.subinventory_code
       AND milt.inventory_location_id = we.inventory_location_id
       AND oodt.organization_code = :organization_code
       AND mtr.reason_name IN ('ZERO PICK',
                               'PARTIAL PICK',
                               'LOC BLOCKED',
                               'END PICK')
ORDER BY full_name, 3;