Sales Order Holds Query (Holds Applied and Released)

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

Share on Facebook Share on Twitter Share on Google Plus

About RAMESH

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.