Insert End-Line Daily Fault Summary

1. Procedure Identity

• Procedure Name: usp_InsertEndLineDailyFaultSummary
• Database: SooperWizer
• Schema: Reports
• Version: 1.1 (Last Modified: 2026-03-26)

2. Purpose & Functionality

• Business Purpose: This stored procedure builds a daily fault summary from the raw fault log.

  1. Collects unprocessed end-line fault log records.
  2. Aggregates them into daily fault log summaries.
  3. Inserts new records into Reports.EndLineDailyFaultSummary.

• Functional Description:

  • Checks Config.JobConfig to find the ID of end-line fault log that was last processed.
  • Fetches records from Quality.EndLineFaultLog where EndLineFaultLogID is greater than the last processed ID.
  • Creates a temporary table #TempEndLineFaultLog to store records of end-line fault log which are not processed yet.
  • If no new records exist, it updates status in Config.JobConfig (‘No New Records’) and quits.
  • If new records exist, it creates a temporary table #ToProcess by joining #TempEndLineFaultLog with relevant master tables.
  • Groups data by date, order, style, PO, color, size, line, section, company, branch from #ToProcess table.
  • FaultCount → SUM(FaultCount) over grouping fields
  • Create a temp table named #Agg for aggregated data.
  • Inserts aggregated rows into Reports.EndLineDailyFaultSummary skipping any rows that match with the grouping set.
  • Updates LastProcessedID in Config.JobConfig so that the next run picks up exactly where this one left off.

• Return Values:

  • Procedure completes successfully (Code: 0, Message: ‘EndLine Daily Fault Summary updated successfully.’)
  • Procedure encounters an error (Code: ERROR_NUMBER(), Message: ERROR_MESSAGE())

3. Parameters

This procedure does not take any input parameters.

4. Dependencies

• Base Tables:
  • Quality.EndLineFaultLog
  • Quality.EndLineSession
  • Essentials.Line
  • Essentials.Section
  • Essentials.Operation
  • Essentials.Machine
  • Essentials.Worker
  • Essentials.Color
  • Essentials.Size
  • Essentials.Branch
  • Essentials.Factory
  • Orders.PurchaseOrder
  • Orders.WorkOrder

Table Name	Schema	Action(s) in Procedure	Notes
EndLineFaultLog	Quality	SELECT, UPDATE	Source of fault logs
EndLineSession	Quality	SELECT (JOIN)	Links fault logs to end-line session to fetch LineID, SectionID
Bundle	Cutting	SELECT (JOIN)	Links bundles to work order details.
WorkOrder	Orders	SELECT (JOIN)	Provides WorkOrder code and buy month.
PurchaseOrder	Orders	SELECT (JOIN)	Provides PurchaseOrder code.
Color	Essentials	SELECT (JOIN)	Provides Color details (ID, Code, Name).
Size	Essentials	SELECT (JOIN)	Provides Size details (ID, Code, Name).
Line	Essentials	SELECT (JOIN)	Provides Line details (ID, Code, Name).
Section	Essentials	SELECT (JOIN)	Provides Section details (ID, Code, Name).
Branch	Essentials	SELECT (JOIN)	Provides Branch details (ID, Code, Name).
Factory	Essentials	SELECT (JOIN)	Provides Factory details (ID, Code, Name).
PieceScan	Scanning	SELECT (LEFT JOIN)	Links fault logs to scanning details to fetch operation/machine/worker details.
Operation	Essentials	SELECT (LEFT JOIN)	Provides Operation details (ID, Code, Name).
Machine	Essentials	SELECT (LEFT JOIN)	Provides Machine details (ID, Code, Name).
Worker	Essentials	SELECT (LEFT JOIN)	Provides Worker details (ID, Code, Name).
EndLineDailyFaultSummary	Reports	SELECT, UPDATE, INSERT	Final reporting/aggregation table updated by this procedure.

• Referenced Objects: None (self-contained procedure)
• Inter-Database Dependencies: None
• Server Dependencies: None

5. Technical Implementation

T-SQL Code

CREATE OR ALTER PROCEDURE [Reports].[usp_InsertEndLineDailyFaultSummary]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @JobConfigID INT,
            @LastProcessedID INT,
            @NewMaxID INT;

    BEGIN TRY
        BEGIN TRANSACTION;

            -- 1. Get current config for Piece table
            SELECT TOP 1
                @JobConfigID     = JobConfigID,
                @LastProcessedID = LastProcessedID
            FROM [Config].[JobConfig]
            WHERE TableName = '[Reports].[EndLineDailyFaultSummary]'
                AND JobName   = '[Reports].[usp_InsertEndLineDailyFaultSummary]'
                AND IsActive  = 1
            ORDER BY JobConfigID;

            -- Safety check
            IF @JobConfigID IS NULL
            BEGIN
                RAISERROR('Job config for [Reports].[usp_InsertEndLineDailyFaultSummary] not found in Config.JobConfig',16,1);
                ROLLBACK TRANSACTION;
                RETURN;
            END;

            DROP TABLE IF EXISTS #TempEndLineFaultLog;
            DROP TABLE IF EXISTS #ToProcess;
            DROP TABLE IF EXISTS #Agg;

            -- Get new records from EndLineFaultLog
            SELECT
                EndLineFaultLogID, EndLineSessionID, FaultID, FaultCount, PieceScanID, CreatedAt, UpdatedAt
            INTO #TempEndLineFaultLog
            FROM [Quality].[EndLineFaultLog]
            WHERE EndLineFaultLogID > ISNULL(@LastProcessedID,0)
            ORDER BY EndLineFaultLogID;

            -- If no rows found, mark as no new records and exit
            IF NOT EXISTS (SELECT 1 FROM #TempEndLineFaultLog)
            BEGIN
                UPDATE [Config].[JobConfig]
                SET Status    = 'No New Records',
                    UpdatedAt = GETUTCDATE()
                WHERE JobConfigID = @JobConfigID;

                COMMIT TRANSACTION;
                SELECT 0 AS Code, 'No new records found for processing.' AS Message;
                RETURN;
            END;

            -- Get fault log rows to process
            SELECT
                elfl.EndLineFaultLogID, f.FaultID, f.FaultCode, f.FaultName, elfl.FaultCount, col.ColorID, col.ColorCode, col.ColorName, s.SizeID, s.SizeCode, s.SizeName,
                wo.WorkOrderCode, po.PurchaseOrderCode, o.OperationID, o.OperationCode, o.OperationName, m.MachineID, m.MachineCode, m.MachineName,
                w.WorkerID, w.WorkerCode, w.WorkerName, l.LineID, l.LineCode, l.LineName, sec.SectionID, sec.SectionCode, sec.SectionName, fac.FactoryID,
                fac.FactoryCode, fac.FactoryName, br.BranchID, br.BranchCode, br.BranchName, CAST(elfl.CreatedAt as DATE) as InspectionDate
            INTO #ToProcess
            FROM Quality.EndLineFaultLog as elfl
            JOIN [Quality].[EndLineSession] as els ON
                els.EndLineSessionID = elfl.EndLineSessionID
            JOIN [Essentials].[Fault] as f ON
                f.FaultID = elfl.FaultID
            JOIN [Cutting].[Bundle] as b ON
                b.BundleID = els.BundleID
            JOIN [Essentials].[Section] as sec ON
                sec.SectionID = els.SectionID
            JOIN [Essentials].[Line] as l ON
                l.LineID = els.LineID
            JOIN [Orders].[WorkOrderStyle] as wos ON
                wos.WorkOrderStyleID = b.WorkOrderStyleID
            JOIN [Orders].[WorkOrder] as wo ON
                wo.WorkOrderID = wos.WorkOrderID
            JOIN [Orders].[PurchaseOrderWorkOrderStyle] as powos ON
                powos.PurchaseOrderWorkOrderStyleID = b.PurchaseOrderWorkOrderStyleID
            JOIN [Orders].[PurchaseOrder] as po ON
                po.PurchaseOrderID = powos.PurchaseOrderID
            JOIN [Essentials].[Color] as col ON
                col.ColorID = b.ColorID
            JOIN [Essentials].[Size] s ON
                s.SizeID = b.SizeID
            JOIN [Essentials].[Branch] as br ON
                br.BranchID = l.BranchID
            JOIN [Essentials].[Factory] as fac ON
                fac.FactoryID = br.FactoryID
            LEFT JOIN [Scanning].[PieceScan] as ps ON
                ps.PieceScanID = elfl.PieceScanID
            LEFT JOIN [Essentials].[Operation] as o ON
                o.OperationID = ps.OperationID
            LEFT JOIN [Essentials].[Worker] as w ON
                w.WorkerID = ps.WorkerID
            LEFT JOIN [Essentials].[Machine] as m ON
                m.MachineID = ps.MachineID


            -- Step 2: Aggregate
            SELECT
                InspectionDate, WorkOrderCode, PurchaseOrderCode, FaultID, FaultCode, FaultName, SUM(FaultCount) AS FaultCount, ColorID, ColorCode, ColorName,
                SizeID, SizeCode, SizeName, OperationID, OperationCode, OperationName, MachineID, MachineCode, MachineName, WorkerID, WorkerCode, WorkerName,
                LineID, LineCode, LineName, SectionID, SectionCode, SectionName, FactoryID, FactoryCode, FactoryName, BranchID, BranchCode, BranchName
            INTO #Agg
            FROM #ToProcess
            GROUP BY
                InspectionDate, WorkOrderCode, PurchaseOrderCode, FaultID, FaultCode, FaultName, ColorID, ColorCode, ColorName,
                SizeID, SizeCode, SizeName, OperationID, OperationCode, OperationName, MachineID, MachineCode, MachineName, WorkerID, WorkerCode, WorkerName,
                LineID, LineCode, LineName, SectionID, SectionCode, SectionName, FactoryID, FactoryCode, FactoryName, BranchID, BranchCode, BranchName


            -- Step 3: Insert new rows
            INSERT INTO [Reports].[EndLineDailyFaultSummary] (
                FaultID, FaultCode, FaultName, FaultCount, ColorID, ColorCode, ColorName, SizeID, SizeCode, SizeName, WorkOrderCode, PurchaseOrderCode,
                OperationID, OperationCode, OperationName, MachineID, MachineCode, MachineName, WorkerID, WorkerCode, WorkerName, LineID, LineCode,
                LineName, SectionID, SectionCode, SectionName, FactoryID, FactoryCode, FactoryName, BranchID, BranchCode, BranchName, InspectionDate
            )
            SELECT
                FaultID, FaultCode, FaultName, FaultCount, ColorID, ColorCode, ColorName, SizeID, SizeCode, SizeName, WorkOrderCode, PurchaseOrderCode,
                OperationID, OperationCode, OperationName, MachineID, MachineCode, MachineName, WorkerID, WorkerCode, WorkerName, LineID, LineCode,
                LineName, SectionID, SectionCode, SectionName, FactoryID, FactoryCode, FactoryName, BranchID, BranchCode, BranchName, InspectionDate
            FROM #Agg as a
            WHERE NOT EXISTS (
                SELECT 1
                FROM [Reports].[EndLineDailyFaultSummary] as eldfs
                WHERE eldfs.InspectionDate = a.InspectionDate
                AND eldfs.WorkOrderCode = a.WorkOrderCode
                    AND eldfs.PurchaseOrderCode = a.PurchaseOrderCode
                    AND eldfs.ColorID = a.ColorID
                    AND eldfs.SizeID = a.SizeID
                    AND eldfs.FaultID = a.FaultID
                    AND eldfs.FactoryID = a.FactoryID
                    AND eldfs.BranchID = a.BranchID
                    AND eldfs.LineID = a.LineID
                    AND eldfs.SectionID = a.SectionID
                    AND ISNULL(eldfs.WorkerID, -1) = ISNULL(a.WorkerID, -1)
                    AND ISNULL(eldfs.OperationID, -1) = ISNULL(a.OperationID, -1)
                    AND ISNULL(eldfs.MachineID, -1) = ISNULL(a.MachineID, -1)
            );

            -- Find max ID processed in this run
            SELECT @NewMaxID = MAX(s.EndLineFaultLogID)
            FROM #TempEndLineFaultLog s;

            -- Update job config
            UPDATE [Config].[JobConfig]
            SET LastProcessedID = @NewMaxID,
                Status          = 'Success',
                UpdatedAt       = GETUTCDATE()
            WHERE JobConfigID = @JobConfigID;

        COMMIT TRANSACTION;

        SELECT 0 AS Code, 'EndLine Daily Fault Summary updated successfully.' AS Message;

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        IF @JobConfigID IS NOT NULL
        BEGIN
            UPDATE [Config].[JobConfig]
            SET Status    = CONCAT('Failed: ', ERROR_MESSAGE()),
                UpdatedAt = GETUTCDATE()
            WHERE JobConfigID = @JobConfigID;
        END;

        -- Return error message
        SELECT ERROR_NUMBER() AS Code,
               'Failed to update EndLine Daily Fault Summary. Error: ' + ERROR_MESSAGE() AS Message;
    END CATCH
END
GO

Performance Considerations

• Indexes Used: None (to be added later for insert operations)
• Potential Bottlenecks: Complex multi-table joins, large volumes of fault records, NULL handling in WHERE clauses
• Transaction Handling: Explicit transaction with automatic rollback on errors
• Optimization: Uses FILLFACTOR = 95 on target table for frequent updates

6. Usage Information

Calling Jobs

• Update EndLineDailyFaultSummary - Every 7 Minutes
• Manual execution for data recovery or catch-up processing

Calling Applications

• SQL Server Agent (scheduled execution)
• Manufacturing dashboard refresh processes
• Data recovery and maintenance scripts

Usage Examples

-- Standard execution (most common)
EXEC [Reports].[usp_InsertEndLineDailyFaultSummary];

-- Verify fault summary results after execution
SELECT TOP 10 WorkOrderCode, FaultCode, FaultName, FaultCount,
       LineCode, WorkerCode, OperationCode, InspectionDate
FROM [Reports].[EndLineDailyFaultSummary]
WHERE InspectionDate = CONVERT(DATE, GETDATE())
ORDER BY FaultCount DESC, InspectionDate DESC;

-- Analyze fault trends by worker
SELECT WorkerCode, FaultCode, SUM(FaultCount) AS TotalFaults
FROM [Reports].[EndLineDailyFaultSummary]
WHERE InspectionDate >= DATEADD(DAY, -7, GETDATE())
GROUP BY WorkerCode, FaultCode
ORDER BY TotalFaults DESC;

Expected Results

• Success Scenario:
  • Unprocessed fault log records aggregated into summary table
  • Procedure completes successfully (Code: 0, Message: ‘EndLine Daily Fault Summary updated successfully.’)
• No Data Scenario: Procedure completes successfully with no changes if no unprocessed records exist
• Error Scenarios: Procedure fails with SQL error if master data integrity issues or system problems occur

7. Business Value & Analytics

Quality Analysis Capabilities

• Fault Trend Analysis: Track fault patterns over time by type, worker, operation, and machine
• Root Cause Analysis: Identify correlations between faults and operational factors
• Worker Performance: Analyze individual worker fault rates and patterns
• Operation Efficiency: Identify operations with highest fault rates
• Machine Performance: Track machine-related quality issues
• Customer Impact: Analyze fault patterns by customer and order

Key Performance Indicators

• Defect Rate by Fault Type: Most common quality issues
• Worker Fault Attribution: Individual performance tracking
• Operation Fault Density: Process improvement opportunities
• Machine Fault Correlation: Maintenance and replacement planning
• Hourly Fault Patterns: Shift and time-based quality trends

8. Data Quality & Integrity

Null Value Handling

• WorkerID, OperationID, MachineID: Can be NULL when scanning context unavailable
• Comparison Logic: Uses ISNULL(-1) pattern for proper NULL matching in insert
• Data Completeness: Tracks availability of contextual information

Validation Rules

• FaultID must exist in Essentials.Fault master table
• Date Integrity: CreatedAt timestamp must be valid
• Count Validation: FaultCount must be positive integer

9. Monitoring & Troubleshooting

• Performance Metrics: Track execution time, processed fault count, and aggregation efficiency
• Data Quality Checks: Validate that all unprocessed fault records are successfully processed
• Error Handling: Monitor for referential integrity violations, data type mismatches, and timeout issues
• Context Availability: Track percentage of faults with worker/operation/machine attribution
• Maintenance Requirements: Regular index maintenance and statistics updates for optimal performance