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

From dbscript Online Help
Jump to: navigation, search
Line 12: Line 12:
  
  
 +
=== Columns ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 18: Line 19:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''Description / PK / Index
+
| '''Description
 
 
 
|- valign="top"
 
|- valign="top"
Line 25: Line 26:
 
| not null
 
| not null
 
|  
 
|  
−
| Primary key for WorkOrder records.<br />PK_WorkOrder_WorkOrderID
+
| Primary key for WorkOrder records.
 
|- valign="top"
 
|- valign="top"
 
| ProductID
 
| ProductID
Line 31: Line 32:
 
| not null
 
| not null
 
|  
 
|  
−
| Product identification number. Foreign key to Product.ProductID.<br />IX_WorkOrder_ProductID
+
| Product identification number. Foreign key to Product.ProductID.
 
|- valign="top"
 
|- valign="top"
 
| OrderQty
 
| OrderQty
Line 37: Line 38:
 
| not null
 
| not null
 
|  
 
|  
−
| Product quantity to build.<br />
+
| Product quantity to build.
 
|- valign="top"
 
|- valign="top"
 
| StockedQty
 
| StockedQty
Line 43: Line 44:
 
|  
 
|  
 
|  
 
|  
−
| Quantity built and put in inventory.<br />
+
| Quantity built and put in inventory.
 
|- valign="top"
 
|- valign="top"
 
| ScrappedQty
 
| ScrappedQty
Line 49: Line 50:
 
| not null
 
| not null
 
|  
 
|  
−
| Quantity that failed inspection.<br />
+
| Quantity that failed inspection.
 
|- valign="top"
 
|- valign="top"
 
| StartDate
 
| StartDate
Line 55: Line 56:
 
| not null
 
| not null
 
|  
 
|  
−
| Work order start date.<br />
+
| Work order start date.
 
|- valign="top"
 
|- valign="top"
 
| EndDate
 
| EndDate
Line 61: Line 62:
 
| null
 
| null
 
|  
 
|  
−
| Work order end date.<br />
+
| Work order end date.
 
|- valign="top"
 
|- valign="top"
 
| DueDate
 
| DueDate
Line 67: Line 68:
 
| not null
 
| not null
 
|  
 
|  
−
| Work order due date.<br />
+
| Work order due date.
 
|- valign="top"
 
|- valign="top"
 
| ScrapReasonID
 
| ScrapReasonID
Line 73: Line 74:
 
| null
 
| null
 
|  
 
|  
−
| Reason for inspection failure.<br />IX_WorkOrder_ScrapReasonID
+
| Reason for inspection failure.
 
|- valign="top"
 
|- valign="top"
 
| ModifiedDate
 
| ModifiedDate
Line 79: Line 80:
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
| Date and time the record was last updated.<br />
+
| Date and time the record was last updated.
 
|}
 
|}
  
 +
=== Primary Key ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Primary Key
 +
| '''Columns
 +
|-
 +
| PK_WorkOrder_WorkOrderID
 +
| WorkOrderID
 +
|}
  
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|-
 +
| IX_WorkOrder_ScrapReasonID
 +
|
 +
| ScrapReasonID
 +
|-
 +
| IX_WorkOrder_ProductID
 +
|
 +
| ProductID
 +
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''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 ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 100: Line 148:
 
|}
 
|}
  
−
 
+
=== Detail Tables ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 113: Line 161:
 
|}
 
|}
  
−
 
+
=== Triggers ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 127: Line 175:
 
|}
 
|}
  
 +
Trigger iWorkOrder
 +
<pre>
 +
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;
 +
</pre>
 +
Trigger uWorkOrder
 +
<pre>
 +
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;
 +
</pre>
 +
 +
=== References ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 137: Line 275:
 
| '''Child Object
 
| '''Child Object
  
 +
|-
 +
| Schema
 +
| Schema
 +
| [[Production_(schema)|Production]]
 +
 +
|
 +
|
 
|-
 
|-
 
| Insert
 
| Insert
Line 180: Line 325:
 
| iWorkOrder
 
| iWorkOrder
 
|}
 
|}
−
 
  
  

Revision as of 23:06, 2 February 2010

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