Production.WorkOrder Table

Description

Manufacturing work orders.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	72591
Data Space Used	4,224.00 KB
Index Space Used	2,088.00 KB

Columns

Column Name	Description	Datatype	Length	Default	Formula
WorkOrderID	Primary key for WorkOrder records.	Integer	4
ProductID	Product identification number. Foreign key to Product.ProductID.	Integer	4
OrderQty	Product quantity to build.	Integer	4
StockedQty	Quantity built and put in inventory.	Integer	4		(isnull([OrderQty]-[ScrappedQty],(0)))
ScrappedQty	Quantity that failed inspection.	SmallInt	2
StartDate	Work order start date.	DBTimeStamp	4
EndDate	Work order end date.	DBTimeStamp	4
DueDate	Work order due date.	DBTimeStamp	4
ScrapReasonID	Reason for inspection failure.	SmallInt	2
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
IX_WorkOrder_ProductID	Nonclustered index.
IX_WorkOrder_ScrapReasonID	Nonclustered index.
PK_WorkOrder_WorkOrderID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_WorkOrder_EndDate	Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_WorkOrder_OrderQty	Check constraint [OrderQty] > (0)	([OrderQty]>(0))
CK_WorkOrder_ScrappedQty	Check constraint [ScrappedQty] >= (0)	([ScrappedQty]>=(0))

Triggers

Trigger	Description
iWorkOrder	AFTER INSERT trigger that inserts a row in the TransactionHistory table.
uWorkOrder	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.

Relationships

Relationship	Description
FK_WorkOrder_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_WorkOrder_ScrapReason_ScrapReasonID	Foreign key constraint referencing ScrapReason.ScrapReasonID.
FK_WorkOrderRouting_WorkOrder_WorkOrderID	Foreign key constraint referencing WorkOrder.WorkOrderID.

Objects that depend on Production.WorkOrder

Database Object	Object Type	Description	Dep Level
iWorkOrder	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table.	1
uWorkOrder	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.	1
Production.WorkOrderRouting	Table	Work order details.	1

Objects that Production.WorkOrder depends on

Database Object	Object Type	Description	Dep Level
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	2
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCategory	Table	High-level product categorization.	3
Production.ProductModel	Table	Product model classification.	2
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	2
Production.ScrapReason	Table	Manufacturing failure reasons lookup table.	1
Production.TransactionHistory	Table	Record of each purchase order, sales order, or work order transaction year to date.	1
Production.UnitMeasure	Table	Unit of measure lookup table.	2
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	1
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[WorkOrder](
    [WorkOrderID] [int] IDENTITY(1,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [OrderQty] [int] NOT NULL,
    [StockedQty]  AS (isnull([OrderQty]-[ScrappedQty],(0))),
    [ScrappedQty] [smallint] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [DueDate] [datetime] NOT NULL,
    [ScrapReasonID] [smallint] NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED 
(
    [WorkOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Production].[WorkOrder] ADD  CONSTRAINT [DF_WorkOrder_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Production].[WorkOrder]  WITH CHECK ADD  CONSTRAINT [FK_WorkOrder_Product_ProductID] FOREIGN KEY([ProductID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Production].[WorkOrder] CHECK CONSTRAINT [FK_WorkOrder_Product_ProductID]
ALTER TABLE [Production].[WorkOrder]  WITH CHECK ADD  CONSTRAINT [FK_WorkOrder_ScrapReason_ScrapReasonID] FOREIGN KEY([ScrapReasonID])
REFERENCES [Production].[ScrapReason] ([ScrapReasonID])
ALTER TABLE [Production].[WorkOrder] CHECK CONSTRAINT [FK_WorkOrder_ScrapReason_ScrapReasonID]
ALTER TABLE [Production].[WorkOrder]  WITH CHECK ADD  CONSTRAINT [CK_WorkOrder_EndDate] CHECK  (([EndDate]>=[StartDate] OR [EndDate] IS NULL))
ALTER TABLE [Production].[WorkOrder] CHECK CONSTRAINT [CK_WorkOrder_EndDate]
ALTER TABLE [Production].[WorkOrder]  WITH CHECK ADD  CONSTRAINT [CK_WorkOrder_OrderQty] CHECK  (([OrderQty]>(0)))
ALTER TABLE [Production].[WorkOrder] CHECK CONSTRAINT [CK_WorkOrder_OrderQty]
ALTER TABLE [Production].[WorkOrder]  WITH CHECK ADD  CONSTRAINT [CK_WorkOrder_ScrappedQty] CHECK  (([ScrappedQty]>=(0)))
ALTER TABLE [Production].[WorkOrder] CHECK CONSTRAINT [CK_WorkOrder_ScrappedQty]

Related Objects