34 lines
3.1 KiB
SQL
34 lines
3.1 KiB
SQL
-- Plan Takibi (Prc.MaterialRequestPlanTrack): talep kalemleri için verilen her karar — satınalmada ihalenin kök teklif talebindeki kalem
|
||
-- (alternatif tedarikçilere kopyalanan satırlar tekrar listelenmez; siparişe dönüştüyse ihale kaleminin siparişi ve tedarikçisi), iç
|
||
-- transferde transfer satırı; kaynak ve hedefi aynı (teslim konumu) olan transfer Stoktan kararıdır. Gerçekleşen: siparişte alınan,
|
||
-- transferde tamamlanmış belgede yapılan miktar.
|
||
CREATE OR REPLACE VIEW public."Prc_V_MaterialRequestPlanTrack" AS
|
||
SELECT rl."Id", rl."TenantId", i."Id" AS "MaterialRequestItemId", i."MaterialRequestId", r."RequestNo", r."BranchId", r."DepartmentId", r."WarehouseId",
|
||
r."LocationId", i."ProductId", i."DueDate", CAST('Purchase' AS varchar(16)) AS "PlanMethod", q."Id" AS "DocumentId", q."RfqNo" AS "DocumentNo",
|
||
q."Status" AS "DocumentStatus", COALESCE(o."SupplierId", q."SupplierId") AS "SupplierId", CAST(NULL AS uuid) AS "SourceLocationId",
|
||
o."Id" AS "PurchaseOrderId", o."OrderNo" AS "PurchaseOrderNo", o."Status" AS "OrderStatus", rl."Quantity", rl."UomId",
|
||
COALESCE(o."ReceivedQuantity", 0) AS "DoneQuantity", rl."CreationTime" AS "PlanDate", rl."CreatorId" AS "PlannerUserId", rl."IsDeleted"
|
||
FROM "Prc_T_PurchaseRfqLine" rl
|
||
INNER JOIN "Prc_T_PurchaseRfq" q ON q."Id" = rl."PurchaseRfqId" AND q."IsDeleted" = false AND q."Id" = COALESCE(q."TenderGroupId", q."Id")
|
||
INNER JOIN "Prc_T_MaterialRequestItem" i ON i."Id" = rl."MaterialRequestItemId"
|
||
INNER JOIN "Prc_T_MaterialRequest" r ON r."Id" = i."MaterialRequestId"
|
||
LEFT JOIN LATERAL (
|
||
SELECT po."Id", po."OrderNo", po."Status", po."SupplierId", ol."ReceivedQuantity"
|
||
FROM "Prc_T_PurchaseRfqLine" al
|
||
INNER JOIN "Prc_T_PurchaseOrderLine" ol ON ol."PurchaseRfqLineId" = al."Id" AND ol."IsDeleted" = false
|
||
INNER JOIN "Prc_T_PurchaseOrder" po ON po."Id" = ol."PurchaseOrderId" AND po."IsDeleted" = false
|
||
WHERE al."TenderLineId" = COALESCE(rl."TenderLineId", rl."Id") AND al."IsDeleted" = false
|
||
ORDER BY CASE WHEN po."Status" = 'Cancelled' THEN 1 ELSE 0 END, po."CreationTime" DESC
|
||
LIMIT 1) o ON true
|
||
WHERE rl."IsDeleted" = false
|
||
UNION ALL
|
||
SELECT tl."Id", tl."TenantId", i."Id", i."MaterialRequestId", r."RequestNo", r."BranchId", r."DepartmentId", r."WarehouseId",
|
||
r."LocationId", i."ProductId", i."DueDate", CAST(CASE WHEN t."SourceLocationId" = t."DestinationLocationId" THEN 'Stock' ELSE 'Transfer' END AS varchar(16)), t."Id", t."TransferNo",
|
||
t."Status", NULL, t."SourceLocationId",
|
||
NULL, NULL, NULL, tl."DemandQuantity", tl."UomId",
|
||
CASE WHEN t."Status" = 'Done' THEN tl."Quantity" ELSE 0 END, tl."CreationTime", tl."CreatorId", tl."IsDeleted"
|
||
FROM "Stk_T_TransferLine" tl
|
||
INNER JOIN "Stk_T_Transfer" t ON t."Id" = tl."TransferId" AND t."IsDeleted" = false AND t."OperationKind" = 'InternalTransfer' AND t."ReturnOfId" IS NULL
|
||
INNER JOIN "Prc_T_MaterialRequestItem" i ON i."Id" = tl."MaterialRequestItemId"
|
||
INNER JOIN "Prc_T_MaterialRequest" r ON r."Id" = i."MaterialRequestId"
|
||
WHERE tl."IsDeleted" = false;
|