Monday, 16 May 2016

Query to display Sales Order Line Number in Oracle Apps



SELECT  TO_CHAR (l.line_number) 
        || DECODE (l.shipment_number, NULL, NULL, '.' || TO_CHAR (l.shipment_number))
        || DECODE (l.option_number, NULL, NULL, '.' || TO_CHAR (l.option_number))
        || DECODE (l.component_number, NULL, NULL, 
                   DECODE (l.option_number, NULL, '.', NULL)|| '.' ||TO_CHAR (l.component_number))
        || DECODE (l.service_number, NULL, NULL, 
                   DECODE (l.component_number, NULL, '.', NULL)
        || DECODE (l.option_number, NULL, '.', NULL) || '.'|| TO_CHAR (l.service_number))
          line_number,
       h.order_number
  FROM oe_order_headers_all h, oe_order_lines_all l
 WHERE     h.header_id = l.header_id
       AND l.open_flag = 'Y'
       AND h.header_id = :P_header_id
;

1 comment: