창고 관리 데이터 템플릿

DECLARE @StartDate DATETIME = '2023-01-01';
DECLARE @EndDate DATETIME = '2023-12-31';
DECLARE @WarehouseId NVARCHAR(10) = '[Your Warehouse ID]';

-- 1. Warehouse Order Created
SELECT
  ord.ORD_NBR AS WarehouseOrder,
  'Warehouse Order Created' AS ActivityName,
  ord.CREATE_DATE AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  ord.CREATE_USER AS UserOperatorId,
  ord.PRIORITY AS PriorityLevel,
  NULL AS ProductSKU,
  NULL AS ActualQuantity
FROM [ORD_HDR] ord
WHERE ord.CREATE_DATE BETWEEN @StartDate AND @EndDate AND ord.WH_ID = @WarehouseId

UNION ALL

-- 2. Inbound Delivery Notification Rcvd
SELECT
  asn.ASN_NBR AS WarehouseOrder,
  'Inbound Delivery Notification Rcvd' AS ActivityName,
  asn.CREATE_DATE AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  asn.CREATE_USER AS UserOperatorId,
  asn.PRIORITY AS PriorityLevel,
  NULL AS ProductSKU,
  NULL AS ActualQuantity
FROM [ASN_HDR] asn
WHERE asn.CREATE_DATE BETWEEN @StartDate AND @EndDate AND asn.WH_ID = @WarehouseId

UNION ALL

-- 3. Goods Arrived at Dock
SELECT
  asn.ASN_NBR AS WarehouseOrder,
  'Goods Arrived at Dock' AS ActivityName,
  asn.ACTUAL_ARRIVAL_TSTMP AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  asn.MOD_USER AS UserOperatorId,
  asn.PRIORITY AS PriorityLevel,
  NULL AS ProductSKU,
  NULL AS ActualQuantity
FROM [ASN_HDR] asn
WHERE asn.ACTUAL_ARRIVAL_TSTMP IS NOT NULL AND asn.ACTUAL_ARRIVAL_TSTMP BETWEEN @StartDate AND @EndDate AND asn.WH_ID = @WarehouseId

UNION ALL

-- 4. Goods Received and Counted
SELECT
  tran.ORD_NBR AS WarehouseOrder,
  'Goods Received and Counted' AS ActivityName,
  tran.TRAN_TSTMP AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  tran.USER_ID AS UserOperatorId,
  NULL AS PriorityLevel,
  tran.SKU AS ProductSKU,
  tran.TRAN_QTY AS ActualQuantity
FROM [INV_TRAN] tran
WHERE tran.TRAN_TYPE = 'RECV' AND tran.TRAN_TSTMP BETWEEN @StartDate AND @EndDate AND tran.WH_ID = @WarehouseId

UNION ALL

-- 5. Quality Inspection Performed
SELECT
  tran.ORD_NBR AS WarehouseOrder,
  'Quality Inspection Performed' AS ActivityName,
  tran.TRAN_TSTMP AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  tran.USER_ID AS UserOperatorId,
  NULL AS PriorityLevel,
  tran.SKU AS ProductSKU,
  tran.TRAN_QTY AS ActualQuantity
FROM [INV_TRAN] tran
WHERE tran.TRAN_TYPE = 'MOVE' AND tran.REASON_CODE = 'QI_INSP' AND tran.TRAN_TSTMP BETWEEN @StartDate AND @EndDate AND tran.WH_ID = @WarehouseId

UNION ALL

-- 6. Putaway Task Created
SELECT
  tsk.ORD_NBR AS WarehouseOrder,
  'Putaway Task Created' AS ActivityName,
  tsk.CREATE_DATE AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  tsk.CREATE_USER AS UserOperatorId,
  tsk.PRIORITY AS PriorityLevel,
  tsk.SKU AS ProductSKU,
  tsk.TASK_QTY AS ActualQuantity
FROM [TASK_DTL] tsk
WHERE tsk.TASK_TYPE = 'PUTAWAY' AND tsk.CREATE_DATE BETWEEN @StartDate AND @EndDate AND tsk.WH_ID = @WarehouseId

UNION ALL

-- 7. Goods Put Away in Storage
SELECT
  tsk.ORD_NBR AS WarehouseOrder,
  'Goods Put Away in Storage' AS ActivityName,
  tsk.CMPL_TSTMP AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  tsk.USER_ID AS UserOperatorId,
  tsk.PRIORITY AS PriorityLevel,
  tsk.SKU AS ProductSKU,
  tsk.CMPL_QTY AS ActualQuantity
FROM [TASK_DTL] tsk
WHERE tsk.TASK_TYPE = 'PUTAWAY' AND tsk.STAT_CODE = 'COMPLETED' AND tsk.CMPL_TSTMP IS NOT NULL AND tsk.CMPL_TSTMP BETWEEN @StartDate AND @EndDate AND tsk.WH_ID = @WarehouseId

UNION ALL

-- 8. Picking Task Created
SELECT
  tsk.ORD_NBR AS WarehouseOrder,
  'Picking Task Created' AS ActivityName,
  tsk.CREATE_DATE AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  tsk.CREATE_USER AS UserOperatorId,
  tsk.PRIORITY AS PriorityLevel,
  tsk.SKU AS ProductSKU,
  tsk.TASK_QTY AS ActualQuantity
FROM [TASK_DTL] tsk
WHERE tsk.TASK_TYPE = 'PICK' AND tsk.CREATE_DATE BETWEEN @StartDate AND @EndDate AND tsk.WH_ID = @WarehouseId

UNION ALL

-- 9. Goods Picked from Storage
SELECT
  tsk.ORD_NBR AS WarehouseOrder,
  'Goods Picked from Storage' AS ActivityName,
  tsk.CMPL_TSTMP AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  tsk.USER_ID AS UserOperatorId,
  tsk.PRIORITY AS PriorityLevel,
  tsk.SKU AS ProductSKU,
  tsk.CMPL_QTY AS ActualQuantity
FROM [TASK_DTL] tsk
WHERE tsk.TASK_TYPE = 'PICK' AND tsk.STAT_CODE = 'COMPLETED' AND tsk.CMPL_TSTMP IS NOT NULL AND tsk.CMPL_TSTMP BETWEEN @StartDate AND @EndDate AND tsk.WH_ID = @WarehouseId

UNION ALL

-- 10. Packing Initiated
SELECT
  pck.ORD_NBR AS WarehouseOrder,
  'Packing Initiated' AS ActivityName,
  MIN(pck.CREATE_DATE) AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  MIN(pck.USER_ID) AS UserOperatorId,
  NULL AS PriorityLevel,
  NULL AS ProductSKU,
  NULL AS ActualQuantity
FROM [PACK_TRAN] pck
WHERE pck.CREATE_DATE BETWEEN @StartDate AND @EndDate AND pck.WH_ID = @WarehouseId
GROUP BY pck.ORD_NBR

UNION ALL

-- 11. Goods Packed
SELECT
  ctn.ORD_NBR AS WarehouseOrder,
  'Goods Packed' AS ActivityName,
  ctn.PACK_CMPL_TSTMP AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  ctn.PACKER_ID AS UserOperatorId,
  NULL AS PriorityLevel,
  NULL AS ProductSKU,
  NULL AS ActualQuantity
FROM [SHIP_CARTON] ctn
WHERE ctn.PACK_CMPL_TSTMP IS NOT NULL AND ctn.PACK_CMPL_TSTMP BETWEEN @StartDate AND @EndDate AND ctn.WH_ID = @WarehouseId

UNION ALL

-- 12. Staged for Shipment
SELECT
  tran.ORD_NBR AS WarehouseOrder,
  'Staged for Shipment' AS ActivityName,
  tran.TRAN_TSTMP AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  tran.USER_ID AS UserOperatorId,
  NULL AS PriorityLevel,
  tran.SKU AS ProductSKU,
  tran.TRAN_QTY AS ActualQuantity
FROM [INV_TRAN] tran
JOIN [LOC_HDR] loc ON tran.TO_LOC = loc.LOC_ID AND tran.WH_ID = loc.WH_ID
WHERE tran.TRAN_TYPE = 'MOVE' AND loc.LOC_TYPE = 'SHIP_STAGE' AND tran.TRAN_TSTMP BETWEEN @StartDate AND @EndDate AND tran.WH_ID = @WarehouseId

UNION ALL

-- 13. Shipment Dispatched
SELECT
  shp.ORD_NBR AS WarehouseOrder,
  'Shipment Dispatched' AS ActivityName,
  shp.SHIP_CONFIRM_TSTMP AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  shp.USER_ID AS UserOperatorId,
  NULL AS PriorityLevel,
  NULL AS ProductSKU,
  NULL AS ActualQuantity
FROM [SHIPMENT_HDR] shp
WHERE shp.SHIP_CONFIRM_TSTMP IS NOT NULL AND shp.SHIP_CONFIRM_TSTMP BETWEEN @StartDate AND @EndDate AND shp.WH_ID = @WarehouseId

UNION ALL

-- 14. Warehouse Order Completed
SELECT
  ord.ORD_NBR AS WarehouseOrder,
  'Warehouse Order Completed' AS ActivityName,
  ord.MOD_DATE AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  ord.MOD_USER AS UserOperatorId,
  ord.PRIORITY AS PriorityLevel,
  NULL AS ProductSKU,
  NULL AS ActualQuantity
FROM [ORD_HDR] ord
WHERE ord.STAT_CODE IN ('99', 'COMPLETED') AND ord.MOD_DATE BETWEEN @StartDate AND @EndDate AND ord.WH_ID = @WarehouseId

UNION ALL

-- 15. Warehouse Order Canceled
SELECT
  ord.ORD_NBR AS WarehouseOrder,
  'Warehouse Order Canceled' AS ActivityName,
  ord.MOD_DATE AS EventTime,
  'Körber WMS' AS SourceSystem,
  GETDATE() AS LastDataUpdate,
  ord.MOD_USER AS UserOperatorId,
  ord.PRIORITY AS PriorityLevel,
  NULL AS ProductSKU,
  NULL AS ActualQuantity
FROM [ORD_HDR] ord
WHERE ord.STAT_CODE IN ('95', 'CANCELED') AND ord.MOD_DATE BETWEEN @StartDate AND @EndDate AND ord.WH_ID = @WarehouseId;