sozsoft-platform/configs/seeds/sql/object/Prc_V_MaterialRequestPlanTrack.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 ALTER VIEW [dbo].[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 nvarchar(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 uniqueidentifier) AS SourceLocationId,
       o.Id AS PurchaseOrderId, o.OrderNo AS PurchaseOrderNo, o.Status AS OrderStatus, rl.Quantity, rl.UomId,
       ISNULL(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 = 0 AND q.Id = ISNULL(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
  OUTER APPLY (SELECT TOP 1 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 = 0
                 INNER JOIN Prc_T_PurchaseOrder po ON po.Id = ol.PurchaseOrderId AND po.IsDeleted = 0
                WHERE al.TenderLineId = ISNULL(rl.TenderLineId, rl.Id) AND al.IsDeleted = 0
                ORDER BY CASE WHEN po.Status = 'Cancelled' THEN 1 ELSE 0 END, po.CreationTime DESC) o
 WHERE rl.IsDeleted = 0
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 nvarchar(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 = 0 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 = 0