Search 800 + Posts

Dec 11, 2010

Query to display Sales Order Line Number

Below is Simple Query to display Sales Order Line Number in Oracle Order Management

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))||','||
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'

5 comments:

  1. Excellent! Thank you very much!!

    ReplyDelete
  2. nice!but why should option flag be 'Y'?

    ReplyDelete
  3. I set Open flag = Y to see the data for open lines only

    ReplyDelete
  4. If you just call this function, it does all the work for you: apps.oe_order_misc_pub.get_concat_line_number(line_id)

    ReplyDelete