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

Your Purchase to Pay - Requisition Data Template

Oracle Fusion Financials

Purchase to Pay - Requisition Attributes (22) Purchase to Pay - Requisition Activities (12) Extraction Guides (4)

Your Purchase to Pay - Requisition Data Template

This template provides a clear roadmap for collecting the essential data needed to analyze your Purchase to Pay - Requisition process. It outlines the key attributes to gather, the critical activities to track, and practical guidance on how to extract this information from Oracle Fusion Financials. With this, you can effectively prepare your event log for insightful process mining.

Recommended attributes to collect
Key activities to track
Extraction guidance for Oracle Fusion Financials

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

Download Template

Purchase to Pay - Requisition Attributes

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

5 Required 7 Recommended 10 Optional

	Name	Description
	Activity ActivityName	The name of the business event that occurred at a specific point in the requisition process.
Description The Activity represents a distinct step or milestone in the purchase requisition lifecycle. Examples include 'Requisition Created', 'Approval Step Approved', or 'Purchase Order Created'. These activities are derived from status changes, user actions, or system events recorded in the source system's audit logs or transaction tables. This attribute is essential for constructing the process map, which visually represents the flow of requisitions. Analyzing the sequence and frequency of activities helps identify common process paths, bottlenecks, rework loops, and deviations from the standard procedure. Why it matters It forms the backbone of the process map, allowing for the visualization and analysis of the requisition workflow. Where to get Derived from status change records in tables like POR_REQUISITION_HEADERS_ALL, transaction history, or workflow audit trails like FA_FUSION_SOAINFRA.WFTASK. Examples Requisition CreatedApproval Step ApprovedRequisition RejectedPurchase Order Created
	Event Time EventTime	The timestamp indicating when the activity occurred.
Description The Event Time records the precise date and time that a specific activity took place. This timestamp is fundamental for chronological ordering of events within a case and is sourced from creation dates, last update dates, or specific action timestamps in the system. In analysis, Event Time is used to calculate all duration-based metrics, such as cycle times between activities, waiting times, and overall case duration. It is critical for identifying bottlenecks, measuring performance against SLAs, and understanding the temporal dynamics of the requisition process. Why it matters This attribute is essential for calculating all time-related KPIs, ordering events correctly, and analyzing process performance and bottlenecks. Where to get This is typically sourced from a 'LAST_UPDATE_DATE' or 'CREATION_DATE' column associated with the transaction or status change, often found in tables like POR_REQUISITION_HEADERS_ALL or workflow history tables. Examples 2023-04-15T10:30:00Z2023-04-15T11:05:21Z2023-04-16T09:00:15Z
	Purchase Requisition ID PurchaseRequisitionId	The unique identifier for a purchase requisition, serving as the case ID for the process.
Description The Purchase Requisition ID is the central identifier that links all activities related to a specific request for goods or services. Each requisition is assigned a unique ID upon creation, which remains constant throughout its lifecycle. In process mining, this attribute is used to group all related events, such as creation, submission, approval steps, and final closure, into a single case. This allows for the end-to-end analysis of the requisition's journey, making it possible to visualize process maps, calculate cycle times, and analyze variants for each individual request. Why it matters This is the fundamental attribute for tracking a requisition's lifecycle from beginning to end, enabling all case-level analysis and KPI calculations. Where to get This is typically the primary key in the requisition header table, such as POR_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID in Oracle Fusion Financials. Examples 100234810023491002350
	Last Data Update LastDataUpdate	The timestamp of the most recent data refresh from the source system.
Description This attribute indicates the date and time when the data was last extracted from Oracle Fusion Financials. It applies to the entire dataset rather than individual events. Analysts use this information to understand the freshness of the data and to confirm when the latest transactions were included. It is a key piece of metadata for dashboard reporting and ensuring analyses are based on up-to-date information. Why it matters Informs users about the recency of the data, ensuring analyses are relevant and based on the latest available information. Where to get This timestamp is generated and stored during the data extraction process, typically by the ETL tool or data pipeline. Examples 2023-10-27T02:00:00Z
	Source System SourceSystem	The information system from which this data was extracted.
Description This attribute identifies the origin of the process data. For this data model, it will consistently be 'Oracle Fusion Financials'. In environments with multiple ERPs or integrated systems, this field is crucial for data lineage, troubleshooting, and ensuring data quality. It provides context about the source of truth for the process events being analyzed. Why it matters Provides essential context about data origin, which is crucial for data governance and when integrating data from multiple systems. Where to get This is a static value added during the data extraction and transformation process to label the dataset's origin. Examples Oracle Fusion Financials
	Business Unit BusinessUnit	The specific business unit within the organization that the requisition belongs to.
Description The Business Unit represents a distinct legal or functional entity within the company for which the requisition is being made. It is a higher-level organizational grouping than a department. Analyzing data by Business Unit allows for high-level performance comparisons across different parts of the organization. This helps senior management understand if process inefficiencies are localized or widespread and where to focus improvement efforts. It's a key dimension for filtering nearly all dashboards and KPIs. Why it matters Provides a high-level organizational context, enabling performance comparison and strategic analysis across different parts of the enterprise. Where to get This is a fundamental organizational field in Oracle Fusion, typically available on the requisition header in tables like POR_REQUISITION_HEADERS_ALL. Examples BU North AmericaBU EuropeCorporate Headquarters
	Department DepartmentName	The business department to which the requester belongs.
Description This attribute indicates the organizational unit of the person who created the requisition, such as 'Finance', 'IT', or 'Marketing'. It is typically derived from the requester's user profile in the HR system. Analyzing by department is a common and powerful way to segment process data. It helps identify department-specific behaviors, such as higher rejection rates or longer cycle times, which can inform targeted process improvement initiatives. This is a key dimension for the 'Requester Performance Metrics' dashboard. Why it matters Allows for process analysis segmented by business unit, revealing department-specific patterns, performance, and compliance issues. Where to get Typically derived from the requester's profile, often requiring a join from the requisition table to an HR or user directory table that contains department information. Examples Information TechnologyFinanceOperationsMarketing
	Rejection Reason RejectionReason	The reason provided by an approver when a requisition or approval step is rejected.
Description When a requisition is rejected, the approver usually provides a reason, either by selecting from a predefined list or entering free text. This attribute captures that justification. This is a critical attribute for root cause analysis of process failures. It directly supports the 'Amendment and Rejection Trends' dashboard by providing the 'why' behind rejections. Analyzing rejection reasons helps identify common issues, such as incorrect coding, budget overruns, or policy violations, which can then be addressed through training or system controls. Why it matters Provides direct insight into why requisitions are rejected, enabling targeted improvements to reduce rework and increase the straight-through processing rate. Where to get Sourced from workflow comments or specific rejection reason code fields in the workflow audit trail, potentially within tables related to FA_FUSION_SOAINFRA.WFTASK or associated comment storage. Examples Incorrect GL AccountExceeds budget for cost centerNon-preferred supplier selectedDuplicate Request
	Requester Name RequesterName	The name of the employee who created and submitted the purchase requisition.
Description This attribute identifies the individual who initiated the request for goods or services. This information is typically captured at the beginning of the process when the requisition is first created. Analyzing process performance by Requester is critical for the 'Requester Performance Metrics' dashboard. It helps identify which users or groups may require additional training by highlighting high rates of amendments, rejections, or long cycle times associated with their requests. It provides a human-centric view of the process. Why it matters Enables performance analysis by requester, helping to identify training needs and highlight efficient users or departments. Where to get Sourced from the requisition header data, often by joining the requester's ID with an employee or user master data table. Look for fields related to 'PREPARER_ID' in POR_REQUISITION_HEADERS_ALL and join to PER_ALL_PEOPLE_F. Examples John SmithJane DoeEmily Jones
	Required By Date RequiredByDate	The date by which the requester needs the goods or services.
Description This date is specified by the requester to indicate the deadline for receiving the requested items. It serves as an internal service level agreement (SLA) target for the procurement process. This attribute is the foundation for the 'Required By Date Performance' dashboard and the 'Required-By Date Adherence Rate' KPI. By comparing this date with the actual Purchase Order creation date or goods receipt date, the analysis can reveal how well the procurement process is meeting internal customer demands and identify systemic delays. Why it matters Crucial for measuring process performance against internal deadlines and understanding if the procurement process meets business needs in a timely manner. Where to get Usually stored at the requisition line item level, in tables like POR_REQUISITION_LINES_ALL in a field such as 'NEED_BY_DATE'. Examples 2023-11-012023-12-152024-01-31
	Requisition Status RequisitionStatus	The current or final status of the purchase requisition.
Description This attribute indicates the overall state of the requisition at a given point in time or its final outcome, such as 'Approved', 'Rejected', 'In Process', or 'Closed'. This is often the source from which many activities in the event log are derived. This attribute is key for the 'Requisition Status Overview' dashboard, providing a snapshot of the current workload and backlog. It is also used to calculate outcome-based KPIs, like the Requisition Rejection Rate, by filtering for cases that end in a specific status. Why it matters Provides a snapshot of the current state of requisitions and is used to determine final outcomes for KPI calculations. Where to get Found in the requisition header table, typically in a field like 'DOCUMENT_STATUS' or 'APPROVAL_STATUS' in POR_REQUISITION_HEADERS_ALL. Examples APPROVEDIN PROCESSREJECTEDWITHDRAWN
	Requisition Total Amount RequisitionTotalAmount	The total monetary value of the purchase requisition.
Description This attribute represents the sum of the value of all line items on a single purchase requisition. It is a critical data point for understanding the financial significance of each request. In process mining, the total amount is used for a wide range of analyses. It can be used to filter for high-value requisitions, which often have different approval paths or higher scrutiny. Dashboards can use this attribute to analyze how process metrics, like cycle time or rejection rate, correlate with the requisition's value. Why it matters Provides financial context, enabling value-based analysis to prioritize process improvements and understand how requisition value impacts process behavior. Where to get Located on the requisition header, often in a field like REQUISITION_TOTAL in POR_REQUISITION_HEADERS_ALL. It can also be calculated by summing line item amounts from POR_REQUISITION_LINES_ALL. Examples 550.0012500.7599.99
	Approval Workflow Path ApprovalWorkflowPath	The predefined sequence of approvers or approval groups required for the requisition.
Description This attribute defines the expected, standard approval process for a given requisition based on company policies, considering factors like requisition amount, type, and department. It represents the 'to-be' process model. The Approval Workflow Path is fundamental for compliance and conformance analysis. It directly supports the 'Compliance and Deviation Analysis' dashboard and the 'Requisition Conformance Index' KPI by allowing a direct comparison of the actual approval steps taken against the prescribed path. Deviations may indicate policy violations or process inefficiencies. Why it matters Enables conformance checking by comparing the actual process flow against the required approval hierarchy, highlighting non-compliant requisitions. Where to get This information is configured in the Oracle Fusion BPM Worklist or Approval Management Engine (AMX). Extracting the defined path for each requisition can be complex and may require querying configuration tables. Examples Manager > Director > VP FinanceCost Center Owner > IT SecurityManager > Department Head
	Currency CurrencyCode	The currency code for the requisition amount, such as USD or EUR.
Description This attribute specifies the currency in which the Requisition Total Amount is denominated. For global organizations, requisitions may be created in various currencies. It is essential for correctly interpreting and aggregating financial data. In any analysis involving monetary values, the currency code must be used to ensure amounts are compared accurately, either by filtering for a single currency or by converting all amounts to a common currency. Why it matters Ensures accurate financial analysis and reporting, especially in multinational organizations dealing with multiple currencies. Where to get Typically found on the requisition header table alongside the amount fields, for example, in POR_REQUISITION_HEADERS_ALL. Examples USDEURGBPJPY
	Is Amended IsAmendedFlag	A boolean flag that is true if the requisition was amended at least once.
Description This calculated attribute indicates whether a requisition has undergone any changes after its initial submission. It is derived by checking for the presence of a 'Requisition Amended' activity in the case's history. This flag simplifies analysis and KPI calculation. It is used directly to calculate the 'Requisition Amendment Rate' KPI and to identify cases that are not straight-through. It allows for easy filtering and comparison of process metrics between amended and non-amended requisitions. Why it matters Simplifies the calculation of the amendment rate and allows for easy comparison of amended vs. non-amended requisitions. Where to get This attribute is not in the source system but is calculated during data transformation based on the presence of amendment-related activities in the event log. Examples truefalse
	Is Automated IsAutomated	A flag indicating if an activity was performed automatically by the system.
Description This attribute identifies events in the process that were executed by a system user or an automated agent rather than a human. Examples could include system-driven status changes or automated approval steps for low-value items. Analyzing this attribute helps quantify the level of automation in the process. It can be used to compare the speed and efficiency of automated steps versus manual ones and to identify opportunities for further automation. Why it matters Helps measure the level of automation in the process and identify opportunities to automate manual tasks. Where to get Derived by checking if the user associated with an activity is a system or service account. This requires a list of known system user IDs. Examples truefalse
	Is Straight Through IsStraightThrough	A flag indicating if the requisition was approved without any amendments or rejections.
Description This calculated flag identifies requisitions that have passed through the process from submission to approval without any rework loops, such as amendments or rejections. It signifies a perfectly executed process for a single case. This attribute is the basis for the 'Straight-Through Requisition Rate' KPI. Analyzing the characteristics of straight-through requisitions (e.g., common departments, requesters, or types) can reveal best practices and opportunities for automation. Conversely, analyzing those that are not straight-through helps pinpoint the primary drivers of inefficiency. Why it matters Directly measures process efficiency and is the basis for the Straight-Through Requisition Rate KPI, helping to identify drivers of rework. Where to get This attribute is calculated during data transformation. A case is flagged as true if no 'Requisition Amended' or 'Approval Step Rejected' activities are present. Examples truefalse
	Item Description ItemDescription	The description of the product or service being requested on a requisition line.
Description This attribute contains the textual description of the item being procured. It provides specific details about the goods or services requested. While often unstructured, the Item Description provides valuable context for analysis. It can be used in filters to isolate requisitions for specific types of purchases that may not be captured by the Requisition Type. For example, an analyst could search for all requisitions containing 'Software License' to understand their specific process flow and cycle time. Why it matters Offers detailed context about what is being purchased, allowing for more granular filtering and analysis of specific goods or services. Where to get Located on the requisition line item table, POR_REQUISITION_LINES_ALL, in a field like ITEM_DESCRIPTION. Examples 15 inch Laptop, 16GB RAMConsulting Services - Q4 ProjectAnnual Software Maintenance Renewal
	Purchase Order Number PurchaseOrderNumber	The identifier of the purchase order created from the approved requisition.
Description This attribute links a purchase requisition to the resulting purchase order. Once a requisition is fully approved, it is typically converted into one or more purchase orders to be sent to a supplier. In analysis, this ID is essential for tracking the process downstream from the requisition. It enables the calculation of the 'Requisition-to-PO Lead Time' KPI and supports the 'Requisition to PO Cycle Time' dashboard. It also allows for combining the requisition process data with the subsequent PO and invoicing processes for a true end-to-end Purchase-to-Pay analysis. Why it matters Links the requisition to the subsequent purchase order, enabling measurement of the requisition-to-PO cycle time and end-to-end process analysis. Where to get This information is stored once a PO is created. It is typically found by looking at the backing requisition references in the PO distribution tables, like PO_DISTRIBUTIONS_ALL, which links back to the requisition line. Examples PO-2023-5832PO-2023-5833PO-2023-5834
	Requisition Type RequisitionType	The category of the requisition, such as a request for goods or services.
Description This attribute classifies the requisition based on what is being requested. Common types include goods, services, or capital expenditures. The type can influence the required approval workflow and procurement strategy. In analysis, Requisition Type serves as a powerful dimension for filtering and comparison. For example, one could analyze if service requisitions have a longer approval cycle time than goods requisitions. It helps in understanding if different types of requests exhibit different process behaviors or bottlenecks. Why it matters Allows for segmenting the analysis to understand how the process differs for various types of purchases, such as goods versus services. Where to get This is often determined by the line item type or category selected during requisition creation. It may be stored on the requisition line table, POR_REQUISITION_LINES_ALL. Examples GoodsServicesCapital Expenditure
	Supplier Name SupplierName	The name of the suggested or pre-selected supplier for the goods or services.
Description This attribute identifies the vendor from whom the goods or services are intended to be purchased. The supplier may be suggested by the requester or determined by the system based on catalogs or prior agreements. Analyzing by supplier can reveal important procurement patterns. For example, it can help identify if requisitions for certain suppliers take longer to get approved or have higher rejection rates. This information can be valuable for supplier relationship management and procurement strategy. Why it matters Enables analysis of process performance by supplier, which can help in sourcing strategy and supplier relationship management. Where to get Located on the requisition line item table, POR_REQUISITION_LINES_ALL, often linked via a VENDOR_ID to a supplier master table like POZ_SUPPLIERS. Examples Office Supplies Inc.Global Tech SolutionsCreative Marketing Agency
	User Name UserName	The name of the user who performed a specific activity, such as an approver or an editor.
Description While Requester Name identifies the initiator, User Name specifies the individual who executed a particular event in the process, like an approval or rejection. This is especially important for multi-step approval workflows where different individuals are involved. This attribute is crucial for analyzing approval bottlenecks and measuring the performance of specific approvers or teams. It directly supports the 'Approval Workflow Bottlenecks' dashboard by allowing analysis of processing times for each user involved in the approval chain. Why it matters Identifies the actor for each event, which is vital for analyzing handover times, approver performance, and resource allocation. Where to get Sourced from workflow history or audit trail tables, such as FA_FUSION_SOAINFRA.WFTASK, which logs the user associated with each task completion. Examples David LeeSusan ChenMichael Brown

Required Recommended Optional

Purchase to Pay - Requisition Activities

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

6 Recommended 6 Optional

	Activity	Description
	Purchase Order Created	This event occurs when an approved requisition line is used to generate a purchase order. It links the requisition process to the downstream procurement process.
Why it matters This is a critical milestone for measuring the Requisition-to-PO Lead Time. Delays here indicate bottlenecks in the handoff from approval to purchasing. Where to get This is an explicit event. The link between the requisition and the purchase order is stored in tables like PO_LINE_LOCATIONS_ALL, which contains a reference to the source requisition line ID. Capture Find the creation date of the Purchase Order that references the given Requisition ID. Event type explicit
	Requisition Approved	Marks the final approval of the purchase requisition after it has successfully passed all steps in the workflow. This is inferred from the overall status of the requisition changing to 'Approved'.
Why it matters This is a key milestone indicating the request is ready for procurement action. It is the end point for measuring the total requisition approval cycle time. Where to get Inferred from the document status field in the POR_REQUISITION_HEADERS_ALL table changing to 'APPROVED'. The date of this status change is the event time. Capture Identify the timestamp when the document status is first set to 'Approved'. Event type inferred
	Requisition Closed	Indicates the final closure of a requisition's lifecycle, meaning all its lines have been fulfilled (e.g., converted to POs) or cancelled. This is inferred from a final status update.
Why it matters This is the primary successful end event for the process. It confirms that the requisition has been fully processed and no further action is required. Where to get Inferred from the requisition header status in POR_REQUISITION_HEADERS_ALL changing to 'CLOSED'. Capture Identify the timestamp when the requisition's document status changes to 'Closed'. Event type inferred
	Requisition Created	Marks the initiation of the procurement process when a user saves a new purchase requisition for the first time. This event is typically captured as an explicit record creation with a corresponding timestamp in the system.
Why it matters This is the primary start event for the requisition process. Analyzing the time from creation to submission can reveal delays in request formalization. Where to get This event is recorded in the POR_REQUISITION_HEADERS_ALL table, captured from the creation_date column when a new Requisition ID is generated. Capture Use the creation timestamp for the requisition header record. Event type explicit
	Requisition Rejected	Represents the final rejection of the requisition, terminating the process for this request. This is inferred when the requisition's overall status is updated to 'Rejected'.
Why it matters This activity is a terminal endpoint for unsuccessful requests. Analyzing these cases is essential for understanding the Requisition Rejection Rate KPI and reasons for failure. Where to get Inferred from the document status in the POR_REQUISITION_HEADERS_ALL table changing to 'REJECTED'. Capture Identify the timestamp when the document status is first set to 'Rejected'. Event type inferred
	Requisition Submitted	Represents the user action of submitting the completed requisition into the approval workflow. This is captured when the requisition status changes from 'Incomplete' or 'Draft' to a status indicating it is pending approval.
Why it matters This activity triggers the approval cycle. It is a critical milestone for measuring the Requisition Approval Cycle Time and overall lead times. Where to get Inferred from a status change in the POR_REQUISITION_HEADERS_ALL table (e.g., status moves to 'PENDING APPROVAL'). The submission date is often explicitly stored as well. Capture Identify the timestamp when the document status field first changes to 'Pending Approval'. Event type inferred
	Approval Step Approved	Represents an individual approver's action of approving the requisition at their designated step in the workflow. The event is explicitly logged in the approval history.
Why it matters Tracking individual approval steps helps map the actual approval path and measure the processing time at each stage of the hierarchy. Where to get Captured from the requisition's approval action history, which is typically stored in workflow (WF) or Human Capital Management (HCM) tables that manage approval hierarchies. Capture Use the timestamp of the 'APPROVE' action from the workflow action history log. Event type explicit
	Approval Step Rejected	An individual approver rejects the requisition, which typically sends it back to the preparer for correction or terminates the request. This action is explicitly recorded in the workflow history.
Why it matters This activity is a primary driver of rework and delays. Analyzing rejections helps identify compliance issues, budget problems, or unclear justifications. Where to get Captured from the requisition's approval action history. The workflow system logs a 'REJECT' action with a timestamp. Capture Use the timestamp of the 'REJECT' action from the workflow action history log. Event type explicit
	Approval Step Returned	An approver returns the requisition to the preparer for additional information or minor corrections, without formally rejecting it. This is typically an explicit action in the workflow system.
Why it matters This indicates a need for clarification and creates a rework loop that extends the cycle time. Differentiating returns from rejections provides deeper insight into process friction. Where to get Captured from the requisition's approval action history. The workflow system logs a 'RETURN' or similar action with a timestamp. Capture Use the timestamp of the 'RETURN' or 'Request for Information' action from the workflow history. Event type explicit
	Approval Step Started	Marks the moment a requisition is assigned to a specific approver or approval group within the workflow. This is captured from the workflow engine's transaction log.
Why it matters This activity is crucial for calculating the waiting time for each approval step. It helps pinpoint bottlenecks caused by specific approvers or approval levels. Where to get Retrieved from Oracle Fusion's workflow tables, which log tasks assigned to users. The assignment timestamp for the approval task is used. Capture Use the creation timestamp of the task in the workflow history for the given requisition. Event type explicit
	Requisition Amended	This event signifies that a user has modified a requisition after its initial submission, often requiring the approval process to restart. This is inferred by detecting changes to key data fields or the creation of a new version of the requisition.
Why it matters Frequent amendments indicate issues with data quality or changing requirements, leading to rework and process delays. This directly supports the 'Requisition Amendment Rate' KPI. Where to get Inferred by tracking version numbers of the requisition or by identifying status changes back to 'Incomplete' after submission. Change logs or audit trail tables may also capture these modifications. Capture Identify new version creation timestamps for the same Requisition ID after it has been submitted. Event type inferred
	Requisition Withdrawn	Occurs when the requester cancels or withdraws a submitted requisition before it has been fully approved. This is usually an explicit user action resulting in a status change.
Why it matters Tracking withdrawals helps identify reasons for premature termination, such as changed business needs or users correcting errors after submission. Where to get Inferred from a status change to 'WITHDRAWN' in the POR_REQUISITION_HEADERS_ALL table. The action is logged in the requisition's action history. Capture Detect the timestamp when the requisition's status is updated to 'Withdrawn'. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Oracle Fusion Financials

Steps

Navigate to Reports and Analytics: Log in to Oracle Fusion Financials. Use the Navigator menu to go to Tools, then select Reports and Analytics. This is the central hub for all reporting activities.
Create a New Analysis: Within the Reports and Analytics work area, click the 'Create' button and select 'Analysis' from the dropdown menu. This will launch the Oracle Transactional Business Intelligence (OTBI) analysis editor in a new tab.
Select the Subject Area: You will be prompted to select a Subject Area. In the search box, type 'Procurement - Requisitions Real Time' and select it from the list. This subject area contains all the necessary data points for requisition lifecycle events.
Build the Analysis for Requisition Events: This step requires creating a union of several queries to capture each distinct event. Since the OTBI UI does not have a direct UNION ALL feature for different event types, you will create a single report pulling all relevant date and status fields. In the 'Criteria' tab, drag and drop the columns listed in the query section from the 'Subject Areas' pane on the left into the 'Selected Columns' area. This includes columns for creation date, submission date, approval date, PO creation date, etc.
Add Requisition Action History: To capture detailed approval steps (Approved, Rejected, Returned, Withdrawn), you need to include fields from the 'Requisition Action History' folder within the subject area. Drag 'Action Date', 'Action', and 'Performer' into your selected columns.
Configure Filters: Switch to the 'Prompts' tab to create filters for your analysis. It is essential to filter by a date range to manage data volume. Create a filter on 'Requisition Header'.'Requisition Creation Date' and set it to a range, for example, the last 6 months. You can also add prompts for 'Business Unit' or 'Requester Name' to narrow down the data.
Execute and Review the Report: Go to the 'Results' tab to execute the analysis and preview the data. The output will be a 'wide' table with one row per requisition line or approval action, containing multiple date and status columns.
Export the Raw Data: Once you are satisfied with the preview, click the 'Export' link, typically located at the bottom of the results page. Choose 'Data' > 'CSV File' as the format. This will download the raw data from your analysis.
Transform Data into Event Log Format: The exported CSV will be in a wide format. You must use an external tool (like a Python script, Power Query in Excel, or another data preparation tool) to transform this data. The process involves 'unpivoting' the date columns into a single 'EventTime' column and creating a corresponding 'ActivityName' column for each event type (e.g., 'Requisition Created', 'Requisition Submitted').
Finalize and Prepare for Upload: After transformation, ensure the file has the required columns: PurchaseRequisitionId, ActivityName, EventTime, SourceSystem, and LastDataUpdate. Save the final event log as a CSV file, which can then be uploaded to ProcessMind.

Configuration

Subject Area: The primary subject area to use is 'Procurement - Requisitions Real Time'. This contains all core requisition header, line, distribution, and approval history information.
Date Range: Always apply a mandatory date filter to limit the data volume and improve performance. A filter on 'Requisition Header'.'Requisition Creation Date' for the last 3 to 6 months is a good starting point.
Key Filters: Consider adding filters for 'Requisition Header'.'Business Unit' and 'Requisition Header'.'Requisition Status' to focus the extraction on relevant organizational units and exclude irrelevant statuses like 'Canceled' if they are not needed for the analysis.
Column Selection: Be selective with the columns you add to the analysis. Including too many columns, especially from many different folders (dimensions), can negatively impact performance. Focus only on the fields required for the event log.
Permissions: The user running the extraction must have a role with the necessary permissions to access the Reports and Analytics work area and the 'Procurement - Requisitions Real Time' subject area. Typically, roles like 'BI Author' or 'Procurement Analyst' have these privileges.

a Sample Query config

/*
OTBI analyses are built visually, not with SQL. This conceptual query illustrates the logic required to extract the data for all specified activities. The user would build an analysis containing these fields and then transform the output into an event log.
*/

-- 1. Requisition Created
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Created' AS ActivityName,
  "Requisition Header"."Requisition Creation Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Header"."Requisition Creation Date" IS NOT NULL

UNION ALL

-- 2. Requisition Submitted
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Submitted' AS ActivityName,
  "Requisition Header"."Submitted Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Header"."Submitted Date" IS NOT NULL

UNION ALL

-- 3, 5, 6, 7, 8. Approval History Events (Withdrawn, Started, Approved, Rejected, Returned)
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  CASE "Requisition Action History"."Action"
    WHEN 'WITHDRAW' THEN 'Requisition Withdrawn'
    WHEN 'SUBMIT' THEN 'Approval Step Started' -- Assumes submit action assigns to first approver
    WHEN 'APPROVE' THEN 'Approval Step Approved'
    WHEN 'REJECT' THEN 'Approval Step Rejected'
    WHEN 'RETURN' THEN 'Approval Step Returned'
  END AS ActivityName,
  "Requisition Action History"."Action Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  CASE WHEN "Requisition Action History"."Action" = 'REJECT' THEN "Requisition Action History"."Action Note" ELSE NULL END AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Action History"."Action" IN ('WITHDRAW', 'SUBMIT', 'APPROVE', 'REJECT', 'RETURN')

UNION ALL

-- 4. Requisition Amended
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Amended' AS ActivityName,
  "Requisition Header"."Last Update Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Header"."Requisition Version" > 1 AND "Requisition Header"."Last Update Date" > "Requisition Header"."Submitted Date"

UNION ALL

-- 9. Requisition Approved (Final)
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Approved' AS ActivityName,
  "Requisition Header"."Approved Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Header"."Requisition Status" = 'APPROVED'
AND "Requisition Header"."Approved Date" IS NOT NULL

UNION ALL

-- 10. Requisition Rejected (Final)
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Rejected' AS ActivityName,
  "Requisition Action History"."Action Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  "Requisition Action History"."Action Note" AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Header"."Requisition Status" = 'REJECTED'
AND "Requisition Action History"."Action" = 'REJECT'
AND "Requisition Action History"."Sequence Number" = (SELECT MAX(T2."Sequence Number") FROM "Procurement - Requisitions Real Time" T2 WHERE T2."Requisition Header"."Requisition Number" = "Requisition Header"."Requisition Number")

UNION ALL

-- 11. Purchase Order Created
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Purchase Order Created' AS ActivityName,
  "Purchase Order Schedule"."PO Creation Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Purchase Order Schedule"."PO Creation Date" IS NOT NULL

UNION ALL

-- 12. Requisition Closed
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Closed' AS ActivityName,
  "Requisition Line"."Line Close Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Line"."Line Close Date" IS NOT NULL AND "Requisition Line"."Line Status" = 'CLOSED'

Steps

Log in to Oracle Fusion Financials with a user that has BI Author or similar privileges.
Navigate to the Reports and Analytics work area by clicking the Navigator icon, then selecting Tools > Reports and Analytics.
Click the "Browse Catalog" button to open the Business Intelligence Publisher catalog.
In the BI Publisher interface, click "New" and select "Data Model" from the dropdown list.
In the Data Model properties, you can provide a name and description. Click the "+" icon to add a new Data Set and select "SQL Query".
In the new Data Set dialog, provide a name (e.g., Requisition_Event_Log), select the appropriate Data Source for your Financials data (e.g., ApplicationDB_FSCM), and set the Type of SQL to "Standard SQL".
Copy the complete SQL script from the "query" section of this document and paste it into the SQL Query text area.
The query includes parameters :p_start_date and :p_end_date. These will automatically be recognized by BI Publisher. You can set default values if desired.
Save the Data Model in a suitable catalog folder (e.g., Shared Folders/Custom).
To export the data, navigate back to the catalog, click "New" and select "Report". Link this new report to the Data Model you just created.
Run the report, providing the start and end date parameters. Once the report displays the data, use the "Export" functionality to download the results as a CSV file.
Ensure the final CSV file is saved with UTF-8 encoding and contains the required headers for ingestion into your process mining tool.

Configuration

Data Source: The BI Publisher Data Source, typically named 'ApplicationDB_FSCM' or similar, must be configured with a user that has read access to all tables in the query, including those from Procurement (POR, PO), Human Capital Management (PER, HR), and Financials (FUN).
Date Range Filtering: The query is designed with mandatory date parameters (:p_start_date and :p_end_date) that filter on the requisition creation date. For initial extractions or large systems, it is critical to extract data in manageable batches, for example 3 to 6 months at a time, to prevent system timeouts.
Business Unit Filtering: A filter for Business Unit name is included in a commented section of the query's WHERE clause. It is highly recommended to uncomment and configure this filter to limit the data to the business units relevant to your analysis, which can significantly improve performance.
Prerequisites: The user creating and running the report requires a role with BI Publisher privileges, such as 'BI Administrator Role' or 'BI Author Role'. Additionally, this user needs the necessary data security grants to access the procurement data.

a Sample Query sql

WITH BaseRequisitions AS (
    SELECT
        PRH.REQUISITION_HEADER_ID,
        PRH.REQUISITION_NUMBER,
        PRH.CREATION_DATE,
        PRH.LAST_UPDATE_DATE AS HEADER_LAST_UPDATE_DATE,
        PRH.DOCUMENT_STATUS,
        PRH.PREPARER_ID,
        PPNF_REQ.DISPLAY_NAME AS RequesterName,
        HOU.NAME AS DepartmentName,
        BU.BU_NAME AS BusinessUnit,
        SUM(PRL.AMOUNT) OVER (PARTITION BY PRH.REQUISITION_HEADER_ID) AS RequisitionTotalAmount,
        MIN(PRL.NEED_BY_DATE) OVER (PARTITION BY PRH.REQUISITION_HEADER_ID) AS RequiredByDate
    FROM
        POR_REQUISITION_HEADERS_ALL PRH
    JOIN
        POR_REQUISITION_LINES_ALL PRL ON PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
    JOIN
        PER_USERS PU_REQ ON PRH.PREPARER_ID = PU_REQ.USER_ID
    LEFT JOIN
        PER_PERSON_NAMES_F PPNF_REQ ON PU_REQ.PERSON_ID = PPNF_REQ.PERSON_ID AND PPNF_REQ.NAME_TYPE = 'GLOBAL' AND SYSDATE BETWEEN PPNF_REQ.EFFECTIVE_START_DATE AND PPNF_REQ.EFFECTIVE_END_DATE
    LEFT JOIN
        PER_ALL_ASSIGNMENTS_F PAAF ON PU_REQ.PERSON_ID = PAAF.PERSON_ID AND PAAF.ASSIGNMENT_TYPE = 'E' AND PAAF.PRIMARY_FLAG = 'Y' AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
    LEFT JOIN
        HR_ORGANIZATION_UNITS_F_VL HOU ON PAAF.ORGANIZATION_ID = HOU.ORGANIZATION_ID AND SYSDATE BETWEEN HOU.EFFECTIVE_START_DATE AND HOU.EFFECTIVE_END_DATE
    JOIN
        FUN_ALL_BUSINESS_UNITS_V BU ON PRH.REQ_BU_ID = BU.BU_ID
    WHERE PRH.CREATION_DATE BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD') AND TO_DATE(:p_end_date, 'YYYY-MM-DD')
    -- AND BU.BU_NAME IN ('[Your Business Unit Name]') -- Recommended: Uncomment and specify your Business Unit(s)
)
-- 1. Requisition Created
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    'Requisition Created' AS "ActivityName",
    BR.CREATION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    BR.DOCUMENT_STATUS AS "RequisitionStatus",
    BR.RequiredByDate,
    NULL AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR

UNION ALL

-- 2. Requisition Submitted, 4. Requisition Amended
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    CASE 
        WHEN PAH.OBJECT_REVISION_NUM > 1 THEN 'Requisition Amended'
        ELSE 'Requisition Submitted'
    END AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    BR.DOCUMENT_STATUS AS "RequisitionStatus",
    BR.RequiredByDate,
    NULL AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR
JOIN PO_ACTION_HISTORY PAH ON BR.REQUISITION_HEADER_ID = PAH.OBJECT_ID
WHERE PAH.OBJECT_TYPE_CODE = 'REQUISITION' AND PAH.ACTION_CODE = 'SUBMIT'

UNION ALL

-- 3. Requisition Withdrawn
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    'Requisition Withdrawn' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    BR.DOCUMENT_STATUS AS "RequisitionStatus",
    BR.RequiredByDate,
    NULL AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR
JOIN PO_ACTION_HISTORY PAH ON BR.REQUISITION_HEADER_ID = PAH.OBJECT_ID
WHERE PAH.OBJECT_TYPE_CODE = 'REQUISITION' AND PAH.ACTION_CODE = 'WITHDRAW'

UNION ALL

-- 5. Approval Step Started
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    'Approval Step Started' AS "ActivityName",
    PAH.CREATION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    BR.DOCUMENT_STATUS AS "RequisitionStatus",
    BR.RequiredByDate,
    NULL AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR
JOIN PO_ACTION_HISTORY PAH ON BR.REQUISITION_HEADER_ID = PAH.OBJECT_ID
WHERE PAH.OBJECT_TYPE_CODE = 'REQUISITION' AND PAH.ACTION_CODE IN ('APPROVE', 'REJECT', 'SUBMIT', 'RETURN')

UNION ALL

-- 6. Approval Step Approved
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    'Approval Step Approved' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    BR.DOCUMENT_STATUS AS "RequisitionStatus",
    BR.RequiredByDate,
    NULL AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR
JOIN PO_ACTION_HISTORY PAH ON BR.REQUISITION_HEADER_ID = PAH.OBJECT_ID
WHERE PAH.OBJECT_TYPE_CODE = 'REQUISITION' AND PAH.ACTION_CODE = 'APPROVE'

UNION ALL

-- 7. Approval Step Rejected
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    'Approval Step Rejected' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    BR.DOCUMENT_STATUS AS "RequisitionStatus",
    BR.RequiredByDate,
    PAH.NOTE AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR
JOIN PO_ACTION_HISTORY PAH ON BR.REQUISITION_HEADER_ID = PAH.OBJECT_ID
WHERE PAH.OBJECT_TYPE_CODE = 'REQUISITION' AND PAH.ACTION_CODE = 'REJECT'

UNION ALL

-- 8. Approval Step Returned
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    'Approval Step Returned' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    BR.DOCUMENT_STATUS AS "RequisitionStatus",
    BR.RequiredByDate,
    PAH.NOTE AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR
JOIN PO_ACTION_HISTORY PAH ON BR.REQUISITION_HEADER_ID = PAH.OBJECT_ID
WHERE PAH.OBJECT_TYPE_CODE = 'REQUISITION' AND PAH.ACTION_CODE = 'RETURN'

UNION ALL

-- 9. Requisition Approved
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    'Requisition Approved' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    'APPROVED' AS "RequisitionStatus",
    BR.RequiredByDate,
    NULL AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR
JOIN PO_ACTION_HISTORY PAH ON BR.REQUISITION_HEADER_ID = PAH.OBJECT_ID
WHERE PAH.OBJECT_TYPE_CODE = 'REQUISITION' AND PAH.ACTION_CODE = 'FINALLY CLOSE' AND BR.DOCUMENT_STATUS = 'APPROVED'

UNION ALL

-- 10. Requisition Rejected
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    'Requisition Rejected' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    'REJECTED' AS "RequisitionStatus",
    BR.RequiredByDate,
    PAH.NOTE AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR
JOIN PO_ACTION_HISTORY PAH ON BR.REQUISITION_HEADER_ID = PAH.OBJECT_ID
WHERE PAH.OBJECT_TYPE_CODE = 'REQUISITION' AND PAH.ACTION_CODE = 'REJECT'
AND BR.DOCUMENT_STATUS = 'REJECTED'
AND PAH.ACTION_DATE = (SELECT MAX(PAH2.ACTION_DATE) FROM PO_ACTION_HISTORY PAH2 WHERE PAH2.OBJECT_ID = BR.REQUISITION_HEADER_ID AND PAH2.ACTION_CODE = 'REJECT')

UNION ALL

-- 11. Purchase Order Created
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    'Purchase Order Created' AS "ActivityName",
    POH.CREATION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    BR.DOCUMENT_STATUS AS "RequisitionStatus",
    BR.RequiredByDate,
    NULL AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR
JOIN POR_REQUISITION_LINES_ALL PRL ON BR.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
JOIN PO_DISTRIBUTIONS_ALL POD ON PRL.REQUISITION_LINE_ID = POD.REQUISITION_LINE_ID
JOIN PO_HEADERS_ALL POH ON POD.PO_HEADER_ID = POH.PO_HEADER_ID

UNION ALL

-- 12. Requisition Closed
SELECT DISTINCT
    BR.REQUISITION_HEADER_ID AS "PurchaseRequisitionId",
    'Requisition Closed' AS "ActivityName",
    BR.HEADER_LAST_UPDATE_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    BR.RequesterName,
    BR.DepartmentName,
    BR.RequisitionTotalAmount,
    'CLOSED' AS "RequisitionStatus",
    BR.RequiredByDate,
    NULL AS "RejectionReason",
    BR.BusinessUnit
FROM BaseRequisitions BR
WHERE BR.DOCUMENT_STATUS = 'CLOSED'

Steps

Prerequisites and Environment Setup: Ensure you have Python 3 installed on your system. Install the necessary requests library by running the following command in your terminal: pip install requests. You will also need API credentials (username, password) for an Oracle Fusion user with sufficient permissions to read purchase requisition data, along with the base URL for your Oracle Fusion instance.
Save the Python Script: Save the complete Python code provided in the 'query' section as a file, for instance, extract_requisitions.py.
Configure Script Parameters: Open the script file in a text editor. Modify the placeholder values at the top of the script. Set FUSION_URL, USERNAME, and PASSWORD to match your Oracle Fusion environment's details. Adjust the START_DATE and END_DATE to define the extraction period.
Understand API Endpoints and Fields: The script targets the purchaseRequisitions REST API resource. It uses the fields parameter to request only the necessary data, including nested collections like lines (for PO information) and actionHistory (for approval events), which improves performance.
Execute the Script: Run the script from your terminal using the command: python extract_requisitions.py. The script will authenticate with the API, begin fetching data, and print progress updates to the console, showing how many requisitions have been processed.
Handle Pagination: The script automatically handles pagination. The Oracle Fusion REST API returns data in chunks (pages). The script checks the hasMore flag in each response and repeatedly makes requests with an updated offset parameter until all requisitions within the specified date range have been retrieved.
Data Transformation Logic: For each requisition retrieved from the API, the script generates multiple rows in the event log. It creates a 'Requisition Created' event from the CreationDate, a 'Requisition Submitted' event from the SubmittedDate, and iterates through the actionHistory collection to create detailed events for each approval step (Started, Approved, Rejected, Returned).
Infer Final Status Events: The script captures the final outcome events like 'Requisition Approved' and 'Requisition Rejected' by checking the requisition's final DocumentStatus and using the LastUpdateDate as the event timestamp.
Generate Purchase Order Event: The script inspects each requisition line. If a line has a PoHeaderId, it signifies that a Purchase Order was created. An event log entry is generated for this activity, linking the requisition to the downstream procurement process.
Review the Output File: Once the script finishes, it will create a CSV file named oracle_fusion_requisitions_event_log.csv in the same directory. This file contains the extracted event log.
Prepare for Upload: Open the generated CSV file in a spreadsheet program to verify its contents. Ensure the columns and data formats align with the requirements for your process mining tool. The file is now ready to be uploaded to ProcessMind.

Configuration

API Credentials: A dedicated integration user account with a role that grants read-only access to the Purchase Requisitions REST API is required. Example roles include Procurement Manager or a custom integration role.
REST API Endpoint: The script is configured to use the .../fscmRestApi/resources/11.13.18.05/purchaseRequisitions endpoint. Ensure the version number in the URL matches your Oracle Fusion environment.
Date Range Filter: The script includes START_DATE and END_DATE variables. It is highly recommended to extract data in manageable batches, such as 3 to 6 months at a time, to avoid API timeouts and performance issues. The filter is applied to the CreationDate of the requisitions.
Field Selection: The fields parameter in the script is crucial for performance. It limits the response payload to only the attributes needed for the event log, reducing network traffic and processing time. Do not remove required fields like RequisitionId, CreationDate, actionHistory, or lines.
Pagination Limit: The limit parameter is set to 100, which is a safe value for most environments. You can try increasing this to 200 or 500 for potentially faster extraction, but be aware of potential memory issues or API limits if set too high.

a Sample Query python

import requests
import csv
import json
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration Settings --- 
class=class="hl-string">"hl-comment"># Replace with your Oracle Fusion instance URL, username, and password.
FUSION_URL = class="hl-string">"https://[your-fusion-instance-url].oraclecloud.com"
USERNAME = class="hl-string">"your_api_user"
PASSWORD = class="hl-string">"your_api_password"

class=class="hl-string">"hl-comment"># Define the date range for the extraction.
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-06-30"

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---

def get_requisitions_data():
    class="hl-string">"""Fetches and processes purchase requisition data from Oracle Fusion REST API."""
    
    api_url = fclass="hl-string">"{FUSION_URL}/fscmRestApi/resources/11.13.18.05/purchaseRequisitions"
    
    class=class="hl-string">"hl-comment"># Define the fields to retrieve to optimize the API call
    fields_to_get = [
        class="hl-string">"RequisitionId", class="hl-string">"RequisitionNumber", class="hl-string">"CreationDate", class="hl-string">"SubmittedDate", class="hl-string">"LastUpdateDate",
        class="hl-string">"DocumentStatus", class="hl-string">"PreparerName", class="hl-string">"BusinessUnitName", class="hl-string">"RequisitionTotal", class="hl-string">"NeedByDate",
        class="hl-string">"Department", class="hl-string">"RejectionReason", 
        class="hl-string">"actionHistory(ActionCode,ActionDate,PerformerName)",
        class="hl-string">"lines(PoHeaderId,LastUpdateDate)"
    ]
    
    class=class="hl-string">"hl-comment"># Parameters for the API request
    params = {
        class="hl-string">"q": fclass="hl-string">"CreationDate between '{START_DATE}' and '{END_DATE}'",
        class="hl-string">"fields": class="hl-string">",".join(fields_to_get),
        class="hl-string">"limit": 100,
        class="hl-string">"offset": 0
    }
    
    headers = {
        class="hl-string">'Content-Type': class="hl-string">'application/vnd.oracle.adf.resourceitem+json'
    }
    
    event_log = []
    has_more = True
    total_processed = 0
    now_iso = datetime.utcnow().isoformat() + class="hl-string">'Z'

    while has_more:
        print(fclass="hl-string">"Fetching requisitions from offset {params['offset']}...")
        try:
            response = requests.get(
                api_url, 
                auth=(USERNAME, PASSWORD), 
                params=params, 
                headers=headers
            )
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes (4xx or 5xx)
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching data: {e}")
            break

        data = response.json()
        requisitions = data.get(class="hl-string">'items', [])

        if not requisitions:
            print(class="hl-string">"No more requisitions found.")
            break

        for req in requisitions:
            base_event = {
                class="hl-string">'PurchaseRequisitionId': req.get(class="hl-string">'RequisitionNumber'),
                class="hl-string">'RequesterName': req.get(class="hl-string">'PreparerName'),
                class="hl-string">'DepartmentName': req.get(class="hl-string">'Department'),
                class="hl-string">'RequisitionTotalAmount': req.get(class="hl-string">'RequisitionTotal'),
                class="hl-string">'RequisitionStatus': req.get(class="hl-string">'DocumentStatus'),
                class="hl-string">'RequiredByDate': req.get(class="hl-string">'NeedByDate'),
                class="hl-string">'RejectionReason': req.get(class="hl-string">'RejectionReason'),
                class="hl-string">'BusinessUnit': req.get(class="hl-string">'BusinessUnitName'),
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials',
                class="hl-string">'LastDataUpdate': now_iso
            }

            class=class="hl-string">"hl-comment"># 1. Requisition Created
            if req.get(class="hl-string">'CreationDate'):
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Requisition Created', class="hl-string">'EventTime': req[class="hl-string">'CreationDate']})

            class=class="hl-string">"hl-comment"># 2. Requisition Submitted
            if req.get(class="hl-string">'SubmittedDate'):
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Requisition Submitted', class="hl-string">'EventTime': req[class="hl-string">'SubmittedDate']})

            class=class="hl-string">"hl-comment"># 3-8. Approval Workflow Events from actionHistory
            po_created = False
            if class="hl-string">'actionHistory' in req and req[class="hl-string">'actionHistory'] is not None:
                for action in req[class="hl-string">'actionHistory']:
                    activity = None
                    action_code = action.get(class="hl-string">'ActionCode')
                    if action_code == class="hl-string">'SUBMIT':
                        class=class="hl-string">"hl-comment"># This is often the first step in the approval flow
                        activity = class="hl-string">'Approval Step Started'
                    elif action_code == class="hl-string">'APPROVE':
                        activity = class="hl-string">'Approval Step Approved'
                    elif action_code == class="hl-string">'REJECT':
                        activity = class="hl-string">'Approval Step Rejected'
                    elif action_code == class="hl-string">'RETURN':
                        activity = class="hl-string">'Approval Step Returned'
                    elif action_code == class="hl-string">'WITHDRAW':
                        activity = class="hl-string">'Requisition Withdrawn'
                    elif action_code == class="hl-string">'UPDATE':
                        activity = class="hl-string">'Requisition Amended'
                    
                    if activity:
                        event_log.append({**base_event, class="hl-string">'ActivityName': activity, class="hl-string">'EventTime': action.get(class="hl-string">'ActionDate')})

            class=class="hl-string">"hl-comment"># 9-10 & 12. Final Status Events
            final_status = req.get(class="hl-string">'DocumentStatus')
            last_update_date = req.get(class="hl-string">'LastUpdateDate')
            if last_update_date:
                if final_status == class="hl-string">'APPROVED':
                    event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Requisition Approved', class="hl-string">'EventTime': last_update_date})
                elif final_status == class="hl-string">'REJECTED':
                    event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Requisition Rejected', class="hl-string">'EventTime': last_update_date})
                elif final_status == class="hl-string">'CLOSED':
                    event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Requisition Closed', class="hl-string">'EventTime': last_update_date})

            class=class="hl-string">"hl-comment"># 11. Purchase Order Created
            if class="hl-string">'lines' in req and req[class="hl-string">'lines'] is not None:
                for line in req[class="hl-string">'lines']:
                    if line.get(class="hl-string">'PoHeaderId') and not po_created:
                        event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Purchase Order Created', class="hl-string">'EventTime': line.get(class="hl-string">'LastUpdateDate', last_update_date)})
                        po_created = True class=class="hl-string">"hl-comment"># Create only one PO event per requisition
                        break
        
        total_processed += len(requisitions)
        print(fclass="hl-string">"Processed {total_processed} total requisitions so far.")

        class=class="hl-string">"hl-comment"># Check if there is more data to fetch for pagination
        has_more = data.get(class="hl-string">'hasMore', False)
        if has_more:
            params[class="hl-string">'offset'] += params[class="hl-string">'limit']

    class=class="hl-string">"hl-comment"># Write to CSV
    if event_log:
        filename = class="hl-string">'oracle_fusion_requisitions_event_log.csv'
        fieldnames = list(event_log[0].keys())
        with open(filename, class="hl-string">'w', newline=class="hl-string">'', encoding=class="hl-string">'utf-8') as csvfile:
            writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
            writer.writeheader()
            writer.writerows(event_log)
        print(fclass="hl-string">"\nSuccessfully extracted {len(event_log)} events to {filename}")
    else:
        print(class="hl-string">"\nNo events were generated.")

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

Steps

Navigate to Reports and Analytics: Log in to Oracle Fusion Financials. Use the Navigator menu to go to Tools, then select Reports and Analytics. This is the central hub for all reporting activities.
Create a New Analysis: Within the Reports and Analytics work area, click the 'Create' button and select 'Analysis' from the dropdown menu. This will launch the Oracle Transactional Business Intelligence (OTBI) analysis editor in a new tab.
Select the Subject Area: You will be prompted to select a Subject Area. In the search box, type 'Procurement - Requisitions Real Time' and select it from the list. This subject area contains all the necessary data points for requisition lifecycle events.
Build the Analysis for Requisition Events: This step requires creating a union of several queries to capture each distinct event. Since the OTBI UI does not have a direct UNION ALL feature for different event types, you will create a single report pulling all relevant date and status fields. In the 'Criteria' tab, drag and drop the columns listed in the query section from the 'Subject Areas' pane on the left into the 'Selected Columns' area. This includes columns for creation date, submission date, approval date, PO creation date, etc.
Add Requisition Action History: To capture detailed approval steps (Approved, Rejected, Returned, Withdrawn), you need to include fields from the 'Requisition Action History' folder within the subject area. Drag 'Action Date', 'Action', and 'Performer' into your selected columns.
Configure Filters: Switch to the 'Prompts' tab to create filters for your analysis. It is essential to filter by a date range to manage data volume. Create a filter on 'Requisition Header'.'Requisition Creation Date' and set it to a range, for example, the last 6 months. You can also add prompts for 'Business Unit' or 'Requester Name' to narrow down the data.
Execute and Review the Report: Go to the 'Results' tab to execute the analysis and preview the data. The output will be a 'wide' table with one row per requisition line or approval action, containing multiple date and status columns.
Export the Raw Data: Once you are satisfied with the preview, click the 'Export' link, typically located at the bottom of the results page. Choose 'Data' > 'CSV File' as the format. This will download the raw data from your analysis.
Transform Data into Event Log Format: The exported CSV will be in a wide format. You must use an external tool (like a Python script, Power Query in Excel, or another data preparation tool) to transform this data. The process involves 'unpivoting' the date columns into a single 'EventTime' column and creating a corresponding 'ActivityName' column for each event type (e.g., 'Requisition Created', 'Requisition Submitted').
Finalize and Prepare for Upload: After transformation, ensure the file has the required columns: PurchaseRequisitionId, ActivityName, EventTime, SourceSystem, and LastDataUpdate. Save the final event log as a CSV file, which can then be uploaded to ProcessMind.

Configuration

Subject Area: The primary subject area to use is 'Procurement - Requisitions Real Time'. This contains all core requisition header, line, distribution, and approval history information.
Date Range: Always apply a mandatory date filter to limit the data volume and improve performance. A filter on 'Requisition Header'.'Requisition Creation Date' for the last 3 to 6 months is a good starting point.
Key Filters: Consider adding filters for 'Requisition Header'.'Business Unit' and 'Requisition Header'.'Requisition Status' to focus the extraction on relevant organizational units and exclude irrelevant statuses like 'Canceled' if they are not needed for the analysis.
Column Selection: Be selective with the columns you add to the analysis. Including too many columns, especially from many different folders (dimensions), can negatively impact performance. Focus only on the fields required for the event log.
Permissions: The user running the extraction must have a role with the necessary permissions to access the Reports and Analytics work area and the 'Procurement - Requisitions Real Time' subject area. Typically, roles like 'BI Author' or 'Procurement Analyst' have these privileges.

a Sample Query config

/*
OTBI analyses are built visually, not with SQL. This conceptual query illustrates the logic required to extract the data for all specified activities. The user would build an analysis containing these fields and then transform the output into an event log.
*/

-- 1. Requisition Created
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Created' AS ActivityName,
  "Requisition Header"."Requisition Creation Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Header"."Requisition Creation Date" IS NOT NULL

UNION ALL

-- 2. Requisition Submitted
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Submitted' AS ActivityName,
  "Requisition Header"."Submitted Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Header"."Submitted Date" IS NOT NULL

UNION ALL

-- 3, 5, 6, 7, 8. Approval History Events (Withdrawn, Started, Approved, Rejected, Returned)
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  CASE "Requisition Action History"."Action"
    WHEN 'WITHDRAW' THEN 'Requisition Withdrawn'
    WHEN 'SUBMIT' THEN 'Approval Step Started' -- Assumes submit action assigns to first approver
    WHEN 'APPROVE' THEN 'Approval Step Approved'
    WHEN 'REJECT' THEN 'Approval Step Rejected'
    WHEN 'RETURN' THEN 'Approval Step Returned'
  END AS ActivityName,
  "Requisition Action History"."Action Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  CASE WHEN "Requisition Action History"."Action" = 'REJECT' THEN "Requisition Action History"."Action Note" ELSE NULL END AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Action History"."Action" IN ('WITHDRAW', 'SUBMIT', 'APPROVE', 'REJECT', 'RETURN')

UNION ALL

-- 4. Requisition Amended
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Amended' AS ActivityName,
  "Requisition Header"."Last Update Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Header"."Requisition Version" > 1 AND "Requisition Header"."Last Update Date" > "Requisition Header"."Submitted Date"

UNION ALL

-- 9. Requisition Approved (Final)
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Approved' AS ActivityName,
  "Requisition Header"."Approved Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Header"."Requisition Status" = 'APPROVED'
AND "Requisition Header"."Approved Date" IS NOT NULL

UNION ALL

-- 10. Requisition Rejected (Final)
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Rejected' AS ActivityName,
  "Requisition Action History"."Action Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  "Requisition Action History"."Action Note" AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Header"."Requisition Status" = 'REJECTED'
AND "Requisition Action History"."Action" = 'REJECT'
AND "Requisition Action History"."Sequence Number" = (SELECT MAX(T2."Sequence Number") FROM "Procurement - Requisitions Real Time" T2 WHERE T2."Requisition Header"."Requisition Number" = "Requisition Header"."Requisition Number")

UNION ALL

-- 11. Purchase Order Created
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Purchase Order Created' AS ActivityName,
  "Purchase Order Schedule"."PO Creation Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Purchase Order Schedule"."PO Creation Date" IS NOT NULL

UNION ALL

-- 12. Requisition Closed
SELECT 
  "Requisition Header"."Requisition Number" AS PurchaseRequisitionId,
  'Requisition Closed' AS ActivityName,
  "Requisition Line"."Line Close Date" AS EventTime,
  'Oracle Fusion Financials OTBI' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  "Requisition Distribution"."Requester Name" AS RequesterName,
  "Requisition Distribution"."Requester Department" AS DepartmentName,
  "Requisition Header"."Total" AS RequisitionTotalAmount,
  "Requisition Header"."Requisition Status" AS RequisitionStatus,
  "Requisition Line"."Need By Date" AS RequiredByDate,
  NULL AS RejectionReason,
  "Requisition Header"."Business Unit" AS BusinessUnit
FROM "Procurement - Requisitions Real Time"
WHERE "Requisition Line"."Line Close Date" IS NOT NULL AND "Requisition Line"."Line Status" = 'CLOSED'

Your Purchase to Pay - Requisition Data Template

Your Purchase to Pay - Requisition Data Template

Purchase to Pay - Requisition Attributes

Purchase to Pay - Requisition Activities

Extraction Guides

Oracle Transactional Business Intelligence (OTBI) Export

Steps

Configuration

a Sample Query config

Oracle BI Publisher SQL-Based Data Model Extraction

Steps

Configuration

a Sample Query sql

Python Script via Oracle Fusion REST API

Steps

Configuration

a Sample Query python

Oracle BI Cloud Connector (BICC) Bulk Data Extract

Steps

Configuration

a Sample Query config

Ready to Get Started?

Achieve 30% Faster Purchase to Pay - Requisition

Your Purchase to Pay - Requisition Data Template

Your Purchase to Pay - Requisition Data Template

Purchase to Pay - Requisition Attributes

Purchase to Pay - Requisition Activities

Extraction Guides

Oracle Transactional Business Intelligence (OTBI) Export

Steps

Configuration

a Sample Query config

Oracle BI Publisher SQL-Based Data Model Extraction

Steps

Configuration

a Sample Query sql

Python Script via Oracle Fusion REST API

Steps

Configuration

a Sample Query python

Oracle BI Cloud Connector (BICC) Bulk Data Extract

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Achieve 30% Faster Purchase to Pay - Requisition