Difference between revisions of "Production.WorkOrder (table)"

From dbscript Online Help
Jump to: navigation, search
 
Line 98: Line 98:
 
| '''Columns
 
| '''Columns
  
 +
|- valign="top"
 +
| IX_WorkOrder_ProductID
 +
|
 +
| ProductID
 
|- valign="top"
 
|- valign="top"
 
| IX_WorkOrder_ScrapReasonID
 
| IX_WorkOrder_ScrapReasonID
 
|  
 
|  
 
| ScrapReasonID
 
| ScrapReasonID
−
|- valign="top"
 
−
| IX_WorkOrder_ProductID
 
−
|
 
−
| ProductID
 
 
|}
 
|}
  
Line 315: Line 315:
  
 
| Trigger
 
| Trigger
−
| uWorkOrder
+
| iWorkOrder
 
|- valign="top"
 
|- valign="top"
 
| Execute
 
| Execute
Line 322: Line 322:
  
 
| Trigger
 
| Trigger
−
| iWorkOrder
+
| uWorkOrder
 
|}
 
|}
  

Latest revision as of 09:55, 27 August 2011

wikibot[edit]

Table Production.WorkOrder
Description Manufacturing work orders.

Columns[edit]

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[edit]

Primary Key Columns
PK_WorkOrder_WorkOrderID WorkOrderID

Indexes[edit]

Index Type Columns
IX_WorkOrder_ProductID ProductID
IX_WorkOrder_ScrapReasonID ScrapReasonID

Check Constraints[edit]

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[edit]

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

Detail Tables[edit]

Detail Table Column Referencing Column
Production.WorkOrderRouting WorkOrderID WorkOrderID

Triggers[edit]

Trigger Type
iWorkOrder ON INSERT
uWorkOrder ON UPDATE

Trigger iWorkOrder[edit]

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[edit]

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[edit]

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 iWorkOrder
Execute Procedure dbo.uspPrintError Trigger uWorkOrder


automatically generated[edit]

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
Retrieved from "http://dbshelp.devio.at/index.php?title=Production.WorkOrder_(table)&oldid=2937"