Order Hold Information

This query gives orders and their holds if any. IF there are no order level holds, then hold and release information NULL.

SELECT /*+ use_nl (ooh hld ohs ohd ohr) */
       ooh.order_number              "Order Number"
     , ooh.ordered_date              "Ordered Date"
     , ooh.flow_status_code          "Order Status"
     , ooh.credit_card_approval_code "Credit Card Approval Code"
     , 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 TO_DATE('10-sep-2008 04:40:00', 'dd-mon-yyyy hh24:mi:ss')
                            AND TO_DATE('10-sep-2008 10:40:00', 'dd-mon-yyyy hh24:mi:ss')
   AND ooh.order_category_code = 'ORDER'
   -- 
   -- Add some other conditions to filter desired orders
   --
   AND hld.header_id       (+) = ooh.header_id
   AND ohs.hold_source_id  (+) = hld.hold_source_id
   AND ohd.hold_id         (+) = ohs.hold_id 
   --
   -- Add hold name if your are looking for specific holds
   --
   --  and def.name  = 'Time Out for Credit Card Auth'
   AND ohr.hold_release_id (+)  = ohs.hold_release_id
 ORDER BY ooh.order_number