Insert End-Line Daily Summary

1. Procedure Identity

• Procedure Name: usp_InsertEndLineDailySummary
• Database: SooperWizer
• Schema: Reports
• Version: 1.2 (Last Modified: 2026-07-04)

2. Purpose & Functionality

• Business Purpose: This stored procedure builds a daily summary from the raw end-line quality inspection data.

  1. Collects unprocessed end-line session records.
  2. Aggregates them into daily inspection summaries.
  3. Inserts new records into Reports.EndLineDailySummary.

• Functional Description:

  • Checks Config.JobConfig to find the ID of end-line session that was last processed.
  • Fetches records from Quality.EndLineSession where EndLineSessionID is greater than the last processed ID.
  • Creates a temporary table #TempEndLine to store records of end-line session 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 #TempEndLine with relevant master tables.
  • Groups data by hour, date, order, style, PO, color, size, line, section, company, branch, customer from #ToProcess table.
  • InspectedQuantity → Total distinct pieces inspected.
  • FirstTimeClearQuantity → Pieces cleared on first inspection (Status = 1, no fault).
  • ClearAfterReworkQuantity → Pieces cleared after rework (Status = 1 with fault).
  • AtReworkQuantity → Pieces currently at rework stage (Status = 2).
  • AtRejectionQuantity → Pieces rejected (Status = 3).
  • Create a temp table named #Agg for aggregated data.
  • Inserts aggregated rows into Reports.EndLineDailySummary 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 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.EndLineSession
  • Quality.EndLineFaultLog
  • Essentials.Line
  • Essentials.Section
  • Essentials.Customer
  • Essentials.Color
  • Essentials.Size
  • Essentials.Branch
  • Essentials.Factory
  • Orders.PurchaseOrder
  • Orders.WorkOrder
  • Orders.Style

Table Name	Schema	Action(s) in Procedure	Notes
EndLineSession	Quality	SELECT, UPDATE	Source of session/piece data
WorkOrder	Orders	SELECT (JOIN)	Provides WorkOrder code and buy month.
PurchaseOrder	Orders	SELECT (JOIN)	Provides PurchaseOrder code.
Style	Orders	SELECT (JOIN)	Provides Style ID and name.
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).
Customer	Essentials	SELECT (JOIN)	Provides Customer code and name.
EndLineFaultLog	Quality	SELECT (LEFT JOIN)	Links faults to inspected pieces.
Bundle	Cutting	SELECT (JOIN)	Links bundles to work order details.
EndLineDailySummary	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_InsertEndLineDailySummary]
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].[EndLineDailySummary]'
                AND JobName   = '[Reports].[usp_InsertEndLineDailySummary]'
                AND IsActive  = 1
            ORDER BY JobConfigID;

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

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


            -- Get new records from EndLineSession
            SELECT
                EndLineSessionID, BundleID, PieceID, LineID, SectionID, Status, CreatedAt, UpdatedAt
            INTO #TempEndLine
            FROM [Quality].[EndLineSession]
            WHERE EndLineSessionID > ISNULL(@LastProcessedID,0)
            ORDER BY EndLineSessionID;

            -- If no rows found, mark as no new records and exit
            IF NOT EXISTS (SELECT 1 FROM #TempEndLine)
            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;

            -- Build ToProcess data
            SELECT
                wo.WorkOrderCode, po.PurchaseOrderCode, col.ColorID, col.ColorCode, col.ColorName, s.SizeID, s.SizeCode, s.SizeName, st.StyleID, st.StyleName,
                l.LineID, l.LineCode, l.LineName, sec.SectionID, sec.SectionCode, sec.SectionName, fac.FactoryID, fac.FactoryCode, fac.FactoryName, br.BranchID,
                br.BranchCode, br.BranchName, cust.CustomerCode, cust.CustomerName, wo.BuyMonth, CONVERT(DATE, els.UpdatedAt) AS InspectionDate,
                RIGHT('0' + CAST(DATEPART(HOUR, els.UpdatedAt) AS VARCHAR(2)), 2) AS HourSlot, elfl.FaultID, els.PieceID, els.Status, els.UpdatedAt, els.EndLineSessionID
            INTO #ToProcess
            FROM #TempEndLine as els
            LEFT JOIN [Quality].[EndLineFaultLog] as elfl ON
                elfl.EndLineSessionID = els.EndLineSessionID
            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 [Essentials].[Customer] as cust ON
                cust.CustomerID = wo.CustomerID
            JOIN [Orders].[PurchaseOrderWorkOrderStyle] as powos ON
                powos.PurchaseOrderWorkOrderStyleID = b.PurchaseOrderWorkOrderStyleID
            JOIN [Orders].[PurchaseOrder] as po ON
                po.PurchaseOrderID = powos.PurchaseOrderID
            JOIN [Orders].[Style] as st ON
                st.StyleID = wos.StyleID
            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

            -- Aggregate results into temp table
            SELECT
                HourSlot, InspectionDate, WorkOrderCode, PurchaseOrderCode, StyleID, StyleName, BuyMonth, CustomerCode, CustomerName, ColorID, ColorCode, ColorName, SizeID, SizeCode, SizeName,
                FactoryID, FactoryCode, FactoryName, BranchID, BranchCode, BranchName, LineID, LineCode, LineName, SectionID, SectionCode, SectionName,
                COUNT(DISTINCT PieceID) AS InspectedQuantity,
                COUNT(DISTINCT CASE WHEN Status = 1 AND FaultID IS NULL THEN PieceID END) AS FirstTimeClearQuantity,
                COUNT(DISTINCT CASE WHEN Status = 1 AND FaultID IS NOT NULL THEN PieceID END) AS ClearAfterReworkQuantity,
                COUNT(DISTINCT CASE WHEN Status = 2 THEN PieceID END) AS AtReworkQuantity,
                COUNT(DISTINCT CASE WHEN Status = 3 THEN PieceID END) AS AtRejectionQuantity
            INTO #Agg
            FROM #ToProcess
            GROUP BY
                HourSlot, InspectionDate, WorkOrderCode, PurchaseOrderCode, StyleID, StyleName, BuyMonth, CustomerCode, CustomerName, ColorID, ColorCode, ColorName, SizeID, SizeCode, SizeName,
                FactoryID, FactoryCode, FactoryName, BranchID, BranchCode, BranchName, LineID, LineCode, LineName, SectionID, SectionCode, SectionName;


            -- Insert new rows
            INSERT INTO [Reports].[EndLineDailySummary] (
                WorkOrderCode, PurchaseOrderCode, ColorID, ColorCode, ColorName, SizeID, SizeCode, SizeName, StyleID, StyleName,
                LineID, LineCode, LineName, SectionID, SectionCode, SectionName, FactoryID, FactoryCode, FactoryName, BranchID,
                BranchCode, BranchName, CustomerCode, CustomerName, BuyMonth, InspectedQuantity, FirstTimeClearQuantity,
                ClearAfterReworkQuantity, AtReworkQuantity, AtRejectionQuantity, InspectionDate, HourSlot
            )
            SELECT
                WorkOrderCode, PurchaseOrderCode, ColorID, ColorCode, ColorName, SizeID, SizeCode, SizeName, StyleID, StyleName,
                LineID, LineCode, LineName, SectionID, SectionCode, SectionName, FactoryID, FactoryCode, FactoryName, BranchID,
                BranchCode, BranchName, CustomerCode, CustomerName, BuyMonth, InspectedQuantity, FirstTimeClearQuantity,
                ClearAfterReworkQuantity, AtReworkQuantity, AtRejectionQuantity, InspectionDate, HourSlot
            FROM #Agg as a
            WHERE NOT EXISTS (
                SELECT 1
                FROM [Reports].[EndLineDailySummary] as elds
                WHERE elds.InspectionDate = a.InspectionDate
                AND elds.HourSlot = a.HourSlot
                    AND elds.WorkOrderCode = a.WorkOrderCode
                    AND elds.StyleID = a.StyleID
                    AND elds.BuyMonth = a.BuyMonth
                    AND elds.CustomerCode = a.CustomerCode
                    AND elds.PurchaseOrderCode = a.PurchaseOrderCode
                    AND elds.ColorID = a.ColorID
                    AND elds.SizeID = a.SizeID
                    AND elds.FactoryID = a.FactoryID
                    AND elds.BranchID = a.BranchID
                    AND elds.LineID = a.LineID
                    AND elds.SectionID = a.SectionID
            );

            -- Find max ID processed in this run
            SELECT @NewMaxID = MAX(s.EndLineSessionID)
            FROM #TempEndLine 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 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 Summary. Error: ' + ERROR_MESSAGE() AS Message;
    END CATCH

END
GO

Performance Considerations

• Indexes Used:
  • Quality.EndLineSession: IX_EndLineSession_PieceID_SectionID_LineID
  • Reports.EndLineDailySummary: None (to be added later for insert operations)
• Potential Bottlenecks: Large volumes of unprocessed records, complex joins with master data
• Transaction Handling: Explicit transaction with automatic rollback on errors

6. Usage Information

Calling Jobs

• Update EndLineDailySummary - 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_InsertEndLineDailySummary];

-- Verify results after execution
SELECT TOP 10 *
FROM [Reports].[EndLineDailySummary]
WHERE InspectionDate = CONVERT(DATE, GETDATE())
ORDER BY InspectionDate DESC, HourSlot DESC;

Expected Results

• Success Scenario:
  • Unprocessed session queue records aggregated into summary table
  • Procedure completes successfully (Code: 0, Message: ‘EndLine Daily 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. Monitoring & Troubleshooting

• Performance Metrics: Track execution time, processed record count, and memory usage
• Data Quality Checks: Validate that all unprocessed records are successfully processed
• Error Handling: Monitor for foreign key violations, data type mismatches, and timeout issues
• Maintenance Requirements: Regular index maintenance on involved tables for optimal performance