10 lines
758 B
MySQL
10 lines
758 B
MySQL
|
CREATE OR REPLACE VIEW public."Prc_V_PurchaseAgreementLine" AS
|
||
|
|
SELECT l."Id", l."TenantId", l."AgreementId", l."ProductId", l."Description", l."Quantity", l."UomId", l."UnitPrice",
|
||
|
|
COALESCE((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" = false AND ol."IsDeleted" = false), 0) AS "OrderedQuantity",
|
||
|
|
l."CreationTime", l."CreatorId", l."LastModificationTime", l."LastModifierId", l."IsDeleted", l."DeletionTime", l."DeleterId"
|
||
|
|
FROM "Prc_T_PurchaseAgreementLine" l;
|