> Continuous Process Improvement > Order to Cash Sales Order Processing > Oracle E Business Suite > Data Template

Data Template: Order to Cash - Sales Order Processing

Oracle E-Business Suite

Order to Cash - Sales Order Processing Attributes (20) Order to Cash - Sales Order Processing Activities (13) Extraction Guides (3)

Your Order to Cash - Sales Order Processing Data Template

This template provides a structured overview of the essential data you need to extract for effective Order to Cash - Sales Order Processing analysis. It outlines the crucial attributes to collect and the key activities to track within your Oracle E-Business Suite. Additionally, you will find practical guidance on how to extract this data, ensuring a smooth start to your process mining journey.

Recommended attributes to collect
Key activities to track for analysis
Guidance for data extraction

New to event logs? Learn how to create a process mining event log.

Download Template

Order to Cash - Sales Order Processing Attributes

These are the recommended data fields to include in your event log for comprehensive Order to Cash - Sales Order Processing analysis.

3 Required 7 Recommended 10 Optional

	Name	Description
	Activity Name ActivityName	The name of a specific business event or step that occurred within the sales order lifecycle.
Description This attribute records the name of each activity performed on a sales order, such as 'Order Booked', 'Goods Shipped', or 'Payment Received'. These activities represent the key milestones in the Order to Cash process. Analyzing the sequence and frequency of these activities is the core of process mining. It allows for the discovery of the actual process flows, including common paths, deviations, and bottlenecks. This data is used to generate the process map, which is the primary visualization for process analysis. Why it matters Defines the steps in the process map, which is essential for visualizing and analyzing the process flow. Where to get This is a conceptual field derived from various source system events, statuses, and transaction dates across modules like Order Management, Shipping Execution, and Accounts Receivable. Examples Sales Order CreatedGoods ShippedInvoice CreatedPayment Received
	Event Time EventTime	The timestamp indicating when a specific activity occurred.
Description Event Time, or timestamp, captures the precise date and time that an activity was executed. For example, it records when an order was created, when an invoice was sent, or when payment was received. This temporal data is fundamental to process mining. This attribute is used to order events chronologically for each case, which is necessary to reconstruct the process flow accurately. It is also the basis for all duration and performance calculations, such as cycle times between activities, overall case duration, and identifying delays or bottlenecks. Why it matters Provides the chronological sequence of events and is the foundation for all time-based performance analysis, including cycle time and bottleneck identification. Where to get Sourced from various date fields across Oracle EBS tables, such as CREATION_DATE in OE_ORDER_HEADERS_ALL, ACTUAL_SHIPMENT_DATE in WSH_DELIVERY_DETAILS, or TRX_DATE in RA_CUSTOMER_TRX_ALL. Examples 2023-04-15T10:30:00Z2023-04-18T14:00:00Z2023-05-01T09:15:00Z
	Sales Order SalesOrder	The unique identifier for a customer's sales order, serving as the primary case for the Order to Cash process.
Description The Sales Order number uniquely identifies each customer order throughout its lifecycle, from creation to final closure. It acts as the central thread connecting all related activities, such as booking, shipping, invoicing, and payment. In process mining, this attribute is essential for reconstructing the end-to-end journey of every order. By grouping all events under a single Sales Order, analysts can visualize the complete process flow, identify variations between orders, and measure key performance indicators like cycle time and on-time delivery for individual cases. Why it matters This is the Case ID, which is fundamental for linking all process events together to analyze the end-to-end order lifecycle. Where to get The primary key for a sales order, typically found in Oracle Order Management tables like OE_ORDER_HEADERS_ALL.HEADER_ID. Examples 685127103482459
	Confirmed Delivery Date ConfirmedDeliveryDate	The committed date by which the goods are scheduled to be delivered to the customer.
Description This attribute stores the delivery date that was promised or confirmed to the customer. It serves as the target or baseline for measuring on-time delivery performance. In process mining, this date is compared against the actual delivery timestamp (from the 'Goods Delivered' activity) to calculate the 'On-Time Delivery Rate' KPI. Analyzing deviations from this date helps identify systemic issues in logistics, inventory management, or production planning that lead to delivery delays. Why it matters This is the baseline for measuring on-time delivery performance, a critical KPI for customer satisfaction and operational excellence. Where to get This date is typically found in the LATEST_ACCEPTABLE_DATE or REQUEST_DATE fields on the order line level in the OE_ORDER_LINES_ALL table. Examples 2023-05-102023-06-012023-07-20
	Customer Name CustomerName	The name of the customer who placed the sales order.
Description This attribute identifies the legal name of the customer associated with the sales order. It is a primary dimension for segmenting and analyzing process performance. By filtering or breaking down the process by customer, analysts can identify which customers experience the longest cycle times, have the highest rework rates, or are most often associated with payment delays. This insight is invaluable for improving customer relationships, tailoring service levels, and understanding customer behavior. Why it matters Allows for customer-centric analysis to identify performance variations, improve service, and understand payment behaviors across different customers. Where to get Derived by joining the SOLD_TO_ORG_ID from OE_ORDER_HEADERS_ALL with the HZ_CUST_ACCOUNTS and HZ_PARTIES tables to retrieve the party name. Examples Global Tech Inc.Innovate Solutions LLCPioneer Corp
	Order Status OrderStatus	The current or historical status of the sales order or order line.
Description This attribute captures the status of a sales order at different points in time, such as 'Entered', 'Booked', 'Closed', or 'Cancelled'. Statuses often correspond directly to process activities. Tracking order status is key to building dashboards that show the current state of all active orders. It allows managers to monitor the order pipeline, identify stalled orders, and proactively manage exceptions. Analyzing status transitions is a common way to define activities for the process map. Why it matters Provides visibility into the sales order pipeline, helping to identify stalled orders and manage process exceptions. Where to get Typically found in the FLOW_STATUS_CODE column in the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL tables. Examples BOOKEDAWAITING_SHIPPINGSHIPPEDCLOSEDCANCELLED
	Payment Due Date PaymentDueDate	The calculated date by which the invoice payment is due from the customer.
Description The Payment Due Date is the specific calendar date on which an invoice must be paid, calculated based on the invoice date and the agreed payment terms. It is the target date for the 'Payment Received' activity. This attribute is essential for calculating the 'Payment Term Compliance Rate' KPI by comparing it to the actual payment date. Analyzing deviations helps the collections team prioritize their efforts, identify chronically late-paying customers, and measure the effectiveness of the dunning process. Why it matters Serves as the target date for payment collection, enabling the measurement of payment timeliness and customer compliance with terms. Where to get Located in the DUE_DATE field in the AR_PAYMENT_SCHEDULES_ALL table, which is linked to the invoice transaction. Examples 2023-06-152023-07-012023-08-30
	Payment Terms PaymentTerms	The agreed-upon terms that define when the customer is expected to pay for the goods or services.
Description Payment Terms specify the conditions of payment for an invoice, such as 'Net 30', 'Net 60', or 'Due on Receipt'. This attribute is fundamental to managing accounts receivable and cash flow. Analyzing process performance by payment terms helps identify whether customers with certain terms are more likely to pay late. This information is used to assess financial risk, optimize collection strategies, and evaluate the effectiveness of different payment term offerings. It is a key dimension for the 'Payment Terms Compliance Monitoring' dashboard. Why it matters Crucial for analyzing payment behavior, monitoring cash flow, and assessing the financial risk associated with different customer agreements. Where to get Found in the RA_TERMS table, linked via TERM_ID in tables like RA_CUSTOMER_TRX_ALL (for invoices) or OE_ORDER_HEADERS_ALL (for orders). Examples Net 30Net 60Due on receipt
	Total Order Amount TotalOrderAmount	The total monetary value of the sales order.
Description This attribute represents the total value of all lines on a sales order, expressed in the transaction currency. It is a key financial metric associated with each case. Analyzing process metrics by order amount helps to prioritize improvement efforts. For instance, analysts can investigate if high-value orders experience more delays or rework than low-value orders. It also allows for financial impact assessment, such as quantifying the value of orders stuck in a particular bottleneck. Why it matters Enables financial analysis of the process, helping to prioritize high-value orders and quantify the monetary impact of inefficiencies. Where to get This value is typically calculated by summing the line amounts for a given order. Line amounts are found in tables like OE_ORDER_LINES_ALL. Examples 5450.00125000.75980.50
	User Name UserName	The user who performed the activity.
Description Identifies the specific user responsible for executing a given process step. This could be the sales representative who created the order, the credit analyst who performed the check, or the clerk who created the invoice. Analyzing activities by user helps identify training needs, high-performing individuals or teams, and workload distribution. It is also critical for compliance and audit trail analysis, such as investigating unauthorized actions or understanding rework patterns associated with specific users. Why it matters Enables analysis of user performance, workload distribution, and adherence to compliance protocols. It helps answer 'who' performed an action. Where to get Sourced from user-related fields like CREATED_BY or LAST_UPDATED_BY in various Oracle EBS tables. This often requires joining with FND_USER to get the full user name. Examples JSMITHRWILLIAMSCDAVIS
	Cancellation Reason CancellationReason	The documented reason for the cancellation of a sales order or order line.
Description When a sales order is cancelled, this attribute captures the reason provided for the cancellation. Example reasons might include 'Customer Request', 'Out of Stock', or 'Credit Hold'. This data is vital for root cause analysis of order cancellations. The 'Sales Order Cancellation Rate & Reasons' dashboard uses this attribute to identify the primary drivers of cancellations, allowing the business to implement targeted strategies to reduce churn, improve inventory forecasting, or refine credit policies. Why it matters Provides direct insight into why orders are cancelled, enabling root cause analysis to reduce lost sales and improve customer retention. Where to get This information is often stored in a reason code field, such as CANCELLED_REASON, which may be available in the OE_ORDER_LINES_ALL table or a related table for order changes. Examples Item discontinuedCustomer cancelledDuplicate order
	Currency Currency	The currency code for the sales order's monetary values.
Description The currency attribute specifies the currency in which the order amounts are denominated, such as USD, EUR, or JPY. It provides the necessary context for interpreting any financial data related to the order. This is essential for analyses in multi-national organizations that transact in various currencies. It ensures that financial metrics like 'Total Order Amount' are correctly understood and allows for proper currency conversion if required for aggregate reporting. Why it matters Provides essential context for all monetary values, ensuring accurate financial analysis, especially in a global business environment. Where to get Located in the TRANSACTIONAL_CURR_CODE field in the OE_ORDER_HEADERS_ALL table. Examples USDEURGBP
	Is On Time Delivery IsOnTimeDelivery	A flag indicating if the order was delivered on or before the confirmed delivery date.
Description This calculated attribute is a boolean flag (True/False) that indicates whether an order met its delivery commitment. It is derived by comparing the timestamp of the 'Goods Delivered' activity with the 'Confirmed Delivery Date'. This attribute directly supports the 'On-Time Delivery Rate' KPI. It simplifies analysis and dashboard creation by providing a clear, binary outcome for each order. This allows for easy filtering and aggregation to identify the characteristics of late deliveries, such as common products, customers, or shipping methods. Why it matters Directly measures customer service levels and fulfillment reliability, simplifying the calculation and visualization of the on-time delivery KPI. Where to get This is a calculated field. The logic is: IF ('Goods Delivered' EventTime <= ConfirmedDeliveryDate) THEN True ELSE False. Examples truefalse
	Is Payment On Time IsPaymentOnTime	A flag indicating if the payment was received on or before the invoice due date.
Description This is a calculated boolean attribute derived by comparing the timestamp of the 'Payment Received' activity against the 'Payment Due Date' for the corresponding invoice. It provides a simple true/false indicator of payment compliance. This flag is the basis for the 'Payment Term Compliance Rate' KPI. It simplifies the creation of dashboards and reports that monitor customer payment behavior and the effectiveness of the accounts receivable process. It allows for quick segmentation of on-time versus late payments to analyze contributing factors like customer type or payment terms. Why it matters Directly measures adherence to payment terms, which is critical for managing cash flow and assessing customer financial reliability. Where to get This is a calculated field. The logic is: IF ('Payment Received' EventTime <= PaymentDueDate) THEN True ELSE False. Examples truefalse
	Is Rework IsRework	A flag that identifies if a sales order has undergone rework, such as repeated confirmation or update activities.
Description This is a calculated boolean attribute that flags cases containing patterns indicative of rework. Rework can be identified by detecting loops in the process map, such as an order being booked multiple times, or by specific change events logged in the system. This flag is used to calculate the 'Sales Order Rework Rate' KPI and power the 'Sales Order Rework Analysis' dashboard. It allows analysts to easily isolate and study orders that deviate from the standard process, helping to quantify the impact of rework on cycle times and costs, and to identify the root causes of these inefficient loops. Why it matters Helps quantify process inefficiency by flagging orders that required manual changes, enabling analysis of the causes and impacts of rework. Where to get Calculated during data transformation by identifying sequences of activities that represent a loop (e.g., 'Order Booked' occurring more than once for the same case). Examples truefalse
	Last Data Update LastDataUpdate	The timestamp of the most recent data refresh from the source system.
Description This attribute indicates the last time the data was extracted from Oracle E-Business Suite and loaded into the process mining tool. It reflects the freshness of the data being analyzed. This is crucial for users to understand the timeliness of the insights they are viewing. It helps them know whether they are looking at real-time information or a snapshot from a specific point in time, which is important for making operational decisions. Why it matters Informs users about the freshness of the data, which is critical for trusting the analysis and making timely decisions. Where to get This timestamp is generated and added during the data extraction, transformation, and loading (ETL) process. Examples 2023-10-27T02:00:00Z2023-10-28T02:00:00Z
	Order Quantity OrderQuantity	The quantity of the product ordered on a specific sales order line.
Description This attribute specifies the number of units of a particular product requested by the customer on a sales order line. It represents the volume of the transaction at a line-item level. Order quantity can be used as a dimension for analysis to determine if process behavior changes with order size. For example, very large or very small orders might follow different process paths or experience different types of delays. It also provides context for other metrics, like order value. Why it matters Provides context on the scale of an order, enabling analysis of how order volume impacts process efficiency and fulfillment paths. Where to get Found in the ORDERED_QUANTITY field in the OE_ORDER_LINES_ALL table. Examples 102501
	Product Number ProductNumber	The unique identifier for the product or item on the sales order line.
Description This attribute identifies the specific material, item, or service being sold. It allows for analysis to be performed at a more granular level than the sales order header. Analyzing the process by product helps uncover product-specific issues. For instance, certain products might have longer fulfillment times due to complex manufacturing or sourcing, while others might be associated with a higher rate of shipping errors or customer disputes. This enables targeted improvements in supply chain and product management. Why it matters Allows for product-level analysis to identify items that cause process delays, rework, or other inefficiencies. Where to get Derived from INVENTORY_ITEM_ID in the OE_ORDER_LINES_ALL table, which can be joined to MTL_SYSTEM_ITEMS_B to get the item number or description. Examples AS54888CM15001SV20100
	Shipping Method ShippingMethod	The method or carrier used to transport the goods to the customer.
Description This attribute specifies the transportation mode or service level used for shipping, such as 'Ground Freight', 'Air Express', or 'Local Courier'. It is a key factor influencing both delivery time and cost. By analyzing the process using this attribute, businesses can evaluate the performance of different shipping methods. For example, the 'Shipping Method Performance' dashboard compares the cycle time from 'Goods Shipped' to 'Goods Delivered' for each method, helping to optimize logistics for a balance of speed, cost, and reliability. Why it matters Enables performance evaluation of different carriers and shipping options to optimize for cost, speed, and reliability. Where to get Generally stored as SHIPPING_METHOD_CODE in tables like WSH_DELIVERY_DETAILS or OE_ORDER_LINES_ALL. Examples UPS GroundFedEx Priority OvernightDHL Express Worldwide
	Source System SourceSystem	The system from which the data was extracted.
Description This attribute identifies the source information system where the event data originated. For this process, it will consistently be 'Oracle E-Business Suite'. In environments with multiple systems, this field is critical for data lineage and troubleshooting. Even in a single-system context, it provides important context for the data model and helps in standardizing data ingestion processes. Why it matters Provides essential context about data origin, ensuring traceability and proper interpretation of the data, especially in multi-system landscapes. Where to get This is typically a static value added during the data extraction, transformation, and loading (ETL) process to label the origin of the data. Examples Oracle E-Business SuiteOracle EBS R12

Required Recommended Optional

Order to Cash - Sales Order Processing Activities

These are the key process steps and milestones to capture in your event log for accurate process discovery and optimization.

7 Recommended 6 Optional

	Activity	Description
	Goods Shipped	Represents the completion of the ship confirmation process, where the goods physically leave the warehouse. This is a key explicit event in the shipping module that updates inventory and progresses the order status.
Why it matters This is a critical fulfillment milestone used to measure on-time shipping performance. It also serves as the trigger for invoicing and revenue recognition processes. Where to get Captured as an explicit transaction in Oracle Shipping Execution. The timestamp can be found in `WSH_NEW_DELIVERIES` table's `INITIAL_PICKUP_DATE` or derived from status updates in `WSH_DELIVERY_DETAILS` to 'Shipped'. Capture Use the ship confirmation date from WSH_DELIVERY_DETAILS or WSH_NEW_DELIVERIES. Event type explicit
	Inventory Allocated	This activity represents the reservation of inventory for the order lines, ensuring the required quantity is available for picking. This is typically inferred from a status change on the sales order line, indicating it is ready for release to the warehouse.
Why it matters This milestone is crucial for understanding fulfillment readiness. Delays here can point to inventory shortages, sourcing problems, or allocation process inefficiencies. Where to get Inferred from status changes in the `WSH_DELIVERY_DETAILS` table. The activity occurs when a line's status is updated to 'Ready to Release', with the timestamp derived from the associated status update. Capture Inferred from line status updates in WSH_DELIVERY_DETAILS to 'Ready to Release'. Event type inferred
	Invoice Created	This event marks the creation of the accounts receivable invoice for the shipped goods. It is an explicit event triggered by the AutoInvoice process, which pulls data from Order Management and Shipping into the Receivables module.
Why it matters This activity initiates the financial settlement part of the process. It is the starting point for measuring the invoice-to-payment cycle time and monitoring billing efficiency. Where to get This is an explicit transaction recorded in the `RA_CUSTOMER_TRX_ALL` table in Oracle Receivables. The `TRX_DATE` or `CREATION_DATE` serves as the event timestamp. Capture Use TRX_DATE from the RA_CUSTOMER_TRX_ALL table. Event type explicit
	Order Booked	Represents the formal confirmation of the sales order, making it active and eligible for subsequent processing like sourcing and shipping. This is an explicit action in Oracle EBS that changes the order's status from 'Entered' to 'Booked'.
Why it matters Booking is a critical milestone that officially commits the order for fulfillment. Delays between creation and booking can indicate issues with data entry, approvals, or initial validation. Where to get Captured from the `OE_ORDER_HEADERS_ALL` table. The event occurs when the `BOOKED_FLAG` is set to 'Y', and the timestamp is recorded in the `BOOKED_DATE` column. Capture Use BOOKED_DATE from the OE_ORDER_HEADERS_ALL table. Event type explicit
	Order Closed	This activity marks the final completion of the sales order after all its lines have been successfully shipped, invoiced, and closed. This is an explicit status update on the order header.
Why it matters This is the primary success endpoint for the Order to Cash process. It provides the final timestamp needed to calculate the end-to-end cycle time for successfully fulfilled orders. Where to get This event is recorded in the `OE_ORDER_HEADERS_ALL` table when the `FLOW_STATUS_CODE` is updated to 'CLOSED'. The `LAST_UPDATE_DATE` for this status change is the event timestamp. Capture Timestamp of update to OE_ORDER_HEADERS_ALL where FLOW_STATUS_CODE becomes 'CLOSED'. Event type explicit
	Payment Received	This activity occurs when a customer's payment is received and applied to the corresponding invoice in the system. It is an explicit financial transaction recorded in the Accounts Receivable module.
Why it matters This milestone is critical for tracking cash flow, Days Sales Outstanding (DSO), and payment term compliance. It is a key endpoint for measuring the financial cycle time. Where to get Recorded explicitly in the `AR_RECEIVABLE_APPLICATIONS_ALL` table. The `APPLY_DATE` column provides the timestamp for when the cash receipt was applied to the invoice. Capture Use APPLY_DATE from AR_RECEIVABLE_APPLICATIONS_ALL for the specific invoice. Event type explicit
	Sales Order Created	This activity marks the initial creation of a sales order in the system. It is an explicit event captured when a user saves a new sales order header, representing the formal start of the order-to-cash process.
Why it matters This is the primary start event for the process. Analyzing the time from this point to subsequent activities is essential for measuring the overall Order to Cash cycle time. Where to get This event is captured from the `OE_ORDER_HEADERS_ALL` table in the Oracle Order Management module. The `CREATION_DATE` column provides the explicit timestamp for this activity. Capture Use CREATION_DATE from the OE_ORDER_HEADERS_ALL table. Event type explicit
	Credit Check Performed	This activity signifies the completion of the customer credit check process for the order. It is often captured when a credit check hold, if applied, is released from the sales order, allowing it to proceed.
Why it matters Delays in credit checking are a common bottleneck that can stall the entire fulfillment process. Monitoring this activity helps identify inefficiencies in financial controls and approvals. Where to get This event can be inferred from the `OE_ORDER_HOLDS_ALL` table by identifying the timestamp when a 'Credit Check Hold' is released for a specific order header. Capture Use the release timestamp for credit-related holds from OE_ORDER_HOLDS_ALL. Event type inferred
	Goods Delivered	This activity signifies that the shipment has reached the customer. Standard Oracle EBS does not track this event, so it typically needs to be inferred or imported from external carrier systems.
Why it matters Essential for measuring on-time delivery KPIs and understanding the complete customer experience. The gap between shipping and delivery highlights carrier performance. Where to get Requires system analysis. This data is not natively available in Oracle EBS and must be obtained from external carrier data feeds or logistics platforms integrated with the system. Capture Inferred from external carrier data or assumed based on a standard transit time after 'Goods Shipped'. Event type inferred
	Invoice Sent to Customer	This activity represents the point when the invoice is transmitted to the customer, either by printing or electronic means. This is typically inferred as it is not always an explicit, separate event from creation.
Why it matters Marks the official start of the customer's payment term clock. Delays between invoice creation and sending can negatively impact cash flow and lead to late payments. Where to get This can be inferred from the `LAST_PRINTED_DATE` on the `RA_CUSTOMER_TRX_ALL` table. For electronic invoices, it may require looking at logs from an external document delivery system. Capture Use the LAST_PRINTED_DATE from RA_CUSTOMER_TRX_ALL or logs from third-party tools. Event type inferred
	Order Cancelled	Represents the cancellation of an entire sales order before fulfillment is completed. This is an explicit event that terminates the order processing workflow.
Why it matters This is a key exception endpoint. Analyzing the frequency, timing, and reasons for cancellations is crucial for identifying revenue loss and process or product issues. Where to get Recorded in the `OE_ORDER_HEADERS_ALL` table when the `FLOW_STATUS_CODE` is set to 'CANCELLED' and the `CANCELLED_FLAG` is 'Y'. The `LAST_UPDATE_DATE` can be used as the timestamp. Capture Timestamp when CANCELLED_FLAG on OE_ORDER_HEADERS_ALL is set to 'Y'. Event type explicit
	Order Line Closed	Indicates that all processing for an individual line on a sales order is complete, including shipping and invoicing. This is an explicit status change managed by the workflow process.
Why it matters Tracking line-level closures helps in analyzing partial shipments and identifying issues with specific products or fulfillment paths before the entire order is complete. Where to get This is recorded in the `OE_ORDER_LINES_ALL` table when the `FLOW_STATUS_CODE` is updated to 'CLOSED'. The `LAST_UPDATE_DATE` for this status change can serve as the timestamp. Capture Timestamp of update to OE_ORDER_LINES_ALL where FLOW_STATUS_CODE becomes 'CLOSED'. Event type explicit
	Pick Released	This event marks the point where the sales order lines are released to the warehouse for picking activities to begin. It's an explicit action that creates pick slips and makes the order visible to warehouse operators.
Why it matters This activity initiates the physical fulfillment process. Analyzing the time from this point to 'Goods Shipped' reveals warehouse floor efficiency and potential picking bottlenecks. Where to get This is an explicit event captured in the Oracle Shipping Execution module. It can be identified by the status of delivery details in `WSH_DELIVERY_DETAILS` changing to 'Released to Warehouse' or 'Transactable'. Capture Timestamp when WSH_DELIVERY_DETAILS.RELEASED_STATUS changes to 'S' (Submitted). Event type explicit

Recommended Optional

Extraction Guides

How to get your data from Oracle E-Business Suite

Steps

Log into Oracle EBS: Navigate to your designated Oracle BI Publisher responsibility, such as 'BI Publisher Administrator' or 'XML Publisher Administrator'.
Create a Data Model: From the BI Publisher home page, select 'Create' and then choose 'Data Model'. This opens the data model editor.
Define a SQL Query Dataset: Inside the data model editor, click the '+' icon to add a new dataset and select 'SQL Query'.
Configure the Dataset: Name your dataset (e.g., 'O2C_EVENT_LOG'). Set the 'Data Source' to your EBS application database (e.g., 'EBS_DB'). Copy the complete SQL query provided in the 'query' section of this document and paste it into the SQL Query text area.
Define Parameters: BI Publisher will automatically detect the :p_start_date and :p_end_date placeholders in the query. You should configure these as 'Date' type parameters and make them mandatory for users running the report.
Save the Data Model: Save the completed data model in a shared BI Publisher catalog folder, for example, under 'Shared Folders/Custom/Process Mining'.
Create a New Report: Navigate back to the BI Publisher home page, select 'Create', and then choose 'Report'. Select the option 'Use Existing Data Model' and find the data model you just saved.
Configure the Report Layout: A wizard will guide you through creating a layout. Choose a simple 'Table' layout. Drag and drop all columns from your data model into the table. You do not need complex formatting, as the goal is raw data export.
Save the Report: Save the report in the same catalog folder as the data model.
Run and Export the Report: Run the report, providing the desired start and end dates. Once the report view loads, find the 'Export' option. Select 'CSV' as the export format. The downloaded file is your event log.
Schedule the Report (Optional): For recurring extractions, use the 'Schedule' option for the report. Configure the schedule, parameters, and set the output format to CSV. You can configure delivery to an email address or a server location.

Configuration

Prerequisites: The user running the report needs a BI Publisher responsibility and must have database-level read access to all tables in the query, which span Order Management (OE), Shipping (WSH), Receivables (AR), and Foundation (FND, HZ) modules.
Data Model Parameters: The query includes two mandatory parameters, :p_start_date and :p_end_date. These are critical for limiting the data volume and ensuring the report completes in a reasonable time. They should be configured as 'Date' type parameters in the data model.
Date Range Filters: For your initial analysis, extract a limited historical period, such as 3 to 6 months. A full historical load should be planned carefully and executed in smaller batches to avoid system performance degradation.
Performance Considerations: This is a comprehensive query that can be resource-intensive on the database. Schedule large extractions to run during off-peak business hours. Ensure that standard Oracle indexes on date and primary key columns are in place.
Data Scope Filters: To further limit the data, consider adding WHERE clauses to the main base_data CTE in the query. Common filters include OOHA.ORG_ID for a specific Operating Unit or OOHA.ORDER_TYPE_ID for certain sales order types.

a Sample Query sql

WITH base_data AS (
  SELECT
    OOHA.HEADER_ID,
    OOHA.ORDER_NUMBER,
    OOHA.ORG_ID,
    HP.PARTY_NAME AS CUSTOMER_NAME,
    OOHA.CREATION_DATE AS ORDER_CREATION_DATE,
    OOHA.CREATED_BY AS ORDER_CREATED_BY,
    OOHA.BOOKED_DATE,
    OOHA.BOOKED_FLAG,
    OOHA.FLOW_STATUS_CODE AS HEADER_STATUS,
    OOHA.LAST_UPDATE_DATE AS HEADER_LAST_UPDATE_DATE,
    OOHA.LAST_UPDATED_BY AS HEADER_LAST_UPDATED_BY,
    OOHA.TRANSACTIONAL_CURR_CODE,
    RT.NAME AS PAYMENT_TERMS,
    (
      SELECT
        SUM(OOLA.ORDERED_QUANTITY * OOLA.UNIT_SELLING_PRICE)
      FROM
        OE_ORDER_LINES_ALL OOLA
      WHERE
        OOLA.HEADER_ID = OOHA.HEADER_ID
    ) AS TOTAL_ORDER_AMOUNT,
    OOHA.SOLD_TO_ORG_ID
  FROM
    OE_ORDER_HEADERS_ALL OOHA
    JOIN HZ_CUST_ACCOUNTS HCA ON OOHA.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
    JOIN HZ_PARTIES HP ON HCA.PARTY_ID = HP.PARTY_ID
    LEFT JOIN RA_TERMS_B RT ON OOHA.PAYMENT_TERM_ID = RT.TERM_ID
  WHERE
    OOHA.CREATION_DATE BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD') AND TO_DATE(:p_end_date, 'YYYY-MM-DD HH24:MI:SS')
)
-- 1. Sales Order Created
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Sales Order Created' AS "ActivityName",
  BD.ORDER_CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.ORDER_CREATED_BY = FU.USER_ID
UNION ALL
-- 2. Order Booked
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Booked' AS "ActivityName",
  BD.BOOKED_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'BOOKED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.BOOKED_DATE IS NOT NULL
UNION ALL
-- 3. Credit Check Performed (using Booked Date as a proxy)
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Credit Check Performed' AS "ActivityName",
  BD.BOOKED_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'BOOKED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.BOOKED_DATE IS NOT NULL
UNION ALL
-- 4. Inventory Allocated & 5. Pick Released (using WSH_DELIVERY_DETAILS creation)
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  CASE
    WHEN WDD.RELEASED_STATUS = 'Y' THEN 'Pick Released'
    ELSE 'Inventory Allocated'
  END AS "ActivityName",
  WDD.CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  JOIN WSH_DELIVERY_DETAILS WDD ON OOLA.LINE_ID = WDD.SOURCE_LINE_ID
  LEFT JOIN FND_USER FU ON WDD.CREATED_BY = FU.USER_ID
WHERE
  WDD.RELEASED_STATUS IN ('S', 'Y', 'C')
UNION ALL
-- 6. Goods Shipped
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Goods Shipped' AS "ActivityName",
  WTS.ACTUAL_DEPARTURE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  JOIN WSH_DELIVERY_DETAILS WDD ON OOLA.LINE_ID = WDD.SOURCE_LINE_ID
  JOIN WSH_DELIVERY_ASSIGNMENTS WDA ON WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
  JOIN WSH_NEW_DELIVERIES WND ON WDA.DELIVERY_ID = WND.DELIVERY_ID
  JOIN WSH_DELIVERY_LEGS WDL ON WND.DELIVERY_ID = WDL.DELIVERY_ID
  JOIN WSH_TRIP_STOPS WTS ON WDL.PICK_UP_STOP_ID = WTS.STOP_ID
  LEFT JOIN FND_USER FU ON WTS.LAST_UPDATED_BY = FU.USER_ID
WHERE
  WTS.ACTUAL_DEPARTURE_DATE IS NOT NULL
UNION ALL
-- 7. Goods Delivered - This is a placeholder as standard EBS does not track this event.
-- Data must be sourced from a carrier integration or customization.
/*
SELECT 
    '[Your Sales Order]' AS "SalesOrder",
    'Goods Delivered' AS "ActivityName",
    TO_DATE('[Your Delivery Date]', 'YYYY-MM-DD') AS "EventTime", 
    ... 
FROM [Your Custom Table]
*/
-- 8. Invoice Created
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Invoice Created' AS "ActivityName",
  RCTA.CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'INVOICED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1 AND TO_CHAR(BD.HEADER_ID) = RCTLA.INTERFACE_LINE_ATTRIBUTE6
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
  LEFT JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  LEFT JOIN FND_USER FU ON RCTA.CREATED_BY = FU.USER_ID
UNION ALL
-- 9. Invoice Sent to Customer (using Invoice Creation date as a proxy)
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Invoice Sent to Customer' AS "ActivityName",
  RCTA.TRX_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'INVOICED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1 AND TO_CHAR(BD.HEADER_ID) = RCTLA.INTERFACE_LINE_ATTRIBUTE6
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
  LEFT JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  LEFT JOIN FND_USER FU ON RCTA.CREATED_BY = FU.USER_ID
UNION ALL
-- 10. Payment Received
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Payment Received' AS "ActivityName",
  ARAA.APPLY_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'PAID' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON BD.SOLD_TO_ORG_ID = RCTA.SOLD_TO_CUSTOMER_ID
  JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  JOIN AR_RECEIVABLE_APPLICATIONS_ALL ARAA ON APSA.PAYMENT_SCHEDULE_ID = ARAA.APPLIED_PAYMENT_SCHEDULE_ID
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID AND BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1
  LEFT JOIN FND_USER FU ON ARAA.CREATED_BY = FU.USER_ID
WHERE
  ARAA.STATUS = 'APP'
UNION ALL
-- 11. Order Line Closed
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Line Closed' AS "ActivityName",
  OOLA.LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  LEFT JOIN FND_USER FU ON OOLA.LAST_UPDATED_BY = FU.USER_ID
WHERE
  OOLA.FLOW_STATUS_CODE = 'CLOSED'
UNION ALL
-- 12. Order Closed
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Closed' AS "ActivityName",
  BD.HEADER_LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.HEADER_STATUS = 'CLOSED'
UNION ALL
-- 13. Order Cancelled
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Cancelled' AS "ActivityName",
  BD.HEADER_LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.HEADER_STATUS = 'CANCELLED'

Steps

Log into Oracle EBS: Navigate to your designated Oracle BI Publisher responsibility, such as 'BI Publisher Administrator' or 'XML Publisher Administrator'.
Create a Data Model: From the BI Publisher home page, select 'Create' and then choose 'Data Model'. This opens the data model editor.
Define a SQL Query Dataset: Inside the data model editor, click the '+' icon to add a new dataset and select 'SQL Query'.
Configure the Dataset: Name your dataset (e.g., 'O2C_EVENT_LOG'). Set the 'Data Source' to your EBS application database (e.g., 'EBS_DB'). Copy the complete SQL query provided in the 'query' section of this document and paste it into the SQL Query text area.
Define Parameters: BI Publisher will automatically detect the :p_start_date and :p_end_date placeholders in the query. You should configure these as 'Date' type parameters and make them mandatory for users running the report.
Save the Data Model: Save the completed data model in a shared BI Publisher catalog folder, for example, under 'Shared Folders/Custom/Process Mining'.
Create a New Report: Navigate back to the BI Publisher home page, select 'Create', and then choose 'Report'. Select the option 'Use Existing Data Model' and find the data model you just saved.
Configure the Report Layout: A wizard will guide you through creating a layout. Choose a simple 'Table' layout. Drag and drop all columns from your data model into the table. You do not need complex formatting, as the goal is raw data export.
Save the Report: Save the report in the same catalog folder as the data model.
Run and Export the Report: Run the report, providing the desired start and end dates. Once the report view loads, find the 'Export' option. Select 'CSV' as the export format. The downloaded file is your event log.
Schedule the Report (Optional): For recurring extractions, use the 'Schedule' option for the report. Configure the schedule, parameters, and set the output format to CSV. You can configure delivery to an email address or a server location.

Configuration

Prerequisites: The user running the report needs a BI Publisher responsibility and must have database-level read access to all tables in the query, which span Order Management (OE), Shipping (WSH), Receivables (AR), and Foundation (FND, HZ) modules.
Data Model Parameters: The query includes two mandatory parameters, :p_start_date and :p_end_date. These are critical for limiting the data volume and ensuring the report completes in a reasonable time. They should be configured as 'Date' type parameters in the data model.
Date Range Filters: For your initial analysis, extract a limited historical period, such as 3 to 6 months. A full historical load should be planned carefully and executed in smaller batches to avoid system performance degradation.
Performance Considerations: This is a comprehensive query that can be resource-intensive on the database. Schedule large extractions to run during off-peak business hours. Ensure that standard Oracle indexes on date and primary key columns are in place.
Data Scope Filters: To further limit the data, consider adding WHERE clauses to the main base_data CTE in the query. Common filters include OOHA.ORG_ID for a specific Operating Unit or OOHA.ORDER_TYPE_ID for certain sales order types.

a Sample Query sql

WITH base_data AS (
  SELECT
    OOHA.HEADER_ID,
    OOHA.ORDER_NUMBER,
    OOHA.ORG_ID,
    HP.PARTY_NAME AS CUSTOMER_NAME,
    OOHA.CREATION_DATE AS ORDER_CREATION_DATE,
    OOHA.CREATED_BY AS ORDER_CREATED_BY,
    OOHA.BOOKED_DATE,
    OOHA.BOOKED_FLAG,
    OOHA.FLOW_STATUS_CODE AS HEADER_STATUS,
    OOHA.LAST_UPDATE_DATE AS HEADER_LAST_UPDATE_DATE,
    OOHA.LAST_UPDATED_BY AS HEADER_LAST_UPDATED_BY,
    OOHA.TRANSACTIONAL_CURR_CODE,
    RT.NAME AS PAYMENT_TERMS,
    (
      SELECT
        SUM(OOLA.ORDERED_QUANTITY * OOLA.UNIT_SELLING_PRICE)
      FROM
        OE_ORDER_LINES_ALL OOLA
      WHERE
        OOLA.HEADER_ID = OOHA.HEADER_ID
    ) AS TOTAL_ORDER_AMOUNT,
    OOHA.SOLD_TO_ORG_ID
  FROM
    OE_ORDER_HEADERS_ALL OOHA
    JOIN HZ_CUST_ACCOUNTS HCA ON OOHA.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
    JOIN HZ_PARTIES HP ON HCA.PARTY_ID = HP.PARTY_ID
    LEFT JOIN RA_TERMS_B RT ON OOHA.PAYMENT_TERM_ID = RT.TERM_ID
  WHERE
    OOHA.CREATION_DATE BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD') AND TO_DATE(:p_end_date, 'YYYY-MM-DD HH24:MI:SS')
)
-- 1. Sales Order Created
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Sales Order Created' AS "ActivityName",
  BD.ORDER_CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.ORDER_CREATED_BY = FU.USER_ID
UNION ALL
-- 2. Order Booked
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Booked' AS "ActivityName",
  BD.BOOKED_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'BOOKED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.BOOKED_DATE IS NOT NULL
UNION ALL
-- 3. Credit Check Performed (using Booked Date as a proxy)
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Credit Check Performed' AS "ActivityName",
  BD.BOOKED_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'BOOKED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.BOOKED_DATE IS NOT NULL
UNION ALL
-- 4. Inventory Allocated & 5. Pick Released (using WSH_DELIVERY_DETAILS creation)
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  CASE
    WHEN WDD.RELEASED_STATUS = 'Y' THEN 'Pick Released'
    ELSE 'Inventory Allocated'
  END AS "ActivityName",
  WDD.CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  JOIN WSH_DELIVERY_DETAILS WDD ON OOLA.LINE_ID = WDD.SOURCE_LINE_ID
  LEFT JOIN FND_USER FU ON WDD.CREATED_BY = FU.USER_ID
WHERE
  WDD.RELEASED_STATUS IN ('S', 'Y', 'C')
UNION ALL
-- 6. Goods Shipped
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Goods Shipped' AS "ActivityName",
  WTS.ACTUAL_DEPARTURE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  JOIN WSH_DELIVERY_DETAILS WDD ON OOLA.LINE_ID = WDD.SOURCE_LINE_ID
  JOIN WSH_DELIVERY_ASSIGNMENTS WDA ON WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
  JOIN WSH_NEW_DELIVERIES WND ON WDA.DELIVERY_ID = WND.DELIVERY_ID
  JOIN WSH_DELIVERY_LEGS WDL ON WND.DELIVERY_ID = WDL.DELIVERY_ID
  JOIN WSH_TRIP_STOPS WTS ON WDL.PICK_UP_STOP_ID = WTS.STOP_ID
  LEFT JOIN FND_USER FU ON WTS.LAST_UPDATED_BY = FU.USER_ID
WHERE
  WTS.ACTUAL_DEPARTURE_DATE IS NOT NULL
UNION ALL
-- 7. Goods Delivered - This is a placeholder as standard EBS does not track this event.
-- Data must be sourced from a carrier integration or customization.
/*
SELECT 
    '[Your Sales Order]' AS "SalesOrder",
    'Goods Delivered' AS "ActivityName",
    TO_DATE('[Your Delivery Date]', 'YYYY-MM-DD') AS "EventTime", 
    ... 
FROM [Your Custom Table]
*/
-- 8. Invoice Created
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Invoice Created' AS "ActivityName",
  RCTA.CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'INVOICED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1 AND TO_CHAR(BD.HEADER_ID) = RCTLA.INTERFACE_LINE_ATTRIBUTE6
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
  LEFT JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  LEFT JOIN FND_USER FU ON RCTA.CREATED_BY = FU.USER_ID
UNION ALL
-- 9. Invoice Sent to Customer (using Invoice Creation date as a proxy)
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Invoice Sent to Customer' AS "ActivityName",
  RCTA.TRX_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'INVOICED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1 AND TO_CHAR(BD.HEADER_ID) = RCTLA.INTERFACE_LINE_ATTRIBUTE6
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
  LEFT JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  LEFT JOIN FND_USER FU ON RCTA.CREATED_BY = FU.USER_ID
UNION ALL
-- 10. Payment Received
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Payment Received' AS "ActivityName",
  ARAA.APPLY_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'PAID' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON BD.SOLD_TO_ORG_ID = RCTA.SOLD_TO_CUSTOMER_ID
  JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  JOIN AR_RECEIVABLE_APPLICATIONS_ALL ARAA ON APSA.PAYMENT_SCHEDULE_ID = ARAA.APPLIED_PAYMENT_SCHEDULE_ID
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID AND BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1
  LEFT JOIN FND_USER FU ON ARAA.CREATED_BY = FU.USER_ID
WHERE
  ARAA.STATUS = 'APP'
UNION ALL
-- 11. Order Line Closed
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Line Closed' AS "ActivityName",
  OOLA.LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  LEFT JOIN FND_USER FU ON OOLA.LAST_UPDATED_BY = FU.USER_ID
WHERE
  OOLA.FLOW_STATUS_CODE = 'CLOSED'
UNION ALL
-- 12. Order Closed
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Closed' AS "ActivityName",
  BD.HEADER_LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.HEADER_STATUS = 'CLOSED'
UNION ALL
-- 13. Order Cancelled
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Cancelled' AS "ActivityName",
  BD.HEADER_LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.HEADER_STATUS = 'CANCELLED'

Steps

Set Up Python Environment: Ensure you have a Python 3.6+ environment. Install the necessary libraries using pip: pip install pandas oracledb. You must also have the Oracle Instant Client libraries installed and accessible in your system's path.
Obtain Database Credentials: Secure the necessary database connection details, including the username, password, and the connection string (DSN) for your Oracle E-Business Suite instance. The database user must have read access to all tables mentioned in the query.
Prepare the Script File: Create a new file named extract_o2c_events.py on your local machine.
Copy the Python Script: Copy the complete Python script provided in the 'query' section below and paste it into the extract_o2c_events.py file.
Configure Script Parameters: Open the script and modify the placeholder values within the ## CONFIGURATION ## section. This includes your database credentials, the connection DSN, and the start_date and end_date for the extraction period.
Execute the Extraction Script: Open a terminal or command prompt, navigate to the directory where you saved the file, and run the script using the command: python extract_o2c_events.py.
Monitor Execution: The script will connect to the database, execute the comprehensive SQL query, and display status messages in the console. For large datasets, this process may take a significant amount of time.
Retrieve the Event Log: Upon successful completion, the script will generate a CSV file named o2c_event_log.csv in the same directory. This file contains the extracted event log.
Prepare for Upload: Review the o2c_event_log.csv file. Ensure the column headers (SalesOrder, ActivityName, EventTime, etc.) are correct and the data is properly formatted. The file is now ready to be uploaded to your process mining tool.

Configuration

Database Connector: The script uses the oracledb library, the official Oracle driver for Python. It requires proper configuration of the Oracle Instant Client on the machine running the script.
Data Handling: The pandas library is used to execute the SQL query and structure the result set into a DataFrame, which is then easily exported to a CSV file.
Date Range: The script includes start_date and end_date variables. It is recommended to extract a data range of 3 to 6 months to balance detail with performance. For initial analysis, a smaller range might be preferable.
Key Filters: The underlying SQL query is filtered by the order creation date (ooha.creation_date). You can add more WHERE clauses to the SQL query within the script to filter by specific Operating Units (ooha.org_id) or Sales Order Types (ooha.order_type_id) if needed.
Prerequisites: You need a Python environment, network access to the Oracle database, and a database user with read permissions to tables in the Order Management (OE), Shipping (WSH), Accounts Receivable (AR), and foundational (FND, HZ) modules.

a Sample Query python

import oracledb
import pandas as pd
import sys

class=class="hl-string">"hl-comment">## CONFIGURATION ##
class=class="hl-string">"hl-comment"># Replace with your actual database credentials and connection details
ORA_USER = class="hl-string">'[your_username]'
ORA_PASS = class="hl-string">'[your_password]'
class=class="hl-string">"hl-comment"># Example DSN: class="hl-string">'your_host:1521/your_service_name'
ORA_DSN = class="hl-string">'[your_db_host:port/service_name]'

class=class="hl-string">"hl-comment"># Set the date range for the extraction
start_date = class="hl-string">'2023-01-01'
end_date = class="hl-string">'2023-06-30'

class=class="hl-string">"hl-comment"># Output file name
output_csv_file = class="hl-string">'o2c_event_log.csv'

sql_query = fclass="hl-string">"""
WITH OrderAmounts AS (
    SELECT 
        header_id,
        SUM(ordered_quantity * unit_selling_price) AS calculated_order_amount
    FROM oe_order_lines_all
    GROUP BY header_id
)
SELECT
    ooha.order_number AS "SalesOrder",
    'Sales Order Created' AS "ActivityName",
    ooha.creation_date AS "EventTime",
    fu_created.user_name AS "UserName",
    ooha.flow_status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_headers_all ooha
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu_created ON ooha.created_by = fu_created.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Order Booked' AS "ActivityName",
    ooha.booked_date AS "EventTime",
    fu_updated.user_name AS "UserName",
    'BOOKED' AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_headers_all ooha
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu_updated ON ooha.last_updated_by = fu_updated.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND ooha.booked_date IS NOT NULL

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Credit Check Performed' AS "ActivityName",
    ooh.release_date AS "EventTime",
    fu.user_name AS "UserName",
    ooha.flow_status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_holds_all ooh
    JOIN oe_order_headers_all ooha ON ooh.header_id = ooha.header_id
    JOIN oe_hold_sources_all ohs ON ooh.hold_source_id = ohs.hold_source_id
    JOIN oe_hold_definitions ohd ON ohs.hold_id = ohd.hold_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON ooh.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND ohd.name LIKE '%Credit%'
    AND ooh.release_date IS NOT NULL

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Inventory Allocated' AS "ActivityName",
    wdd.creation_date AS "EventTime",
    fu.user_name AS "UserName",
    wdd.released_status AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    oola.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM 
    wsh_delivery_details wdd
    JOIN oe_order_lines_all oola ON wdd.source_line_id = oola.line_id
    JOIN oe_order_headers_all ooha ON oola.header_id = ooha.header_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON wdd.created_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE 
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND wdd.source_code = 'OE'

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Pick Released' AS "ActivityName",
    wdd.last_update_date AS "EventTime",
    fu.user_name AS "UserName",
    wdd.released_status AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    oola.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM 
    wsh_delivery_details wdd
    JOIN oe_order_lines_all oola ON wdd.source_line_id = oola.line_id
    JOIN oe_order_headers_all ooha ON oola.header_id = ooha.header_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON wdd.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE 
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND wdd.released_status IN ('S', 'Y')

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Goods Shipped' AS "ActivityName",
    wts.actual_departure_date AS "EventTime",
    fu.user_name AS "UserName",
    wts.status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    oola.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    wsh_trip_stops wts
    JOIN wsh_delivery_legs wdl ON wts.stop_id = wdl.pick_up_stop_id
    JOIN wsh_delivery_assignments wda ON wdl.delivery_leg_id = wda.delivery_leg_id
    JOIN wsh_delivery_details wdd ON wda.delivery_detail_id = wdd.delivery_detail_id
    JOIN oe_order_lines_all oola ON wdd.source_line_id = oola.line_id
    JOIN oe_order_headers_all ooha ON oola.header_id = ooha.header_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON wts.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND wts.status_code = 'CL' -- CL = Closed
    AND wts.actual_departure_date IS NOT NULL

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Goods Delivered' AS "ActivityName",
    -- Standard EBS does not track delivery. This needs data from an external carrier system or a custom table.
    -- Using actual_departure_date + a placeholder interval as an example.
    wts.actual_departure_date + INTERVAL '2' DAY AS "EventTime",
    'SYSTEM' AS "UserName",
    'DELIVERED' AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    oola.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    wsh_trip_stops wts
    JOIN wsh_delivery_legs wdl ON wts.stop_id = wdl.pick_up_stop_id
    JOIN wsh_delivery_assignments wda ON wdl.delivery_leg_id = wda.delivery_leg_id
    JOIN wsh_delivery_details wdd ON wda.delivery_detail_id = wdd.delivery_detail_id
    JOIN oe_order_lines_all oola ON wdd.source_line_id = oola.line_id
    JOIN oe_order_headers_all ooha ON oola.header_id = ooha.header_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND wts.status_code = 'CL' AND wts.actual_departure_date IS NOT NULL

UNION ALL

SELECT
    rcta.interface_header_attribute1 AS "SalesOrder",
    'Invoice Created' AS "ActivityName",
    rcta.creation_date AS "EventTime",
    fu.user_name AS "UserName",
    'INVOICED' AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    apsa.due_date AS "PaymentDueDate"
FROM
    ra_customer_trx_all rcta
    JOIN oe_order_headers_all ooha ON rcta.interface_header_attribute1 = ooha.order_number
    JOIN hz_cust_accounts hca ON rcta.bill_to_customer_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON rcta.created_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON rcta.term_id = rtb.term_id
    LEFT JOIN ar_payment_schedules_all apsa ON rcta.customer_trx_id = apsa.customer_trx_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999

UNION ALL

SELECT
    rcta.interface_header_attribute1 AS "SalesOrder",
    'Invoice Sent to Customer' AS "ActivityName",
    rcta.trx_date + 1/1440 AS "EventTime", -- Approximating: 1 minute after creation, as this is not explicitly tracked
    'SYSTEM' AS "UserName",
    'INVOICED' AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    apsa.due_date AS "PaymentDueDate"
FROM
    ra_customer_trx_all rcta
    JOIN oe_order_headers_all ooha ON rcta.interface_header_attribute1 = ooha.order_number
    JOIN hz_cust_accounts hca ON rcta.bill_to_customer_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    LEFT JOIN ra_terms_b rtb ON rcta.term_id = rtb.term_id
    LEFT JOIN ar_payment_schedules_all apsa ON rcta.customer_trx_id = apsa.customer_trx_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Payment Received' AS "ActivityName",
    araa.apply_date AS "EventTime",
    fu.user_name AS "UserName",
    'PAID' AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    apsa.due_date AS "PaymentDueDate"
FROM
    ar_receivable_applications_all araa
    JOIN ra_customer_trx_all rcta ON araa.applied_customer_trx_id = rcta.customer_trx_id
    JOIN oe_order_headers_all ooha ON rcta.interface_header_attribute1 = ooha.order_number
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON araa.created_by = fu.user_id
    LEFT JOIN ar_payment_schedules_all apsa ON rcta.customer_trx_id = apsa.customer_trx_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND araa.status = 'APP'

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Order Line Closed' AS "ActivityName",
    oola.last_update_date AS "EventTime",
    fu.user_name AS "UserName",
    oola.flow_status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    oola.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_lines_all oola
    JOIN oe_order_headers_all ooha ON oola.header_id = ooha.header_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON oola.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND oola.flow_status_code = 'CLOSED'

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Order Closed' AS "ActivityName",
    ooha.last_update_date AS "EventTime",
    fu.user_name AS "UserName",
    ooha.flow_status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_headers_all ooha
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON ooha.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND ooha.flow_status_code = 'CLOSED'

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Order Cancelled' AS "ActivityName",
    ooha.last_update_date AS "EventTime",
    fu.user_name AS "UserName",
    ooha.flow_status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_headers_all ooha
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON ooha.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND ooha.flow_status_code = 'CANCELLED'

ORDER BY "SalesOrder", "EventTime"
"""

try:
    print(class="hl-string">"Initializing database connection...")
    class=class="hl-string">"hl-comment"># Using thick mode requires Oracle Instant Client. 
    class=class="hl-string">"hl-comment"># For thin mode, remove the lib_dir and config_dir parameters.
    oracledb.init_oracle_client()

    print(fclass="hl-string">"Connecting to {ORA_DSN}...")
    with oracledb.connect(user=ORA_USER, password=ORA_PASS, dsn=ORA_DSN) as connection:
        print(class="hl-string">"Connection successful. Executing query...")
        print(fclass="hl-string">"Extracting data from {start_date} to {end_date}.")
        
        class=class="hl-string">"hl-comment"># Use pandas to execute the query and fetch data into a DataFrame
        df = pd.read_sql_query(sql_query, connection)
        
        print(fclass="hl-string">"Query executed. Found {len(df)} events.")

        if not df.empty:
            class=class="hl-string">"hl-comment"># Data type conversions and formatting
            df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
            df[class="hl-string">'TotalOrderAmount'] = pd.to_numeric(df[class="hl-string">'TotalOrderAmount'])
            
            print(fclass="hl-string">"Saving data to {output_csv_file}...")
            df.to_csv(output_csv_file, index=False, date_format=class="hl-string">'%Y-%m-%d %H:%M:%S')
            print(class="hl-string">"Extraction complete.")
        else:
            print(class="hl-string">"No data found for the specified date range.")

except oracledb.Error as e:
    print(fclass="hl-string">"Database error occurred: {e}")
    sys.exit(1)
except Exception as e:
    print(fclass="hl-string">"An error occurred: {e}")
    sys.exit(1)

Data Template: Order to Cash - Sales Order Processing

Your Order to Cash - Sales Order Processing Data Template

Order to Cash - Sales Order Processing Attributes

Order to Cash - Sales Order Processing Activities

Extraction Guides

Oracle BI Publisher Report for Sales Order Data

Steps

Configuration

a Sample Query sql

Direct SQL Query on Oracle EBS Order Management Tables

Steps

Configuration

a Sample Query sql

Python Script Extraction via Oracle Database Connector

Steps

Configuration

a Sample Query python

Ready to Get Started?

Transform Your Order to Cash Sales Processing Now!