I want to add a NESTING_NO column to the Purchase Order Lines table.
The same column already exists in the Purchase Part Requisition Lines table.
If a Purchase Order is created from a Purchase Requisition, the Purchase Order Lines table includes references to the requisition using the REQUISITION_NO, REQ_RELEASE, and REQ_LINE columns.
My goal is to populate the NESTING_NO value in the Purchase Order Lines table based on these referenced values.
To achieve this, I wrote a PL/SQL function and tried to use it in the Expression field while adding a Custom Field.
The function works correctly and returns expected values. However, when I try to add the column using the Custom Field interface, IFS throws the following error:
As a test, I changed the parameters from REQ_RELEASE to RELEASE_NO and from REQ_LINE to LINE_NO, and it worked correctly.
This leads me to believe that the issue is specifically related to the REQ_RELEASE and REQ_LINE columns.
This is the function:
FUNCTION GetNestNoFromPurReqLinePart(reqNo IN VARCHAR2,relNo IN VARCHAR2, lineNo IN VARCHAR2) RETURN VARCHAR2
IS
v_nesting_no VARCHAR2(100);
BEGIN
SELECT CF$_NESTING_NO
INTO v_nesting_no
FROM PURCHASE_REQ_LINE_PART_CFV
WHERE REQUISITION_NO = reqNo and RELEASE_NO = relNo and LINE_NO = lineNo;
RETURN v_nesting_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
RETURN 'ERROR: TOO MANY ROWS';
WHEN OTHERS THEN
RETURN 'ERROR: ' || SQLERRM;
END GetNestNoFromPurReqLinePart;
