Order Hold Information Query

 SELECT

   ooh.order_number "Order Number",

       ooh.ordered_date "Ordered Date",
       ooh.flow_status_code "Order Status",
       ohd.name "Hold Name",
       ohs.released_flag "Hold Release Flag",
       ohr.release_reason_code "Hold Release Reason",
       ohr.creation_date "Hold Release Date",
       ooh.payment_type_code "Payment Type Code"
FROM apps.oe_order_headers_all ooh,
     apps.oe_order_holds_all hld,
     apps.oe_hold_sources_all ohs,
     apps.oe_hold_definitions ohd,
     apps.oe_hold_releases ohr
WHERE ooh.ordered_date BETWEEN :P_DATE_FROM AND :P_DATE_TO
      AND ooh.order_category_code = 'ORDER'
      AND ooh.header_id=hld.header_id(+)
      AND hld.hold_source_id=ohs.hold_source_id(+)
      AND ohs.hold_id=ohd.hold_id(+)
      AND ohs.hold_release_id=ohr.hold_release_id(+)
ORDER BY ooh.order_number