sozsoft-platform/configs/seeds/sql/object/Stk_P_TransferCheckAvailability.sql

-- Yapılacak miktarı doldurur ve belgeyi Hazır ya da Bekleyen yapar. Rezervasyon yoktur; Tamamla stoğu yeniden kontrol eder.
-- Kaynak konum stok tutmuyorsa (dış konum) yapılacak miktar talep kadardır. İç konumdan çıkışta satırlar ürün bazında sırayla
-- karşılanır; tümü karşılanırsa Hazır olur. Teslimatta "Mümkün olduğunca kısa sürede" politikası kısmi karşılamada da Hazır yapar.
CREATE OR ALTER PROCEDURE [dbo].[Stk_P_TransferCheckAvailability]
    @Id uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Src uniqueidentifier, @Kind nvarchar(32), @Policy nvarchar(16), @Tenant uniqueidentifier, @All bit, @Any bit;

    SELECT @Src = t.SourceLocationId, @Kind = t.OperationKind, @Policy = t.DeliveryPolicy, @Tenant = t.TenantId
      FROM Stk_T_Transfer t WHERE t.Id = @Id;

    IF NOT EXISTS (SELECT 1 FROM Stk_T_Location WHERE Id = @Src AND LocationType = 'Internal')
    BEGIN
        UPDATE Stk_T_TransferLine SET Quantity = DemandQuantity WHERE TransferId = @Id AND IsDeleted = 0 AND Quantity = 0;
        UPDATE Stk_T_Transfer SET Status = 'Ready' WHERE Id = @Id;
        RETURN;
    END

    DECLARE @Fill TABLE (Id uniqueidentifier PRIMARY KEY, Fill decimal(18, 4) NOT NULL, Demand decimal(18, 4) NOT NULL);

    WITH d AS (
        SELECT l.Id, l.ProductId, l.UomId, p.UomId AS ProductUomId, p.TrackInventory, l.DemandQuantity,
               dbo.Stk_F_UomConvert(l.DemandQuantity, l.UomId, p.UomId) AS DemandBase,
               SUM(dbo.Stk_F_UomConvert(l.DemandQuantity, l.UomId, p.UomId))
                   OVER (PARTITION BY l.ProductId ORDER BY l.CreationTime, l.Id ROWS UNBOUNDED PRECEDING) AS CumBase
          FROM Stk_T_TransferLine l
          INNER JOIN Stk_T_Product p ON p.Id = l.ProductId
         WHERE l.TransferId = @Id AND l.IsDeleted = 0),
    s AS (
        SELECT ProductId, SUM(Quantity) AS OnHand
          FROM Stk_T_Stock
         WHERE LocationId = @Src AND (TenantId = @Tenant OR (TenantId IS NULL AND @Tenant IS NULL))
         GROUP BY ProductId)
    INSERT INTO @Fill (Id, Fill, Demand)
    SELECT d.Id,
           CASE WHEN d.TrackInventory = 0 OR ISNULL(s.OnHand, 0) >= d.CumBase THEN d.DemandQuantity
                WHEN ISNULL(s.OnHand, 0) > d.CumBase - d.DemandBase
                    THEN dbo.Stk_F_UomConvert(ISNULL(s.OnHand, 0) - (d.CumBase - d.DemandBase), d.ProductUomId, d.UomId)
                ELSE 0 END,
           d.DemandQuantity
      FROM d
      LEFT JOIN s ON s.ProductId = d.ProductId;

    UPDATE l SET Quantity = f.Fill FROM Stk_T_TransferLine l INNER JOIN @Fill f ON f.Id = l.Id;

    SET @All = CASE WHEN EXISTS (SELECT 1 FROM @Fill WHERE Fill < Demand) THEN 0 ELSE 1 END;
    SET @Any = CASE WHEN EXISTS (SELECT 1 FROM @Fill WHERE Fill > 0) THEN 1 ELSE 0 END;

    UPDATE Stk_T_Transfer
       SET Status = CASE WHEN @All = 1 OR (@Kind = 'Delivery' AND ISNULL(@Policy, 'Direct') = 'Direct' AND @Any = 1) THEN 'Ready' ELSE 'Waiting' END
     WHERE Id = @Id;
END