Properties

Owner:	Production
Creation Date:	04/26/2006
Located On:	PRIMARY
Data Size KB:	4184
Index Size KB:	1704
Rows:	72591
Description:	Manufacturing work orders.

Columns

		Name	Data Type	Length	NULL	Default	IsIdentity	IsGUID	Description
		WorkOrderID	int	4					Primary key for WorkOrder records.
		ProductID	int	4					Product identification number. Foreign key to Product.ProductID.
		OrderQty	int	4					Product quantity to build.
		StockedQty	int	4					Quantity built and put in inventory.
		ScrappedQty	smallint	2					Quantity that failed inspection.
		StartDate	datetime	8					Work order start date.
		EndDate	datetime	8					Work order end date.
		DueDate	datetime	8					Work order due date.
		ScrapReasonID	smallint	2					Reason for inspection failure.
		ModifiedDate	datetime	8		(getdate())			Date and time the record was last updated.

Total: 10 column(s)

Identity column

	Name	Seed	Increment	Not for replication
	WorkOrderID	1	1

Indexes

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

Total: 3 index(es)

Triggers

	Name	Owner	Instead Of	Disabled	Table/View	Description
	iWorkOrder	Production			Production.WorkOrder	AFTER INSERT trigger that inserts a row in the TransactionHistory table.
	uWorkOrder	Production			Production.WorkOrder	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.

Total: 2 trigger(s)

Check Constraints

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

Total: 3 constraint(s)

Referencing Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	Production.WorkOrderRouting	FK_WorkOrderRouting_WorkOrder_WorkOrderID	PK_WorkOrder_WorkOrderID

Total: 1 table(s)

Referenced Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	Production.Product	FK_WorkOrder_Product_ProductID	PK_Product_ProductID
	Production.ScrapReason	FK_WorkOrder_ScrapReason_ScrapReasonID	PK_ScrapReason_ScrapReasonID

Total: 2 table(s)

Objects that [Production].[WorkOrder] depends on

	Object Name	Owner	Object Type	Dep Level
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	ProductCategory	Production	Table	2
	ProductModel	Production	Table	2
	ScrapReason	Production	Table	2
	UnitMeasure	Production	Table	2
	ProductSubcategory	Production	Table	3
	Product	Production	Table	4

Total: 8 object(s)

Objects that depend on [Production].[WorkOrder]

	Object Name	Owner	Object Type	Dep Level
	WorkOrderRouting	Production	Table	1
	iWorkOrder	Production	Trigger	1
	uWorkOrder	Production	Trigger	1

Total: 3 object(s)

SQL

CREATE TABLE [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 [DF_WorkOrder_ModifiedDate] DEFAULT (getdate()),
    CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY  CLUSTERED
    (
        [WorkOrderID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_WorkOrder_Product_ProductID] FOREIGN KEY
    (
        [ProductID]
    ) REFERENCES [Product] (
        [ProductID]
    ),
    CONSTRAINT [FK_WorkOrder_ScrapReason_ScrapReasonID] FOREIGN KEY
    (
        [ScrapReasonID]
    ) REFERENCES [ScrapReason] (
        [ScrapReasonID]
    ),
    CONSTRAINT [CK_WorkOrder_EndDate] CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL),
    CONSTRAINT [CK_WorkOrder_OrderQty] CHECK ([OrderQty]>(0)),
    CONSTRAINT [CK_WorkOrder_ScrappedQty] CHECK ([ScrappedQty]>=(0))
) ON [PRIMARY]
GO

See Also

List of tables