Order Hold

SELECT   ooha.header_id,
         order_number,
         hold_lines.line_id,
         oe_order_misc_pub.get_concat_line_number (hold_lines.line_id) so_line_number, 
      /* Uncomment below to get the concatenated line number if you are running from non apps user */

        /*hold_lines.line_number || '.' || hold_lines.shipment_number

         || DECODE (hold_lines.option_number,

                    NULL, NULL,

                    '.' || hold_lines.option_number)

         || DECODE (

               hold_lines.component_number,

               NULL,

               NULL,

                  DECODE (hold_lines.option_number, NULL, '.')

               || '.'

               || hold_lines.component_number

            )

         || DECODE (

               hold_lines.service_number,

               NULL,

               NULL,

                  DECODE (hold_lines.option_number, NULL, '.')

               || DECODE (hold_lines.component_number, NULL, '.')

               || '.'

               || hold_lines.service_number

            )

            so_line_number, */
         ohd.name "Hold Name",

         holds.released_flag,
         holds.creation_date "Hold Applied Date",
         ohr.creation_date "Hold Released date",
         ohr.release_reason_code,
         ohr.release_comment
  FROM   oe_order_lines_all hold_lines,
         oe_order_headers_all ooha,
         oe_order_holds_all holds,
         oe_hold_sources_all ohsa,
         oe_hold_releases ohr,
         oe_hold_definitions ohd
 WHERE       holds.line_id = hold_lines.line_id(+)
         AND holds.header_id = hold_lines.header_id(+)
         AND holds.hold_release_id = ohr.hold_release_id(+)
         AND holds.hold_source_id = ohsa.hold_source_id
         AND ohsa.hold_id = ohd.hold_id
         AND holds.header_id = ooha.header_id
         AND ooha.order_number = :P_Order_Number

ORDER BY NVL(hold_lines.line_id,0), ohsa.hold_source_id