9 lines
678 B
Transact-SQL
9 lines
678 B
Transact-SQL
CREATE OR ALTER VIEW [dbo].[Prc_V_PurchaseAgreementLine] AS
|
|
SELECT l.Id, l.TenantId, l.AgreementId, l.ProductId, l.Description, l.Quantity, l.UomId, l.UnitPrice,
|
|
ISNULL((SELECT SUM(ol.Quantity)
|
|
FROM Prc_T_PurchaseOrderLine ol
|
|
INNER JOIN Prc_T_PurchaseOrder o ON o.Id = ol.PurchaseOrderId
|
|
WHERE o.AgreementId = l.AgreementId AND ol.ProductId = l.ProductId
|
|
AND o.Status = 'Purchase' AND o.IsDeleted = 0 AND ol.IsDeleted = 0), 0) AS OrderedQuantity,
|
|
l.CreationTime, l.CreatorId, l.LastModificationTime, l.LastModifierId, l.IsDeleted, l.DeletionTime, l.DeleterId
|
|
FROM Prc_T_PurchaseAgreementLine l
|