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 | |||
| Activity Duration ActivityDuration | Time taken to complete the specific activity. | ||
| Description The duration of the specific step, particularly useful for 'Operation Confirmed' activities where labor hours are recorded. This maps to 'ProcessingTime' in the generic model and is used to calculate efficiency and labor utilization. Why it matters Measures the actual effort spent on tasks. Where to get SAP Table AFRU, Field ISDD/ISDZ vs IEDD/IEDZ Examples 2.5 hours45 minutes | |||
| 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
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 = ' '