Your Asset Maintenance Data Template
Your Asset Maintenance Data Template
- Recommended attributes for collection
- Critical maintenance activities to track
- Technical extraction guidance for SAP PM
Asset Maintenance Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name Activity | The specific task or status change that occurred. | ||
| Description Represents the step performed in the maintenance process, such as 'Maintenance Order Created', 'Goods Issue Posted', or 'Order Operation Confirmed'. These are typically derived from the status management tables (JEST) for status changes, or transaction tables (AFRU for confirmations, MKPF for material movements). This attribute determines the nodes in the process map. Why it matters This defines the 'what' of the process, essential for discovering the process flow and variants. Where to get Derived from JEST (Status), AFRU (Confirmations), QMEL (Notifications) Examples Maintenance Order CreatedOrder ReleasedTechnically Completed | |||
| Event Timestamp EventTimestamp | The exact date and time the activity occurred. | ||
| Description Records the specific moment an event took place. For status changes, this comes from the system status update time; for operations, it is the confirmation time. Accurate timestamps are critical for calculating cycle times, lead times, and identifying bottlenecks between process steps like 'Planning' and 'Execution'. Why it matters Required to sequence events correctly and calculate all duration-based KPIs. Where to get JEST (UDATE/UTIME), AFRU (ISDD/ISDZ), MKPF (CPUDT/CPUTM) Examples 2023-10-15T08:30:00Z2023-10-15T14:45:12Z | |||
| Last Data Update LastDataUpdate | Timestamp of the most recent data extraction. | ||
| Description Indicates when the data was last refreshed in the process mining data model. This allows analysts to understand how current the insights are and helps validate data freshness for dashboards monitoring real-time backlog or SLA adherence. Why it matters Critical for data governance and establishing trust in the dashboards. Where to get ETL Timestamp Examples 2023-11-01T12:00:00Z | |||
| Maintenance Work Order MaintenanceWorkOrder | The unique identifier for the maintenance work order. | ||
| Description The Maintenance Work Order is the central document in SAP Plant Maintenance (table AUFK, field AUFNR). It links the maintenance requirement (Notification) to the execution resources, materials, and costs. In process mining analysis, this serves as the Case ID, allowing the reconstruction of the end-to-end lifecycle of a maintenance job from creation to technical and business completion. Why it matters It is the unique key that binds all activities, costs, and timestamps together, enabling the visualization of the process flow. Where to get SAP Table AUFK, Field AUFNR Examples 00004001234540012345000040098765 | |||
| Source System SourceSystem | The system where the data originated. | ||
| Description Identifies the specific SAP client or instance (e.g., 'SAP_PM_PROD'). In landscape with multiple ERP systems, this attribute helps distinguish between records that might share identical IDs but belong to different legal entities or system environments. Why it matters Ensures data uniqueness and lineage in multi-system environments. Where to get System Configuration / Extraction Log Examples SAP_ECC_P01SAP_S4H_100 | |||
| Created By User CreatedByUser | The username of the person who created the order. | ||
| Description Identifies the user (ERNAM) responsible for initiating the work order. This is mapped to 'User' in the generic model. It allows for analysis of manual vs. automated creation and helps in auditing the 'Maintenance Order Created' activity. Why it matters Enables user behavior analysis and auditing. Where to get SAP Table AUFK, Field ERNAM Examples JSMITHBSERVICEDDAVIS | |||
| Equipment Number EquipmentNumber | Identifier for the specific physical asset being maintained. | ||
| Description The Equipment Number (EQUNR) identifies the individual machine or asset object. By aggregating data on this field, analysts can compute the 'Asset Maintenance Cost and Throughput' dashboard to find 'bad actor' equipment that requires frequent, costly repairs. It enables reliability-centered maintenance analysis. Why it matters Connects process performance to specific physical assets. Where to get SAP Table AUFK, Field EQUNR Examples 10004567PUMP-001HVAC-202 | |||
| Functional Location FunctionalLocation | The hierarchical location where the asset is installed. | ||
| Description Represents the place in the technical structure where the maintenance task is performed (TPLNR). Unlike Equipment, which moves, the Functional Location is static. This attribute supports the 'Technical Inspection Cycle Times' dashboard by allowing performance comparisons across different areas of the plant (e.g., Boiler Room vs. Packaging Line). Why it matters Allows spatial and hierarchical analysis of maintenance performance. Where to get SAP Table IFLOT, Field TPLNR (or via AUFK-TPLNR) Examples PLANT1-BLD2-LN01US-TX-DAL-01 | |||
| Main Work Center MainWorkCenter | The primary work center or crew assigned to execute the order. | ||
| Description The Main Work Center (VAPLZ) represents the group of technicians or machines that will perform the physical work. This attribute is mapped to 'Team' and is vital for analyzing execution efficiency, such as 'Mean Time to Repair' per crew. It helps distinguish between internal labor and external contractors. Why it matters Enables resource-level performance analysis. Where to get SAP Table AFKO, Field VAPLZ (or AUFK-VAPLZ depending on config) Examples MECH_01ELEC_TEAMEXT_CONT | |||
| Maintenance Order Type MaintenanceOrderType | Categorizes the work order (e.g., Preventive, Corrective). | ||
| Description The Order Type (AUART) in SAP PM distinguishes the nature of the maintenance work. Common types include PM01 (Corrective), PM02 (Preventive), and PM03 (Refurbishment). This attribute is essential for the 'Emergency Work Order Impact Monitor' and for segmenting process variants, as preventive workflows differ significantly from reactive ones. Why it matters Fundamental for segmenting analysis between planned and unplanned work. Where to get SAP Table AUFK, Field AUART Examples PM01PM02PM03 | |||
| Maintenance Plant MaintenancePlant | The logistics plant responsible for the maintenance. | ||
| Description The Plant (WERKS) is the primary organizational unit in logistics. It separates data by physical facility. This attribute is essential for benchmarking performance across different sites and is mapped to 'BusinessUnit' to support high-level organizational filtering in dashboards. Why it matters Primary organizational filter for benchmarking facilities. Where to get SAP Table AUFK, Field WERKS Examples 100020003000 | |||
| Maintenance Priority MaintenancePriority | The urgency level assigned to the work order. | ||
| Description Indicates the priority (PRIOK) of the maintenance task, such as '1-Very High', '2-High', or '3-Medium'. This field is the direct input for the 'Emergency Work Order Ratio' KPI. Analyzing flow by priority helps identify if high-priority orders are genuinely expedited through the system compared to routine work. Why it matters Key driver for SLA analysis and emergency work identification. Where to get SAP Table AFKO, Field PRIOK (or AUFK via view) Examples 1-Emergency2-High3-Medium4-Low | |||
| Notification Number NotificationNumber | The maintenance notification that triggered this order. | ||
| Description The Notification Number (QMNUM) links the Work Order back to the initial request (Notification). This link is necessary to calculate the full lead time from 'Maintenance Request Received' to completion. Without this, the analysis misses the initial detection and reporting phase of the maintenance lifecycle. Why it matters Connects the execution phase back to the request/detection phase. Where to get SAP Table AUFK, Field QMNUM Examples 1000050010000501 | |||
| Planner Group ResponsiblePlannerGroup | The group of planners responsible for processing the order. | ||
| Description The Planner Group (INGRP) identifies the specific team or department responsible for planning the maintenance work. This is mapped to 'Department' and is crucial for the 'Resource Utilization and Scheduling' dashboard to identify which planning teams are bottlenecks in the preparation phase. Why it matters Identifies the administrative team owning the planning process. Where to get SAP Table AUFK, Field INGRP Examples M01ELEMEC | |||
| System Status SystemStatus | The current technical status of the order (e.g., REL, TECO). | ||
| Description A concatenated string or list of active system statuses (from JEST) currently applied to the order. Common values include CRTD (Created), REL (Released), TECO (Technically Completed), and CLSD (Closed). This helps filter open vs. closed cases in the 'Backlog and Work In Progress Monitor'. Why it matters The primary indicator of the order's lifecycle stage. Where to get SAP Function Module STATUS_TEXT_EDIT or Table JEST Examples REL CNFTECOCRTD MACM | |||
| Target Finish Date TargetFinishDate | The scheduled completion date for the maintenance work. | ||
| Description This date (typically Basic Finish Date - GLTRP) represents the deadline for the work order. It is compared against the actual completion timestamp to calculate the 'SLA Adherence Rate' KPI. It is the baseline for determining if a work order is on time or late. Why it matters The reference point for all On-Time Delivery (OTD) calculations. Where to get SAP Table AFKO, Field GLTRP Examples 2023-12-012023-12-15 | |||
| Is Emergency Work IsEmergencyWork | Flag indicating if the work order is an emergency. | ||
| Description A boolean attribute calculated based on the Maintenance Priority. If Priority is '1' (or the specific code for Emergency in the configuration), this flag is true. This simplifies filtering and building the 'Emergency Work Order Ratio' KPI without complex logic in the dashboard layer. Why it matters Simplifies filtering for critical reactive maintenance analysis. Where to get Calculated from MaintenancePriority Examples truefalse | |||
| Is Rework IsRework | Flag indicating if the order involved rework activities. | ||
| Description A calculated boolean that identifies if a process loop occurred, specifically if the 'Maintenance Task Executed' activity happened more than once or if the status reverted from 'Technically Completed' to 'In Process'. This supports the 'Maintenance Rework Rate' KPI and helps identify quality issues. Why it matters Directly supports quality control analysis. Where to get Calculated from Process Flow Examples truefalse | |||
| Planned Labor Hours PlannedLaborHours | The estimated labor hours for the order. | ||
| Description The total planned work (ARBEI) from the order operations. Comparing this against the actual Activity Duration helps in the 'Planning and Estimation Performance' dashboard to assess the accuracy of maintenance planning. Why it matters Base metric for planning accuracy analysis. Where to get SAP Table AFKO (Sum of Operation Planned Work) Examples 4.08.512.0 | |||
| Total Actual Cost TotalActualCost | The total actual cost posted to the work order. | ||
| Description Aggregated costs from labor, materials, and services (from table PMCO). This attribute supports the 'Asset Maintenance Cost and Throughput' dashboard, allowing managers to see the financial impact of maintenance activities. It is essential for determining the cost-effectiveness of repairing versus replacing assets. Why it matters Provides the financial dimension to the operational process data. Where to get SAP Table PMCO (sum of value type 04 - Actual) Examples 150.002500.500.00 | |||
Asset Maintenance Activities
| Activity | Description | ||
|---|---|---|---|
| Business Completion Set | Marks the final administrative closure of the work order, preventing any further cost postings. Captured via the status change to CLSD (Closed). | ||
| Why it matters Represents the absolute end of the order lifecycle, critical for Data Accuracy and Records Compliance. Where to get Table JCDS, Status I0046 (CLSD). Capture Logged when status updates to CLSD in JCDS Event type explicit | |||
| Goods Issue Posted | Records the physical consumption of spare parts or materials against the work order. Captured from material document headers linked to the order. | ||
| Why it matters Confirms that parts are available and have been picked, directly impacting Parts Availability Wait Time analysis. Where to get Table MSEG/MKPF, Movement Type 261 (Goods Issue for Order). Capture Logged when transaction MIGO/MB1A executed Event type explicit | |||
| Maintenance Order Created | Marks the generation of the maintenance work order object in the system. Captured explicitly from the Order Master Data table creation timestamp. | ||
| Why it matters This is the central anchor event for the case, marking the transition from request to planning and execution. Where to get Table AUFK, field ERDAT (Date) and ERFZEIT (Time). Capture Logged when transaction IW31 executed Event type explicit | |||
| Maintenance Order Released | Indicates that planning is complete and the order is released for execution. Captured by monitoring the status change to REL in the system status logs. | ||
| Why it matters This milestone separates the planning phase from the execution phase, crucial for measuring Planning and Estimation Lead Time. Where to get Table JCDS (Status Change Documents), Status I0002 (REL). Capture Logged when status updates to REL in JCDS Event type explicit | |||
| Order Operation Confirmed | Represents the recording of actual time spent by a technician on a specific operation. This captures the actual execution work on the asset. | ||
| Why it matters This activity is the primary data source for Mean Time to Repair and labor cost calculations. Where to get Table AFRU (Order Confirmations). Capture Logged when transaction IW41 executed Event type explicit | |||
| Technical Completion Set | Marks the maintenance work as physically and technically finished. Captured via the status change to TECO (Technically Completed). | ||
| Why it matters The most critical endpoint for operational analysis, signifying the asset is restored and maintenance is done. Where to get Table JCDS, Status I0045 (TECO). Capture Logged when status updates to TECO in JCDS Event type explicit | |||
| Maintenance Notification Created | Records the initial creation of a maintenance notification which is often the trigger for a work order. Captured from the QMEL table creation timestamp, this activity links the upstream request to the execution order. | ||
| Why it matters This activity establishes the start of the end-to-end maintenance process, allowing for the calculation of reaction times between fault detection and order generation. Where to get Table QMEL, field ERDAT (Creation Date) and MZEIT (Time). Linked to order via QMEL-AUFNR. Capture Logged when record created in QMEL table Event type explicit | |||
| Maintenance Priority Changed | Logs an update to the priority level of the work order, such as escalating to Emergency. Derived from Change Documents associated with the order master data. | ||
| Why it matters Tracking priority changes explains process deviations and supports the Emergency Work Order Impact Monitor by identifying reactive escalations. Where to get Table CDPOS/CDHDR for Object Class AUFTRAG, Field PRIOK. Capture Logged in Change Documents (CDPOS) Event type explicit | |||
| Material Component Added | Records the addition of spare parts or materials to the work order component list. Captured from the creation of a reservation line item. | ||
| Why it matters Identifies the start of the procurement cycle for required parts, supporting the Spare Parts Procurement Lead Times dashboard. Where to get Table RESB, field BDTER (Requirement Date) or creation timestamp. Capture Logged when line item added to RESB Event type explicit | |||
| Measurement Reading Recorded | Logs the entry of a measurement document, such as a counter reading or condition assessment. Often serves as the Technical Inspection event. | ||
| Why it matters Essential for Condition Based Maintenance and tracking the Technical Inspection Cycle Times. Where to get Table IMRG (Measurement Document), linked to Equipment or Functional Location. Capture Logged when measurement document created Event type explicit | |||
| Order Cost Settled | Indicates that the costs collected on the order have been transferred to the controlling object (e.g., Cost Center). Captured from the Controlling document. | ||
| Why it matters Tracks the financial closure aspect and supports the Financial Settlement Duration KPI. Where to get Table COBK (CO Object: Document Header), linked to Order. Capture Logged when settlement transaction KO88 executed Event type explicit | |||
| Purchase Requisition Created | Signifies that a request for external procurement or services has been generated. This connects the internal maintenance order to the external supply chain. | ||
| Why it matters Crucial for analyzing delays caused by external vendors or non-stock material procurement. Where to get Table EBAN, linked via Account Assignment to AUFK-AUFNR. Capture Logged when EBAN record created Event type explicit | |||
| Technical Completion Reversed | Records the revocation of the Technical Completion status, indicating the order has been reopened. This creates a rework loop in the process model. | ||
| Why it matters Primary indicator for the Maintenance Rework Rate KPI, highlighting premature closures or quality issues. Where to get Table JCDS, Status I0045 (TECO) change to Inactive. Capture Logged when TECO status deactivated Event type explicit | |||
| Work Order Scheduled | Indicates the specific scheduling of operations to a capacity or date. Inferred from the assignment of the Dispatch status to operations. | ||
| Why it matters Measures the efficiency of resource allocation and supports the Resource Utilization and Scheduling dashboard. Where to get Table JCDS (Status Change Documents) for Operations (ObjType OV), Status I0009 (DSPT). Capture Compare status field before/after Event type inferred | |||
Extraction Guides
Steps
Identify the Data Source Interface: Determine how you will access the SAP S/4HANA SQL interface. This is typically done via SAP HANA Studio, the ABAP Development Tools (ADT) in Eclipse, or a DBaaS connection string if the HANA database is exposed to your analytics layer.
Verify CDS View Availability: Ensure the standard Core Data Services (CDS) views are active in your S/4HANA system. The primary views required are I_MaintenanceOrder, I_MaintenanceOrderOperation, I_MaintenanceNotification, I_MaintOrderConfirmation, and I_MeasurementDocument. You will also need access to the system status history table JCDS and change document tables (CDHDR/CDPOS) to reconstruct the full event history, as standard CDS views often only show the current state.
Prepare the SQL Environment: Open your SQL editor. Ensure your user has SELECT permissions on the DDL SQL views (usually schema SAPABAP1 or similar) for the artifacts listed above.
Execute the Extraction Query: Copy the provided SQL script into your editor. Replace the placeholder [Your Client Number] with your specific SAP client ID (e.g., 100). If you are filtering by a specific date range, update the EventTimestamp filters in the WHERE clauses.
Data Transformation: The query handles most transformations, such as casting timestamps and mapping status codes to readable text. However, ensure that the EventTimestamp column is formatted to ISO 8601 (YYYY-MM-DDTHH:MM:SS) during the export if your target system requires it.
Export the Result: Execute the query and export the results to a CSV or Parquet file. Ensure the encoding is set to UTF-8 to handle special characters in user names or descriptions.
Validate Column Mapping: Open a sample of the export and ensure the columns MaintenanceWorkOrder, Activity, and EventTimestamp are fully populated. Check that the Activity column contains the specific strings listed in the requirements (e.g., Maintenance Order Created, Goods Issue Posted).
Upload to ProcessMind: Import the validated flat file into ProcessMind. Map the Activity column to the Activity ID field, MaintenanceWorkOrder to the Case ID field, and EventTimestamp to the Timestamp field.
Configuration
- Client Filter: SAP systems are multi-client. Always filter by
MANDTorClient(e.g., '100') to avoid duplicate data or cross-client leakage. - Date Range: It is recommended to filter the
CreationDateof the Maintenance Order for the last 6 to 12 months. Querying the full history ofJCDS(Status) orCDPOS(Change Documents) without date filters can cause performance timeouts. - Language Key: The query defaults to English ('E'). If your organization uses a different primary language for text descriptions, adjust the language filter.
- System Access: Requires access to the ABAP Core Data Services schema or the underlying HANA tables. Access to the raw tables JCDS and CDHDR is crucial for historical status analysis, as pure CDS views are often snapshot-based.
- Order Types: The query extracts all order types. You may wish to filter
MaintenanceOrderType(e.g., only PM01, PM02) if you only want to analyze specific maintenance scenarios.
a Sample Query sql
/* Activity 1: Maintenance Notification Created */
SELECT
CAST(N.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Maintenance Notification Created' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(N.CreationDate, ' '), N.CreationTime), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
N.MaintenanceOrderType,
N.MaintenancePriority,
N.Equipment AS EquipmentNumber,
N.FunctionalLocation,
N.MaintenancePlant,
N.MaintenancePlannerGroup AS ResponsiblePlannerGroup,
N.MainWorkCenter,
N.MaintenanceNotification AS NotificationNumber,
CAST(NULL AS DATE) AS TargetFinishDate,
N.CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM I_MaintenanceNotification N
JOIN I_MaintenanceOrder O ON N.MaintenanceOrder = O.MaintenanceOrder
WHERE N.MaintenanceOrder IS NOT NULL AND N.MaintenanceOrder <> ''
UNION ALL
/* Activity 2: Maintenance Order Created */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Maintenance Order Created' AS Activity,
O.CreationDate AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
O.CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM I_MaintenanceOrder O
UNION ALL
/* Activity 3: Maintenance Priority Changed */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Maintenance Priority Changed' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(H.UDATE, ' '), H.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
H.USERNAME AS CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM CDHDR H
JOIN CDPOS P ON H.OBJECTCLAS = P.OBJECTCLAS AND H.OBJECTID = P.OBJECTID AND H.CHANGENR = P.CHANGENR
JOIN I_MaintenanceOrder O ON O.MaintenanceOrder = H.OBJECTID
WHERE H.OBJECTCLAS = 'ORDER'
AND P.TABNAME = 'AFKO'
AND P.FNAME = 'PRIOK'
UNION ALL
/* Activity 4: Maintenance Order Released */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Maintenance Order Released' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(J.UDATE, ' '), J.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
J.USNAM AS CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM JCDS J
JOIN I_MaintenanceOrder O ON J.OBJNR = CONCAT('OR', O.MaintenanceOrder)
WHERE J.STAT = 'I0002' /* REL Status */
AND J.INACT = ' '
UNION ALL
/* Activity 5: Material Component Added */
SELECT
CAST(C.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Material Component Added' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(C.CreationDate, ' '), COALESCE(C.CreationTime, '000000')), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
O.CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM I_MaintenanceOrderComponent C
JOIN I_MaintenanceOrder O ON C.MaintenanceOrder = O.MaintenanceOrder
UNION ALL
/* Activity 6: Purchase Requisition Created */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Purchase Requisition Created' AS Activity,
PR.CreationDate AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
PR.CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM I_PurchasingDocumentItem PR
JOIN I_MaintenanceOrder O ON PR.ServiceMaintenanceOrder = O.MaintenanceOrder
WHERE PR.PurchasingDocumentCategory = 'B' /* Requisition */
UNION ALL
/* Activity 7: Goods Issue Posted */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Goods Issue Posted' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(MD.CreationDate, ' '), MD.CreationTime), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
MD.CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM I_MaterialDocumentItem MD
JOIN I_MaintenanceOrder O ON MD.MaintenanceOrder = O.MaintenanceOrder
WHERE MD.GoodsMovementType = '261'
UNION ALL
/* Activity 8: Work Order Scheduled (Operation Dispatched) */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Work Order Scheduled' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(J.UDATE, ' '), J.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
J.USNAM AS CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM JCDS J
JOIN I_MaintenanceOrderOperation OP ON J.OBJNR = OP.MaintenanceOrderOperationInternalID
JOIN I_MaintenanceOrder O ON OP.MaintenanceOrder = O.MaintenanceOrder
WHERE J.STAT = 'I0019' /* DSP Dispatched Status */
AND J.INACT = ' '
UNION ALL
/* Activity 9: Order Operation Confirmed */
SELECT
CAST(CNF.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Order Operation Confirmed' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(CNF.CreationDate, ' '), CNF.CreationTime), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
CNF.CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM I_MaintOrderConfirmation CNF
JOIN I_MaintenanceOrder O ON CNF.MaintenanceOrder = O.MaintenanceOrder
WHERE CNF.IsReversed = ' '
UNION ALL
/* Activity 10: Measurement Reading Recorded */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Measurement Reading Recorded' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(M.CreationDate, ' '), M.CreationTime), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
M.CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM I_MeasurementDocument M
JOIN I_MaintenanceOrder O ON M.MaintenanceOrder = O.MaintenanceOrder
UNION ALL
/* Activity 11: Technical Completion Set */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Technical Completion Set' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(J.UDATE, ' '), J.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
J.USNAM AS CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM JCDS J
JOIN I_MaintenanceOrder O ON J.OBJNR = CONCAT('OR', O.MaintenanceOrder)
WHERE J.STAT = 'I0045' /* TECO Status */
AND J.INACT = ' '
UNION ALL
/* Activity 12: Technical Completion Reversed */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Technical Completion Reversed' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(J.UDATE, ' '), J.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
J.USNAM AS CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM JCDS J
JOIN I_MaintenanceOrder O ON J.OBJNR = CONCAT('OR', O.MaintenanceOrder)
WHERE J.STAT = 'I0045' /* TECO Status */
AND J.INACT = 'X' /* Inactive indicates it was removed/reversed */
AND J.CHIND = 'U' /* Update indicator */
UNION ALL
/* Activity 13: Order Cost Settled */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Order Cost Settled' AS Activity,
JE.CreationDateTime AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
JE.CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM I_ActualPlanJrnlEntryItem JE
JOIN I_MaintenanceOrder O ON JE.OrderID = O.MaintenanceOrder
WHERE JE.BusinessTransactionType = 'KOAO' /* Settlement */
UNION ALL
/* Activity 14: Business Completion Set */
SELECT
CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
'Business Completion Set' AS Activity,
TO_TIMESTAMP(CONCAT(CONCAT(J.UDATE, ' '), J.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
'SAP S/4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
O.MaintenanceOrderType,
O.MaintenancePriority,
O.Equipment AS EquipmentNumber,
O.FunctionalLocation,
O.MaintenancePlant,
O.ResponsiblePlannerGroup,
O.MainWorkCenter,
O.MaintenanceNotification AS NotificationNumber,
O.BasicEndDateTime AS TargetFinishDate,
J.USNAM AS CreatedByUser,
O.SystemStatusName AS SystemStatus
FROM JCDS J
JOIN I_MaintenanceOrder O ON J.OBJNR = CONCAT('OR', O.MaintenanceOrder)
WHERE J.STAT = 'I0046' /* CLSD Status */
AND J.INACT = ' 'Steps
- Confirm that direct read access is available for AUFK, AFVC, JEST, QMEL, RESB, EBAN, MSEG, MKPF, AFRU, IMRG, and the applicable change document and controlling document tables. Confirm the exact physical names and field mappings in your SAP database, because field availability and replicated table structures vary by deployment.
- Configure the extraction connection, credentials, extraction start date, extraction end date, and any company specific filters. Use a rolling period of three to six months for initial validation, then extend the period after performance and completeness checks succeed.
- Extract order header attributes from AUFK, operation attributes from AFVC, and status records from JEST. Join order related records through the order number field used in your SAP schema. JEST is a current status table in many SAP implementations, so historical status timestamps require a status history source or change document source. Configure [Your status history table] and its timestamp fields if JEST does not retain status change history.
- Extract notification creation from QMEL, component creation from RESB, purchase requisition creation from EBAN, goods issue information from MSEG and MKPF, confirmations from AFRU, measurement readings from IMRG, and controlling settlement records from [Your controlling document table]. Extract priority changes from [Your order change document table] and [Your change document item table]. These sources are required because AUFK, AFVC, and JEST alone cannot provide all 14 requested activities.
- Normalize all source timestamps into one timezone and a single timestamp data type. Convert SAP date and time fields, or database timestamp fields, into EventTimestamp. Preserve the source timestamp precision available in the system. Apply the configured extraction window to EventTimestamp, not only to the order creation date.
- Create one event row for every source occurrence. Do not rely on ProcessMind to infer events. Assign the exact Activity value specified in the query, populate MaintenanceWorkOrder, EventTimestamp, SourceSystem, LastDataUpdate, and the available recommended attributes, and retain source identifiers where useful for duplicate investigation.
- Deduplicate only identical source events using stable source keys. Do not collapse separate status changes, confirmations, material movements, measurement readings, or settlement documents that occurred at different times. If multiple source records represent one business event, document the selected source precedence in the extraction specification.
- Validate that all 14 activity values occur as explicit rows, that every row has a case identifier and timestamp, and that timestamps fall within the requested extraction period. Compare counts with SAP reports or source table counts for the same filters.
- Export the result as a UTF 8 CSV or another ProcessMind supported event log format. Use one row per event, include the required columns with the exact configured names, preserve ISO 8601 timestamps where supported, and upload the file or configure the database connection in ProcessMind. Set MaintenanceWorkOrder as the case identifier, Activity as the activity column, and EventTimestamp as the timestamp column.
Configuration
- Source tables and mappings: AUFK supplies order header data, AFVC supplies operation data, and JEST supplies system status data. QMEL, RESB, EBAN, MSEG, MKPF, AFRU, IMRG, change document sources, and controlling document sources are also required to produce all 14 activities. Verify exact field names in your SAP database before execution.
- Required output columns: MaintenanceWorkOrder, Activity, EventTimestamp, SourceSystem, and LastDataUpdate must be present on every event row.
- Recommended output columns: MaintenanceOrderType, MaintenancePriority, EquipmentNumber, FunctionalLocation, MaintenancePlant, ResponsiblePlannerGroup, MainWorkCenter, NotificationNumber, TargetFinishDate, CreatedByUser, and SystemStatus should be populated when available.
- Date range: Start with three to six months. Use an inclusive start and exclusive end boundary to avoid duplicate events between incremental runs. For historical backfills, partition extraction by month or another period supported by the database.
- Filters: Configure [Company Code filter], [Order Type filter], [Maintenance Plant filter], [Notification Type filter], and [Document Type filter] according to the reporting scope. Do not apply filters that exclude orders without notifications, operations, components, requisitions, confirmations, measurements, or settlement documents.
- Status history: JEST commonly represents status assignments, but historical status changes may require [Your status history table] or change documents. Configure the source that contains the status change timestamp and status transition history.
- Priority history: Priority changes require [Your order change document table] and [Your change document item table], with the relevant object identifier, field name, old value, new value, and change timestamp.
- Source system value: Replace [Your SAP system identifier] with the stable source system name used by your organization.
- Last data update: Replace [Your extraction timestamp] with the timestamp generated at the start or end of the extraction run, and use the same value for all rows in that run.
- Performance: Index or partition join and filter columns where permitted, restrict the extraction window, select only required columns, process large periods in batches, and materialize intermediate source extracts when direct cross schema joins are slow.
- Prerequisites: Database connectivity, read authorization for all configured source tables, access to the relevant Plant Maintenance, Materials Management, Controlling, and measurement data, and approval to extract operational data. Confirm any replication or licensing constraints for the database platform.
a Sample Query sql
WITH
params AS (
SELECT
CAST('[Start date and time]' AS TIMESTAMP) AS start_ts,
CAST('[End date and time]' AS TIMESTAMP) AS end_ts,
CAST('[Your SAP system identifier]' AS VARCHAR(100)) AS source_system,
CAST('[Your extraction timestamp]' AS TIMESTAMP) AS last_data_update
),
order_base AS (
SELECT
a.[Order number field] AS MaintenanceWorkOrder,
a.[Order type field] AS MaintenanceOrderType,
a.[Priority field] AS MaintenancePriority,
a.[Equipment field] AS EquipmentNumber,
a.[Functional location field] AS FunctionalLocation,
a.[Maintenance plant field] AS MaintenancePlant,
a.[Planner group field] AS ResponsiblePlannerGroup,
a.[Main work center field] AS MainWorkCenter,
a.[Notification number field] AS NotificationNumber,
a.[Target finish date field] AS TargetFinishDate,
a.[Created by field] AS CreatedByUser,
a.[System status field] AS SystemStatus,
a.[Creation date field] AS OrderCreationDate,
a.[Creation time field] AS OrderCreationTime
FROM AUFK a
WHERE a.[Order number field] IS NOT NULL
AND a.[Company code field] = '[Your company code filter]'
),
base_events AS (
SELECT
o.MaintenanceWorkOrder,
'Maintenance Notification Created' AS Activity,
CAST(q.[Notification creation timestamp field] AS TIMESTAMP) AS EventTimestamp,
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, o.SystemStatus
FROM order_base o
JOIN QMEL q ON q.[Notification number field] = o.NotificationNumber
CROSS JOIN params p
WHERE q.[Notification creation timestamp field] >= p.start_ts
AND q.[Notification creation timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Maintenance Order Created',
CAST(o.OrderCreationDate || ' ' || o.OrderCreationTime AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, o.SystemStatus
FROM order_base o CROSS JOIN params p
WHERE CAST(o.OrderCreationDate || ' ' || o.OrderCreationTime AS TIMESTAMP) >= p.start_ts
AND CAST(o.OrderCreationDate || ' ' || o.OrderCreationTime AS TIMESTAMP) < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Maintenance Priority Changed',
CAST(c.[Change timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, c.[New priority value field], o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, o.SystemStatus
FROM order_base o
JOIN [Your order change document table] h ON h.[Order object identifier field] = o.MaintenanceWorkOrder
JOIN [Your change document item table] c ON c.[Change document number field] = h.[Change document number field]
CROSS JOIN params p
WHERE c.[Changed field name field] = '[Your priority field name]'
AND CAST(c.[Change timestamp field] AS TIMESTAMP) >= p.start_ts
AND CAST(c.[Change timestamp field] AS TIMESTAMP) < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Maintenance Order Released',
CAST(s.[Status change timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, 'REL'
FROM order_base o
JOIN [Your status history table] s ON s.[Order number field] = o.MaintenanceWorkOrder
CROSS JOIN params p
WHERE s.[Status code field] = 'REL'
AND s.[Status change timestamp field] >= p.start_ts
AND s.[Status change timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Material Component Added',
CAST(r.[Reservation creation timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, o.SystemStatus
FROM order_base o
JOIN RESB r ON r.[Order number field] = o.MaintenanceWorkOrder
CROSS JOIN params p
WHERE r.[Reservation line creation timestamp field] >= p.start_ts
AND r.[Reservation line creation timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Purchase Requisition Created',
CAST(e.[Purchase requisition creation timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, o.SystemStatus
FROM order_base o
JOIN EBAN e ON e.[Order number field] = o.MaintenanceWorkOrder
CROSS JOIN params p
WHERE e.[Purchase requisition creation timestamp field] >= p.start_ts
AND e.[Purchase requisition creation timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Goods Issue Posted',
CAST(m.[Material document timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, o.SystemStatus
FROM order_base o
JOIN MSEG m ON m.[Order number field] = o.MaintenanceWorkOrder
JOIN MKPF k ON k.[Material document number field] = m.[Material document number field]
AND k.[Material document year field] = m.[Material document year field]
CROSS JOIN params p
WHERE m.[Movement type field] IN ('201', '261')
AND k.[Material document timestamp field] >= p.start_ts
AND k.[Material document timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Work Order Scheduled',
CAST(v.[Dispatch status timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, o.SystemStatus
FROM order_base o
JOIN AFVC v ON v.[Order number field] = o.MaintenanceWorkOrder
CROSS JOIN params p
WHERE v.[Dispatch status field] = '[Your dispatch status value]'
AND v.[Dispatch status timestamp field] >= p.start_ts
AND v.[Dispatch status timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Order Operation Confirmed',
CAST(f.[Confirmation timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, o.SystemStatus
FROM order_base o
JOIN AFRU f ON f.[Order number field] = o.MaintenanceWorkOrder
CROSS JOIN params p
WHERE f.[Confirmation timestamp field] >= p.start_ts
AND f.[Confirmation timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Measurement Reading Recorded',
CAST(i.[Measurement document timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, o.SystemStatus
FROM order_base o
JOIN IMRG i ON i.[Equipment field] = o.EquipmentNumber
CROSS JOIN params p
WHERE i.[Measurement document timestamp field] >= p.start_ts
AND i.[Measurement document timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Technical Completion Set',
CAST(s.[Status change timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, 'TECO'
FROM order_base o
JOIN [Your status history table] s ON s.[Order number field] = o.MaintenanceWorkOrder
CROSS JOIN params p
WHERE s.[Status code field] = 'TECO'
AND s.[Status change timestamp field] >= p.start_ts
AND s.[Status change timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Technical Completion Reversed',
CAST(s.[Status change timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, s.[Resulting system status field]
FROM order_base o
JOIN [Your status history table] s ON s.[Order number field] = o.MaintenanceWorkOrder
CROSS JOIN params p
WHERE s.[Previous status code field] = 'TECO'
AND s.[Status code field] <> 'TECO'
AND s.[Status change timestamp field] >= p.start_ts
AND s.[Status change timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Order Cost Settled',
CAST(c.[Controlling document timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, o.SystemStatus
FROM order_base o
JOIN [Your controlling document table] c ON c.[Order number field] = o.MaintenanceWorkOrder
CROSS JOIN params p
WHERE c.[Controlling document timestamp field] >= p.start_ts
AND c.[Controlling document timestamp field] < p.end_ts
UNION ALL
SELECT o.MaintenanceWorkOrder, 'Business Completion Set',
CAST(s.[Status change timestamp field] AS TIMESTAMP),
o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
o.CreatedByUser, 'CLSD'
FROM order_base o
JOIN [Your status history table] s ON s.[Order number field] = o.MaintenanceWorkOrder
CROSS JOIN params p
WHERE s.[Status code field] = 'CLSD'
AND s.[Status change timestamp field] >= p.start_ts
AND s.[Status change timestamp field] < p.end_ts
)
SELECT
e.MaintenanceWorkOrder,
e.Activity,
e.EventTimestamp,
p.source_system AS SourceSystem,
p.last_data_update AS LastDataUpdate,
e.MaintenanceOrderType,
e.MaintenancePriority,
e.EquipmentNumber,
e.FunctionalLocation,
e.MaintenancePlant,
e.ResponsiblePlannerGroup,
e.MainWorkCenter,
e.NotificationNumber,
e.TargetFinishDate,
e.CreatedByUser,
e.SystemStatus
FROM base_events e
CROSS JOIN params p
WHERE e.MaintenanceWorkOrder IS NOT NULL
AND e.EventTimestamp IS NOT NULL
AND e.EventTimestamp >= p.start_ts
AND e.EventTimestamp < p.end_ts
ORDER BY e.MaintenanceWorkOrder, e.EventTimestamp, e.Activity;Ready to Get Started?
Use these resources to begin mapping your maintenance workflows and identifying opportunities for cost savings. Our framework ensures you have the right data structure to achieve full transparency in your operations.
Fix Asset Maintenance Bottlenecks in SAP Today
Reduce your maintenance cycle time by 30 percent starting now
No credit card required... Setup in 5 minutes