Production WorkOrder (table)

Modified on 2011/08/27 11:18 by wikibot Categorized as Uncategorized
== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table'''
| Production.WorkOrder
|- valign="top"
| '''Description'''
| Manufacturing work orders.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column'''
| '''Data Type'''
| '''Nullable'''
| '''Default'''
| '''Description'''

|- valign="top"
| WorkOrderID
| int
| not null
| &nbsp;
| Primary key for WorkOrder records.
|- valign="top"
| ProductID
| int
| not null
| &nbsp;
| Product identification number. Foreign key to Product.ProductID.
|- valign="top"
| OrderQty
| int
| not null
| &nbsp;
| Product quantity to build.
|- valign="top"
| StockedQty
| &nbsp;
| &nbsp;
| &nbsp;
| Quantity built and put in inventory.
|- valign="top"
| ScrappedQty
| smallint
| not null
| &nbsp;
| Quantity that failed inspection.
|- valign="top"
| StartDate
| datetime
| not null
| &nbsp;
| Work order start date.
|- valign="top"
| EndDate
| datetime
| null
| &nbsp;
| Work order end date.
|- valign="top"
| DueDate
| datetime
| not null
| &nbsp;
| Work order due date.
|- valign="top"
| ScrapReasonID
| smallint
| null
| &nbsp;
| Reason for inspection failure.
|- valign="top"
| ModifiedDate
| datetime
| not null
| (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
| &nbsp;
| ProductID
|- valign="top"
| IX_WorkOrder_ScrapReasonID
| &nbsp;
| 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"
|- style="background:silver"
| '''Relation'''
| '''Column'''
| '''Referenced Column'''

|- valign="top"
| [[Production Product (table)|Production.Product]]
| ProductID
| ProductID
|- valign="top"
| [[Production ScrapReason (table)|Production.ScrapReason]]
| ScrapReasonID
| ScrapReasonID
|}

=== Detail Tables ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table'''
| '''Column'''
| '''Referencing Column'''

|- valign="top"
| [[Production WorkOrderRouting (table)|Production.WorkOrderRouting]]
| WorkOrderID
| WorkOrderID
|}

=== Triggers ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Trigger'''
| '''Type'''

|- valign="top"
| iWorkOrder
| ON INSERT
|- valign="top"
| uWorkOrder
| ON UPDATE
|}

==== Trigger iWorkOrder ====
{{{{<nowiki>
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;
</nowiki>}}}}

==== Trigger uWorkOrder ====
{{{{<nowiki>
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;
</nowiki>}}}}

=== 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]]

| &nbsp;
| &nbsp;
|- 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
|}
devio dbscript ScrewTurn Wiki

Navigation¶

Production WorkOrder (table)
