창고 관리 데이터 템플릿

publish data
 where wh_id = '[Your Warehouse ID]'
   and event_time between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
|
[
    /* 1. Warehouse Order Created */
    select
        ordnum as WarehouseOrder,
        'Warehouse Order Created' as ActivityName,
        adddte as EventStartTime,
        moddte as EventEndTime,
        add_usr_id as UserOperatorId,
        ordqty as PlannedQuantity,
        null as ActualQuantity,
        null as StorageLocation,
        req_ship_dte as RequestedCompletionDate,
        prifld as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from ord_hdr
    where ordtyp in ('ORD', 'INB')
      and adddte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 2. Inbound Delivery Notified */
    select
        supnum as WarehouseOrder, /* ASN number often used as the order key for inbound */
        'Inbound Delivery Notified' as ActivityName,
        adddte as EventStartTime,
        moddte as EventEndTime,
        add_usr_id as UserOperatorId,
        null as PlannedQuantity,
        null as ActualQuantity,
        null as StorageLocation,
        expdte as RequestedCompletionDate,
        null as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from asnhdr
    where adddte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 3. Goods Arrived at Dock */
    select
        refnum as WarehouseOrder, 
        'Goods Arrived at Dock' as ActivityName,
        cmpl_dte as EventStartTime,
        cmpl_dte as EventEndTime,
        mod_usr_id as UserOperatorId,
        null as PlannedQuantity,
        null as ActualQuantity,
        dstloc as StorageLocation, /* Typically a receiving dock location */
        null as RequestedCompletionDate,
        null as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from trn_log
    where trncod = 'RCV_ARVL'
      and cmpl_dte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 4. Goods Received and Counted */
    select
        ordnum as WarehouseOrder,
        'Goods Received and Counted' as ActivityName,
        cmpl_dte as EventStartTime,
        cmpl_dte as EventEndTime,
        mod_usr_id as UserOperatorId,
        untqty as PlannedQuantity,
        actqty as ActualQuantity,
        srcloc as StorageLocation,
        null as RequestedCompletionDate,
        null as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from invmov
    where trntyp = 'R' /* Standard receipt transaction type */
      and cmpl_dte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 5. Quality Inspection Performed */
    select
        ordnum as WarehouseOrder, 
        'Quality Inspection Performed' as ActivityName,
        cmpl_dte as EventStartTime,
        cmpl_dte as EventEndTime,
        mod_usr_id as UserOperatorId,
        untqty as PlannedQuantity,
        actqty as ActualQuantity,
        srcloc as StorageLocation,
        null as RequestedCompletionDate,
        null as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from invmov
    where trntyp = 'H' and trncod = 'QA_CMP' /* Example transaction for QA Hold Release/Complete */
      and cmpl_dte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 6. Putaway Task Created */
    select
        ordnum as WarehouseOrder,
        'Putaway Task Created' as ActivityName,
        adddte as EventStartTime,
        moddte as EventEndTime,
        add_usr_id as UserOperatorId,
        pckqty as PlannedQuantity,
        null as ActualQuantity,
        srcloc as StorageLocation,
        null as RequestedCompletionDate,
        prifld as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from pckwrk_dtl
    where wrktyp = 'P' /* Putaway work type */
      and adddte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 7. Goods Put Away in Storage */
    select
        ordnum as WarehouseOrder,
        'Goods Put Away in Storage' as ActivityName,
        cmpl_dte as EventStartTime,
        cmpl_dte as EventEndTime,
        mod_usr_id as UserOperatorId,
        untqty as PlannedQuantity,
        actqty as ActualQuantity,
        dstloc as StorageLocation,
        null as RequestedCompletionDate,
        null as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from invmov
    where trntyp = 'M' and trncod = 'PUTAWAY' /* Move transaction for putaway */
      and cmpl_dte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 8. Picking Task Created */
    select
        ordnum as WarehouseOrder,
        'Picking Task Created' as ActivityName,
        adddte as EventStartTime,
        moddte as EventEndTime,
        add_usr_id as UserOperatorId,
        pckqty as PlannedQuantity,
        null as ActualQuantity,
        srcloc as StorageLocation,
        null as RequestedCompletionDate,
        prifld as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from pckwrk_dtl
    where wrktyp = 'O' /* Outbound Picking work type */
      and adddte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 9. Goods Picked from Storage */
    select
        ordnum as WarehouseOrder,
        'Goods Picked from Storage' as ActivityName,
        pk_end_dte as EventStartTime,
        pk_end_dte as EventEndTime,
        pckr_id as UserOperatorId,
        pckqty as PlannedQuantity,
        actqty as ActualQuantity,
        srcloc as StorageLocation,
        null as RequestedCompletionDate,
        prifld as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from pckwrk_dtl
    where wrktyp = 'O'
      and statcod = 'P' /* Status 'Picked' */
      and pk_end_dte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 10. Packing Initiated */
    select
        ordnum as WarehouseOrder,
        'Packing Initiated' as ActivityName,
        cmpl_dte as EventStartTime,
        cmpl_dte as EventEndTime,
        mod_usr_id as UserOperatorId,
        null as PlannedQuantity,
        null as ActualQuantity,
        dstloc as StorageLocation, /* Packing station */
        null as RequestedCompletionDate,
        null as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from trn_log
    where trncod = 'PACK_INIT'
      and cmpl_dte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 11. Goods Packed */
    select
        ordnum as WarehouseOrder,
        'Goods Packed' as ActivityName,
        moddte as EventStartTime,
        moddte as EventEndTime,
        mod_usr_id as UserOperatorId,
        null as PlannedQuantity,
        null as ActualQuantity,
        null as StorageLocation,
        req_ship_dte as RequestedCompletionDate,
        prifld as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from ord_hdr
    where statcod >= 80 and statcod < 90 /* Example status range for Packed */
      and moddte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 12. Staging for Shipment */
    select
        ordnum as WarehouseOrder,
        'Staging for Shipment' as ActivityName,
        cmpl_dte as EventStartTime,
        cmpl_dte as EventEndTime,
        mod_usr_id as UserOperatorId,
        untqty as PlannedQuantity,
        actqty as ActualQuantity,
        dstloc as StorageLocation, /* Staging lane */
        null as RequestedCompletionDate,
        null as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from invmov
    where trncod = 'STG_MOVE' /* Move to staging transaction */
      and cmpl_dte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 13. Shipment Dispatched */
    select
        ordnum as WarehouseOrder,
        'Shipment Dispatched' as ActivityName,
        act_ship_dte as EventStartTime,
        act_ship_dte as EventEndTime,
        mod_usr_id as UserOperatorId,
        ordqty as PlannedQuantity,
        shpqty as ActualQuantity,
        null as StorageLocation,
        req_ship_dte as RequestedCompletionDate,
        prifld as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from ord_hdr
    where statcod = 90 /* Status Shipped */
      and act_ship_dte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 14. Warehouse Order Completed */
    select
        ordnum as WarehouseOrder,
        'Warehouse Order Completed' as ActivityName,
        moddte as EventStartTime,
        moddte as EventEndTime,
        mod_usr_id as UserOperatorId,
        ordqty as PlannedQuantity,
        shpqty as ActualQuantity,
        null as StorageLocation,
        req_ship_dte as RequestedCompletionDate,
        prifld as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from ord_hdr
    where statcod = 99 /* Status Completed/Closed */
      and moddte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'

    union all

    /* 15. Warehouse Order Canceled */
    select
        ordnum as WarehouseOrder,
        'Warehouse Order Canceled' as ActivityName,
        moddte as EventStartTime,
        moddte as EventEndTime,
        mod_usr_id as UserOperatorId,
        ordqty as PlannedQuantity,
        null as ActualQuantity,
        null as StorageLocation,
        req_ship_dte as RequestedCompletionDate,
        prifld as PriorityLevel,
        'Blue Yonder WMS' as SourceSystem,
        sysdate as LastDataUpdate
    from ord_hdr
    where statcod = 91 /* Example Canceled status */
      and moddte between to_date(@start_date, 'YYYY-MM-DD') and to_date(@end_date, 'YYYY-MM-DD')
      and wh_id = '[Your Warehouse ID]'
]

WITH BaseOrders AS (
    SELECT 
        ordnum AS WarehouseOrder
    FROM 
        ord
    WHERE 
        adddte >= '2023-01-01' -- Placeholder: Set your start date
        AND adddte < '2023-07-01' -- Placeholder: Set your end date
        AND wh_id = '[Your_Warehouse_ID]' -- Placeholder: Set your warehouse ID
)
-- 1. Warehouse Order Created
SELECT
    o.ordnum AS WarehouseOrder,
    'Warehouse Order Created' AS ActivityName,
    o.adddte AS EventStartTime,
    o.adddte AS EventEndTime,
    o.add_usr_id AS UserOperatorId,
    o.req_shp_dte AS RequestedCompletionDate,
    o.prirty AS PriorityLevel,
    CAST(o.ordqty AS DECIMAL(18, 4)) AS PlannedQuantity,
    NULL AS ActualQuantity,
    NULL AS StorageLocation,
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM ord o
WHERE o.ordnum IN (SELECT WarehouseOrder FROM BaseOrders)

UNION ALL

-- 2. Inbound Delivery Notified (ASN Received)
SELECT 
    a.ordnum AS WarehouseOrder,
    'Inbound Delivery Notified' AS ActivityName,
    a.adddte AS EventStartTime,
    a.adddte AS EventEndTime,
    a.add_usr_id AS UserOperatorId,
    a.exp_arv_dte AS RequestedCompletionDate,
    NULL AS PriorityLevel,
    CAST(ad.qtyord AS DECIMAL(18, 4)) AS PlannedQuantity,
    NULL AS ActualQuantity,
    NULL AS StorageLocation,
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM asnhdr a
JOIN asndtl ad ON a.asnhdr_id = ad.asnhdr_id
WHERE a.ordnum IN (SELECT WarehouseOrder FROM BaseOrders)

UNION ALL

-- 3. Goods Arrived at Dock
SELECT
    t.ordnum AS WarehouseOrder,
    'Goods Arrived at Dock' AS ActivityName,
    t.checkin_dte AS EventStartTime,
    t.checkin_dte AS EventEndTime,
    t.usr_id AS UserOperatorId,
    NULL AS RequestedCompletionDate,
    NULL AS PriorityLevel,
    NULL AS PlannedQuantity,
    NULL AS ActualQuantity,
    t.dock_loc AS StorageLocation,
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM trk_log t -- Note: Yard management table may vary
WHERE t.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND t.checkin_dte IS NOT NULL

UNION ALL

-- 4. Goods Received and Counted
SELECT 
    i.ordnum AS WarehouseOrder,
    'Goods Received and Counted' AS ActivityName,
    i.moddte AS EventStartTime,
    i.moddte AS EventEndTime,
    i.mod_usr_id AS UserOperatorId,
    NULL AS RequestedCompletionDate,
    NULL AS PriorityLevel,
    CAST(i.qtyexp AS DECIMAL(18, 4)) AS PlannedQuantity,
    CAST(i.qtyrcv AS DECIMAL(18, 4)) AS ActualQuantity,
    i.inv_loc AS StorageLocation,
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM rcvlin i -- Receiving Line table
WHERE i.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND i.qtyrcv > 0

UNION ALL

-- 5. Quality Inspection Performed
SELECT
    q.ordnum AS WarehouseOrder,
    'Quality Inspection Performed' AS ActivityName,
    q.insp_dte AS EventStartTime,
    q.insp_dte AS EventEndTime,
    q.usr_id AS UserOperatorId,
    NULL AS RequestedCompletionDate,
    NULL AS PriorityLevel,
    CAST(q.insp_qty AS DECIMAL(18, 4)) AS PlannedQuantity,
    CAST(q.act_qty AS DECIMAL(18, 4)) AS ActualQuantity,
    q.stoloc AS StorageLocation,
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM qc_log q -- Quality Control log table may vary
WHERE q.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND q.status = 'COMPLETED'

UNION ALL

-- 6. Putaway Task Created
SELECT 
    w.ordnum AS WarehouseOrder,
    'Putaway Task Created' AS ActivityName,
    w.adddte AS EventStartTime,
    NULL AS EventEndTime,
    w.add_usr_id AS UserOperatorId,
    NULL AS RequestedCompletionDate,
    w.wrkprt AS PriorityLevel,
    CAST(w.untqty AS DECIMAL(18, 4)) AS PlannedQuantity,
    NULL AS ActualQuantity,
    w.frmloc AS StorageLocation, -- From receiving dock
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM wrkque w
WHERE w.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND w.wrktyp = 'PUTAWAY'

UNION ALL

-- 7. Goods Put Away in Storage
SELECT
    m.ordnum AS WarehouseOrder,
    'Goods Put Away in Storage' AS ActivityName,
    m.adddte AS EventStartTime,
    m.adddte AS EventEndTime,
    m.usr_id AS UserOperatorId,
    NULL AS RequestedCompletionDate,
    NULL AS PriorityLevel,
    NULL AS PlannedQuantity,
    CAST(m.movqty AS DECIMAL(18, 4)) AS ActualQuantity,
    m.toloc AS StorageLocation, -- Destination storage location
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM invmov m -- Inventory Movement table
WHERE m.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND m.trntyp = 'PUTFIN' -- Putaway Finish transaction type

UNION ALL

-- 8. Picking Task Created
SELECT 
    w.ordnum AS WarehouseOrder,
    'Picking Task Created' AS ActivityName,
    w.adddte AS EventStartTime,
    NULL AS EventEndTime,
    w.add_usr_id AS UserOperatorId,
    o.req_shp_dte AS RequestedCompletionDate,
    w.wrkprt AS PriorityLevel,
    CAST(w.untqty AS DECIMAL(18, 4)) AS PlannedQuantity,
    NULL AS ActualQuantity,
    w.frmloc AS StorageLocation,
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM wrkque w
JOIN ord o ON w.ordnum = o.ordnum
WHERE w.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND w.wrktyp = 'PICK'

UNION ALL

-- 9. Goods Picked from Storage
SELECT
    p.ordnum AS WarehouseOrder,
    'Goods Picked from Storage' AS ActivityName,
    p.moddte AS EventStartTime,
    p.moddte AS EventEndTime,
    p.mod_usr_id AS UserOperatorId,
    NULL AS RequestedCompletionDate,
    NULL AS PriorityLevel,
    CAST(p.pckqty AS DECIMAL(18, 4)) AS PlannedQuantity, -- Often planned and actual are the same here
    CAST(p.pckqty AS DECIMAL(18, 4)) AS ActualQuantity,
    p.pckloc AS StorageLocation,
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM pckwrk p
WHERE p.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND p.wrksts = 'C' -- Status for Completed Pick

UNION ALL

-- 10. Packing Initiated
SELECT
    s.ordnum AS WarehouseOrder,
    'Packing Initiated' AS ActivityName,
    s.moddte AS EventStartTime,
    NULL AS EventEndTime,
    s.mod_usr_id AS UserOperatorId,
    NULL AS RequestedCompletionDate,
    NULL AS PriorityLevel,
    NULL AS PlannedQuantity,
    NULL AS ActualQuantity,
    s.pckstn AS StorageLocation, -- Packing Station
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM ord_status_log s -- Status log table may vary
WHERE s.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND s.ordsta = 'PCK_START'

UNION ALL

-- 11. Goods Packed
SELECT
    c.ordnum AS WarehouseOrder,
    'Goods Packed' AS ActivityName,
    c.moddte AS EventStartTime,
    c.moddte AS EventEndTime,
    c.mod_usr_id AS UserOperatorId,
    NULL AS RequestedCompletionDate,
    NULL AS PriorityLevel,
    NULL AS PlannedQuantity,
    CAST(c.actqty AS DECIMAL(18, 4)) AS ActualQuantity,
    c.pckstn AS StorageLocation, -- Packing Station
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM ship_cntr c -- Shipping Container table
WHERE c.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND c.cntr_sts = 'PACKED'

UNION ALL

-- 12. Staging for Shipment
SELECT
    m.ordnum AS WarehouseOrder,
    'Staging for Shipment' AS ActivityName,
    m.adddte AS EventStartTime,
    m.adddte AS EventEndTime,
    m.usr_id AS UserOperatorId,
    NULL AS RequestedCompletionDate,
    NULL AS PriorityLevel,
    NULL AS PlannedQuantity,
    CAST(m.movqty AS DECIMAL(18, 4)) AS ActualQuantity,
    m.toloc AS StorageLocation, -- Staging location
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM invmov m
WHERE m.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND m.trntyp = 'STAGEMOV' -- Staging Movement transaction type

UNION ALL

-- 13. Shipment Dispatched
SELECT
    l.ordnum AS WarehouseOrder,
    'Shipment Dispatched' AS ActivityName,
    l.shp_dte AS EventStartTime,
    l.shp_dte AS EventEndTime,
    l.mod_usr_id AS UserOperatorId,
    NULL AS RequestedCompletionDate,
    NULL AS PriorityLevel,
    NULL AS PlannedQuantity,
    CAST(sl.shpqty AS DECIMAL(18, 4)) AS ActualQuantity,
    l.wh_id AS StorageLocation,
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM lodhdr l
JOIN ship_line sl ON l.lodnum = sl.lodnum
WHERE l.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND l.lodsts = 'S' -- Shipped status

UNION ALL

-- 14. Warehouse Order Completed
SELECT 
    o.ordnum AS WarehouseOrder,
    'Warehouse Order Completed' AS ActivityName,
    o.moddte AS EventStartTime,
    o.moddte AS EventEndTime,
    o.mod_usr_id AS UserOperatorId,
    o.req_shp_dte AS RequestedCompletionDate,
    o.prirty AS PriorityLevel,
    CAST(o.ordqty AS DECIMAL(18, 4)) AS PlannedQuantity,
    CAST(o.shpqty AS DECIMAL(18, 4)) AS ActualQuantity,
    NULL AS StorageLocation,
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM ord o
WHERE o.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND o.ordsta = 'C' -- Status for Completed

UNION ALL

-- 15. Warehouse Order Canceled
SELECT 
    o.ordnum AS WarehouseOrder,
    'Warehouse Order Canceled' AS ActivityName,
    o.moddte AS EventStartTime,
    o.moddte AS EventEndTime,
    o.mod_usr_id AS UserOperatorId,
    o.req_shp_dte AS RequestedCompletionDate,
    o.prirty AS PriorityLevel,
    CAST(o.ordqty AS DECIMAL(18, 4)) AS PlannedQuantity,
    NULL AS ActualQuantity,
    NULL AS StorageLocation,
    'Blue Yonder WMS' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM ord o
WHERE o.ordnum IN (SELECT WarehouseOrder FROM BaseOrders) AND o.ordsta = 'X'; -- Status for Canceled