ScrewTurn Wiki
Navigation
Main Page
Random Page
Create a new Page
All Pages
Categories
Administration
File Management
Login/Logout
Language Selection
Your Profile
Create Account
Quick Search
Advanced Search »
Back
History
Production.WorkOrder_(table)
== 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 | | Primary key for WorkOrder records. |- valign="top" | ProductID | int | not null | | Product identification number. Foreign key to Product.ProductID. |- valign="top" | OrderQty | int | not null | | Product quantity to build. |- valign="top" | StockedQty | | | | Quantity built and put in inventory. |- valign="top" | ScrappedQty | smallint | not null | | Quantity that failed inspection. |- valign="top" | StartDate | datetime | not null | | Work order start date. |- valign="top" | EndDate | datetime | null | | Work order end date. |- valign="top" | DueDate | datetime | not null | | Work order due date. |- valign="top" | ScrapReasonID | smallint | null | | 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 | | 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" |- 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]] | | |- 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 == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''Table''' | Production.WorkOrder |- valign="top" | '''Description''' | Manufacturing work orders. |- |- |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Column''' | '''Data Type''' | '''Nullable''' | '''Default''' | '''Description / PK / Index''' |- | WorkOrderID | int | not null | | Primary key for WorkOrder records.<br />PK_WorkOrder_WorkOrderID |- | ProductID | int | not null | | Product identification number. Foreign key to Product.ProductID.<br />IX_WorkOrder_ProductID |- | OrderQty | int | not null | | Product quantity to build.<br /> |- | StockedQty | | | | Quantity built and put in inventory.<br /> |- | ScrappedQty | smallint | not null | | Quantity that failed inspection.<br /> |- | StartDate | datetime | not null | | Work order start date.<br /> |- | EndDate | datetime | null | | Work order end date.<br /> |- | DueDate | datetime | not null | | Work order due date.<br /> |- | ScrapReasonID | smallint | null | | Reason for inspection failure.<br />IX_WorkOrder_ScrapReasonID |- | ModifiedDate | datetime | not null | (GETDATE()) | Date and time the record was last updated.<br /> |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Relation''' | '''Column''' | '''Referenced Column''' |- | [[Production.Product_(table)|Production.Product]] | ProductID | ProductID |- | [[Production.ScrapReason_(table)|Production.ScrapReason]] | ScrapReasonID | ScrapReasonID |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Detail Table''' | '''Column''' | '''Referencing Column''' |- | [[Production.WorkOrderRouting_(table)|Production.WorkOrderRouting]] | WorkOrderID | WorkOrderID |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Triggers''' | '''Type''' |- | iWorkOrder | ON INSERT |- | uWorkOrder | ON UPDATE |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Dependency Type''' | '''Object Type''' | '''Referenced Object''' | '''Child Type''' | '''Child Object''' |- | Schema | Schema | [[Production_(schema)|Production]] |- | Insert | Table | [[Production.TransactionHistory_(table)|Production.TransactionHistory]] | Trigger | iWorkOrder |- | Insert | Table | [[Production.TransactionHistory_(table)|Production.TransactionHistory]] | Trigger | uWorkOrder |- | Execute | Procedure | [[dbo.uspLogError_(procedure)|dbo.uspLogError]] | Trigger | iWorkOrder |- | Execute | Procedure | [[dbo.uspLogError_(procedure)|dbo.uspLogError]] | Trigger | uWorkOrder |- | Execute | Procedure | [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]] | Trigger | uWorkOrder |- | Execute | Procedure | [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]] | Trigger | iWorkOrder |}
ScrewTurn Wiki version 2.0.36. Some of the icons created by
FamFamFam
.