Daily Work Order Status

1. Dataset Overview

• Dataset Name: DailyWorkOrderStatus
• Description: This table stores date-wise production status of apparel work orders at production-line level, broken down by customer, style, color, size, factory, branch, and sewing line.
• Owner/Maintainer: Manufacturing Systems Team / Production Data Engineering

2. Schema Definition

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

Table Structure

Column Name	Data Type	Constraints	Description
DailyWorkOrderStatusID	INT	Primary Key, NOT NULL	A unique serial number for each record in the table.
WorkOrderCode	VARCHAR(100)	NOT NULL	The factory’s own internal order number to track this production job.
CustomerCode	NVARCHAR(128)	NULL	A short code identifying the buyer
CustomerName	NVARCHAR(128)	NULL	The full name of the buyer
BuyMonth	VARCHAR(128)	NOT NULL	The month the buyer wants to receive this order
StyleName	VARCHAR(64)	NOT NULL	The name of the garment style
PurchaseOrderCode	VARCHAR(100)	NOT NULL	The buyer’s official order number
ColorCode	NVARCHAR(64)	NOT NULL	A short code for the garment color
ColorName	NVARCHAR(64)	NOT NULL	The full color name
SizeName	NVARCHAR(64)	NOT NULL	The size of the garment
FactoryCode	NVARCHAR(64)	NOT NULL	Code of factory manufacturing this order
FactoryName	NVARCHAR(64)	NOT NULL	Name of factory manufacturing this order
BranchCode	NVARCHAR(64)	NOT NULL	Code of branch of the factory group overseeing production.
BranchName	NVARCHAR(64)	NOT NULL	Name of branch of the factory group overseeing production.
LineID	INT	NOT NULL, FK	Id of sewing line inside the factory working on this order
LineCode	NVARCHAR(64)	NOT NULL	Code of sewing line inside the factory working on this order
LineName	NVARCHAR(64)	NOT NULL	Name of sewing line inside the factory working on this order
OrderQuantity	INT	NOT NULL	The total pieces the buyer ordered for this specific style, color, and size
SyncedQuantity	INT	NOT NULL	The quantity that has been received and confirmed from the buyer’s system
CardInitializedQuantity	INT	NOT NULL	The number of pieces for which production tracking cards have been created
CuttingOutQuantity	INT	NOT NULL	No. of pieces that have been cut and sent from cutting department toward sewing floor
InductionQuantity	INT	NOT NULL	Pieces that have been scanned on the very first sewing machine on the line
EndLineQualityPassedQuantity	INT	NOT NULL	Pieces that passed the quality check at the end of the sewing line
EndLineQualityRejectedQuantity	INT	NOT NULL	Pieces that failed the quality check at the end of the sewing line
SewingLastOperationQuantity	INT	NOT NULL	Pieces that have completed the very last sewing operation on the line
SendDownQuantity	INT	NOT NULL	No. of pieces that have been officially sent down to the sewing department
FinishingFirstOperationQuantity	INT	NOT NULL	Pieces that have entered the finishing department and started first finishing step
FinishingQualityPassQuantity	INT	NOT NULL	Pieces that passed the final finishing quality inspection
FinishingQualityRejectionQuantity	INT	NOT NULL	Pieces that failed the finishing quality inspection
FinishingLastOperationQuantity	INT	NOT NULL	Pieces that have completed all finishing operations
StatusDate	DATE	NOT NULL	The production date for which the quantities are reported
CreatedAt	DATETIME	NOT NULL	Timestamp when the record was created in the system
UpdatedAt	DATETIME	NOT NULL	Timestamp when the record was last updated

---

Relationships

• Primary Keys:
  • PK_DailyWorkOrderStatus
• Foreign Keys:
  • No
• Referenced By:
  • Stored Procedure : uspSyncDailyWorkOrderStatus

Indexes

• Primary Key: PK_DailyWorkOrderStatus (clustered, identity)
• Performance Indexes:
  • PK_DailyWorkOrderStatus (clustered, unique)
  • UQ_DailyWorkOrderStatus (non-clustered, unique)
• Default Constraints:
  • All event dates null if piece status doesn’t match.

3. Data Identification & Grouping

• Unique Identifiers:
  • PK_DailyWorkOrderStatus (clustered, unique)
  • UQ_DailyWorkOrderStatus (non-clustered, unique)
• Grouping Logic: Aggregated by WorkOrderCode, CustomerCode, CustomerName, BuyMonth, StyleName, PurchaseOrderCode, ColorCode, ColorName, SizeName, FactoryCode, FactoryName, BranchCode, BranchName, LineID, LineCode, LineName, StatusDate;

4. Data Lifecycle Management

Data Deletion Strategy (Standard, not done yet)

• Retention Policy: 3 years for reporting, 7 years for compliance
• Deletion Triggers: Automated quarterly archival process
• Archival Process: Move to DailyWorkOrderStatus_Archive after 3 years
• Compliance Requirements: Manufacturing and supply chain audit requirements

Data Insertion Policy

• Column-wise Insertion Rules:
  • DailyWorkOrderStatusID: Auto-generated (IDENTITY)
  • Date Columns: Default to NULL, updated via aggregation
  • Descriptive fields: Populated from master data joins
• Insertion Procedures: uspSyncDailyWorkOrderStatus

5. Data Modification Jobs

Data Read Operations (Selection)

• Job: Job_To_Sync_Daily_WorkOrder_Status_Every_11_Minutes
  • Purpose: Populate data in [Reports].[DailyWorkOrderStatus]
  • Frequency: Runs every day every 11 minutes

DML Operations (Insert/Update/Delete)

• Job: Job_To_Sync_Daily_WorkOrder_Status_Every_11_Minutes
  • Purpose: Aggregate and sync Daily work order status data
  • Data Sources: [Reports].[Piece], [Cutting].[Bundle], [Orders].[WorkOrderStyle], [Orders].[WorkOrder], [Orders].[Style], [Essentials].[Customer], [Orders].[PurchaseOrder], [Essentials].[Color], [Essentials].[Size], [Essentials].[Line], [Essentials].[Branch], [Essentials].[Factory]
  • Transformation: Aggregation and enrichment via joins
  • Error Handling: Transactional integrity, error logging

6. Business Rules & Calculations

Calculations

• SyncedQty, CardInitializedQty, CuttingOutQty, InductionQty, EndLineQualityPassedQty, EndLineQualityRejectedQty, SewingLastOperationQty, SendDownQty, FinishingFirstOperationQty, FinishingQualityPassQty, FinishingQualityRejectionQty, FinishingLastOperationQty : Calculated on the base of event dates grouped by bundles.

Quality Metrics

• Pass/Reject Rates: Calculated in downstream reports

7. Data Sources & Dependencies

• Primary Source: [Reports].[Piece], [Cutting].[Bundle], [Orders].[WorkOrderStyle], [Orders].[WorkOrder], [Orders].[Style]
• Master Data Sources:
  • [Essentials].[Customer], [Orders].[PurchaseOrder], [Essentials].[Color], [Essentials].[Size], [Essentials].[Line], [Essentials].[Branch], [Essentials].[Factory]

8. Reporting Usage

• Views Using This Dataset:
  • VW_WIP_InputDateWiseReport
  • VW_WIP_WorkOrderInPurOutPutQtyBalanceReport
  • VW_WIP_DailyWorkingInProgressInlineBalanceReport
  • Vw_Stitching_SewingInputReport
  • VW_WIP_LineWiseLineBalanceReport
• Reports:
  • N/A