Production.TransactionHistory

Object type: Table

Record of each purchase order, sales order, or work order transaction year to date.

Columns

Name Data type Nullable Description

TransactionID int No Column has identity seed value of 1 with a step value of 1.
Primary key for TransactionHistory records.

ProductID int No Product identification number. Foreign key to Product.ProductID.

ReferenceOrderID int No Purchase order, sales order, or work order identification number.

ReferenceOrderLineID int No Default value: 0
Line number associated with the purchase order, sales order, or work order.

TransactionDate datetime No Default value: GETDATE()
Date and time of the transaction.

TransactionType nchar(1) No W = WorkOrder, S = SalesOrder, P = PurchaseOrder

Quantity int No Product quantity.

ActualCost money No Product cost.

ModifiedDate datetime No Default value: GETDATE()
Date and time the record was last updated.

Constraints

Name Type Columns Description

DF_TransactionHistory_ReferenceOrderLineID DEFAULT ReferenceOrderLineID Value: 0
Default constraint value of 0

DF_TransactionHistory_TransactionDate DEFAULT TransactionDate Value: GETDATE()
Default constraint value of GETDATE()

DF_TransactionHistory_ModifiedDate DEFAULT ModifiedDate Value: GETDATE()
Default constraint value of GETDATE()

CK_TransactionHistory_TransactionType CHECK N/A Value: (UPPER([TransactionType]) IN ('W', 'S', 'P') )
Check constraint [TransactionType]='p' OR [TransactionType]='s' OR [TransactionType]='w' OR [TransactionType]='P' OR [TransactionType]='S' OR [TransactionType]='W')

PK_TransactionHistory_TransactionID PRIMARY KEY TransactionID Clustered index created by a primary key constraint.

FK_TransactionHistory_Product_ProductID FOREIGN KEY ProductID Reference table: Production.Product
Foreign key constraint referencing Product.ProductID.

Indices

Name Description

IX_TransactionHistory_ProductID Nonclustered index.

IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID Nonclustered index.>

Triggers

Name Description

Production.uTransactionHistory AFTER UPDATE trigger setting the ModifiedDate column in the TransactionHistory table to the current date.

Modified by

Name Description

Production.iWorkOrder AFTER INSERT trigger that inserts a row in the TransactionHistory table.

Production.uTransactionHistory AFTER UPDATE trigger setting the ModifiedDate column in the TransactionHistory table to the current date.

Production.uWorkOrder AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.

Purchasing.iPurchaseOrderDetail AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.

Purchasing.uPurchaseOrderDetail AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.

Sales.iduSalesOrderDetail AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.

Applies to

Tables | User

Name	Data type	Nullable	Description
TransactionID	int	No	Column has identity seed value of 1 with a step value of 1. Primary key for TransactionHistory records.
ProductID	int	No	Product identification number. Foreign key to Product.ProductID.
ReferenceOrderID	int	No	Purchase order, sales order, or work order identification number.
ReferenceOrderLineID	int	No	Default value: 0 Line number associated with the purchase order, sales order, or work order.
TransactionDate	datetime	No	Default value: GETDATE() Date and time of the transaction.
TransactionType	nchar(1)	No	W = WorkOrder, S = SalesOrder, P = PurchaseOrder
Quantity	int	No	Product quantity.
ActualCost	money	No	Product cost.
ModifiedDate	datetime	No	Default value: GETDATE() Date and time the record was last updated.

Name	Type	Columns	Description
DF_TransactionHistory_ReferenceOrderLineID	DEFAULT	ReferenceOrderLineID	Value: 0 Default constraint value of 0
DF_TransactionHistory_TransactionDate	DEFAULT	TransactionDate	Value: GETDATE() Default constraint value of GETDATE()
DF_TransactionHistory_ModifiedDate	DEFAULT	ModifiedDate	Value: GETDATE() Default constraint value of GETDATE()
CK_TransactionHistory_TransactionType	CHECK	N/A	Value: (UPPER([TransactionType]) IN ('W', 'S', 'P') ) Check constraint [TransactionType]='p' OR [TransactionType]='s' OR [TransactionType]='w' OR [TransactionType]='P' OR [TransactionType]='S' OR [TransactionType]='W')
PK_TransactionHistory_TransactionID	PRIMARY KEY	TransactionID	Clustered index created by a primary key constraint.
FK_TransactionHistory_Product_ProductID	FOREIGN KEY	ProductID	Reference table: Production.Product Foreign key constraint referencing Product.ProductID.

Name	Description
IX_TransactionHistory_ProductID	Nonclustered index.
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	Nonclustered index.>

Name	Description
Production.uTransactionHistory	AFTER UPDATE trigger setting the ModifiedDate column in the TransactionHistory table to the current date.

Name	Description
Production.iWorkOrder	AFTER INSERT trigger that inserts a row in the TransactionHistory table.
Production.uTransactionHistory	AFTER UPDATE trigger setting the ModifiedDate column in the TransactionHistory table to the current date.
Production.uWorkOrder	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.
Purchasing.iPurchaseOrderDetail	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.
Purchasing.uPurchaseOrderDetail	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.
Sales.iduSalesOrderDetail	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.