Query for Standard and Blanket Purchase order Details

Below query can be handy to retrieve details for Standard Purchase order and Blanket Purchase order with releases. The query is not completely tested and is only for reference.

SELECT poh.segment1 po_number
     , poh.type_lookup_code
     , pr.release_num
     , poh.creation_date
     , pv.vendor_name supplier
     , pvs.vendor_site_code supplier_site_code
     , hl.location_code ship_to_location_code
     , pb.agent_name buyer_name
     , msi.segment1 item_number
     , msi.description item_desc
     , msi.inventory_item_status_code item_status
     , pll.quantity
     , pll.quantity_received
     , pll.quantity_cancelled
     , pll.quantity_billed
     , pol.unit_price
     , mp.organization_code receiving_org_code
     , (SELECT mc.concatenated_segments
        FROM mtl_categories_kfv mc, mtl_item_categories mic, mtl_category_sets mcs
        WHERE mcs.category_set_name = 'PURCHASING'
          AND mcs.category_set_id = mic.category_set_id
          AND mic.inventory_item_id = msi.inventory_item_id
          AND mic.organization_id = msi.organization_id
          AND mic.category_id = mc.category_id)
          po_category
FROM po_headers_all poh
   , po_lines_all pol
   , po_line_locations_all pll
   , po_releases_all pr
   , mtl_system_items msi
   , org_organization_definitions mp
   , po_vendors pv
   , po_vendor_sites_all pvs
   , po_agents_v pb
   , hr_locations hl
   , hr_operating_units hou
WHERE poh.type_lookup_code IN ('BLANKET', 'STANDARD')
  AND msi.inventory_item_id = pol.item_id
  AND msi.organization_id = pll.ship_to_organization_id
  AND mp.organization_id = msi.organization_id
  AND poh.po_header_id = pol.po_header_id
  AND pol.po_line_id = pll.po_line_id
  AND pr.po_header_id(+) = poh.po_header_id
  AND NVL (pll.po_release_id, 1) = NVL (pr.po_release_id, 1)
  AND poh.vendor_id = pv.vendor_id
  AND poh.vendor_site_id = pvs.vendor_site_id
  AND pvs.vendor_id = pv.vendor_id
  AND pb.agent_id = poh.agent_id
  AND hl.location_id = poh.ship_to_location_id
  AND poh.org_id = hou.organization_id
  AND hou.short_code = 'VIS-US'
ORDER BY poh.segment1, pr.release_num

Query for Drop Ship Order

Below query can be helpful to get integrated information for drop ship orders

SELECT h.order_number
     , l.line_number so_line_number
     , ph.segment1 po_number
     , l.ordered_item
     , l.ordered_quantity
     , por.release_num
     , pl.line_num po_line_number
     , ph.authorization_status
     , prh.interface_source_code
     , prh.segment1 requisition_number
     , prl.line_num requisition_line_number
     , ph.closed_date
     , ph.closed_code
     , ods.header_id so_header_id
     , ods.line_id so_line_id
     , prh.requisition_header_id
     , prl.requisition_line_id
     , ph.po_header_id
     , pl.po_line_id
     , pll.line_location_id po_line_location_id
     , por.po_release_id
FROM oe_drop_ship_sources ods
   , oe_order_headers_all h
   , oe_order_lines_all l
   , po_line_locations_all pll
   , po_lines_all pl
   , po_headers_all ph
   , po_requisition_headers_all prh
   , po_requisition_lines_all prl
   , po_releases_all por
WHERE h.header_id = l.header_id
  AND h.header_id = ods.header_id
  AND l.line_id = ods.line_id
  AND por.po_release_id(+) = ods.po_release_id
  AND ods.line_location_id = pll.line_location_id(+)
  AND ods.po_header_id = pl.po_header_id(+)
  AND ods.po_line_id = pl.po_line_id(+)
  AND ph.po_header_id(+) = pl.po_header_id
  AND prl.requisition_header_id(+) = ods.requisition_header_id
  AND prl.requisition_line_id(+) = ods.requisition_line_id
  AND prh.requisition_header_id(+) = prl.requisition_header_id
  AND h.order_number = :sales_order;