PO Ex-Factory Split Status

1. Dataset Overview

• Dataset Name: POExFactorySplitStatus
• Description: This table stores split status for purchase orders by ex-factory date, consolidating production, packing, and quality metrics for each PO, style, color, factory, and branch. It enables tracking of quantities and key dates across the supply chain.
• Owner/Maintainer: Quality Assurance Team / Manufacturing Analytics

2. Schema Definition

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

Table Structure

Column Name	Data Type	Constraints	Description
POExFactorySplitStatusID	INT	PRIMARY KEY, IDENTITY(1,1), NOT NULL	A unique serial number auto-assigned to every row
WorkOrderCode	VARCHAR(100)	NOT NULL	The factory’s internal order number
CustomerCode	NVARCHAR(256)	NOT NULL	Code identifying the buyer
CustomerName	NVARCHAR(256)	NOT NULL	Full name of the buyer
StyleName	VARCHAR(64)	NOT NULL	The garment design/style in the order
BuyMonth	VARCHAR(128)	NOT NULL	Month the buyer planned to receive this order
PurchaseOrderCode	VARCHAR(100)	NOT NULL	The buyer’s official order number
FactoryName	NVARCHAR(128)	NOT NULL	Which factory is producing this split
BranchName	NVARCHAR(128)	NOT NULL	Which branch of the factory group manages this
ColorCode	NVARCHAR(128)	NOT NULL	Short code for the color
ColorName	NVARCHAR(128)	NOT NULL	Full color name
StoreDetail	NVARCHAR(510)	NULL	Destination this shipment is going to
ShipType	NVARCHAR(20)	NULL	How the goods are being shipped
OrderQuantity	INT	NOT NULL	The total pieces the buyer ordered for this split — the target number
PackedQty	INT	NOT NULL	Total pieces that have been packed into boxes and are ready for shipment
TotalCutQuantity	INT	NOT NULL, DEFAULT(0)	Total pieces that have been physically cut from fabric so far
TotalInputQuantity	INT	NOT NULL, DEFAULT(0)	Total pieces that have entered the production process
TotalInductionQuantity	INT	NOT NULL, DEFAULT(0)	Total pieces scanned at the first sewing machine
TotalSewingLastOperationQuantity	INT	NOT NULL, DEFAULT(0)	Total pieces that have completed all sewing steps
TotalEndLineQualityPassedQuantity	INT	NOT NULL, DEFAULT(0)	Total pieces that passed quality check at the end of the sewing line
TotalEndLineQualityRejectedQuantity	INT	NOT NULL, DEFAULT(0)	Total pieces that failed quality check at the end of the sewing line
TotalFinishingQualityPassQuantity	INT	NOT NULL, DEFAULT(0)	Total pieces that passed the final finishing quality inspection
TotalFinishingQualityRejectionQuantity	INT	NOT NULL, DEFAULT(0)	Total pieces that failed finishing quality inspection
TotalRecordedSendDownQuantity	INT	NOT NULL, DEFAULT(0)	Total cut pieces officially handed over to the sewing department
TotalRecordedLostQuantity	INT	NOT NULL, DEFAULT(0)	Total pieces that have been lost during production
ExFactoryDate	DATE	NULL	The date when goods physically leave the factory gate
HandOverDate	DATE	NULL	The date when the finished & packed goods were handed over to the buyer’s QC team
SendDownDate	DATE	NULL	The date when cut pieces were first sent down to the sewing department

Relationships

• Foreign Keys:
  • No
• Referenced By:
  • Stored Procedure : uspSyncPOExFactorySplitStatus

Indexes

• Primary Key: POExFactorySplitStatusID (clustered, identity)
• Performance Indexes:
  • PK_POExFactorySplitStatus (clustered, unique)
• Default Constraints:
  • All quantity fields default to 0

3. Data Identification & Grouping

• Unique Identifiers: POExFactorySplitStatusID (primary)
• Grouping Logic: Aggregated by PO, style, color, factory, branch, and ex-factory date

4. Data Lifecycle Management

Data Deletion Strategy

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

Data Insertion Policy

• Column-wise Insertion Rules:
  • POExFactorySplitStatusID: Auto-generated (IDENTITY)
  • Quantity fields: Default to 0, updated via aggregation
  • Descriptive fields: Populated from master data joins
• Insertion Procedures: uspSyncPOExFactorySplitStatus

5. Data Modification Jobs

Data Read Operations (Selection)

• Job: Job_To_Sync_Daily_WorkOrder_Status_Every_11_Minutes
  • Purpose: Populate data in [Reports].[POExFactorySplitStatus]
  • 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 PO split status data
  • Data Sources: Reports.PackingPlan, Reports.DailyWorkOrderStatus
  • Transformation: Aggregation and enrichment via joins
  • Error Handling: Transactional integrity, error logging

6. Business Rules & Calculations

Quantity Calculations

• OrderQuantity, PackedQty, TotalCutQuantity, etc.: Aggregated from source tables

Quality Metrics

• Pass/Reject Rates: Calculated in downstream reports

7. Data Sources & Dependencies

• Primary Source: Reports.PackingPlan, Reports.DailyWorkOrderStatus
• Master Data Sources:
  • Essentials.Customer, Essentials.Factory, Essentials.Branch, Essentials.Color

8. Reporting Usage

• Views Using This Dataset:
  • No views using this dataset
• Reports:
  • No reports currently using this dataset