Wednesday, December 12, 2018

Query to Find Order Management QUERY in Oracle Apps R12

SELECT   oh.order_number, ol.ordered_item, org.organization_name,
         ol.ordered_quantity, ol.shipped_quantity,         
         rsv.reservation_quantity,
         lkup.meaning pick_status, oh.org_id
FROM     oe_order_headers_all oh,
         oe_order_lines_all ol,
         oe_transaction_types_tl typ,
         wsh_delivery_details wdd,
         mtl_reservations rsv,
         org_organization_definitions org,
         fnd_lookup_values lkup
WHERE oh.header_id = ol.header_id
     AND oh.order_type_id = typ.transaction_type_id
     AND typ.NAME NOT LIKE '%Internal%Order%'
     AND wdd.source_header_id = oh.header_id
     AND wdd.source_line_id = ol.line_id
     AND wdd.released_status = lkup.lookup_code
     AND lkup.lookup_type = 'PICK_STATUS'
     AND org.organization_id = ol.ship_from_org_id
     AND rsv.demand_source_header_id(+) = ol.header_id
     AND rsv.demand_source_line_id(+) = ol.line_id
     AND ol.ordered_item LIKE 'X%'
     AND ol.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
ORDER BY order_number;



SELECT   rh.segment1 requisition_number, hrl.location_code requesting_org,
         oh.order_number, org.organization_name fulfillment_org,
         it.segment1 item_number, rl.quantity
 FROM po_requisition_headers_all rh,
         po_requisition_lines_all rl,
         hr_locations hrl,
         mtl_system_items_b it,
         oe_order_headers_all oh,
         oe_order_lines_all ol,
         org_organization_definitions org
WHERE rh.requisition_header_id = rl.requisition_header_id
     AND rl.deliver_to_location_id = hrl.location_id
     AND it.inventory_item_id = rl.item_id
     AND it.organization_id = rl.source_organization_id
     AND rl.requisition_header_id = oh.source_document_id
     AND ol.ship_from_org_id = org.organization_id
     AND oh.header_id = ol.header_id
     AND ol.inventory_item_id = rl.item_id
     AND it.segment1 LIKE 'X%'
     AND rh.authorization_status = 'APPROVED'
     AND ol.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
ORDER BY rh.segment1;

No comments:

Post a Comment