Edit wikibot
Table | Production.WorkOrder |
Description | Manufacturing work orders. |
Edit 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. |
Edit Primary Key
Primary Key | Columns |
PK_WorkOrder_WorkOrderID | WorkOrderID |
Edit Indexes
Index | Type | Columns |
IX_WorkOrder_ProductID | | ProductID |
IX_WorkOrder_ScrapReasonID | | ScrapReasonID |
Edit Check Constraints
Edit Foreign Keys
Edit Detail Tables
Edit Triggers
Trigger | Type |
iWorkOrder | ON INSERT |
uWorkOrder | ON UPDATE |
Edit 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;
Edit 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;
Edit References
Edit 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.
|
Triggers | Type |
iWorkOrder | ON INSERT |
uWorkOrder | ON UPDATE |