End-Line Daily Fault Summary

1. Job Identification

Job Name: Update EndLineDailyFaultSummary - Every 7 Minutes
Database: SooperWizer
Server: T3600-SERVERPC\DEV2019

2. Job Purpose

Business Objective:

Runs the stored procedure [Reports].[usp_InsertEndLineDailyFaultSummary] every 7 minutes to incrementally process end-line fault log data and aggregate it into hourly summaries for reporting and analytics.

Data Flow:

Quality.EndLineFaultLog → Processing (Enrichment & Aggregation) → Reports.EndLineDailyFaultSummary

Dependencies:

Requires Quality.EndLineFaultLog to have unprocessed records (EndLineFaultLogID > LastProcessedID)
Requires Config.JobConfig to have an active entry for this job to track the watermark (LastProcessedID)
Depends on master data tables: Line, Section, Branch, Factory, WorkOrder, PurchaseOrder, Bundle, Color, Size, Operation, Machine, Worker

2.1 Job Steps (Execution Order)

Step 1: EXEC [Reports].[usp_InsertEndLineDailyFaultSummary]

3. Technical Details

Permissions Required:

SELECT on Quality.EndLineFaultLog
SELECT on Quality.EndLineSession
SELECT on all Essentials master tables (Line, Section, Branch, Factory, Color, Size, Customer, Operation, Machine, Worker)
SELECT on Orders tables (WorkOrder, WorkOrderStyle, PurchaseOrder, PurchaseOrderWorkOrderStyle)
SELECT on Cutting.Bundle
SELECT on Scanning.PieceScan
INSERT on Reports.EndLineDailyFaultSummary
UPDATE on Config.JobConfig

4. Job Steps

Step 1: Insert EndLineDailyFaultSummary

Step Name: Insert EndLineDailyFaultSummary
Step Type: T-SQL Stored Procedure
Success Action: Quit job reporting success
Failure Action: Quit job reporting failure

Command:

EXEC [Reports].[usp_InsertEndLineDailyFaultSummary];

Description:

Incrementally reads new records from Quality.EndLineFaultLog using a watermark (LastProcessedID) stored in Config.JobConfig. Enriches the raw fault log data with master data via joins, aggregates into hourly slots, and inserts non-duplicate rows into Reports.EndLineDailyFaultSummary. On completion, advances the watermark to the highest EndLineFaultLogID processed in the run.

5. Scheduling

Property	Value
Frequency	Every 7 Minutes
Frequency Type	Daily
Active Start Time	00:00:00 (midnight)
Active End Time	23:59:59
Overlap Prevention	SQL Server Agent will not start a new execution if the previous one is still running
Retry Logic	No retries configured — the next scheduled run within 7 minutes acts as the effective retry

6. Monitoring & Logging

Error Log Locations:

SQL Server Agent Job History (primary)
Windows Application Event Log — logged on failure only (notify_level_eventlog = 2)
Config.JobConfig — the Status column is updated after every run with 'Success', 'No New Records', or 'Failed: <error message>'

Success Metrics:

Config.JobConfig.Status = 'Success' and LastProcessedID advances after each run
Config.JobConfig.Status = 'No New Records' when no new EndLineFaultLog rows exist (this is not an error)
New rows visible in Reports.EndLineDailyFaultSummary within 7 minutes of fault log data being recorded

Note: Email, SMS, and dashboard alerting are not implemented in the current procedure or job script. The job logs failures to the Windows Event Log and to Config.JobConfig only. Any additional alerting must be configured separately.

7. Data Processing Details

Procedure Logic

Refer to usp_InsertEndLineDailyFaultSummary

Quantity Calculations

FaultCount: Total faults of each grouping set

8. Business Impact

Quality Reporting: Enables near-real-time quality dashboards refreshed every 7 minutes
Performance Tracking: Provides hourly data for line and section performance analysis
Trend Analysis: Supports quality trend identification by order, style, color, and size
Compliance: Maintains an audit trail via Config.JobConfig run history
Decision Support: Provides granular data for production planning and corrective actions

9. Error Handling & Recovery

Transaction Management: The procedure wraps all work in an explicit transaction with full rollback on any failure
Watermark Safety: LastProcessedID is only updated on a successful COMMIT, so a failed run will reprocess the same records on the next execution
Duplicate Prevention: WHERE NOT EXISTS on insert ensures re-runs do not create duplicate summary rows
Status Tracking: Config.JobConfig.Status is set to 'Failed: <error message>' on failure for easy diagnosis
Manual Recovery: The procedure can be executed manually at any time — it will always pick up from LastProcessedID
No Retry Logic: The job has 0 retry attempts configured; the next scheduled run (within 7 minutes) acts as the effective retry

10. Dependencies & Integration Points

Upstream Dependencies:

End-line inspection stations feeding data into Quality.EndLineFaultLog
Config.JobConfig must have an active row for this job with a valid LastProcessedID

Downstream Dependencies:

Reports.EndLineDailyFaultSummary feeds quality reporting views and dashboards
Management reporting systems consuming the summary table
Performance analysis applications dependent on hourly aggregated data