Oracle Order to Cash Queries 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
No comments:
Post a Comment