Audit Form Report

1. Dataset Overview

• Dataset Name: AuditFormDataSet
• Description: This dataset captures inline quality audit sessions conducted on each machine of a production line. It tracks defects found on bundles as they pass through machine operations.
• Owner/Maintainer: Manufacturing Reports Team / Production Analytics

2. Schema Definition

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

Table Structure

Column Name	Data Type	Constraints	Description
AuditFormSessionID	INT	NOT NULL	Unique identifier for the audit form session
WorkerID	INT	NOT NULL	Worker identifier
WorkerCode	NVARCHAR(64)	NULL	Worker code
WorkerDescription	NVARCHAR(64)	NULL	Worker description
WorkerType	NVARCHAR(64)	NULL	Classification of worker (e.g. Operator, Helper, Supervisor)
MachineID	INT	NOT NULL	Machine identifier
MachineCode	NVARCHAR(64)	NULL	Machine code
MachineDescription	NVARCHAR(64)	NULL	Machine description
OperationID	INT	NOT NULL	Operation identifier
OperationCode	NVARCHAR(64)	NULL	Operation code
OperationDescription	NVARCHAR(64)	NULL	Operation description
OperationType	NVARCHAR(64)	NULL	Category of sewing/assembly operation (e.g. Stitch, Press, Cut)
LineID	INT	NOT NULL	Production line identifier
LineCode	NVARCHAR(64)	NULL	Production line code
LineDescription	NVARCHAR(64)	NULL	Production line description
SectionID	INT	NOT NULL	Section identifier
SectionCode	NVARCHAR(64)	NULL	Section code
SectionDescription	NVARCHAR(64)	NULL	Section description
BundleID	INT	NOT NULL	Unique identifier for the bundle of cut pieces being processed
BundleCode	VARCHAR(64)	NULL	Human-readable tracking code assigned to the bundle
CutJobCode	VARCHAR(32)	NULL	Reference code for the cutting job that produced this bundle
PurchaseOrderCode	VARCHAR(100)	NULL	Buyer Purchase order number
ProductionStatus	VARCHAR(64)	NULL	Current status of the production order
ColorCode	NVARCHAR(64)	NULL	Color code
ColorName	NVARCHAR(64)	NULL	Color name
SizeCode	NVARCHAR(64)	NULL	Size code
SizeName	NVARCHAR(64)	NULL	Size name
WorkOrderCode	VARCHAR(100)	NULL	Internal production work order number
StyleName	VARCHAR(64)	NULL	Name or reference of the garment style
MachineRound	INT	NOT NULL	Inspection round number for this machine
FollowUp	INT	NOT NULL	Flag indicating whether this record requires a follow-up action
DefectedPieces	INT	NOT NULL	Total count of defective pieces
RoundColor	VARCHAR(6)	NOT NULL	Color assigned to this inspection round (RED/YELLOW/GREEN)
CreatedBy	VARCHAR(36)	NOT NULL	UUID of the user who created this record
USERNAME	NVARCHAR(255)	NULL	Display name of the user who created this record
UpdatedBy	VARCHAR(36)	NULL	UUID of the user who last updated this record
ScanningTime	DATETIME	NULL	Timestamp when the bundle was scanned
UpdatedAt	DATETIME	NULL	Timestamp of the most recent update to this record
CreatedAtDate	DATE	NULL	Date portion of when this audit record was created
FaultID	INT	NULL	Unique identifier for the fault recorded
FaultCode	NVARCHAR(64)	NULL	Short code identifying the fault
FaultDescription	NVARCHAR(256)	NULL	Description of the fault observed
FaultClassificationName	NVARCHAR(50)	NULL	Grouping category of the fault
FaultCount	INT	NOT NULL	Number of times this specific fault was observed
FaultSeverityLevel	NVARCHAR(50)	NULL	Severity rating of the fault (e.g. Critical, Major, Minor)
DefectTime	DATETIME	NULL	Timestamp when the specific defect was recorded during the audit

Relationships

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

Indexes

• Primary Key:

  No primary key of its own. However it fetches AuditFormSessionID from Quality.AuditFormSession. There is no insertion in this dataset. It gets deleted and refreshed every day so AuditFormSessionID is always a unique identifier

• Performance Indexes: None

3. 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 AuditFormSession_Archive after 3 years
• Compliance Requirements: Quality management system requirements (ISO 9001, customer audits)

Data Insertion Policy

• Column-wise Insertion Rules:
  • All columns populated from master tables via joins in the procedure.
• Insertion Procedures: [dbo].Sp_DataSetQuery_Quality_AuditFormReport

4. 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

• Job: Update AuditFormDataSet - Every 30 Minutes
  • Purpose: Refresh dbo.AuditFormDataSet from Quality.AuditFormSession data
  • Data Sources: Quality.AuditFormSession
  • Error Handling: None

5. Data Sources & Dependencies

• Primary Source: Quality.AuditFormSession (raw quality inspection data)
• Master Data Sources:
  • Essentials.Operation (Operation details)
  • Essentials.Machine (Machine details)
  • Essentials.Worker (Worker details)
  • Essentials.Line (Line details)
  • Essentials.Section (Section details)
  • Essentials.Color (Color details)
  • Essentials.Size (Size details)
  • Cutting.Bundle (Bundle details)
  • Cutting.CutJob (Cut job details)
  • Essentials.Fault (Fault details)
  • Essentials.FaultClassification (Fault Classification details)
  • Essentials.FaultSeverity (Fault Severity details)
  • Orders.PurchaseOrder (Purchase Order details)
  • Orders.WorkOrder (Work Order details)
  • Orders.Style (Style details)
  • auth.USER_ENTITY (User details)

6. Reporting Usage

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