Production.TransactionHistory Table

Description

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

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	113443
Data Space Used	6,336.00 KB
Index Space Used	3,928.00 KB

Columns

Column Name	Description	Datatype	Length	Default
TransactionID	Primary key for TransactionHistory records.	Integer	4
ProductID	Product identification number. Foreign key to Product.ProductID.	Integer	4
ReferenceOrderID	Purchase order, sales order, or work order identification number.	Integer	4
ReferenceOrderLineID	Line number associated with the purchase order, sales order, or work order.	Integer	4	((0))
TransactionDate	Date and time of the transaction.	DBTimeStamp	4	(getdate())
TransactionType	W = WorkOrder, S = SalesOrder, P = PurchaseOrder	WChar	1
Quantity	Product quantity.	Integer	4
ActualCost	Product cost.	Currency	8
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
IX_TransactionHistory_ProductID	Nonclustered index.
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	Nonclustered index.
PK_TransactionHistory_TransactionID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_TransactionHistory_TransactionType	Check constraint [TransactionType]='p' OR [TransactionType]='s' OR [TransactionType]='w' OR [TransactionType]='P' OR [TransactionType]='S' OR [TransactionType]='W')	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')

Relationships

Relationship	Description
FK_TransactionHistory_Product_ProductID	Foreign key constraint referencing Product.ProductID.

Objects that depend on Production.TransactionHistory

Database Object	Object Type	Description	Dep Level
iduSalesOrderDetail	Trigger	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.	2
iPurchaseOrderDetail	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.	2
iWorkOrder	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table.	2
Purchasing.PurchaseOrderDetail	Table	Individual products associated with a specific purchase order. See PurchaseOrderHeader.	1
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	1
uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	2
uWorkOrder	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.	2
Production.WorkOrder	Table	Manufacturing work orders.	1
Production.WorkOrderRouting	Table	Work order details.	2

Objects that Production.TransactionHistory depends on

Database Object	Object Type	Description	Dep Level
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCategory	Table	High-level product categorization.	3
Production.ProductModel	Table	Product model classification.	2
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	2
Production.UnitMeasure	Table	Unit of measure lookup table.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[TransactionHistory](
    [TransactionID] [int] IDENTITY(100000,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReferenceOrderID] [int] NOT NULL,
    [ReferenceOrderLineID] [int] NOT NULL,
    [TransactionDate] [datetime] NOT NULL,
    [TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Quantity] [int] NOT NULL,
    [ActualCost] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_TransactionHistory_TransactionID] PRIMARY KEY CLUSTERED 
(
    [TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Production].[TransactionHistory] ADD  CONSTRAINT [DF_TransactionHistory_ReferenceOrderLineID]  DEFAULT ((0)) FOR [ReferenceOrderLineID]
ALTER TABLE [Production].[TransactionHistory] ADD  CONSTRAINT [DF_TransactionHistory_TransactionDate]  DEFAULT (getdate()) FOR [TransactionDate]
ALTER TABLE [Production].[TransactionHistory] ADD  CONSTRAINT [DF_TransactionHistory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Production].[TransactionHistory]  WITH CHECK ADD  CONSTRAINT [FK_TransactionHistory_Product_ProductID] FOREIGN KEY([ProductID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Production].[TransactionHistory] CHECK CONSTRAINT [FK_TransactionHistory_Product_ProductID]
ALTER TABLE [Production].[TransactionHistory]  WITH CHECK ADD  CONSTRAINT [CK_TransactionHistory_TransactionType] CHECK  ((upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W'))
ALTER TABLE [Production].[TransactionHistory] CHECK CONSTRAINT [CK_TransactionHistory_TransactionType]

Related Objects