sozsoft-platform/configs/seeds/postgres/object/Prc_P_PurchaseRfqCreateAlternative.sql

-- Alternatif Oluştur › Tedarikçiler'de seçilen kontak (p_key = teklif|kontak) için belgenin kopyasını aynı ihalede açar: kalemler TenderLineId ile
-- birlikte kopyalanır, fiyat tedarikçinin fiyat listesinden, para birimi ve koşullar kontak kartından gelir.
CREATE OR REPLACE PROCEDURE public."Prc_P_PurchaseRfqCreateAlternative"(p_key varchar, p_user_id uuid DEFAULT NULL)
LANGUAGE plpgsql AS $$
DECLARE v_id uuid; v_supplier uuid; v_tenant uuid; v_status varchar(16); v_group uuid; v_new uuid := gen_random_uuid(); v_no varchar(128);
BEGIN
  IF p_key !~* '^[0-9a-f-]{36}\|[0-9a-f-]{36}$' THEN
    RAISE EXCEPTION 'Siparişe dönüştürülmüş ya da iptal edilmiş teklife alternatif eklenemez.' USING ERRCODE = 'P0001', DETAIL = '50164';
  END IF;
  v_id := left(p_key, 36)::uuid;
  v_supplier := right(p_key, 36)::uuid;

  SELECT "TenantId", "Status", "TenderGroupId" INTO v_tenant, v_status, v_group FROM "Prc_T_PurchaseRfq" WHERE "Id" = v_id;
  IF COALESCE(v_status, '') NOT IN ('Draft', 'Sent') THEN
    RAISE EXCEPTION 'Siparişe dönüştürülmüş ya da iptal edilmiş teklife alternatif eklenemez.' USING ERRCODE = 'P0001', DETAIL = '50164';
  END IF;
  IF EXISTS (SELECT 1 FROM "Prc_T_PurchaseRfq" WHERE "TenderGroupId" = v_group AND "SupplierId" = v_supplier AND "IsDeleted" = false AND "Status" <> 'Cancelled') THEN
    RAISE EXCEPTION 'Bu tedarikçinin ihalede zaten bir teklif talebi var.' USING ERRCODE = 'P0001', DETAIL = '50167';
  END IF;

  CALL public."Adm_P_SequenceNext"('Prc.PurchaseRfq', v_tenant, v_no);

  INSERT INTO "Prc_T_PurchaseRfq" ("Id", "TenantId", "RfqNo", "TenderGroupId", "SupplierId", "AgreementId", "BranchId", "OperationTypeId", "WarehouseId", "CurrencyId", "OrderDeadline",
         "ExpectedArrival", "BuyerUserId", "ProjectId", "IncotermId", "IncotermLocation", "PaymentTermId",
         "Terms", "PriceWeight", "DeliveryWeight", "CreationTime", "CreatorId", "IsDeleted")
  SELECT v_new, r."TenantId", v_no, r."TenderGroupId", c."Id", NULL, r."BranchId", r."OperationTypeId", r."WarehouseId", COALESCE(c."SupplierCurrency", r."CurrencyId"), r."OrderDeadline",
         r."ExpectedArrival", r."BuyerUserId", r."ProjectId",
         COALESCE(c."PurchaseIncotermId", r."IncotermId"), COALESCE(c."PurchaseIncotermLocation", r."IncotermLocation"),
         COALESCE(c."PurchasePaymentTermId", r."PaymentTermId"), r."Terms", r."PriceWeight", r."DeliveryWeight",
         NOW() AT TIME ZONE 'UTC', p_user_id, false
    FROM "Prc_T_PurchaseRfq" r
    INNER JOIN "Adm_T_Contact" c ON c."Id" = v_supplier
   WHERE r."Id" = v_id;

  INSERT INTO "Prc_T_PurchaseRfqLine" ("Id", "TenantId", "PurchaseRfqId", "TenderLineId", "ProductId", "Description", "Quantity", "UomId",
         "UnitPrice", "TaxRate", "Amount", "DeliveryDate", "MaterialRequestItemId", "CreationTime", "CreatorId", "IsDeleted")
  SELECT gen_random_uuid(), l."TenantId", v_new, l."TenderLineId", l."ProductId", l."Description", l."Quantity", l."UomId",
         COALESCE((SELECT ps."Price" FROM "Stk_T_ProductSupplier" ps
                    WHERE ps."ProductId" = l."ProductId" AND ps."ContactId" = v_supplier AND ps."IsDeleted" = false
                    ORDER BY ps."MinQuantity" LIMIT 1), 0),
         l."TaxRate", 0, l."DeliveryDate", l."MaterialRequestItemId", NOW() AT TIME ZONE 'UTC', p_user_id, false
    FROM "Prc_T_PurchaseRfqLine" l
   WHERE l."PurchaseRfqId" = v_id AND l."IsDeleted" = false;

  CALL public."Prc_P_PurchaseRfqRecalc"(v_new);
END;
$$;