-- 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;