Production.WorkOrderRouting Table

Description

Work order details.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	67131
Data Space Used	5,568.00 KB
Index Space Used	1,288.00 KB

Columns

Column Name	Description	Datatype	Length	Default
WorkOrderID	Primary key. Foreign key to WorkOrder.WorkOrderID.	Integer	4
ProductID	Primary key. Foreign key to Product.ProductID.	Integer	4
OperationSequence	Primary key. Indicates the manufacturing process sequence.	SmallInt	2
LocationID	Manufacturing location where the part is processed. Foreign key to Location.LocationID.	SmallInt	2
ScheduledStartDate	Planned manufacturing start date.	DBTimeStamp	4
ScheduledEndDate	Planned manufacturing end date.	DBTimeStamp	4
ActualStartDate	Actual start date.	DBTimeStamp	4
ActualEndDate	Actual end date.	DBTimeStamp	4
ActualResourceHrs	Number of manufacturing hours used.	Numeric	9 (9,4)
PlannedCost	Estimated manufacturing cost.	Currency	8
ActualCost	Actual manufacturing cost.	Currency	8
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
IX_WorkOrderRouting_ProductID	Nonclustered index.
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_WorkOrderRouting_ActualCost	Check constraint [ActualCost] > (0.00)	([ActualCost]>(0.00))
CK_WorkOrderRouting_ActualEndDate	Check constraint [ActualEndDate] >= [ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL	([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL)
CK_WorkOrderRouting_ActualResourceHrs	Check constraint [ActualResourceHrs] >= (0.0000)	([ActualResourceHrs]>=(0.0000))
CK_WorkOrderRouting_PlannedCost	Check constraint [PlannedCost] > (0.00)	([PlannedCost]>(0.00))
CK_WorkOrderRouting_ScheduledEndDate	Check constraint [ScheduledEndDate] >= [ScheduledStartDate]	([ScheduledEndDate]>=[ScheduledStartDate])

Relationships

Relationship	Description
FK_WorkOrderRouting_Location_LocationID	Foreign key constraint referencing Location.LocationID.
FK_WorkOrderRouting_WorkOrder_WorkOrderID	Foreign key constraint referencing WorkOrder.WorkOrderID.

Objects that Production.WorkOrderRouting 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.	3
dbo.Flag	User Defined Data Type		3
Production.Location	Table	Product inventory and manufacturing locations.	1
Production.Product	Table	Products sold or used in the manfacturing of sold products.	2
Production.ProductCategory	Table	High-level product categorization.	4
Production.ProductModel	Table	Product model classification.	3
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	3
Production.ScrapReason	Table	Manufacturing failure reasons lookup table.	2
Production.TransactionHistory	Table	Record of each purchase order, sales order, or work order transaction year to date.	2
Production.UnitMeasure	Table	Unit of measure lookup table.	3
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.	2
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.	2
Production.WorkOrder	Table	Manufacturing work orders.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[WorkOrderRouting](
    [WorkOrderID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [OperationSequence] [smallint] NOT NULL,
    [LocationID] [smallint] NOT NULL,
    [ScheduledStartDate] [datetime] NOT NULL,
    [ScheduledEndDate] [datetime] NOT NULL,
    [ActualStartDate] [datetime] NULL,
    [ActualEndDate] [datetime] NULL,
    [ActualResourceHrs] [decimal](9, 4) NULL,
    [PlannedCost] [money] NOT NULL,
    [ActualCost] [money] NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence] PRIMARY KEY CLUSTERED 
(
    [WorkOrderID] ASC,
    [ProductID] ASC,
    [OperationSequence] 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].[WorkOrderRouting] ADD  CONSTRAINT [DF_WorkOrderRouting_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Production].[WorkOrderRouting]  WITH CHECK ADD  CONSTRAINT [FK_WorkOrderRouting_Location_LocationID] FOREIGN KEY([LocationID])
REFERENCES [Production].[Location] ([LocationID])
ALTER TABLE [Production].[WorkOrderRouting] CHECK CONSTRAINT [FK_WorkOrderRouting_Location_LocationID]
ALTER TABLE [Production].[WorkOrderRouting]  WITH CHECK ADD  CONSTRAINT [FK_WorkOrderRouting_WorkOrder_WorkOrderID] FOREIGN KEY([WorkOrderID])
REFERENCES [Production].[WorkOrder] ([WorkOrderID])
ALTER TABLE [Production].[WorkOrderRouting] CHECK CONSTRAINT [FK_WorkOrderRouting_WorkOrder_WorkOrderID]
ALTER TABLE [Production].[WorkOrderRouting]  WITH CHECK ADD  CONSTRAINT [CK_WorkOrderRouting_ActualCost] CHECK  (([ActualCost]>(0.00)))
ALTER TABLE [Production].[WorkOrderRouting] CHECK CONSTRAINT [CK_WorkOrderRouting_ActualCost]
ALTER TABLE [Production].[WorkOrderRouting]  WITH CHECK ADD  CONSTRAINT [CK_WorkOrderRouting_ActualEndDate] CHECK  (([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL))
ALTER TABLE [Production].[WorkOrderRouting] CHECK CONSTRAINT [CK_WorkOrderRouting_ActualEndDate]
ALTER TABLE [Production].[WorkOrderRouting]  WITH CHECK ADD  CONSTRAINT [CK_WorkOrderRouting_ActualResourceHrs] CHECK  (([ActualResourceHrs]>=(0.0000)))
ALTER TABLE [Production].[WorkOrderRouting] CHECK CONSTRAINT [CK_WorkOrderRouting_ActualResourceHrs]
ALTER TABLE [Production].[WorkOrderRouting]  WITH CHECK ADD  CONSTRAINT [CK_WorkOrderRouting_PlannedCost] CHECK  (([PlannedCost]>(0.00)))
ALTER TABLE [Production].[WorkOrderRouting] CHECK CONSTRAINT [CK_WorkOrderRouting_PlannedCost]
ALTER TABLE [Production].[WorkOrderRouting]  WITH CHECK ADD  CONSTRAINT [CK_WorkOrderRouting_ScheduledEndDate] CHECK  (([ScheduledEndDate]>=[ScheduledStartDate]))
ALTER TABLE [Production].[WorkOrderRouting] CHECK CONSTRAINT [CK_WorkOrderRouting_ScheduledEndDate]

Related Objects