APEX

Hello,

I'm trying to dynamically return columns in an interactive report based on a page item value.

I've created a PLSQL block that builds a SQL query with a variable number of columns:

DECLARE
    v_years NUMBER;
    query VARCHAR2(5000) :=  'SELECT * FROM
                    (
                    SELECT
                        c.customer_num,
                        c.name AS customer_name,
                        sr.code AS rep_code,
                        sr.name AS rep_name,
                        SUM(DECODE( TO_CHAR(NVL(p.shipped_date, ''01-jan-49''), ''YYYY''),
                                      TO_CHAR(sysdate, ''YYYY''), ((ol.shipped_qty - ol.cancel_qty) * ol.price),
                                      0) ) "YTD_SHIPPED_AND_BOOKED_AMOUNT"';
    v_ty NUMBER := TO_NUMBER(TO_CHAR(sysdate, 'YYYY'));
BEGIN
    v_years := NVL(TO_NUMBER(:P2_YEARS), 1);
    
    FOR i IN 1..v_years LOOP
        query := query || ',';
        query := query || 'SUM(DECODE( TO_CHAR(NVL(p.shipped_date, ''01-jan-49''), ''YYYY''),
                      TO_CHAR(TO_NUMBER(TO_CHAR(sysdate, ''YYYY'')) - 1), (ol.shipped_qty * ol.price),
                      0) ) "' || TO_CHAR(v_ty - i) || '_SHIPPED_AMOUNT"';
    END LOOP;
                        
    query := query || '
                FROM
                    customers c
                    JOIN customer_mv cm ON c.customer_num = cm.customer_num
                    JOIN sales_reps sr ON cm.rep = sr.code
                    JOIN stores s ON cm.customer_num = s.customer_num
                    LEFT JOIN orders o ON s.store_num = o.store_num
                    JOIN order_splits os ON os.order_num = o.order_num
                    JOIN order_lines ol ON os.order_split_num = ol.order_split_num
                    LEFT JOIN pick_slips p ON ol.pick_slip_num = p.pick_slip_num
                    JOIN items_mv im ON ol.item_id = im.item_id
                WHERE
                    cm.status = ''ACTIVE''
                GROUP BY
                    c.customer_num,
                    c.name,
                    sr.code,
                    sr.name
                ) cl
                WHERE
                    YTD_SHIPPED_AND_BOOKED_AMOUNT > 0';
                    
    FOR i IN 1..v_years LOOP
        query := query || ' AND "' || TO_CHAR(v_ty - i) || '_SHIPPED_AMOUNT" > 0';
    END LOOP;
    
    --DBMS_OUTPUT.PUT_LINE (query);
    return query;
END;

After the BEGIN statement, I try to pull the value of :P2_YEARS which should be a number of years to generate results going back in time:

v_years := NVL(TO_NUMBER(:P2_YEARS), 1);

I believe :P2_YEARS hasn't been loaded yet, even though I'm passing it a value from another page. It's seemingly always evaluating to NULL, so I'm then defaulting it to my value of 1.

Is there a way to accomplish this functionality? Thank you!