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

Your Purchase to Pay - Invoice Processing Data Template

Oracle Fusion Financials

Purchase to Pay - Invoice Processing Attributes (21) Purchase to Pay - Invoice Processing Activities (15) Extraction Guides (3)

Your Purchase to Pay - Invoice Processing Data Template

This template is designed to guide you through collecting the essential data for analyzing your Purchase to Pay, specifically Invoice Processing, within Oracle Fusion Financials. It outlines the crucial attributes, key activities, and provides guidance on how to extract this information from your system. Using this template will help you prepare an accurate and comprehensive event log for 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 - Invoice Processing Attributes

These are the recommended data fields to include in your event log for comprehensive purchase to pay - invoice processing analysis.

3 Required 7 Recommended 11 Optional

	Name	Description
	Invoice Number InvoiceNumber	The unique identifier for a vendor invoice.
Description The Invoice Number serves as the primary case identifier, linking all activities and events related to a single vendor invoice from its creation to final payment. Each invoice is treated as a unique case instance in the process analysis. In process mining, this attribute is fundamental for reconstructing the end-to-end journey of each invoice. It allows for the analysis of process flows, cycle times, and variations on a per-invoice basis. It is the key to connecting different activities like validation, approval, and payment into a coherent process narrative. Why it matters This is the essential case identifier that connects all related process steps, making it possible to trace the entire lifecycle of an invoice. Where to get This is typically found in the AP_INVOICES_ALL table, in the INVOICE_NUM column. Examples INV-2023-001987654321ACME-FIN-5501
	Activity ActivityName	The name of the business activity or event that occurred in the invoice process.
Description This attribute describes a specific step or status change in the invoice lifecycle, such as 'Invoice Created', 'Invoice Approved', or 'Payment Executed'. It forms the sequence of events that constitutes the process flow. Analyzing the sequence and frequency of activities is the core of process mining. It helps in discovering the actual process path, identifying bottlenecks where activities are delayed, and spotting deviations or rework loops, such as an invoice being rejected after approval. Why it matters It defines the steps of the process, which is essential for visualizing the process map, analyzing flow variations, and identifying bottlenecks. Where to get This attribute is typically derived from a combination of status fields, audit tables, or workflow logs within Oracle Fusion Financials, such as AP_INVOICES_ALL.WFAPPROVAL_STATUS or related workflow tables. Examples Invoice ValidatedHold Placed on InvoiceInvoice ApprovedPayment Executed
	Start Time EventTime	The timestamp indicating when an activity or event occurred.
Description This attribute provides the date and time for each activity in the invoice process. It is critical for all time-based process analysis, including calculating cycle times, durations, and waiting times between steps. By ordering events chronologically using this timestamp, process mining tools can reconstruct the exact sequence of activities for each invoice. This enables the calculation of key performance indicators like Average Invoice Cycle Time and helps identify which stages of the process consume the most time. Why it matters This timestamp is crucial for calculating all performance metrics related to duration and time, such as cycle times and bottlenecks. Where to get This timestamp is derived from various date fields in Oracle Fusion tables, such as CREATION_DATE or LAST_UPDATE_DATE in tables like AP_INVOICES_ALL or related workflow and payment tables. Examples 2023-04-15T10:00:00Z2023-04-16T14:35:10Z2023-04-20T09:05:00Z
	Company Code CompanyCode	The identifier for the legal entity or company processing the invoice.
Description The Company Code represents the specific business entity within the organization that is financially responsible for the invoice. In multi-company organizations, this is a fundamental organizational data point. This attribute allows for process analysis to be segmented by legal entity. This is useful for comparing process performance across different parts of the business, identifying entity-specific bottlenecks or compliance issues, and ensuring that KPIs can be reported at a company level. It is also often a factor in determining the correct approval workflow. Why it matters It enables process comparison and performance benchmarking across different legal entities or business units within the organization. Where to get This is typically represented by the LEGAL_ENTITY_ID or similar field in AP_INVOICES_ALL, which can be joined to general ledger tables to get a code or name. Examples 1001US01DE01
	End Time EndTime	The timestamp indicating when an activity or event was completed.
Description The End Time marks the completion of a specific activity. While Start Time signals the beginning, End Time provides the closing point, allowing for precise duration calculation of individual steps. In analysis, the difference between End Time and Start Time gives the processing time for each activity. This is essential for detailed performance analysis, helping to distinguish between active processing time and idle waiting time. For example, it helps measure the actual time an approver spent on an approval task versus the time the task was waiting in their queue. Why it matters It enables the precise calculation of activity processing times, separating active work time from waiting time. Where to get This is a conceptual attribute derived from the Start Time of the subsequent event in the sequence for a given case. Examples 2023-04-15T10:05:12Z2023-04-16T15:00:00Z2023-04-20T09:15:30Z
	Invoice Amount InvoiceAmount	The total monetary value of the invoice.
Description The Invoice Amount represents the total sum due to the vendor as stated on the invoice. This is a critical financial attribute that influences the entire process, often determining the approval path, level of scrutiny, and payment priority. In process mining analysis, Invoice Amount is a key dimension for filtering and segmentation. For example, analysts can compare the process for high-value invoices versus low-value invoices to see if different paths are followed or if cycle times differ significantly. It is also essential for calculating financial KPIs and assessing the monetary impact of process inefficiencies like late payments. Why it matters This value is crucial for financial analysis, understanding process deviations based on value, and for KPIs like Approval Compliance Rate. Where to get Found in the AP_INVOICES_ALL table, in the INVOICE_AMOUNT column. Examples 1500.00250.75125000.50
	Invoice Status InvoiceStatus	The current status of the invoice in its lifecycle.
Description This attribute reflects the last known state of the invoice, such as 'Validated', 'Needs Revalidation', 'Paid', or 'Canceled'. It provides a snapshot of where an invoice is in the process at any given time. Invoice Status is critical for the 'Current Invoice Status Distribution' dashboard, which helps operations managers identify backlogs and monitor the overall health of the invoice processing pipeline. Analyzing how statuses change over time can also provide a simplified view of the process flow. Why it matters It provides a current-state view of invoices, which is essential for operational dashboards that track backlogs and workload. Where to get Consult Oracle Fusion Financials documentation. Status may be derived from fields like WFAPPROVAL_STATUS in AP_INVOICES_ALL or related approval and payment tables. Examples ValidatedPaidCanceledNeeds Revalidation
	Payment Due Date PaymentDueDate	The date by which the invoice payment is due to the vendor.
Description The Payment Due Date is calculated based on the payment terms agreed upon with the vendor. It serves as the deadline for making the payment to avoid penalties, maintain good vendor relationships, and capture potential early payment discounts. This attribute is essential for the 'On-Time Payment Performance' dashboard and the associated KPI. By comparing the actual payment execution timestamp with the Payment Due Date, the analysis can classify payments as on-time or late, helping the organization monitor and improve its payment timeliness. Why it matters This is the baseline for measuring on-time payment performance, a critical KPI for vendor management and financial health. Where to get This date is often available in payment schedule tables like AP_PAYMENT_SCHEDULES_ALL, linked to the invoice. Examples 2023-05-152023-06-012023-06-30
	User Name UserName	The name of the user who performed the activity.
Description This attribute identifies the specific user or system agent responsible for executing an activity, such as validating an invoice, placing a hold, or approving a payment. It provides a human or system resource dimension to the process. Analyzing by user helps in understanding workload distribution, identifying top performers, and detecting potential training needs or compliance issues. For instance, it can reveal if certain users are consistently associated with rework loops or if specific approval steps are always handled by the same individual, creating a potential single point of failure. Why it matters It allows for analysis of resource performance, workload balancing, and identifying which users or teams are involved in specific process steps. Where to get This information is often stored in audit columns like CREATED_BY or LAST_UPDATED_BY in tables such as AP_INVOICES_ALL, or in related workflow logs. Examples john.doejane.smithSystem.Admin
	Vendor Name VendorName	The name of the vendor or supplier who issued the invoice.
Description This attribute identifies the supplier entity that the invoice is from. Vendor information provides critical business context to the financial transaction. Analyzing the process by vendor can reveal important insights into supplier relationships and performance. For example, it can highlight if invoices from certain vendors are more prone to matching discrepancies, holds, or delays. This information can be used to improve vendor onboarding, communication, and overall supply chain efficiency. It's also used in identifying potential duplicate payments. Why it matters It enables vendor-specific process analysis, helping to identify issues with particular suppliers that cause delays or exceptions. Where to get Found in the POZ_SUPPLIERS table. The invoice table AP_INVOICES_ALL contains a VENDOR_ID which can be used to join with the supplier table. Examples Acme CorporationGlobal Tech Inc.Office Supplies Co.
	Approver Name ApproverName	The name of the person who approved or rejected the invoice.
Description This attribute captures the identity of the individual who took action during the approval step. This is recorded for activities like 'Invoice Approved' or 'Invoice Rejected'. In analysis, the Approver Name is used to understand approval workloads, measure individual approver cycle times, and audit compliance with approval policies. For the 'Approval Compliance Rate' KPI, this attribute can be checked against delegation of authority rules based on the invoice amount and company code. Why it matters This is essential for analyzing approval cycle times, ensuring compliance with approval matrix policies, and understanding workload distribution. Where to get This information resides in the Oracle Fusion workflow or approval history tables associated with the invoice object. Examples David WilsonSarah JohnsonMichael Brown
	Currency InvoiceCurrencyCode	The currency of the invoice amount.
Description This attribute specifies the currency in which the invoice is denominated, for example, USD, EUR, or GBP. It is essential context for the Invoice Amount field. In a global organization, analyzing invoices by currency is important for financial reporting and understanding process variations by region. It ensures that monetary values are interpreted correctly and allows for currency-specific analysis of payment practices or approval thresholds. Why it matters Provides necessary context for any financial amounts, ensuring accurate interpretation and analysis, especially in multinational operations. Where to get Found in the AP_INVOICES_ALL table in the INVOICE_CURRENCY_CODE column. Examples USDEURGBPCAD
	Hold Reason HoldReason	The reason why a hold or payment block was placed on an invoice.
Description When an invoice is put on hold, this attribute specifies the reason, such as 'Price Mismatch', 'Quantity Discrepancy', or 'Awaiting Goods Receipt'. This provides context for why the normal process flow was interrupted. This attribute is the primary dimension for the 'Payment Block Trends and Analysis' dashboard. By analyzing the frequency of different hold reasons, the business can identify and address the root causes of payment blocks, such as issues with procurement processes or vendor invoicing accuracy, ultimately reducing processing delays. Why it matters It provides the root cause for payment blocks, enabling targeted improvements to reduce the frequency of holds and payment delays. Where to get Hold information is typically stored in the AP_HOLDS_ALL table, which links to the invoice and contains a hold reason or code. Examples Price MismatchQuantity Billed Exceeds Quantity ReceivedInvalid PO Number
	Invoice Date InvoiceDate	The date stated on the vendor's invoice document.
Description This attribute is the date the vendor officially issued the invoice. It is a key piece of information on the source document and is used as the starting point for calculating the payment due date based on agreed payment terms. While not always the start of the internal process, the Invoice Date is crucial context. It is used in combination with Vendor Name and Invoice Amount to help identify potential duplicate invoices. Analyzing the delay between the Invoice Date and the date the invoice is created in the system can also reveal inefficiencies in the mailroom or invoice ingestion process. Why it matters It's a key data point for identifying duplicate invoices and for calculating payment due dates. Where to get This is a standard field in the AP_INVOICES_ALL table, named INVOICE_DATE. Examples 2023-04-102023-05-012023-05-25
	Is On-Time Payment IsOnTimePayment	A boolean flag indicating if the invoice was paid by its due date.
Description This flag is set to true if the payment was executed on or before the specified Payment Due Date, and false otherwise. It provides a simple, clear classification for each paid invoice. This calculated attribute directly supports the On-Time Payment Rate KPI and its corresponding dashboard. It simplifies analysis by allowing users to easily filter, count, and visualize the proportion of late versus on-time payments without performing date comparisons on the fly. This helps to quickly identify the scale of payment delays and track improvements over time. Why it matters This simplifies the analysis of payment timeliness and is the direct input for calculating the On-Time Payment Rate KPI. Where to get This is a calculated attribute derived from comparing the timestamp of the 'Payment Executed' activity with the 'PaymentDueDate' attribute. Examples truefalse
	Is Rework IsRework	A boolean flag that identifies activities that are part of a rework loop.
Description This flag is set to true for activities that indicate a deviation from the ideal process flow, such as 'Invoice Corrected' or a second instance of 'Invoice Validated' after a rejection. It helps to explicitly tag and quantify inefficient process loops. This attribute is essential for the 'Rework and Exception Handling Rate' dashboard. By flagging rework activities, analysts can easily quantify the volume and cost of rework, identify its root causes, and measure the impact of process improvement initiatives aimed at getting things right the first time. Why it matters It explicitly identifies and quantifies rework, making it easier to analyze the frequency, causes, and impact of process inefficiencies. Where to get This is a calculated attribute. The logic is defined during data transformation to identify sequences of activities that constitute rework. Examples truefalse
	Last Data Update LastUpdateDate	Timestamp indicating when the record was last updated in the source system.
Description This attribute reflects the most recent modification time of the underlying data in Oracle Fusion Financials. It is used to manage incremental data loads and ensure the process mining model is up to date. While not directly used for process flow analysis, this technical timestamp is crucial for maintaining data freshness and integrity. It allows data pipelines to efficiently query only new or changed records since the last update, reducing load on the source system. Why it matters Ensures data pipelines can be run efficiently and incrementally, keeping the process analysis current without full reloads. Where to get Commonly found as LAST_UPDATE_DATE in many Oracle Fusion tables, including AP_INVOICES_ALL. Examples 2023-05-20T11:00:00Z2023-05-21T16:45:00Z
	Matching Discrepancy Reason MatchingDiscrepancyReason	The specific reason for a mismatch between an invoice, purchase order, and receipt.
Description This attribute details why an invoice failed the automated matching process. Common reasons include differences in price, quantity, or item codes between the invoice and the corresponding purchase order or goods receipt. This information is vital for the 'Invoice Matching Discrepancy Rate' dashboard. By categorizing and analyzing the reasons for discrepancies, companies can pinpoint systemic issues in their procurement or receiving processes. This allows for corrective actions that can increase the rate of straight-through, touchless invoice processing. Why it matters It explains why invoices fail automated matching, providing insights needed to improve first-time match rates and reduce manual rework. Where to get Consult Oracle Fusion Financials documentation. This may be recorded as a specific type of hold reason in AP_HOLDS_ALL or in related matching detail tables. Examples Unit price differs from POQuantity invoiced > quantity receivedInvalid item on invoice
	Processing Time ProcessingTime	The duration of time spent on a single activity.
Description Processing time measures the active work duration for a specific process step. It is calculated as the difference between the End Time and Start Time of an activity. This calculated metric is vital for performance analysis, as it helps distinguish active processing time from idle waiting time. For instance, it can show that an approval activity took 5 minutes of active work but was waiting in a queue for 3 days. This insight allows managers to focus improvement efforts on either reducing work time through training or automation, or reducing wait time through better workload management. Why it matters It isolates the active work time for an activity, helping to differentiate between inefficient tasks and long waiting periods. Where to get This is a calculated attribute, derived by subtracting the StartTime from the EndTime (EndTime - StartTime). Examples PT5MPT1H30MP2D
	Purchase Order Number PurchaseOrderNumber	The identifier of the purchase order associated with the invoice.
Description This attribute links the invoice to the corresponding purchase order (PO) that authorized the procurement of the goods or services. Invoices can be PO-backed or non-PO. Analyzing by PO provides a link back to the procurement part of the P2P process. It helps in understanding how compliant the procurement process is and whether issues in invoicing, such as matching discrepancies, originate from problems with the initial PO. The presence or absence of a PO number is a primary way to segment and analyze different invoice processing paths. Why it matters It links the invoice back to the procurement process and is a key attribute for analyzing PO vs. non-PO invoice flows. Where to get This information is available by linking the invoice line items in AP_INVOICE_LINES_ALL to the PO distributions via the PO_DISTRIBUTION_ID. Examples PO-2023-5001600789PO-FIN-9981
	Source System SourceSystem	Identifies the originating system where the event data was recorded.
Description This attribute specifies the source application or module that generated the data, such as Oracle Fusion Financials. In environments with multiple integrated systems, this field helps distinguish the origin of different process steps. Understanding the source system is valuable for data validation, troubleshooting, and analyzing process variations that may be specific to a particular system. It ensures clarity in complex IT landscapes where an invoice's journey may span multiple applications. Why it matters It provides context about data origin, which is important for data governance, troubleshooting, and analyzing system-specific process behavior. Where to get This is often a static value added during the data extraction process to label the dataset's origin. Examples Oracle Fusion FinancialsOracle Payables Cloud

Required Recommended Optional

Purchase to Pay - Invoice Processing Activities

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

6 Recommended 9 Optional

	Activity	Description
	Invoice Accounted	The invoice is successfully posted to the General Ledger, creating journal entries. This event confirms the financial impact of the invoice has been formally recorded.
Why it matters A critical financial control point and a prerequisite for payment. It confirms the invoice is fully validated, approved, and ready for settlement. Where to get Inferred from the status in the AP_INVOICE_DISTRIBUTIONS_ALL table (ACCRUAL_POSTED_FLAG = 'Y') or by checking for corresponding journal entries in the General Ledger tables like XLA_AE_HEADERS. Capture Inferred from flags in AP_INVOICE_DISTRIBUTIONS_ALL or linked GL entries. Event type inferred
	Invoice Approved	The invoice has been fully approved by all required parties in the workflow. It is now ready for accounting and payment scheduling.
Why it matters A major milestone signifying the successful completion of the approval process. Delays before this step are common bottlenecks. Where to get Inferred from the APPROVAL_STATUS in the AP_INVOICES_ALL table changing to a final approved state like 'Approved' or 'Workflow Approved'. Audit history on this field provides the timestamp. Capture Inferred from AP_INVOICES_ALL.APPROVAL_STATUS change to 'Approved'. Event type inferred
	Invoice Created	Represents the initial creation of an invoice record in the system, either through manual entry, scanning, or electronic submission. This event is typically captured when a new row is inserted into the main invoice table.
Why it matters Marks the beginning of the invoice processing lifecycle. Analyzing the time from this event helps understand data entry efficiency and overall process start delays. Where to get In Oracle Fusion Financials, this corresponds to the creation timestamp of the record in the AP_INVOICES_ALL table, specifically the CREATION_DATE column. Capture Record creation timestamp in AP_INVOICES_ALL. Event type explicit
	Invoice Matched to PO	This activity indicates the successful matching of an invoice line to a corresponding Purchase Order line, confirming that the invoiced goods or services were ordered. This is typically an automated or manual action recorded in the system.
Why it matters Crucial for three-way matching, involving the PO, receipt, and invoice. Failures at this stage are a primary source of exceptions and delays. Where to get Can be inferred from the creation of records in the AP_INVOICE_DISTRIBUTIONS_ALL table linking the invoice to a PO distribution (PO_DISTRIBUTION_ID). The line level match status in AP_INVOICE_LINES_ALL can also be used. Capture Inferred from the population of PO distribution data on invoice lines. Event type inferred
	Invoice Sent for Approval	The invoice is submitted into an approval workflow, based on configured business rules. This marks the start of the formal approval cycle.
Why it matters Initiates a critical part of the process. Tracking this start time is essential for measuring and optimizing the 'Invoice Approval Cycle Time'. Where to get Inferred from a status change in the AP_INVOICES_ALL table, where APPROVAL_STATUS moves to a state like 'Initiated' or 'Pending Approval'. The workflow tables may also contain this event. Capture Inferred from AP_INVOICES_ALL.APPROVAL_STATUS change to 'Initiated'. Event type inferred
	Payment Executed	The final confirmation that the payment has been made and cleared. For checks, this would be the cleared date, and for electronic payments, it's the confirmation from the bank.
Why it matters This is the final activity in the invoice lifecycle, marking the successful completion of the process. It's the endpoint for calculating 'Average Invoice Cycle Time' and 'On-Time Payment Rate'. Where to get Inferred from the payment status being updated to 'Cleared' or 'Reconciled' in the AP_CHECKS_ALL table (using the CLEARED_DATE) or through data from bank reconciliation in the Cash Management module (CE_STATEMENT_LINES). Capture Inferred from status updates in AP_CHECKS_ALL or reconciliation in CE module. Event type inferred
	Hold Placed on Invoice	A generic activity representing any hold being placed on an invoice, preventing it from proceeding to payment. This can be for various reasons like matching issues, insufficient funds, or manual intervention.
Why it matters Directly impacts cycle time and can lead to late payments. Analyzing holds is key to identifying and resolving systemic process issues, supporting the 'Payment Block Trends' analysis. Where to get Explicitly logged in the AP_HOLDS_ALL table. Each row represents a hold with a creation date (HOLD_DATE) and reason. Capture Creation of a record in the AP_HOLDS_ALL table. Event type explicit
	Hold Released from Invoice	This event marks the resolution of a hold that was previously placed on the invoice. A user or an automated process takes action to remove the block, allowing the invoice to continue its lifecycle.
Why it matters The time between a hold being placed and released is a critical measure of exception handling efficiency. This supports the 'Average Exception Resolution Time' KPI. Where to get Logged in the AP_HOLDS_ALL table. When a hold is released, the RELEASE_LOOKUP_CODE and RELEASE_REASON fields are populated along with a LAST_UPDATE_DATE. Capture Update of a record in the AP_HOLDS_ALL table marking the hold as released. Event type explicit
	Invoice Canceled	The invoice has been voided or canceled and will not be processed further or paid. This represents a terminal end state for the process.
Why it matters An important endpoint for invoices that do not result in payment. Analyzing cancellations can reveal issues with duplicate invoices or incorrect vendor submissions. Where to get Inferred from a status change in AP_INVOICES_ALL. The CANCELLED_DATE column will be populated with the cancellation timestamp. Capture The CANCELLED_DATE field in the AP_INVOICES_ALL table is populated. Event type explicit
	Invoice Corrected	Occurs when a user modifies an invoice, often in response to a rejection or to fix a data entry error. This represents a manual rework step in the process.
Why it matters This activity is a clear indicator of rework. Analyzing its frequency helps quantify process inefficiency and supports the 'Invoice Rework Rate' KPI. Where to get Can be inferred by tracking significant updates to the invoice record in AP_INVOICES_ALL after it has already been validated or submitted for approval. The LAST_UPDATE_DATE timestamp, along with audit trail data, would be required. Capture Inferred by comparing LAST_UPDATE_DATE after a rejection or hold. Event type inferred
	Invoice Rejected	An approver has rejected the invoice during the approval workflow. This action typically sends the invoice back for correction or cancellation, initiating a rework loop.
Why it matters Represents a negative outcome and a key driver of rework and increased cycle times. Tracking rejections helps identify issues with invoice quality or PO compliance. Where to get Inferred from the APPROVAL_STATUS in the AP_INVOICES_ALL table changing to 'Rejected'. The workflow history will contain details about who rejected it and when. Capture Inferred from AP_INVOICES_ALL.APPROVAL_STATUS change to 'Rejected'. Event type inferred
	Invoice Validated	Signifies that the invoice has passed the system's validation checks for completeness and correctness of header and line information. This is often inferred from a status change on the invoice record.
Why it matters A key milestone before matching and approval. Delays here can indicate issues with invoice data quality or system configuration. Where to get Inferred from the invoice status changing to 'Validated' in the AP_INVOICES_ALL table. The change history or audit trail for the VALIDATION_STATUS column can be used. Capture Inferred from change in AP_INVOICES_ALL.VALIDATION_STATUS field. Event type inferred
	Matching Discrepancy Identified	Occurs when the system or a user identifies a mismatch between the invoice, purchase order, and receipt information, such as price or quantity. This often results in a system-placed hold on the invoice.
Why it matters Highlights process exceptions that require manual intervention. Tracking these events is essential for the 'Invoice Matching Discrepancy Rate' KPI and root cause analysis. Where to get This is often logged as a specific type of hold being placed on the invoice. Check the AP_HOLDS_ALL table for hold types related to matching, for example, 'QTY REC' or 'PRICE'. The HOLD_DATE indicates the timestamp. Capture Creation of a record in AP_HOLDS_ALL with a matching-related hold type. Event type explicit
	Payment Created	The payment instruction for the invoice has been generated by the system. This could be creating a check, an electronic funds transfer (EFT) file, or another payment instrument.
Why it matters This is the point where the organization commits the funds for payment. It's a key milestone just before the money leaves the bank account. Where to get Recorded in the AP_CHECKS_ALL table, which stores information about all payments. The CHECK_DATE indicates when the payment document was created. Capture Creation of a record in the AP_CHECKS_ALL table. Event type explicit
	Payment Scheduled	The invoice is selected and included in a payment process request, often called a payment run or payment batch. It is now queued for payment.
Why it matters The step between accounting and actual payment. The duration of this stage impacts cash flow forecasting and the ability to capture early payment discounts. Where to get This is recorded in the AP_INVOICE_PAYMENTS_ALL table when a payment record is created but not yet confirmed as paid. It can also be found in payment batch tables like IBY_PAYMENT_PROCESS_REQUESTS. Capture Creation of a record in AP_INVOICE_PAYMENTS_ALL for a scheduled payment. Event type explicit

Recommended Optional

Extraction Guides

How to get your data from Oracle Fusion Financials

Steps

Log into Oracle Fusion Cloud: Access your Oracle Fusion Cloud environment with a user who has the BI Cloud Connector Administrator role (BICC_ADMIN).
Navigate to BI Cloud Connector: From the main navigation menu, go to Tools > BI Cloud Connector. This will open the BICC console.
Select Offering: In the BICC console, choose the offering that corresponds to your Financials data. This is typically named 'Financials'.
Configure External Storage: Navigate to the 'Configure External Storage' section. Set up the connection to your cloud storage where the extracted CSV files will be delivered, for example, Oracle Cloud Infrastructure (OCI) Object Storage.
Enable Offerings for Extraction: Go to the 'Manage Offerings and PVOs' or a similar section. Select the 'Financials' offering and enable it for extraction.
Select Public View Objects (PVOs): Search for and select the specific PVOs required for the invoice process. The key PVOs are: FscmTopModelAM.FinApInvQuickInvoicesAM.InvoiceHeaderPVO, FscmTopModelAM.FinApInvHoldAM.InvoiceHoldPVO, FscmTopModelAM.FinApInvSharedAM.InvoiceApprovalHistoryPVO, and FscmTopModelAM.FinApPmtsInvPmtsAM.PaymentPVO.
Schedule the Extraction Job: Navigate to 'Manage Extract Schedules'. Create a new job, give it a descriptive name, and select all the PVOs you enabled in the previous step. Schedule the job to run. For the initial load, run it for the desired historical date range. For subsequent runs, configure it to perform incremental extracts based on the LastUpdateDate.
Monitor and Retrieve Data: Monitor the job's progress in the BICC console. Once it completes successfully, navigate to your configured cloud storage location. You will find the extracted data as a set of compressed CSV files, one for each PVO.
Load Data into Staging Area: Unzip the files and load the CSVs into a staging database or data processing platform (like a data warehouse or data lake) where you can run SQL queries. Each CSV file should be loaded into its own table.
Transform Data into Event Log: Execute the provided SQL query against the tables in your staging area. This query unions data from the different tables to create a single, unified event log table.
Export for ProcessMind: Export the result of the SQL query as a single CSV file. Ensure the column headers match the required attributes (InvoiceNumber, ActivityName, EventTime, etc.) for upload into the ProcessMind application.

Configuration

Key Public View Objects (PVOs): The extraction relies on several core PVOs from the Financials offering. At a minimum, you must include:
- FscmTopModelAM.FinApInvQuickInvoicesAM.InvoiceHeaderPVO: For core invoice data, creation, validation, and cancellation events.
- FscmTopModelAM.FinApInvHoldAM.InvoiceHoldPVO: For all invoice hold and release events.
- FscmTopModelAM.FinApInvSharedAM.InvoiceApprovalHistoryPVO: To capture detailed approval workflow steps like submission, approval, and rejection.
- FscmTopModelAM.FinApPmtsInvPmtsAM.PaymentPVO: For payment creation and execution events.
- FscmTopModelAM.FinApInvPmtsAM.InstallmentPVO: To identify when an invoice is scheduled for payment.
Incremental Extracts: For ongoing monitoring, configure the BICC job to perform incremental extracts. Use the LastUpdateDate column available in most PVOs as the filter criteria to extract only new or changed records since the last run.
Date Range Filtering: For the initial historical data load, it is recommended to extract a manageable period, such as the last 6 to 12 months of data. You can apply a global filter in the BICC job schedule to restrict the extraction to a specific date range.
Business Unit Filter: To limit the scope of the extraction, apply a filter on BusinessUnitName or a similar attribute within the BICC job configuration. This is crucial in multi-entity environments to manage data volume.
Prerequisites: Your user account needs the appropriate roles, primarily BICC Administrator (BICC_ADMIN) and Functional Setup User (FSM_IMPLEMENTATION_CONSULTANT), to configure and run extraction jobs. You must also have a configured and accessible cloud storage location (like OCI Object Storage, UCM).

a Sample Query config

-- This script is designed to be run on the CSV files after they have been extracted by BICC and loaded into a staging database.
-- Each CTE (e.g., Invoices, Holds) represents a table created from a corresponding PVO's CSV file.

WITH Invoices AS (
    SELECT 
        InvoiceId,
        InvoiceNumber,
        InvoiceAmount,
        SupplierName AS VendorName,
        CreationDate,
        CreatedBy AS UserName, 
        LastUpdateDate, 
        LastUpdatedBy, 
        ValidatedDate,
        ApprovalStatus,
        WfApprovalStatus,
        PayablesAccountingStatus,
        CancelledDate,
        CancelledBy,
        BusinessUnitName AS CompanyCode
    FROM FscmTopModelAM_FinApInvQuickInvoicesAM_InvoiceHeaderPVO
    WHERE LastUpdateDate >= '[Specify Start Date]'
),
Holds AS (
    SELECT 
        InvoiceId,
        HoldDate,
        ReleasedDate,
        HoldReason,
        CreatedBy,
        LastUpdatedBy
    FROM FscmTopModelAM_FinApInvHoldAM_InvoiceHoldPVO
),
ApprovalHistory AS (
    SELECT
        InvoiceId,
        ActionDate,
        Action,
        Performer AS UserName
    FROM FscmTopModelAM_FinApInvSharedAM_InvoiceApprovalHistoryPVO
),
Installments AS (
    SELECT 
        InvoiceId,
        DueDate AS PaymentDueDate,
        PaymentStatusFlag,
        LastUpdateDate
    FROM FscmTopModelAM_FinApInvPmtsAM_InstallmentPVO
),
Payments AS (
    SELECT
        InvoiceId,
        PaymentId,
        CreationDate,
        CreatedBy AS UserName,
        PaymentDate
    FROM FscmTopModelAM_FinApPmtsInvPmtsAM_PaymentPVO
)

-- 1. Invoice Created
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Invoice Created' AS "ActivityName",
    i.CreationDate AS "EventTime",
    i.CreationDate AS "EndTime",
    i.UserName AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    i.ApprovalStatus AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId

UNION ALL

-- 2. Invoice Validated
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Invoice Validated' AS "ActivityName",
    i.ValidatedDate AS "EventTime",
    i.ValidatedDate AS "EndTime",
    i.LastUpdatedBy AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'Validated' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE i.ValidatedDate IS NOT NULL

UNION ALL

-- 3. Invoice Matched to PO (Inferred from hold release)
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Invoice Matched to PO' AS "ActivityName",
    h.ReleasedDate AS "EventTime",
    h.ReleasedDate AS "EndTime",
    h.LastUpdatedBy AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    i.ApprovalStatus AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
JOIN Holds h ON i.InvoiceId = h.InvoiceId
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE h.HoldReason IN ('QTY ORD', 'AMT ORD', 'PRICE') AND h.ReleasedDate IS NOT NULL

UNION ALL

-- 4. Matching Discrepancy Identified
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Matching Discrepancy Identified' AS "ActivityName",
    h.HoldDate AS "EventTime",
    h.HoldDate AS "EndTime",
    h.CreatedBy AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'On Hold' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
JOIN Holds h ON i.InvoiceId = h.InvoiceId
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE h.HoldReason IN ('QTY ORD', 'AMT ORD', 'PRICE')

UNION ALL

-- 5. Hold Placed on Invoice
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Hold Placed on Invoice' AS "ActivityName",
    h.HoldDate AS "EventTime",
    h.HoldDate AS "EndTime",
    h.CreatedBy AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'On Hold' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
JOIN Holds h ON i.InvoiceId = h.InvoiceId
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId

UNION ALL

-- 6. Hold Released from Invoice
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Hold Released from Invoice' AS "ActivityName",
    h.ReleasedDate AS "EventTime",
    h.ReleasedDate AS "EndTime",
    h.LastUpdatedBy AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    i.ApprovalStatus AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
JOIN Holds h ON i.InvoiceId = h.InvoiceId
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE h.ReleasedDate IS NOT NULL

UNION ALL

-- 7. Invoice Sent for Approval
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Invoice Sent for Approval' AS "ActivityName",
    ah.ActionDate AS "EventTime",
    ah.ActionDate AS "EndTime",
    ah.UserName AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'Initiated' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
JOIN ApprovalHistory ah ON i.InvoiceId = ah.InvoiceId
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE ah.Action = 'SUBMITTED'

UNION ALL

-- 8. Invoice Approved
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Invoice Approved' AS "ActivityName",
    ah.ActionDate AS "EventTime",
    ah.ActionDate AS "EndTime",
    ah.UserName AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'Workflow approved' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
JOIN ApprovalHistory ah ON i.InvoiceId = ah.InvoiceId
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE ah.Action = 'APPROVED'

UNION ALL

-- 9. Invoice Rejected
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Invoice Rejected' AS "ActivityName",
    ah.ActionDate AS "EventTime",
    ah.ActionDate AS "EndTime",
    ah.UserName AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'Rejected' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
JOIN ApprovalHistory ah ON i.InvoiceId = ah.InvoiceId
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE ah.Action = 'REJECTED'

UNION ALL

-- 10. Invoice Corrected (Inferred from update after rejection)
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Invoice Corrected' AS "ActivityName",
    i.LastUpdateDate AS "EventTime",
    i.LastUpdateDate AS "EndTime",
    i.LastUpdatedBy AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    i.ApprovalStatus AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE i.WfApprovalStatus = 'WORKFLOW_REJECTED' AND i.LastUpdatedBy <> 'FUSION_APPS_PRC_SOA_APPID'

UNION ALL

-- 11. Invoice Accounted
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Invoice Accounted' AS "ActivityName",
    i.LastUpdateDate AS "EventTime",
    i.LastUpdateDate AS "EndTime",
    i.LastUpdatedBy AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'Accounted' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE i.PayablesAccountingStatus = 'ACCOUNTED'

UNION ALL

-- 12. Payment Scheduled
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Payment Scheduled' AS "ActivityName",
    inst.LastUpdateDate AS "EventTime",
    inst.LastUpdateDate AS "EndTime",
    i.LastUpdatedBy AS "UserName", -- User who ran the PPR is not in this PVO
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'Selected for Payment' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE inst.PaymentStatusFlag = 'Y'

UNION ALL

-- 13. Payment Created
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Payment Created' AS "ActivityName",
    p.CreationDate AS "EventTime",
    p.CreationDate AS "EndTime",
    p.UserName AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'Paid' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
JOIN Payments p ON i.InvoiceId = p.InvoiceId
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId

UNION ALL

-- 14. Payment Executed
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Payment Executed' AS "ActivityName",
    p.PaymentDate AS "EventTime",
    p.PaymentDate AS "EndTime",
    p.UserName AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'Paid' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
JOIN Payments p ON i.InvoiceId = p.InvoiceId
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE p.PaymentDate IS NOT NULL

UNION ALL

-- 15. Invoice Canceled
SELECT
    i.InvoiceNumber AS "InvoiceNumber",
    'Invoice Canceled' AS "ActivityName",
    i.CancelledDate AS "EventTime",
    i.CancelledDate AS "EndTime",
    i.CancelledBy AS "UserName",
    i.InvoiceAmount AS "InvoiceAmount",
    i.VendorName AS "VendorName",
    inst.PaymentDueDate AS "PaymentDueDate",
    'Cancelled' AS "InvoiceStatus",
    i.CompanyCode AS "CompanyCode"
FROM Invoices i
LEFT JOIN Installments inst ON i.InvoiceId = inst.InvoiceId
WHERE i.CancelledDate IS NOT NULL;

Steps

Access Oracle BI Publisher: Log in to Oracle Fusion Applications with a user that has BI Administrator or a similar role. Navigate to the Reports and Analytics tool by opening the Navigator menu, then selecting Tools, and finally clicking on Reports and Analytics.
Navigate to the Catalog: Inside the Reports and Analytics work area, click the Browse Catalog button to open the Oracle BI Publisher interface.
Create a New Data Model: In the BI Publisher catalog, navigate to a suitable folder (e.g., Shared Folders > Custom). Click the New dropdown menu and select Data Model.
Define the SQL Query Data Set: In the Data Model editor, click the '+' icon to add a new data set and select SQL Query from the list.
Configure the Data Set: In the New Data Set dialog, provide a name such as P2P_Invoice_Events. Select the appropriate Data Source, which is typically ApplicationDB_FSCM for financial data. Ensure the Type of SQL is set to Standard SQL.
Enter the SQL Query: Copy the complete SQL provided in the query section of this document and paste it into the SQL Query text area.
Define Parameters: The query uses parameters for filtering. In the Data Model properties, create three parameters: p_start_date, p_end_date, and p_bu_name. Set their data types to String. The user will provide values for these when running the report, for example '2023-01-01' for dates and the specific business unit name.
Save the Data Model: Save the data model in your custom folder. You can view sample data by clicking the 'Data' tab and providing values for the parameters you created.
Create a Report: Navigate back to the catalog, click the New dropdown menu, and select Report. In the report creation wizard, select the 'Use Data Model' option and find the data model you just saved.
Configure Report Layout and Output: In the report editor, a simple table layout is sufficient. The most important step is to configure the default output format. Click on 'View a List' and ensure that CSV is the default format and is the only one enabled. This ensures the output is a clean, unformatted data file.
Schedule the Report: Save the report. You can now run it on demand or, for better performance, schedule it to run during off-peak hours. The schedule can be configured to deliver the CSV output to a specific destination, such as the Universal Content Management (UCM) server, from where it can be retrieved for upload.
Export and Prepare Data: Once the report job completes, download the resulting CSV file. Verify that the column headers match the required attributes (InvoiceNumber, ActivityName, EventTime, etc.) before uploading it to the process mining tool.

Configuration

Data Source: The query must be run against the Fusion Financials application database, typically connected via the ApplicationDB_FSCM JNDI data source in BI Publisher.
Parameters: The data model should be configured with the following parameters to allow for flexible data extraction:
- p_start_date: The start date for the extraction period in 'YYYY-MM-DD' format.
- p_end_date: The end date for the extraction period in 'YYYY-MM-DD' format.
- p_bu_name: The name of the Business Unit (Company Code) to filter the data. Use a wildcard character like '%' to extract for all business units.
Date Range: For the initial extraction, it is recommended to use a date range of 3 to 6 months of data. This provides a balance between having a representative dataset and managing the extraction performance.
Performance Considerations: Extracting data for long periods or for all business units at once can be resource-intensive and may lead to timeouts. It is recommended to run large extractions in smaller, sequential batches (e.g., month by month) or schedule them during off-peak hours.
Prerequisites: The user creating and running the report needs a role with permissions to access the BI Publisher tool (e.g., BI Administrator) and the necessary data security privileges to view the underlying Accounts Payable tables (AP_INVOICES_ALL, AP_PAYMENTS_ALL, etc.) for the target business units.

a Sample Query sql

WITH INVOICE_BASE AS (
    SELECT 
        AIA.INVOICE_ID,
        AIA.INVOICE_NUM,
        AIA.INVOICE_AMOUNT,
        AIA.CREATION_DATE,
        AIA.CREATED_BY,
        AIA.LAST_UPDATE_DATE,
        AIA.LAST_UPDATED_BY,
        AIA.CANCELLED_DATE,
        AIA.CANCELLED_BY,
        AIA.WFAPPROVAL_STATUS,
        AIA.TERMS_DATE AS PAYMENT_DUE_DATE,
        PSV.VENDOR_NAME,
        FABUV.BU_NAME AS COMPANY_CODE
    FROM 
        AP_INVOICES_ALL AIA
    JOIN 
        POZ_SUPPLIERS_V PSV ON AIA.VENDOR_ID = PSV.VENDOR_ID
    JOIN
        FUN_ALL_BUSINESS_UNITS_V FABUV ON AIA.ORG_ID = FABUV.BU_ID
    WHERE 
        AIA.CREATION_DATE BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD') AND TO_DATE(:p_end_date, 'YYYY-MM-DD')
        AND FABUV.BU_NAME LIKE :p_bu_name
)
-- 1. Invoice Created
SELECT
    IB.INVOICE_NUM AS "InvoiceNumber",
    'Invoice Created' AS "ActivityName",
    CAST(IB.CREATION_DATE AS TIMESTAMP) AS "EventTime",
    CAST(IB.CREATION_DATE AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = IB.CREATED_BY AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM
    INVOICE_BASE IB

UNION ALL

-- 2. Invoice Validated
SELECT
    IB.INVOICE_NUM AS "InvoiceNumber",
    'Invoice Validated' AS "ActivityName",
    CAST(AIA.LAST_UPDATE_DATE AS TIMESTAMP) AS "EventTime",
    CAST(AIA.LAST_UPDATE_DATE AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = AIA.LAST_UPDATED_BY AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    AIA.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM
    AP_INVOICES_ALL AIA
JOIN 
    INVOICE_BASE IB ON AIA.INVOICE_ID = IB.INVOICE_ID
WHERE 
    AIA.WFAPPROVAL_STATUS IN ('VALIDATED', 'NEEDS REVALIDATION', 'STOPPED')

UNION ALL

-- 3. Invoice Matched to PO
SELECT
    IB.INVOICE_NUM AS "InvoiceNumber",
    'Invoice Matched to PO' AS "ActivityName",
    CAST(MIN(AID.CREATION_DATE) AS TIMESTAMP) AS "EventTime",
    CAST(MIN(AID.CREATION_DATE) AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = MIN(AID.CREATED_BY) AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM
    AP_INVOICE_DISTRIBUTIONS_ALL AID
JOIN 
    INVOICE_BASE IB ON AID.INVOICE_ID = IB.INVOICE_ID
WHERE
    AID.PO_DISTRIBUTION_ID IS NOT NULL
GROUP BY
    IB.INVOICE_NUM, IB.INVOICE_AMOUNT, IB.VENDOR_NAME, IB.PAYMENT_DUE_DATE, IB.WFAPPROVAL_STATUS, IB.COMPANY_CODE

UNION ALL

-- 4. Matching Discrepancy Identified & 5. Hold Placed on Invoice
SELECT
    IB.INVOICE_NUM AS "InvoiceNumber",
    CASE 
        WHEN AH.HOLD_LOOKUP_CODE IN ('QTY ORD', 'QTY REC', 'PRICE', 'AMT ORD', 'AMT REC') THEN 'Matching Discrepancy Identified'
        ELSE 'Hold Placed on Invoice'
    END AS "ActivityName",
    CAST(AH.HOLD_DATE AS TIMESTAMP) AS "EventTime",
    CAST(AH.HOLD_DATE AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = AH.CREATED_BY AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM
    AP_HOLDS_ALL AH
JOIN 
    INVOICE_BASE IB ON AH.INVOICE_ID = IB.INVOICE_ID

UNION ALL

-- 6. Hold Released from Invoice
SELECT
    IB.INVOICE_NUM AS "InvoiceNumber",
    'Hold Released from Invoice' AS "ActivityName",
    CAST(AH.RELEASE_DATE AS TIMESTAMP) AS "EventTime",
    CAST(AH.RELEASE_DATE AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = AH.LAST_UPDATED_BY AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM
    AP_HOLDS_ALL AH
JOIN 
    INVOICE_BASE IB ON AH.INVOICE_ID = IB.INVOICE_ID
WHERE 
    AH.RELEASE_LOOKUP_CODE IS NOT NULL AND AH.RELEASE_DATE IS NOT NULL

UNION ALL

-- 7. Invoice Sent for Approval, 8. Invoice Approved, 9. Invoice Rejected
SELECT
    IB.INVOICE_NUM AS "InvoiceNumber",
    CASE 
        WHEN AAH.ACTION = 'INITIATED' THEN 'Invoice Sent for Approval'
        WHEN AAH.ACTION = 'APPROVED' THEN 'Invoice Approved'
        WHEN AAH.ACTION = 'REJECTED' THEN 'Invoice Rejected'
    END AS "ActivityName",
    CAST(AAH.RESPONSE_DATE AS TIMESTAMP) AS "EventTime",
    CAST(AAH.RESPONSE_DATE AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = AAH.RESPONSE_ID AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM
    AP_INV_APRVL_HIST_ALL AAH
JOIN 
    INVOICE_BASE IB ON AAH.INVOICE_ID = IB.INVOICE_ID
WHERE
    AAH.ACTION IN ('INITIATED', 'APPROVED', 'REJECTED') AND AAH.RESPONSE_DATE IS NOT NULL

UNION ALL

-- 10. Invoice Corrected
SELECT DISTINCT
    IB.INVOICE_NUM AS "InvoiceNumber",
    'Invoice Corrected' AS "ActivityName",
    CAST(IB.LAST_UPDATE_DATE AS TIMESTAMP) AS "EventTime",
    CAST(IB.LAST_UPDATE_DATE AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = IB.LAST_UPDATED_BY AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM
    INVOICE_BASE IB
WHERE
    IB.LAST_UPDATE_DATE > IB.CREATION_DATE
    AND IB.LAST_UPDATED_BY <> IB.CREATED_BY 
    AND EXISTS (
        SELECT 1 FROM AP_INV_APRVL_HIST_ALL AAH 
        WHERE AAH.INVOICE_ID = IB.INVOICE_ID AND AAH.ACTION = 'REJECTED' AND IB.LAST_UPDATE_DATE > AAH.RESPONSE_DATE
    )

UNION ALL

-- 11. Invoice Accounted
SELECT 
    IB.INVOICE_NUM AS "InvoiceNumber",
    'Invoice Accounted' AS "ActivityName",
    CAST(MIN(XAH.ACCOUNTING_DATE) AS TIMESTAMP) AS "EventTime",
    CAST(MIN(XAH.ACCOUNTING_DATE) AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = MIN(XAH.CREATED_BY) AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM XLA_AE_HEADERS XAH
JOIN XLA_TRANSACTION_ENTITIES XTE ON XAH.ENTITY_ID = XTE.ENTITY_ID
JOIN INVOICE_BASE IB ON XTE.SOURCE_ID_INT_1 = IB.INVOICE_ID
WHERE XTE.APPLICATION_ID = 200
AND XTE.ENTITY_CODE = 'AP_INVOICES'
AND XAH.ACCOUNTING_ENTRY_STATUS_CODE = 'F' -- Final
GROUP BY
    IB.INVOICE_NUM, IB.INVOICE_AMOUNT, IB.VENDOR_NAME, IB.PAYMENT_DUE_DATE, IB.WFAPPROVAL_STATUS, IB.COMPANY_CODE

UNION ALL

-- 12. Payment Scheduled
SELECT 
    IB.INVOICE_NUM AS "InvoiceNumber",
    'Payment Scheduled' AS "ActivityName",
    CAST(MIN(APISC.CREATION_DATE) AS TIMESTAMP) AS "EventTime",
    CAST(MIN(APISC.CREATION_DATE) AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = MIN(APISC.CREATED_BY) AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM AP_INV_SELECTION_CRITERIA_ALL APISC
JOIN AP_INVOICES_ALL AIA ON APISC.CHECKRUN_NAME = AIA.PAYMENT_STATUS_FLAG
JOIN INVOICE_BASE IB ON AIA.INVOICE_ID = IB.INVOICE_ID
GROUP BY
    IB.INVOICE_NUM, IB.INVOICE_AMOUNT, IB.VENDOR_NAME, IB.PAYMENT_DUE_DATE, IB.WFAPPROVAL_STATUS, IB.COMPANY_CODE

UNION ALL

-- 13. Payment Created
SELECT 
    IB.INVOICE_NUM AS "InvoiceNumber",
    'Payment Created' AS "ActivityName",
    CAST(AIPA.CREATION_DATE AS TIMESTAMP) AS "EventTime",
    CAST(AIPA.CREATION_DATE AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = AIPA.CREATED_BY AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM AP_INVOICE_PAYMENTS_ALL AIPA
JOIN INVOICE_BASE IB ON AIPA.INVOICE_ID = IB.INVOICE_ID

UNION ALL

-- 14. Payment Executed
SELECT 
    IB.INVOICE_NUM AS "InvoiceNumber",
    'Payment Executed' AS "ActivityName",
    CAST(AC.CLEARED_DATE AS TIMESTAMP) AS "EventTime",
    CAST(AC.CLEARED_DATE AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = AC.LAST_UPDATED_BY AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM AP_INVOICE_PAYMENTS_ALL AIPA
JOIN AP_CHECKS_ALL AC ON AIPA.CHECK_ID = AC.CHECK_ID
JOIN INVOICE_BASE IB ON AIPA.INVOICE_ID = IB.INVOICE_ID
WHERE AC.CLEARED_DATE IS NOT NULL

UNION ALL

-- 15. Invoice Canceled
SELECT
    IB.INVOICE_NUM AS "InvoiceNumber",
    'Invoice Canceled' AS "ActivityName",
    CAST(IB.CANCELLED_DATE AS TIMESTAMP) AS "EventTime",
    CAST(IB.CANCELLED_DATE AS TIMESTAMP) AS "EndTime",
    (SELECT USERNAME FROM PER_USERS PU WHERE PU.USER_ID = IB.CANCELLED_BY AND ROWNUM = 1) AS "UserName",
    IB.INVOICE_AMOUNT AS "InvoiceAmount",
    IB.VENDOR_NAME AS "VendorName",
    IB.PAYMENT_DUE_DATE AS "PaymentDueDate",
    IB.WFAPPROVAL_STATUS AS "InvoiceStatus",
    IB.COMPANY_CODE AS "CompanyCode"
FROM
    INVOICE_BASE IB
WHERE
    IB.CANCELLED_DATE IS NOT NULL

Steps

Prerequisites: Ensure you have Python 3.6+ installed on your system, along with the requests and pandas libraries. You can install them using pip: pip install requests pandas.
Oracle Fusion API Setup: In your Oracle Fusion instance, navigate to the Security Console. Create a new confidential application to gain API access. Note the assigned Client ID and Client Secret. Ensure the application user has the necessary roles to read invoice and payment data, such as the Financials - Payables Invoice Inquiry Duty role.
Configure the Python Script: Copy the provided Python script into a file, for example extract_fusion_invoices.py. Open the file and populate the placeholder variables at the top of the script. This includes CLIENT_ID, CLIENT_SECRET, TOKEN_URL, FUSION_HOST, START_DATE, and END_DATE. The FUSION_HOST should be the base URL for your Oracle Cloud applications.
Set Extraction Period: Modify the START_DATE and END_DATE variables to define the time window for the data extraction. It is recommended to start with a smaller period, like one month, to test the script and verify the output.
Execute the Script: Run the script from your command line or terminal by navigating to its directory and typing python extract_fusion_invoices.py. The script will authenticate, fetch the data, and display progress messages in the console.
Authentication Process: The script first sends a request with your client credentials to the Oracle identity provider (TOKEN_URL) to obtain an OAuth access token. This token is then included in the header of all subsequent API requests to authenticate with the Fusion Financials API.
Data Fetching and Pagination: The script queries the invoices REST API endpoint, retrieving records created within your specified date range. It automatically handles pagination by checking the hasMore flag in the API response and making subsequent requests with an updated offset parameter until all invoice data is retrieved.
Detailed Data Retrieval: For each invoice retrieved, the script makes additional API calls to fetch associated details. It queries the /child/holds endpoint for hold and release information and the /payments endpoint (filtered by the invoice number) to gather payment data.
Event Log Generation: The core logic of the script processes the raw JSON data from the API. It checks for specific dates, statuses, and child records to generate distinct rows for each of the 15 required business activities. For example, the presence of a CancelledDate generates an 'Invoice Canceled' event.
Review the Output: Once the script finishes, it will create a file named invoice_event_log.csv in the same directory. Open this CSV file with a spreadsheet application to review the data. Check that all required columns (InvoiceNumber, ActivityName, EventTime) and recommended attributes are present.
Prepare for Upload: Ensure the column headers in the CSV file match the required attribute names. No further formatting is typically needed. The file is now ready to be uploaded to your process mining tool.

Configuration

Authentication Credentials: A Client ID and Client Secret from a confidential application registered in Oracle Fusion's Security Console are required. The Token URL for your identity domain must also be provided.
API Host URL: The FUSION_HOST variable must be set to the base URL of your Oracle Fusion instance, for example, https://[your-instance].fa.us2.oraclecloud.com.
Date Range: The START_DATE and END_DATE variables are critical for controlling the scope of the extraction. It is recommended to extract 3 to 6 months of data for an initial analysis to ensure a balance between data volume and performance.
API Endpoints Used: The script primarily interacts with the fscmRestApi/resources/11.13.18.05/invoices and fscmRestApi/resources/11.13.18.05/payments endpoints. It also uses the /invoices/{InvoiceId}/child/holds sub-resource.
Pagination Limit: The LIMIT variable in the script controls how many records are fetched per API request. The default is 100. A higher value can reduce the number of API calls but increase memory consumption per request. A lower value may be necessary if you encounter timeout errors.
Required Permissions: The user account associated with the API credentials needs read access to Payables invoices, invoice holds, payments, and vendor information. The Payables Invoice Inquiry Duty role typically provides sufficient permissions.

a Sample Query python

import requests
import pandas as pd
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 credentials and instance details
CLIENT_ID = class="hl-string">'[Your Client ID]'
CLIENT_SECRET = class="hl-string">'[Your Client Secret]'
TOKEN_URL = class="hl-string">'[Your Oracle Identity Cloud Service Token URL, e.g., https://idcs-....identity.oraclecloud.com/oauth2/v1/token]'
FUSION_HOST = class="hl-string">'[Your Fusion Host URL, e.g., https://your-fusion-instance.fa.ocs.oraclecloud.com]'

class=class="hl-string">"hl-comment"># --- Extraction Parameters ---
class=class="hl-string">"hl-comment"># Set the date range for the invoice extraction (YYYY-MM-DD)
START_DATE = class="hl-string">'2023-01-01'
END_DATE = class="hl-string">'2023-06-30'

class=class="hl-string">"hl-comment"># API request settings
LIMIT = 100

class=class="hl-string">"hl-comment"># --- Helper Functions ---
def get_access_token():
    class="hl-string">"""Authenticates and retrieves an OAuth2 access token."""
    headers = {class="hl-string">'Content-Type': class="hl-string">'application/x-www-form-urlencoded'}
    data = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials',
        class="hl-string">'scope': class="hl-string">'urn:opc:resource:consumer::all'
    }
    try:
        response = requests.post(TOKEN_URL, headers=headers, data=data, auth=(CLIENT_ID, CLIENT_SECRET))
        response.raise_for_status()
        return response.json().get(class="hl-string">'access_token')
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error getting access token: {e}")
        return None

def fetch_data(api_url, headers, params):
    class="hl-string">"""Fetches data from a given API endpoint with pagination handling."""
    all_items = []
    offset = 0
    has_more = True

    while has_more:
        params[class="hl-string">'offset'] = offset
        try:
            response = requests.get(api_url, headers=headers, params=params)
            response.raise_for_status()
            data = response.json()
            items = data.get(class="hl-string">'items', [])
            all_items.extend(items)
            has_more = data.get(class="hl-string">'hasMore', False)
            offset += len(items)
            print(fclass="hl-string">"Fetched {len(all_items)} records so far...")
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching data from {api_url}: {e}")
            print(fclass="hl-string">"Response content: {response.text}")
            break
    return all_items

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    class="hl-string">"""Main function to extract, transform, and save invoice data."""
    access_token = get_access_token()
    if not access_token:
        print(class="hl-string">"Could not obtain access token. Exiting.")
        return

    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/vnd.oracle.adf.resourceitem+json'
    }

    event_log = []

    class=class="hl-string">"hl-comment"># 1. Fetch Invoices
    print(class="hl-string">"Starting invoice extraction...")
    invoice_url = fclass="hl-string">'{FUSION_HOST}/fscmRestApi/resources/11.13.18.05/invoices'
    invoice_params = {
        class="hl-string">'q': fclass="hl-string">'CreationDate between \'{START_DATE}T00:00:00.000Z\class="hl-string">' and \'{END_DATE}T23:59:59.999Z\class="hl-string">'',
        class="hl-string">'onlyData': class="hl-string">'true',
        class="hl-string">'limit': LIMIT,
        class="hl-string">'fields': class="hl-string">'InvoiceId,InvoiceNumber,InvoiceAmount,CreationDate,CreatedBy,LastUpdateDate,LastUpdatedBy,SupplierName,ValidationDate,CancelledDate,ApprovalStatus,AccountingDate,PaymentStatus,DueDate,InvoiceStatus,BusinessUnitName'
    }
    invoices = fetch_data(invoice_url, headers, invoice_params)
    print(fclass="hl-string">"Total invoices fetched: {len(invoices)}")

    class=class="hl-string">"hl-comment"># 2. Process each invoice to create events
    for inv in invoices:
        case_id = inv.get(class="hl-string">'InvoiceNumber')
        if not case_id:
            continue

        base_event = {
            class="hl-string">'InvoiceNumber': case_id,
            class="hl-string">'EndTime': None,
            class="hl-string">'InvoiceAmount': inv.get(class="hl-string">'InvoiceAmount'),
            class="hl-string">'VendorName': inv.get(class="hl-string">'SupplierName'),
            class="hl-string">'PaymentDueDate': inv.get(class="hl-string">'DueDate'),
            class="hl-string">'InvoiceStatus': inv.get(class="hl-string">'InvoiceStatus'),
            class="hl-string">'CompanyCode': inv.get(class="hl-string">'BusinessUnitName')
        }

        class=class="hl-string">"hl-comment"># Activity: Invoice Created
        if inv.get(class="hl-string">'CreationDate'):
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Invoice Created', class="hl-string">'EventTime': inv[class="hl-string">'CreationDate'], class="hl-string">'UserName': inv.get(class="hl-string">'CreatedBy')})

        class=class="hl-string">"hl-comment"># Activity: Invoice Validated
        if inv.get(class="hl-string">'ValidationDate'):
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Invoice Validated', class="hl-string">'EventTime': inv[class="hl-string">'ValidationDate'], class="hl-string">'UserName': inv.get(class="hl-string">'LastUpdatedBy')})
        
        class=class="hl-string">"hl-comment"># Activities based on Approval Status
        if inv.get(class="hl-string">'ApprovalStatus') in [class="hl-string">'Initiated', class="hl-string">'Required']:
             event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Invoice Sent for Approval', class="hl-string">'EventTime': inv[class="hl-string">'LastUpdateDate'], class="hl-string">'UserName': inv.get(class="hl-string">'LastUpdatedBy')})
        elif inv.get(class="hl-string">'ApprovalStatus') in [class="hl-string">'Approved', class="hl-string">'Not required', class="hl-string">'Workflow approved']:
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Invoice Approved', class="hl-string">'EventTime': inv[class="hl-string">'LastUpdateDate'], class="hl-string">'UserName': inv.get(class="hl-string">'LastUpdatedBy')})
        elif inv.get(class="hl-string">'ApprovalStatus') == class="hl-string">'Rejected':
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Invoice Rejected', class="hl-string">'EventTime': inv[class="hl-string">'LastUpdateDate'], class="hl-string">'UserName': inv.get(class="hl-string">'LastUpdatedBy')})
        
        class=class="hl-string">"hl-comment"># This is an inference, might represent corrections after rejection or validation failure
        if inv.get(class="hl-string">'InvoiceStatus') == class="hl-string">'Needs revalidation':
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Invoice Corrected', class="hl-string">'EventTime': inv[class="hl-string">'LastUpdateDate'], class="hl-string">'UserName': inv.get(class="hl-string">'LastUpdatedBy')})

        class=class="hl-string">"hl-comment"># Activity: Invoice Accounted
        if inv.get(class="hl-string">'AccountingDate'):
             event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Invoice Accounted', class="hl-string">'EventTime': inv[class="hl-string">'AccountingDate'], class="hl-string">'UserName': inv.get(class="hl-string">'LastUpdatedBy')})

        class=class="hl-string">"hl-comment"># Activity: Invoice Canceled
        if inv.get(class="hl-string">'CancelledDate'):
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Invoice Canceled', class="hl-string">'EventTime': inv[class="hl-string">'CancelledDate'], class="hl-string">'UserName': inv.get(class="hl-string">'LastUpdatedBy')})
        
        class=class="hl-string">"hl-comment"># Activity: Matched to PO - Inference based on PaymentStatus which can indicate matching holds resolved
        if inv.get(class="hl-string">'PaymentStatus') == class="hl-string">'Available':
             event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Invoice Matched to PO', class="hl-string">'EventTime': inv.get(class="hl-string">'ValidationDate') or inv.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': inv.get(class="hl-string">'LastUpdatedBy')})

        class=class="hl-string">"hl-comment"># 3. Fetch Holds for the Invoice
        holds_url = fclass="hl-string">'{FUSION_HOST}/fscmRestApi/resources/11.13.18.05/invoices/{inv["InvoiceId"]}/child/holds'
        holds = fetch_data(holds_url, headers, {class="hl-string">'onlyData': class="hl-string">'true'})
        for hold in holds:
            class=class="hl-string">"hl-comment"># Activity: Hold Placed on Invoice
            event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Hold Placed on Invoice', class="hl-string">'EventTime': hold[class="hl-string">'HoldDate'], class="hl-string">'UserName': hold.get(class="hl-string">'LastUpdatedBy')})
            if hold.get(class="hl-string">'HoldReason') in [class="hl-string">'Price', class="hl-string">'Qty Ord', class="hl-string">'Qty Rec', class="hl-string">'Quality']:
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Matching Discrepancy Identified', class="hl-string">'EventTime': hold[class="hl-string">'HoldDate'], class="hl-string">'UserName': hold.get(class="hl-string">'LastUpdatedBy')})
            
            class=class="hl-string">"hl-comment"># Activity: Hold Released from Invoice
            if hold.get(class="hl-string">'ReleaseDate'):
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Hold Released from Invoice', class="hl-string">'EventTime': hold[class="hl-string">'ReleaseDate'], class="hl-string">'UserName': hold.get(class="hl-string">'LastUpdatedBy')})

        class=class="hl-string">"hl-comment"># 4. Fetch Payments for the Invoice
        if inv.get(class="hl-string">'PaymentStatus') in [class="hl-string">'Paid', class="hl-string">'Partially paid']:
            payment_url = fclass="hl-string">'{FUSION_HOST}/fscmRestApi/resources/11.13.18.05/payments'
            payment_params = {
                class="hl-string">'q': fclass="hl-string">'InvoiceNumber=\'{case_id}\class="hl-string">'',
                class="hl-string">'onlyData': class="hl-string">'true',
                class="hl-string">'fields': class="hl-string">'PaymentId,CreationDate,CreatedBy,ClearedDate,PaymentDate'
            }
            payments = fetch_data(payment_url, headers, payment_params)
            for pmt in payments:
                 class=class="hl-string">"hl-comment"># Activity: Payment Scheduled (Approximation using Payment Creation Date)
                 event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Payment Scheduled', class="hl-string">'EventTime': pmt[class="hl-string">'CreationDate'], class="hl-string">'UserName': pmt.get(class="hl-string">'CreatedBy')})
                 class=class="hl-string">"hl-comment"># Activity: Payment Created
                 event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Payment Created', class="hl-string">'EventTime': pmt[class="hl-string">'PaymentDate'], class="hl-string">'UserName': pmt.get(class="hl-string">'CreatedBy')})
                 class=class="hl-string">"hl-comment"># Activity: Payment Executed
                 if pmt.get(class="hl-string">'ClearedDate'):
                     event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Payment Executed', class="hl-string">'EventTime': pmt[class="hl-string">'ClearedDate'], class="hl-string">'UserName': pmt.get(class="hl-string">'CreatedBy')})

    class=class="hl-string">"hl-comment"># 5. Create and Save DataFrame
    if not event_log:
        print(class="hl-string">"No events were generated. Please check your date range and API responses.")
        return

    df = pd.DataFrame(event_log)
    df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime'], inplace=True)
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime']).dt.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
    if class="hl-string">'EndTime' in df.columns:
         df[class="hl-string">'EndTime'] = pd.to_datetime(df[class="hl-string">'EndTime']).dt.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')

    output_filename = class="hl-string">'invoice_event_log.csv'
    df.to_csv(output_filename, index=False)
    print(fclass="hl-string">"Extraction complete. Event log saved to {output_filename}")

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

Your Purchase to Pay - Invoice Processing Data Template

Your Purchase to Pay - Invoice Processing Data Template

Purchase to Pay - Invoice Processing Attributes

Purchase to Pay - Invoice Processing Activities

Extraction Guides

Oracle BI Cloud Connector (BICC) Bulk Data Export

Steps

Configuration

a Sample Query config

Oracle BI Publisher Report with Custom SQL Data Model

Steps

Configuration

a Sample Query sql

Python Script Extraction using Fusion Financials REST API

Steps

Configuration

a Sample Query python

Ready to Get Started?

Optimize Your P2P Invoice Processing Now for 30% Faster Cycles

Your Purchase to Pay - Invoice Processing Data Template

Your Purchase to Pay - Invoice Processing Data Template

Purchase to Pay - Invoice Processing Attributes

Purchase to Pay - Invoice Processing Activities

Extraction Guides

Oracle BI Cloud Connector (BICC) Bulk Data Export

Steps

Configuration

a Sample Query config

Oracle BI Publisher Report with Custom SQL Data Model

Steps

Configuration

a Sample Query sql

Python Script Extraction using Fusion Financials REST API

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Optimize Your P2P Invoice Processing Now for 30% Faster Cycles