Production.WorkOrder - AdventureWorks

AdventureWorks

- AdventureWorks

Documentation	AdventureWorks
Schema	Production
Name	WorkOrder
Module	Manufacturing

Manufacturing work orders.

Columns

		Name	Data type	Attributes	References	Description
	1	WorkOrderID	int	Identity		Primary key for WorkOrder records.
Primary key for WorkOrder records. Identity
	2	ProductID	int		Production.Product	Product identification number. Foreign key to Product.ProductID.
Product identification number. Foreign key to Product.ProductID. References: Production.Product
	3	OrderQty	int			Product quantity to build.
Product quantity to build.
	4	StockedQty	int	Computed: isnull([OrderQty]-[ScrappedQty],(0))		Quantity built and put in inventory.
Quantity built and put in inventory. Computed: isnull([OrderQty]-[ScrappedQty],(0))
	5	ScrappedQty	smallint			Quantity that failed inspection.
Quantity that failed inspection.
	6	StartDate	datetime			Work order start date.
Work order start date.
	7	EndDate	datetime			Work order end date.
Work order end date.
	8	DueDate	datetime			Work order due date.
Work order due date.
	9	ScrapReasonID	smallint		Production.ScrapReason	Reason for inspection failure.
Reason for inspection failure. References: Production.ScrapReason
	10	ModifiedDate	datetime	Default: getdate()		Date and time the record was last updated.
Date and time the record was last updated. Default: getdate()

Relations

Foreign table	Primary table	Join	Title / Name / Description
Production.WorkOrder	Production.Product	Production.WorkOrder.ProductID = Production.Product.ProductID	FK_WorkOrder_Product_ProductID Foreign key constraint referencing Product.ProductID.
Production.WorkOrder.ProductID = Production.Product.ProductID Name: FK_WorkOrder_Product_ProductID Foreign key constraint referencing Product.ProductID.
Production.WorkOrder	Production.ScrapReason	Production.WorkOrder.ScrapReasonID = Production.ScrapReason.ScrapReasonID	FK_WorkOrder_ScrapReason_ScrapReasonID Foreign key constraint referencing ScrapReason.ScrapReasonID.
Production.WorkOrder.ScrapReasonID = Production.ScrapReason.ScrapReasonID Name: FK_WorkOrder_ScrapReason_ScrapReasonID Foreign key constraint referencing ScrapReason.ScrapReasonID.
Production.WorkOrderRouting	Production.WorkOrder	Production.WorkOrderRouting.WorkOrderID = Production.WorkOrder.WorkOrderID	FK_WorkOrderRouting_WorkOrder_WorkOrderID Foreign key constraint referencing WorkOrder.WorkOrderID.
Production.WorkOrderRouting.WorkOrderID = Production.WorkOrder.WorkOrderID Name: FK_WorkOrderRouting_WorkOrder_WorkOrderID Foreign key constraint referencing WorkOrder.WorkOrderID.

Unique keys

	Key name	Columns	Description
	PK_WorkOrder_WorkOrderID	WorkOrderID	Primary key (clustered) constraint
Primary key (clustered) constraint

Triggers

	Key name	When	Description
	iWorkOrder	After Insert	AFTER INSERT trigger that inserts a row in the TransactionHistory table.
CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder] AFTER INSERT AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY INSERT INTO [Production].[TransactionHistory]( [ProductID] ,[ReferenceOrderID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[WorkOrderID] ,'W' ,GETDATE() ,inserted.[OrderQty] ,0 FROM inserted; END TRY BEGIN CATCH EXECUTE [uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [uspLogError]; END CATCH; END;
AFTER INSERT trigger that inserts a row in the TransactionHistory table.
CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder] AFTER INSERT AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY INSERT INTO [Production].[TransactionHistory]( [ProductID] ,[ReferenceOrderID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[WorkOrderID] ,'W' ,GETDATE() ,inserted.[OrderQty] ,0 FROM inserted; END TRY BEGIN CATCH EXECUTE [uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [uspLogError]; END CATCH; END;
	uWorkOrder	After Update	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.
CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder] AFTER UPDATE AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY IF UPDATE([ProductID]) OR UPDATE([OrderQty]) BEGIN INSERT INTO [Production].[TransactionHistory]( [ProductID] ,[ReferenceOrderID] ,[TransactionType] ,[TransactionDate] ,[Quantity]) SELECT inserted.[ProductID] ,inserted.[WorkOrderID] ,'W' ,GETDATE() ,inserted.[OrderQty] FROM inserted; END; END TRY BEGIN CATCH EXECUTE [uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [uspLogError]; END CATCH; END;
AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.
CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder] AFTER UPDATE AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY IF UPDATE([ProductID]) OR UPDATE([OrderQty]) BEGIN INSERT INTO [Production].[TransactionHistory]( [ProductID] ,[ReferenceOrderID] ,[TransactionType] ,[TransactionDate] ,[Quantity]) SELECT inserted.[ProductID] ,inserted.[WorkOrderID] ,'W' ,GETDATE() ,inserted.[OrderQty] FROM inserted; END; END TRY BEGIN CATCH EXECUTE [uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [uspLogError]; END CATCH; END;

Uses

Name
Production.WorkOrder
Production.WorkOrder
Production.Product
Production.ScrapReason
Production.iWorkOrder
Production.TransactionHistory
dbo.uspLogError
dbo.ErrorLog
dbo.uspPrintError
dbo.uspPrintError
Production.uWorkOrder
Production.TransactionHistory
dbo.uspLogError
dbo.ErrorLog
dbo.uspPrintError
dbo.uspPrintError

Used by

Name
Production.WorkOrder
Production.WorkOrder
Production.WorkOrderRouting

Exported: 2019-02-04 23:13, Last imported: 2018-03-07 11:56