Your Warehouse Management Data Template
Your Warehouse Management Data Template
- Recommended attributes to collect for comprehensive analysis
- Key activities to track across your material flow
- Practical guidance for data extraction from Blue Yonder WMS
Warehouse Management Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name ActivityName | The name of the specific warehouse task or event that occurred, such as 'Goods Picked' or 'Shipment Dispatched'. | ||
| Description This attribute describes the specific step or task performed within the warehouse management process. Each event in the process log is associated with an Activity Name, forming the sequence of steps that make up the process flow. In analysis, the Activity Name is fundamental for discovering the process map, analyzing transitions between steps, and identifying bottlenecks or deviations from the standard procedure. It is used in virtually all process mining analyses, from conformance checking to performance monitoring. Why it matters This attribute is critical for building the process map, as it defines the individual steps and allows for the visualization and analysis of the process flow. Where to get This information is typically found in warehouse task or event log tables, often derived from a task type or status code. Examples Picking Task CreatedGoods Picked from StorageShipment Dispatched | |||
| Event Start Time EventStartTime | The timestamp indicating when a specific warehouse activity or event began. | ||
| Description This attribute records the date and time that a warehouse task or event was initiated. It provides the chronological context for all activities within a case. This timestamp is essential for all time-based process mining analyses. It is used to order events, calculate cycle times between activities, measure the duration of the entire process, and identify waiting times or delays. It forms the backbone of performance analysis and is required to animate the process map. Why it matters The start timestamp is mandatory for ordering events chronologically and calculating all performance metrics, such as cycle times and wait times. Where to get Located in event log or task tables, corresponding to the creation or start time of a recorded action. Examples 2023-10-26T08:30:00Z2023-10-26T09:15:10Z2023-10-26T11:05:45Z | |||
| Warehouse Order WarehouseOrder | The unique identifier for a warehouse order, which serves as the primary case for tracking all related logistical activities from creation to completion. | ||
| Description The Warehouse Order is the central identifier that groups all events and tasks related to a specific logistical request, such as an inbound receipt or an outbound shipment. It represents a complete unit of work within the warehouse. In process mining, this attribute is used to define the case, allowing for the end-to-end analysis of the entire order lifecycle. By tracing all activities associated with a single Warehouse Order, analysts can measure total fulfillment times, identify common process variations, and understand the complete journey of an order through the facility. Why it matters This is the essential case identifier that connects all related warehouse activities, enabling a complete, end-to-end analysis of the order fulfillment or goods receipt process. Where to get This is typically the primary key in the warehouse order header table. Consult Blue Yonder WMS documentation for tables related to order management. Examples WO-0012845WO-0012991WO-0013057 | |||
| Last Data Update LastDataUpdate | Timestamp indicating when the data for this record was last refreshed from the source system. | ||
| Description This attribute records the date and time when the dataset was last extracted or updated from Blue Yonder WMS. It provides metadata about the freshness of the data being analyzed. This timestamp is important for data governance and for users to understand the timeliness of their analysis. It ensures that stakeholders are aware of the data's currency and can trust that they are looking at a recent and relevant snapshot of the process. Why it matters This timestamp informs users about the freshness of the data, ensuring they understand the time period covered by the analysis. Where to get This is a metadata field typically generated and added during the data extraction (ETL) process. Examples 2024-01-15T04:00:00Z2024-01-16T04:00:00Z | |||
| Source System SourceSystem | The system from which the data was extracted, in this case, Blue Yonder WMS. | ||
| Description This attribute identifies the originating system for the event data. In a modern IT landscape, data for a single end-to-end process might come from multiple systems, such as an ERP, WMS, and TMS. Specifying the source system is crucial for data governance, troubleshooting, and understanding the context of the data. It helps in tracing data quality issues back to their origin and is essential when merging data from multiple sources to create a unified process view. Why it matters It provides essential data lineage, helping to trace data back to its origin for validation and in scenarios where data is merged from multiple systems. Where to get This is typically a static value added during the data extraction process to label the origin of the dataset. Examples BlueYonderWMS_USBlueYonderWMS_EU | |||
| Actual Quantity ActualQuantity | The actual quantity of items handled during a task, such as the quantity physically counted or picked. | ||
| Description Actual Quantity is the number of units physically processed by a warehouse operator during a task. This could be the count of items received from a supplier, the number of units picked from a storage bin, or the quantity packed into a shipping container. When compared with the Planned Quantity, this attribute reveals process exceptions and errors. It is the core metric for calculating discrepancy rates, which are key indicators of operational quality. This data is vital for identifying issues with supplier shipments, picking errors, or inventory inaccuracies. Why it matters Comparing this with the planned quantity is essential for identifying process errors and calculating key quality KPIs like discrepancy rates. Where to get Found in task confirmation or transaction log tables, where operators record the executed quantity. Examples 1004924 | |||
| Event End Time EventEndTime | The timestamp indicating when a specific warehouse activity or event was completed. | ||
| Description This attribute records the date and time that a warehouse task or event was finished. When available, it provides a precise measure of the processing time for each activity. Having both a start and end time allows for a more detailed performance analysis. It enables the separation of waiting time (time between activities) from processing time (duration of the activity itself). This is crucial for pinpointing whether delays are caused by idle periods or by tasks taking too long to complete. Why it matters It enables the precise calculation of activity processing time, distinguishing it from waiting time, which is key for targeted performance improvement. Where to get Located in event log or task tables, corresponding to the completion or closing time of a recorded action. Examples 2023-10-26T08:35:12Z2023-10-26T09:20:05Z2023-10-26T11:06:00Z | |||
| Planned Quantity PlannedQuantity | The expected quantity of items for a given task, such as the quantity to be picked or received. | ||
| Description Planned Quantity represents the target number of units specified by the warehouse order for a particular task. For an inbound delivery, it is the quantity expected from the supplier. For a picking task, it is the quantity requested by the customer order. This attribute is critical for accuracy analysis. By comparing the Planned Quantity to the Actual Quantity, it becomes possible to identify discrepancies in receiving, picking, or inventory counts. It directly supports KPIs like Picking Quantity Discrepancy Rate and is essential for the Quantity Accuracy Audit dashboard. Why it matters It serves as the baseline for measuring accuracy, allowing for the detection of quantity discrepancies in receiving and picking activities. Where to get Found in the details or line item tables associated with warehouse orders or specific tasks. Examples 1005024 | |||
| Priority Level PriorityLevel | The priority of the warehouse order, such as 'High', 'Standard', or 'Low'. | ||
| Description The Priority Level indicates the urgency of a warehouse order. High-priority orders, such as expedited shipments, are expected to be processed faster than standard orders. This attribute is used by the WMS to sequence tasks and allocate resources. In process mining, this attribute is crucial for analyzing whether prioritization strategies are effective. The High Priority Order Fulfillment dashboard relies on this field to filter for urgent orders and compare their cycle times against standard ones. It helps verify if high-priority orders truly move faster through the process or if they get stuck in the same bottlenecks. Why it matters This enables analysis of whether high-priority orders are processed faster than standard ones, validating the effectiveness of prioritization rules. Where to get This information is usually stored in the warehouse order header table. Examples HighStandardLow | |||
| Requested Completion Date RequestedCompletionDate | The date and time by which the warehouse order is scheduled or requested to be completed and dispatched. | ||
| Description The Requested Completion Date represents the service level agreement (SLA) or target for fulfilling an outbound warehouse order. It is the deadline by which the goods should be picked, packed, and ready for shipment. This date is the benchmark against which actual performance is measured. It is used to calculate the On-Time Shipment Rate KPI by comparing it to the actual dispatch timestamp. Analyzing orders based on this attribute helps identify which orders are at risk of being late and diagnose the root causes of SLA breaches. Why it matters This attribute is the benchmark for measuring on-time performance and is critical for calculating the On-Time Shipment Rate KPI. Where to get Typically stored in the warehouse order header table, often inherited from the source sales order or delivery request. Examples 2023-10-27T17:00:00Z2023-10-28T12:00:00Z2023-11-01T17:00:00Z | |||
| Storage Location StorageLocation | The specific location within the warehouse, such as a bin or aisle, where goods are stored or picked from. | ||
| Description This attribute identifies the physical location within the warehouse associated with a task. For a putaway activity, it is the destination bin. For a picking activity, it is the source bin. This could be represented as a composite code including aisle, rack, shelf, and bin number. Analyzing data by Storage Location helps in understanding warehouse layout efficiency, slotting strategies, and resource movement. It is used to identify high-traffic areas, underutilized zones, and potential bottlenecks in material flow. This attribute is fundamental for the Storage Location Utilization Trends dashboard. Why it matters It provides crucial context for analyzing warehouse layout, slotting strategy effectiveness, and identifying movement bottlenecks. Where to get Available in tables related to inventory, warehouse tasks (picking, putaway), and bin master data. Examples A1-R03-S02-B01B5-R10-S04-B05C2-R01-S01-B02 | |||
| User/Operator ID UserOperatorId | The identifier for the warehouse employee or operator who performed the activity. | ||
| Description This attribute captures the unique ID of the person responsible for executing a given warehouse task, such as picking, packing, or putaway. It links process activities to human resources. Analyzing activities by User/Operator ID is essential for understanding resource utilization, workload distribution, and individual performance. It helps answer questions like which operators are most efficient, who may require additional training, or how tasks are balanced across a team. This is a primary dimension for the Warehouse Resource Utilization dashboard. Why it matters This attribute connects process steps to the individuals who performed them, enabling analysis of resource performance, workload, and training needs. Where to get Commonly found in task or transaction tables, linked to the user logged into the system or handheld device during the operation. Examples JSMITHBWILLISAMILLER | |||
| Activity Processing Time ActivityProcessingTime | The duration, in seconds or minutes, required to complete a single activity. | ||
| Description This is a calculated metric that measures the time elapsed between the start and end of an activity. It represents the actual time spent working on a task, as opposed to the time spent waiting for the task to begin. This metric is fundamental for performance analysis, helping to identify which specific activities are the most time-consuming. It allows analysts to distinguish between process steps that are inherently slow and delays that occur between steps, enabling more targeted improvement efforts. This is a key metric for dashboards focused on throughput and bottlenecks. Why it matters It isolates the time spent actively working on a task, helping to pinpoint which specific activities are taking the longest to complete. Where to get Calculated by subtracting EventStartTime from EventEndTime for each activity record. Examples 31229515 | |||
| Equipment ID EquipmentId | Identifier for the material handling equipment used, such as a specific forklift or conveyor belt. | ||
| Description The Equipment ID specifies which piece of machinery or equipment was used to perform a warehouse task. This could include forklifts, pallet jacks, automated guided vehicles (AGVs), or specific packing stations. This attribute allows for analysis of equipment utilization, performance, and maintenance needs. By tracking activities per piece of equipment, managers can identify over or underutilized assets, compare the efficiency of different machine types, and gather data to inform maintenance schedules. It is a key dimension for the Warehouse Resource Utilization dashboard. Why it matters It allows for analysis of equipment utilization and performance, helping to optimize asset allocation and maintenance schedules. Where to get May be recorded in task execution logs, especially in environments where operators log into equipment. Examples FORKLIFT-07AGV-03PACKSTATION-12 | |||
| Is On-Time Shipment IsOnTimeShipment | A boolean flag that is true if the shipment was dispatched on or before the requested completion date. | ||
| Description This calculated attribute provides a simple true or false indicator of whether an order met its shipping SLA. It is derived by comparing the timestamp of the 'Shipment Dispatched' activity with the 'Requested Completion Date' for the order. This flag simplifies analysis and visualization for on-time performance. It allows for easy filtering and aggregation to calculate the On-Time Shipment Rate KPI and power the corresponding dashboard. It also enables root cause analysis to identify common characteristics of late shipments. Why it matters This boolean flag simplifies the calculation of the On-Time Shipment Rate KPI and allows for easy filtering to analyze the characteristics of late orders. Where to get Calculated by comparing the EventStartTime of the 'Shipment Dispatched' activity to the RequestedCompletionDate attribute. Examples truefalsetrue | |||
| Is Quantity Mismatch IsQuantityMismatch | A boolean flag indicating if the actual quantity handled differs from the planned quantity for a task. | ||
| Description This calculated attribute is a simple flag that indicates a quantity discrepancy for a given task, such as picking or receiving. It is set to true when the 'Actual Quantity' does not equal the 'Planned Quantity'. This flag is used to easily identify and count errors within the process. It simplifies the calculation of KPIs like the Picking Quantity Discrepancy Rate and the Inbound Quantity Discrepancy Rate. It also facilitates root cause analysis by allowing analysts to filter for all mismatch events and look for patterns related to products, operators, or locations. Why it matters It flags events with quantity errors, simplifying the calculation of discrepancy rates and enabling targeted analysis of inaccurate tasks. Where to get Calculated by comparing the PlannedQuantity and ActualQuantity fields for each relevant activity. Examples falsetruefalse | |||
| Product SKU ProductSku | The Stock Keeping Unit (SKU) or material number of the item being handled. | ||
| Description This attribute identifies the specific product involved in a warehouse task. It provides granular detail on the materials being moved, stored, picked, and packed. Analyzing the process by Product SKU can reveal patterns related to specific items. For example, some products may be prone to picking errors, have longer putaway times due to special handling requirements, or be stored in inefficient locations. This allows for product-specific process optimization and improvement of slotting strategies. Why it matters It enables product-level analysis, helping to identify items that cause process delays, errors, or require special handling. Where to get This information is located at the line-item level of the warehouse order or task tables. Examples PN-A5540-BSKU-300-RED-LGHW-88201 | |||
| Shipment ID ShipmentId | The unique identifier for the outbound shipment that a warehouse order belongs to. | ||
| Description The Shipment ID is a higher-level identifier that can group multiple warehouse orders together if they are being dispatched in the same truck or container. For a single order, it may be the same as the warehouse order or delivery number. Analyzing by Shipment ID provides a view of the dispatch process. It can help understand how orders are consolidated, measure the time from staging to final dispatch for a full truckload, and analyze the efficiency of the shipping department. It connects warehouse activities to the final transportation leg of the supply chain. Why it matters It groups warehouse orders that are dispatched together, enabling analysis of the shipment consolidation and dispatch processes. Where to get Found in shipment or transportation-related tables, linked back to the warehouse orders. Examples SHP-45000123SHP-45000124SHP-45000125 | |||
| Task Status TaskStatus | The final status of a given task, such as 'Completed', 'Canceled', or 'Failed'. | ||
| Description This attribute describes the outcome of a specific warehouse task. While many tasks complete successfully, some may be canceled by a supervisor or fail due to system or operational issues. This provides more context than just the activity name. Analyzing by Task Status is useful for understanding exceptions and process failures. A high rate of canceled or failed tasks can indicate underlying problems with inventory accuracy, system configuration, or operator training. It helps pinpoint activities that are prone to failure and require further investigation. Why it matters It provides the outcome of an activity, enabling analysis of exceptions like canceled or failed tasks that can indicate deeper operational issues. Where to get Typically found in the task table, indicating the final state of the task record. Examples CompletedCanceledOn Hold | |||
| Warehouse ID WarehouseId | Identifier for the specific warehouse or distribution center where the activity took place. | ||
| Description The Warehouse ID uniquely identifies the facility in which the process occurs. This is essential for organizations that operate multiple distribution centers. This attribute allows for benchmarking and comparative analysis across different sites. By filtering or splitting the data by Warehouse ID, companies can compare performance, identify best practices at top-performing sites, and understand why certain facilities lag behind. It provides a crucial dimension for multi-site operational analysis. Why it matters For multi-site organizations, this attribute is essential for benchmarking performance and comparing processes across different locations. Where to get This is often a top-level organizational field available in almost all transaction tables, or it can be inferred from the system instance. Examples WHC-01DC-EAST-03FAC-WEST | |||
| Warehouse Order Type WarehouseOrderType | Categorizes the warehouse order, for example, as an inbound receipt, outbound shipment, or internal transfer. | ||
| Description This attribute classifies the overall purpose of the warehouse order. Common types include inbound deliveries from suppliers, outbound shipments to customers, returns processing, or internal stock movements between warehouse locations. Segmenting the process by Warehouse Order Type is a fundamental first step in any analysis. Inbound and outbound processes are often significantly different, with different steps, resources, and performance targets. This attribute allows for filtering the data to analyze a specific process, such as Goods Receipt or Order Fulfillment, in isolation. Why it matters It allows for the separation and comparative analysis of different processes, such as inbound vs. outbound, which have distinct flows and goals. Where to get Found in the warehouse order header table, typically as a document type or order category field. Examples Inbound DeliveryOutbound ShipmentInternal Transfer | |||
Warehouse Management Activities
| Activity | Description | ||
|---|---|---|---|
| Goods Put Away in Storage | This event confirms that goods have been successfully moved and scanned into their designated storage bin. It's captured when an operator confirms the completion of the putaway task, typically using a handheld RF device. | ||
| Why it matters This marks the end of the inbound process, making the inventory available for fulfillment. Analyzing the time from receipt to this point is crucial for the 'Goods Receipt to Putaway Cycle Time' dashboard. Where to get Recorded as a timestamped transaction when the putaway task status is updated to 'Completed' or 'Confirmed'. This data is found in the warehouse task or transfer order tables. Capture Confirmation timestamp of the putaway warehouse task. Event type explicit | |||
| Goods Received and Counted | This event signifies that goods have been unloaded, scanned, and their quantities verified against the delivery documents. It is usually captured when a receiving clerk confirms the receipt quantities in the system for each item on the inbound order. | ||
| Why it matters This is a critical milestone that makes inventory officially available in the system, though not yet ready for fulfillment. The duration and accuracy of this step directly impact inventory visibility and the start of the putaway process. Where to get This is an explicit transaction recorded in inventory or receiving logs. Look for transactions related to goods receipt posting or status changes on the inbound delivery line items to 'Received'. Capture Timestamp of the transaction confirming receipt of goods. Event type explicit | |||
| Picking Task Created | This event signifies the creation of a task for an operator to retrieve goods from a storage location to fulfill an outbound order. It is an explicit event generated by the WMS when an outbound order is released for picking. | ||
| Why it matters This is the start of the physical outbound process. Analyzing the time from order creation to picking task creation reveals delays in order processing and allocation. Where to get Logged in the task management or warehouse control tables. It corresponds to the creation timestamp of picking tasks associated with the warehouse order. Capture Creation timestamp of the system-generated picking task. Event type explicit | |||
| Shipment Dispatched | This event signifies that the packed goods have been loaded onto the carrier's truck, and the truck has departed the warehouse. This is typically recorded when a 'Goods Issue' is posted in the system, finalizing the shipment. | ||
| Why it matters This is a critical milestone that marks the end of warehouse responsibility for the order. It is the final data point for measuring on-time shipment performance and the end-to-end fulfillment lead time. Where to get This is a major financial and logistical transaction, often called 'Post Goods Issue' (PGI). The timestamp for this transaction serves as the dispatch time and is usually stored in the outbound delivery or shipment document tables. Capture Timestamp of the Post Goods Issue (PGI) transaction. Event type explicit | |||
| Warehouse Order Completed | This is the final status of the warehouse order, indicating that all associated activities, including shipment, have been finished and the order is closed. It is captured when the order's lifecycle status is updated to 'Completed' or 'Closed'. | ||
| Why it matters This activity marks the definitive end of the process case. It ensures that the process analysis captures the complete lifecycle of every order from start to finish. Where to get This can be inferred from a status change in the warehouse order header table. Look for a final status like 'Completed', 'Closed', or 'Invoiced', along with the timestamp of that status change. Capture Inferred from the timestamp of the final status change on the order header. Event type inferred | |||
| Warehouse Order Created | This event marks the creation of a warehouse order, which is the central document for managing inbound, outbound, or internal warehouse tasks. It is typically captured as an explicit transaction when a new order is entered into the Blue Yonder WMS, either manually or through an integration. | ||
| Why it matters This is the definitive start of the process. Analyzing the time from this event to completion provides the total order fulfillment lead time, which is essential for measuring overall efficiency and adherence to service level agreements. Where to get This event is likely recorded in an order header table, captured by the creation timestamp of the warehouse order record. Look for tables related to Capture From the creation timestamp of the warehouse order record. Event type explicit | |||
| Goods Arrived at Dock | This activity marks the physical arrival of a truck or carrier at the warehouse receiving dock, before unloading begins. This event is often logged explicitly by a yard management module or when a gate agent checks in the delivery. | ||
| Why it matters Tracking arrival time helps measure carrier on-time performance and identifies delays between carrier arrival and the start of the receiving process. It highlights potential bottlenecks in yard management or at the receiving doors. Where to get Typically recorded in a yard management or gate control module within Blue Yonder WMS. It could also be a manual timestamp entry by a receiving clerk when the truck arrives. Capture Timestamp of carrier check-in transaction. Event type explicit | |||
| Goods Packed | This event confirms that all items for a shipment have been packed into shipping containers, and labels have been generated. It is recorded when the packer confirms the completion of the packing process for the order in the system. | ||
| Why it matters This marks the completion of value-adding activities within the warehouse walls. The time from this point to shipment dispatch represents staging and loading time, a key area for potential delays. Where to get This is an explicit transaction logged when the packing process is finalized. Look for a status change on the outbound delivery to 'Packed' or a completion timestamp from the packing station transaction. Capture Timestamp of the 'Confirm Packing' or 'Close Container' transaction. Event type explicit | |||
| Goods Picked from Storage | Represents the completion of the picking task, where an operator has retrieved the items and confirmed the action in the system. The event is captured when the operator scans the items and confirms the pick on their device. | ||
| Why it matters This milestone concludes the picking stage. The accuracy and duration of this activity are critical for overall order fulfillment efficiency and form the basis for the 'Picking Accuracy' analysis. Where to get Captured from the confirmation timestamp when the picking task's status is changed to 'Completed'. This is found in the warehouse task tables, often linked to the specific operator and equipment. Capture Confirmation timestamp of the picking warehouse task. Event type explicit | |||
| Inbound Delivery Notified | Represents the receipt of an Advanced Shipping Notification (ASN) from a supplier, indicating that goods are en route to the warehouse. This is an explicit event captured when an ASN is received and processed by the system, often via EDI or a portal. | ||
| Why it matters This activity is the trigger for inbound planning and resource allocation. The time between this notification and the physical receipt of goods is a key KPI for measuring supplier performance and inbound pipeline visibility. Where to get Captured from ASN receipt logs or the creation timestamp of the inbound delivery document within Blue Yonder WMS. Check tables related to ASNs or inbound shipment notifications. Capture Timestamp from an ASN or inbound delivery document creation. Event type explicit | |||
| Packing Initiated | This activity marks the start of the packing process at a packing station. It is typically logged when an operator scans the picked items or the order tote at the packing station to begin preparing the shipment. | ||
| Why it matters This event signals the handoff from picking to packing. It helps isolate the packing stage of the fulfillment process to identify specific bottlenecks within the packing area. Where to get This may be an explicit transaction log from a packing station's user interface. Alternatively, it could be inferred from the first timestamped activity associated with a packing work center for that order. Capture Timestamp of the 'Start Packing' transaction at a packing station. Event type explicit | |||
| Putaway Task Created | This activity marks the system's creation of a task to move received goods from the receiving dock to a final storage location. It's an explicit system event generated by the WMS logic to direct a warehouse operator. | ||
| Why it matters This is the start of the putaway process. Delays between goods receipt and putaway task creation can indicate system configuration or performance issues, leaving goods sitting in the receiving area. Where to get Generated and logged in the task management or warehouse control tables. Look for the creation timestamp of putaway tasks or transfer orders linked to the inbound delivery. Capture Creation timestamp of the system-generated putaway task. Event type explicit | |||
| Quality Inspection Performed | Represents a quality check performed on received goods. This may be a standard step for certain materials or a triggered event due to exceptions, and it is captured when a quality inspector records their findings in the system. | ||
| Why it matters Quality inspections can be a significant source of delay in the inbound process. Analyzing their frequency and duration helps identify quality issues with suppliers and bottlenecks in the inspection workflow. Where to get Recorded in Quality Management (QM) modules or logs associated with the inbound delivery. Look for specific transaction codes for quality inspection results or status changes on inventory to 'Quality Hold'. Capture Timestamp of quality inspection completion or status update. Event type explicit | |||
| Staging for Shipment | Represents the movement of packed containers from the packing area to a designated shipment staging lane to await carrier pickup. The event is logged when an operator confirms the movement of the handling unit to the staging area. | ||
| Why it matters This activity helps analyze dwell time, the period packed orders wait before being loaded. Long staging times can indicate poor coordination with carriers or inefficient use of staging space. Where to get This event can be inferred from a location change of the handling unit or shipping container to a staging location. It can also be an explicit 'Move to Stage' task confirmation. Capture Inferred from inventory movement logs showing transfer to a staging bin. Event type inferred | |||
| Warehouse Order Canceled | Represents the cancellation of a warehouse order before it was fully processed or shipped. This event is captured when a user executes a cancellation transaction, updating the order's status to 'Canceled'. | ||
| Why it matters Analyzing cancellations helps identify reasons for process failures, such as stock unavailability or customer changes. It's a critical terminating event for understanding process deviations and fallout. Where to get This is typically an inferred event based on the final status of the warehouse order. The timestamp of the status change to 'Canceled' or 'Deleted' would be used. Capture Inferred from the timestamp of a status change to 'Canceled'. Event type inferred | |||
Extraction Guides
Steps
- Prerequisites and Access: Ensure you have a user account for Blue Yonder WMS with the necessary permissions to execute MOCA commands and access the required tables, such as ord_hdr, pckwrk_dtl, and invmov. You will need access to a MOCA client, like the MOCA Console or a command line interface.
- Review and Customize the MOCA Script: Copy the provided MOCA script. Carefully review the table and column names to ensure they match your specific Blue Yonder WMS implementation. Pay close attention to placeholders like
@[where_clause_dates]and@[where_clause_warehouse], which must be replaced with actual values. - Define Extraction Parameters: Replace the placeholder variables in the script. For
@[where_clause_dates], define a specific date range, for example,where adddte between 'YYYY-MM-DD' and 'YYYY-MM-DD'. For@[where_clause_warehouse], specify the warehouse IDs you want to extract, for example,where wh_id = '[Your Warehouse ID]'. - Connect to the MOCA Server: Launch your MOCA client (e.g., MOCA Console) and establish a connection to the correct Blue Yonder WMS environment.
- Execute the MOCA Script: Paste the customized script into the MOCA Console. Execute the command. The script will run on the server and collect data for all specified activities.
- Monitor Execution: For large data sets, the query may take a significant amount of time to run. Monitor the console for any error messages or performance warnings. If the query times out, consider running it for smaller date ranges.
- Export the Results to a File: Once the script executes successfully, the results will be displayed in the console. Use the client's export functionality to save the output as a CSV file. A common method from the command line is to pipe the output directly to a file, for example:
mocarun -S "[Your MOCA Script]" > event_log.csv. - Format the CSV for ProcessMind: Open the exported CSV file. Verify that the column headers match the attributes specified in the query (
WarehouseOrder,ActivityName,EventStartTime, etc.). Ensure the file is saved with UTF-8 encoding to prevent character issues during upload. - Review and Upload: Perform a final check on the file's contents, looking for any obvious errors or inconsistencies. Once satisfied, upload the CSV file to ProcessMind for analysis.
Configuration
- Date Range: It is recommended to extract data for a period of 3 to 6 months to ensure a representative sample of process variations. The date filter placeholder
@[where_clause_dates]should be applied to the primary timestamp column in each SELECT statement, such asadddteormoddte. - Warehouse and Client Filters: Always use filters to limit the scope of the extraction. The
@[where_clause_warehouse]placeholder should be used to filter by specific warehouse IDs (wh_id) and, if applicable, client IDs (client_id). This is critical for performance and data relevance. - Order Type Filters: To focus the analysis, consider filtering on specific warehouse order types (
ordtyp). For example, you may want to analyze only outbound customer orders or inbound purchase orders. This can be added to the WHERE clause in the relevant sections of the script. - Performance Considerations: The extraction script joins and unions multiple large tables. To avoid impacting system performance, schedule the extraction to run during off-peak hours. Extracting data in smaller, incremental batches (e.g., one month at a time) is a safe strategy for very large environments.
- Prerequisites: The user executing the script must have read access permissions for all tables referenced in the query, including
ord_hdr,ord_dtl,invmov,pckwrk_dtl,asnhdr, andtrn_log. The user must also be authorized to execute MOCA commands.
a Sample Query config
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]'
]Steps
- Establish Database Connection: Obtain read-only credentials and connection details (server address, database name, port) for the Blue Yonder WMS underlying database, which is typically Oracle or SQL Server. Use a standard SQL client like DBeaver, Oracle SQL Developer, or SQL Server Management Studio to connect.
- Identify Core WMS Tables: The provided query relies on standard Blue Yonder WMS tables such as
ord(orders),pckwrk(picking work),wrkque(work queue),invmov(inventory movements), andlodhdr(load header). Verify these table names and column structures against your system's data dictionary, as customizations may exist. - Review and Parameterize the SQL Query: Copy the provided SQL script into your SQL client. Locate the placeholder variables within the
BaseOrdersCommon Table Expression (CTE) at the top of the script. - Set the Date Range: Modify the
adddte >= 'YYYY-MM-DD'andadddte < 'YYYY-MM-DD'clauses to define the time window for the data extraction. A period of 3 to 6 months is recommended for initial analysis. - Apply System-Specific Filters: Adjust the
wh_id = '[Your_Warehouse_ID]'filter to limit the extraction to a specific warehouse. Add or modify other filters, such asclient_idfor multi-client environments, as needed. - Execute the Extraction Script: Run the complete SQL script. The query is designed to consolidate events from multiple tables into a single, unified event log format. Execution time will vary based on the date range and data volume.
- Validate the Initial Results: After the query finishes, perform a quick review of the output. Check that the
WarehouseOrder,ActivityName, andEventStartTimecolumns are populated as expected. The number of rows should be significantly larger than the number of unique warehouse orders. - Export the Event Log: Export the query results to a CSV file. Ensure the file encoding is set to UTF-8 to prevent character encoding issues during upload.
- Prepare for Upload: Confirm that the column headers in the exported CSV file match the required attributes, for example,
WarehouseOrder,ActivityName,EventStartTime. The file is now ready to be uploaded into the process mining software.
Configuration
- Prerequisites: You must have read-only SQL access to the Blue Yonder WMS database. Familiarity with your organization's specific WMS configuration and data model is highly beneficial.
- Database Connection: This method requires direct database connectivity. Ensure any necessary firewall rules or network access permissions are in place before you begin.
- Date Range Filtering: It is critical to set a specific date range in the query's
WHEREclause to manage the data volume. A range of 3 to 6 months is typically sufficient for meaningful analysis without causing excessive load on the database. - Warehouse and Client Filtering: In multi-warehouse or multi-client environments, always filter by the specific
wh_id(Warehouse ID) andclient_id(Client ID) to ensure the analysis is focused and the dataset is manageable. - Performance Considerations: Executing this query on a live production database can impact system performance. It is strongly recommended to run it during off-peak hours or, preferably, against a dedicated reporting or replicated database if one is available.
- System Customizations: The provided query uses standard table and column names. Be prepared to adjust these names based on any customizations or version differences in your Blue Yonder WMS instance. Consult your internal WMS administrator or data dictionary for guidance.
a Sample Query sql
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