ONT Transaction Types and Line WF Processes – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Overview
Master data report showing the setup of order management transaction types and associated line types and workflow processes.
Report Parameters
Show assigned Line Flows, Operating Unit
Oracle EBS Tables Used
hr_all_organization_units_vl, oe_transaction_types_all, org_organization_definitions, qp_list_headers_tl, oe_credit_check_rules, ra_rules, ra_cust_trx_types_all, gl_code_combinations_kfv, oe_workflow_assignments, wf_activities_vl, oe_transaction_types_tl, xdo_templates_vl, okc_terms_templates_all
Report Categories
Related Reports
AR Customer Upload, ONT Transaction Types Listing, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Distribution Analysis, GL Account Analysis
Running This SQL Without Blitz Report
Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | ONT Transaction Types and Line WF Processes 24-Jul-2017 150548.xlsx |
| Blitz Report™ XML Import | ONT_Transaction_Types_and_Line_WF_Processes.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/ont-transaction-types-and-line-wf-processes/ |
Case Study & Technical Analysis: ONT Transaction Types and Line WF Processes Report
Executive Summary
The ONT Transaction Types and Line WF Processes report is a master data configuration report that provides a detailed, consolidated view of the Oracle Order Management transaction type setup. It displays order and line transaction types, and critically, the associated Oracle Workflow processes that govern their behavior. This report is an essential tool for functional consultants, business analysts, and IT support staff to audit, document, and troubleshoot the core processing logic of the entire order-to-cash cycle.
Business Challenge
The power of Oracle Order Management lies in its flexibility, which is primarily controlled by the Transaction Type setup. However, this flexibility brings complexity, and understanding the complete setup for a given order type is a significant challenge.
- Fragmented Configuration: The setup for a single transaction type is spread across multiple forms in Oracle EBS. There is no single screen where you can view the order type, its permissible line types, and the assigned header and line workflows all at once.
- Complex Troubleshooting: When an order is stuck at a certain status or a specific action (like “Schedule”) is not available, the root cause is almost always in the underlying workflow process. Tracing this logic from the order back through the transaction type setup is a difficult and time-consuming task for support analysts.
- Difficult Documentation: Manually documenting the complete setup for a new transaction type (e.g., for a new business process) requires taking numerous screenshots and compiling them, a process that is both tedious and hard to maintain.
- Risk of Inconsistency: Ensuring that a new or modified transaction type is configured identically between Test and Production environments is critical for a smooth deployment. Verifying this manually is prone to human error.
The Solution
This report provides a single, unified, and easy-to-understand view of the entire transaction type configuration, directly addressing the challenges of complexity and fragmentation.
- Consolidated View: It presents the order type, its associated line types, and the full name of the assigned header and line workflow processes in a single row, providing a complete picture that is not available anywhere in the standard application.
- Accelerated Problem Solving: By showing the exact workflow process name, the report gives technical and functional analysts the precise information they need to begin workflow diagnostics, drastically reducing troubleshooting time.
- Simplified Auditing and Documentation: The report serves as on-demand documentation of the system’s core business logic. It can be exported to Excel and archived for audit purposes or used as a blueprint for creating new transaction types.
- Ensured Consistency: By running the report in two different environments, users can easily compare the complete setup for all transaction types and instantly spot any inconsistencies before they cause production issues.
Technical Architecture (High Level)
The report queries the core setup tables in Order Management and Oracle Workflow to link transaction types to their underlying process logic.
- Primary Tables Involved:
oe_transaction_types_allandoe_transaction_types_tl(stores the definition and name of both order and line transaction types).oe_workflow_assignments(the critical mapping table that links a transaction type and/or line type to a specific workflow process).wf_activities_vl(the Oracle Workflow table that provides the user-friendly name of the assigned workflow process, e.g., ‘Line Flow - Generic’).
- Logical Relationships: The report selects all transaction types and then joins to the workflow assignment table to find the assigned processes for both the order header and the associated line types. A final join to the workflow activities table translates the internal workflow name into a human-readable format.
Parameters & Filtering
The report uses simple parameters for targeted analysis:
- Operating Unit: Allows the user to view transaction types that are specific to an operating unit, as well as those available globally.
- Show assigned Line Flows: A key parameter that likely acts as a switch to either show a summary of just the order types, or to expand the report to show all the detailed order type-to-line type assignments.
Performance & Optimization
As a report on setup data, it is designed to run very quickly.
- Small Data Volume: The configuration tables for transaction types hold a relatively small number of rows, meaning the query can be resolved very fast.
- Efficient Joins: The query uses indexed columns (like
transaction_type_id) to perform efficient joins between the Order Management and Workflow tables.
FAQ
1. What is the difference between an Order Type and a Line Type? An Order Type defines the overall characteristics and process for a sales order header (e.g., “Standard Order,” “Return Order”). A Line Type defines the characteristics and workflow for the individual lines within that order (e.g., “Standard Item Line,” “Return Item Line”). An order type is typically assigned one or more permissible line types.
2. What is a “workflow process” and why is it so important? An Oracle Workflow process is a graphical flowchart of business logic. For an order line, the workflow dictates the exact sequence of statuses it will go through (e.g., Entered -> Awaiting Shipping -> Shipped -> Fulfilled -> Closed) and what actions are allowed at each step. The workflow is the “engine” that drives an order through its lifecycle, making it a critical piece of the configuration.
3. Can I use this report to see if a transaction type is currently active?
Yes, the oe_transaction_types_all table contains start and end date fields. These can be included in the report to show whether a transaction type is currently active and usable for new orders.
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
© 2026 Enginatics