Production.WorkOrder (table)

wikibot

Table	Production.WorkOrder
Description	Manufacturing work orders.

Columns

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

Primary Key

Primary Key	Columns
PK_WorkOrder_WorkOrderID	WorkOrderID

Indexes

Index	Type	Columns
IX_WorkOrder_ScrapReasonID		ScrapReasonID
IX_WorkOrder_ProductID		ProductID

Check Constraints

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

Foreign Keys

Relation	Column	Referenced Column
Production.Product	ProductID	ProductID
Production.ScrapReason	ScrapReasonID	ScrapReasonID

Detail Tables

Detail Table	Column	Referencing Column
Production.WorkOrderRouting	WorkOrderID	WorkOrderID

Triggers

Triggers	Type
iWorkOrder	ON INSERT
uWorkOrder	ON UPDATE

Trigger iWorkOrder

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 [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Trigger uWorkOrder

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 [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

References

Dependency Type	Object Type	Referenced Object	Child Type	Child Object
Schema	Schema	Production
Insert	Table	Production.TransactionHistory	Trigger	iWorkOrder
Insert	Table	Production.TransactionHistory	Trigger	uWorkOrder
Execute	Procedure	dbo.uspLogError	Trigger	iWorkOrder
Execute	Procedure	dbo.uspLogError	Trigger	uWorkOrder
Execute	Procedure	dbo.uspPrintError	Trigger	uWorkOrder
Execute	Procedure	dbo.uspPrintError	Trigger	iWorkOrder

automatically generated

Table	Production.WorkOrder
Description	Manufacturing work orders.

Column	Data Type	Nullable	Default	Description / PK / Index
WorkOrderID	int	not null		Primary key for WorkOrder records. PK_WorkOrder_WorkOrderID
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID. IX_WorkOrder_ProductID
OrderQty	int	not null		Product quantity to build.
StockedQty				Quantity built and put in inventory.
ScrappedQty	smallint	not null		Quantity that failed inspection.
StartDate	datetime	not null		Work order start date.
EndDate	datetime	null		Work order end date.
DueDate	datetime	not null		Work order due date.
ScrapReasonID	smallint	null		Reason for inspection failure. IX_WorkOrder_ScrapReasonID
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Relation	Column	Referenced Column
Production.Product	ProductID	ProductID
Production.ScrapReason	ScrapReasonID	ScrapReasonID

Detail Table	Column	Referencing Column
Production.WorkOrderRouting	WorkOrderID	WorkOrderID

Triggers	Type
iWorkOrder	ON INSERT
uWorkOrder	ON UPDATE

Dependency Type	Object Type	Referenced Object	Child Type	Child Object
Schema	Schema	Production
Insert	Table	Production.TransactionHistory	Trigger	iWorkOrder
Insert	Table	Production.TransactionHistory	Trigger	uWorkOrder
Execute	Procedure	dbo.uspLogError	Trigger	iWorkOrder
Execute	Procedure	dbo.uspLogError	Trigger	uWorkOrder
Execute	Procedure	dbo.uspPrintError	Trigger	uWorkOrder
Execute	Procedure	dbo.uspPrintError	Trigger	iWorkOrder