Oracle Order to Cash Queries


Query to Join OM and requisition Interface table for Back 2 back Order

select l.line_id, l.flow_status_code , l.open_flag,pr.interface_source_code,pr.interface_source_line_id,pr.note_to_buyer,

pr.note_to_receiver

from

oe_order_lines_all l,

po_requisitions_interface_all pr

where l.line_id = pr.interface_source_line_id

and pr.interface_source_code='CTO'

Query to Join OM and Purchase Order tables for Back 2 Back Order

select ph.segment1,a. supply_source_line_id, a.supply_source_header_id

from

mtl_reservations a,

oe_order_lines_all l,

po_headers_all ph

where demand_source_line_id = &Enter_Order_lineID

and l.line_id = a.demand_source_line_id

and a.supply_source_header_id = ph.po_header_id

Query to Join OM and PO Requisition table for Back 2 Back Order

select ph.segment1,a. supply_source_line_id, a.supply_source_header_id

from

mtl_reservations a,

oe_order_lines_all l,

po_requisition_headers_all pqh

where demand_source_line_id = &Enter_Order_lineID

and l.line_id = a.demand_source_line_id

and a.supply_source_header_id = pqh.requisition_header_id

Query to Join OM , WSH and AR table

SELECT 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

Mapping Between AR and OM (Transaction Flex field)

(RAL) – RA_CUSTOMER_TRX_LINES_ALL

RAL.INTERFACE_LINE_ATTRIBUTE1 Order_Num

RAL.INTERFACE_LINE_ATTRIBUTE2 Order_Type

RAL.INTERFACE_LINE_ATTRIBUTE3 Delivery ID

RAL.INTERFACE_LINE_ATTRIBUTE4 WayBill

RAL.INTERFACE_LINE_ATTRIBUTE6 Line_ID

RAL.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading

RAL.INTERFACE_LINE_ATTRIBUTE10 WH_ID RAL.INTERFACE_LINE_ATTRIBUTE11 PA_ID

via Oracle Technologies.: Oracle Order to Cash Queries.

Advertisements

One thought on “Oracle Order to Cash Queries

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s