The error you encountered, oracle.xdo.XDOException: java.sql.SQLException: Invalid column index, typically occurs when there's a mismatch in the columns being referenced or returned within the SQL query or in the way BI Publisher is handling the data.

Here are some potential causes and solutions for the issue:

1. Invalid Column Index in Function Return:

The error may be related to how the GetValue1 function is defined or invoked. Ensure that the function is correctly handling the cursor and returning the expected value. Since the cursor c1 might not return any rows if the condition is not met, make sure to initialize the variable cc with a default value or handle this scenario explicitly.

FUNCTION GetValue1 (p_value VARCHAR2) RETURN VARCHAR2 IS

    CURSOR c1 IS

        SELECT 'aa' aa FROM dual WHERE p_value = 'ABC';

    cc VARCHAR2(100) := 'Default Value';  -- Initialize with a default value

BEGIN

    FOR x IN c1 LOOP

        cc := x.aa;

    END LOOP;

    RETURN cc;

EXCEPTION

    WHEN OTHERS THEN

        RETURN 'Error GetValue1: ' || p_value;

END GetValue1;

2. Check the Column Mappings in BI Publisher:

If the query works correctly in SQL Developer but fails in BI Publisher, ensure that all columns referenced in the SQL query are properly mapped in the data model of BI Publisher. Sometimes, BI Publisher expects specific column indices that may not align with the SQL query.

3. Check the Compatibility of the SQL Query:

Although the WITH clause and functions are valid in Oracle SQL, BI Publisher might not handle such constructs well in certain versions or configurations. Simplify the query, if possible, by removing the WITH clause and inline the function call directly within the main query.

4. Data Type or Conversion Issues:

Ensure that the data types returned by the function and used in the query are consistent and do not require any implicit conversions that could lead to errors.

5. Check for BI Publisher Patch or Configuration Issues:

Sometimes, specific versions of BI Publisher may have bugs or configuration issues that lead to such errors. Ensure that you have the latest patches installed and check Oracle's support site for any known issues related to your BI Publisher version.

Final Query:

Here’s how you might modify the function to be more robust and ensure the query structure is BI Publisher-friendly:

WITH 

    FUNCTION GetValue1 (p_value VARCHAR2) RETURN VARCHAR2 IS

        CURSOR c1 IS

            SELECT 'aa' aa FROM dual WHERE p_value = 'ABC';

        cc VARCHAR2(100) := 'Default Value';

    BEGIN

        FOR x IN c1 LOOP

            cc := x.aa;

        END LOOP;

        RETURN cc;

    EXCEPTION

        WHEN OTHERS THEN

            RETURN 'Error GetValue1: ' || p_value;

    END GetValue1

------------


, PO AS

    (SELECT * FROM po_headers_all)


, POLines AS

    (SELECT * FROM po_lines_all)


--Main Query

SELECT po.segment1, polines.po_header_id , GetValue1('ABC') GetValue

FROM PO, POLines

WHERE polines.po_header_id = po.po_header_id

If this doesn’t resolve the issue, consider running the query outside of BI Publisher to see if it produces any errors or behavior that might indicate what’s going wrong.