ONT Transaction Types Listing – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Imported Oracle standard Order Management transaction types listing, enhanced to show the setup across all operating units Description: Transaction Types Listing Report Application: Order Management Source: Transaction Types Listing Report (XML) Short Name: OEXORDTP_XML DB package: ONT_OEXORDTP_XMLP_PKG

Report Parameters

Operating Unit, Transaction Type Code, Order Category Code

Oracle EBS Tables Used

hr_all_organization_units_vl, oe_transaction_types_all, oe_transaction_types_tl, oe_price_lists_v, oe_credit_check_rules, org_organization_definitions, org_freight, ra_rules, ra_batch_sources, ra_cust_trx_types, fnd_currencies_vl, gl_daily_conversion_types, gl_code_combinations_kfv, mo_glob_org_access_tmp, dual

Report Categories

BI Publisher, Enginatics

Related Reports

AR Customer Upload, ONT Transaction Types and Line WF Processes, GL Account Distribution Analysis, GL Account Analysis (Distributions), AR Transaction Upload, ONT Order Upload, AR Transactions and Lines 11i

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 Listing 11-Jul-2021 230055.xlsx
Blitz Report™ XML Import	ONT_Transaction_Types_Listing.xml
Full SQL on Enginatics	www.enginatics.com/reports/ont-transaction-types-listing/

Case Study & Technical Analysis: ONT Transaction Types Listing Report

Executive Summary

The ONT Transaction Types Listing report is a comprehensive configuration audit tool that provides a detailed list of all Order Management transaction types and their associated setup attributes. As an enhanced version of a standard Oracle report, its key advantage is the ability to display and compare these setups across all operating units in a single view. It is a foundational report for functional analysts and system administrators for documenting, auditing, and ensuring the consistency of the core objects that drive order processing.

Business Challenge

Consistent and correct transaction type setup is vital for smooth order processing. However, auditing this configuration in a multi-organization environment is a significant challenge.

• Siloed Configuration Views: The standard Oracle application forms and reports typically only show the setup for the current operating unit. This makes it extremely difficult to perform a global audit or to compare setups between two different business units.
• Hidden Details: Key attributes that have a major financial or logistical impact—such as the assigned Price List, Invoicing Rule, and Credit Check rules—are not visible in a single summary view, requiring users to navigate to multiple different setup screens.
• Risk of Inconsistency: Without an easy way to compare, transaction types that should be identical can slowly drift apart between operating units, leading to process inconsistencies and user confusion.
• Manual Documentation Effort: Documenting the full setup for dozens of transaction types across a global implementation is a massive manual task, prone to errors and quickly becoming outdated.

The Solution

This report provides a multi-organizational, detailed listing of transaction type setups, offering a powerful tool for configuration management.

• Global, Cross-Organization Auditing: The report’s primary function is to display transaction type setups for all or multiple operating units at once, making it simple to identify and rectify any inconsistencies.
• Consolidated Attribute View: It brings together all critical attributes for a transaction type—from pricing and invoicing rules to credit checks and freight terms—into a single, easy-to-read line, providing a complete picture of the configuration.
• Enforces Standardization: It is the perfect tool for ensuring that corporate standards for order processing are being followed consistently across all business units.
• Automated and Accurate Documentation: The report serves as a live, accurate snapshot of the transaction type configuration, which can be exported and used for project documentation, audit requests, or end-user training.

Technical Architecture (High Level)

The report queries the central Order Management setup tables and joins them with various related definition tables to provide a user-friendly output.

• Primary Tables Involved:
  • oe_transaction_types_all (the main table storing the attribute settings for each transaction type per operating unit).
  • oe_transaction_types_tl (for the name and description of the types).
  • oe_price_lists_v (to get the name of the assigned price list).
  • oe_credit_check_rules (to show the credit check rule setup).
  • ra_rules (to get the name of the accounting and invoicing rules).
• Logical Relationships: The report’s logic centers on the oe_transaction_types_all table. For each transaction type, it performs lookups to other setup tables to translate the internal IDs for entities like Price List or Invoicing Rule into their human-readable names, making the report far more intuitive than a raw data dump.

Parameters & Filtering

The parameters allow for both high-level and specific views of the configuration:

• Operating Unit: Allows the user to run the report for a single OU or, if left blank, for all OUs they have access to.
• Transaction Type Code: Filters the output for a specific transaction type to see its setup across different organizations.
• Order Category Code: Allows filtering for specific classes of transaction types, such as all ‘Sales Order’ types or all ‘Return’ types.

Performance & Optimization

The report is designed for rapid execution.

• Optimized for Setup Data: The report queries configuration tables that have a low volume of data, resulting in near-instantaneous execution.
• Direct SQL vs. XML: As an enhancement of a standard BI Publisher report, this Blitz Report version offers superior performance by querying the database directly and delivering the output to Excel, avoiding the overhead of the XML/XSLT processing used by its predecessor.

FAQ

1. When should I use this report versus the ‘Transaction Types and Line WF Processes’ report? Use this ‘Transaction Types Listing’ report when you need to audit and compare the attributes of transaction types, such as price lists, credit rules, and invoice sources, especially across multiple operating units. Use the ‘Transaction Types and Line WF Processes’ report when you need to understand the process flow, that is, the specific workflow engine and process steps that are assigned to an order or line type.

2. What does ‘Order Category’ signify? The Order Category is a high-level classification for a transaction type. The main categories are ‘SALES’ for sales orders, ‘RETURN’ for return material authorizations (RMAs), and ‘MIXED’ for orders that can contain both sales and return lines. This allows for easy filtering and grouping.

3. How can I see if a transaction type is restricted to a specific operating unit? The oe_transaction_types_all table contains the org_id column. This report will show a row for every operating unit (org_id) a transaction type is defined for. If a type is defined with a specific org_id, it is restricted to that OU. If it is defined without an org_id, it is typically available globally.

---

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