Sync DailyWorkOrder Status

1. Job Identification

• Job Name: Job_To_Sync_Daily_WorkOrder_Status_Every_11_Minutes
• Job ID: Auto-assigned by SQL Server Agent on creation
• Category: [Uncategorized (Local)]

2. Job Purpose

• Business Objective:

  • Sync daily work-order status data into the reporting database.
  • Keep packing plan and PO ex-factory split status up-to-date for real-time dashboards.
  • Ensure piece-level status flags (piece group, piece scan, end-line quality) are calculated and current.
  • Fetch the latest piece records from the main production database into the reporting store.

• Data Flow: Main DB -> ReportingSooperWizer -> Reporting dashboards / downstream consumers

2.1 Job Steps (Execution Order)

• Call Procedure EXEC [dbo].[uspFetchPieceFromMainDB]
• Call Procedure EXEC [dbo].[uspSetPieceStatusPieceGroup]
• Call Procedure EXEC [dbo].[uspSetPieceStatusPieceScan]
• Call Procedure EXEC [dbo].[uspSetPieceStatusEndLineQuality]
• Call Procedure EXEC [Reports].[uspRefreshPackingPlan]
• Call Procedure EXEC [Reports].[uspSyncDailyWorkOrderStatus]
• Call Procedure EXEC [Reports].[uspSyncPOExFactorySplitStatus]

2.2 Procedure Description

• [dbo].[uspFetchPieceFromMainDB]:
  • Pulls the latest piece records from the main production database into the reporting database.
• [dbo].[uspSetPieceStatusPieceGroup]:
  • Calculates and sets the piece status at the piece-group level.
• [dbo].[uspSetPieceStatusPieceScan]:
  • Calculates and sets the piece status based on scanning events.
• [dbo].[uspSetPieceStatusEndLineQuality]:
  • Calculates and sets the piece status based on end-line quality inspection results.
• [Reports].[uspRefreshPackingPlan]:
  • Refreshes the packing plan dataset used for reporting and planning.
• [Reports].[uspSyncDailyWorkOrderStatus]:
  • Synchronizes the daily work-order status summary for reporting dashboards.
• [Reports].[uspSyncPOExFactorySplitStatus]:
  • Synchronizes the PO ex-factory split status for order tracking reports.

3. Technical Details

• Server Name: T3600-SERVERPC\DEV2019
• Database: SooperWizer
• Service Account: sa

4. Job Steps

1. Step Name: Call Procedure [dbo].[uspFetchPieceFromMainDB]

   • Step Type: T-SQL Stored Procedure
   • Command/Query:

   EXEC [dbo].[uspFetchPieceFromMainDB]

   • Success Action: Continue to next step
   • Failure Action: Quit job reporting failure
   • Expected Duration: N/A

2. Step Name: Call Procedure [dbo].[uspSetPieceStatusPieceGroup]

   • Step Type: T-SQL Stored Procedure
   • Command/Query:

   EXEC [dbo].[uspSetPieceStatusPieceGroup]

   • Success Action: Continue to next step
   • Failure Action: Quit job reporting failure
   • Expected Duration: N/A

3. Step Name: Call Procedure [dbo].[uspSetPieceStatusPieceScan]

   • Step Type: T-SQL Stored Procedure
   • Command/Query:

   EXEC [dbo].[uspSetPieceStatusPieceScan]

   • Success Action: Continue to next step
   • Failure Action: Quit job reporting failure
   • Expected Duration: N/A

4. Step Name: Call Procedure [dbo].[uspSetPieceStatusEndLineQuality]

   • Step Type: T-SQL Stored Procedure
   • Command/Query:

   EXEC [dbo].[uspSetPieceStatusEndLineQuality]

   • Success Action: Continue to next step
   • Failure Action: Quit job reporting failure
   • Expected Duration: N/A

5. Step Name: Call Procedure [Reports].[uspRefreshPackingPlan]

   • Step Type: T-SQL Stored Procedure
   • Command/Query:

   EXEC [Reports].[uspRefreshPackingPlan]

   • Success Action: Continue to next step
   • Failure Action: Quit job reporting failure
   • Expected Duration: N/A

6. Step Name: Call Procedure [Reports].[uspSyncDailyWorkOrderStatus]

   • Step Type: T-SQL Stored Procedure
   • Command/Query:

   EXEC [Reports].[uspSyncDailyWorkOrderStatus]

   • Success Action: Continue to next step
   • Failure Action: Quit job reporting failure
   • Expected Duration: N/A

7. Step Name: Call Procedure [Reports].[uspSyncPOExFactorySplitStatus]

   • Step Type: T-SQL Stored Procedure
   • Command/Query:

   EXEC [Reports].[uspSyncPOExFactorySplitStatus]

   • Success Action: Quit job reporting success
   • Failure Action: Quit job reporting failure
   • Expected Duration: N/A

5. Scheduling

• Schedule Name: Every_11_Minutes
• Schedule UID: a771cfed-b9d5-4134-8a86-cfab1148992e
• Frequency Type: Daily (freq_type = 4)
• Sub-day Interval: Every 11 minutes (freq_subday_type = 4, freq_subday_interval = 11)
• Active Start Time: 00:00:00 (midnight — runs all day)
• Active End Time: 23:59:59
• Active Start Date: 2026-01-20
• Active End Date: No end date (9999-12-31)
• Overlap Prevention: SQL Server Agent will not start a new instance if previous execution is still running

6. Monitoring & Logging

• Error Log Location:

  • SQL Server Agent Job History
  • SQL Server Application Event Log

• Success Metrics:

  • All seven stored procedures complete without error.
  • Step exits with return code 0.
  • Reporting tables contain current data after job completion.

7. Data Processing Details

Dependencies

1. Source Objects:

• [dbo].[uspFetchPieceFromMainDB]
• [dbo].[uspSetPieceStatusPieceGroup]
• [dbo].[uspSetPieceStatusPieceScan]
• [dbo].[uspSetPieceStatusEndLineQuality]

2. Reporting Objects:

• [Reports].[uspRefreshPackingPlan]
• [Reports].[uspSyncDailyWorkOrderStatus]
• [Reports].[uspSyncPOExFactorySplitStatus]

3. Runtime Requirements:

• SQL Server Agent service must be running on the local instance.
• Service account ‘sa’ must have EXECUTE permission on all seven procedures.
• Network connectivity to the main production database.

8. Business Impact

• Work-Order Reporting: Provides up-to-date daily work-order status for production planning and management dashboards.
• Packing Visibility: Keeps packing plan data current for logistics and shipping teams.
• PO Tracking: Maintains ex-factory split status for customer order tracking and compliance.
• Quality Insight: Piece-level status flags enable end-line quality analysis and rework tracking.
• Operational Continuity: 11-minute refresh ensures near-real-time data availability throughout the working day.

9. Error Handling & Recovery

• Step retry attempts: 0 (no automatic retry at step level)
• On step failure: Quit job reporting failure — remaining procedures are skipped
• Transaction management: Each stored procedure manages its own transactions internally
• Manual recovery: Individual procedures can be executed manually in SSMS for targeted re-runs