View Object not updating when there is changes in DB

10.4.7 How to Automatically Refresh the View Object of the View Accessor

If you need to ensure that your view accessor always queries the latest data from the lookup table, you can set the Auto Refresh property on the destination view object. This property allows the view object instance to refresh itself after a change in the database. The typical use case is when you define a view accessor for the destination view object.

Because the auto-refresh feature relies on the database change notification feature, observe these restrictions when enabling auto-refresh for your view object:

• The view objects should query as few read-only tables as possible. This will ensure the best performance and prevent the database invalidation queue from becoming too large.
• The database user must have database notification privileges. For example, to accomplish this with a SQL*Plus command use grant change notification to <user name>.

When these restrictions are observed, the refresh is accomplished through the Oracle database change notification feature. Prior to executing the view object query, the framework will use the JDBC API to register the query for database notifications. When a notification arrives, the row sets of the corresponding view object instance are marked for refresh during the next checkout of the application module. Because the shared application module waits until the next checkout, the row set currency of the current transaction is maintained and the end user is not hampered by the update.

For example, assume that an LOV displays a list of zip codes that is managed in read-only fashion by a database administrator. After the administrator adds a new zip code as a row to the database, the shared application module detects a time when there are no outstanding requests and determines that a pending notification exists for the view instance that access the list of zip codes; at that point, the view object refreshes the data and all future requests will see the new zip code.

To enable auto-refresh for a view instance of a shared application module:

1. In the Application Navigator, double-click the view object that you want to receive database change notifications.
2. In the overview editor, click the General navigation tab.
3. In the Property Inspector expand the Tuning section, and select True for the Auto Refresh property.

https://docs.oracle.com/cd/E16162_01/web.1112/e16182/bclookups.htm#ADFFD1622

It’s not enough to simply re-execute the master view object as it will not refresh the query collections of the child row sets. Leading to the possibility of out dated data cached earlier to be visible in the af:tree shown to the end user. To avoid such data inconsistency its necessary to refresh the child nodes as well, simply done by retrieving their respective row sets and refreshing them using the execute query.