End-Line Daily Summary

1. Dataset Overview

• Dataset Name: EndLineDailySummary
• Description: Stores daily production inspection summaries at the end-line, capturing inspected quantities, cleared counts, rework, and rejections, grouped by order, size, color, style, line, branch, factory, section, and hourly time slot.
• Owner/Maintainer: Manufacturing Reports Team / Production Analytics

2. Schema Definition

• Database: SooperWizer
• Server: T3600-SERVERPC\DEV2019
• Schema: Reports

Table Structure

Column Name	Data Type	Constraints	Description
EndLineDailySummaryID	INT	PRIMARY KEY, IDENTITY(1,1)	Unique daily summary record ID
WorkOrderCode	VARCHAR(100)	NOT NULL	Order identifier
PurchaseOrderCode	VARCHAR(100)	NOT NULL	Purchase order number
ColorID	INT	NOT NULL	Product color ID
ColorCode	NVARCHAR(128)	NOT NULL	Product color code
ColorName	NVARCHAR(128)	NULL	Color description
SizeID	INT	NOT NULL	Product size ID
SizeCode	NVARCHAR(128)	NOT NULL	Product size code
SizeName	NVARCHAR(128)	NOT NULL	Product size description
StyleID	INT	NOT NULL	Style ID
StyleName	VARCHAR(64)	NULL	Style name
LineID	INT	NOT NULL	Production line ID
LineCode	NVARCHAR(128)	NULL	Line code
LineName	NVARCHAR(128)	NULL	Line name
SectionID	INT	NOT NULL	Section ID
SectionCode	NVARCHAR(128)	NULL	Section code
SectionName	NVARCHAR(128)	NULL	Section name
FactoryID	INT	NOT NULL	Company/factory ID
FactoryCode	NVARCHAR(128)	NULL	Company/factory code
FactoryName	NVARCHAR(128)	NULL	Company/factory name
BranchID	INT	NOT NULL	Branch ID
BranchCode	NVARCHAR(128)	NULL	Branch code
BranchName	NVARCHAR(128)	NULL	Branch name
CustomerCode	NVARCHAR(256)	NULL	Customer code
CustomerName	NVARCHAR(256)	NULL	Customer name
BuyMonth	VARCHAR(128)	NULL	Buying month
InspectedQuantity	INT	NULL	Total inspected quantity
FirstTimeClearQuantity	INT	NULL	Quantity cleared on first inspection
ClearAfterReworkQuantity	INT	NULL	Quantity cleared after rework
AtReworkQuantity	INT	NULL	Quantity currently at rework
AtRejectionQuantity	INT	NULL	Quantity rejected after inspection
InspectionDate	DATE	NULL	Date of inspection
HourSlot	INT	NULL	Hour slot (e.g., 6, 14, 23)

Relationships

• Foreign Keys:
  • None
• Referenced By:
  • Quality defect analysis reports
  • Worker performance analysis
  • Machine performance tracking
  • Operation efficiency reports

Indexes

• Primary Key: EndLineDailySummaryID
• Unique Constraint: UQ_EndLineDailySummary (InspectionDate, HourSlot, WorkOrderCode, StyleID, BuyMonth, CustomerCode, PurchaseOrderCode, ColorID, SizeID, FactoryID, BranchID, LineID, SectionID)
• Performance Indexes:
  • PK_EndLineDailySummary (Clustered)
  • UQ_EndLineDailySummary (Unique, Non-Clustered)

3. Data Identification & Grouping

• Unique Identifiers: EndLineDailySummaryID (Primary Key)
• Grouping Logic: Aggregated by order, style, color, size, customer, line, branch, factory, section, and hourly time slots

4. Data Lifecycle Management

Data Deletion Strategy

• Retention Policy: 3 years for detailed fault analysis, 7 years total for quality compliance
• Deletion Triggers: Automated quarterly archival process
• Archival Process: Move to EndLineDailySummary_Archive after 3 years
• Compliance Requirements: Quality management system requirements (ISO 9001, customer audits)

Data Insertion Policy

• Column-wise Insertion Rules:
  • EndLineDailySummaryID: Auto-generated (IDENTITY, PRIMARY KEY)
  • WorkOrderCode, PurchaseOrderCode, BuyMonth, InspectionDate, HourSlot, color fields, size fields, style fields, line fields, section fields, factory fields, branch fields, customer fields: Required grouping and descriptive keys, populated from master data joins.
  • InspectedQuantity, FirstTimeClearQuantity, ClearAfterReworkQuantity, AtReworkQuantity, AtRejectionQuantity: Aggregated counts per date and hour slot.
  • Descriptive fields (e.g. LineName, BranchName): Populated from master tables via joins in the procedure.
  • Insertion Procedures: usp_InsertEndLineDailySummary
  • Modification Scripts: Only quantity updates allowed via processing procedures

5. Data Modification Jobs

Data Read Operations (Selection)

• Job: EndLine Quality Reports
  • Purpose: Generate daily/hourly quality reports
  • Frequency: On-demand and scheduled
  • Output: Quality dashboards, management reports

DML Operations (Insert/Update/Delete)

• Job: Update EndLineDailySummary - Every 7 Minutes
  • Purpose: Aggregate end line quality inspection data into daily summaries
  • Data Sources: Quality.EndLineSession
  • Transformation: Hourly aggregation with quantity calculations
  • Error Handling: Failed aggregations logged with retry mechanism

6. Business Rules & Calculations

Quantity Calculations

• InspectedQuantity: Count of all pieces, grouped per date and hour slot.
• FirstTimeClearQuantity: Count of pieces which are cleared on the first scan, grouped per date and hour slot.
• ClearAfterReworkQuantity: Count of pieces which are cleared after rework, grouped per date and hour slot.
• AtReworkQuantity: Count of pieces which are at rework, grouped per date and hour slot.
• AtRejectionQuantity: Count of pieces which are rejected, grouped per date and hour slot.

Quality Metrics

• First Time Yield (FTY): FirstTimeClearQuantity / InspectedQuantity * 100
• Rework Rate: AtReworkQuantity / InspectedQuantity * 100
• Rejection Rate: AtRejectionQuantity / InspectedQuantity * 100
• Overall Pass Rate: (FirstTimeClearQuantity + ClearAfterReworkQuantity) / InspectedQuantity * 100

7. Data Sources & Dependencies

• Primary Source: Quality.EndLineSession (raw quality inspection data)
• Master Data Sources:
  • Essentials.Line (Line details)
  • Essentials.Section (Section details)
  • Essentials.Customer (Customer details)
  • Essentials.Color (Color details)
  • Essentials.Size (Size details)
  • Essentials.Branch (Branch details)
  • Essentials.Factory (Factory details)
  • Orders.PurchaseOrder (Purchase Order details)
  • Orders.WorkOrder (Work Order details)
  • Orders.Style (Style details)

8. Reporting Usage

• Views Using This Dataset:
  • None
• Reports:
  • To be added later