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

From dbscript Online Help
Jump to: navigation, search
 
(7 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
−
|-
+
|- valign="top"
 +
| '''Description
 +
| 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 15: Line 17:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description
−
| '''Index
+
 
−
+
|- valign="top"
−
|-
 
 
| WorkOrderID
 
| WorkOrderID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
| PK_WorkOrder_WorkOrderID
+
| Primary key for WorkOrder records.
−
|
+
|- valign="top"
−
|-
 
 
| ProductID
 
| ProductID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Product identification number. Foreign key to Product.ProductID.
−
| IX_WorkOrder_ProductID
+
|- valign="top"
−
|-
 
 
| OrderQty
 
| OrderQty
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Product quantity to build.
−
|
+
|- valign="top"
−
|-
 
 
| StockedQty
 
| StockedQty
 
|  
 
|  
 
|  
 
|  
 
|  
 
|  
−
|  
+
| Quantity built and put in inventory.
−
|
+
|- valign="top"
−
|-
 
 
| ScrappedQty
 
| ScrappedQty
 
| smallint
 
| smallint
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Quantity that failed inspection.
−
|
+
|- valign="top"
−
|-
 
 
| StartDate
 
| StartDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Work order start date.
−
|
+
|- valign="top"
−
|-
 
 
| EndDate
 
| EndDate
 
| datetime
 
| datetime
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Work order end date.
−
|
+
|- valign="top"
−
|-
 
 
| DueDate
 
| DueDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Work order due date.
−
|
+
|- valign="top"
−
|-
 
 
| ScrapReasonID
 
| ScrapReasonID
 
| smallint
 
| smallint
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Reason for inspection failure.
−
| IX_WorkOrder_ScrapReasonID
+
|- valign="top"
−
|-
 
 
| ModifiedDate
 
| ModifiedDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
 +
| 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 97: 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 108: 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 114: Line 152:
 
| '''Column
 
| '''Column
 
| '''Referencing Column
 
| '''Referencing Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Production.WorkOrderRouting_(table)|Production.WorkOrderRouting]]
 
| [[Production.WorkOrderRouting_(table)|Production.WorkOrderRouting]]
 
| WorkOrderID
 
| WorkOrderID
Line 121: 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"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
| '''Child Type
 +
| '''Child Object
 +
 +
|- valign="top"
 +
| Schema
 +
| Schema
 +
| [[Production_(schema)|Production]]
 +
 +
|
 +
|
 +
|- valign="top"
 +
| Insert
 +
| Table
 +
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
 +
 +
| Trigger
 +
| iWorkOrder
 +
|- valign="top"
 +
| Insert
 +
| Table
 +
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
 +
 +
| Trigger
 +
| uWorkOrder
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 +
 +
| Trigger
 +
| iWorkOrder
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 +
 +
| Trigger
 +
| uWorkOrder
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 +
 +
| Trigger
 +
| iWorkOrder
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 +
 +
| Trigger
 +
| uWorkOrder
 +
|}
 +
 +
 
== automatically generated ==
 
== automatically generated ==
  
Line 140: Line 332:
 
| '''Table
 
| '''Table
 
| Production.WorkOrder
 
| Production.WorkOrder
−
|-
+
|- valign="top"
−
 
 
 
| '''Description
 
| '''Description
 
| Manufacturing work orders.
 
| Manufacturing work orders.
 
|-
 
|-
−
 
 
|}
 
|}
  
Line 162: Line 352:
 
| not null
 
| not null
 
|  
 
|  
−
| Primary key for WorkOrder records.PK_WorkOrder_WorkOrderID
+
| Primary key for WorkOrder records.<br />PK_WorkOrder_WorkOrderID
 
|- valign="top"
 
|- valign="top"
 
| ProductID
 
| ProductID
Line 168: Line 358:
 
| not null
 
| not null
 
|  
 
|  
−
| Product identification number. Foreign key to Product.ProductID.IX_WorkOrder_ProductID
+
| Product identification number. Foreign key to Product.ProductID.<br />IX_WorkOrder_ProductID
 
|- valign="top"
 
|- valign="top"
 
| OrderQty
 
| OrderQty
Line 174: Line 364:
 
| not null
 
| not null
 
|  
 
|  
−
| Product quantity to build.
+
| Product quantity to build.<br />
 
|- valign="top"
 
|- valign="top"
 
| StockedQty
 
| StockedQty
Line 180: Line 370:
 
|  
 
|  
 
|  
 
|  
−
| Quantity built and put in inventory.
+
| Quantity built and put in inventory.<br />
 
|- valign="top"
 
|- valign="top"
 
| ScrappedQty
 
| ScrappedQty
Line 186: Line 376:
 
| not null
 
| not null
 
|  
 
|  
−
| Quantity that failed inspection.
+
| Quantity that failed inspection.<br />
 
|- valign="top"
 
|- valign="top"
 
| StartDate
 
| StartDate
Line 192: Line 382:
 
| not null
 
| not null
 
|  
 
|  
−
| Work order start date.
+
| Work order start date.<br />
 
|- valign="top"
 
|- valign="top"
 
| EndDate
 
| EndDate
Line 198: Line 388:
 
| null
 
| null
 
|  
 
|  
−
| Work order end date.
+
| Work order end date.<br />
 
|- valign="top"
 
|- valign="top"
 
| DueDate
 
| DueDate
Line 204: Line 394:
 
| not null
 
| not null
 
|  
 
|  
−
| Work order due date.
+
| Work order due date.<br />
 
|- valign="top"
 
|- valign="top"
 
| ScrapReasonID
 
| ScrapReasonID
Line 210: Line 400:
 
| null
 
| null
 
|  
 
|  
−
| Reason for inspection failure.IX_WorkOrder_ScrapReasonID
+
| Reason for inspection failure.<br />IX_WorkOrder_ScrapReasonID
 
|- valign="top"
 
|- valign="top"
 
| ModifiedDate
 
| ModifiedDate
Line 216: Line 406:
 
| not null
 
| not null
 
| (GETDATE())
 
| (GETDATE())
−
| Date and time the record was last updated.
+
| Date and time the record was last updated.<br />
 
|}
 
|}
  
Line 267: Line 457:
 
{| 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"
 +
| '''Dependency Type
 +
| '''Object Type
 
| '''Referenced Object
 
| '''Referenced Object
−
| '''Object Type
+
 
−
| '''Dependency Type
+
| '''Child Type
−
+
| '''Child Object
 +
 
 +
|-
 +
| Schema
 +
| Schema
 +
| [[Production_(schema)|Production]]
 +
 
 +
|
 +
|
 
|-
 
|-
 +
| Insert
 +
| Table
 
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
 
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
 +
 +
| Trigger
 +
| iWorkOrder
 +
|-
 +
| Insert
 
| Table
 
| Table
−
| Insert
 
−
|-
 
 
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
 
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
−
| Table
+
 
−
| Insert
+
| Trigger
 +
| uWorkOrder
 
|-
 
|-
 +
| Execute
 +
| Procedure
 
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 +
 +
| Trigger
 +
| iWorkOrder
 +
|-
 +
| Execute
 
| Procedure
 
| Procedure
 +
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 +
 +
| Trigger
 +
| uWorkOrder
 +
|-
 
| Execute
 
| Execute
 +
| Procedure
 +
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 +
 +
| Trigger
 +
| uWorkOrder
 
|-
 
|-
−
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 
−
| Procedure
 
 
| Execute
 
| Execute
−
|-
 
−
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 
 
| Procedure
 
| Procedure
−
| Execute
 
−
|-
 
 
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
−
| Procedure
+
 
−
| Execute
+
| Trigger
 +
| iWorkOrder
 
|}
 
|}

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"