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

From dbscript Online Help
Jump to: navigation, search
 
(2 intermediate revisions by the same user not shown)
Line 2: Line 2:
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
−
|-
+
|- valign="top"
 
| '''Table
 
| '''Table
 
| Production.WorkOrder
 
| Production.WorkOrder
Line 8: Line 8:
 
| '''Description
 
| '''Description
 
| Manufacturing work orders.
 
| Manufacturing work orders.
−
|-
 
 
|}
 
|}
  
−
 
+
=== 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 17:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''Description / PK / Index
+
| '''Description
−
+
 
 
|- valign="top"
 
|- valign="top"
 
| WorkOrderID
 
| WorkOrderID
Line 25: Line 24:
 
| 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 30:
 
| 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 36:
 
| not null
 
| not null
 
|  
 
|  
−
| Product quantity to build.<br />
+
| Product quantity to build.
 
|- valign="top"
 
|- valign="top"
 
| StockedQty
 
| StockedQty
Line 43: Line 42:
 
|  
 
|  
 
|  
 
|  
−
| Quantity built and put in inventory.<br />
+
| Quantity built and put in inventory.
 
|- valign="top"
 
|- valign="top"
 
| ScrappedQty
 
| ScrappedQty
Line 49: Line 48:
 
| not null
 
| not null
 
|  
 
|  
−
| Quantity that failed inspection.<br />
+
| Quantity that failed inspection.
 
|- valign="top"
 
|- valign="top"
 
| StartDate
 
| StartDate
Line 55: Line 54:
 
| not null
 
| not null
 
|  
 
|  
−
| Work order start date.<br />
+
| Work order start date.
 
|- valign="top"
 
|- valign="top"
 
| EndDate
 
| EndDate
Line 61: Line 60:
 
| null
 
| null
 
|  
 
|  
−
| Work order end date.<br />
+
| Work order end date.
 
|- valign="top"
 
|- valign="top"
 
| DueDate
 
| DueDate
Line 67: Line 66:
 
| not null
 
| not null
 
|  
 
|  
−
| Work order due date.<br />
+
| Work order due date.
 
|- valign="top"
 
|- valign="top"
 
| ScrapReasonID
 
| ScrapReasonID
Line 73: Line 72:
 
| null
 
| null
 
|  
 
|  
−
| Reason for inspection failure.<br />IX_WorkOrder_ScrapReasonID
+
| Reason for inspection failure.
 
|- valign="top"
 
|- valign="top"
 
| ModifiedDate
 
| ModifiedDate
Line 79: Line 78:
 
| 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
 +
|- valign="top"
 +
| PK_WorkOrder_WorkOrderID
 +
| WorkOrderID
 +
|}
  
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
  
 +
|- valign="top"
 +
| IX_WorkOrder_ProductID
 +
|
 +
| ProductID
 +
|- valign="top"
 +
| IX_WorkOrder_ScrapReasonID
 +
|
 +
| ScrapReasonID
 +
|}
 +
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Check Constraint
 +
| '''Expression
 +
| '''Description
 +
 +
|- valign="top"
 +
| CK_WorkOrder_EndDate
 +
| ([EndDate]>=[StartDate] OR [EndDate] IS NULL)
 +
| Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL
 +
|- valign="top"
 +
| CK_WorkOrder_OrderQty
 +
| ([OrderQty]>(0))
 +
| Check constraint [OrderQty] > (0)
 +
|- valign="top"
 +
| 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 89: Line 135:
 
| '''Column
 
| '''Column
 
| '''Referenced Column
 
| '''Referenced Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Production.Product_(table)|Production.Product]]
 
| [[Production.Product_(table)|Production.Product]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Production.ScrapReason_(table)|Production.ScrapReason]]
 
| [[Production.ScrapReason_(table)|Production.ScrapReason]]
 
| ScrapReasonID
 
| ScrapReasonID
Line 100: Line 146:
 
|}
 
|}
  
−
 
+
=== 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 106: Line 152:
 
| '''Column
 
| '''Column
 
| '''Referencing Column
 
| '''Referencing Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Production.WorkOrderRouting_(table)|Production.WorkOrderRouting]]
 
| [[Production.WorkOrderRouting_(table)|Production.WorkOrderRouting]]
 
| WorkOrderID
 
| WorkOrderID
Line 113: Line 159:
 
|}
 
|}
  
−
 
+
=== 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"
−
| '''Triggers
+
| '''Trigger
 
| '''Type
 
| '''Type
−
+
 
−
|-
+
|- valign="top"
 
| iWorkOrder
 
| iWorkOrder
 
| ON INSERT
 
| ON INSERT
−
|-
+
|- valign="top"
 
| uWorkOrder
 
| uWorkOrder
 
| ON UPDATE
 
| ON UPDATE
 
|}
 
|}
  
 +
==== 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 274:
 
| '''Child Object
 
| '''Child Object
  
−
|-
+
|- valign="top"
 +
| Schema
 +
| Schema
 +
| [[Production_(schema)|Production]]
 +
 
 +
|
 +
|
 +
|- valign="top"
 
| Insert
 
| Insert
 
| Table
 
| Table
Line 144: Line 288:
 
| Trigger
 
| Trigger
 
| iWorkOrder
 
| iWorkOrder
−
|-
+
|- valign="top"
 
| Insert
 
| Insert
 
| Table
 
| Table
Line 151: Line 295:
 
| Trigger
 
| Trigger
 
| uWorkOrder
 
| uWorkOrder
−
|-
+
|- valign="top"
 
| Execute
 
| Execute
 
| Procedure
 
| Procedure
Line 158: Line 302:
 
| Trigger
 
| Trigger
 
| iWorkOrder
 
| iWorkOrder
−
|-
+
|- valign="top"
 
| Execute
 
| Execute
 
| Procedure
 
| Procedure
Line 165: Line 309:
 
| Trigger
 
| Trigger
 
| uWorkOrder
 
| uWorkOrder
−
|-
+
|- valign="top"
 
| Execute
 
| Execute
 
| Procedure
 
| Procedure
Line 171: Line 315:
  
 
| Trigger
 
| Trigger
−
| uWorkOrder
+
| iWorkOrder
−
|-
+
|- valign="top"
 
| Execute
 
| Execute
 
| Procedure
 
| Procedure
Line 178: Line 322:
  
 
| Trigger
 
| Trigger
−
| iWorkOrder
+
| uWorkOrder
 
|}
 
|}
−
 
  
  

Latest revision as of 10: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"