> Continuous Process Improvement > Asset Maintenance > Oracle Maintenance Cloud > Data Template

Your Asset Maintenance Data Template

Oracle Maintenance Cloud

Asset Maintenance Attributes (22) Asset Maintenance Activities (15) Extraction Guides (3)

Your Asset Maintenance Data Template

This template provides a structured framework to help you organize your maintenance records from Oracle Maintenance Cloud for advanced process mining. It identifies the necessary data points and process milestones needed to gain full visibility into your equipment upkeep and resource allocation. By following these guidelines, you can build a robust event log that highlights inefficiencies and improves your maintenance strategy.

Recommended attributes for comprehensive analysis
Critical process milestones to monitor
System-specific extraction guidance for Oracle

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

Download Template

Asset Maintenance Attributes

These recommended data fields allow you to categorize and filter your maintenance events to gain deeper insights into equipment performance and resource utilization.

5 Required 8 Recommended 9 Optional

	Name	Description
	Activity ActivityName	The name of the event or status change occurring in the workflow.
Description This attribute captures the specific step performed in the maintenance process, such as 'Work Order Created', 'Operation Started', or 'Work Order Closed'. It represents the nodes in the process map. Analysts use this to visualize the process flow, identify variants, and detect bottlenecks between specific process steps. Why it matters Required to define the process flow and sequence of events. Where to get Derived from transaction history tables or status change logs. Examples Maintenance Work Order CreatedWork Order ReleasedOperation Completed
	Event Timestamp EventDateTime	The date and time when the activity occurred.
Description This attribute records the exact moment an event took place. It is used to order activities chronologically within a case and to calculate the duration between steps. Accurate timestamps are critical for calculating cycle times, identifying delays, and measuring performance against SLAs. Why it matters Essential for establishing the timeline of the process. Where to get CREATION_DATE or LAST_UPDATE_DATE columns in transaction tables. Examples 2023-10-15T08:30:00Z2023-10-15T09:15:00Z2023-10-16T14:00:00Z
	Maintenance Work Order WorkOrderNumber	The unique identifier for the maintenance work order.
Description The Work Order Number serves as the unique case identifier for the maintenance process. It tracks the lifecycle of a maintenance task from the initial request or schedule generation through to execution, completion, and financial closure. In analysis, this attribute is the central key for joining event data with case-level attributes. It allows analysts to group all activities related to a single job and calculate end-to-end cycle times. Why it matters It is the primary key for the process instance, essential for reconstructing the case history. Where to get WIE_WORK_ORDERS_B table, column WORK_ORDER_NUMBER Examples WO-2023-0015WO-2023-0089WO-2023-1102
	Last Data Update LastDataUpdate	Timestamp of the most recent data extraction.
Description The date and time when the data was last extracted or refreshed in the process mining tool. It helps users understand how current the analysis is. This is a system metadata field required for data governance. Why it matters Ensures users know the freshness of the data. Where to get System time at extraction. Examples 2023-10-30T12:00:00Z
	Source System SourceSystem	The name of the system where the data originated.
Description Indicates the source software or database from which the record was extracted. In this context, it is typically 'Oracle Maintenance Cloud'. Essential in multi-system environments to track data lineage. Why it matters Tracks data lineage in multi-system environments. Where to get Hardcoded during extraction. Examples Oracle Maintenance CloudOracle Cloud ERP
	Actual Completion Date ActualCompletionDate	The date and time when the maintenance work was finished.
Description Records the final completion of the physical work. This timestamp is used to close the execution phase of the lifecycle. It is the end point for Technical Execution Cycle Time and start point for Administrative Lag Time. Why it matters Marks the end of the technical execution phase. Where to get WIE_WORK_ORDERS_B table, column COMPLETED_DATE or CLOSED_DATE. Examples 2023-10-252023-10-262023-10-27
	Actual Start Date ActualStartDate	The actual date and time when work commenced.
Description Records when the technician actually started the work. This is often triggered by an 'Operation Started' transaction. Used to calculate the Material Fulfillment Delay (end point) and Resource Scheduling Fluidity. Why it matters Critical for calculating execution duration vs planning. Where to get WIE_WORK_ORDERS_B table, column ACTUAL_START_DATE. Examples 2023-10-212023-10-222023-10-23
	Asset Number AssetNumber	The unique identifier of the equipment being maintained.
Description Identifies the specific physical asset or machine that is the subject of the maintenance work order. It links the process to the asset hierarchy. This is crucial for the Asset Rework Frequency KPI, enabling analysts to spot machines that break down repeatedly. Why it matters Links maintenance activities to specific physical equipment. Where to get WIE_WORK_ORDERS_B table, column ASSET_ID (join to CSI_ITEM_INSTANCES). Examples PUMP-101HVAC-Unit-5CONVEYOR-BELT-A
	Assigned Technician AssignedTechnician	The person or resource group assigned to execute the task.
Description Captures the user or employee ID of the technician responsible for the work. This may be assigned at the header or operation level. Used in Technician Utilization and Labor analysis to identify skill gaps or over-utilized resources. Why it matters Enables resource performance analysis and capacity planning. Where to get WIE_OPERATION_RESOURCES table or similar assignment table. Examples J. SmithTech-005External Contractor
	Maintenance Type WorkOrderType	Categorizes the work order as preventive, corrective, or predictive.
Description This attribute defines the nature of the maintenance work. Common values include Preventive, Corrective, Emergency, or Condition-Based. It is vital for the Proactive Maintenance Ratio KPI, allowing analysts to compare the volume of planned work versus unplanned reactions to failures. Why it matters Distinguishes between planned maintenance and reactive repairs. Where to get WIE_WORK_ORDERS_B table, column WORK_ORDER_TYPE_ID (joined to type definition). Examples PreventiveCorrectiveEmergency
	Organization Code OrganizationCode	The code for the maintenance organization or plant.
Description Represents the specific business unit, plant, or facility responsible for the maintenance work. It allows for regional or facility-based benchmarking. Used to map performance differences between different sites or operational units. Why it matters Segment analysis by physical location or business unit. Where to get WIE_WORK_ORDERS_B table, column ORGANIZATION_ID. Examples M1NY-PLANTLON-DEPOT
	Priority Priority	The urgency level assigned to the work order.
Description Indicates the importance of the work order, such as Critical, High, Medium, or Low. This drives the scheduling and resource allocation. Important for the Work Order Conversion Efficiency dashboard to ensure critical faults are prioritized correctly. Why it matters Determines the target response time and resource allocation. Where to get WIE_WORK_ORDERS_B table, column WORK_ORDER_PRIORITY_ID. Examples CriticalHighStandard
	Work Order Status WorkOrderStatus	The current lifecycle state of the work order.
Description Indicates the status of the work order, such as Unreleased, Released, On Hold, Completed, or Closed. This helps filter cases that are still open versus those that have finished. Used in dashboarding to visualize the current work in progress (WIP) and to identify orders stuck in specific states. Why it matters Provides a snapshot of the work order's progress. Where to get WIE_WORK_ORDERS_B table, column WORK_ORDER_STATUS_ID. Examples ReleasedOn HoldClosed
	Actual Labor Hours ActualLaborHours	Total hours spent by technicians on this work order.
Description The sum of all duration values recorded in labor transactions for the work order. This measures the actual effort expended. Used in Estimated versus Actual Labor Variance KPI to assess planning accuracy. Why it matters Key metric for costing and workforce efficiency. Where to get Aggregated from WIE_WORK_ORDER_TRANSACTIONS (Resource transactions). Examples 4.512.00.75
	Asset Category AssetCategory	Grouping of assets into types like HVAC, Fleet, or Production Line.
Description Classifies the asset into broader groups. This allows for high-level analysis of maintenance performance across different types of equipment. Used in the Maintenance Lead Time Overview dashboard to identify which equipment classes cause the most delays. Why it matters Enables aggregated analysis of maintenance performance by equipment type. Where to get Derived from the Item Category assigned to the Asset ID. Examples Heavy MachineryFleet VehiclesFacilities
	Estimated Labor Hours EstimatedLaborHours	Planned labor hours for the work order.
Description The amount of time the planner estimated the job would take. This is set during the planning or release phase. Compared against Actual Labor Hours to determine the variance and improve future planning standards. Why it matters Benchmark for assessing execution efficiency. Where to get WIE_OPERATION_RESOURCES table, planned quantity. Examples 5.010.01.0
	Is Rework IsRework	Flag indicating if this work order is a repeat repair.
Description A boolean flag that evaluates to true if another work order was closed for the same Asset ID within a preceding 30-day window. This is a calculated attribute. It directly supports the Asset Rework Frequency KPI and helps identify poor quality repairs or failing assets. Why it matters Identifies quality issues and recurring failures. Where to get Calculated in the data transformation layer. Examples truefalse
	Request Date RequestDate	Date when the initial maintenance request was received.
Description The timestamp indicating when the need for maintenance was first identified and logged. This forms the start of the lead time calculation. Critical for the Work Order Conversion Lead Time KPI to measure the delay between identifying a problem and creating the order. Why it matters Marks the beginning of the end-to-end process lifecycle. Where to get WIE_WORK_ORDERS_B table, derived from source request link. Examples 2023-10-012023-10-052023-11-12
	Scheduled Start Date ScheduledStartDate	The planned start date for the maintenance work.
Description The date and time when the work was scheduled to begin. Comparison against Actual Start Date reveals schedule adherence. Used in Scheduling and Slippage Analysis to detect disruptions in the maintenance plan. Why it matters Baseline for measuring schedule adherence and slippage. Where to get WIE_WORK_ORDERS_B table, column PLANNED_START_DATE. Examples 2023-10-202023-10-212023-10-22
	SLA Target Date SlaTargetDate	The deadline by which the work order must be completed.
Description Calculated based on the priority and the creation date. It represents the commitment to the business for asset restoration. Used for the SLA Achievement Rate KPI to determine if maintenance is meeting its service obligations. Why it matters Defines the success criteria for timeliness. Where to get Usually a custom field or derived from Priority configuration. Examples 2023-11-012023-11-052023-11-10
	Total Cost TotalCost	The total financial cost incurred for the work order.
Description Sum of material, labor, and overhead costs associated with the work order. This is typically finalized during the 'Maintenance Costs Transferred' activity. Used in Maintenance Planning Accuracy analysis to compare budget vs actual spend. Why it matters Primary financial metric for maintenance activities. Where to get Costing module tables linked to Work Order ID. Examples 1500.00250.505000.00
	Work Order Description WorkOrderDescription	Brief text describing the issue or task.
Description A free-text field entered by the requestor or planner describing the problem or the required work. It provides context that structured data might miss. Useful for text analysis or manual review of 'Other' work types. Why it matters Provides human-readable context for the maintenance task. Where to get WIE_WORK_ORDERS_B table, column WORK_ORDER_DESCRIPTION. Examples Replace conveyer belt motorMonthly safety inspectionFix oil leak on Pump A

Required Recommended Optional

Asset Maintenance Activities

These represent the essential lifecycle stages and operational updates necessary to visualize the end to end flow of your maintenance work orders.

8 Recommended 7 Optional

	Activity	Description
	Labor Hours Recorded	The logging of time spent by a technician on the work order. This is a transactional event where costs are accrued against the maintenance activity.
Why it matters Tracks effort and cost accumulation. Multiple events of this type indicate ongoing work and are used to calculate total labor variances. Where to get Derived from WIE_RESOURCE_TRANSACTIONS table. Capture Logged when transaction X executed Event type explicit
	Maintenance Request Created	The initial submission of a maintenance request via the self-service portal or help desk interface. This event captures the first indication of a fault or required service before it becomes a formal work order, typically found in the Work Requests table.
Why it matters Marks the true start of the maintenance demand cycle. Essential for calculating the lead time between fault identification and the generation of an actionable work order. Where to get Derived from the MNT_WORK_REQUESTS table using the CREATION_DATE field. Capture Logged when a record is inserted into the Work Requests table Event type explicit
	Maintenance Work Order Created	The system generation of the Work Order entity, either converted from a request or created manually. This event establishes the Case ID for process mining and sets the baseline for planning and execution.
Why it matters The primary anchor event for the process instance. It separates the intake phase from the planning and execution phase. Where to get Derived from WIE_WORK_ORDERS_B table using the CREATION_DATE timestamp. Capture Logged when the WO header record is created Event type explicit
	Materials Issued	The actual deduction of inventory from the warehouse issued to the specific work order. This represents the physical movement of parts to the maintenance location.
Why it matters A tangible sign that work is preparing to start. Comparing this to the 'Released' event highlights delays in the warehouse picking process. Where to get Derived from INV_MATERIAL_TXNS table where TRANSACTION_SOURCE_TYPE_ID links to the Work Order. Capture Logged when transaction X executed Event type explicit
	Operation Started	The commencement of a specific operation (e.g., Op 10, Op 20) within the work order. This is the precise moment technical work begins on a task.
Why it matters Represents the 'Work Commenced' milestone. Essential for calculating the actual wrench time versus the planned duration. Where to get Derived from WIE_WO_OPERATIONS_B status changes or the first resource transaction timestamp for the operation. Capture Derive from comparing field X to Y Event type inferred
	Work Order Closed	The final administrative closure of the work order. No further costs can be accrued, and the order is finalized for accounting.
Why it matters Marks the end of the administrative lifecycle. The time between Completed and Closed represents the administrative lag. Where to get Derived from WIE_WO_STATUS_HISTORY where STATUS_CODE changes to 'CLOSED'. Capture Compare status field before/after Event type explicit
	Work Order Completed	The technical completion of the entire work order. This signifies that the physical asset is repaired and ready for service, though financial processing may continue.
Why it matters The effective end of the maintenance intervention from an operations perspective. Used to calculate SLA adherence. Where to get Derived from WIE_WO_STATUS_HISTORY where STATUS_CODE changes to 'COMPLETED'. Capture Compare status field before/after Event type explicit
	Work Order Released	The status change moving the work order from a Draft or Unreleased state to Released. This action authorizes the consumption of materials and resources against the order.
Why it matters Indicates the end of the planning phase and the authorization for execution to begin. Delays here indicate planning bottlenecks. Where to get Derived from WIE_WO_STATUS_HISTORY where STATUS_CODE changes to 'RELEASED'. Capture Compare status field before/after Event type inferred
	Maintenance Costs Transferred	The transfer of accumulated costs from the maintenance system to the Cost Management module. This acts as the financial settlement.
Why it matters Confirms that the financial impact of the maintenance has been recognized. Essential for 'Maintenance Planning Accuracy' analysis. Where to get Derived from Cost Accounting distribution tables linked to the Work Order transaction source. Capture Logged when transaction X executed Event type inferred
	Materials Allocated	The reservation or allocation of required spare parts and components to the work order. This ensures inventory is available before work commences.
Why it matters Critical for analyzing supply chain readiness. If this step is delayed, it suggests inventory shortages or slow procurement planning. Where to get Derived from WIE_WO_COMPONENTS_B table based on the CREATION_DATE or ALLOCATION_DATE. Capture Logged when transaction X executed Event type explicit
	Operation Completed	The completion of a specific step (Operation) in the routing. Signals that a specific portion of the technical work is finished.
Why it matters Provides granular visibility into progress. 'Maintenance Task Executed' is typically derived from the completion of the final operation. Where to get Derived from WIE_WO_OPERATIONS_B status changes or transaction history showing completion. Capture Compare status field before/after Event type inferred
	Quality Inspection Recorded	The entry of quality results associated with the maintenance work order. This validates that the repair meets safety and operational standards.
Why it matters Ensures compliance. Missing quality checks before completion can indicate safety risks or process deviations. Where to get Derived from QA_RESULTS or similar Quality Collection Plan tables linked to the Work Order ID. Capture Logged when transaction X executed Event type explicit
	Work Order Canceled	The termination of a work order before successful completion. This may happen if the work was duplicate, unnecessary, or consolidated.
Why it matters Identifying high cancellation rates can reveal issues with the request intake process or duplicate detection logic. Where to get Derived from WIE_WO_STATUS_HISTORY where STATUS_CODE changes to 'CANCELED'. Capture Compare status field before/after Event type explicit
	Work Order On Hold	The status of the work order is changed to 'On Hold', stopping execution. This typically occurs due to missing parts, lack of access, or safety issues.
Why it matters A major source of process inefficiency. Identifying the frequency and duration of holds is key to reducing overall lead time. Where to get Derived from WIE_WO_STATUS_HISTORY where STATUS_CODE changes to 'ON_HOLD'. Capture Compare status field before/after Event type explicit
	Work Order Scheduled	The assignment of specific start dates or specific resources (technicians/tools) to the work order operations. This updates the planned schedule dates in the system.
Why it matters Measures the efficiency of the dispatching or scheduling team. Gaps between release and scheduling indicate resource contention. Where to get Derived from updates to WIE_OPERATION_RESOURCES or WIE_WO_OPERATIONS_B scheduling columns. Capture Compare status field before/after Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Oracle Maintenance Cloud

Steps

Log in to the Oracle Cloud Console and navigate to the Business Intelligence Cloud Connector (BICC) console.
Create a new Offering Configuration or Edit an existing one. Locate the Offering for 'Maintenance Management' or 'Supply Chain Management'.
Select the required Public View Objects (PVOs) for extraction. You must select: 'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkOrderExtractPVO' (Headers), 'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkOrderStatusHistoryExtractPVO' (Status Changes), 'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkRequestExtractPVO' (Requests), 'FscmTopModelAM.ScmExtractAM.InvBiccExtractAM.MaterialTransactionExtractPVO' (Material Issues), and 'FscmTopModelAM.ScmExtractAM.MntBiccExtractAM.WorkOrderResourceTransactionExtractPVO' (Labor).
Configure the extraction storage. Point the BICC jobs to a valid Oracle Universal Content Management (UCM) folder or an external Object Storage bucket (e.g., OCI Object Storage).
Set up the scheduler to run these extracts. For the initial load, select 'Full Extract'. For subsequent runs, configure 'Incremental Extract' based on the 'LastUpdateDate' to reduce data volume.
Download the generated CSV files from the storage location and load them into your staging database or data lake (e.g., Snowflake, BigQuery, or SQL Server). Ensure the table names in your staging environment align with the PVO names for clarity.
Execute the SQL transformation script provided in the Query section below against your staging tables. This script unifies the disparate PVO data into a single event log structure.
Verify the output contains all required columns: WorkOrderNumber, ActivityName, EventDateTime, and SourceSystem.
Apply any necessary timezone conversions if the BICC extracts are in UTC (standard behavior) and your analysis requires local plant time.
Export the final result set as a CSV or Parquet file.
Upload the file to ProcessMind, ensuring the Case ID is mapped to WorkOrderNumber and the Timestamp is mapped to EventDateTime.

Configuration

Extraction Mode: Use 'Full' for the first run, then switch to 'Incremental' to extract only records changed since the last run.
Pruning Date: Set a 'Pruning Date' in BICC configuration to limit the historical data (e.g., typically last 12-24 months) to improve performance.
Column Selection: In the BICC console, you can deselect unused columns within the PVOs to reduce file size. Ensure you keep Primary Keys, Foreign Keys, Status Codes, and Date timestamps.
Fetch Size: Default fetch size is usually sufficient, but for high-volume environments (millions of work orders), consider increasing the batch size if valid for your target storage.
Filters: If you are only analyzing specific organizations, apply a filter on 'OrganizationCode' within the BICC PVO configuration to filter at the source.

a Sample Query config

/* 
  SQL Transformation Script for Oracle Maintenance Cloud via BICC
  Assumes BICC CSVs are loaded into staging tables:
  - STG_WORK_ORDER (Source: WorkOrderExtractPVO)
  - STG_WO_STATUS_HISTORY (Source: WorkOrderStatusHistoryExtractPVO)
  - STG_WORK_REQUEST (Source: WorkRequestExtractPVO)
  - STG_WO_MATERIAL (Source: WorkOrderMaterialExtractPVO)
  - STG_MAT_TRX (Source: MaterialTransactionExtractPVO)
  - STG_WO_OP_TRX (Source: WorkOrderOperationTransactionExtractPVO)
  - STG_WO_RES_TRX (Source: WorkOrderResourceTransactionExtractPVO)
  - STG_QA_RESULTS (Source: QAResultsExtractPVO)
  - STG_COST_TRX (Source: CostTransactionExtractPVO)
*/

SELECT
    CAST(WR.WorkRequestNumber AS VARCHAR(255)) AS WorkOrderNumber, -- Using Request Number temporarily or link to WO if exists
    'Maintenance Request Created' AS ActivityName,
    WR.CreationDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS WorkOrderType,
    WR.StatusCode AS WorkOrderStatus,
    WR.AssetNumber AS AssetNumber,
    WR.OrganizationCode AS OrganizationCode,
    NULL AS Priority,
    NULL AS AssignedTechnician,
    NULL AS ActualStartDate,
    NULL AS ActualCompletionDate
FROM STG_WORK_REQUEST WR

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Maintenance Work Order Created' AS ActivityName,
    WO.CreationDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WORK_ORDER WO

UNION ALL

-- Captures Released, On Hold, Completed, Closed, Canceled based on status history
SELECT
    WO.WorkOrderNumber,
    CASE 
        WHEN WSH.StatusCode = 'RELEASED' THEN 'Work Order Released'
        WHEN WSH.StatusCode = 'ON_HOLD' THEN 'Work Order On Hold'
        WHEN WSH.StatusCode = 'COMPLETED' THEN 'Work Order Completed'
        WHEN WSH.StatusCode = 'CLOSED' THEN 'Work Order Closed'
        WHEN WSH.StatusCode = 'CANCELED' THEN 'Work Order Canceled'
        ELSE CONCAT('Status Change: ', WSH.StatusCode)
    END AS ActivityName,
    WSH.StatusChangeDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WSH.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WO_STATUS_HISTORY WSH
JOIN STG_WORK_ORDER WO ON WSH.WorkOrderId = WO.WorkOrderId
WHERE WSH.StatusCode IN ('RELEASED', 'ON_HOLD', 'COMPLETED', 'CLOSED', 'CANCELED')

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Materials Allocated' AS ActivityName,
    WOM.CreationDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WO_MATERIAL WOM
JOIN STG_WORK_ORDER WO ON WOM.WorkOrderId = WO.WorkOrderId

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Materials Issued' AS ActivityName,
    MT.TransactionDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_MAT_TRX MT
JOIN STG_WORK_ORDER WO ON MT.WorkOrderNumber = WO.WorkOrderNumber -- Note: Linking via WO Number string if ID not present in Inv Extract
WHERE MT.TransactionTypeId IN (35, 43) -- Standard Type IDs for WIP Issue/Return, adjust based on specific setup

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Work Order Scheduled' AS ActivityName,
    WO.ScheduledStartDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WORK_ORDER WO
WHERE WO.ScheduledStartDate IS NOT NULL
AND WO.ScheduledStartDate <> WO.CreationDate -- Attempt to isolate explicit scheduling action

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Operation Started' AS ActivityName,
    WOT.TransactionDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WO_OP_TRX WOT
JOIN STG_WORK_ORDER WO ON WOT.WorkOrderId = WO.WorkOrderId
WHERE WOT.TransactionType = 'OP_START' -- Adjust literal based on lookup code

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Operation Completed' AS ActivityName,
    WOT.TransactionDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WO_OP_TRX WOT
JOIN STG_WORK_ORDER WO ON WOT.WorkOrderId = WO.WorkOrderId
WHERE WOT.TransactionType = 'OP_COMPLETE' -- Adjust literal based on lookup code

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Labor Hours Recorded' AS ActivityName,
    WRT.TransactionDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    WRT.PersonName AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_WO_RES_TRX WRT
JOIN STG_WORK_ORDER WO ON WRT.WorkOrderId = WO.WorkOrderId

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Quality Inspection Recorded' AS ActivityName,
    QR.CreationDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_QA_RESULTS QR
JOIN STG_WORK_ORDER WO ON QR.SourceReferenceId = WO.WorkOrderId -- Mapping depends on Quality Plan setup

UNION ALL

SELECT
    WO.WorkOrderNumber,
    'Maintenance Costs Transferred' AS ActivityName,
    CT.TransactionDate AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    WO.WorkOrderType,
    WO.StatusCode AS WorkOrderStatus,
    WO.AssetNumber,
    WO.OrganizationCode,
    WO.Priority,
    NULL AS AssignedTechnician,
    WO.ActualStartDate,
    WO.ActualCompletionDate
FROM STG_COST_TRX CT
JOIN STG_WORK_ORDER WO ON CT.SourceRefId = WO.WorkOrderId
WHERE CT.TransactionSource = 'MAINTENANCE'

Steps

Log in to the Oracle Fusion Cloud environment with a user that has BI Administrator or Data Model developer privileges.
Navigate to Tools > Reports and Analytics, then click Browse Catalog to open the Oracle BI Publisher interface (typically /xmlpserver).
In the Catalog, click the Create button in the top right and select Data Model.
In the Data Model editor, locate the Data Sets panel on the left side, click the Plus (+) icon, and select SQL Query.
Name the dataset (e.g., MaintenanceEventLog), select the appropriate Data Source (typically ApplicationDB_SCM or similar), and leave the Type as Standard SQL.
Paste the complete SQL query provided below into the SQL Query text area.
Review the specific table names (like QA_RESULTS or Cost tables) as these can vary based on specific module implementation; adjust the schema prefix if your environment requires distinct schema references (e.g., FUSION.WIE_...).
Navigate to the Parameters tab if you wish to parameterize the date range, replacing the hardcoded timestamps in the WHERE clauses with bind variables like :p_start_date.
Click the View Data button, select a sample size, and click View to verify the output contains rows for various activity types.
Once verified, save the Data Model to a Shared Folder (e.g., Custom/Maintenance).
To extract the full log, Create a Report using this Data Model, deselect all layout options to produce a raw data export, and schedule the report to output as CSV or XML.
Download the resulting file and import it into ProcessMind, ensuring the EventDateTime column is parsed with the correct timezone offset.

Configuration

Data Source: Ensure the SQL query runs against the SCM or Maintenance Cloud operational database, not the archive.
Date Range: The query currently extracts all history. For production, add a WHERE clause filtering by CREATION_DATE or TRANSACTION_DATE (e.g., last 12 months) to improve performance.
Organization Filtering: If your environment has multiple maintenance organizations, add a filter for ORGANIZATION_CODE or ORGANIZATION_ID to separate logs by plant.
System Timezone: Oracle Cloud stores dates in UTC. Ensure your ProcessMind project is configured to interpret these timestamps as UTC or adjust the SQL to cast to your local time zone.
Status Codes: The query assumes standard Oracle lookup codes (e.g., ORA_WO_RELEASED). If you have created custom status codes, update the CASE statements or WHERE clauses in the status history block.
Validation: Always validate the Row Count against the Manage Maintenance Work Orders screen to ensure no major subsets of data are excluded.

a Sample Query sql

/* 1. Maintenance Request Created */
SELECT
    CAST(req.WORK_REQUEST_NUMBER AS VARCHAR(50)) AS WorkOrderNumber,
    'Maintenance Request Created' AS ActivityName,
    req.CREATION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    'Corrective' AS WorkOrderType,
    'NEW' AS WorkOrderStatus,
    req.ASSET_NUMBER AS AssetNumber,
    req.ORGANIZATION_CODE AS OrganizationCode,
    NULL AS Priority,
    NULL AS AssignedTechnician,
    NULL AS ActualStartDate,
    NULL AS ActualCompletionDate
FROM WIE_WORK_REQUESTS_B req

UNION ALL

/* 2. Maintenance Work Order Created */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Maintenance Work Order Created' AS ActivityName,
    wo.CREATION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'UNRELEASED' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WORK_ORDERS_B wo

UNION ALL

/* 3. Work Order Released */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order Released' AS ActivityName,
    hist.STATUS_CHANGE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'RELEASED' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_STATUS_HISTORY hist
JOIN WIE_WORK_ORDERS_B wo ON hist.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE hist.STATUS_CODE = 'ORA_WO_RELEASED'

UNION ALL

/* 4. Materials Allocated (Requirement Definition) */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Materials Allocated' AS ActivityName,
    comps.CREATION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_OPERATION_COMPONENTS comps
JOIN WIE_WORK_ORDERS_B wo ON comps.WORK_ORDER_ID = wo.WORK_ORDER_ID

UNION ALL

/* 5. Materials Issued */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Materials Issued' AS ActivityName,
    mat_txn.TRANSACTION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_MATERIAL_TRANSACTIONS mat_txn
JOIN WIE_WORK_ORDERS_B wo ON mat_txn.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE mat_txn.TRANSACTION_TYPE_CODE = 'MATERIAL_ISSUE'

UNION ALL

/* 6. Work Order Scheduled */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order Scheduled' AS ActivityName,
    ops.LAST_UPDATE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_OPERATIONS_B ops
JOIN WIE_WORK_ORDERS_B wo ON ops.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE ops.PLANNED_START_DATE IS NOT NULL
AND ops.CREATION_DATE <> ops.LAST_UPDATE_DATE

UNION ALL

/* 7. Operation Started */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Operation Started' AS ActivityName,
    op_txn.TRANSACTION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_OPERATION_TRANSACTIONS op_txn
JOIN WIE_WORK_ORDERS_B wo ON op_txn.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE op_txn.TRANSACTION_TYPE_CODE IN ('ORA_WIE_OP_START', 'OP_START')

UNION ALL

/* 8. Labor Hours Recorded */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Labor Hours Recorded' AS ActivityName,
    res_txn.TRANSACTION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    res_txn.RESOURCE_CODE AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_OPERATION_TRANSACTIONS res_txn
JOIN WIE_WORK_ORDERS_B wo ON res_txn.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE res_txn.TRANSACTION_TYPE_CODE = 'RESOURCE_TRANSACTION'

UNION ALL

/* 9. Work Order On Hold */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order On Hold' AS ActivityName,
    hist.STATUS_CHANGE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'ON_HOLD' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_STATUS_HISTORY hist
JOIN WIE_WORK_ORDERS_B wo ON hist.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE hist.STATUS_CODE = 'ORA_WO_ON_HOLD'

UNION ALL

/* 10. Quality Inspection Recorded */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Quality Inspection Recorded' AS ActivityName,
    qa.CREATION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    qa.CREATED_BY AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM QA_RESULTS qa
JOIN WIE_WORK_ORDERS_B wo ON qa.WORK_ORDER_ID = wo.WORK_ORDER_ID

UNION ALL

/* 11. Operation Completed */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Operation Completed' AS ActivityName,
    op_txn.TRANSACTION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_OPERATION_TRANSACTIONS op_txn
JOIN WIE_WORK_ORDERS_B wo ON op_txn.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE op_txn.TRANSACTION_TYPE_CODE IN ('ORA_WIE_OP_COMPLETE', 'OP_COMPLETE')

UNION ALL

/* 12. Work Order Completed */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order Completed' AS ActivityName,
    hist.STATUS_CHANGE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'COMPLETED' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_STATUS_HISTORY hist
JOIN WIE_WORK_ORDERS_B wo ON hist.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE hist.STATUS_CODE = 'ORA_WO_COMPLETED'

UNION ALL

/* 13. Work Order Closed */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order Closed' AS ActivityName,
    hist.STATUS_CHANGE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'CLOSED' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_STATUS_HISTORY hist
JOIN WIE_WORK_ORDERS_B wo ON hist.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE hist.STATUS_CODE = 'ORA_WO_CLOSED'

UNION ALL

/* 14. Work Order Canceled */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Work Order Canceled' AS ActivityName,
    hist.STATUS_CHANGE_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    'CANCELED' AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM WIE_WO_STATUS_HISTORY hist
JOIN WIE_WORK_ORDERS_B wo ON hist.WORK_ORDER_ID = wo.WORK_ORDER_ID
WHERE hist.STATUS_CODE = 'ORA_WO_CANCELED'

UNION ALL

/* 15. Maintenance Costs Transferred */
SELECT
    wo.WORK_ORDER_NUMBER AS WorkOrderNumber,
    'Maintenance Costs Transferred' AS ActivityName,
    cst.TRANSACTION_DATE AS EventDateTime,
    'Oracle Maintenance Cloud' AS SourceSystem,
    SYSDATE AS LastDataUpdate,
    wo.WORK_ORDER_TYPE AS WorkOrderType,
    wo.WORK_ORDER_STATUS AS WorkOrderStatus,
    wo.ASSET_NUMBER AS AssetNumber,
    wo.ORGANIZATION_CODE AS OrganizationCode,
    wo.WORK_ORDER_PRIORITY AS Priority,
    NULL AS AssignedTechnician,
    wo.ACTUAL_START_DATE AS ActualStartDate,
    wo.ACTUAL_COMPLETION_DATE AS ActualCompletionDate
FROM CST_TRANSACTIONS cst
JOIN WIE_WORK_ORDERS_B wo ON cst.SOURCE_HEADER_ID = wo.WORK_ORDER_ID
WHERE cst.TRANSACTION_SOURCE_CODE = 'MAINTENANCE'

Steps

Prerequisites: Ensure you have a configured Oracle Cloud user with the Maintenance Manager role and REST API access enabled.
Environment Setup: Install Python (version 3.8 or later) and the required libraries pandas and requests using pip install pandas requests.
Endpoint Identification: Identify the Base URL for your Oracle Fusion instance (e.g., https://fa-instance.oraclecloud.com) and the specific REST endpoints: maintenanceWorkOrders, maintenanceWorkOrders/child/WorkOrderOperation, and maintenanceWorkOrders/child/WorkOrderMaterialTransaction.
Authentication Configuration: Configure the script with Basic Authentication credentials (Username and Password) or implement OAuth 2.0 token handling if your security policy requires it.
Scope Definition: Define the extraction time window (e.g., LastUpdateDate > 2023-01-01) to limit the volume of data and ensure performance efficiency.
Header Extraction: The script queries the parent maintenanceWorkOrders endpoint first. This provides the core Case ID (WorkOrderNumber) and header-level timestamps for Creation, Release, and Closure.
Child Resource Iteration: For every Work Order retrieved, the script must make subsequent API calls to child resources (Operations, Material Transactions, Resource Transactions) to capture granular events like Operation Started or Labor Hours Recorded.
History Reconstruction: To capture status changes like Work Order On Hold, the script queries the Work Order History child resource or compares state timestamps.
Data Transformation: As data is returned in nested JSON format, the script flattens these structures into a normalized list of dictionaries, where each dictionary represents one event row.
Attribute Mapping: Map specific API fields to the required ProcessMind schema (e.g., mapping OrganizationCode to Organization Code and WorkOrderNumber to Case ID).
DataFrame Construction: Convert the list of event dictionaries into a pandas DataFrame to handle sorting, deduplication, and final formatting.
Export: Save the final DataFrame as a CSV file (e.g., oracle_maintenance_event_log.csv), ensuring the date format is ISO 8601 compliant for ingestion.

Configuration

API Base URL: The primary URL for your Oracle Fusion instance (e.g., https://pod.oraclecloud.com).
Authentication: Basic Auth (Base64 encoded) is standard for scripts, but OAuth is recommended for production automation.
Batch Size: Oracle REST APIs default to 25 records. Use the limit parameter (e.g., limit=499) to maximize throughput per call.
Date Filtering: Use the q parameter to filter by LastUpdateDate to enable incremental extraction (e.g., LastUpdateDate > '2023-01-01').
Concurrency: Implement rate limiting (sleep times) if processing thousands of work orders to avoid hitting API 429 Too Many Requests errors.
Child Resources: Ensure the user has permissions to view child objects like Work Order Costs and Resource Transactions.

a Sample Query python

import requests
import pandas as pd
import base64
import json
from datetime import datetime

class=class="hl-string">"hl-comment"># Configuration
BASE_URL = class="hl-string">"https://your-instance.fa.us2.oraclecloud.com"
USER = class="hl-string">"your_username"
PASSWORD = class="hl-string">"your_password"
OUTPUT_FILE = class="hl-string">"oracle_maintenance_log.csv"

class=class="hl-string">"hl-comment"># API Endpoints
WO_ENDPOINT = fclass="hl-string">"{BASE_URL}/fscmRestApi/resources/11.13.18.05/maintenanceWorkOrders"

class=class="hl-string">"hl-comment"># List to hold all extracted events
event_log = []

def get_headers():
    auth_str = fclass="hl-string">"{USER}:{PASSWORD}"
    b64_auth = base64.b64encode(auth_str.encode()).decode()
    return {
        class="hl-string">"Authorization": fclass="hl-string">"Basic {b64_auth}",
        class="hl-string">"Content-Type": class="hl-string">"application/vnd.oracle.adf.resourceitem+json"
    }

def fetch_all_pages(url, params=None):
    results = []
    while url:
        try:
            response = requests.get(url, headers=get_headers(), params=params)
            response.raise_for_status()
            data = response.json()
            if class="hl-string">'items' in data:
                results.extend(data[class="hl-string">'items'])
            
            class=class="hl-string">"hl-comment"># Check for next page
            url = None
            if class="hl-string">'links' in data:
                for link in data[class="hl-string">'links']:
                    if link[class="hl-string">'rel'] == class="hl-string">'next':
                        url = link[class="hl-string">'href']
                        params = None class=class="hl-string">"hl-comment"># Params are usually part of the next link
                        break
        except Exception as e:
            print(fclass="hl-string">"Error fetching data: {e}")
            break
    return results

class=class="hl-string">"hl-comment"># 1. Fetch Maintenance Work Orders (Header Level)
print(class="hl-string">"Fetching Work Orders...")
class=class="hl-string">"hl-comment"># Example filter: Last 90 days. Adjust date as needed.
params = {
    class="hl-string">"q": class="hl-string">"LastUpdateDate > '2023-10-01'",
    class="hl-string">"limit": 499,
    class="hl-string">"fields": class="hl-string">"WorkOrderNumber,WorkOrderId,WorkOrderDefinitionName,WorkOrderStatus,WorkOrderType,AssetNumber,OrganizationCode,CreationDate,ReleasedDate,ScheduledStartDate,ActualStartDate,ActualCompletionDate,ClosedDate,CanceledDate,WorkRequestId,WorkRequestDate"
}

work_orders = fetch_all_pages(WO_ENDPOINT, params)

for wo in work_orders:
    wo_num = wo.get(class="hl-string">'WorkOrderNumber')
    wo_id = wo.get(class="hl-string">'WorkOrderId')
    asset_num = wo.get(class="hl-string">'AssetNumber')
    org_code = wo.get(class="hl-string">'OrganizationCode')
    wo_type = wo.get(class="hl-string">'WorkOrderType')
    
    common_attributes = {
        class="hl-string">"WorkOrderNumber": wo_num,
        class="hl-string">"AssetNumber": asset_num,
        class="hl-string">"OrganizationCode": org_code,
        class="hl-string">"WorkOrderType": wo_type,
        class="hl-string">"WorkOrderStatus": wo.get(class="hl-string">'WorkOrderStatus'),
        class="hl-string">"SourceSystem": class="hl-string">"Oracle Maintenance Cloud",
        class="hl-string">"LastDataUpdate": datetime.now().isoformat()
    }

    class=class="hl-string">"hl-comment"># --- Header Level Activities ---

    class=class="hl-string">"hl-comment"># 1. Maintenance Request Created
    if wo.get(class="hl-string">'WorkRequestDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Maintenance Request Created", class="hl-string">"EventDateTime": wo[class="hl-string">'WorkRequestDate']})
    elif wo.get(class="hl-string">'CreationDate'):
        class=class="hl-string">"hl-comment"># Fallback if no specific request date linked
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Maintenance Request Created", class="hl-string">"EventDateTime": wo[class="hl-string">'CreationDate']})

    class=class="hl-string">"hl-comment"># 2. Maintenance Work Order Created
    if wo.get(class="hl-string">'CreationDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Maintenance Work Order Created", class="hl-string">"EventDateTime": wo[class="hl-string">'CreationDate']})

    class=class="hl-string">"hl-comment"># 3. Work Order Released
    if wo.get(class="hl-string">'ReleasedDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order Released", class="hl-string">"EventDateTime": wo[class="hl-string">'ReleasedDate']})

    class=class="hl-string">"hl-comment"># 4. Work Order Scheduled
    if wo.get(class="hl-string">'ScheduledStartDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order Scheduled", class="hl-string">"EventDateTime": wo[class="hl-string">'ScheduledStartDate']})

    class=class="hl-string">"hl-comment"># 5. Work Order Completed (Header)
    if wo.get(class="hl-string">'ActualCompletionDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order Completed", class="hl-string">"EventDateTime": wo[class="hl-string">'ActualCompletionDate']})

    class=class="hl-string">"hl-comment"># 6. Work Order Closed
    if wo.get(class="hl-string">'ClosedDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order Closed", class="hl-string">"EventDateTime": wo[class="hl-string">'ClosedDate']})

    class=class="hl-string">"hl-comment"># 7. Work Order Canceled
    if wo.get(class="hl-string">'CanceledDate'):
        event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order Canceled", class="hl-string">"EventDateTime": wo[class="hl-string">'CanceledDate']})

    class=class="hl-string">"hl-comment"># --- Child Resources Extraction ---
    
    class=class="hl-string">"hl-comment"># Operations (Started, Completed)
    ops_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderOperation"
    operations = fetch_all_pages(ops_url)
    for op in operations:
        if op.get(class="hl-string">'ActualStartDate'):
            event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Operation Started", class="hl-string">"EventDateTime": op[class="hl-string">'ActualStartDate'], class="hl-string">"AssignedTechnician": op.get(class="hl-string">'WorkCenterName')})
        if op.get(class="hl-string">'ActualCompletionDate'):
            event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Operation Completed", class="hl-string">"EventDateTime": op[class="hl-string">'ActualCompletionDate'], class="hl-string">"AssignedTechnician": op.get(class="hl-string">'WorkCenterName')})

    class=class="hl-string">"hl-comment"># Material Transactions (Issued, Allocated)
    class=class="hl-string">"hl-comment"># Note: Allocated is derived from class="hl-string">'RequiredDate' in WorkOrderComponent child, Issued is from WorkOrderMaterialTransaction
    
    class=class="hl-string">"hl-comment"># 8. Materials Allocated (Using Component Requirements)
    comp_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderComponent"
    components = fetch_all_pages(comp_url)
    for comp in components:
        if comp.get(class="hl-string">'RequiredDate'):
             event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Materials Allocated", class="hl-string">"EventDateTime": comp[class="hl-string">'RequiredDate']})

    class=class="hl-string">"hl-comment"># 9. Materials Issued (Using Transactions)
    mat_trans_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderMaterialTransaction"
    mat_trans = fetch_all_pages(mat_trans_url)
    for trans in mat_trans:
        if trans.get(class="hl-string">'TransactionDate'):
             event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Materials Issued", class="hl-string">"EventDateTime": trans[class="hl-string">'TransactionDate']})

    class=class="hl-string">"hl-comment"># 10. Labor Hours Recorded
    res_trans_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderResourceTransaction"
    res_trans = fetch_all_pages(res_trans_url)
    for res in res_trans:
        if res.get(class="hl-string">'TransactionDate'):
             event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Labor Hours Recorded", class="hl-string">"EventDateTime": res[class="hl-string">'TransactionDate'], class="hl-string">"AssignedTechnician": res.get(class="hl-string">'ResourceName')})

    class=class="hl-string">"hl-comment"># 11. Work Order On Hold (Check History)
    class=class="hl-string">"hl-comment"># Note: Accessing history child resource. If unavailable, requires separate Audit Trail query.
    hist_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderHistory"
    history = fetch_all_pages(hist_url)
    for h in history:
        if h.get(class="hl-string">'Status') == class="hl-string">'ON_HOLD' and h.get(class="hl-string">'StatusChangeDate'):
            event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Work Order On Hold", class="hl-string">"EventDateTime": h[class="hl-string">'StatusChangeDate']})

    class=class="hl-string">"hl-comment"># 12. Quality Inspection Recorded
    qual_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderQualityCollection"
    quality = fetch_all_pages(qual_url)
    for q in quality:
        if q.get(class="hl-string">'CollectionDate'):
            event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Quality Inspection Recorded", class="hl-string">"EventDateTime": q[class="hl-string">'CollectionDate']})

    class=class="hl-string">"hl-comment"># 13. Maintenance Costs Transferred
    class=class="hl-string">"hl-comment"># Often found in WorkOrderCost child or inferred from specific Cost interactions
    cost_url = fclass="hl-string">"{WO_ENDPOINT}/{wo_id}/child/WorkOrderCost"
    costs = fetch_all_pages(cost_url)
    for c in costs:
        class=class="hl-string">"hl-comment"># Assuming transfer date exists or using creation of cost record
        date_field = c.get(class="hl-string">'TransferDate') or c.get(class="hl-string">'CreationDate')
        if date_field:
             event_log.append({**common_attributes, class="hl-string">"ActivityName": class="hl-string">"Maintenance Costs Transferred", class="hl-string">"EventDateTime": date_field})

class=class="hl-string">"hl-comment"># Convert to DataFrame and Export
df = pd.DataFrame(event_log)

class=class="hl-string">"hl-comment"># Ensure Date format
df[class="hl-string">'EventDateTime'] = pd.to_datetime(df[class="hl-string">'EventDateTime']).dt.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')

class=class="hl-string">"hl-comment"># Final Column Selection
columns = [class="hl-string">"WorkOrderNumber", class="hl-string">"ActivityName", class="hl-string">"EventDateTime", class="hl-string">"SourceSystem", class="hl-string">"LastDataUpdate", 
           class="hl-string">"WorkOrderType", class="hl-string">"WorkOrderStatus", class="hl-string">"AssetNumber", class="hl-string">"OrganizationCode", class="hl-string">"AssignedTechnician"]

class=class="hl-string">"hl-comment"># Handle missing columns gracefully
for col in columns:
    if col not in df.columns:
        df[col] = None

df = df[columns]
df.to_csv(OUTPUT_FILE, index=False)
print(fclass="hl-string">"Extraction complete. Saved {len(df)} rows to {OUTPUT_FILE}")

Your Asset Maintenance Data Template

Your Asset Maintenance Data Template

Asset Maintenance Attributes

Asset Maintenance Activities

Extraction Guides

Oracle BICC View Object Extraction

Steps

Configuration

a Sample Query config

Oracle BI Publisher SQL Data Model

Steps

Configuration

a Sample Query sql

Python Script via Fusion REST API

Steps

Configuration

a Sample Query python

Ready to Get Started?

Optimize Asset Maintenance and Stop Schedule Slips Today

Your Asset Maintenance Data Template

Your Asset Maintenance Data Template

Asset Maintenance Attributes

Asset Maintenance Activities

Extraction Guides

Oracle BICC View Object Extraction

Steps

Configuration

a Sample Query config

Oracle BI Publisher SQL Data Model

Steps

Configuration

a Sample Query sql

Python Script via Fusion REST API

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Optimize Asset Maintenance and Stop Schedule Slips Today