50 lines
3.4 KiB
PL/PgSQL
50 lines
3.4 KiB
PL/PgSQL
-- 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 REPLACE PROCEDURE public."Stk_P_TransferSupplierPrice"(p_id uuid, p_user_id uuid DEFAULT NULL)
|
||
LANGUAGE plpgsql AS $$
|
||
DECLARE v_now timestamp := NOW() AT TIME ZONE 'UTC';
|
||
BEGIN
|
||
WITH buy AS (
|
||
SELECT b."TenantId", b."ProductId", b."SupplierId" AS "ContactId", b."UomId", b."CurrencyId", SUM(b."Quantity") AS "Quantity",
|
||
ROUND(SUM(b."Quantity" * b."UnitPrice") / SUM(b."Quantity"), 4) AS "Price", MAX(b."LeadTimeDays") AS "LeadTimeDays"
|
||
FROM (SELECT t."TenantId", ol."ProductId", o."SupplierId", ol."UomId", o."CurrencyId", ol."UnitPrice",
|
||
public."Stk_F_UomConvert"(l."Quantity", l."UomId", ol."UomId") AS "Quantity",
|
||
CASE WHEN o."ConfirmDate" IS NULL OR o."ConfirmDate" > v_now THEN 0
|
||
ELSE v_now::date - o."ConfirmDate"::date END AS "LeadTimeDays"
|
||
FROM "Stk_T_Transfer" t
|
||
INNER JOIN "Stk_T_TransferLine" l ON l."TransferId" = t."Id" AND l."IsDeleted" = false 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" = p_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
|
||
), target AS (
|
||
SELECT buy.*,
|
||
(SELECT ps."Id"
|
||
FROM "Stk_T_ProductSupplier" ps
|
||
INNER JOIN "Stk_T_Product" p ON p."Id" = ps."ProductId"
|
||
WHERE ps."ProductId" = buy."ProductId" AND ps."ContactId" = buy."ContactId" AND ps."IsDeleted" = false AND ps."VariantKey" IS NULL
|
||
AND COALESCE(ps."UomId", p."UomId") = buy."UomId" AND COALESCE(ps."CurrencyId", buy."CurrencyId") = buy."CurrencyId"
|
||
AND ps."MinQuantity" <= buy."Quantity"
|
||
AND ps."TenantId" IS NOT DISTINCT FROM buy."TenantId"
|
||
ORDER BY ps."MinQuantity" DESC
|
||
LIMIT 1) AS "TargetId"
|
||
FROM buy
|
||
), updated AS (
|
||
UPDATE "Stk_T_ProductSupplier" ps
|
||
SET "Price" = x."Price", "UomId" = x."UomId", "CurrencyId" = x."CurrencyId", "LastPurchaseDate" = v_now,
|
||
"LastModificationTime" = v_now, "LastModifierId" = p_user_id
|
||
FROM target x
|
||
WHERE x."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 gen_random_uuid(), x."TenantId", x."ProductId", x."ContactId", CASE WHEN x."Quantity" < 1 THEN x."Quantity" ELSE 1 END,
|
||
x."UomId", x."Price", x."CurrencyId", x."LeadTimeDays", v_now, v_now, p_user_id, false
|
||
FROM target x
|
||
WHERE x."TargetId" IS NULL;
|
||
END;
|
||
$$;
|