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