> Continuous Process Improvement > Purchase to Pay Purchase Order > Oracle Fusion Financials > Data Template

Data Template: Purchase to Pay - Purchase Order

Oracle Fusion Financials

Purchase to Pay - Purchase Order Attributes (22) Purchase to Pay - Purchase Order Activities (16) Extraction Guides (4)

Your Purchase to Pay - Purchase Order Data Template

This template provides a clear roadmap for collecting the necessary data to analyze your Purchase to Pay - Purchase Order process. It outlines the essential data attributes, key activities to track, and practical guidance for extracting this information. Use it to ensure you capture all critical details for effective process mining.

Recommended attributes for comprehensive data collection
Key process activities to track and analyze
Step-by-step extraction guidance for Oracle Fusion Financials

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

Download Template

Purchase to Pay - Purchase Order Attributes

These are the recommended data fields to include in your event log for comprehensive analysis of your Purchase to Pay - Purchase Order process.

3 Required 7 Recommended 12 Optional

	Name	Description
	Activity ActivityName	The name of a specific business event or step that occurred within the Purchase Order lifecycle.
Description This attribute describes a specific task or status change in the process, such as 'Purchase Order Created' or 'Goods Received'. These activities form the sequence of events that make up the process flow. Analyzing the sequence and timing of these activities is the core of process mining. It helps to visualize the process map, identify bottlenecks, discover deviations from the standard procedure, and measure the duration of specific steps. Why it matters Activities are the building blocks of the process map. Tracking them allows for visualization and analysis of the process flow, bottlenecks, and deviations. Where to get Derived from status changes in tables like PO_HEADERS_ALL and PO_ACTION_HISTORY, or from specific transaction tables like RCV_TRANSACTIONS for goods receipts. Examples Purchase Order CreatedPurchase Order ApprovedGoods Received
	Purchase Order PurchaseOrder	The unique identifier for the Purchase Order document, serving as the primary case ID for tracking the procurement lifecycle.
Description The Purchase Order number is the central identifier that links all related activities, from its creation to final closure. It allows for the end-to-end analysis of a single procurement case. In process mining, each unique Purchase Order number represents a single instance of the process. Analyzing data grouped by this identifier helps in understanding process variations, cycle times, and compliance for individual orders. Why it matters This is the essential Case ID that connects all related events, enabling the reconstruction and analysis of the entire Purchase Order lifecycle. Where to get Oracle Fusion Cloud SCM, Procurement Module, table PO_HEADERS_ALL, column SEGMENT1. Examples 100234510023461002347
	Start Time EventTime	The timestamp indicating when a specific activity or event occurred.
Description This attribute records the exact date and time for each activity in the process. It is fundamental for chronological ordering of events and for all time-based analysis. In process mining, the start time is used to construct the event log, calculate cycle times between activities, measure waiting times, and analyze process performance over different time periods. It is essential for dashboards related to cycle time and performance. Why it matters This timestamp is critical for sequencing events correctly and calculating all duration-based metrics, such as cycle times and bottlenecks. Where to get Timestamp fields such as CREATION_DATE and LAST_UPDATE_DATE from various tables, including PO_HEADERS_ALL, PO_ACTION_HISTORY, and RCV_SHIPMENT_LINES. Examples 2023-04-15T10:05:00Z2023-04-16T14:30:00Z2023-05-01T09:00:00Z
	Approver Name ApproverName	The name of the user who performed an approval or rejection action on the Purchase Order.
Description This attribute captures the individual responsible for an approval step in the workflow. This information is typically stored in an action history or workflow log table associated with the Purchase Order. Analyzing data by approver is key to the 'PO Approval Cycle Time Analysis' and 'Approval Resource Workload' dashboards. It helps identify which approvers or approval groups are bottlenecks, enables a fair assessment of workload, and can highlight opportunities for delegation or process redesign. Why it matters Identifies the individuals in the approval chain, making it possible to analyze approval bottlenecks, workload, and cycle times by approver. Where to get The user who performed the action, found in PO_ACTION_HISTORY.ACTION_PERFORMED_BY, linked to a user table for the full name. Examples susan.managerdavid.directoremily.finance
	Department DepartmentName	The name of the department that initiated or is responsible for the Purchase Order.
Description This attribute specifies the organizational unit, such as 'Finance', 'IT', or 'Manufacturing', associated with the purchase. It is used for cost allocation and organizational reporting. In a process mining context, segmenting the process by department is crucial for comparing performance, identifying department-specific bottlenecks, and understanding variations in process execution across the organization. It directly supports dashboards like 'PO Approval Cycle Time Analysis' and 'Purchase Order Modification Trends'. Why it matters Allows for filtering and comparing process performance across different business units, revealing department-specific issues or best practices. Where to get Derived from cost center information in tables like PO_DISTRIBUTIONS_ALL, which links to department master data. Examples IT OperationsMarketingResearch and Development
	End Time EndTime	The timestamp when an activity was completed. Often the same as the Start Time for atomic events.
Description For activities that have a duration, this marks the completion time. For instantaneous events, it is typically the same as the Start Time. It's essential for calculating the processing time of individual activities. Having a distinct End Time allows for more precise analysis of activity durations, which can be different from the waiting time between activities. This helps differentiate active work time from idle time, supporting resource workload and efficiency analysis. Why it matters Enables the calculation of precise activity processing times, which is key for analyzing resource efficiency and identifying time-consuming tasks. Where to get Can be the same as the Start Time for atomic events or derived from subsequent event timestamps. For some activities, a separate completion timestamp may exist. Examples 2023-04-15T10:05:00Z2023-04-16T14:45:00Z2023-05-01T09:15:00Z
	PO Total Amount PurchaseOrderTotalAmount	The total monetary value of the Purchase Order.
Description This attribute represents the total cost of all items on the Purchase Order in the specified currency. It is a key financial metric for understanding the value of transactions flowing through the process. Analyzing PO Total Amount helps in prioritizing process improvement efforts. For instance, high-value orders might be routed through a more stringent approval process. It also allows for financial impact analysis, such as calculating the value of POs that are frequently modified or delayed. Why it matters Provides financial context to the process, allowing for analysis based on monetary value, such as focusing on high-value orders or understanding financial impacts of delays. Where to get Calculated by summing the amounts from PO_LINES_ALL for a given PO header, or taken from a header-level total if available. Examples 5250.00120000.50750.99
	Requested Delivery Date RequestedDeliveryDate	The date by which the requesting party expects the goods or services to be delivered.
Description This date is specified on the purchase order line and communicates the desired delivery timeline to the vendor. It serves as the baseline for measuring on-time delivery performance. This attribute is critical for calculating the 'On-Time Delivery Rate' KPI. By comparing the actual goods receipt date with the requested delivery date, organizations can quantitatively measure and track vendor reliability and identify systemic delays in the supply chain. Why it matters Serves as the baseline for measuring on-time delivery performance, a key KPI for evaluating vendor reliability and supply chain efficiency. Where to get Located at the line location level, in table PO_LINE_LOCATIONS_ALL, column NEED_BY_DATE. Examples 2023-05-202023-06-152023-07-01
	User UserName	The user ID or name of the person who performed the activity.
Description This attribute identifies the employee or system user responsible for a given event, such as creating a requisition, approving a PO, or posting a goods receipt. It is typically sourced from fields like 'Created By' or 'Last Updated By'. Analyzing the process by user helps in understanding workload distribution, individual performance, and identifying training needs. It is fundamental for the 'Approval Resource Workload' dashboard and for investigating compliance issues related to user actions. Why it matters Attributes user actions to specific individuals, enabling workload analysis, performance evaluation, and identification of training opportunities. Where to get Join with user tables based on IDs in fields like CREATED_BY or LAST_UPDATED_BY in tables such as PO_HEADERS_ALL and PO_ACTION_HISTORY. Examples john.doejane.smithsystem.batch
	Vendor Name VendorName	The name of the supplier or vendor from whom the goods or services are being purchased.
Description This attribute identifies the external supplier for the Purchase Order. It is a critical piece of master data linked to the PO header. Vendor analysis is a key part of P2P process mining. By filtering or segmenting by vendor, companies can analyze 'Vendor Delivery Performance', compare on-time delivery rates, and investigate 'Goods Return Rate' to identify high or low-performing suppliers. This data is essential for managing supplier relationships and strategic sourcing. Why it matters Essential for supplier performance analysis, enabling comparison of delivery times, return rates, and overall reliability across vendors. Where to get Linked from PO_HEADERS_ALL.VENDOR_ID to POZ_SUPPLIERS.VENDOR_NAME. Examples Global Office SuppliesTech Solutions Inc.Advanced Logistics Co.
	Approval Cycle Time ApprovalCycleTime	The duration from when a Purchase Order was created until it was finally approved.
Description This is a calculated duration metric that measures the time elapsed between the 'Purchase Order Created' activity and the 'Purchase Order Approved' activity for a single case. This attribute directly provides the value for the 'Average PO Approval Cycle Time' KPI. Analyzing its distribution helps to understand the efficiency of the approval workflow, identify outliers, and measure the impact of process improvement initiatives aimed at reducing approval delays. Why it matters Quantifies the approval bottleneck, directly measuring the 'Average PO Approval Cycle Time' KPI and highlighting delays in the workflow. Where to get Calculated field. The difference between the timestamps of the 'Purchase Order Approved' and 'Purchase Order Created' activities. Examples P2DPT8H30MP5DT12H
	Business Unit BusinessUnitName	The specific business unit within the organization that is making the purchase.
Description The Business Unit represents a distinct business entity within the enterprise, often with its own ledger and financial reporting. It is a primary data segregation mechanism in Oracle Fusion. Analyzing process performance by Business Unit is crucial in large, multinational corporations. It allows for comparison of procurement efficiency, compliance, and costs across different parts of the organization, highlighting both best practices and areas for improvement. Why it matters Crucial for large organizations to compare process efficiency and compliance across different operational divisions. Where to get The business unit context is typically on the PO header, PO_HEADERS_ALL.PRC_BU_ID, which links to the FUN_ALL_BUSINESS_UNITS_V view. Examples US Business UnitEMEA VisionAPAC Services
	Delivery Location DeliveryLocation	The physical location or address where the goods are to be delivered.
Description This attribute specifies the ship-to address for the items on the Purchase Order. It is a key piece of logistical information. For process mining, analyzing by delivery location supports the 'Goods Receipt Processing Efficiency' dashboard. It helps to identify if certain warehouses or sites are slower in their receiving process, pointing to potential resource or process issues at specific locations. Why it matters Allows for performance analysis by geographic location, helping to identify regional or site-specific bottlenecks in the goods receipt process. Where to get Linked from PO_LINE_LOCATIONS_ALL.SHIP_TO_LOCATION_ID to the HR_LOCATIONS_ALL view. Examples Main Warehouse - Dock ABuilding 3 - ReceptionSF Office - 10th Floor
	Is Approval Compliant IsApprovalCompliant	A flag indicating if the PO was approved before being sent to the vendor.
Description This is a calculated boolean attribute that checks for adherence to a key internal control: a Purchase Order must be approved before it is dispatched to a supplier. It is true if the 'Purchase Order Approved' activity occurs before the 'Purchase Order Sent to Vendor' activity. This attribute is essential for the 'PO Process Compliance Audit' dashboard and the 'PO Approval Compliance Rate' KPI. It provides a straightforward way to identify and quantify compliance breaches, helping to enforce procurement policies and mitigate risks associated with unauthorized spending. Why it matters Directly measures the 'PO Approval Compliance Rate' KPI, highlighting critical internal control violations where orders are sent to vendors before approval. Where to get Calculated field. Set to 'true' if the timestamp for 'Purchase Order Approved' is less than or equal to the timestamp for 'Purchase Order Sent to Vendor'. Examples truefalse
	Is Late Delivery IsLateDelivery	A flag indicating if the final goods receipt occurred after the requested delivery date.
Description This calculated boolean attribute is true if the timestamp of the 'Goods Received' activity is later than the value in the 'Requested Delivery Date' attribute for a given Purchase Order. This flag is the foundation for the 'On-Time Delivery Rate' KPI. It allows for easy segmentation and analysis of late versus on-time orders, helping to investigate the root causes of delays, whether they are related to specific vendors, locations, or product categories. Why it matters Directly supports the 'On-Time Delivery Rate' KPI, allowing for clear analysis of vendor performance and delivery reliability. Where to get Calculated field. Set to 'true' if the 'Goods Received' activity timestamp is after the 'RequestedDeliveryDate' attribute. Examples truefalse
	Is Rework IsRework	A flag indicating if the Purchase Order was modified after its initial creation.
Description This is a calculated boolean attribute that is set to true if a Purchase Order case contains a 'Purchase Order Changed' activity. It helps to quickly identify orders that required corrections or modifications. This flag simplifies the calculation of the 'PO Modification Rate' KPI and allows for easy filtering and analysis of reworked orders. Understanding the characteristics of reworked orders, such as the vendors or departments involved, can help pinpoint root causes of data inaccuracy or changing requirements. Why it matters Directly supports the 'PO Modification Rate' KPI and simplifies analysis of process instability by flagging all orders that underwent changes. Where to get Calculated field. Set to 'true' if the event log for a case contains the activity 'Purchase Order Changed', otherwise 'false'. Examples truefalse
	Last Data Update LastDataUpdate	The timestamp when the data was last extracted or refreshed from the source system.
Description This attribute indicates the freshness of the data being analyzed. It records the date and time of the most recent data pull from Oracle Fusion Financials. This information is vital for users to understand the timeliness of the analysis and the dashboards. It clarifies how up-to-date the process insights are, managing expectations about the inclusion of very recent transactions. Why it matters Provides transparency on data freshness, ensuring users understand how current the process analysis is. Where to get This is a timestamp generated and added during the data extraction and transformation (ETL) process. Examples 2023-10-27T02:00:00Z2023-10-28T02:00:00Z
	PO Status PurchaseOrderStatus	The current status of the Purchase Order document.
Description This attribute indicates the current state of the Purchase Order in its lifecycle, such as 'Open', 'Approved', 'Finally Closed', or 'Canceled'. It provides a snapshot of the PO's progress. While process mining focuses on the sequence of activities, the current status is valuable for filtering cases. For example, analysis can be focused on only open POs to understand the current pipeline, or on closed POs to analyze completed process instances. It is essential for the 'Purchase Order Flow & Status' dashboard. Why it matters Provides a current snapshot of a purchase order's state, allowing for analysis to be filtered to active, completed, or canceled orders. Where to get Oracle Fusion Cloud SCM, table PO_HEADERS_ALL, columns AUTHORIZATION_STATUS or DOCUMENT_STATUS. Examples OPENAPPROVEDFINALLY_CLOSEDCANCELED
	PO Type PurchaseOrderType	The type of Purchase Order, such as 'Standard', 'Blanket', or 'Contract'.
Description This attribute classifies the Purchase Order based on its procurement purpose. Different types of POs often follow different process rules and lifecycles. A 'Standard' PO is a one-time purchase, while a 'Blanket' PO is a longer-term agreement with a supplier. Analyzing the process by PO Type allows for a more accurate view of process performance, as comparing the cycle time of a standard PO to a blanket agreement would be misleading. It enables like-for-like comparisons. Why it matters Differentiates between various procurement scenarios, enabling more accurate, apples-to-apples comparisons of process performance for similar types of orders. Where to get Oracle Fusion Cloud SCM, table PO_HEADERS_ALL, column TYPE_LOOKUP_CODE. Examples STANDARDBLANKETCONTRACT
	Purchase Category PurchaseCategory	The classification of the goods or services being purchased, such as 'IT Hardware' or 'Office Supplies'.
Description This attribute categorizes the items on the Purchase Order into a procurement hierarchy. This classification is used for spend analysis and supplier management. In process mining, segmenting the process by purchase category can reveal different behaviors or performance levels. For example, the approval process for capital expenditures might be longer than for operational supplies. It directly supports the 'Goods Return Rate & Reasons' dashboard by allowing analysis of which categories are returned most often. Why it matters Enables process analysis by type of spend, which can reveal different process paths, bottlenecks, or return rates for different categories of goods. Where to get Linked from PO_LINES_ALL.CATEGORY_ID to the EGP_CATEGORIES_VL view. Examples IT.Hardware.LaptopsOffice.Supplies.StationeryProfessional.Services.Consulting
	Purchase Requisition PurchaseRequisitionNumber	The identifier of the Purchase Requisition that preceded and authorized the Purchase Order.
Description The Purchase Requisition is the internal document used to request the procurement of goods or services. This attribute links the Purchase Order back to its originating request. Including the requisition number allows for a broader analysis of the procurement process, starting from the initial request rather than just the PO. It can help analyze the cycle time from request to order, and understand how requisition details influence the downstream PO process. Why it matters Links the PO to the initial request, enabling a more comprehensive end-to-end process view from requisition to payment. Where to get Linked via the PO_DISTRIBUTIONS_ALL table, which contains REQ_DISTRIBUTION_ID, tracing back to the POR_REQUISITION_LINES_ALL table. Examples PR-2023-05-001PR-2023-05-002PR-2023-05-003
	Source System SourceSystem	The information system from which this data was extracted.
Description This attribute identifies the origin of the data, which is especially useful in environments with multiple integrated systems. For this process, it would typically be 'Oracle Fusion Financials'. While often a static value for a given dataset, it is crucial for data governance, troubleshooting, and ensuring data lineage. In analyses that combine data from multiple sources, it allows for filtering and segmentation by the system of origin. Why it matters Identifies the data's origin, which is crucial for data governance, context, and integration with other systems. Where to get This is typically a constant value defined and added during the data extraction and transformation (ETL) process. Examples Oracle Fusion FinancialsOracle Cloud SCMOracle Fusion P2P

Required Recommended Optional

Purchase to Pay - Purchase Order Activities

These are the key process steps and milestones to capture in your event log for accurate Purchase to Pay - Purchase Order process discovery.

6 Recommended 10 Optional

	Activity	Description
	Goods Received	The physical goods have been received, counted, and recorded against the purchase order. This is a transactional event that updates inventory and the PO status.
Why it matters This is a key milestone for measuring vendor on-time delivery performance and overall lead time. It also serves as a trigger for subsequent activities like quality inspection and invoice matching. Where to get This is an explicit event recorded in the RCV_TRANSACTIONS table. The specific transaction can be identified by a TRANSACTION_TYPE of 'RECEIVE'. Capture Use the TRANSACTION_DATE from RCV_TRANSACTIONS where TRANSACTION_TYPE is 'RECEIVE'. Event type explicit
	Purchase Order Approved	The purchase order has received all necessary approvals and is now authorized for dispatch to the vendor. This is a key milestone event explicitly logged in the document's action history.
Why it matters This is a critical milestone that gates the PO from being sent to the vendor. It is essential for measuring approval cycle times and ensuring compliance with spending policies. Where to get This event is recorded in the PO_ACTION_HISTORY table, typically with an ACTION_CODE of 'APPROVE' or when the document status in PO_HEADERS_ALL changes to an approved state. Capture Filter PO_ACTION_HISTORY for the final 'APPROVE' action. Event type explicit
	Purchase Order Canceled	The purchase order has been permanently canceled and no further transactions are expected. This is an explicit action that changes the document's terminal status.
Why it matters This activity represents a negative end state for the process. Analyzing cancellations can reveal issues such as duplicate orders, budget changes, or changes in project requirements. Where to get This action is recorded in the PO_ACTION_HISTORY table with an ACTION_CODE of 'CANCEL', and the PO status in PO_HEADERS_ALL is updated accordingly. Capture Filter PO_ACTION_HISTORY for ACTION_CODE = 'CANCEL'. Event type explicit
	Purchase Order Created	This is the official start of the purchase order lifecycle, where a PO document is generated in a draft or incomplete status. The system captures this event by recording the creation timestamp of the new PO header record.
Why it matters As the primary start event for the PO case, this activity is fundamental for all cycle time calculations. It provides the baseline for measuring the efficiency of subsequent steps like approval and supplier communication. Where to get This is an explicit event based on the CREATION_DATE field in the PO_HEADERS_ALL table for a given Purchase Order ID (PO_HEADER_ID). Capture Use the creation timestamp from the PO_HEADERS_ALL table. Event type explicit
	Purchase Order Finally Closed	The purchase order is considered complete, meaning it has been fully received and/or invoiced, and no further activity is anticipated. This is an explicit action that sets a terminal status on the PO.
Why it matters This activity marks the successful completion of the purchase order lifecycle. It is the primary positive end state, and tracking it is essential for measuring overall process throughput and completion rates. Where to get This event is recorded in the PO_ACTION_HISTORY table with an ACTION_CODE of 'FINALLY CLOSE'. The PO status in PO_HEADERS_ALL is also updated to 'Finally Closed'. Capture Filter PO_ACTION_HISTORY for ACTION_CODE = 'FINALLY CLOSE'. Event type explicit
	Purchase Order Sent to Vendor	The approved purchase order is officially communicated to the vendor, for example via email or EDI. This event is often inferred from a status change or a timestamp on the PO communication record.
Why it matters This marks the start of the vendor's lead time. It is a crucial point for measuring supplier performance, from acknowledgement to final delivery. Where to get This can be inferred from the PO document status changing to 'Open' and a communication date being populated. The specific field is often `PO_HEADERS_ALL.communicated_date` or a related status. Capture Infer from the timestamp when the PO's communication status is updated to 'Communicated'. Event type inferred
	Goods Receipt Created	A receiving document is initiated in the system in preparation for the physical arrival of goods. This activity signifies the start of the internal receiving process.
Why it matters This marks the transition from procurement to logistics. Analyzing the time from this point to the final receipt posting helps identify inefficiencies in the warehouse or receiving department. Where to get This is an explicit event, captured by the creation timestamp of a new record in the RCV_SHIPMENT_HEADERS table linked to the purchase order. Capture Use the creation date of the corresponding record in RCV_SHIPMENT_HEADERS. Event type explicit
	Goods Returned to Vendor	Previously received goods are sent back to the vendor, typically due to defects, damages, or incorrect shipments. This is captured as a specific return transaction in the receiving module.
Why it matters Tracking returns is essential for evaluating supplier quality and order accuracy. High return rates for a vendor may indicate systemic issues that need to be addressed. Where to get This is an explicit event recorded in the RCV_TRANSACTIONS table with a TRANSACTION_TYPE of 'RETURN TO VENDOR'. Capture Use the TRANSACTION_DATE from RCV_TRANSACTIONS where TRANSACTION_TYPE is 'RETURN TO VENDOR'. Event type explicit
	Purchase Order Acknowledged	The vendor has confirmed receipt and acceptance of the purchase order terms. This event is often captured manually by procurement staff based on vendor communication or through an electronic acknowledgement.
Why it matters Vendor acknowledgement provides certainty that the order has been received and is being processed. Tracking this helps in managing supplier communication and proactively identifying potential fulfillment issues. Where to get This is typically inferred from a change in the acknowledgement status fields on the PO header or lines, such as `PO_HEADERS_ALL.acceptance_status` changing to 'Accepted'. Capture Infer from updates to PO acknowledgement status fields. Event type inferred
	Purchase Order Changed	A modification has been made to the purchase order after its initial approval, such as a change in quantity, price, or delivery date. Oracle Fusion tracks this by creating a new revision of the document.
Why it matters PO changes represent rework and can indicate issues with initial order accuracy or changing business needs. Analyzing the frequency and nature of these changes helps identify opportunities to improve process efficiency. Where to get This event is explicitly captured when a new document revision is created. This can be identified by an increment in the `REVISION_NUM` field in the PO_HEADERS_ALL table. Capture Identify each instance where the REVISION_NUM for a PO_HEADER_ID increases. Event type explicit
	Purchase Order Rejected	An approver has rejected the purchase order, sending it back to the creator for revision. This is an explicit event logged in the action history, indicating a break in the standard process flow.
Why it matters Rejections introduce rework and delays into the process. Tracking this activity helps identify common reasons for rejection, training needs, or unclear approval requirements. Where to get This action is recorded in the PO_ACTION_HISTORY table with an ACTION_CODE of 'REJECT' for the corresponding PO_HEADER_ID. Capture Filter PO_ACTION_HISTORY for ACTION_CODE = 'REJECT'. Event type explicit
	Purchase Order Submitted	The created purchase order is submitted into the approval workflow. Oracle Fusion explicitly logs this action, capturing the user and timestamp for the submission event.
Why it matters This activity marks the beginning of the approval cycle. Analyzing the time between submission and approval is key to identifying bottlenecks in the internal sign-off process. Where to get This action is recorded in the PO_ACTION_HISTORY table with an ACTION_CODE of 'SUBMIT' for the corresponding PO_HEADER_ID. Capture Filter PO_ACTION_HISTORY for ACTION_CODE = 'SUBMIT'. Event type explicit
	Purchase Requisition Approved	The purchase requisition has been approved by the designated authority, authorizing the procurement department to create a purchase order. This event is explicitly logged in the system's action history for the requisition.
Why it matters This milestone signifies the end of the internal approval process for the request. Delays here can directly impact the entire procurement timeline, so monitoring its duration is crucial. Where to get This event is recorded in the action history associated with the requisition, typically tracked via workflow tables or specific approval status fields on the requisition document. Capture Logged as an approval action in the workflow history for the specific requisition document. Event type explicit
	Purchase Requisition Created	This activity marks the creation of a purchase requisition, which is the formal request for goods or services that precedes a purchase order. It is captured when a new entry is created in the requisition header table within Oracle Fusion.
Why it matters Analyzing this activity helps understand the demand origination phase. Tracking the time from requisition to purchase order creation reveals potential delays in converting internal demand into actionable procurement orders. Where to get This is an explicit event recorded upon saving a new requisition. It can be found by tracking the creation timestamp in the POR_REQUISITION_HEADERS_ALL table. Capture Event is based on the creation date of the record in the POR_REQUISITION_HEADERS_ALL table. Event type explicit
	Quality Inspection Performed	Goods that require quality control have been inspected and either accepted or rejected. This activity occurs after the initial receipt and is recorded as a distinct transaction.
Why it matters This activity is crucial for quality management. Analyzing the duration of inspections helps to streamline the quality control process and reduce delays in making goods available for use. Where to get This is recorded in the RCV_TRANSACTIONS table. It is identified by transactions with a TRANSACTION_TYPE of 'ACCEPT' or 'REJECT' that follow a receipt transaction. Capture Use TRANSACTION_DATE from RCV_TRANSACTIONS where TRANSACTION_TYPE is 'ACCEPT' or 'REJECT'. Event type explicit
	Services Delivered Confirmed	For service-based purchase orders, this activity marks the confirmation that services have been rendered as agreed. This confirmation is often recorded manually or through a service entry sheet.
Why it matters This is the equivalent of a goods receipt for services and is a critical step before an invoice can be paid. Delays in service confirmation can lead to late payments and strained vendor relationships. Where to get This is typically captured as a receipt against a service line on the PO. It may involve specific fields or complex receipts that track progress or completion of services. Capture Identify receipt transactions (RCV_TRANSACTIONS) linked to service-based PO lines. Event type explicit

Recommended Optional

Extraction Guides

How to get your data from Oracle Fusion Financials

Steps

Navigate to OTBI: Log in to Oracle Fusion Applications and navigate to the 'Reports and Analytics' work area from the main navigator menu. Click 'Browse Catalog' to open the Business Intelligence catalog.
Create a New Analysis: Click the 'Create' button in the toolbar and select 'Analysis'. You will be prompted to choose a subject area.
Select Subject Area: From the list of available subject areas, select 'Procurement - Purchasing Real Time'. This subject area contains the necessary data for purchase requisitions, orders, and receipts.
Build the First Criteria Block (Purchase Order Created): In the 'Criteria' tab, drag and drop the required columns into the 'Selected Columns' area. For the first activity, 'Purchase Order Created', include:
- "PO Headers All"."PO Number" as PurchaseOrder
- A custom text formula 'Purchase Order Created' as ActivityName
- "PO Headers All"."Creation Date" as EventTime and EndTime
- "PO Headers All"."Created By" as UserName
- "Requisitioning BU"."Department Name" as DepartmentName
- "Supplier"."Supplier" as VendorName
- "Base Measures"."Ordered Amount" as PurchaseOrderTotalAmount
- "PO Schedules All"."Requested Delivery Date" as RequestedDeliveryDate
- NULL as ApproverName
Create Union Reports for All Activities: To capture each activity as a separate row, you must combine multiple reports using a 'Union All' operation. For each of the remaining 15 activities, you will create a similar criteria block. Click the 'Combine results based on another analysis' icon at the bottom of the 'Selected Columns' section and choose 'Union All'.
Configure Each Activity Block: Create a new criteria block for each of the 16 activities listed in the requirements. For each block, set the ActivityName to a static value (e.g., 'Purchase Order Approved') and map the corresponding date column to EventTime. Use the same set of attribute columns in each block, populating them with NULL if the data is not applicable to that specific event (e.g., ApproverName is NULL for the 'Purchase Order Created' event).
Map Key Date and User Fields:
- Approval/Rejection: Use the 'Document History' folder to get action dates and the performing user for EventTime and ApproverName.
- Requisition: Use the 'Requisition Headers' folder for requisition creation and approval dates.
- Receiving: Use the 'Receipts' and 'Receipt Line Transactions' folders for events like 'Goods Received' and 'Goods Returned to Vendor'. Filter on the 'Transaction Type' for specific receiving activities.
- Changes: Filter for "PO Headers All"."Revision Number" > 0 and use the creation date of the revision for the 'Purchase Order Changed' activity.
Add Filters: Switch to the 'Filters' section for the combined analysis. Add a global filter to limit the data scope. A common and effective filter is on 'PO Headers All'.'Creation Date', prompting the user for a date range (e.g., the last 6 months).
Save and Run the Analysis: Save the analysis in a shared folder. Click the 'Results' tab to preview the data. The output should be a long table with one row per event.
Export the Event Log: Once the results are verified, click the 'Export' link on the results page. Choose 'CSV File' as the format. This will download the event log file, ready for upload.

Configuration

Subject Area: Primarily use 'Procurement - Purchasing Real Time'. This subject area contains dimensions and facts related to purchase orders, requisitions, suppliers, receipts, and document history.
Report Structure: A 'Union All' analysis is required. Each component query in the union represents a single business activity (e.g., 'Purchase Order Created', 'Purchase Order Approved'), ensuring each activity becomes a distinct row in the final event log.
Date Range Filter: It is critical to apply a filter on a key date field, such as "PO Headers All"."Creation Date", to limit the query scope. A typical range for analysis is 3 to 6 months of data. For initial testing, use a shorter period or filter on a single Purchase Order number.
Business Unit Filter: If your organization operates with multiple business units, add a filter on "Business Unit"."Business Unit Name" to extract data for a specific entity.
Prerequisites: The user running the report needs a role with the necessary privileges to run OTBI analyses (e.g., BI Author) and access to the 'Procurement - Purchasing Real Time' subject area. Data visibility will be constrained by the user's data security profile in Oracle Fusion.

a Sample Query config

/* This is a conceptual representation of the logic to be built in the OTBI Analysis using the 'Union All' feature. 
Each SELECT statement corresponds to a separate criteria block in the OTBI analysis. */

-- 1. Purchase Requisition Created
SELECT
  "PO Requisition Lines All"."PO Header Number" AS PurchaseOrder,
  'Purchase Requisition Created' AS ActivityName,
  "Requisition Headers"."Creation Date" AS EventTime,
  "Requisition Headers"."Creation Date" AS EndTime,
  "Requisition Headers"."Created By" AS UserName,
  "Requisition Headers"."Department" AS DepartmentName,
  NULL AS VendorName,
  NULL AS PurchaseOrderTotalAmount,
  "Requisition Lines"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Requisition Headers"."Creation Date" IS NOT NULL

UNION ALL

-- 2. Purchase Requisition Approved
SELECT
  "PO Requisition Lines All"."PO Header Number" AS PurchaseOrder,
  'Purchase Requisition Approved' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisition Headers"."Department" AS DepartmentName,
  NULL AS VendorName,
  NULL AS PurchaseOrderTotalAmount,
  "Requisition Lines"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'REQUISITION' AND "Document History"."Action" = 'APPROVE'

UNION ALL

-- 3. Purchase Order Created
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Created' AS ActivityName,
  "PO Headers All"."Creation Date" AS EventTime,
  "PO Headers All"."Creation Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Creation Date" IS NOT NULL

UNION ALL

-- 4. Purchase Order Submitted
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Submitted' AS ActivityName,
  "PO Headers All"."Submitted Date" AS EventTime,
  "PO Headers All"."Submitted Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName, -- Submitter is often the creator
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Submitted Date" IS NOT NULL

UNION ALL

-- 5. Purchase Order Approved
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Approved' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'PO' AND "Document History"."Action" = 'APPROVE'

UNION ALL

-- 6. Purchase Order Rejected
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Rejected' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'PO' AND "Document History"."Action" = 'REJECT'

UNION ALL

-- 7. Purchase Order Sent to Vendor (Communicated)
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Sent to Vendor' AS ActivityName,
  "PO Headers All"."PO Communication Date" AS EventTime,
  "PO Headers All"."PO Communication Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName, -- User who took action
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."PO Communication Date" IS NOT NULL AND "PO Headers All"."Document Status" <> 'INCOMPLETE'

UNION ALL

-- 8. Purchase Order Acknowledged
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Acknowledged' AS ActivityName,
  "PO Headers All"."Acknowledged Date" AS EventTime,
  "PO Headers All"."Acknowledged Date" AS EndTime,
  NULL AS UserName, -- Often an automated or system action
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Acknowledged Date" IS NOT NULL

UNION ALL

-- 9. Purchase Order Changed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Changed' AS ActivityName,
  "PO Headers All"."Creation Date" AS EventTime, -- The creation date of the new revision
  "PO Headers All"."Creation Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Revision Number" > 0

UNION ALL

-- 10. Goods Receipt Created
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Receipt Created' AS ActivityName,
  "Receipt"."Creation Date" AS EventTime,
  "Receipt"."Creation Date" AS EndTime,
  "Receipt"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt"."Creation Date" IS NOT NULL

UNION ALL

-- 11. Goods Received
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Received' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RECEIVE'

UNION ALL

-- 12. Quality Inspection Performed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Quality Inspection Performed' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" IN ('ACCEPT', 'REJECT')

UNION ALL

-- 13. Goods Returned to Vendor
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Returned to Vendor' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RETURN TO VENDOR'

UNION ALL

-- 14. Services Delivered Confirmed (Modeled as a receipt against a Service line)
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Services Delivered Confirmed' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RECEIVE' AND "PO Lines All"."Line Type" = 'Fixed Price Services'

UNION ALL

-- 15. Purchase Order Canceled
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Canceled' AS ActivityName,
  "PO Headers All"."Cancel Date" AS EventTime,
  "PO Headers All"."Cancel Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName, -- User who cancelled
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Cancel Flag" = 'Y' AND "PO Headers All"."Cancel Date" IS NOT NULL

UNION ALL

-- 16. Purchase Order Finally Closed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Finally Closed' AS ActivityName,
  "PO Headers All"."Closed Date" AS EventTime,
  "PO Headers All"."Closed Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName, -- User who closed
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Closed Code" = 'FINALLY CLOSED' AND "PO Headers All"."Closed Date" IS NOT NULL

Steps

Navigate to OTBI: Log in to Oracle Fusion Applications and navigate to the 'Reports and Analytics' work area from the main navigator menu. Click 'Browse Catalog' to open the Business Intelligence catalog.
Create a New Analysis: Click the 'Create' button in the toolbar and select 'Analysis'. You will be prompted to choose a subject area.
Select Subject Area: From the list of available subject areas, select 'Procurement - Purchasing Real Time'. This subject area contains the necessary data for purchase requisitions, orders, and receipts.
Build the First Criteria Block (Purchase Order Created): In the 'Criteria' tab, drag and drop the required columns into the 'Selected Columns' area. For the first activity, 'Purchase Order Created', include:
- "PO Headers All"."PO Number" as PurchaseOrder
- A custom text formula 'Purchase Order Created' as ActivityName
- "PO Headers All"."Creation Date" as EventTime and EndTime
- "PO Headers All"."Created By" as UserName
- "Requisitioning BU"."Department Name" as DepartmentName
- "Supplier"."Supplier" as VendorName
- "Base Measures"."Ordered Amount" as PurchaseOrderTotalAmount
- "PO Schedules All"."Requested Delivery Date" as RequestedDeliveryDate
- NULL as ApproverName
Create Union Reports for All Activities: To capture each activity as a separate row, you must combine multiple reports using a 'Union All' operation. For each of the remaining 15 activities, you will create a similar criteria block. Click the 'Combine results based on another analysis' icon at the bottom of the 'Selected Columns' section and choose 'Union All'.
Configure Each Activity Block: Create a new criteria block for each of the 16 activities listed in the requirements. For each block, set the ActivityName to a static value (e.g., 'Purchase Order Approved') and map the corresponding date column to EventTime. Use the same set of attribute columns in each block, populating them with NULL if the data is not applicable to that specific event (e.g., ApproverName is NULL for the 'Purchase Order Created' event).
Map Key Date and User Fields:
- Approval/Rejection: Use the 'Document History' folder to get action dates and the performing user for EventTime and ApproverName.
- Requisition: Use the 'Requisition Headers' folder for requisition creation and approval dates.
- Receiving: Use the 'Receipts' and 'Receipt Line Transactions' folders for events like 'Goods Received' and 'Goods Returned to Vendor'. Filter on the 'Transaction Type' for specific receiving activities.
- Changes: Filter for "PO Headers All"."Revision Number" > 0 and use the creation date of the revision for the 'Purchase Order Changed' activity.
Add Filters: Switch to the 'Filters' section for the combined analysis. Add a global filter to limit the data scope. A common and effective filter is on 'PO Headers All'.'Creation Date', prompting the user for a date range (e.g., the last 6 months).
Save and Run the Analysis: Save the analysis in a shared folder. Click the 'Results' tab to preview the data. The output should be a long table with one row per event.
Export the Event Log: Once the results are verified, click the 'Export' link on the results page. Choose 'CSV File' as the format. This will download the event log file, ready for upload.

Configuration

Subject Area: Primarily use 'Procurement - Purchasing Real Time'. This subject area contains dimensions and facts related to purchase orders, requisitions, suppliers, receipts, and document history.
Report Structure: A 'Union All' analysis is required. Each component query in the union represents a single business activity (e.g., 'Purchase Order Created', 'Purchase Order Approved'), ensuring each activity becomes a distinct row in the final event log.
Date Range Filter: It is critical to apply a filter on a key date field, such as "PO Headers All"."Creation Date", to limit the query scope. A typical range for analysis is 3 to 6 months of data. For initial testing, use a shorter period or filter on a single Purchase Order number.
Business Unit Filter: If your organization operates with multiple business units, add a filter on "Business Unit"."Business Unit Name" to extract data for a specific entity.
Prerequisites: The user running the report needs a role with the necessary privileges to run OTBI analyses (e.g., BI Author) and access to the 'Procurement - Purchasing Real Time' subject area. Data visibility will be constrained by the user's data security profile in Oracle Fusion.

a Sample Query config

/* This is a conceptual representation of the logic to be built in the OTBI Analysis using the 'Union All' feature. 
Each SELECT statement corresponds to a separate criteria block in the OTBI analysis. */

-- 1. Purchase Requisition Created
SELECT
  "PO Requisition Lines All"."PO Header Number" AS PurchaseOrder,
  'Purchase Requisition Created' AS ActivityName,
  "Requisition Headers"."Creation Date" AS EventTime,
  "Requisition Headers"."Creation Date" AS EndTime,
  "Requisition Headers"."Created By" AS UserName,
  "Requisition Headers"."Department" AS DepartmentName,
  NULL AS VendorName,
  NULL AS PurchaseOrderTotalAmount,
  "Requisition Lines"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Requisition Headers"."Creation Date" IS NOT NULL

UNION ALL

-- 2. Purchase Requisition Approved
SELECT
  "PO Requisition Lines All"."PO Header Number" AS PurchaseOrder,
  'Purchase Requisition Approved' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisition Headers"."Department" AS DepartmentName,
  NULL AS VendorName,
  NULL AS PurchaseOrderTotalAmount,
  "Requisition Lines"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'REQUISITION' AND "Document History"."Action" = 'APPROVE'

UNION ALL

-- 3. Purchase Order Created
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Created' AS ActivityName,
  "PO Headers All"."Creation Date" AS EventTime,
  "PO Headers All"."Creation Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Creation Date" IS NOT NULL

UNION ALL

-- 4. Purchase Order Submitted
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Submitted' AS ActivityName,
  "PO Headers All"."Submitted Date" AS EventTime,
  "PO Headers All"."Submitted Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName, -- Submitter is often the creator
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Submitted Date" IS NOT NULL

UNION ALL

-- 5. Purchase Order Approved
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Approved' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'PO' AND "Document History"."Action" = 'APPROVE'

UNION ALL

-- 6. Purchase Order Rejected
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Rejected' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'PO' AND "Document History"."Action" = 'REJECT'

UNION ALL

-- 7. Purchase Order Sent to Vendor (Communicated)
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Sent to Vendor' AS ActivityName,
  "PO Headers All"."PO Communication Date" AS EventTime,
  "PO Headers All"."PO Communication Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName, -- User who took action
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."PO Communication Date" IS NOT NULL AND "PO Headers All"."Document Status" <> 'INCOMPLETE'

UNION ALL

-- 8. Purchase Order Acknowledged
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Acknowledged' AS ActivityName,
  "PO Headers All"."Acknowledged Date" AS EventTime,
  "PO Headers All"."Acknowledged Date" AS EndTime,
  NULL AS UserName, -- Often an automated or system action
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Acknowledged Date" IS NOT NULL

UNION ALL

-- 9. Purchase Order Changed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Changed' AS ActivityName,
  "PO Headers All"."Creation Date" AS EventTime, -- The creation date of the new revision
  "PO Headers All"."Creation Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Revision Number" > 0

UNION ALL

-- 10. Goods Receipt Created
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Receipt Created' AS ActivityName,
  "Receipt"."Creation Date" AS EventTime,
  "Receipt"."Creation Date" AS EndTime,
  "Receipt"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt"."Creation Date" IS NOT NULL

UNION ALL

-- 11. Goods Received
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Received' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RECEIVE'

UNION ALL

-- 12. Quality Inspection Performed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Quality Inspection Performed' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" IN ('ACCEPT', 'REJECT')

UNION ALL

-- 13. Goods Returned to Vendor
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Returned to Vendor' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RETURN TO VENDOR'

UNION ALL

-- 14. Services Delivered Confirmed (Modeled as a receipt against a Service line)
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Services Delivered Confirmed' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RECEIVE' AND "PO Lines All"."Line Type" = 'Fixed Price Services'

UNION ALL

-- 15. Purchase Order Canceled
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Canceled' AS ActivityName,
  "PO Headers All"."Cancel Date" AS EventTime,
  "PO Headers All"."Cancel Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName, -- User who cancelled
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Cancel Flag" = 'Y' AND "PO Headers All"."Cancel Date" IS NOT NULL

UNION ALL

-- 16. Purchase Order Finally Closed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Finally Closed' AS ActivityName,
  "PO Headers All"."Closed Date" AS EventTime,
  "PO Headers All"."Closed Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName, -- User who closed
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Closed Code" = 'FINALLY CLOSED' AND "PO Headers All"."Closed Date" IS NOT NULL

Steps

Access Oracle BI Publisher: Log in to your Oracle Fusion environment. Use the Navigator menu to go to Tools > Reports and Analytics. Click the 'Browse Catalog' button to open the BI Publisher interface.
Create a New Data Model: In the BI Publisher catalog, navigate to a suitable folder (e.g., Shared Folders > Custom). Click the 'New' dropdown and select 'Data Model'.
Define the SQL Query Dataset: In the Data Model editor, click the '+' icon to add a new data set and select 'SQL Query'.
Enter the SQL Query: In the 'New Data Set' dialog, provide a name (e.g., 'ProcessMind_P2P_PO_Extraction'). Select your Oracle Fusion application database as the 'Data Source' (e.g., 'FSCM'). Copy the complete SQL from the 'query' section of this document and paste it into the SQL Query text area.
Define Parameters: The query uses :P_START_DATE and :P_END_DATE as bind variables for filtering. BI Publisher will automatically detect these. You can configure them in the 'Parameters' section of the data model to set default values or create a list of values if desired. For example, set the Data Type to 'Date'.
View and Save the Data: Go to the 'Data' tab. You can enter dates and click 'View' to see a sample of the extracted data. If the data looks correct, save the data model in your custom folder.
Create a Report: Click the 'Create Report' button in the upper right corner. This will launch the report creation wizard.
Configure the Report Layout: In the wizard, select the 'Use Data Model' option. Choose the 'Table' layout. Drag and drop all columns from the data source into the table. You do not need to apply complex formatting, as the goal is a raw data export. Click 'Finish' and save the report with a descriptive name.
Run the Report: Open the newly created report. You will be prompted to enter values for the start and end date parameters. Enter your desired date range and click 'Apply'.
Export the Event Log: Once the report has finished running, click the 'Actions' menu (gear icon) in the upper right and select 'Export', then choose 'CSV'. The file will download to your computer.
Prepare for Upload: Open the CSV file. Verify that the column headers match the required ProcessMind attributes: PurchaseOrder, ActivityName, EventTime, etc. Ensure the date format is consistent. The file is now ready for upload into the ProcessMind application.

Configuration

Data Source: Select the primary Oracle Fusion application database, typically named 'FSCM' or similar, as the data source for the BI Publisher data model.
Date Range Parameters: The query is designed with two mandatory parameters, :P_START_DATE and :P_END_DATE. It is recommended to extract data in batches of 3-6 months to maintain system performance. These parameters filter events based on their occurrence date.
Data Filtering: Additional filters can be added in the WHERE clause of each subquery to narrow the scope. Common filters include PRC_BU_ID (Procurement Business Unit) or POH.TYPE_LOOKUP_CODE (PO Type, e.g., 'STANDARD').
User Authorizations: The user running the report must have a role with explicit read access to all tables used in the query. This includes tables from Procurement (PO_), Receiving (RCV_), Requisitioning (POR_), Supplier (POZ_), and User (PER_) modules. A role like BIAdministrator or a custom BI role is typically required.
Performance: For very large data volumes, the report may take a significant time to run. Schedule the report to run during off-peak hours to minimize the impact on system performance. Consider indexing key columns if performance is a recurring issue.

a Sample Query sql

SELECT
    PRH.REQUISITION_NUMBER AS "PurchaseOrder",
    'Purchase Requisition Created' AS "ActivityName",
    PRH.CREATION_DATE AS "EventTime",
    PRH.CREATION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    HOU.NAME AS "DepartmentName",
    NULL AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    PRL.NEED_BY_DATE AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    POR_REQUISITION_HEADERS_ALL PRH
JOIN
    POR_REQUISITION_LINES_ALL PRL ON PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
LEFT JOIN
    PER_USERS PU ON PRH.CREATED_BY = PU.USERNAME
LEFT JOIN
    HR_ORGANIZATION_UNITS HOU ON PRH.DELIVER_TO_ORGANIZATION_ID = HOU.ORGANIZATION_ID
WHERE
    PRH.CREATION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    PRH.REQUISITION_NUMBER AS "PurchaseOrder",
    'Purchase Requisition Approved' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    PAH.ACTION_DATE AS "EndTime",
    PU_PERFORMER.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    NULL AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    PU_PERFORMER.USERNAME AS "ApproverName"
FROM
    PO_ACTION_HISTORY PAH
JOIN
    POR_REQUISITION_HEADERS_ALL PRH ON PAH.OBJECT_ID = PRH.REQUISITION_HEADER_ID
JOIN
    PER_USERS PU_PERFORMER ON PAH.PERFORMER_ID = PU_PERFORMER.USER_ID
WHERE
    PAH.OBJECT_TYPE_CODE = 'REQUISITION'
    AND PAH.ACTION_CODE = 'APPROVE'
    AND PAH.ACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Purchase Order Created' AS "ActivityName",
    POH.CREATION_DATE AS "EventTime",
    POH.CREATION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    HOU.NAME AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    (SELECT SUM(POL.AMOUNT) FROM PO_LINES_ALL POL WHERE POL.PO_HEADER_ID = POH.PO_HEADER_ID) AS "PurchaseOrderTotalAmount",
    (SELECT MIN(PLL.NEED_BY_DATE) FROM PO_LINE_LOCATIONS_ALL PLL WHERE PLL.PO_HEADER_ID = POH.PO_HEADER_ID) AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    PO_HEADERS_ALL POH
LEFT JOIN
    PER_USERS PU ON POH.CREATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
LEFT JOIN
    PO_DISTRIBUTIONS_ALL PDA ON POH.PO_HEADER_ID = PDA.PO_HEADER_ID
LEFT JOIN
    HR_ORGANIZATION_UNITS HOU ON PDA.PJC_EXPENDITURE_ORGANIZATION_ID = HOU.ORGANIZATION_ID
WHERE
    POH.CREATION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')
    AND ROWNUM = 1

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    CASE PAH.ACTION_CODE
        WHEN 'SUBMIT' THEN 'Purchase Order Submitted'
        WHEN 'APPROVE' THEN 'Purchase Order Approved'
        WHEN 'REJECT' THEN 'Purchase Order Rejected'
        WHEN 'ACCEPT' THEN 'Purchase Order Acknowledged'
        WHEN 'CANCEL' THEN 'Purchase Order Canceled'
        WHEN 'FINALLY CLOSE' THEN 'Purchase Order Finally Closed'
    END AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    PAH.ACTION_DATE AS "EndTime",
    PU_PERFORMER.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    CASE WHEN PAH.ACTION_CODE IN ('APPROVE', 'REJECT') THEN PU_PERFORMER.USERNAME ELSE NULL END AS "ApproverName"
FROM
    PO_ACTION_HISTORY PAH
JOIN
    PO_HEADERS_ALL POH ON PAH.OBJECT_ID = POH.PO_HEADER_ID
LEFT JOIN
    PER_USERS PU_PERFORMER ON PAH.PERFORMER_ID = PU_PERFORMER.USER_ID
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
WHERE
    PAH.OBJECT_TYPE_CODE = 'PO'
    AND PAH.ACTION_CODE IN ('SUBMIT', 'APPROVE', 'REJECT', 'ACCEPT', 'CANCEL', 'FINALLY CLOSE')
    AND PAH.ACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Purchase Order Sent to Vendor' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    PAH.ACTION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    PO_HEADERS_ALL POH
JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
LEFT JOIN
    PER_USERS PU ON POH.SOLDTO_CONTACT_ID = PU.PERSON_ID
JOIN
    (SELECT OBJECT_ID, MIN(ACTION_DATE) AS ACTION_DATE FROM PO_ACTION_HISTORY WHERE OBJECT_TYPE_CODE = 'PO' AND ACTION_CODE = 'APPROVE' GROUP BY OBJECT_ID) PAH ON POH.PO_HEADER_ID = PAH.OBJECT_ID
WHERE
    POH.DOCUMENT_STATUS = 'OPEN'
    AND PAH.ACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Purchase Order Changed' AS "ActivityName",
    POH.LAST_UPDATE_DATE AS "EventTime",
    POH.LAST_UPDATE_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    PO_HEADERS_ALL POH
LEFT JOIN
    PER_USERS PU ON POH.LAST_UPDATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
WHERE
    POH.REVISION_NUM > 0
    AND POH.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Goods Receipt Created' AS "ActivityName",
    RSH.CREATION_DATE AS "EventTime",
    RSH.CREATION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    RCV_SHIPMENT_HEADERS RSH
JOIN
    PO_HEADERS_ALL POH ON RSH.PO_HEADER_ID = POH.PO_HEADER_ID
LEFT JOIN
    PER_USERS PU ON RSH.CREATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON RSH.VENDOR_ID = PS.VENDOR_ID
WHERE
    RSH.CREATION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    CASE RT.TRANSACTION_TYPE
        WHEN 'RECEIVE' THEN 'Goods Received'
        WHEN 'ACCEPT' THEN 'Quality Inspection Performed'
        WHEN 'RETURN TO VENDOR' THEN 'Goods Returned to Vendor'
        WHEN 'DELIVER' THEN 'Services Delivered Confirmed' -- Using DELIVER for services as a proxy
    END AS "ActivityName",
    RT.TRANSACTION_DATE AS "EventTime",
    RT.TRANSACTION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    RCV_TRANSACTIONS RT
JOIN
    PO_HEADERS_ALL POH ON RT.PO_HEADER_ID = POH.PO_HEADER_ID
LEFT JOIN
    PER_USERS PU ON RT.CREATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
WHERE
    RT.TRANSACTION_TYPE IN ('RECEIVE', 'ACCEPT', 'RETURN TO VENDOR', 'DELIVER')
    AND RT.TRANSACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

Steps

Prepare the Python Environment: Ensure you have Python 3.6 or newer installed. Install the necessary requests library by running pip install requests in your command line or terminal. This library will be used to make HTTP requests to the Oracle Fusion REST API.
Configure Security in Oracle Fusion: Create a dedicated integration user in Oracle Identity Cloud Service (IDCS) for this extraction. Assign the user appropriate roles to grant read-access to the required business objects. Key roles include Procurement Manager, Requisition Self Service User, and Warehouse Operator to ensure access to Purchase Orders, Requisitions, and Receiving Receipts APIs respectively. Note the username, password, and your Oracle Fusion instance URL (e.g., https://[instance].fa.em2.oraclecloud.com).
Set Up the Python Script: Copy the provided Python script into a file named extract_p2p_events.py. Open the file and update the configuration section at the top with your Oracle Fusion URL, username, and password. Also, define the START_DATE and END_DATE for the data extraction period.
Understand the Script Logic: The script first authenticates to get a session token. It then makes a primary API call to the purchaseOrders endpoint, filtered by the specified creation date range. This call retrieves all Purchase Orders created within that period, handling pagination automatically.
Iterate and Enrich Data: The script loops through each Purchase Order retrieved. For each PO, it generates the 'Purchase Order Created' event. It then makes subsequent, targeted API calls for each PO to fetch related data for other events.
Fetch Action History: For each PO, the script queries the approvals child resource (.../child/approvals) to extract workflow events like 'Submitted', 'Approved', and 'Rejected'. It uses the ActionCode and ActionDate from the response.
Fetch Requisition Data: The script inspects the PO lines to find the associated RequisitionNumber. It then queries the purchaseRequisitions endpoint to get the creation and approval events for that requisition, linking them to the Purchase Order case ID.
Fetch Receiving Data: The script queries the receivingReceipts endpoint, filtering by the POHeaderId of the current Purchase Order. It processes these transactions to generate events like 'Goods Receipt Created', 'Goods Received', 'Quality Inspection Performed', and 'Goods Returned to Vendor'.
Consolidate and Transform Events: As data is fetched from various endpoints, it is transformed into a standardized, flat structure. Each distinct action becomes a row in a list, representing the event log. Fields from the API responses are mapped to the required attribute names (PurchaseOrder, ActivityName, EventTime, etc.).
Execute the Script: Run the script from your terminal using the command python extract_p2p_events.py. The script will print progress updates to the console and, upon completion, will generate a p2p_event_log.csv file in the same directory.
Prepare for Upload: Open the generated CSV file. Verify that the columns match the required format for ProcessMind: PurchaseOrder, ActivityName, EventTime, and other recommended attributes. Ensure the date format is consistent (ISO 8601 is recommended). The file is now ready for upload.

Configuration

Prerequisites: An Oracle Fusion integration user with roles granting read access to Purchase Orders, Purchase Requisitions, and Receiving objects is required. Python 3.6+ and the requests library must be installed on the machine running the script.
API Endpoints: The script primarily uses the following REST API resources (version 11.13.18.05):
- /fscmRestApi/resources/11.13.18.05/purchaseOrders
- /fscmRestApi/resources/11.13.18.05/purchaseOrders/{id}/child/approvals
- /fscmRestApi/resources/11.13.18.05/purchaseRequisitions
- /fscmRestApi/resources/11.13.18.05/receivingReceipts
Authentication: The script uses Basic Authentication with the integration user's credentials to connect to the Oracle Fusion instance. Session management is handled within the script.
Date Range: The START_DATE and END_DATE variables in the script control the extraction window. It is highly recommended to start with a smaller range, such as one to three months, to manage the extraction time and API load. The filter is applied to the Purchase Order creation date.
Filtering: The script uses the q query parameter to filter records. The primary filter is on CreationDate for Purchase Orders. For optimal performance in large environments, you can add filters on other fields like ProcurementBU (Procurement Business Unit).
Pagination: The script is designed to handle API pagination automatically. It uses the limit and offset parameters and checks the hasMore flag in API responses to ensure all records for the given query are retrieved.
Performance: Be aware that the script makes multiple API calls for each Purchase Order. For an environment with thousands of POs in the selected date range, the extraction can take a significant amount of time. Run the extraction during off-peak hours.

a Sample Query python

import requests
import json
import csv
import os
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Oracle Fusion instance details
FUSION_URL = class="hl-string">"https://[your_instance_url].fa.em2.oraclecloud.com"
USERNAME = os.environ.get(class="hl-string">"FUSION_USERNAME", class="hl-string">"your_username")
PASSWORD = os.environ.get(class="hl-string">"FUSION_PASSWORD", class="hl-string">"your_password")

class=class="hl-string">"hl-comment"># Date range for the extraction (ISO 8601 format)
START_DATE = class="hl-string">"2023-01-01T00:00:00Z"
END_DATE = class="hl-string">"2023-03-31T23:59:59Z"

class=class="hl-string">"hl-comment"># API settings
PAGE_SIZE = 500
OUTPUT_FILE = class="hl-string">"p2p_event_log.csv"

class=class="hl-string">"hl-comment"># --- Helper Functions ---
def get_api_data(endpoint, params=None):
    class="hl-string">"""Generic function to handle pagination and data retrieval from Fusion REST API."""
    all_items = []
    offset = 0
    while True:
        if params is None:
            params = {}
        params[class="hl-string">'limit'] = PAGE_SIZE
        params[class="hl-string">'offset'] = offset

        try:
            response = requests.get(
                FUSION_URL + endpoint,
                auth=(USERNAME, PASSWORD),
                params=params,
                headers={class="hl-string">'Content-Type': class="hl-string">'application/vnd.oracle.adf.resourceitem+json'}
            )
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()
            items = data.get(class="hl-string">'items', [])
            all_items.extend(items)

            if not data.get(class="hl-string">'hasMore', False):
                break
            offset += PAGE_SIZE
        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"HTTP Error fetching {endpoint}: {e}")
            print(fclass="hl-string">"Response content: {e.response.text}")
            break
        except Exception as e:
            print(fclass="hl-string">"An error occurred fetching {endpoint}: {e}")
            break
    return all_items

def add_event(event_log, po_data, activity, timestamp, user, approver=None):
    class="hl-string">"""Helper to format and add an event to the log."""
    if not timestamp:
        return class=class="hl-string">"hl-comment"># Skip events without a timestamp
    
    event = {
        class="hl-string">'PurchaseOrder': po_data.get(class="hl-string">'OrderNumber'),
        class="hl-string">'ActivityName': activity,
        class="hl-string">'EventTime': timestamp,
        class="hl-string">'EndTime': timestamp,
        class="hl-string">'UserName': user,
        class="hl-string">'DepartmentName': po_data.get(class="hl-string">'BillToBU'),
        class="hl-string">'VendorName': po_data.get(class="hl-string">'Supplier'),
        class="hl-string">'PurchaseOrderTotalAmount': po_data.get(class="hl-string">'Total'),
        class="hl-string">'RequestedDeliveryDate': None, class=class="hl-string">"hl-comment"># Often on the line level
        class="hl-string">'ApproverName': approver
    }
    event_log.append(event)

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    event_log = []
    print(class="hl-string">"Starting P2P event log extraction...")

    class=class="hl-string">"hl-comment"># 1. Get Purchase Orders within the date range
    print(fclass="hl-string">"Fetching Purchase Orders created between {START_DATE} and {END_DATE}...")
    po_query = fclass="hl-string">"CreationDate between '{START_DATE}' and '{END_DATE}'"
    purchase_orders = get_api_data(class="hl-string">"/fscmRestApi/resources/11.13.18.05/purchaseOrders", {class="hl-string">'q': po_query, class="hl-string">'fields': class="hl-string">'OrderNumber,CreationDate,POHeaderId,CreatedBy,Supplier,Total,POStatus,LastUpdateDate,BillToBU,VersionNumber,ChangeOrderDescription,lines'})
    print(fclass="hl-string">"Found {len(purchase_orders)} Purchase Orders.")

    unique_requisition_nums = set()
    for po in purchase_orders:
        if po.get(class="hl-string">'lines'):
            for line in po[class="hl-string">'lines']:
                if line.get(class="hl-string">'RequisitionNumber'):
                    unique_requisition_nums.add(line[class="hl-string">'RequisitionNumber'])

    class=class="hl-string">"hl-comment"># 2. Fetch Requisition data in a batch for efficiency
    print(fclass="hl-string">"Fetching data for {len(unique_requisition_nums)} related requisitions...")
    requisitions_data = {}
    if unique_requisition_nums:
        class=class="hl-string">"hl-comment"># API has a limit on URL length, so we may need to batch this for very large sets
        req_query = class="hl-string">" or ".join([fclass="hl-string">"RequisitionNumber='{req_num}'" for req_num in unique_requisition_nums])
        reqs = get_api_data(class="hl-string">"/fscmRestApi/resources/11.13.18.05/purchaseRequisitions", {class="hl-string">'q': req_query})
        for req in reqs:
            requisitions_data[req[class="hl-string">'RequisitionNumber']] = req

    class=class="hl-string">"hl-comment"># 3. Process each Purchase Order to create events
    for i, po in enumerate(purchase_orders):
        print(fclass="hl-string">"Processing PO {i+1}/{len(purchase_orders)}: {po.get('OrderNumber')}")

        class=class="hl-string">"hl-comment"># PO Events
        add_event(event_log, po, class="hl-string">'Purchase Order Created', po.get(class="hl-string">'CreationDate'), po.get(class="hl-string">'CreatedBy'))
        if po.get(class="hl-string">'VersionNumber', 0) > 1:
            add_event(event_log, po, class="hl-string">'Purchase Order Changed', po.get(class="hl-string">'LastUpdateDate'), po.get(class="hl-string">'LastUpdatedBy', po.get(class="hl-string">'CreatedBy')))
        if po.get(class="hl-string">'POStatus') == class="hl-string">'CANCELED':
            add_event(event_log, po, class="hl-string">'Purchase Order Canceled', po.get(class="hl-string">'LastUpdateDate'), po.get(class="hl-string">'LastUpdatedBy', po.get(class="hl-string">'CreatedBy')))
        if po.get(class="hl-string">'POStatus') == class="hl-string">'CLOSED_FOR_RECEIVING' or po.get(class="hl-string">'POStatus') == class="hl-string">'CLOSED':
            add_event(event_log, po, class="hl-string">'Purchase Order Finally Closed', po.get(class="hl-string">'LastUpdateDate'), po.get(class="hl-string">'LastUpdatedBy', po.get(class="hl-string">'CreatedBy')))

        class=class="hl-string">"hl-comment"># Requisition Events (linked to this PO)
        po_req_nums = set()
        if po.get(class="hl-string">'lines'):
            for line in po[class="hl-string">'lines']:
                 if line.get(class="hl-string">'RequisitionNumber'):
                    po_req_nums.add(line[class="hl-string">'RequisitionNumber'])
        
        for req_num in po_req_nums:
            req = requisitions_data.get(req_num)
            if req:
                add_event(event_log, po, class="hl-string">'Purchase Requisition Created', req.get(class="hl-string">'CreationDate'), req.get(class="hl-string">'EnteredBy'))
                if req.get(class="hl-string">'RequisitionStatus') == class="hl-string">'APPROVED':
                     class=class="hl-string">"hl-comment"># Note: This timestamp is the final approval, not an event stream
                     add_event(event_log, po, class="hl-string">'Purchase Requisition Approved', req.get(class="hl-string">'LastUpdateDate'), req.get(class="hl-string">'LastUpdatedBy'))

        class=class="hl-string">"hl-comment"># Approval History Events
        approvals = get_api_data(fclass="hl-string">"/fscmRestApi/resources/11.13.18.05/purchaseOrders/{po['POHeaderId']}/child/approvals")
        is_first_approval = True
        for action in approvals:
            activity = None
            user = action.get(class="hl-string">'PerformedBy')
            approver = user if action.get(class="hl-string">'ActionCode') in [class="hl-string">'APPROVE', class="hl-string">'REJECT'] else None
            if action.get(class="hl-string">'ActionCode') == class="hl-string">'SUBMIT':
                activity = class="hl-string">'Purchase Order Submitted'
            elif action.get(class="hl-string">'ActionCode') == class="hl-string">'APPROVE':
                activity = class="hl-string">'Purchase Order Approved'
                if is_first_approval:
                    add_event(event_log, po, class="hl-string">'Purchase Order Sent to Vendor', action.get(class="hl-string">'ActionDate'), user, approver)
                    is_first_approval = False
            elif action.get(class="hl-string">'ActionCode') == class="hl-string">'REJECT':
                activity = class="hl-string">'Purchase Order Rejected'
            
            if activity:
                add_event(event_log, po, activity, action.get(class="hl-string">'ActionDate'), user, approver)

        class=class="hl-string">"hl-comment"># Receiving Events
        receipts_query = fclass="hl-string">"POHeaderId={po['POHeaderId']}"
        receipts = get_api_data(class="hl-string">"/fscmRestApi/resources/11.13.18.05/receivingReceipts", {class="hl-string">'q': receipts_query})
        for receipt in receipts:
            add_event(event_log, po, class="hl-string">'Goods Receipt Created', receipt.get(class="hl-string">'CreationDate'), receipt.get(class="hl-string">'CreatedBy'))
            class=class="hl-string">"hl-comment"># Lines contain the detailed transactions
            if receipt.get(class="hl-string">'lines'):
                for rcv_line in receipt[class="hl-string">'lines']:
                    tx_type = rcv_line.get(class="hl-string">'TransactionType')
                    tx_time = rcv_line.get(class="hl-string">'TransactionDate')
                    tx_user = receipt.get(class="hl-string">'CreatedBy') class=class="hl-string">"hl-comment"># User is on header
                    activity = None
                    if tx_type == class="hl-string">'RECEIVE':
                        activity = class="hl-string">'Goods Received'
                    elif tx_type == class="hl-string">'INSPECT':
                        activity = class="hl-string">'Quality Inspection Performed'
                    elif tx_type == class="hl-string">'RETURN TO VENDOR':
                        activity = class="hl-string">'Goods Returned to Vendor'
                    elif tx_type == class="hl-string">'MATCH' and po.get(class="hl-string">'OrderTypeCode') == class="hl-string">'SERVICES': class=class="hl-string">"hl-comment"># Approximation for services
                        activity = class="hl-string">'Services Delivered Confirmed'
                    
                    if activity:
                        add_event(event_log, po, activity, tx_time, tx_user)

    class=class="hl-string">"hl-comment"># 4. Write to CSV
    if not event_log:
        print(class="hl-string">"No events generated. Exiting.")
        return

    print(fclass="hl-string">"Writing {len(event_log)} events to {OUTPUT_FILE}...")
    class=class="hl-string">"hl-comment"># Sort events by case ID and timestamp
    event_log.sort(key=lambda x: (x[class="hl-string">'PurchaseOrder'], x[class="hl-string">'EventTime']))

    with open(OUTPUT_FILE, class="hl-string">'w', newline=class="hl-string">'', encoding=class="hl-string">'utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=event_log[0].keys())
        writer.writeheader()
        writer.writerows(event_log)

    print(class="hl-string">"Extraction complete.")

if __name__ == class="hl-string">"__main__":
    main()

Data Template: Purchase to Pay - Purchase Order

Your Purchase to Pay - Purchase Order Data Template

Purchase to Pay - Purchase Order Attributes

Purchase to Pay - Purchase Order Activities

Extraction Guides

Oracle OTBI Report for Purchase Order Data Export

Steps

Configuration

a Sample Query config

Oracle BI Cloud Connector (BICC) Bulk Data Export

Steps

Configuration

a Sample Query config

Oracle BI Publisher SQL Query for PO Data Extraction

Steps

Configuration

a Sample Query sql

Python Script Extraction via Oracle Fusion REST APIs

Steps

Configuration

a Sample Query python

Ready to Get Started?

Optimize Your Purchase to Pay - Purchase Order Today