> Continuous Process Improvement > Accounts Payable Invoice Processing > Oracle Fusion Financials > Data Template

Data Template: Accounts Payable Invoice Processing

Oracle Fusion Financials

Accounts Payable Invoice Processing Attributes (23) Accounts Payable Invoice Processing Activities (13) Extraction Guides (4)

Your Accounts Payable Invoice Processing Data Template

This template provides a comprehensive guide to collecting the right data for your Accounts Payable Invoice Processing analysis. It outlines the essential attributes and activities needed to build an accurate event log, along with practical guidance for data extraction. Use this resource to ensure you capture all critical information for effective 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

Accounts Payable Invoice Processing Attributes

These are the recommended data fields to include in your event log for a comprehensive analysis of your accounts payable invoice processing.

3 Required 8 Recommended 12 Optional

	Name	Description
	Activity Name ActivityName	The name of the specific process step or event that occurred.
Description This attribute describes a single step in the invoice processing lifecycle, such as 'Invoice Created', 'Approval Initiated', or 'Payment Executed'. By sequencing these activities in time, process mining tools can reconstruct the end-to-end process flow for each invoice. Analyzing activities helps identify the most frequent paths, deviations, and rework loops within the process. Why it matters It forms the core of the process map, allowing for the visualization and analysis of the process flow, identification of deviations, and measurement of step-specific performance. Where to get This is typically derived from status changes, event tables, or audit logs within Oracle Fusion Financials. It may require mapping from various source tables and fields. Examples Invoice ValidatedHold PlacedInvoice ApprovedPayment Executed
	Event Time EventTime	The timestamp indicating when the activity occurred.
Description This attribute provides the exact date and time for each activity in the process. It is fundamental for ordering events chronologically and calculating durations between steps. This data allows for performance analysis, such as identifying bottlenecks by measuring waiting times, calculating cycle times for specific stages (like approvals), and monitoring SLA compliance. Why it matters It is critical for calculating all time-based metrics, including cycle times and waiting times, which are essential for identifying bottlenecks and measuring process efficiency. Where to get This corresponds to creation dates, last update dates, or specific event timestamps in various Oracle Fusion tables (e.g., LAST_UPDATE_DATE, CREATION_DATE, APPROVAL_DATE). Examples 2023-10-26T10:00:00Z2023-11-15T14:35:10Z2024-01-05T09:12:45Z
	Invoice InvoiceId	The unique identifier for each invoice processed.
Description The 'Invoice' serves as the primary case identifier, linking all activities from the moment an invoice is received until its final payment. Each unique InvoiceId represents a single end-to-end process instance, allowing for a complete analysis of each invoice's journey through the Accounts Payable process. It is the backbone of the process analysis, connecting all related events and attributes. Why it matters It is essential for tracking each invoice's lifecycle from start to finish, enabling the analysis of process variants, bottlenecks, and overall throughput. Where to get This is typically the INVOICE_ID or INVOICE_NUM from the AP_INVOICES_ALL table in Oracle Fusion Financials. Examples INV-987657334001APO-INV-2023-005
	Due Date DueDate	The date by which the invoice payment is due.
Description The calculated date by which the invoice must be paid to avoid late fees and maintain good vendor relationships, based on the invoice date and payment terms. This is a critical attribute for monitoring on-time payment performance and managing cash flow. The 'On-Time Payment Rate' KPI is directly calculated by comparing the 'Payment Date' to this 'Due Date'. Why it matters Essential for measuring on-time payment rates, managing cash flow, and avoiding late payment penalties. It is a key field for payment policy compliance. Where to get This may be a direct field in a payment schedule table (e.g., AP_PAYMENT_SCHEDULES_ALL) or calculated based on INVOICE_DATE and payment terms. Examples 2023-11-142023-12-012024-01-19
	End Time EndTime	The timestamp of the final activity for the invoice.
Description Represents the timestamp of the terminal event in the invoice's lifecycle, such as 'Payment Cleared' or 'Invoice Cancelled'. This attribute is typically derived by finding the latest 'EventTime' for each 'InvoiceId'. It is essential for calculating the total end-to-end processing time of an invoice. Why it matters Enables the calculation of the total end-to-end cycle time for each invoice, a fundamental KPI for measuring overall process efficiency. Where to get This is a calculated attribute, derived by finding the maximum EventTime for each case (InvoiceId) in the dataset. Examples 2023-10-30T11:00:00Z2023-11-20T16:45:00Z2024-01-10T10:20:30Z
	Invoice Amount InvoiceAmount	The total monetary value of the invoice.
Description This attribute represents the total amount due on the invoice. It is a fundamental metric for financial analysis and for prioritizing invoices. Analyzing invoice amount can reveal if high-value invoices are processed differently or experience more delays than low-value ones. It's also key for dashboards related to cash flow forecasting and duplicate payment analysis. Why it matters Allows for financial impact analysis, such as prioritizing high-value invoices, analyzing processing time by value, and calculating the financial impact of delays. Where to get Sourced from the INVOICE_AMOUNT field in the AP_INVOICES_ALL table. Examples 5400.50125000.00750.25
	Invoice Status InvoiceStatus	The current or final status of the invoice.
Description Indicates the current state of an invoice within the process, such as 'Validated', 'Pending Approval', 'Paid', or 'Cancelled'. This attribute provides a snapshot of where an invoice is in its lifecycle, which is useful for operational dashboards. Analyzing the final status helps understand process outcomes, such as the rate of cancelled or rejected invoices. Why it matters Provides a quick overview of the invoice's outcome and current state, helping to analyze exception rates (e.g., cancellations) and process efficiency. Where to get This can be derived from the status field in the AP_INVOICES_ALL table or from the payment status in AP_PAYMENT_SCHEDULES_ALL. Examples ValidatedPaidCancelledNeeds Revalidation
	Payment Date PaymentDate	The date when the payment for the invoice was executed.
Description This attribute records the date on which the payment was actually made. It is used to confirm the completion of the AP process for an invoice. Comparing the Payment Date against the Due Date is fundamental for calculating the on-time payment rate and analyzing early payment discount capture. This is a key data point for treasury and cash flow management. Why it matters Allows for the calculation of on-time payment KPIs and analysis of early payment discount realization, directly impacting cash flow management. Where to get Sourced from payment tables such as AP_INVOICE_PAYMENTS_ALL, specifically a field like ACCOUNTING_DATE or CHECK_DATE. Examples 2023-11-132023-12-052024-01-19
	Purchase Order Number PurchaseOrderNumber	The identifier for the purchase order associated with the invoice.
Description The unique number of the purchase order (PO) that authorized the procurement of goods or services. This attribute is fundamental for analyzing the three-way matching process (PO vs. Goods Receipt vs. Invoice). A high rate of discrepancies for invoices linked to POs can indicate issues in the procurement or receiving departments. Analyzing invoices with and without POs can also reveal different process behaviors. Why it matters Crucial for analyzing the 3-way matching process, identifying matching discrepancies, and understanding differences between PO-backed and non-PO invoices. Where to get This information is typically available by joining invoice line tables (e.g., AP_INVOICE_LINES_ALL) with PO tables (e.g., PO_HEADERS_ALL). Examples PO-10056982347null
	User User	The user ID or name of the person who performed the activity.
Description Identifies the employee or system user responsible for executing a particular process step. This could be the AP clerk who entered the invoice, the manager who approved it, or the payment specialist who executed the payment. Analyzing by user helps identify training needs, workload distribution, and performance variations among team members. It is also crucial for compliance and audit trail purposes. Why it matters Enables analysis of workload, team performance, and identification of individual bottlenecks or training opportunities. It is also vital for auditability. Where to get Sourced from user-related fields like CREATED_BY or LAST_UPDATED_BY in various tables, or from specific workflow or approval history tables. Examples john.doejane.smithap.clerk1
	Vendor Name VendorName	The name of the supplier or vendor who submitted the invoice.
Description Identifies the vendor associated with the invoice. This is a critical dimension for analysis, as it allows for segmenting the process performance by supplier. For example, one can analyze which vendors have the highest rate of invoice holds, longest processing times, or are most often associated with matching discrepancies. This helps in managing supplier relationships and identifying systemic issues with specific vendors. Why it matters Enables performance analysis by vendor, helping to identify issues specific to certain suppliers, such as frequent delays, discrepancies, or holds. Where to get Typically sourced from the VENDOR_NAME field in the POZ_SUPPLIERS or related vendor tables, joined via VENDOR_ID in AP_INVOICES_ALL. Examples Global Office Supplies Inc.Innovate Tech ServicesReliable Logistics Co.
	Approver Approver	The individual who approved the invoice for payment.
Description Identifies the user or manager who provided the final or a key approval for the invoice. This attribute is essential for the 'Invoice Approval Cycle Time' dashboard, which segments approval times by approver. Analyzing this data can reveal bottlenecks in the approval hierarchy, highlight individuals with high workloads, and ensure compliance with delegation of authority policies. Why it matters Critical for analyzing the approval workflow, identifying bottlenecks caused by specific approvers, and auditing compliance with approval policies. Where to get This information is typically stored in Oracle's workflow tables (e.g., related to the approval management engine - AME) or audit history tables. Examples s.jonesm.riverad.chen
	Business Unit BusinessUnit	The business unit or operating unit responsible for the invoice.
Description Identifies the specific business unit, department, or cost center that incurred the expense. This is a critical dimension for organizational analysis, allowing process performance to be compared across different parts of the company. It helps to pinpoint if process issues like long approval times or high exception rates are concentrated in specific business units, guiding targeted improvement efforts. Why it matters Enables performance benchmarking across different organizational units, helping to identify department-specific bottlenecks or compliance issues. Where to get This information is often found in the AP_INVOICES_ALL table as ORG_ID, which can be joined with HR organization tables for the business unit name. Examples North America SalesEuropean OperationsCorporate HQ Finance
	Hold Reason HoldReason	The reason an invoice was placed on hold.
Description When an invoice is prevented from proceeding to payment, a hold is placed on it. This attribute captures the specific reason for that hold, such as 'Price Mismatch', 'Quantity Discrepancy', or 'Awaiting Goods Receipt'. Analyzing hold reasons is key to identifying the root causes of process interruptions and delays. It directly supports dashboards that monitor exceptions and rework. Why it matters Pinpoints the root causes of payment delays and invoice exceptions, providing actionable insights for process improvement and vendor communication. Where to get Sourced from tables like AP_HOLDS_ALL, which links to the invoice and contains a hold reason or code. Examples Price MismatchQuantity Billed Exceeds Quantity ReceivedDuplicate Invoice
	Invoice Currency InvoiceCurrency	The currency of the invoice amount.
Description Specifies the currency in which the invoice is denominated (e.g., USD, EUR, GBP). This is essential for analyses involving multiple countries or currencies. It provides necessary context for the Invoice Amount and ensures that financial metrics are interpreted correctly. For global organizations, it's a key attribute for filtering and segmenting financial data. Why it matters Provides essential context for the 'Invoice Amount', enabling accurate financial analysis and reporting, especially in multi-national operations. Where to get Sourced from the INVOICE_CURRENCY_CODE field in the AP_INVOICES_ALL table. Examples USDEURGBPJPY
	Invoice Date InvoiceDate	The date stated on the vendor's invoice.
Description This is the date the invoice was issued by the vendor. It is the starting point for determining the payment due date based on the agreed payment terms. Comparing the Invoice Date to the date it was entered into the system ('Invoice Created' activity) reveals delays in invoice submission or receipt processing. This analysis helps identify front-end bottlenecks that can impact on-time payments and discount capture. Why it matters Serves as the baseline for calculating payment due dates and identifying delays between when an invoice is issued and when it enters the AP process. Where to get Sourced from the INVOICE_DATE field in the AP_INVOICES_ALL table. Examples 2023-10-152023-11-012023-12-20
	Invoice Processing Time InvoiceProcessingTime	The total time taken to process an invoice from start to finish.
Description This calculated metric measures the duration from the first event (e.g., 'Invoice Created') to the last event ('EndTime') for each invoice. It is the primary KPI for measuring end-to-end process efficiency. Analyzing this duration helps to identify which types of invoices (e.g., by vendor, amount, or business unit) take the longest to process and provides a baseline for improvement initiatives. Why it matters Directly measures the end-to-end efficiency of the AP process, helping to quantify bottlenecks and track the impact of improvement efforts over time. Where to get Calculated by subtracting the earliest EventTime from the latest EventTime (EndTime) for each InvoiceId. Examples 10 days 4 hours35 days 11 hours5 days 2 hours
	Is On Time Payment IsOnTimePayment	A flag indicating if the invoice was paid by its due date.
Description A boolean flag (True/False) derived by comparing the 'Payment Date' to the 'Due Date'. If the payment date is on or before the due date, the value is True. This attribute directly supports the calculation of the 'On-Time Payment Rate' KPI and is crucial for monitoring payment policy compliance and managing vendor relationships. Why it matters Directly measures adherence to payment terms, which is critical for vendor relationships, financial planning, and avoiding late fees. Where to get This is a calculated attribute derived from other fields. The logic is: IF PaymentDate <= DueDate THEN true ELSE false. Examples truefalse
	Is Rework IsRework	A flag indicating if the invoice has undergone rework.
Description A boolean flag (True/False) that identifies invoices that have experienced rework loops, such as repeated validation steps, or moving from a later process stage back to an earlier one (e.g., from 'Pending Approval' back to 'Needs Revalidation'). This is typically derived by analyzing the sequence of activities. It is essential for quantifying the 'Invoice Rework Rate' and understanding process inefficiencies. Why it matters Quantifies the frequency of inefficient rework loops, helping to identify the root causes of process exceptions and wasted effort. Where to get This is a calculated attribute. It is derived by analyzing the sequence of activities for each case to detect repeated steps or backward movements in the process flow. Examples truefalse
	Last Data Update LastUpdateDate	The timestamp of the last data refresh.
Description This attribute indicates the most recent time the data for this process was extracted from the source system. It is a metadata field applied to the entire dataset during each refresh. This information is crucial for users to understand the freshness of the data and the time window covered by the analysis. It ensures transparency and helps in interpreting the findings correctly. Why it matters Informs users about the timeliness of the data, ensuring they understand the period covered by the analysis and when the last update occurred. Where to get This value is generated and stamped on the dataset at the time of data extraction. Examples 2024-03-10T05:00:00Z2024-03-11T05:00:00Z
	Matching Status MatchingStatus	Indicates the outcome of the invoice matching process.
Description Shows the result of the validation process where the invoice is matched against a purchase order and goods receipt (for 3-way matching). Possible statuses include 'Matched', 'Partially Matched', or 'Failed'. This attribute is the basis for the '3-Way Matching Failure Rate' KPI and the associated discrepancy analysis dashboard. It helps diagnose issues in the procurement-to-pay cycle. Why it matters Directly measures the success of the automated matching process, highlighting discrepancies that lead to manual rework and payment delays. Where to get This status is often found on the invoice header or line level within tables like AP_INVOICES_ALL or AP_INVOICE_LINES_ALL. Examples SuccessFailure - Price MismatchFailure - Quantity MismatchNot Required
	Payment Terms PaymentTerms	The agreed-upon terms for invoice payment with the vendor.
Description Defines the conditions under which a vendor must be paid, such as 'Net 30' or '2% 10, Net 30'. This attribute is the basis for calculating the invoice Due Date and identifying opportunities for early payment discounts. Analyzing payment terms helps in cash flow planning and is crucial for the 'Early Payment Discount Realization' dashboard to measure how effectively the company is capturing available discounts. Why it matters Directly impacts cash flow and profitability by determining payment schedules and eligibility for early payment discounts. Where to get Available in the AP_TERMS_TL or AP_TERMS_B tables, linked from the AP_INVOICES_ALL table. Examples Net 30Net 602% 10, Net 30
	Source System SourceSystem	The system from which the data was extracted.
Description This attribute identifies the origin of the data, which is typically 'Oracle Fusion Financials' for this process. In environments with multiple systems (e.g., a separate OCR scanning system), this field helps distinguish the source of different events. It ensures data lineage and provides context, especially when integrating data from various platforms. Why it matters Provides crucial context about data origin, ensuring traceability and helping to manage data integration from potentially multiple systems. Where to get This is usually a static value defined during the data extraction setup. Examples Oracle Fusion FinancialsOracle EBS R12Fusion Cloud AP

Required Recommended Optional

Accounts Payable Invoice Processing Activities

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

6 Recommended 7 Optional

	Activity	Description
	Invoice Approved	Represents the final approval of the invoice, authorizing it for payment. This key milestone is inferred from the workflow status field being updated to a terminal approved state.
Why it matters A critical milestone that concludes the approval cycle. It is essential for calculating 'Invoice Approval Cycle Time' and identifying approver-related bottlenecks. Where to get Inferred from the `WFAPPROVAL_STATUS` column in the `AP_INVOICES_ALL` table changing to 'Manually Approved', 'Workflow Approved', or a similar final approval status. Capture Change in `AP_INVOICES_ALL.WFAPPROVAL_STATUS` to an approved state. Event type inferred
	Invoice Created	Represents the initial creation of an invoice record in the system, either through manual entry, scanning (OCR), or electronic data interchange (EDI). This event is captured when a new record is inserted into the primary invoice table.
Why it matters This is the definitive start of the invoice processing lifecycle. Analyzing the time from this event to others reveals upstream delays and overall process duration. Where to get This is an explicit event captured from the creation timestamp of the invoice record in the `AP_INVOICES_ALL` table, typically using the `CREATION_DATE` column. Capture Record creation timestamp in `AP_INVOICES_ALL` table. Event type explicit
	Invoice Validated	This activity marks the successful completion of the system's automated validation checks on the invoice data, such as format correctness and matching to a supplier. It is captured by tracking changes in the invoice's validation status field.
Why it matters Indicates the invoice is ready for further processing, like matching or approval. Delays before this step suggest data entry issues, supporting the 'Invoice Data Entry Error Analysis' dashboard. Where to get Inferred from the `VALIDATION_STATUS` column in the `AP_INVOICES_ALL` table changing to 'Validated'. An audit trail or history table on this field provides the timestamp. Capture Change in `AP_INVOICES_ALL.VALIDATION_STATUS` to 'Validated'. Event type inferred
	Payment Cleared	The final activity in the process, confirming that the payment has cleared the bank. This event is typically captured via status updates from Oracle Cash Management after bank reconciliation.
Why it matters This is the true end of the end-to-end process, providing the most accurate data for 'Average Invoice Cycle Time'. It closes the loop on the entire financial transaction. Where to get Inferred from the payment status being updated to 'CLEARED' in the `AP_CHECKS_ALL` table. The `CLEARED_DATE` column provides the timestamp for this event. Capture `CLEARED_DATE` is populated in `AP_CHECKS_ALL` table. Event type inferred
	Payment Executed	Represents the moment the payment is created and issued to the supplier. This is an explicit event captured by the creation of a payment record linked to the invoice.
Why it matters A crucial milestone for measuring 'On-Time Payment Rate' and 'End-to-End Invoice Throughput'. It signifies that the company has fulfilled its financial obligation. Where to get This is an explicit event recorded by the creation of a linking record in the `AP_INVOICE_PAYMENTS_ALL` table. The `ACCOUNTING_DATE` or `CREATION_DATE` can be used as the timestamp. Capture Record creation in `AP_INVOICE_PAYMENTS_ALL` linking an invoice to a payment. Event type explicit
	Payment Scheduled	Marks the point where an approved invoice is included in a Payment Process Request (PPR) or payment batch, but not yet paid. It is captured when payment schedule records are finalized.
Why it matters This activity bridges the gap between approval and actual payment. Analyzing the time spent in this stage is important for cash flow forecasting and management. Where to get Inferred from the `checkrun_id` being populated in the `AP_PAYMENT_SCHEDULES_ALL` table for the invoice. The creation date of the PPR can serve as the timestamp. Capture Invoice association with a Payment Process Request (PPR). Event type inferred
	Approval Initiated	Signifies that the invoice has entered the approval workflow and has been routed to the first approver. This is inferred from a change in the invoice's workflow status.
Why it matters This activity starts the clock for the 'Invoice Approval Cycle Time' KPI. It helps differentiate waiting-for-data time from waiting-for-approver time. Where to get Inferred from the `WFAPPROVAL_STATUS` in the `AP_INVOICES_ALL` table changing from a pre-workflow status to 'Initiated' or a similar pending state. Capture Change in `AP_INVOICES_ALL.WFAPPROVAL_STATUS` to 'Initiated'. Event type inferred
	Early Payment Taken	A calculated event indicating that a payment was executed early to capture a discount. This is derived by comparing the actual payment date against the discount terms stored in the payment schedule.
Why it matters Directly supports the 'Early Payment Discount Realization' dashboard. It helps quantify the financial benefits of an efficient AP process and identifies missed opportunities. Where to get Calculated by comparing the payment date (from `AP_INVOICE_PAYMENTS_ALL`) with the discount date fields (`DISCOUNT_DATE`) in the `AP_PAYMENT_SCHEDULES_ALL` table. Capture Compare payment execution date to discount eligibility date. Event type calculated
	Hold Placed	Indicates that a hold has been placed on the invoice, preventing it from being paid. This is an explicit event captured when a hold record is created and associated with the invoice, often due to discrepancies or policy violations.
Why it matters Directly identifies process exceptions and bottlenecks. Analyzing hold reasons and durations is key to understanding and resolving common processing issues. Where to get This is an explicit event recorded by the creation of a new entry in the `AP_HOLDS_ALL` table linked to the specific `INVOICE_ID`. Capture Record creation timestamp in `AP_HOLDS_ALL` table. Event type explicit
	Hold Released	Marks the resolution of an issue and the removal of a hold, allowing the invoice to proceed with the payment process. This is captured when the hold record is updated with release information.
Why it matters Measures the time taken to resolve discrepancies. Paired with 'Hold Placed', this helps quantify the duration and impact of rework and exception handling. Where to get Inferred from the `RELEASE_LOOKUP_CODE` and `LAST_UPDATE_DATE` columns being populated in the `AP_HOLDS_ALL` table for an existing hold record. Capture Update timestamp on `AP_HOLDS_ALL` record with release details. Event type inferred
	Invoice Cancelled	Represents the termination of the invoice process before payment. This is an explicit action captured when a user cancels the invoice, populating a cancellation date.
Why it matters A terminal, non-successful end state for an invoice. Analyzing the frequency and reasons for cancellations can highlight upstream issues in procurement or vendor management. Where to get Inferred from the `CANCELLED_DATE` column being populated in the `AP_INVOICES_ALL` table. The date itself serves as the event timestamp. Capture `CANCELLED_DATE` is populated in the `AP_INVOICES_ALL` table. Event type inferred
	Invoice Rejected	Indicates that an approver has rejected the invoice, halting the process and typically requiring correction and resubmission. This is captured as a status change within the approval workflow.
Why it matters Highlights a significant rework loop in the process. Analyzing rejection frequency and reasons can uncover policy misunderstandings or systemic data issues. Where to get Inferred from the `WFAPPROVAL_STATUS` column in the `AP_INVOICES_ALL` table changing to 'Rejected'. Workflow history tables may provide more detail. Capture Change in `AP_INVOICES_ALL.WFAPPROVAL_STATUS` to 'Rejected'. Event type inferred
	Matching Performed	Represents the system or user action of matching the invoice to a Purchase Order (PO) and/or Goods Receipt Note (GRN). This event is captured when the matching status on the invoice is updated.
Why it matters A crucial step for PO-based invoices that directly impacts the 'Three-Way Matching Discrepancy Trends' analysis. Failures or delays here are a major source of process bottlenecks. Where to get Inferred from the update of the `WFAPPROVAL_STATUS` or a similar matching status field in `AP_INVOICES_ALL` indicating a matching attempt has occurred. Capture Timestamp of status change related to PO matching. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Oracle Fusion Financials

Steps

Navigate to OTBI: Log in to Oracle Fusion Cloud Applications. Use the Navigator menu to go to Tools > Reports and Analytics. This will open the Business Intelligence catalog.
Create New Analysis: Click Create > Analysis. You will be prompted to select a Subject Area.
Select Primary Subject Area: Choose the Payables Invoices - Transactions Real Time subject area. This will be the basis for most invoice-related activities.
Build the First Activity Report (Invoice Created): In the 'Criteria' tab, drag and drop the required columns for the event log (e.g., "Invoice Details"."Invoice Number" for InvoiceId, "Supplier"."Supplier Name" for VendorName, etc.). Create a custom text column for the ActivityName and enter the formula 'Invoice Created'. Use "Invoice Details"."Invoice Creation Date" for the EventTime. Add a filter to ensure the creation date is not null.
Combine Analyses for Subsequent Activities: At the bottom of the 'Criteria' tab, click the Combine results based on a union, intersection, or difference operation with another analysis button. Select Union. This will create a new results block.
Add Additional Invoice Activities: For each subsequent activity (e.g., 'Invoice Validated', 'Matching Performed', 'Hold Placed', 'Hold Released', 'Invoice Cancelled'), repeat the process. Select the same set of columns in the same order. Change the ActivityName literal (e.g., 'Invoice Validated') and map the EventTime to the corresponding date field (e.g., "Invoice Details"."Validation Date", "Invoice Holds"."Hold Date"). Add a filter to ensure that the specific date field for that activity is not null.
Incorporate Payment and Other Subject Areas: For payment activities ('Payment Scheduled', 'Payment Executed', 'Payment Cleared'), you must add additional Subject Areas. Go to the 'Criteria' tab, click the Add / Remove Subject Areas icon (folder with a plus sign) in the Subject Areas pane, and add Payables Payments - Disbursements Real Time and Cash Management - Bank Statement Reconciliation Real Time. Create new unioned analyses using fields from these subject areas, ensuring the column structure matches the invoice analyses.
Define Report Filters and Prompts: Navigate to the 'Prompts' tab. Create prompts for key filters like Business Unit Name, Ledger Name, and a date range (e.g., on "Invoice Details"."Invoice Creation Date"). This allows users to control the data scope when running the report.
Configure Results and View Data: Go to the 'Results' tab. The combined data from all the unioned analyses will be displayed. It's recommended to create a simple table view. Add a filter to the report to exclude rows where EventTime is null to ensure every row represents a valid activity.
Run and Export the Report: Save the analysis with a descriptive name. Run the report by clicking Open. Once the data is displayed, use the Export link at the bottom of the page to export the event log to CSV format. This file is ready for upload into a process mining tool.

Configuration

Subject Areas: The report must be built by combining multiple subject areas. The primary ones are:
- Payables Invoices - Transactions Real Time: For invoice creation, validation, holds, approvals, and cancellation.
- Payables Payments - Disbursements Real Time: For payment scheduling and execution events.
- Cash Management - Bank Statement Reconciliation Real Time: For the 'Payment Cleared' event.
Report Structure: A UNION query structure is mandatory. Each activity must be a separate analysis block combined with the others using the UNION operator to produce a single event log table.
Date Range Filtering: It is critical to apply a date range filter to limit the data volume. A recommended starting point is 3-6 months of data, filtering on the "Invoice Details"."Invoice Creation Date".
Key Data Filters: To manage performance and relevance, always filter by:
- Business Unit Name
- Ledger Name
- Source (e.g., to include or exclude specific invoice sources like 'Manual' or 'EDI')
Prerequisites: The user running the report needs a role with access to the specified subject areas, such as Accounts Payable Manager, Accounts Payable Specialist, and BI Administrator role for building reports.

a Sample Query config

-- OTBI Analysis Configuration for Accounts Payable Event Log
-- This is a logical representation of an OTBI UNION report.
-- Each block represents a separate analysis combined with a UNION operation.
-- The column order and data types must be identical in each block.

-- Base Columns (ensure these are selected in this order for every block):
-- 1. InvoiceId: "Payables Invoices - Transactions Real Time"."Invoice Details"."Invoice Number"
-- 2. ActivityName: 'Literal String Value'
-- 3. EventTime: [Specific Date/Time Column]
-- 4. VendorName: "Payables Invoices - Transactions Real Time"."Supplier"."Supplier Name"
-- 5. InvoiceAmount: "Payables Invoices - Transactions Real Time"."Invoice Lines"."Line Amount"
-- 6. User: [Specific User Column]
-- 7. PurchaseOrderNumber: "Payables Invoices - Transactions Real Time"."Matched Purchase Order"."PO Number"
-- 8. DueDate: "Payables Invoices - Transactions Real Time"."Invoice Installments"."Due Date"
-- 9. PaymentDate: "Payables Payments - Disbursements Real Time"."Payment Details"."Payment Date"
-- 10. InvoiceStatus: "Payables Invoices - Transactions Real Time"."Invoice Details"."Invoice Validation Status"
-- 11. EndTime: [Calculated as MAX(EventTime) over InvoiceId later]

-- Block 1: Invoice Created
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Created' AS ActivityName,
  "Invoice Details"."Invoice Creation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Invoice Creation Date" IS NOT NULL

UNION ALL

-- Block 2: Invoice Validated
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Validated' AS ActivityName,
  "Invoice Details"."Validation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Validation Date" IS NOT NULL

UNION ALL

-- Block 3: Matching Performed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Matching Performed' AS ActivityName,
  "Invoice Details"."PO Matched Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."PO Matched Date" IS NOT NULL

UNION ALL

-- Block 4: Hold Placed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Hold Placed' AS ActivityName,
  "Invoice Holds"."Hold Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Holds"."Hold Date" IS NOT NULL

UNION ALL

-- Block 5: Hold Released
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Hold Released' AS ActivityName,
  "Invoice Holds"."Release Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Holds"."Release Date" IS NOT NULL

UNION ALL

-- Block 6: Approval Initiated
-- Note: This is often the workflow start date, which might be the same as creation.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Approval Initiated' AS ActivityName,
  "Invoice Details"."Workflow Initiated Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Status" <> 'Not Required'
  AND "Invoice Details"."Workflow Initiated Date" IS NOT NULL

UNION ALL

-- Block 7: Invoice Approved
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Approved' AS ActivityName,
  "Invoice Details"."Workflow Approved Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Approved Date" IS NOT NULL

UNION ALL

-- Block 8: Invoice Rejected
-- Note: A specific rejection date field may not exist; use the last update date when status is 'Rejected'.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Rejected' AS ActivityName,
  "General Information"."Last Update Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Status" = 'Rejected'

UNION ALL

-- Block 9: Payment Scheduled
-- Uses the Payment subject area and joins back to Invoice.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Scheduled' AS ActivityName,
  "Payment Process Request"."Request Creation Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Process Request"."Request Creation Date" IS NOT NULL

UNION ALL

-- Block 10: Payment Executed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Executed' AS ActivityName,
  "Payment Details"."Payment Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Details"."Payment Date" IS NOT NULL

UNION ALL

-- Block 11: Early Payment Taken
-- Note: Requires a formula column or filter condition.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Early Payment Taken' AS ActivityName,
  "Payment Details"."Payment Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Details"."Payment Date" IS NOT NULL
  AND "Invoice Installments"."Discount Date 1" IS NOT NULL
  AND "Payment Details"."Payment Date" <= "Invoice Installments"."Discount Date 1"

UNION ALL

-- Block 12: Payment Cleared
-- Uses the Cash Management subject area.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Cleared' AS ActivityName,
  "Bank Statement Line"."Cleared Date" AS EventTime,
  ...
FROM "Cash Management - Bank Statement Reconciliation Real Time"
WHERE "Bank Statement Line"."Cleared Date" IS NOT NULL

UNION ALL

-- Block 13: Invoice Cancelled
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Cancelled' AS ActivityName,
  "Invoice Details"."Cancellation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Cancellation Date" IS NOT NULL

Steps

Navigate to OTBI: Log in to Oracle Fusion Cloud Applications. Use the Navigator menu to go to Tools > Reports and Analytics. This will open the Business Intelligence catalog.
Create New Analysis: Click Create > Analysis. You will be prompted to select a Subject Area.
Select Primary Subject Area: Choose the Payables Invoices - Transactions Real Time subject area. This will be the basis for most invoice-related activities.
Build the First Activity Report (Invoice Created): In the 'Criteria' tab, drag and drop the required columns for the event log (e.g., "Invoice Details"."Invoice Number" for InvoiceId, "Supplier"."Supplier Name" for VendorName, etc.). Create a custom text column for the ActivityName and enter the formula 'Invoice Created'. Use "Invoice Details"."Invoice Creation Date" for the EventTime. Add a filter to ensure the creation date is not null.
Combine Analyses for Subsequent Activities: At the bottom of the 'Criteria' tab, click the Combine results based on a union, intersection, or difference operation with another analysis button. Select Union. This will create a new results block.
Add Additional Invoice Activities: For each subsequent activity (e.g., 'Invoice Validated', 'Matching Performed', 'Hold Placed', 'Hold Released', 'Invoice Cancelled'), repeat the process. Select the same set of columns in the same order. Change the ActivityName literal (e.g., 'Invoice Validated') and map the EventTime to the corresponding date field (e.g., "Invoice Details"."Validation Date", "Invoice Holds"."Hold Date"). Add a filter to ensure that the specific date field for that activity is not null.
Incorporate Payment and Other Subject Areas: For payment activities ('Payment Scheduled', 'Payment Executed', 'Payment Cleared'), you must add additional Subject Areas. Go to the 'Criteria' tab, click the Add / Remove Subject Areas icon (folder with a plus sign) in the Subject Areas pane, and add Payables Payments - Disbursements Real Time and Cash Management - Bank Statement Reconciliation Real Time. Create new unioned analyses using fields from these subject areas, ensuring the column structure matches the invoice analyses.
Define Report Filters and Prompts: Navigate to the 'Prompts' tab. Create prompts for key filters like Business Unit Name, Ledger Name, and a date range (e.g., on "Invoice Details"."Invoice Creation Date"). This allows users to control the data scope when running the report.
Configure Results and View Data: Go to the 'Results' tab. The combined data from all the unioned analyses will be displayed. It's recommended to create a simple table view. Add a filter to the report to exclude rows where EventTime is null to ensure every row represents a valid activity.
Run and Export the Report: Save the analysis with a descriptive name. Run the report by clicking Open. Once the data is displayed, use the Export link at the bottom of the page to export the event log to CSV format. This file is ready for upload into a process mining tool.

Configuration

Subject Areas: The report must be built by combining multiple subject areas. The primary ones are:
- Payables Invoices - Transactions Real Time: For invoice creation, validation, holds, approvals, and cancellation.
- Payables Payments - Disbursements Real Time: For payment scheduling and execution events.
- Cash Management - Bank Statement Reconciliation Real Time: For the 'Payment Cleared' event.
Report Structure: A UNION query structure is mandatory. Each activity must be a separate analysis block combined with the others using the UNION operator to produce a single event log table.
Date Range Filtering: It is critical to apply a date range filter to limit the data volume. A recommended starting point is 3-6 months of data, filtering on the "Invoice Details"."Invoice Creation Date".
Key Data Filters: To manage performance and relevance, always filter by:
- Business Unit Name
- Ledger Name
- Source (e.g., to include or exclude specific invoice sources like 'Manual' or 'EDI')
Prerequisites: The user running the report needs a role with access to the specified subject areas, such as Accounts Payable Manager, Accounts Payable Specialist, and BI Administrator role for building reports.

a Sample Query config

-- OTBI Analysis Configuration for Accounts Payable Event Log
-- This is a logical representation of an OTBI UNION report.
-- Each block represents a separate analysis combined with a UNION operation.
-- The column order and data types must be identical in each block.

-- Base Columns (ensure these are selected in this order for every block):
-- 1. InvoiceId: "Payables Invoices - Transactions Real Time"."Invoice Details"."Invoice Number"
-- 2. ActivityName: 'Literal String Value'
-- 3. EventTime: [Specific Date/Time Column]
-- 4. VendorName: "Payables Invoices - Transactions Real Time"."Supplier"."Supplier Name"
-- 5. InvoiceAmount: "Payables Invoices - Transactions Real Time"."Invoice Lines"."Line Amount"
-- 6. User: [Specific User Column]
-- 7. PurchaseOrderNumber: "Payables Invoices - Transactions Real Time"."Matched Purchase Order"."PO Number"
-- 8. DueDate: "Payables Invoices - Transactions Real Time"."Invoice Installments"."Due Date"
-- 9. PaymentDate: "Payables Payments - Disbursements Real Time"."Payment Details"."Payment Date"
-- 10. InvoiceStatus: "Payables Invoices - Transactions Real Time"."Invoice Details"."Invoice Validation Status"
-- 11. EndTime: [Calculated as MAX(EventTime) over InvoiceId later]

-- Block 1: Invoice Created
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Created' AS ActivityName,
  "Invoice Details"."Invoice Creation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Invoice Creation Date" IS NOT NULL

UNION ALL

-- Block 2: Invoice Validated
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Validated' AS ActivityName,
  "Invoice Details"."Validation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Validation Date" IS NOT NULL

UNION ALL

-- Block 3: Matching Performed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Matching Performed' AS ActivityName,
  "Invoice Details"."PO Matched Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."PO Matched Date" IS NOT NULL

UNION ALL

-- Block 4: Hold Placed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Hold Placed' AS ActivityName,
  "Invoice Holds"."Hold Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Holds"."Hold Date" IS NOT NULL

UNION ALL

-- Block 5: Hold Released
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Hold Released' AS ActivityName,
  "Invoice Holds"."Release Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Holds"."Release Date" IS NOT NULL

UNION ALL

-- Block 6: Approval Initiated
-- Note: This is often the workflow start date, which might be the same as creation.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Approval Initiated' AS ActivityName,
  "Invoice Details"."Workflow Initiated Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Status" <> 'Not Required'
  AND "Invoice Details"."Workflow Initiated Date" IS NOT NULL

UNION ALL

-- Block 7: Invoice Approved
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Approved' AS ActivityName,
  "Invoice Details"."Workflow Approved Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Approved Date" IS NOT NULL

UNION ALL

-- Block 8: Invoice Rejected
-- Note: A specific rejection date field may not exist; use the last update date when status is 'Rejected'.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Rejected' AS ActivityName,
  "General Information"."Last Update Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Status" = 'Rejected'

UNION ALL

-- Block 9: Payment Scheduled
-- Uses the Payment subject area and joins back to Invoice.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Scheduled' AS ActivityName,
  "Payment Process Request"."Request Creation Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Process Request"."Request Creation Date" IS NOT NULL

UNION ALL

-- Block 10: Payment Executed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Executed' AS ActivityName,
  "Payment Details"."Payment Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Details"."Payment Date" IS NOT NULL

UNION ALL

-- Block 11: Early Payment Taken
-- Note: Requires a formula column or filter condition.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Early Payment Taken' AS ActivityName,
  "Payment Details"."Payment Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Details"."Payment Date" IS NOT NULL
  AND "Invoice Installments"."Discount Date 1" IS NOT NULL
  AND "Payment Details"."Payment Date" <= "Invoice Installments"."Discount Date 1"

UNION ALL

-- Block 12: Payment Cleared
-- Uses the Cash Management subject area.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Cleared' AS ActivityName,
  "Bank Statement Line"."Cleared Date" AS EventTime,
  ...
FROM "Cash Management - Bank Statement Reconciliation Real Time"
WHERE "Bank Statement Line"."Cleared Date" IS NOT NULL

UNION ALL

-- Block 13: Invoice Cancelled
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Cancelled' AS ActivityName,
  "Invoice Details"."Cancellation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Cancellation Date" IS NOT NULL

Steps

Prerequisites: Ensure you have Python 3.6+ installed along with the requests library (pip install requests). You will also need valid user credentials (username and password) for an Oracle Fusion Financials user with the necessary privileges to read invoice and payment data via the REST API (e.g., roles like 'Payables Inquiry' and 'Integration Specialist').
Identify your Oracle Fusion Host: Determine the base URL for your Oracle Fusion Cloud instance's REST API. It typically looks like https://[hostname].fa.em2.oraclecloud.com.
Configure the Python Script: Copy the provided Python script into a file (e.g., extract_invoices.py). Edit the script to replace placeholder values for ORACLE_HOST, USERNAME, and PASSWORD with your specific credentials and host. Adjust the START_DATE and END_DATE to define the desired extraction window.
Understand API Parameters: The script uses the /fscmRestApi/resources/11.13.18.05/invoices endpoint. It filters invoices by CreationDate using the q parameter. It also uses the fields parameter to request specific invoice attributes and expand to include child objects like holds and paymentSchedules in a single call, which is crucial for efficiency.
Execute the Script: Run the script from your command line using python extract_invoices.py. The script will authenticate with the API, send requests, and handle pagination automatically.
Monitor the Progress: The script will print progress messages to the console, indicating which page of results it is currently fetching. This helps monitor the extraction process for large datasets.
Retrieve Child Data: For each invoice returned by the API, the script processes the main invoice data and iterates through nested arrays for holds and paymentSchedules to generate the corresponding activity rows ('Hold Placed', 'Hold Released', 'Payment Scheduled', etc.).
Data Transformation Logic: The script contains logic to transform the JSON data into a flat, event-log structure. For each invoice, it generates multiple rows—one for each business activity (e.g., 'Invoice Created', 'Invoice Approved'). It checks for the existence of dates or specific statuses to decide whether to create an event.
Generate the Output File: Once the script has fetched all invoice data within the specified date range, it compiles all the generated events and writes them into a single CSV file named oracle_fusion_ap_event_log.csv in the same directory where the script was run.
Review and Upload: Open the generated CSV file in a spreadsheet application to review its structure and content. Ensure all required columns (InvoiceId, ActivityName, EventTime) are present and populated. The file is now ready to be uploaded to ProcessMind.

Configuration

API Endpoint: The script primarily targets the invoices REST API resource. The example uses version 11.13.18.05: /fscmRestApi/resources/11.13.18.05/invoices.
Authentication: The script uses Basic Authentication. It is highly recommended to use a dedicated integration user account with the minimum required permissions to read invoice, supplier, and payment data.
Date Range Filtering: The extraction period is controlled by the START_DATE and END_DATE variables in the script. It's recommended to start with a smaller range (e.g., 3-6 months) to manage the data volume and extraction time. The filter is applied on the CreationDate of the invoice.
Data Scope (Fields & Expansion): The script uses the fields parameter to request only necessary attributes and the expand parameter to eagerly fetch related child objects (holds, paymentSchedules, payments). This is critical for performance as it reduces the number of API calls needed.
Pagination: Oracle's REST API uses limit and offset for pagination. The script is configured with a LIMIT of 500 records per request, which is a common and efficient batch size. The script automatically handles looping through all pages of results.
Prerequisites: Access to an Oracle Fusion Financials Cloud environment is mandatory. The user account must be granted appropriate security roles to access the Payables Invoices REST API. A Python 3 environment is required to execute the script.

a Sample Query python

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

class=class="hl-string">"hl-comment"># --- Configuration Settings --- 
class=class="hl-string">"hl-comment"># Replace with your Oracle Fusion instance details
ORACLE_HOST = class="hl-string">"https://[your-fusion-host].fa.em2.oraclecloud.com"
USERNAME = class="hl-string">"[your_api_user]"
PASSWORD = class="hl-string">"[your_api_password]"

class=class="hl-string">"hl-comment"># --- Extraction Parameters ---
class=class="hl-string">"hl-comment"># Date range for invoice creation date
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-12-31"

class=class="hl-string">"hl-comment"># API request parameters
LIMIT = 500
API_ENDPOINT = fclass="hl-string">"{ORACLE_HOST}/fscmRestApi/resources/11.13.18.05/invoices"
OUTPUT_FILE = class="hl-string">"oracle_fusion_ap_event_log.csv"

def process_invoice(invoice):
    class="hl-string">"""Processes a single invoice JSON object and returns a list of event dictionaries."""
    events = []
    
    class=class="hl-string">"hl-comment"># Common attributes for all events related to this invoice
    common_attrs = {
        class="hl-string">'InvoiceId': invoice.get(class="hl-string">'InvoiceId'),
        class="hl-string">'VendorName': invoice.get(class="hl-string">'Supplier'),
        class="hl-string">'InvoiceAmount': invoice.get(class="hl-string">'InvoiceAmount'),
        class="hl-string">'PurchaseOrderNumber': invoice.get(class="hl-string">'PoNumber'),
        class="hl-string">'DueDate': invoice.get(class="hl-string">'DueDate'),
        class="hl-string">'InvoiceStatus': invoice.get(class="hl-string">'InvoiceStatus'),
        class="hl-string">'User': None, class=class="hl-string">"hl-comment"># Often not available per event in summary APIs
        class="hl-string">'PaymentDate': None,
        class="hl-string">'EndTime': None
    }

    class=class="hl-string">"hl-comment"># Activity: Invoice Created
    if invoice.get(class="hl-string">'CreationDate'):
        events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Created',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'CreationDate'),
            class="hl-string">'User': invoice.get(class="hl-string">'CreatedBy')
        })

    class=class="hl-string">"hl-comment"># Activity: Invoice Validated
    if invoice.get(class="hl-string">'ValidatedDate'):
        events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Validated',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'ValidatedDate')
        })

    class=class="hl-string">"hl-comment"># Activity: Matching Performed (using ValidationDate as a proxy if no specific match date is available)
    if invoice.get(class="hl-string">'MatchStatus') in [class="hl-string">'Success', class="hl-string">'Partial'] and invoice.get(class="hl-string">'ValidatedDate'):
        events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Matching Performed',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'ValidatedDate') class=class="hl-string">"hl-comment"># Proxy timestamp
        })

    class=class="hl-string">"hl-comment"># Activities from Holds
    if invoice.get(class="hl-string">'holds'):
        for hold in invoice[class="hl-string">'holds']:
            if hold.get(class="hl-string">'HoldDate'):
                events.append({
                    **common_attrs,
                    class="hl-string">'ActivityName': class="hl-string">'Hold Placed',
                    class="hl-string">'EventTime': hold.get(class="hl-string">'HoldDate'),
                    class="hl-string">'User': hold.get(class="hl-string">'HeldBy')
                })
            if hold.get(class="hl-string">'ReleaseDate'):
                events.append({
                    **common_attrs,
                    class="hl-string">'ActivityName': class="hl-string">'Hold Released',
                    class="hl-string">'EventTime': hold.get(class="hl-string">'ReleaseDate'),
                    class="hl-string">'User': hold.get(class="hl-string">'ReleasedBy')
                })

    class=class="hl-string">"hl-comment"># Activities from Approval Status
    if invoice.get(class="hl-string">'ApprovalStatus') == class="hl-string">'Initiated' and invoice.get(class="hl-string">'LastUpdateDate'):
         events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Approval Initiated',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'LastUpdateDate') class=class="hl-string">"hl-comment"># Proxy timestamp
        })
    if invoice.get(class="hl-string">'ApprovalStatus') == class="hl-string">'Approved' and invoice.get(class="hl-string">'ApprovedDate'):
         events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Approved',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'ApprovedDate')
        })
    if invoice.get(class="hl-string">'ApprovalStatus') == class="hl-string">'Rejected' and invoice.get(class="hl-string">'LastUpdateDate'):
         events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Rejected',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'LastUpdateDate') class=class="hl-string">"hl-comment"># Proxy timestamp
        })

    class=class="hl-string">"hl-comment"># Activities from Payment Schedules and Payments
    final_payment_date = None
    if invoice.get(class="hl-string">'paymentSchedules'):
        for schedule in invoice[class="hl-string">'paymentSchedules']:
            class=class="hl-string">"hl-comment"># Activity: Payment Scheduled
            events.append({
                **common_attrs,
                class="hl-string">'ActivityName': class="hl-string">'Payment Scheduled',
                class="hl-string">'EventTime': schedule.get(class="hl-string">'CreationDate') or invoice.get(class="hl-string">'ApprovedDate') class=class="hl-string">"hl-comment"># Use schedule creation or invoice approval date
            })

            if schedule.get(class="hl-string">'payments'):
                for payment in schedule[class="hl-string">'payments']:
                    if payment.get(class="hl-string">'PaymentDate'):
                        final_payment_date = payment.get(class="hl-string">'PaymentDate')
                        class=class="hl-string">"hl-comment"># Activity: Payment Executed
                        events.append({
                            **common_attrs,
                            class="hl-string">'ActivityName': class="hl-string">'Payment Executed',
                            class="hl-string">'EventTime': payment.get(class="hl-string">'PaymentDate'),
                            class="hl-string">'PaymentDate': payment.get(class="hl-string">'PaymentDate')
                        })

                        class=class="hl-string">"hl-comment"># Activity: Early Payment Taken (Calculated)
                        discount_date = schedule.get(class="hl-string">'firstDiscountDate')
                        if discount_date and payment.get(class="hl-string">'PaymentDate') <= discount_date:
                            events.append({
                                **common_attrs,
                                class="hl-string">'ActivityName': class="hl-string">'Early Payment Taken',
                                class="hl-string">'EventTime': payment.get(class="hl-string">'PaymentDate')
                            })

                    class=class="hl-string">"hl-comment"># Activity: Payment Cleared
                    if payment.get(class="hl-string">'clearedDate'):
                        events.append({
                            **common_attrs,
                            class="hl-string">'ActivityName': class="hl-string">'Payment Cleared',
                            class="hl-string">'EventTime': payment.get(class="hl-string">'clearedDate')
                        })

    class=class="hl-string">"hl-comment"># Activity: Invoice Cancelled
    if invoice.get(class="hl-string">'CancelledDate'):
        events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Cancelled',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'CancelledDate'),
            class="hl-string">'User': invoice.get(class="hl-string">'CancelledBy')
        })

    class=class="hl-string">"hl-comment"># Calculate EndTime as the timestamp of the last event
    if events:
        end_time = max(e[class="hl-string">'EventTime'] for e in events if e.get(class="hl-string">'EventTime'))
        for e in events:
            e[class="hl-string">'EndTime'] = end_time
            if not e.get(class="hl-string">'PaymentDate'): class=class="hl-string">"hl-comment"># Backfill payment date if available
                e[class="hl-string">'PaymentDate'] = final_payment_date

    return events

def main():
    class="hl-string">"""Main function to fetch data and write to CSV."""
    all_events = []
    offset = 0
    has_more = True

    class=class="hl-string">"hl-comment"># Prepare headers for the API request
    headers = {
        class="hl-string">'Content-Type': class="hl-string">'application/vnd.oracle.adf.resourceitem+json'
    }
    auth = (USERNAME, PASSWORD)

    class=class="hl-string">"hl-comment"># Define fields to retrieve for efficiency
    fields_to_get = [
        class="hl-string">'InvoiceId', class="hl-string">'InvoiceNumber', class="hl-string">'InvoiceAmount', class="hl-string">'CreationDate', class="hl-string">'CreatedBy',
        class="hl-string">'Supplier', class="hl-string">'PoNumber', class="hl-string">'DueDate', class="hl-string">'InvoiceStatus', class="hl-string">'ValidatedDate',
        class="hl-string">'MatchStatus', class="hl-string">'ApprovalStatus', class="hl-string">'ApprovedDate', class="hl-string">'LastUpdateDate',
        class="hl-string">'CancelledDate', class="hl-string">'CancelledBy',
        class="hl-string">'holds(HoldDate,HeldBy,ReleaseDate,ReleasedBy)',
        class="hl-string">'paymentSchedules(CreationDate,firstDiscountDate,payments(PaymentDate,clearedDate))'
    ]

    print(class="hl-string">"Starting Oracle Fusion AP Invoice data extraction...")

    while has_more:
        params = {
            class="hl-string">'q': fclass="hl-string">"CreationDate between '{START_DATE}' and '{END_DATE}'",
            class="hl-string">'onlyData': class="hl-string">'true',
            class="hl-string">'limit': LIMIT,
            class="hl-string">'offset': offset,
            class="hl-string">'fields': class="hl-string">','.join(fields_to_get),
            class="hl-string">'expand': class="hl-string">'holds,paymentSchedules.payments'
        }

        try:
            print(fclass="hl-string">"Fetching records from offset {offset}...")
            response = requests.get(API_ENDPOINT, auth=auth, headers=headers, params=params, timeout=120)
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raise an exception for bad status codes

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

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

            for invoice in invoices:
                all_events.extend(process_invoice(invoice))
            
            has_more = data.get(class="hl-string">'hasMore', False)
            offset += len(invoices)

        except requests.exceptions.HTTPError as http_err:
            print(fclass="hl-string">"HTTP error occurred: {http_err} - {response.text}")
            break
        except requests.exceptions.RequestException as req_err:
            print(fclass="hl-string">"Request error occurred: {req_err}")
            break
        except json.JSONDecodeError:
            print(fclass="hl-string">"Failed to decode JSON from response: {response.text}")
            break

    if not all_events:
        print(class="hl-string">"No events were generated. The output file will not be created.")
        return

    class=class="hl-string">"hl-comment"># Write to CSV
    print(fclass="hl-string">"\nWriting {len(all_events)} events to {OUTPUT_FILE}...")
    fieldnames = [
        class="hl-string">'InvoiceId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'VendorName', class="hl-string">'InvoiceAmount',
        class="hl-string">'User', class="hl-string">'PurchaseOrderNumber', class="hl-string">'DueDate', class="hl-string">'PaymentDate', class="hl-string">'InvoiceStatus', class="hl-string">'EndTime'
    ]
    
    try:
        with open(OUTPUT_FILE, 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(all_events)
        print(class="hl-string">"Extraction complete.")
    except IOError as io_err:
        print(fclass="hl-string">"Error writing to file: {io_err}")

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

Steps

Navigate to BI Publisher: Log in to Oracle Fusion Applications and use the Navigator menu to go to Tools > Reports and Analytics. Click the Browse Catalog button to open the Business Intelligence Publisher interface.
Create a Data Model: In the BI Publisher catalog, click New > Data Model. This will open the data model editor.
Create a SQL Query Data Set: In the Data Sets panel on the left, click the + icon and select SQL Query. A dialog box for creating the new data set will appear.
Enter the SQL Query: In the New Data Set - SQL Query dialog, give your data set a name (e.g., AP_Invoice_Events_DS). Copy the complete SQL query provided in the query section of this document and paste it into the SQL Query text area.
Define Parameters: The query uses parameters :P_START_DATE and :P_END_DATE to filter the date range. BI Publisher will automatically detect these. You can set default values or make them mandatory for users running the report.
Save the Data Model: Click OK to close the data set dialog. Go to the Data tab, enter your parameters, and click View to test the query and see a sample of the data. Once validated, save the data model in a shared folder (e.g., Shared Folders/Custom/Financials).
Create a Report: Navigate back to the catalog, click New > Report. Select Use Existing Data Model, find and select the data model you just saved, and proceed with the report creation wizard.
Configure Report Layout: In the report editor, the wizard will prompt you to design the layout. Choose the Table layout for a simple, flat structure. Drag and drop all columns from the data set into the table.
Set Report Properties: After creating the layout, click the Properties button. Under the Formatting tab, ensure the CSV (Data) output format is enabled. This format is ideal for process mining ingestion.
Save the Report: Save the report in the same custom folder where you saved the data model.
Schedule the Report: To automate the extraction, schedule the report as a job. Open the report, click Actions > Schedule. Configure the schedule frequency (e.g., daily), enter the parameter values for the date range, and under the Output tab, ensure CSV is selected.
Configure Delivery: In the Destination tab of the scheduler, choose where to deliver the file. Common options include the Content Server, an external FTP server, or email. The file can then be retrieved from this location for upload into your process mining tool.

Configuration

Prerequisites: A user with BI Administrator or Application Developer role is required to create and manage data models and reports.
Data Model Parameters: The query is designed with two mandatory parameters to control the data volume:
- :P_START_DATE: The start date for the invoice creation timestamp (format 'YYYY-MM-DD').
- :P_END_DATE: The end date for the invoice creation timestamp (format 'YYYY-MM-DD').
Date Range Recommendation: For initial extraction, select a representative period of 3-6 months. For ongoing extractions, schedule daily or weekly runs covering the recent period.
Query Filtering: The primary filtering is done in the BaseInvoices CTE. You can add more conditions to the WHERE clause to restrict the extraction to specific business units (BU.BU_NAME), suppliers, or invoice types (AIA.INVOICE_TYPE_LOOKUP_CODE).
Performance Considerations: This is a comprehensive query joining multiple large tables. For environments with high transaction volumes, running the report during off-peak hours is highly recommended to avoid impacting system performance. Ensure the date range is manageable.
Output Format: Configure the BI Publisher report job to generate a CSV file. This format is simple, text-based, and easily parsed by most process mining platforms. Ensure the CSV output does not have any formatting or aggregation applied by the report layout.

a Sample Query sql

WITH BaseInvoices AS (
    -- Select base invoice data and apply primary filters here
    SELECT 
        AIA.INVOICE_ID,
        AIA.INVOICE_NUM,
        AIA.INVOICE_AMOUNT,
        AIA.CREATION_DATE AS INVOICE_CREATION_DATE,
        AIA.CANCELLED_DATE,
        AIA.WFAPPROVAL_STATUS AS INVOICE_STATUS,
        AIA.LAST_UPDATE_DATE AS INVOICE_LAST_UPDATE_DATE,
        AIA.LAST_UPDATED_BY AS INVOICE_LAST_UPDATED_BY,
        AIA.CREATED_BY AS INVOICE_CREATED_BY,
        PSV.VENDOR_NAME,
        (SELECT LISTAGG(PHA.SEGMENT1, ', ') WITHIN GROUP (ORDER BY PHA.PO_HEADER_ID) 
         FROM AP_INVOICE_LINES_ALL AILA 
         JOIN PO_HEADERS_ALL PHA ON AILA.PO_HEADER_ID = PHA.PO_HEADER_ID
         WHERE AILA.INVOICE_ID = AIA.INVOICE_ID) AS PurchaseOrderNumber
    FROM 
        AP_INVOICES_ALL AIA
    JOIN 
        POZ_SUPPLIERS_V PSV ON AIA.VENDOR_ID = PSV.VENDOR_ID
    JOIN
        FUN_ALL_BUSINESS_UNITS_V BU ON AIA.ORG_ID = BU.BU_ID
    WHERE 
        AIA.CREATION_DATE BETWEEN TO_TIMESTAMP(:P_START_DATE, 'YYYY-MM-DD') AND TO_TIMESTAMP(:P_END_DATE, 'YYYY-MM-DD HH24:MI:SS')
        -- Add additional filters here if needed, e.g.:
        -- AND BU.BU_NAME = '[Your Business Unit Name]'
),
EventLog_Pre AS (
    -- 1. Invoice Created
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Invoice Created' AS "ActivityName",
        BI.INVOICE_CREATION_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN PER_USERS PU ON BI.INVOICE_CREATED_BY = PU.USER_GUID

    UNION ALL

    -- 2. Invoice Validated
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Invoice Validated' AS "ActivityName",
        APH.CREATION_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_APPROVAL_HISTORY_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.CREATED_BY = PU.USERNAME
    WHERE APH.RESPONSE = 'VALIDATED'

    UNION ALL

    -- 3. Matching Performed
    -- This uses the last update date when match status becomes 'A' (Tested)
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Matching Performed' AS "ActivityName",
        AID.LAST_UPDATE_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_INVOICE_DISTRIBUTIONS_ALL AID ON BI.INVOICE_ID = AID.INVOICE_ID
    JOIN PER_USERS PU ON AID.LAST_UPDATED_BY = PU.USER_GUID
    WHERE AID.MATCH_STATUS_FLAG = 'A'
    AND ROWNUM = 1 -- To get one event per invoice if multiple lines are matched at different times

    UNION ALL

    -- 4. Hold Placed
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Hold Placed' AS "ActivityName",
        APH.HOLD_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_HOLDS_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.CREATED_BY = PU.USERNAME

    UNION ALL

    -- 5. Hold Released
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Hold Released' AS "ActivityName",
        APH.LAST_UPDATE_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_HOLDS_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.LAST_UPDATED_BY = PU.USER_GUID
    WHERE APH.RELEASE_LOOKUP_CODE IS NOT NULL

    UNION ALL

    -- 6. Approval Initiated
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Approval Initiated' AS "ActivityName",
        APH.CREATION_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_APPROVAL_HISTORY_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.CREATED_BY = PU.USERNAME
    WHERE APH.RESPONSE = 'SUBMITTED'

    UNION ALL

    -- 7. Invoice Approved
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Invoice Approved' AS "ActivityName",
        APH.CREATION_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_APPROVAL_HISTORY_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.CREATED_BY = PU.USERNAME
    WHERE APH.RESPONSE = 'APPROVED'

    UNION ALL

    -- 8. Invoice Rejected
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Invoice Rejected' AS "ActivityName",
        APH.CREATION_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_APPROVAL_HISTORY_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.CREATED_BY = PU.USERNAME
    WHERE APH.RESPONSE = 'REJECTED'

    UNION ALL

    -- 9. Payment Scheduled
    SELECT
        BI.INVOICE_ID AS "InvoiceId",
        'Payment Scheduled' AS "ActivityName",
        APS.LAST_UPDATE_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_PAYMENT_SCHEDULES_ALL APS ON BI.INVOICE_ID = APS.INVOICE_ID
    JOIN PER_USERS PU ON APS.LAST_UPDATED_BY = PU.USER_GUID
    WHERE APS.CHECKRUN_ID IS NOT NULL

    UNION ALL

    -- 10. Payment Executed
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Payment Executed' AS "ActivityName",
        AC.CHECK_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_INVOICE_PAYMENTS_ALL AIP ON BI.INVOICE_ID = AIP.INVOICE_ID
    JOIN AP_CHECKS_ALL AC ON AIP.CHECK_ID = AC.CHECK_ID
    JOIN PER_USERS PU ON AC.LAST_UPDATED_BY = PU.USER_GUID

    UNION ALL

    -- 11. Early Payment Taken
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Early Payment Taken' AS "ActivityName",
        AC.CHECK_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_INVOICE_PAYMENTS_ALL AIP ON BI.INVOICE_ID = AIP.INVOICE_ID
    JOIN AP_CHECKS_ALL AC ON AIP.CHECK_ID = AC.CHECK_ID
    JOIN AP_PAYMENT_SCHEDULES_ALL APS ON AIP.INVOICE_ID = APS.INVOICE_ID AND AIP.PAYMENT_NUM = APS.PAYMENT_NUM
    JOIN PER_USERS PU ON AC.LAST_UPDATED_BY = PU.USER_GUID
    WHERE AC.CHECK_DATE <= APS.DISCOUNT_DATE

    UNION ALL

    -- 12. Payment Cleared
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Payment Cleared' AS "ActivityName",
        AC.CLEARED_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_INVOICE_PAYMENTS_ALL AIP ON BI.INVOICE_ID = AIP.INVOICE_ID
    JOIN AP_CHECKS_ALL AC ON AIP.CHECK_ID = AC.CHECK_ID
    JOIN PER_USERS PU ON AC.LAST_UPDATED_BY = PU.USER_GUID
    WHERE AC.CLEARED_DATE IS NOT NULL

    UNION ALL

    -- 13. Invoice Cancelled
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Invoice Cancelled' AS "ActivityName",
        BI.CANCELLED_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN PER_USERS PU ON BI.INVOICE_LAST_UPDATED_BY = PU.USER_GUID
    WHERE BI.CANCELLED_DATE IS NOT NULL
),
FinalLog AS (
    SELECT 
        E."InvoiceId",
        E."ActivityName",
        E."EventTime",
        E."VendorName",
        E."InvoiceAmount",
        E."User",
        E."PurchaseOrderNumber",
        APS.DUE_DATE AS "DueDate",
        AC.CHECK_DATE AS "PaymentDate",
        AIA.WFAPPROVAL_STATUS AS "InvoiceStatus"
    FROM EventLog_Pre E
    LEFT JOIN AP_INVOICES_ALL AIA ON E."InvoiceId" = AIA.INVOICE_ID
    LEFT JOIN AP_PAYMENT_SCHEDULES_ALL APS ON E."InvoiceId" = APS.INVOICE_ID AND APS.PAYMENT_NUM = 1 -- Assuming first payment schedule for due date
    LEFT JOIN AP_INVOICE_PAYMENTS_ALL AIP ON E."InvoiceId" = AIP.INVOICE_ID AND ROWNUM = 1 -- To get one payment record
    LEFT JOIN AP_CHECKS_ALL AC ON AIP.CHECK_ID = AC.CHECK_ID
)
SELECT 
    F."InvoiceId",
    F."ActivityName",
    F."EventTime",
    F."VendorName",
    F."InvoiceAmount",
    F."User",
    F."PurchaseOrderNumber",
    F."DueDate",
    F."PaymentDate",
    F."InvoiceStatus",
    MAX(F."EventTime") OVER (PARTITION BY F."InvoiceId") AS "EndTime"
FROM FinalLog F
WHERE F."EventTime" IS NOT NULL
ORDER BY F."InvoiceId", F."EventTime";

Data Template: Accounts Payable Invoice Processing

Your Accounts Payable Invoice Processing Data Template

Accounts Payable Invoice Processing Attributes

Accounts Payable Invoice Processing Activities

Extraction Guides

Oracle Transactional Business Intelligence (OTBI) Report

Steps

Configuration

a Sample Query config

Oracle BI Cloud Connector (BICC) PVO Extraction

Steps

Configuration

a Sample Query config

Python Script using Oracle Fusion Financials REST API

Steps

Configuration

a Sample Query python

Oracle BI Publisher SQL-Based Report Extraction

Steps

Configuration

a Sample Query sql

Ready to Get Started?

Cut AP Invoice Processing Costs by 60%: Start Now