Search 800 + Posts

Dec 15, 2010

Query to Connect tables from Oracle Order Management to Oracle receivables

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)) order_line_number,
h.order_number,l.line_id,l.ordered_quantity,l.shipped_quantity,l.invoiced_quantity,
wdd.delivery_detail_id,wnd.delivery_id,wdd.shipped_quantity,
a.org_id,
a.creation_date ,a.trx_number,
b.quantity_ordered , b.quantity_invoiced,
b.interface_line_attribute1,b.interface_line_attribute3,
b.interface_line_attribute6,interface_line_attribute12
from ra_customer_trx_all a,
ra_customer_trx_lines_all b,
oe_order_headers_all h
, oe_order_lines_all l
,wsh_delivery_details wdd

,wsh_delivery_assignments wda
,wsh_new_deliveries wnd
where a.customer_trx_id = b.customer_trx_id
and a.interface_header_context = 'ORDER ENTRY'
and b.interface_line_attribute1 = to_char(h.order_number)
and h.header_id = l.header_id
and to_char(l.line_id) = b.interface_line_attribute6
and l.line_id = wdd.source_line_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and to_char(wnd.delivery_id) = b.interface_line_attribute3

4 comments:

  1. THANK YOU VERY MUCH FOR THIS QUERY.
    YOU REALLY SOLVE A BIG PROPLEM FOR ME.

    I'M TRYING TO DEVELOPE AN INVOICE REPORT AND I NEEDED TO CONNECT ORDER MANAGEMENT MODULE WITH RECEIVABLES MODULE BUT I COULDN'T.

    THANK YOU VERY MUCH AGAIN

    ReplyDelete
  2. Good to hear that , query helped you to save time.

    ReplyDelete
  3. Hi eoraclesapps

    My requirement is, i have to develop an report(RDF) to display the sales order when i pass the order number has parameter alike Order management main form in EBS. could you please help me in querying. i really don't understand the flow of the data in tables.thanks in advance.

    ReplyDelete
    Replies
    1. I am not sure about your requirement , but you can try to pull the data from
      oe_order_headers_all
      oe_order_lines_all
      wsh_delivery_details etc to pull the Order and shipping related data

      Delete