In this post we will discuss on Sales order tables and query to get sales order OM details in Oracle Apps R12, this query will give you complete information on Sales order from Order to Cash.
Important table of Sales Order OM in Oracle Apps R12
OE_ORDER_HEADERS_ALL: This table records the sale order header details
OE_ORDER_LINES_ALL: This table records the sale order line details
HZ_PARTIES: This table stores basic information about customers
WSH_DELIVERY_ASSIGNMENTS: This table stores released_status Ready to release Pick Released
WSH_DELIVERY_DETAILS: This table stores Released_Status=I Need to run workflow background process.
MTL_DEMAND: This table stores demand and reservation information used in Available To Promise
MTL_RESERVATIONS: This table stores only soft reservations, No physical movement of stock Full Transaction
MTL_MATERIAL_TRANSACTIONS: This table stores the information about transaction related data which is done in inventory or cost update performed in Inventory.
WSH_NEW_DELIVERIES: This table stores If Auto-Create is Yes then data populated
Query to get Sales Order information from Order to Cash
select distinct ooha.order_number, ooha.cust_po_number, ooha.fob_point_code FOB, ooha.flow_status_code "Order Status", ooha.ordered_date, ooha.booked_date, oola.line_number, oola.flow_status_code "Line Status", ooha.org_id, hcasa.cust_acct_site_id, hp.party_name "Customer Name", hl.city "Customer City", hl.state "Customer State", hl.country "Customer Country", ft.nls_territory "Region", rs.name "Salesperson", nvl((select 'Y' from oe_order_holds where header_id =ooha.header_id and line_id = oola.line_id and rownum <=1),'N') "Hold Status", oola.actual_shipment_date "Actual Ship Date", rcta.trx_number "AR Invoice Number", rcta.trx_date "AR Invoice Date", organization_code "Inventory Org", oola.ordered_item "Item#/Part#", msib.description "Item Description", oola.source_type_code "Source Type", oola.schedule_ship_date, oola.pricing_quantity "Quantity", oola.pricing_quantity_uom "UOM" from oe_order_headers ooha, oe_order_lines oola, mtl_system_items_b msib, org_organization_definitions ood, ra_salesreps rs, hz_cust_site_uses hcsua , hz_cust_acct_sites hcasa , hz_party_sites hps , hz_locations hl , hz_parties hp, fnd_territories ft, ra_customer_trx rcta where ooha.header_id = oola.header_id and ooha.org_id =oola.org_id and oola.ordered_item = msib.segment1 and ooha.ship_from_org_id =msib.organization_id and ooha.ship_from_org_id =ood.organization_id and oola.salesrep_id = rs.salesrep_id AND ooha.ship_to_org_id = hcsua.site_use_id AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id AND hcasa.party_site_id = hps.party_site_id AND hps.location_id = hl.location_id AND hps.party_id = hp.party_id AND hl.country = ft.territory_code and nvl(rcta.interface_header_context,'ORDER ENTRY') = 'ORDER ENTRY' and to_char(ooha.order_number) = rcta.interface_header_attribute1(+) order by ooha.order_number, oola.line_number;