Properties

Owner:	Production
Creation Date:	04/26/2006
Located On:	PRIMARY
Data Size KB:	6304
Index Size KB:	2992
Rows:	113443
Description:	Record of each purchase order, sales order, or work order transaction year to date.

Columns

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

Total: 9 column(s)

Identity column

	Name	Seed	Increment	Not for replication
	TransactionID	100000	1

Indexes

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

Total: 3 index(es)

Check Constraints

	Name	Expression
	CK_TransactionHistory_TransactionType	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')

Total: 1 constraint(s)

Referenced Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	Production.Product	FK_TransactionHistory_Product_ProductID	PK_Product_ProductID

Total: 1 table(s)

Objects that [Production].[TransactionHistory] depends on

	Object Name	Owner	Object Type	Dep Level
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	ProductCategory	Production	Table	2
	ProductModel	Production	Table	2
	UnitMeasure	Production	Table	2
	ProductSubcategory	Production	Table	3
	Product	Production	Table	4

Total: 7 object(s)

Objects that depend on [Production].[TransactionHistory]

	Object Name	Owner	Object Type	Dep Level
	iduSalesOrderDetail	Sales	Trigger	1
	iPurchaseOrderDetail	Purchasing	Trigger	1
	iWorkOrder	Production	Trigger	1
	uPurchaseOrderDetail	Purchasing	Trigger	1
	uWorkOrder	Production	Trigger	1

Total: 5 object(s)

SQL

CREATE TABLE [TransactionHistory] (
    [TransactionID] [int] IDENTITY (100000, 1) NOT NULL ,
    [ProductID] [int] NOT NULL ,
    [ReferenceOrderID] [int] NOT NULL ,
    [ReferenceOrderLineID] [int] NOT NULL CONSTRAINT [DF_TransactionHistory_ReferenceOrderLineID] DEFAULT ((0)),
    [TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_TransactionDate] DEFAULT (getdate()),
    [TransactionType] [nchar] (1) COLLATE Latin1_General_CS_AS NOT NULL ,
    [Quantity] [int] NOT NULL ,
    [ActualCost] [money] NOT NULL ,
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_ModifiedDate] DEFAULT (getdate()),
    CONSTRAINT [PK_TransactionHistory_TransactionID] PRIMARY KEY  CLUSTERED
    (
        [TransactionID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_TransactionHistory_Product_ProductID] FOREIGN KEY
    (
        [ProductID]
    ) REFERENCES [Product] (
        [ProductID]
    ),
    CONSTRAINT [CK_TransactionHistory_TransactionType] CHECK (upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')
) ON [PRIMARY]
GO

See Also

List of tables