SQL Query and Information about Sales Order Line Number

By | May 3, 2012

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;

 

WOW! Did you like this post? We'll send more interesting posts like SQL Query and Information about Sales Order Line Number to you!
Enter your Email Address:
  • Vishal P

    Hi shailendher,

    Please also see below function for line number printing. It comes from oracle.

    OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER

    Thank You
    Vishal P