End-Line Daily Summary

1. Job Identification

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

---

2. Job Purpose

Business Objective:

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

Data Flow:

Quality.EndLineSession → Processing (Enrichment & Aggregation) → Reports.EndLineDailySummary

Dependencies:

• Requires Quality.EndLineSession to have unprocessed records (EndLineSessionID > 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, Bundle, WorkOrder, PurchaseOrder, Style, Color, Size, Customer
• Depends on Quality.EndLineFaultLog for fault enrichment per inspection session

2.1 Job Steps (Execution Order)

• Step 1: EXEC [Reports].[usp_InsertEndLineDailySummary]

---

3. Technical Details

Permissions Required:

• SELECT on Quality.EndLineSession
• SELECT on Quality.EndLineFaultLog
• SELECT on all Essentials master tables (Line, Section, Branch, Factory, Color, Size, Customer)
• SELECT on Orders tables (WorkOrder, WorkOrderStyle, PurchaseOrder, PurchaseOrderWorkOrderStyle, Style)
• SELECT on Cutting.Bundle
• INSERT on Reports.EndLineDailySummary
• UPDATE on Config.JobConfig

---

4. Job Steps

Step 1: Insert EndLineDailySummary

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

Command:

EXEC [Reports].[usp_InsertEndLineDailySummary];

Description:

Incrementally reads new records from Quality.EndLineSession using a watermark (LastProcessedID) stored in Config.JobConfig. Enriches the raw session data with master data via joins, aggregates into hourly slots, and inserts non-duplicate rows into Reports.EndLineDailySummary. On completion, advances the watermark to the highest EndLineSessionID 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 EndLineSession rows exist (this is not an error)
• New rows visible in Reports.EndLineDailySummary within 7 minutes of inspection 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_InsertEndLineDailySummary

Quantity Calculations

• InspectedQuantity: Total distinct pieces
• FirstTimeClearQuantity: Status = 1 with no faults
• ClearAfterReworkQuantity: Status = 1 with faults
• AtReworkQuantity: Status = 2
• AtRejectionQuantity: Status = 3

---

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.EndLineSession
• Quality.EndLineFaultLog populated with fault records linked to EndLineSessionID
• Config.JobConfig must have an active row for this job with a valid LastProcessedID

Downstream Dependencies:

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