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

-- Tamamlanan siparişli alım ürünün tedarikçi fiyat listesini (Stk_T_ProductSupplier) günceller: tedarikçi × ürün × birim ×
-- para birimi başına tek satır tutulur. Alınan miktara uyan kademe (MinQuantity ≤ miktar olan en büyüğü) siparişin
-- birim fiyatı ve son alım tarihiyle güncellenir; uyan satır yoksa yeni satır açılır (temin süresi = onaydan alıma gün).
-- Fiyatı sipariş verir: siparişsiz alım, iade ve fiyatı 0 olan kalem yazmaz.
CREATE OR ALTER PROCEDURE [dbo].[Stk_P_TransferSupplierPrice]
    @Id uniqueidentifier,
    @UserId uniqueidentifier = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Now datetime = GETUTCDATE();
    DECLARE @Buy TABLE (TenantId uniqueidentifier NULL, ProductId uniqueidentifier NOT NULL, ContactId uniqueidentifier NOT NULL,
                        UomId nvarchar(64) NOT NULL, CurrencyId nvarchar(64) NOT NULL, Quantity decimal(18, 4) NOT NULL,
                        Price decimal(18, 4) NOT NULL, LeadTimeDays int NOT NULL, TargetId uniqueidentifier NULL,
                        PRIMARY KEY (ProductId, ContactId, UomId, CurrencyId));

    INSERT INTO @Buy (TenantId, ProductId, ContactId, UomId, CurrencyId, Quantity, Price, LeadTimeDays)
    SELECT b.TenantId, b.ProductId, b.SupplierId, b.UomId, b.CurrencyId, SUM(b.Quantity),
           ROUND(SUM(b.Quantity * b.UnitPrice) / SUM(b.Quantity), 4), MAX(b.LeadTimeDays)
      FROM (SELECT t.TenantId, ol.ProductId, o.SupplierId, ol.UomId, o.CurrencyId, ol.UnitPrice,
                   dbo.Stk_F_UomConvert(l.Quantity, l.UomId, ol.UomId) AS Quantity,
                   CASE WHEN o.ConfirmDate IS NULL OR o.ConfirmDate > @Now THEN 0 ELSE DATEDIFF(day, o.ConfirmDate, @Now) END AS LeadTimeDays
              FROM Stk_T_Transfer t
              INNER JOIN Stk_T_TransferLine l ON l.TransferId = t.Id AND l.IsDeleted = 0 AND l.Quantity > 0
              INNER JOIN Prc_T_PurchaseOrderLine ol ON ol.Id = l.PurchaseOrderLineId AND ol.UnitPrice > 0
              INNER JOIN Prc_T_PurchaseOrder o ON o.Id = ol.PurchaseOrderId
             WHERE t.Id = @Id AND t.OperationKind = 'Receipt' AND t.ReturnOfId IS NULL) b
     GROUP BY b.TenantId, b.ProductId, b.SupplierId, b.UomId, b.CurrencyId
    HAVING SUM(b.Quantity) > 0;

    UPDATE b SET TargetId = (SELECT TOP 1 ps.Id
                               FROM Stk_T_ProductSupplier ps
                               INNER JOIN Stk_T_Product p ON p.Id = ps.ProductId
                              WHERE ps.ProductId = b.ProductId AND ps.ContactId = b.ContactId AND ps.IsDeleted = 0 AND ps.VariantKey IS NULL
                                AND ISNULL(ps.UomId, p.UomId) = b.UomId AND ISNULL(ps.CurrencyId, b.CurrencyId) = b.CurrencyId
                                AND ps.MinQuantity <= b.Quantity
                                AND (ps.TenantId = b.TenantId OR (ps.TenantId IS NULL AND b.TenantId IS NULL))
                              ORDER BY ps.MinQuantity DESC)
      FROM @Buy b;

    UPDATE ps SET Price = b.Price, UomId = b.UomId, CurrencyId = b.CurrencyId, LastPurchaseDate = @Now,
                  LastModificationTime = @Now, LastModifierId = @UserId
      FROM Stk_T_ProductSupplier ps
      INNER JOIN @Buy b ON b.TargetId = ps.Id;

    -- Miktarı 1'in altındaki alımda 1'lik kademe uymaz; yeni kademe alınan miktardan başlar.
    INSERT INTO Stk_T_ProductSupplier (Id, TenantId, ProductId, ContactId, MinQuantity, UomId, Price, CurrencyId, LeadTimeDays,
           LastPurchaseDate, CreationTime, CreatorId, IsDeleted)
    SELECT NEWID(), b.TenantId, b.ProductId, b.ContactId, CASE WHEN b.Quantity < 1 THEN b.Quantity ELSE 1 END, b.UomId, b.Price,
           b.CurrencyId, b.LeadTimeDays, @Now, @Now, @UserId, 0
      FROM @Buy b
     WHERE b.TargetId IS NULL;
END
GO