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

From dbscript Online Help
Jump to: navigation, search
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 ===
 
=== Columns ===
Line 20: Line 18:
 
| '''Default
 
| '''Default
 
| '''Description
 
| '''Description
−
+
 
 
|- valign="top"
 
|- valign="top"
 
| WorkOrderID
 
| WorkOrderID
Line 88: Line 86:
 
| '''Primary Key
 
| '''Primary Key
 
| '''Columns
 
| '''Columns
−
|-
+
|- valign="top"
 
| PK_WorkOrder_WorkOrderID
 
| PK_WorkOrder_WorkOrderID
 
| WorkOrderID
 
| WorkOrderID
Line 99: Line 97:
 
| '''Type
 
| '''Type
 
| '''Columns
 
| '''Columns
−
+
 
−
|-
+
|- valign="top"
 
| IX_WorkOrder_ScrapReasonID
 
| IX_WorkOrder_ScrapReasonID
 
|  
 
|  
 
| ScrapReasonID
 
| ScrapReasonID
−
|-
+
|- valign="top"
 
| IX_WorkOrder_ProductID
 
| IX_WorkOrder_ProductID
 
|  
 
|  
Line 116: Line 114:
 
| '''Expression
 
| '''Expression
 
| '''Description
 
| '''Description
−
+
 
−
|-
+
|- valign="top"
 
| CK_WorkOrder_EndDate
 
| CK_WorkOrder_EndDate
 
| ([EndDate]>=[StartDate] OR [EndDate] IS NULL)
 
| ([EndDate]>=[StartDate] OR [EndDate] IS NULL)
 
| Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL
 
| Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL
−
|-
+
|- valign="top"
 
| CK_WorkOrder_OrderQty
 
| CK_WorkOrder_OrderQty
 
| ([OrderQty]>(0))
 
| ([OrderQty]>(0))
 
| Check constraint [OrderQty] > (0)
 
| Check constraint [OrderQty] > (0)
−
|-
+
|- valign="top"
 
| CK_WorkOrder_ScrappedQty
 
| CK_WorkOrder_ScrappedQty
 
| ([ScrappedQty]>=(0))
 
| ([ScrappedQty]>=(0))
Line 137: 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 154: Line 152:
 
| '''Column
 
| '''Column
 
| '''Referencing Column
 
| '''Referencing Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Production.WorkOrderRouting_(table)|Production.WorkOrderRouting]]
 
| [[Production.WorkOrderRouting_(table)|Production.WorkOrderRouting]]
 
| WorkOrderID
 
| WorkOrderID
Line 164: Line 162:
 
{| 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
+
==== Trigger iWorkOrder ====
 
<pre>
 
<pre>
 
CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder]  
 
CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder]  
Line 219: Line 217:
 
END;
 
END;
 
</pre>
 
</pre>
−
Trigger uWorkOrder
+
 
 +
==== Trigger uWorkOrder ====
 
<pre>
 
<pre>
 
CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder]  
 
CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder]  
Line 264: Line 263:
 
END;
 
END;
 
</pre>
 
</pre>
−
+
 
 
=== References ===
 
=== References ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
Line 275: Line 274:
 
| '''Child Object
 
| '''Child Object
  
−
|-
+
|- valign="top"
 
| Schema
 
| Schema
 
| Schema
 
| Schema
Line 282: Line 281:
 
|  
 
|  
 
|  
 
|  
−
|-
+
|- valign="top"
 
| Insert
 
| Insert
 
| Table
 
| Table
Line 289: Line 288:
 
| Trigger
 
| Trigger
 
| iWorkOrder
 
| iWorkOrder
−
|-
+
|- valign="top"
 
| Insert
 
| Insert
 
| Table
 
| Table
Line 296: Line 295:
 
| Trigger
 
| Trigger
 
| uWorkOrder
 
| uWorkOrder
−
|-
+
|- valign="top"
 
| Execute
 
| Execute
 
| Procedure
 
| Procedure
Line 303: Line 302:
 
| Trigger
 
| Trigger
 
| iWorkOrder
 
| iWorkOrder
−
|-
+
|- valign="top"
 
| Execute
 
| Execute
 
| Procedure
 
| Procedure
Line 310: Line 309:
 
| Trigger
 
| Trigger
 
| uWorkOrder
 
| uWorkOrder
−
|-
+
|- valign="top"
 
| Execute
 
| Execute
 
| Procedure
 
| Procedure
Line 317: Line 316:
 
| Trigger
 
| Trigger
 
| uWorkOrder
 
| uWorkOrder
−
|-
+
|- valign="top"
 
| Execute
 
| Execute
 
| Procedure
 
| Procedure

Revision as of 00:17, 24 June 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

Trigger 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=2649"