Production WorkOrder (table)

wikibot

TableProduction.WorkOrder
DescriptionManufacturing work orders.

Columns

ColumnData TypeNullableDefaultDescription
WorkOrderIDintnot null Primary key for WorkOrder records.
ProductIDintnot null Product identification number. Foreign key to Product.ProductID.
OrderQtyintnot null Product quantity to build.
StockedQty   Quantity built and put in inventory.
ScrappedQtysmallintnot null Quantity that failed inspection.
StartDatedatetimenot null Work order start date.
EndDatedatetimenull Work order end date.
DueDatedatetimenot null Work order due date.
ScrapReasonIDsmallintnull Reason for inspection failure.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Primary Key

Primary KeyColumns
PK_WorkOrder_WorkOrderIDWorkOrderID

Indexes

IndexTypeColumns
IX_WorkOrder_ProductID ProductID
IX_WorkOrder_ScrapReasonID ScrapReasonID

Check Constraints

Check ConstraintExpressionDescription
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

RelationColumnReferenced Column
Production.ProductProductIDProductID
Production.ScrapReasonScrapReasonIDScrapReasonID

Detail Tables

Detail TableColumnReferencing Column
Production.WorkOrderRoutingWorkOrderIDWorkOrderID

Triggers

TriggerType
iWorkOrderON INSERT
uWorkOrderON 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 TypeObject TypeReferenced ObjectChild TypeChild Object
SchemaSchemaProduction  
InsertTableProduction.TransactionHistoryTriggeriWorkOrder
InsertTableProduction.TransactionHistoryTriggeruWorkOrder
ExecuteProceduredbo.uspLogErrorTriggeriWorkOrder
ExecuteProceduredbo.uspLogErrorTriggeruWorkOrder
ExecuteProceduredbo.uspPrintErrorTriggeriWorkOrder
ExecuteProceduredbo.uspPrintErrorTriggeruWorkOrder