Record Navigation in Oracle Apex

Update: if you didn't notice, i already posted a follow-up! This post is still relevant, my follow-up shows the plugin i made out of all this.
Update 2: newer, cleaner, improved: follow-up 2

One of my projects involved migrating a bunch of old Oracle Forms over to Oracle Apex. For example, we have a form here, Model.
Users would go in this form and query for models, using code, name, or any other field. Each field has the ability to be queried on (ie, refining the results). For example, here all models would be queried which would have a code starting with ‘V46’.
I translated this form into an Interactive Report with a Form page “underneath”. I added each column that was being queried in the old form, although I do no display each one. This provides users to query their records just as they used to do, only now by applying filters (which allow adding a filter on a non-displayed column). For example, I narrowed the results here to the same result-set I would’ve had in the old forms.
Take a note of columns “Status” and “Segments”. You can see how Status in the old form is a select list. When I looked in the source of the form, the values in the dropbox were a static list of elements.
Segments you can’t see on this screen, but it is on the Classification tab, and is an item with a List of Values on it – dynamical values.
These 2 columns were not alone: many others have an LOV on them.


This is the Query for my Interactive Report (IR):
select id model_id, null show_colours, line_code, size_code, code, name, descr_label, basemodel_id, commtyp_id, commercial_type,
commercial_type_label, initiator_code, guarantee, status, non_samsonite_code, extra_comment,
cat_length, expandable_length, cat_height, expandable_height, cat_width, expandable_width, cat_volume, expandable_volume,segments_id, prodtyp_code, maingroup_code, materltp_id, materlfr_id, linegrps_id, theme_id, gender_id, wheeltyp_id, seasonal_id
from model m
where m.model_type = 'NORMAL'
and m.line_id = NVL(:p40_line_id, m.line_id)
All the italics are columns based on LOVs. Now one way to have the IR display the correct associated value with those columns, would be to join tables, or write so-called ‘enhanced queries’:
SELECT id, null, …., (select description from genders where id = m.gender_id) gender, …
from model m
Not all those queries look as simple as that one though. So I opted for option 2, which wouldn’t clutter my SQL as much: set the item to display a value based off an LOV. For example, column Status: Display Type: Display as Text (based on LOV, escape special characters).


Status was based off static entries, so I created an LOV in the Shared Components of my application:





Segments were based off an LOV



I proceeded to treat the other columns the same, so now I have an IR with plenty of columns based on an LOV.
That was just the introduction however, on to my real beef.
When the users queried records in the old forms, they would move through the retrieved set only. So when they queried for ‘V46’ models, 45 records were retrieved. They would be on record 1, and could navigate back and forth (previous and next record) with the arrow keys, with respect to the “order by”-clause as defined in the form code.
How does this translate to Oracle Apex? Hard.
On my Models IR I have the default sort saved as such:


Fine and dandy, but then remember that users can create their own versions of reports, unless you take away that (powerfull) tool from your users. They can filter and sort, and then even save a private (or even public) version of that report.
Querying on for example the “Segments” isn’t hard either, just put a filter up like ‘segments = ‘not needed’’.
Now let’s go to the “underlying” form page of a record, let’s say record no 4 (encircled in blue on the IR report) with code ‘V46***005’. All  details are fetched etc, no problems. Now the user wants to be able to do a ‘previous’ and ‘next’ record. Why is this important to them? They would query for certain models, and then adjust fields for all those models, meaning they need those keys to be able to be productive.
So I thought, record navigation – easy! There is a provided process that creates everything I need: Processes -> Create -> Form Pagination.
You’d end up with this process:

Spot my glaring issues yet?

• Ordering is limited to 2 columns, and those are static unlike the 6 dynamic columns on the IR
• The where clause is static aswell, unlike the filtering options of the IR. You’d need to use the same where-clause you define on the IR query too, meaning twice the maintenance.

So to keep the same behavior possible for the users of the form, they’d:

• Filter and sort the IR
• Go to the detail of the first record
• Make their changes, and submit
• Click Cancel or use the Breadcrumb to return to the IR
• Go to the detail of the second record (“Oops, missclicked”, “Damn, already did this one”)
• Etc

They didn’t think this acceptable, and I agree. I and the users admit that moving to a new platform brings about changes, and adaption will need to happen. However, losing productivity like that is a big negative.
Which left me one choice only: create my own process which retrieves the previous and next values for the current record.
Thankfully, someone already figured out a large part of the work: Simon Hunt (SHUNT). You can find his work here: http://simonhunt.blogspot.com/2009/12/next-and-prev-from-interactive-report-2.html
It really put me on the right track!
But I ran into some issues (again!). His code does not take into account LOV-columns. My fault for going that route probably, but I didn’t feel like rewriting many queries and changing lots of items now (as I have more than just this one screen which has to react the same).
I took Simon’s code and added onto it the ability to handle those LOV columns. I also allowed the bind variables you can provide to be null by adding some extra parameters (the original code tested for null values – I couldn’t have that, my binds could be null).
This is the end result: I now have 5 buttons, handling everything I need.
On my IR, I filtered for code V46, and 45 records are found.


In my form, you can see ‘4 of 45’. Correct.


What was needed:

• The process fetching the details: Get Record Navigation IDs
• 5 items: ID_NEXT, PREV, TOP, BOT, Current_of_total
• 5 buttons: First, Previous, Current of total (bogus button just to show the current), Next, Last
• 4 branches to handle first, previous next and last

You could eliminate the processes by setting the buttons to redirect instead of submit. However, i tried to keep the style a bit in line with the standard process, which would generate 3 items, 2 buttons and 2 processes aswell. So in the end, you could have the same if you just ditch first and last.
This is just one way of handling it of course. I'd turn this in a plugin if i knew how to generate region buttons,page items branches and a process, but i don't :-)



This is what my page process looks like:


(Please note: i expect queries from the IR to be substitution variables free. So no #OWNER# in it. This is included in the comments of the code too. So take care when you generate an IR: take a look at the sql before trying this process)

And finally, the specs of the package I created. Everything is well documented too, so if you need to make changes to it you won’t be lost.
By far the largest problem was the mapping of the columns to the correct LOVs. Column and row filters, and column and row searches each needed their own handling to direct the queried column to their lov.
For example, querying on status would apply a filter like ‘status in (‘%use%’,’%obsolete%’)’ when status is actually (10, 20, 30, 40, 50, 60,…)!
Take a look in the code if you’re intrigued :-)
If you rather want to see it in action, go to my small demo app: here

create or replace PACKAGE apex_record_navigation
IS
   -- ++ M3.012 ++
   -- ++ Tom Petrus ++
   ------------------------------------------------------------------------------------------------
   /* Parses the sql and checks for the existence of a display and/or
      return value column.
      If the sql (which can be from a static or dynamic lov) does not
      contain a display value column, the return value one doubles as
      one.
      Valid values for:
      display value column: D, DISPLAY_VALUE
      return value column: R, RETURN_VALUE
      These values are what apex would require you to provide when
      creating an lov.
      The name for the display and return value columns are returned
      through the output variables.
   */
   PROCEDURE parse_sql_for_columns
   (
      p_sql          IN VARCHAR2,  -- the sql to be parsed for display/return columns
      o_display_col  OUT VARCHAR2, -- the column alias for the display column
      o_return_col   OUT VARCHAR2  -- the column alias for the return column
   );
   ------------------------------------------------------------------------------------------------
   /* Will parse the condition sql and search for columns which are based on
      an LOV. These columns then need to be remapped to the display value
      of those LOVs
   */
   FUNCTION get_ir_filter_lov_row
   (
      p_app_id          IN NUMBER,
      p_ir_bid          IN VARCHAR2, -- report BASE id
      p_condition_sql   IN VARCHAR2  -- condition sql
   )
   RETURN VARCHAR2;
   ------------------------------------------------------------------------------------------------
   /* It is possible to search on the displayed value of entries. For example, when there is a 'STATUS'
      with value 'In Use', then you can apply a filter on STATUS which only searches for 'Use' -> LIKE '%Use%'
      An exact match is POSSIBLE, but not necessarily!
      What has to happen:
      The filter condition which is applied on a column based on a LOV has to be applied to the
      DISPLAY_VALUEs of the LOV, NOT on the RETURN_VALUEs.
   */
   FUNCTION get_ir_filter_lov_col
   (
      p_app_id                IN NUMBER,
      p_named_lov             IN VARCHAR2,  -- the name of the referenced LOV
      p_condition_col_name    IN VARCHAR2,  -- the name of the (db)column being filtered
      p_condition_sql         IN VARCHAR2,  --
      p_condition_operator    IN VARCHAR2,
      p_condition_expression1 IN VARCHAR2,  -- expression is usually the value of the search
      p_condition_expression2 IN VARCHAR2   -- expression is usually the value of the search
   )
   RETURN VARCHAR2;
   ------------------------------------------------------------------------------------------------
   /* NEXT_PREV_VALUES:
      fetches the NEXT, PREVIOUS, TOP, BOTTOM, CURRENT and TOP values for the
      specified application+page+interactive report+column (usually an id column)
      When you specify filters and searches on an IR, you can't easily retrieve
      the next and previous values of a single record.
      For example, when you have a list of models and filter it down, and go to
      a detail page of a model (= a form page), you can't find the next and
      previous model within that filtered set. The standard form process in apex
      which provides record navigation does not offer navigation based off an IR
      either. It also has sorting limitations.
      
      Wish to use ROWID? Then make sure you have ROWID ALIASED in your query!
      Since the ir query will be made into a subquery, and ROWID is a pseudocolumn,
      it has to be aliased if it is to be selected out of this subquery.
      
      Substitution variables are NOT supported. If your query has been generated
      then there is a good chance it'll include #OWNER#. This procedure will 
      fail because i have provided no support for replacing those strings.
      Simply alter your region source and remove those strings.
      
      p_use_bvar1-4: specify TRUE when this bind variable has to be used
                     This is done so your bind var can have a NULL value
                     It is assumed that when you flag bindvar 3 as being
                     used, bind var 1 and 2 are also being used! There is
                     no conditional testing on which combinations of vars
                     are used!
   */
   PROCEDURE get_navigation_values
   (
      p_app_id       IN  NUMBER,   -- application id (APP_ID)
      p_session_id   IN  NUMBER,   -- session id (APP_SESSION)
      p_column_id    IN  VARCHAR2, -- the column for which to get the next/prev vals
      p_value        IN  VARCHAR2, -- The id value (for p_column_id) of the selected record: indicates current record
      p_page_id      IN  NUMBER,   -- Page number of the interactive report
      p_use_bvar1    IN  BOOLEAN  DEFAULT FALSE,
      p_bvar1        IN  VARCHAR2 DEFAULT NULL, -- Bind variable value1
      p_use_bvar2    IN  BOOLEAN  DEFAULT FALSE,
      p_bvar2        IN  VARCHAR2 DEFAULT NULL, -- Bind variable value2
      p_use_bvar3    IN  BOOLEAN  DEFAULT FALSE,
      p_bvar3        IN  VARCHAR2 DEFAULT NULL, -- Bind variable value3
      p_use_bvar4    IN  BOOLEAN  DEFAULT FALSE,
      p_bvar4        IN  VARCHAR2 DEFAULT NULL, -- Bind variable value4
      p_next         OUT VARCHAR2, -- next value
      p_prev         OUT VARCHAR2, -- previous value
      p_top          OUT VARCHAR2, -- top value: first record
      p_bot          OUT VARCHAR2, -- bottom value: last record
      p_cur_tot      OUT VARCHAR2, -- current of total: '4 of 132'
      p_debug        OUT VARCHAR2  -- Returns the final and adjusted executed query
   );
   ------------------------------------------------------------------------------------------------
END apex_record_navigation;

Download the code: prevnextlogic_V2.sql
Demo app: here