Skip to main content

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;

 

Hi ​@sahango,

Can you please check with the below getter function only.

PURCHASE_REQ_LINE_CFP.GET_CF$_NESTING_NO(REQUISITION_NO,LINE_NO,RELEASE_NO)

 

Regards
Abdul Rehman

Hi ​@sahango,

Can you please check with the below getter function only.

PURCHASE_REQ_LINE_CFP.GET_CF$_NESTING_NO(REQUISITION_NO,LINE_NO,RELEASE_NO)

 

Regards
Abdul Rehman

Thanx for attention. I didn’t quite understand how to check it properly. I guess it was created by itself. The function is below.

FUNCTION Get_Cf$_Nesting_No (
   requisition_no_ IN VARCHAR2,
line_no_ IN VARCHAR2,
release_no_ IN VARCHAR2 ) RETURN VARCHAR2  
IS
   temp_ VARCHAR2(32000);
   CURSOR get_attr IS
   select B.CF$_NESTING_NO from PURCHASE_REQ_LINE_PART_CFV B inner join PURCHASE_REQ_LINE_ALL C on C.REQUISITION_NO = B.REQUISITION_NO   and C.LINE_NO = B.LINE_NO   and C.RELEASE_NO = B.RELEASE_NO where C.REQUISITION_NO = requisition_no_   and C.LINE_NO = line_no_   and C.RELEASE_NO = release_no_;
BEGIN
   OPEN get_attr;
   FETCH get_attr INTO temp_;
   CLOSE get_attr;
   RETURN temp_;
END Get_Cf$_Nesting_No;

 

Hi ​@sahango,

Yes, this is a system-generated getter function.

Enter the getter function in the Expression Statement directly, and publish your custom field.

PURCHASE_REQ_LINE_CFP.GET_CF$_NESTING_NO(REQUISITION_NO,LINE_NO,RELEASE_NO)

 

 

There is no need to write a separate function for this.

 

Regards

Abdul Rehman

Hi ​@sahango,

Yes, this is a system-generated getter function.

Enter the getter function in the Expression Statement directly, and publish your custom field.

PURCHASE_REQ_LINE_CFP.GET_CF$_NESTING_NO(REQUISITION_NO,LINE_NO,RELEASE_NO)

 

 

There is no need to write a separate function for this.

 

Regards

Abdul Rehman

I tried but the result same. 

 

Hi ​@sahango,

There is no need to add V. prefix to the getter parameters.

Simply copy and paste the below code in the expression statement directly without any modification.

PURCHASE_REQ_LINE_CFP.GET_CF$_NESTING_NO(requisition_no,req_line,req_release)

 

Regards

Abdul Rehman

Hi ​@sahango,

There is no need to add V. prefix to the getter parameters.

Simply copy and paste the below code in the expression statement directly without any modification.

PURCHASE_REQ_LINE_CFP.GET_CF$_NESTING_NO(requisition_no,req_line,req_release)

 

Regards

Abdul Rehman

Many thx but i tried the result is same. Finally i found the solution. I will add to topic.

I found the solution. It might be helpful for others as well. I couldn’t directly add the NESTING_NO column to the Purchase Order Lines table (PURCHASE_ORDER_LINE_ALL). Instead, I tried adding the NESTING_NO column to the PURCHASE_ORDER_LINE_PART table, which is located at the bottom of the Purchase Order screen, using the same arguments—and it worked. Then, using the ORDER_NO, RELEASE_NO, and LINE_NO as references, I brought the NESTING_NO information into the Purchase Order Lines table.

Reply


Terms & ConditionsCookie settings