Query to get Order Hold Information

  SELECT ooh.order_number,
         ooh.ordered_date,
         ooh.flow_status_code,
         ooh.credit_card_approval_code,
         ohd.name "Hold Name",
         ohs.released_flag,
         ohr.release_reason_code,
         ohr.creation_date "Hold Release Date",
         ooh.payment_type_code
    FROM apps.oe_order_headers_all ooh,
         apps.oe_order_holds_all ohld,
         apps.oe_hold_sources_all ohs,
         apps.oe_hold_definitions ohd,
         apps.oe_hold_releases ohr
   WHERE     TRUNC (ooh.ordered_date) BETWEEN '10-NOV-15' AND '14-NOV-15'
         AND ooh.order_category_code = 'ORDER'
         AND ohld.header_id(+) = ooh.header_id
         AND ohs.hold_source_id(+) = ohld.hold_source_id
         AND ohd.hold_id(+) = ohs.hold_id
         AND ohr.hold_release_id(+) = ohs.hold_release_id
ORDER BY ooh.order_number