I get an Error in Function Code. Is there a solution?

[LS]

CREATE FUNCTION "LONGRAN2018"."GET_FIFO_STOCK_SOURCE" (
IN item_code NVARCHAR(60),
IN whs_code NVARCHAR(10),
IN required_qty DECIMAL(19,6)
)
RETURNS TABLE (
DocEntry INTEGER,
DocNum INTEGER,
DocDate DATE,
BaseEntry INTEGER,
BaseLine INTEGER,
Quantity DECIMAL(19,6),
UsedQty DECIMAL(19,6),
RemainingQty DECIMAL(19,6)
)
LANGUAGE SQLSCRIPT
AS
BEGIN
DECLARE remaining_qty DECIMAL(19,6) := :required_qty;

-- FIFO sırasına göre stok girişlerini getir (eski tarihliler önce)
fifo_stock = SELECT
T0.DocEntry,
T0.DocNum,
T0.DocDate,
T1.BaseEntry,
T1.BaseLine,
T1.Quantity,
T1.Quantity - T1.OpenQty AS UsedQty,
T1.OpenQty AS RemainingQty
FROM
OPDN T0
JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE
T1.ItemCode = :item_code
AND T1.WhsCode = :whs_code
AND T1.OpenQty > 0
ORDER BY
T0.DocDate ASC,
T0.DocNum ASC;

-- Sonuç tablosu oluştur
result = SELECT
DocEntry,
DocNum,
DocDate,
BaseEntry,
BaseLine,
Quantity,
UsedQty,
RemainingQty,
0 AS AllocationQty
FROM :fifo_stock
WHERE 1=0;

-- FIFO mantığı ile stok tahsisi yap
-- FOR cur_row AS CURSOR FOR
-- SELECT * FROM :fifo_stock;

DECLARE CURSOR cur_row FOR SELECT * FROM :fifo_stock;
OPEN cur_row;
DO
IF :remaining_qty <= 0 THEN
CLOSE cur_row;
BREAK;

END IF;

DECLARE alloc_qty DECIMAL(19,6);

-- Kullanılacak miktarı belirle
IF :cur_row.RemainingQty >= :remaining_qty THEN
alloc_qty := :remaining_qty;
remaining_qty := 0;
ELSE
alloc_qty := :cur_row.RemainingQty;
remaining_qty := :remaining_qty - :cur_row.RemainingQty;
END IF;

-- Sonuç tablosuna ekle
result = UNION_ALL(
:result,
SELECT
:cur_row.DocEntry,
:cur_row.DocNum,
:cur_row.DocDate,
:cur_row.BaseEntry,
:cur_row.BaseLine,
:cur_row.Quantity,
:cur_row.UsedQty,
:cur_row.RemainingQty,
:alloc_qty AS AllocationQty
FROM DUMMY
);
END FOR;


-- Sonuçları döndür
RETURN SELECT
DocEntry AS "Belge No",
DocNum AS "Belge Numarası",
DocDate AS "Belge Tarihi",
BaseEntry AS "Kaynak Belge No",
BaseLine AS "Kaynak Satır No",
Quantity AS "Orijinal Miktar",
UsedQty AS "Kullanılan Miktar",
RemainingQty AS "Kalan Miktar",
AllocationQty AS "Tahsis Edilen Miktar"
FROM :result;
END;

Could not execute 'CREATE FUNCTION "LONGRAN2018"."GET_FIFO_STOCK_SOURCE" ( IN item_code NVARCHAR(60), IN whs_code ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "DECLARE": line 60 col 5 (at pos 1754)

[CharlesFeng]

Hi,

As I don't have access to certain catalog objects, e.g OPDN, I am unable to test it directly.
After reviewing the code theoretically, I was wondering if you could please test the following changes to see if they work:

-------------------------------------
-- FIFO mantığı ile stok tahsisi yap
DECLARE cur_row CURSOR FOR SELECT * FROM :fifo_stock;

OPEN cur_row;
LOOP
FETCH cur_row INTO cur_row_row;
IF cur_row_row IS NULL THEN
CLOSE cur_row;
LEAVE;
END IF;

IF :remaining_qty <= 0 THEN
CLOSE cur_row;
LEAVE;
END IF;

DECLARE alloc_qty DECIMAL(19,6);

-- Kullanılacak miktarı belirle
IF :cur_row_row.RemainingQty >= :remaining_qty THEN
alloc_qty := :remaining_qty;
remaining_qty := 0;
ELSE
alloc_qty := :cur_row_row.RemainingQty;
remaining_qty := :remaining_qty - :cur_row_row.RemainingQty;
END IF;

-- Sonuç tablosuna ekle
result = UNION_ALL(
:result,
SELECT
:cur_row_row.DocEntry,
:cur_row_row.DocNum,
:cur_row_row.DocDate,
:cur_row_row.BaseEntry,
:cur_row_row.BaseLine,
:cur_row_row.Quantity,
:cur_row_row.UsedQty,
:cur_row_row.RemainingQty,
:alloc_qty AS AllocationQty
FROM DUMMY
);
END LOOP;
-------------------------------------

Best regards,
Charles