Ihr Anlagenwartungsdaten-Template

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

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

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

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

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

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

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

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

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

work_orders = fetch_all_pages(WO_ENDPOINT, params)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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