mes.spGetWorkOrders

Description

Returns a list of work orders and associated item, asset, and status metadata. Supports extensive filtering options including asset, item, date ranges, work order status, and more. Designed for reporting and planning interfaces that need to explore or filter active and historical work orders.

Syntax

mes.spGetWorkOrders @assetId, @assetIdList, @assetPath, @workOrderName, @workOrderId, @workOrderIdList, @startDate, @endDate, @itemId, @itemIdList, @itemClassIdList, @workOrderStatusName, @workOrderStatusList, @workOrderSourceId, @workOrderSourceName

Parameters:
- @assetId (int): Filter by a specific asset ID.
- @assetIdList (varchar): Comma-separated list of asset IDs.
- @assetPath (varchar): Wildcard pattern for asset path matching.
- @workOrderName (varchar): Filter by name pattern.
- @workOrderId (int): Filter by specific work order ID.
- @workOrderIdList (varchar): Comma-separated list of work order IDs.
- @startDate, @endDate (datetime): Work orders due or created between these dates.
- @itemId (int): Filter by specific item ID.
- @itemIdList, @itemClassIdList (varchar): Lists of items or item classes.
- @workOrderStatusName, @workOrderStatusList (varchar): Match by status name or list of names.
- @workOrderSourceId, @workOrderSourceName (various): Filter by source info.

Returns:
Multiple rows with detailed columns including:
- **Work Order**: ID, name, due date, status, source, note
- **Item**: ID, name, class, unit, color
- **Asset**: ID, path
- **Production**: mode, required/scheduled/actual quantity
- **Audit Info**: created/changed by/date

Code Example

-- Retrieve all work orders for asset 1025 that are due in August 2025
EXEC mes.spGetWorkOrders
  @assetId = 1025,
  @assetIdList = NULL,
  @assetPath = NULL,
  @workOrderName = NULL,
  @workOrderId = NULL,
  @workOrderIdList = NULL,
  @startDate = '2025-08-01',
  @endDate = '2025-08-31',
  @itemId = NULL,
  @itemIdList = NULL,
  @itemClassIdList = NULL,
  @workOrderStatusName = NULL,
  @workOrderStatusList = NULL,
  @workOrderSourceId = NULL,
  @workOrderSourceName = NULL;