Record navigation: refinement

First off: Dan McGhan, Thanks ;-) I wouldn’t have figured this one out by myself!

Second: my previous posts included the package APEX_IR. It has now been replaced by APEX_IR_PKG because APEX_IR is a new API in apex 4.2!

Finally, the code has been cracked :-) : it IS possible to retrieve the currently active interactive report through SQL! This is actually stored in a preference, residing in wwv_flow_preferences$, but also retrievable through apex_util.get_preference.
Code below is how the report id is retrieved:

IF p_report_id IS NULL THEN
   BEGIN
      SELECT interactive_report_id
        INTO v_report_id
        FROM apex_application_page_ir
       WHERE application_id = p_app_id
         AND page_id = p_page_id;

      apex_debug_message.log_message('interactive report_id: '||v_report_id);

      lv_pref := apex_util.get_preference(p_preference => 'FSP_IR_'||p_app_id||'_P'||p_page_id||'_W'||v_report_id, p_user => p_app_user);
      lv_pref := substr(lv_pref, 1, instr(lv_pref, '_')-1);
      apex_debug_message.log_message(': '||lv_pref);

      SELECT report_id
        INTO v_report_id
        FROM apex_application_page_ir_rpt
       WHERE application_id = p_app_id
         AND page_id = p_page_id
         AND base_report_id = lv_pref
         AND session_id = p_session_id;
   EXCEPTION
      WHEN no_data_found THEN
         apex_debug_message.log_message('no IR id could be found. Check input parameters. -> end');
         RETURN;
   END;
ELSE
   v_report_id := p_report_id;
END IF;

The returned preference value could look like ‘3522014783654717____X’. We’re only concerned with the first value as this is the id of the currently active report. Note that the preference name uses a report id: this is the id of the interactive report itself and not of a saved or session instanced version. The id we get from the preference is the id of the SAVED version of the report that is instanced for the session of the user. If you take a look at how the ids and saved reports work below you’ll understand.

There is a lot of writing I have done already on these report structures, and there is more in the comments in the package, but here is another illustration:

APEX_APPLICATION_PAGE_IR
Metadata for Interactive Reports
Interactive Report Id:
Region Id:

APEX_APPLICATION_PAGE_IR_RPT
Metadata for saved reports and session instances of those
For example (from my sample app on apex.oracle.com):

•    Reports with no session_id are saved reports.
•    All reports are based on the same interactive report
•    All reports have a unique id: REPORT_ID
•    Default (Primary) and alternative have an own application user. Named reports use the name of the creator evidently. Note that in my example app the report "Only Space Two" is a PRIVATE report! You'll see this of course when you log in with the test user.
•    My own instances of the reports are in the view aswell. You can identify them because they have a session_id, base_report_id and their report_type.
•    The base_report_id is the report_id of the report it is based on. "Report it is based on": the report_id of a saved version of the interactive report


Debugging from sql command line is still possible. There are two changes in the parameter list: p_app_user and p_report_id have been added. App_user is straightforward, but p_report_id is not. Leaving p_report_id blank from a command line to resolve the report_id is not possible. The preference fetch will only work in an apex session. You’ll have to retrieve the id yourself.
To do this, basically take the code block above, but query wwv_flow_preferences$  instead of using apex_util.

SELECT attribute_value
FROM apex_040100.wwv_flow_preferences$
WHERE user_id='YOUR_APEX_USERNAME' 
AND preference_name like 'FSP_IR_130_P11%'

Use preference_name like 'FSP_IR_130_P11_W5555555555' if you have retrieved the base interactive report id (that’d be from apex_application_page_ir). And of course, substitute the application and page id!

DECLARE
   v_next      VARCHAR2(50);
   v_prev      VARCHAR2(50);
   v_top       VARCHAR2(50);
   v_bot       VARCHAR2(50);
   v_cur_tot   VARCHAR2(50);
   v_debug     VARCHAR2(5000);
   v_binds     DBMS_SQL.VARCHAR2_TABLE;
   v_binds_val DBMS_SQL.VARCHAR2_TABLE;
BEGIN
    v_binds(1) := 'P52_COMPROD_ID';
    v_binds_val(1) := '106672'; 

    apex_ir_pkg.get_navigation_values
   (
      p_app_id      => 190,
      p_session_id  => 4133013019922250,
      p_column_id   => 'ID', --column id for IR!
      p_value       => 153876,
      p_page_id     => 52, 
      p_report_id => 55555555555555,
      p_app_user => ‘YOUR_APEX_USERNAME’, --APP_USER in apex session
      p_use_session_state => FALSE,      
      p_binds       => v_binds,
      p_binds_val   => v_binds_val,
      p_next        => v_next,
      p_prev        => v_prev,
      p_top         => v_top,
      p_bot         => v_bot,
      p_cur_tot     => v_cur_tot,
      p_debug       => v_debug 
   );   

   dbms_output.put_line('v_next: '||v_next);
   dbms_output.put_line('v_prev: '||v_prev);
   dbms_output.put_line('v_top: '||v_top);
   dbms_output.put_line('v_bot: '||v_bot);
   dbms_output.put_line('v_cur_tot: '||v_cur_tot);
   dbms_output.put_line('v_debug : '||v_debug );
END;

Sample application:  http://apex.oracle.com/pls/apex/f?p=54687:30

Download: HERE