SQL Query and Information about Sales Order Line Number

Sales order line number field automatically defaults to 1.1 if this is the first line entered on the order. This field is for display purposes and cannot be updated.

Order Lines Numbers are displayed in the Sales Order window as a line quintuplet:

Line Number, Shipment Number, Option Number, Component Number, Service Number. For example, if order line number appears as 1.1.2.3.1:

• Line Number -1

• Shipment Number -1
• Option Number – 2
• Component Number -3
• Service Number-1

Note: You may choose to display additional fields within the Sales Order Header Main window by enabling the fields for display within a custom folder. For example, you can choose to display the Line number & shipment number fields.

Below is the query to find out complete sales order line number

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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)) "SO Line Num"
   FROM oe_order_headers_all h ,
  oe_order_lines_all l
  WHERE 1          = 1
AND h.order_number = '&Order_num'
AND h.header_id    = l.header_id;