ONT Order Upload – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Overview
Upload to Create, Update, Book and Cancel Sales Orders in Oracle Order Management. Supports creating new orders, updating existing orders, adding new lines to existing orders, splitting lines, booking orders and cancelling orders.
To split a line, add a row for the order with the line to split in Split From Line and the new shipment’s quantity in Ordered Quantity. The original line is reduced by that quantity unless its own row sets its new quantity. As in the Split Line window, the new shipment copies all other values from the original line, only Request Date, Warehouse and Ship To can differ.
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Upload Mode, Operating Unit, Order Number, Customer PO, Customer Name, Account Number, Order Type, Line Type, Open only, Exclude Cancelled, Order Status, Order Action, Line Status, Shippable Flag, Creation Date From, Creation Date To, Line Creation Date From, Line Creation Date To, Order Category, Line Category, Item, Item Type, Schedule Ship Date From, Schedule Ship Date To, Ship From Warehouse, Request Date From, Request Date To, Cancelled Date From, Cancelled Date To, Debug
Oracle EBS Tables Used
oe_contacts_v, hz_cust_account_roles, hz_relationships, hz_org_contacts, hz_cust_site_uses_all, qp_secu_list_headers_v, jtf_rs_salesreps, jtf_rs_resource_extns_tl, ra_terms_vl, mtl_parameters, oe_ship_methods_v, oe_agreements_vl, ra_rules, oe_order_sources, po_requisition_headers_all, oe_order_headers_all, aso_quote_headers_all, cs_incidents_all_b, qp_list_headers_vl, oe_price_adjustments, pa_projects_all, pa_tasks, hr_all_organization_units_vl, oe_transaction_types_tl, mtl_system_items_vl, hz_cust_accounts, hz_parties, oe_order_lines_all
Report Categories
Related Reports
AR Customer Upload, AR Customers and Sites, ONT Orders and Lines 11i, ONT Orders and Lines, PPF_WP3_OM_DETAILS, ONT Orders, AR Transactions and Lines, GL Account Distribution Analysis
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | Example |
| Blitz Report™ XML Import | ONT_Order_Upload.xml |
| SQL Source | ONT Order Upload.sql |
| Full Report on Enginatics | www.enginatics.com/reports/ont-order-upload/ |
Case Study & Technical Analysis: ONT Order Upload
Executive Summary
This upload creates, updates, books and cancels sales orders in Oracle Order Management, and adds lines to existing orders or splits lines into shipments. Order entry and order management teams use it to load and maintain large volumes of orders in one spreadsheet. It runs in Excel through Blitz Report™, and the download shows the existing orders that the upload will change.
Business Challenge
- High-volume order entry: Orders keyed one by one in the Sales Orders window are slow, and errors in customer, item or pricing data are costly to correct after booking.
- Maintaining open orders: Changes to lines, quantities, dates and shipping details must be made in the right sequence and with the reason and comments that Oracle requires for a booked line.
- Splitting shipments: A line sometimes has to ship in parts, each with its own request date, warehouse or ship-to address.
- Controlled booking: Orders must be booked or cancelled in batch with a clear record of changes.
The Solution
- Downloads existing orders, one row per header or line, with customer, order type, price list, salesperson, shipping, tax and flexfield values.
- Each row can create, update, book or cancel an order, or add a line, using the Order Action column.
- Splits a line by entering a new row for the order with the line to split in Split From Line and the new shipment’s quantity in Ordered Quantity.
- Keeps the new shipment’s other values equal to the original line, so that only Request Date, Warehouse and Ship To can differ, as in the Split Line window.
Technical Architecture (High Level)
Primary Tables
OE_ORDER_HEADERS_ALLandOE_ORDER_LINES_ALL: the order headers and lines, with status, quantities, dates, pricing and flexfield columns.OE_TRANSACTION_TYPES_TL: the order type and line type names.OE_ORDER_SOURCES: the order source of each order.OE_PRICE_ADJUSTMENTS: the applied discount or surcharge of each line.HZ_CUST_ACCOUNTS,HZ_PARTIESandHZ_CUST_SITE_USES_ALL: the customer, contact and ship-to and bill-to site locations.OE_CONTACTS_V,HZ_CUST_ACCOUNT_ROLES,HZ_RELATIONSHIPSandHZ_ORG_CONTACTS: the customer contact and contact number.RA_TERMS_VL,OE_SHIP_METHODS_VandOE_AGREEMENTS_VL: payment terms, shipping methods and agreements.QP_SECU_LIST_HEADERS_VandQP_LIST_HEADERS_VL: the price lists and the discount modifiers.JTF_RS_SALESREPSandJTF_RS_RESOURCE_EXTNS_TL: the salesperson of the header and line.MTL_SYSTEM_ITEMS_VLandMTL_PARAMETERS: the item and the warehouse.PA_PROJECTS_ALL,PA_TASKS,PO_REQUISITION_HEADERS_ALL,ASO_QUOTE_HEADERS_ALLandCS_INCIDENTS_ALL_B: the project, task and source document references.HR_ALL_ORGANIZATION_UNITS_VL: the operating unit.
Logical Relationships
- Each order header is joined to its operating unit, its customer account and party, and its order type and source.
- Each header is outer-joined to its lines, and each line to its line type, its item in the warehouse, and its ship-from organization.
- Header and line flexfield and third-party attribute contexts are displayed by their contexts and segment values.
- The line discount is the applied discount or surcharge adjustment, with its modifier, operator and value.
- Source references are resolved by source document type.
- Who-columns are converted to user names and client times, and header and line identifiers are kept as keys.
Parameters & Filtering
- Upload Mode and Order Action: set whether the file creates, updates, books or cancels orders.
- Operating Unit, Order Number, Customer PO, Customer Name and Account Number: select the orders to download.
- Order Type, Line Type, Order Status and Line Status: restrict orders and lines by their type and status.
- Item, Item Type, Ship From Warehouse and Schedule Ship Date From and To: restrict lines by product and shipping.
- Request Date From and To, Creation Date From and To, Cancelled Date From and To: restrict orders and lines by date.
Performance & Optimization
- Every header and line join is an outer join on an identifier, so the cost is one keyed lookup per row for each reference.
- Discount, price list, salesperson and address values are scalar subqueries, and discount lookups stop at the first match.
- Because the lookups are evaluated per row, the header selection by operating unit and order number is the main lever on run time.
FAQ
Q: How do I split an order line into two shipments in this upload? A: Add a row for the order with the line to split in Split From Line, and enter the new shipment’s quantity in Ordered Quantity; the original line is reduced by that quantity unless its own row sets its new quantity.
Q: Which values can differ on a split shipment? A: Only Request Date, Warehouse and Ship To can differ; all other values are copied from the original line, as in the Split Line window.
Q: Why does a booked line need a change reason? A: Oracle requires a reason and comments to modify a booked line, so the upload carries Line Change Reason and Line Change Comments.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH