Query to get the outstanding holds on an order (Open holds)

  SELECT org.name customer_name,

         ho.name hold_name,

         hs.hold_until_date,

         hs.hold_comment,

         h.order_number,

         h.ordered_date order_date,

         h.transactional_curr_code currency_code,

         NVL (l.ordered_quantity, 0) * NVL (l.unit_selling_price, 0) amount,

         oh.header_id,

         oh.line_id,

         oh.order_hold_id,

         l.item_identifier_type,

         l.inventory_item_id,

         l.ordered_item_id,

         l.ordered_item

    FROM oe_sold_to_orgs_v org,

         mtl_system_items_vl si,

         oe_order_holds_all oh,

         oe_order_lines_all l,

         oe_order_headers h,

         oe_hold_definitions ho,

         oe_hold_sources_all hs

   WHERE     oh.header_id = h.header_id

         AND h.sold_to_org_id = org.organization_id

         AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')

         AND h.open_flag = 'Y'

         AND oh.hold_source_id = hs.hold_source_id

         AND hs.hold_id = ho.hold_id

         AND h.header_id = l.header_id(+)

         AND l.open_flag = 'Y'

         AND l.line_id = NVL (oh.line_id, l.line_id)

         AND l.inventory_item_id = si.inventory_item_id

         AND oh.hold_release_id IS NULL

         AND NVL (si.organization_id, 0) = :c_master_org

         AND NVL (h.org_id, 0) = NVL (:p_org_id, 0)

         AND NVL (l.org_id, 0) = NVL (:p_org_id, 0)

         AND h.order_number = '123456789'

ORDER BY org.name,

         ho.name,

         h.order_number,

         si.description