창고 관리 데이터 템플릿

SET VARIABLE PREFERRED_CURRENCY='USD', LOGLEVEL=0, v_start_date = '2023-01-01', v_end_date = '2024-01-01';

SELECT
    CAST("Inbound Shipment Details"."Shipment Number" AS CHAR) AS "WarehouseOrder",
    'Inbound Delivery Created' AS "ActivityName",
    "Inbound Shipment Details"."Creation Date" AS "EventStartTime",
    "Inbound Shipment Details"."Creation Date" AS "EventEndTime",
    NULL AS "UserOperatorId",
    "Inbound Shipment Header"."Shipment Type" AS "WarehouseOrderType",
    "Inbound Shipment Header"."Expected Delivery Date" AS "RequestedCompletionDate",
    NULL AS "ActualQuantity",
    "Inbound Shipment Header"."Shipment Priority" AS "PriorityLevel",
    CASE WHEN "Inbound Shipment Header"."Actual Delivery Date" > "Inbound Shipment Header"."Expected Delivery Date" THEN 'Late' ELSE 'On-Time' END AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Inbound Shipment Details"."Creation Date" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Inbound Shipment Details"."Shipment Number" AS CHAR) AS "WarehouseOrder",
    'Goods Arrived' AS "ActivityName",
    "Appointment Details"."Check In Time" AS "EventStartTime",
    "Appointment Details"."Check In Time" AS "EventEndTime",
    "Appointment Details"."Checked In By User" AS "UserOperatorId",
    "Inbound Shipment Header"."Shipment Type" AS "WarehouseOrderType",
    "Inbound Shipment Header"."Expected Delivery Date" AS "RequestedCompletionDate",
    NULL AS "ActualQuantity",
    "Inbound Shipment Header"."Shipment Priority" AS "PriorityLevel",
    CASE WHEN "Inbound Shipment Header"."Actual Delivery Date" > "Inbound Shipment Header"."Expected Delivery Date" THEN 'Late' ELSE 'On-Time' END AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Appointment Details"."Check In Time" IS NOT NULL AND "Appointment Details"."Check In Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Receiving Transaction Details"."Shipment Number" AS CHAR) AS "WarehouseOrder",
    'Goods Received' AS "ActivityName",
    "Receiving Transaction Details"."Transaction Creation Time" AS "EventStartTime",
    "Receiving Transaction Details"."Transaction Creation Time" AS "EventEndTime",
    "Receiving Transaction Details"."Received By User" AS "UserOperatorId",
    NULL AS "WarehouseOrderType",
    NULL AS "RequestedCompletionDate",
    "Receiving Transaction Details"."Received Quantity" AS "ActualQuantity",
    NULL AS "PriorityLevel",
    NULL AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Receiving Transaction Details"."Transaction Creation Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Inventory Quality Inspection Results"."Order Number" AS CHAR) AS "WarehouseOrder",
    'Quality Inspection Performed' AS "ActivityName",
    "Inventory Quality Inspection Results"."Inspection Completion Time" AS "EventStartTime",
    "Inventory Quality Inspection Results"."Inspection Completion Time" AS "EventEndTime",
    "Inventory Quality Inspection Results"."Inspected By User" AS "UserOperatorId",
    NULL AS "WarehouseOrderType",
    NULL AS "RequestedCompletionDate",
    "Inventory Quality Inspection Results"."Inspected Quantity" AS "ActualQuantity",
    NULL AS "PriorityLevel",
    NULL AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Inventory Quality Inspection Results"."Inspection Completion Time" IS NOT NULL AND "Inventory Quality Inspection Results"."Inspection Completion Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST(CASE WHEN "Task Details"."Task Type" = 'Putaway' THEN "Task Details"."Reference Number" ELSE NULL END AS CHAR) AS "WarehouseOrder",
    'Putaway Task Created' AS "ActivityName",
    "Task Details"."Task Creation Time" AS "EventStartTime",
    "Task Details"."Task Completion Time" AS "EventEndTime",
    "Task Details"."Created By User" AS "UserOperatorId",
    "Task Details"."Task Type" AS "WarehouseOrderType",
    NULL AS "RequestedCompletionDate",
    "Task Details"."Task Quantity" AS "ActualQuantity",
    CAST("Task Details"."Task Priority" AS CHAR) AS "PriorityLevel",
    NULL AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Task Details"."Task Type" = 'Putaway' AND "Task Details"."Task Creation Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST(CASE WHEN "Task Details"."Task Type" = 'Putaway' THEN "Task Details"."Reference Number" ELSE NULL END AS CHAR) AS "WarehouseOrder",
    'Goods Put Away' AS "ActivityName",
    "Task Details"."Task Completion Time" AS "EventStartTime",
    "Task Details"."Task Completion Time" AS "EventEndTime",
    "Task Details"."Completed By User" AS "UserOperatorId",
    "Task Details"."Task Type" AS "WarehouseOrderType",
    NULL AS "RequestedCompletionDate",
    "Task Details"."Task Quantity" AS "ActualQuantity",
    CAST("Task Details"."Task Priority" AS CHAR) AS "PriorityLevel",
    NULL AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Task Details"."Task Type" = 'Putaway' AND "Task Details"."Task Status" = 'Completed' AND "Task Details"."Task Completion Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Inventory Transaction Details"."Reference Document Number" AS CHAR) AS "WarehouseOrder",
    'Inventory Adjusted' AS "ActivityName",
    "Inventory Transaction Details"."Transaction Creation Time" AS "EventStartTime",
    "Inventory Transaction Details"."Transaction Creation Time" AS "EventEndTime",
    "Inventory Transaction Details"."Created By User" AS "UserOperatorId",
    "Inventory Transaction Details"."Transaction Type" AS "WarehouseOrderType",
    NULL AS "RequestedCompletionDate",
    "Inventory Transaction Details"."Transaction Quantity" AS "ActualQuantity",
    NULL AS "PriorityLevel",
    NULL AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Inventory Transaction Details"."Transaction Type" IN ('Cycle Count Adjustment', 'Manual Adjustment') AND "Inventory Transaction Details"."Transaction Creation Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Task Details"."Order Number" AS CHAR) AS "WarehouseOrder",
    'Picking Task Created' AS "ActivityName",
    "Task Details"."Task Creation Time" AS "EventStartTime",
    "Task Details"."Task Completion Time" AS "EventEndTime",
    "Task Details"."Created By User" AS "UserOperatorId",
    "Task Details"."Task Type" AS "WarehouseOrderType",
    NULL AS "RequestedCompletionDate",
    "Task Details"."Task Quantity" AS "ActualQuantity",
    CAST("Task Details"."Task Priority" AS CHAR) AS "PriorityLevel",
    NULL AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Task Details"."Task Type" = 'Picking' AND "Task Details"."Task Creation Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Task Details"."Order Number" AS CHAR) AS "WarehouseOrder",
    'Goods Picked' AS "ActivityName",
    "Task Details"."Task Completion Time" AS "EventStartTime",
    "Task Details"."Task Completion Time" AS "EventEndTime",
    "Task Details"."Completed By User" AS "UserOperatorId",
    "Task Details"."Task Type" AS "WarehouseOrderType",
    NULL AS "RequestedCompletionDate",
    "Task Details"."Task Quantity" AS "ActualQuantity",
    CAST("Task Details"."Task Priority" AS CHAR) AS "PriorityLevel",
    NULL AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Task Details"."Task Type" = 'Picking' AND "Task Details"."Task Status" = 'Completed' AND "Task Details"."Task Completion Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Outbound Shipment Details"."Order Number" AS CHAR) AS "WarehouseOrder",
    'Packing Started' AS "ActivityName",
    "Packing Details"."Packing Start Time" AS "EventStartTime",
    "Packing Details"."Packing End Time" AS "EventEndTime",
    "Packing Details"."Packed By User" AS "UserOperatorId",
    "Outbound Shipment Header"."Order Type" AS "WarehouseOrderType",
    "Outbound Shipment Header"."Requested Ship Date" AS "RequestedCompletionDate",
    "Packing Details"."Packed Quantity" AS "ActualQuantity",
    "Outbound Shipment Header"."Shipment Priority" AS "PriorityLevel",
    CASE WHEN "Outbound Shipment Header"."Actual Ship Date" > "Outbound Shipment Header"."Requested Ship Date" THEN 'Late' ELSE 'On-Time' END AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Packing Details"."Packing Start Time" IS NOT NULL AND "Packing Details"."Packing Start Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Outbound Shipment Details"."Order Number" AS CHAR) AS "WarehouseOrder",
    'Goods Packed' AS "ActivityName",
    "Packing Details"."Packing End Time" AS "EventStartTime",
    "Packing Details"."Packing End Time" AS "EventEndTime",
    "Packing Details"."Packed By User" AS "UserOperatorId",
    "Outbound Shipment Header"."Order Type" AS "WarehouseOrderType",
    "Outbound Shipment Header"."Requested Ship Date" AS "RequestedCompletionDate",
    "Packing Details"."Packed Quantity" AS "ActualQuantity",
    "Outbound Shipment Header"."Shipment Priority" AS "PriorityLevel",
    CASE WHEN "Outbound Shipment Header"."Actual Ship Date" > "Outbound Shipment Header"."Requested Ship Date" THEN 'Late' ELSE 'On-Time' END AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Packing Details"."Packing End Time" IS NOT NULL AND "Packing Details"."Packing End Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Outbound Shipment Details"."Order Number" AS CHAR) AS "WarehouseOrder",
    'Staged For Shipment' AS "ActivityName",
    "Outbound Load Details"."Staging Time" AS "EventStartTime",
    "Outbound Load Details"."Staging Time" AS "EventEndTime",
    NULL AS "UserOperatorId",
    "Outbound Shipment Header"."Order Type" AS "WarehouseOrderType",
    "Outbound Shipment Header"."Requested Ship Date" AS "RequestedCompletionDate",
    "Outbound Shipment Details"."Shipped Quantity" AS "ActualQuantity",
    "Outbound Shipment Header"."Shipment Priority" AS "PriorityLevel",
    CASE WHEN "Outbound Shipment Header"."Actual Ship Date" > "Outbound Shipment Header"."Requested Ship Date" THEN 'Late' ELSE 'On-Time' END AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Outbound Load Details"."Staging Time" IS NOT NULL AND "Outbound Load Details"."Staging Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Outbound Shipment Details"."Order Number" AS CHAR) AS "WarehouseOrder",
    'Loading Started' AS "ActivityName",
    "Outbound Load Details"."Loading Start Time" AS "EventStartTime",
    "Outbound Load Details"."Loading End Time" AS "EventEndTime",
    "Outbound Load Details"."Loaded By User" AS "UserOperatorId",
    "Outbound Shipment Header"."Order Type" AS "WarehouseOrderType",
    "Outbound Shipment Header"."Requested Ship Date" AS "RequestedCompletionDate",
    "Outbound Shipment Details"."Shipped Quantity" AS "ActualQuantity",
    "Outbound Shipment Header"."Shipment Priority" AS "PriorityLevel",
    CASE WHEN "Outbound Shipment Header"."Actual Ship Date" > "Outbound Shipment Header"."Requested Ship Date" THEN 'Late' ELSE 'On-Time' END AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Outbound Load Details"."Loading Start Time" IS NOT NULL AND "Outbound Load Details"."Loading Start Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Outbound Shipment Header"."Order Number" AS CHAR) AS "WarehouseOrder",
    'Shipment Dispatched' AS "ActivityName",
    "Outbound Shipment Header"."Actual Ship Date" AS "EventStartTime",
    "Outbound Shipment Header"."Actual Ship Date" AS "EventEndTime",
    NULL AS "UserOperatorId",
    "Outbound Shipment Header"."Order Type" AS "WarehouseOrderType",
    "Outbound Shipment Header"."Requested Ship Date" AS "RequestedCompletionDate",
    "Outbound Shipment Details"."Shipped Quantity" AS "ActualQuantity",
    "Outbound Shipment Header"."Shipment Priority" AS "PriorityLevel",
    CASE WHEN "Outbound Shipment Header"."Actual Ship Date" > "Outbound Shipment Header"."Requested Ship Date" THEN 'Late' ELSE 'On-Time' END AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Outbound Shipment Header"."Status" = 'Shipped' AND "Outbound Shipment Header"."Actual Ship Date" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Outbound Shipment Header"."Order Number" AS CHAR) AS "WarehouseOrder",
    'Warehouse Order Completed' AS "ActivityName",
    "Outbound Shipment Header"."Last Update Time" AS "EventStartTime",
    "Outbound Shipment Header"."Last Update Time" AS "EventEndTime",
    "Outbound Shipment Header"."Last Updated By" AS "UserOperatorId",
    "Outbound Shipment Header"."Order Type" AS "WarehouseOrderType",
    "Outbound Shipment Header"."Requested Ship Date" AS "RequestedCompletionDate",
    NULL AS "ActualQuantity",
    "Outbound Shipment Header"."Shipment Priority" AS "PriorityLevel",
    CASE WHEN "Outbound Shipment Header"."Actual Ship Date" > "Outbound Shipment Header"."Requested Ship Date" THEN 'Late' ELSE 'On-Time' END AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Outbound Shipment Header"."Status" = 'Delivered' AND "Outbound Shipment Header"."Last Update Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

UNION ALL

SELECT
    CAST("Outbound Shipment Header"."Order Number" AS CHAR) AS "WarehouseOrder",
    'Warehouse Order Cancelled' AS "ActivityName",
    "Outbound Shipment Header"."Last Update Time" AS "EventStartTime",
    "Outbound Shipment Header"."Last Update Time" AS "EventEndTime",
    "Outbound Shipment Header"."Last Updated By" AS "UserOperatorId",
    "Outbound Shipment Header"."Order Type" AS "WarehouseOrderType",
    "Outbound Shipment Header"."Requested Ship Date" AS "RequestedCompletionDate",
    NULL AS "ActualQuantity",
    "Outbound Shipment Header"."Shipment Priority" AS "PriorityLevel",
    NULL AS "SLAState",
    'Oracle WMS Cloud' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM "Warehouse Operations - Real Time"
WHERE "Outbound Shipment Header"."Status" = 'Cancelled' AND "Outbound Shipment Header"."Last Update Time" BETWEEN TIMESTAMP '@{v_start_date}' AND TIMESTAMP '@{v_end_date}'

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

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Oracle WMS Cloud instance details
BASE_URL = class="hl-string">'https://{your_instance_id}.wms.ocs.oraclecloud.com/wms/api/v11'
FACILITY_ID = class="hl-string">'[Your Facility ID]'
USERNAME = class="hl-string">'[Your API Username]'
PASSWORD = class="hl-string">'[Your API Password]'

class=class="hl-string">"hl-comment"># Set the date range for extraction
START_DATE = class="hl-string">'YYYY-MM-DD' class=class="hl-string">"hl-comment"># e.g., class="hl-string">'2023-01-01'
END_DATE = class="hl-string">'YYYY-MM-DD'   class=class="hl-string">"hl-comment"># e.g., class="hl-string">'2023-06-30'

class=class="hl-string">"hl-comment"># --- End Configuration ---

LIMIT = 500
SOURCE_SYSTEM = class="hl-string">'Oracle WMS Cloud'
LAST_DATA_UPDATE = datetime.utcnow().isoformat() + class="hl-string">'Z'

class=class="hl-string">"hl-comment"># Function to perform paginated GET requests
def get_paginated_data(endpoint, params={}):
    all_items = []
    offset = 0
    auth_header = class="hl-string">'Basic ' + base64.b64encode(fclass="hl-string">'{USERNAME}:{PASSWORD}'.encode(class="hl-string">'utf-8')).decode(class="hl-string">'utf-8')
    headers = {
        class="hl-string">'Authorization': auth_header,
        class="hl-string">'Accept': class="hl-string">'application/json'
    }
    
    while True:
        paginated_params = params.copy()
        paginated_params[class="hl-string">'offset'] = offset
        paginated_params[class="hl-string">'limit'] = LIMIT
        
        try:
            response = requests.get(fclass="hl-string">'{BASE_URL}/facility/{FACILITY_ID}/{endpoint}', headers=headers, params=paginated_params)
            response.raise_for_status()
            data = response.json()
            items = data.get(class="hl-string">'items', [])
            if not items:
                break
            all_items.extend(items)
            offset += len(items)
            print(fclass="hl-string">'Fetched {len(all_items)} records from {endpoint}...')
            class=class="hl-string">"hl-comment"># Respect potential rate limits
            time.sleep(0.5)
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">'Error fetching data from {endpoint}: {e}')
            break
    return all_items

def main():
    events = []
    date_filter = fclass="hl-string">"create_ts > '{START_DATE}T00:00:00Z' and create_ts < '{END_DATE}T23:59:59Z'"

    class=class="hl-string">"hl-comment"># 1. Inbound Delivery Created (from ASN/Inbound Shipments)
    asn_data = get_paginated_data(class="hl-string">'inbound_shipment', {class="hl-string">'q': date_filter})
    for item in asn_data:
        events.append({
            class="hl-string">'WarehouseOrder': item.get(class="hl-string">'shipment_nbr'),
            class="hl-string">'ActivityName': class="hl-string">'Inbound Delivery Created',
            class="hl-string">'EventStartTime': item.get(class="hl-string">'create_ts'),
            class="hl-string">'EventEndTime': None,
            class="hl-string">'UserOperatorId': item.get(class="hl-string">'user_id'),
            class="hl-string">'WarehouseOrderType': class="hl-string">'Inbound',
            class="hl-string">'RequestedCompletionDate': item.get(class="hl-string">'required_ship_date'),
            class="hl-string">'ActualQuantity': None,
            class="hl-string">'PriorityLevel': item.get(class="hl-string">'priority'),
            class="hl-string">'SLAState': None
        })

    class=class="hl-string">"hl-comment"># 2-7, 9-14: Most activities are in Inventory History
    inv_history_filter = fclass="hl-string">"tran_create_ts > '{START_DATE}T00:00:00Z' and tran_create_ts < '{END_DATE}T23:59:59Z'"
    inv_history_data = get_paginated_data(class="hl-string">'inventory_history', {class="hl-string">'q': inv_history_filter})
    
    activity_map = {
        class="hl-string">'ARV': class="hl-string">'Goods Arrived',
        class="hl-string">'REC': class="hl-string">'Goods Received',
        class="hl-string">'QCH': class="hl-string">'Quality Inspection Performed',
        class="hl-string">'MVL': class="hl-string">'Goods Put Away', class=class="hl-string">"hl-comment"># Move to LPN
        class="hl-string">'ADJ': class="hl-string">'Inventory Adjusted',
        class="hl-string">'PCK': class="hl-string">'Goods Picked',
        class="hl-string">'PAK': class="hl-string">'Goods Packed', class=class="hl-string">"hl-comment"># Often part of a packing transaction
        class="hl-string">'LOD': class="hl-string">'Loading Started', class=class="hl-string">"hl-comment"># Often a move to a shipping door location
        class="hl-string">'SHP': class="hl-string">'Shipment Dispatched'
    }

    for item in inv_history_data:
        tran_type = item.get(class="hl-string">'tran_type')
        if tran_type in activity_map:
            class=class="hl-string">"hl-comment"># Use order_nbr for outbound, shipment_nbr for inbound if available
            order_id = item.get(class="hl-string">'order_nbr') or item.get(class="hl-string">'shipment_nbr') or class="hl-string">'[Unknown]'
            events.append({
                class="hl-string">'WarehouseOrder': order_id,
                class="hl-string">'ActivityName': activity_map[tran_type],
                class="hl-string">'EventStartTime': item.get(class="hl-string">'tran_create_ts'),
                class="hl-string">'EventEndTime': None,
                class="hl-string">'UserOperatorId': item.get(class="hl-string">'user_id'),
                class="hl-string">'WarehouseOrderType': class="hl-string">'Inbound' if item.get(class="hl-string">'shipment_nbr') else class="hl-string">'Outbound',
                class="hl-string">'RequestedCompletionDate': None,
                class="hl-string">'ActualQuantity': item.get(class="hl-string">'tran_qty'),
                class="hl-string">'PriorityLevel': None,
                class="hl-string">'SLAState': None
            })

    class=class="hl-string">"hl-comment"># 8, 5: Task Creation Events
    task_filter = fclass="hl-string">"create_ts > '{START_DATE}T00:00:00Z' and create_ts < '{END_DATE}T23:59:59Z'"
    task_data = get_paginated_data(class="hl-string">'task', {class="hl-string">'q': task_filter})
    for item in task_data:
        task_type = item.get(class="hl-string">'task_type')
        activity_name = None
        if task_type in (class="hl-string">'PUT', class="hl-string">'RPL'):
            activity_name = class="hl-string">'Putaway Task Created'
        elif task_type in (class="hl-string">'PCK', class="hl-string">'WAV'):
            activity_name = class="hl-string">'Picking Task Created'
        
        if activity_name:
            order_id = item.get(class="hl-string">'order_nbr') or item.get(class="hl-string">'shipment_nbr') or class="hl-string">'[Unknown]'
            events.append({
                class="hl-string">'WarehouseOrder': order_id,
                class="hl-string">'ActivityName': activity_name,
                class="hl-string">'EventStartTime': item.get(class="hl-string">'create_ts'),
                class="hl-string">'EventEndTime': None,
                class="hl-string">'UserOperatorId': item.get(class="hl-string">'assigned_user_id'),
                class="hl-string">'WarehouseOrderType': class="hl-string">'Inbound' if activity_name == class="hl-string">'Putaway Task Created' else class="hl-string">'Outbound',
                class="hl-string">'RequestedCompletionDate': None,
                class="hl-string">'ActualQuantity': item.get(class="hl-string">'task_qty'),
                class="hl-string">'PriorityLevel': item.get(class="hl-string">'priority'),
                class="hl-string">'SLAState': None
            })
            
    class=class="hl-string">"hl-comment"># Additional specific events if not in inventory history
    class=class="hl-string">"hl-comment"># 10. Packing Started, 12. Staged For Shipment - These can be complex and may require logic based on location moves or container status changes. We use placeholders from Inventory History as a starting point.
    class=class="hl-string">"hl-comment"># For this script, class="hl-string">'PAK' from inventory_history implies both started and completed for simplicity.
    for item in inv_history_data:
        if item.get(class="hl-string">'tran_type') == class="hl-string">'PAK':
             order_id = item.get(class="hl-string">'order_nbr') or class="hl-string">'[Unknown]'
             events.append({
                class="hl-string">'WarehouseOrder': order_id,
                class="hl-string">'ActivityName': class="hl-string">'Packing Started',
                class="hl-string">'EventStartTime': item.get(class="hl-string">'tran_create_ts'), class=class="hl-string">"hl-comment"># Assuming start is same as completion
                class="hl-string">'EventEndTime': item.get(class="hl-string">'tran_create_ts'),
                class="hl-string">'UserOperatorId': item.get(class="hl-string">'user_id'),
                class="hl-string">'WarehouseOrderType': class="hl-string">'Outbound',
                class="hl-string">'RequestedCompletionDate': None,
                class="hl-string">'ActualQuantity': item.get(class="hl-string">'tran_qty'),
                class="hl-string">'PriorityLevel': None,
                class="hl-string">'SLAState': None
            })
        class=class="hl-string">"hl-comment"># Staging is a move to a staging location type
        if item.get(class="hl-string">'tran_type') == class="hl-string">'MVL' and item.get(class="hl-string">'dest_locn_type') == class="hl-string">'STG':
             order_id = item.get(class="hl-string">'order_nbr') or class="hl-string">'[Unknown]'
             events.append({
                class="hl-string">'WarehouseOrder': order_id,
                class="hl-string">'ActivityName': class="hl-string">'Staged For Shipment',
                class="hl-string">'EventStartTime': item.get(class="hl-string">'tran_create_ts'),
                class="hl-string">'EventEndTime': None,
                class="hl-string">'UserOperatorId': item.get(class="hl-string">'user_id'),
                class="hl-string">'WarehouseOrderType': class="hl-string">'Outbound',
                class="hl-string">'RequestedCompletionDate': None,
                class="hl-string">'ActualQuantity': item.get(class="hl-string">'tran_qty'),
                class="hl-string">'PriorityLevel': None,
                class="hl-string">'SLAState': None
            })

    class=class="hl-string">"hl-comment"># 15, 16. Warehouse Order Completed / Cancelled
    order_filter = fclass="hl-string">"mod_ts > '{START_DATE}T00:00:00Z' and mod_ts < '{END_DATE}T23:59:59Z'"
    order_data = get_paginated_data(class="hl-string">'order', {class="hl-string">'q': order_filter})
    for item in order_data:
        status = str(item.get(class="hl-string">'status', class="hl-string">''))
        activity_name = None
        if status == class="hl-string">'90': class=class="hl-string">"hl-comment"># Shipped / Delivered status code
            activity_name = class="hl-string">'Warehouse Order Completed'
        elif status == class="hl-string">'99': class=class="hl-string">"hl-comment"># Cancelled status code
            activity_name = class="hl-string">'Warehouse Order Cancelled'
        
        if activity_name:
            events.append({
                class="hl-string">'WarehouseOrder': item.get(class="hl-string">'order_nbr'),
                class="hl-string">'ActivityName': activity_name,
                class="hl-string">'EventStartTime': item.get(class="hl-string">'mod_ts'),
                class="hl-string">'EventEndTime': item.get(class="hl-string">'mod_ts'),
                class="hl-string">'UserOperatorId': item.get(class="hl-string">'mod_user_id'),
                class="hl-string">'WarehouseOrderType': class="hl-string">'Outbound', class=class="hl-string">"hl-comment"># Assuming order endpoint is for outbound
                class="hl-string">'RequestedCompletionDate': item.get(class="hl-string">'required_ship_date'),
                class="hl-string">'ActualQuantity': item.get(class="hl-string">'shipped_qty'),
                class="hl-string">'PriorityLevel': item.get(class="hl-string">'priority'),
                class="hl-string">'SLAState': None
            })

    class=class="hl-string">"hl-comment"># Final processing
    if not events:
        print(class="hl-string">'No events found for the given criteria.')
        return

    df = pd.DataFrame(events)

    class=class="hl-string">"hl-comment"># Add standard columns
    df[class="hl-string">'SourceSystem'] = SOURCE_SYSTEM
    df[class="hl-string">'LastDataUpdate'] = LAST_DATA_UPDATE

    class=class="hl-string">"hl-comment"># Clean up and order columns
    df = df[[class="hl-string">'WarehouseOrder', class="hl-string">'ActivityName', class="hl-string">'EventStartTime', class="hl-string">'EventEndTime', class="hl-string">'UserOperatorId', class="hl-string">'WarehouseOrderType', class="hl-string">'RequestedCompletionDate', class="hl-string">'ActualQuantity', class="hl-string">'PriorityLevel', class="hl-string">'SLAState', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate']]
    df.sort_values(by=[class="hl-string">'WarehouseOrder', class="hl-string">'EventStartTime'], inplace=True)

    class=class="hl-string">"hl-comment"># Save to CSV
    output_filename = class="hl-string">'oracle_wms_event_log.csv'
    df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%fZ')
    print(fclass="hl-string">'Successfully created event log: {output_filename}')

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