ONT Orders and Lines 11i – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Order Management · all Order Management reports

Overview

Detail Sales Order or Quote header report with line item details including status, cost, project and shipping information.

Show Sales Credits adds one row per sales credit of each line, taken from the line, or from the order header for lines without sales credits of their own. Credited Amount is the line extended price times the credit percent and is shown once per line and credit, so it can be summed by salesperson; the other amount columns stay on one row per line.

Show Order to Cash Details adds the booked date and open holds of each line, and for the line’s latest invoice the due date, the amount still due, the date it was fully paid, the latest receipt applied, and the dates it was transferred to the general ledger (Accounted Date) and its journal posted (GL Posted Date, shown when the Receivables journal source imports journal references). Amount Due Remaining is shown on one row per invoice, so it can be summed. To follow lines until they are paid, leave Open only empty, so that closed orders with unpaid invoices are included.

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

Operating Unit, Order Number, Quote Number, Customer Name, Account Number, Type, Order Category, Line Category, Order Type, Line Type, Item Type, Open only, Exclude Cancelled, Order Status, Line Status, Exclude Line Status, Item, Category Set 1, Category Set 2, Category Set 3, Shippable Flag, Order Fully/Partially Shipped, Project, Task, Schedule Ship Date From, Schedule Ship Date To, Ship To Country, Bill To Country, Ship From Warehouse, Request Date From, Request Date To, Creation Date From, Creation Date To, Line Creation Date From, Line Creation Date To, Cancelled Date From, Cancelled Date To, Invoice GL Date From, Invoice GL Date To, Intrastat Eligible Only, DIFOT Eligible Only, DFF Display, Show Order to Cash Details, Sales Agreement Number, Show Sales Credits

Oracle EBS Tables Used

dual, 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, ra_terms_vl, ra_customer_trx_lines_all, mtl_parameters, wsh_carriers_v, oe_sets, hr_all_organization_units_vl, oe_order_lines_all, mo_glob_org_access_tmp, oe_transaction_types_tl, mtl_system_items_vl, hz_cust_accounts, hz_parties, jtf_rs_salesreps, jtf_rs_resource_extns_vl, pa_projects_all, pjm_seiban_numbers, pa_tasks, ra_customer_trx_all, ra_cust_trx_types_all, ra_cust_trx_line_gl_dist_all, oe_order_lines_history, oe_reasons, wsh_delivery_details, wsh_delivery_assignments, wsh_new_deliveries, hz_cust_site_uses_all, hz_cust_acct_sites_all, hz_party_sites, hz_locations, fnd_territories_vl

Report Categories

Enginatics

ONT Orders and Lines, PPF_WP3_OM_DETAILS, ONT Orders, ONT Order Upload

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_Orders_and_Lines_11i.xml
SQL Source ONT Orders and Lines 11i.sql
Full Report on Enginatics www.enginatics.com/reports/ont-orders-and-lines-11i/

Case Study & Technical Analysis: ONT Orders and Lines 11i

Executive Summary

The ONT Orders and Lines 11i report is a detailed listing of Oracle Order Management sales orders and quotes, with the header, each line, its status, cost, project and shipping information. It can also show the sales credits of each line and the order-to-cash position of each line, from booking through invoice payment. Sales operations, order managers and finance teams use it to track backlog, delivery performance and the invoicing and collection of orders. It runs in Excel through Blitz Report, where rows can be summed by salesperson, warehouse or item.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. OE_ORDER_HEADERS_ALL is joined to OE_ORDER_LINES_ALL on the header id, and the lines are restricted to the operating units in MO_GLOB_ORG_ACCESS_TMP.
  2. Each line is linked to its invoice through the interface line attribute 6, which holds the order line id, on RA_CUSTOMER_TRX_LINES_ALL, then to the transaction header and type.
  3. Each line is linked to its delivery through WSH_DELIVERY_DETAILS on the source line id, with the assignment and new delivery joined from there.
  4. Ship-to and bill-to are reached through the site uses, account sites, party sites and locations of each address.
  5. Cancellation rows from OE_ORDER_LINES_HISTORY are joined to each line, and price adjustments are summed per line in inline views.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I see the sales credit of each salesperson on an order? A: Set Show Sales Credits. Each sales credit has its own row, with the credited amount equal to the line extended price times the credit percent, so the amounts can be summed by salesperson.

Q: How do I track an order until its invoice is paid? A: Set Show Order to Cash Details and leave Open only empty. The report then includes closed orders with unpaid invoices, and shows the amount due, the payment date and the receipt applied.

© Enginatics GmbH