Apex session state issue

Question

Background info:

I have a form page and report page that are linked to two tables:

REASON_CODES

ID|NAME|PARENT_ID

This is a list of available reason codes for an issue entry. The PARENT_ID field allows the user to assign a parent/child relationship between codes.

ENTRIES

ID|RC1|RC2|DESCRIPTION|DATE

This table contains all information for an issue entry (this is the main table for the form and report). The 'RC' columns refer to parent and child reason code IDs, respectively.

On the Apex form, the RC fields are both text fields with autocomplete. Since these fields have no native cascade ability, we had to force such a relationship in the LOV definitions:

RC1:

SELECT ID
FROM REASON_CODES
WHERE PARENT_ID = 0

RC2:

SELECT   ID
FROM REASON_CODES
WHERE PARENT_ID = :p67_RC1

There was an issue in getting the RC2 LOV to populate, as it is dependent on the value in RC1. To help with this, an 'Update' button was added beside RC1 that submits the page without recording the entry on its table. After clicking 'Update' the LOV for RC2 correctly displays.

Issue:

The user selects an existing entry from the report page to edit and changes RC1 and presses 'Update' per their instructions. If they save the changes they have made, this works well. However, if they realize that they did this in error, they would press 'Cancel' instead. If they then go back into this entry from the report (or if they go into a completely different entry), the form field displays what had last been entered instead of what is in the database entry. This only resets if I save the entry or log out and start over.

Any thoughts on what could be causing this?

Answer 1

You've set up cascading LOVs through the native interface? You want to refer to the state of RC1 in RC2's query when it is refreshed? Add P67_RC1 to "Page items to submit" in RC2's definition.
This will let apex take the current value of RC1 and use this value in the query for RC2 when refreshing RC2's values after RC1 has changed.

Answer 2

Explanation

If your source value for a Page Item (which, can take the form of an input field, dropdown menu, etc.) comes from an SQL Query, its output is only used as a display value if there is no session-state value for that page item.

As such, if a user enters or modifies that value during a session, APEX now refers to that value from the user, and no longer the database value (which comes from the SQl Query).

Example

So let's say that you have a Page Item named Annual Salary, which is a number field. Let's assume that its value is a database query that is based on the user who's logged in:

1. On page load, the SQL Query runs, and outputs a number corresponding to the annual salary of that user in the database. Let's say that its is 200,000;
2. Now, the user clicks on the number field and types in 500,000, without saving the input.
3. If you reload the page, the value in Annual Salary will still display as 500,000.

So why did this happen? Because when the user typed in 500,000, that number became the session-state value of that Page Item. Even if you move back or reload the page, the user is still in the same session (hence the term "session-state value"), so the 500,000--being the session-state value--will persist.

Now, a session ends when you log-out of the application. That's the reason why if you log-out and log back in, the Annual Salary will display the database value (200,000), because the session value of the page item reverted to NULL.

Solving the Issue

To overcome this default setting in APEX, do the following:

1. From the developer view of the page, click on the Page Item;

2. Then in its properties, go to Source;

3. Under "Used", there's a dropdown list there with two options:

   • "Only when current value in session state is null"; and
   • "Always, replacing any existing value in session state"

4. As I mentioned, APEX defaults to the first option.

5. Change this value to the second one.

6. Hit "Save"

After doing this, APEX will now always reference the source (i.e. the database value from your SQL Query) as the display value. It could also be NULL, 0, some default text, or any other value you set the page item's source to. The only time the value will be changed is if the user saves his changes (because in that scenario, the database value has changed).

Hope this helps!

Answer 3

I found a solution to this issue. I added a process on the report page to clear the form page's cache upon loading:

APEX_UTIL.CLEAR_PAGE_CACHE (67);

Now the entry's data displays correctly each time it is selected.