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
Purchasing.PurchaseOrderDetail_(table)
== wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- valign="top" | '''Table''' | Purchasing.PurchaseOrderDetail |- valign="top" | '''Description''' | Individual products associated with a specific purchase order. See PurchaseOrderHeader. |} === Columns === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Column''' | '''Data Type''' | '''Nullable''' | '''Default''' | '''Description''' |- valign="top" | PurchaseOrderID | int | not null | | Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID. |- valign="top" | PurchaseOrderDetailID | int | not null | | Primary key. One line number per purchased product. |- valign="top" | DueDate | datetime | not null | | Date the product is expected to be received. |- valign="top" | OrderQty | smallint | not null | | Quantity ordered. |- valign="top" | ProductID | int | not null | | Product identification number. Foreign key to Product.ProductID. |- valign="top" | UnitPrice | money | not null | | Vendor's selling price of a single product. |- valign="top" | LineTotal | | | | Per product subtotal. Computed as OrderQty * UnitPrice. |- valign="top" | ReceivedQty | decimal(8, 2) | not null | | Quantity actually received from the vendor. |- valign="top" | RejectedQty | decimal(8, 2) | not null | | Quantity rejected during inspection. |- valign="top" | StockedQty | | | | Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty. |- 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_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID | PurchaseOrderID, PurchaseOrderDetailID |} === Indexes === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Index''' | '''Type''' | '''Columns''' |- valign="top" | IX_PurchaseOrderDetail_ProductID | | ProductID |} === Check Constraints === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Check Constraint''' | '''Expression''' | '''Description''' |- valign="top" | CK_PurchaseOrderDetail_OrderQty | ([OrderQty]>(0)) | Check constraint [OrderQty] > (0) |- valign="top" | CK_PurchaseOrderDetail_ReceivedQty | ([ReceivedQty]>=(0.00)) | Check constraint [ReceivedQty] >= (0.00) |- valign="top" | CK_PurchaseOrderDetail_RejectedQty | ([RejectedQty]>=(0.00)) | Check constraint [RejectedQty] >= (0.00) |- valign="top" | CK_PurchaseOrderDetail_UnitPrice | ([UnitPrice]>=(0.00)) | Check constraint [UnitPrice] >= (0.00) |} === 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" | [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]] | PurchaseOrderID | PurchaseOrderID |} === Triggers === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Trigger''' | '''Type''' |- valign="top" | iPurchaseOrderDetail | ON INSERT |- valign="top" | uPurchaseOrderDetail | ON UPDATE |} ==== Trigger iPurchaseOrderDetail ==== {{{{<nowiki> CREATE TRIGGER [Purchasing].[iPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] 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] ,[ReferenceOrderLineID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[PurchaseOrderID] ,inserted.[PurchaseOrderDetailID] ,'P' ,GETDATE() ,inserted.[OrderQty] ,inserted.[UnitPrice] FROM inserted INNER JOIN [Purchasing].[PurchaseOrderHeader] ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]; -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber. UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal]) FROM [Purchasing].[PurchaseOrderDetail] WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]) WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] 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 uPurchaseOrderDetail ==== {{{{<nowiki> CREATE TRIGGER [Purchasing].[uPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] 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]) OR UPDATE([UnitPrice]) -- Insert record into TransactionHistory BEGIN INSERT INTO [Production].[TransactionHistory] ([ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[PurchaseOrderID] ,inserted.[PurchaseOrderDetailID] ,'P' ,GETDATE() ,inserted.[OrderQty] ,inserted.[UnitPrice] FROM inserted INNER JOIN [Purchasing].[PurchaseOrderDetail] ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]; -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber. UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal]) FROM [Purchasing].[PurchaseOrderDetail] WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]) WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); UPDATE [Purchasing].[PurchaseOrderDetail] SET [Purchasing].[PurchaseOrderDetail].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID] AND inserted.[PurchaseOrderDetailID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderDetailID]; 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 | [[Purchasing_(schema)|Purchasing]] | | |- valign="top" | Insert | Table | [[Production.TransactionHistory_(table)|Production.TransactionHistory]] | Trigger | iPurchaseOrderDetail |- valign="top" | Insert | Table | [[Production.TransactionHistory_(table)|Production.TransactionHistory]] | Trigger | uPurchaseOrderDetail |- valign="top" | Select | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | iPurchaseOrderDetail |- valign="top" | Select | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | uPurchaseOrderDetail |- valign="top" | Update | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | uPurchaseOrderDetail |- valign="top" | Select | Table | [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]] | Trigger | iPurchaseOrderDetail |- valign="top" | Update | Table | [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]] | Trigger | iPurchaseOrderDetail |- valign="top" | Update | Table | [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]] | Trigger | uPurchaseOrderDetail |- valign="top" | Execute | Procedure | [[dbo.uspLogError_(procedure)|dbo.uspLogError]] | Trigger | iPurchaseOrderDetail |- valign="top" | Execute | Procedure | [[dbo.uspLogError_(procedure)|dbo.uspLogError]] | Trigger | uPurchaseOrderDetail |- valign="top" | Execute | Procedure | [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]] | Trigger | iPurchaseOrderDetail |- valign="top" | Execute | Procedure | [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]] | Trigger | uPurchaseOrderDetail |} === Dependencies === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Reference Type''' | '''Object Type''' | '''Referencing Object''' | '''Child Type''' | '''Child Object''' |- valign="top" | Select | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | iPurchaseOrderDetail |- valign="top" | Select | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | uPurchaseOrderDetail |- valign="top" | Update | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | uPurchaseOrderDetail |} == automatically generated == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''Table''' | Purchasing.PurchaseOrderDetail |- valign="top" | '''Description''' | Individual products associated with a specific purchase order. See PurchaseOrderHeader. |- |- |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Column''' | '''Data Type''' | '''Nullable''' | '''Default''' | '''Description / PK / Index''' |- | PurchaseOrderID | int | not null | | Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.<br />PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID |- | PurchaseOrderDetailID | int | not null | | Primary key. One line number per purchased product.<br />PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID |- | DueDate | datetime | not null | | Date the product is expected to be received.<br /> |- | OrderQty | smallint | not null | | Quantity ordered.<br /> |- | ProductID | int | not null | | Product identification number. Foreign key to Product.ProductID.<br />IX_PurchaseOrderDetail_ProductID |- | UnitPrice | money | not null | | Vendor's selling price of a single product.<br /> |- | LineTotal | | | | Per product subtotal. Computed as OrderQty * UnitPrice.<br /> |- | ReceivedQty | decimal(8, 2) | not null | | Quantity actually received from the vendor.<br /> |- | RejectedQty | decimal(8, 2) | not null | | Quantity rejected during inspection.<br /> |- | StockedQty | | | | Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.<br /> |- | 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 |- | [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]] | PurchaseOrderID | PurchaseOrderID |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Triggers''' | '''Type''' |- | iPurchaseOrderDetail | ON INSERT |- | uPurchaseOrderDetail | 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 | [[Purchasing_(schema)|Purchasing]] |- | Insert | Table | [[Production.TransactionHistory_(table)|Production.TransactionHistory]] | Trigger | iPurchaseOrderDetail |- | Insert | Table | [[Production.TransactionHistory_(table)|Production.TransactionHistory]] | Trigger | uPurchaseOrderDetail |- | Select | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | uPurchaseOrderDetail |- | Select | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | iPurchaseOrderDetail |- | Update | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | uPurchaseOrderDetail |- | Select | Table | [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]] | Trigger | iPurchaseOrderDetail |- | Update | Table | [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]] | Trigger | iPurchaseOrderDetail |- | Update | Table | [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]] | Trigger | uPurchaseOrderDetail |- | Execute | Procedure | [[dbo.uspLogError_(procedure)|dbo.uspLogError]] | Trigger | iPurchaseOrderDetail |- | Execute | Procedure | [[dbo.uspLogError_(procedure)|dbo.uspLogError]] | Trigger | uPurchaseOrderDetail |- | Execute | Procedure | [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]] | Trigger | uPurchaseOrderDetail |- | Execute | Procedure | [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]] | Trigger | iPurchaseOrderDetail |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Reference Type''' | '''Object Type''' | '''Referencing Object''' | '''Child Type''' | '''Child Object''' |- | Select | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | iPurchaseOrderDetail |- | Select | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | uPurchaseOrderDetail |- | Update | Table | [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]] | Trigger | uPurchaseOrderDetail |}
ScrewTurn Wiki version 2.0.36. Some of the icons created by
FamFamFam
.