ONT Orders and Lines – 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 when its final accounting was created and its journal posted in the general ledger. 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.

When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CGST Rate, SGST Rate, IGST Rate, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code, HSN Length Warning.

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, Actual Shipment Date From, Actual Shipment Date To

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, xxen_report_org_access, 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, PPF_WP3_OM_DETAILS, ONT Order Upload, GL Account Analysis, GL Account Distribution Analysis, AR Customer Upload, CAC Internal Order Shipment Margin, INV Item Upload, INV Item Upload 11i

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 ONT Orders and Lines - DIFOT Template 11-Oct-2022 033132.xlsm
Blitz Report™ XML Import ONT_Orders_and_Lines.xml
SQL Source ONT Orders and Lines.sql
Full Report on Enginatics www.enginatics.com/reports/ont-orders-and-lines/

Case Study: End-to-End Order Fulfillment Visibility in Oracle Order Management

Executive Summary

The ONT Orders and Lines report is a comprehensive operational dashboard for the Order-to-Cash (O2C) cycle. It breaks down silos between sales, shipping, and finance by providing a single, flattened view of a sales order—from the initial booking of the header to the status of individual lines, shipping deliveries, and final invoicing.

Business Challenge

Managing the order lifecycle in Oracle EBS often requires navigating multiple screens (Order Organizer, Shipping Transactions, Invoice Status). This fragmentation leads to:

The Solution

This report consolidates the entire lifecycle of a sales order line into a single row of data. It is designed to be the “one version of the truth” for Sales Operations, Logistics, and Accounts Receivable.

Key Features

Technical Architecture

The report queries the core Order Management tables and joins them to Shipping (WSH) and Receivables (RA) to build the complete picture.

Critical Tables

Key Parameters

Functional Analysis

Use Cases

  1. Backlog Analysis: Sales managers run the report filtering for “Open” lines to understand the value of the current order book.
  2. Revenue Assurance: Finance users filter for lines where Flow Status = CLOSED but Invoice Number is NULL to find unbilled shipments.
  3. Logistics Planning: Warehouse managers use the report to see upcoming Request Dates to plan labor and carrier capacity.

FAQ

Q: Does this report include cancelled lines? A: Yes, the report typically includes a parameter to Exclude Cancelled lines, but they can be included to analyze lost demand.

Q: Can I see the tracking number? A: Yes, if the Waybill/Tracking number is entered in the Shipping Transactions form, it will appear in the report output.

Q: How does it handle split lines? A: Split lines (e.g., Line 1.1, 1.2) are treated as individual rows, allowing for accurate tracking of partial shipments.

© Enginatics GmbH