51 lines
2.9 KiB
PL/PgSQL
51 lines
2.9 KiB
PL/PgSQL
-- 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 REPLACE PROCEDURE public."Stk_P_TransferCheckAvailability"(p_id uuid)
|
||
LANGUAGE plpgsql AS $$
|
||
DECLARE v_src uuid; v_kind varchar(32); v_policy varchar(16); v_tenant uuid; v_all boolean; v_any boolean;
|
||
BEGIN
|
||
SELECT "SourceLocationId", "OperationKind", "DeliveryPolicy", "TenantId" INTO v_src, v_kind, v_policy, v_tenant
|
||
FROM "Stk_T_Transfer" WHERE "Id" = p_id;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM "Stk_T_Location" WHERE "Id" = v_src AND "LocationType" = 'Internal') THEN
|
||
UPDATE "Stk_T_TransferLine" SET "Quantity" = "DemandQuantity" WHERE "TransferId" = p_id AND "IsDeleted" = false AND "Quantity" = 0;
|
||
UPDATE "Stk_T_Transfer" SET "Status" = 'Ready' WHERE "Id" = p_id;
|
||
RETURN;
|
||
END IF;
|
||
|
||
DROP TABLE IF EXISTS tmp_inv_fill;
|
||
CREATE TEMP TABLE tmp_inv_fill AS
|
||
WITH d AS (
|
||
SELECT l."Id", l."ProductId", l."UomId", p."UomId" AS "ProductUomId", p."TrackInventory", l."DemandQuantity",
|
||
public."Stk_F_UomConvert"(l."DemandQuantity", l."UomId", p."UomId") AS "DemandBase",
|
||
SUM(public."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" = p_id AND l."IsDeleted" = false),
|
||
s AS (
|
||
SELECT "ProductId", SUM("Quantity") AS "OnHand"
|
||
FROM "Stk_T_Stock"
|
||
WHERE "LocationId" = v_src AND "TenantId" IS NOT DISTINCT FROM v_tenant
|
||
GROUP BY "ProductId")
|
||
SELECT d."Id",
|
||
CASE WHEN d."TrackInventory" = false OR COALESCE(s."OnHand", 0) >= d."CumBase" THEN d."DemandQuantity"
|
||
WHEN COALESCE(s."OnHand", 0) > d."CumBase" - d."DemandBase"
|
||
THEN public."Stk_F_UomConvert"(COALESCE(s."OnHand", 0) - (d."CumBase" - d."DemandBase"), d."ProductUomId", d."UomId")
|
||
ELSE 0 END AS "Fill",
|
||
d."DemandQuantity" AS "Demand"
|
||
FROM d
|
||
LEFT JOIN s ON s."ProductId" = d."ProductId";
|
||
|
||
UPDATE "Stk_T_TransferLine" l SET "Quantity" = f."Fill" FROM tmp_inv_fill f WHERE f."Id" = l."Id";
|
||
|
||
v_all := NOT EXISTS (SELECT 1 FROM tmp_inv_fill WHERE "Fill" < "Demand");
|
||
v_any := EXISTS (SELECT 1 FROM tmp_inv_fill WHERE "Fill" > 0);
|
||
DROP TABLE tmp_inv_fill;
|
||
|
||
UPDATE "Stk_T_Transfer"
|
||
SET "Status" = CASE WHEN v_all OR (v_kind = 'Delivery' AND COALESCE(v_policy, 'Direct') = 'Direct' AND v_any) THEN 'Ready' ELSE 'Waiting' END
|
||
WHERE "Id" = p_id;
|
||
END;
|
||
$$;
|