Oracle APEX Workspace System Activities Application

Application Overview

I created an application to allow internal company developers and workspace administrators to monitor all applications built in a workspace and maintain users on security-based roles, if enabled for an application. Workspace administrators can also manage workspace users.
Unfortunately, I cannot provide a demonstration of the application since it is about all applications in a workspace that is protected information. However, I can tell you how to get started with some built-in Oracle APEX features and of course, my own creativity of enhancements and improvements to make this a better system application, especially for a productional workspace. If you are interested in my version of the System Activities Administration Application, please feel free to contact us for a private demonstration and we will be happy to provide you our working coping of the application and assist in the implementation in your own workspace for a nominal fee if you like. See Home Page for a screenshot of the
main page.

Getting started with Oracle APEX features

In the Oracle APEX App Builder, click the Create link icon to enable
the app wizard. On the next page enter an application name, such as System Activities Administration. Then click the link Use Create App Wizard. See screenshot below.

The next screenshot II below is the App Wizard. Make sure the application Name is correct and check the 5 features,

1. Install Progressive Web App,
2. Access Control
3. Feedback
4. Push Notifications
5. Activity Reporting.

Next click the green Create Application button.

Wait a few seconds and the new application is created.
Now before you run the application let’s make an important change that will allow you to see all the added features after logging into the application.

1) Edit the application in the App Builder
a. Locate the Home page.
i. Edit page to delete page by clicking on the Utilities icon and Delete Page from the pop-up menu.
b. Locate the Administration page (page 10000).
i. Edit the page Identification section properties as shown below. *Make sure the page Alias is home

Now you are ready to run the application and look around! Notice that this does NOT currently serve as a System utility but as an application utility. My Enhancements and Improvements section will convert this to a System utility.

Enhancements and Improvements

1. Edit application in App Builder
2. In shared components, edit the Breadcrumb bar, Administration page #10000 change the short name to System Administration. Click Apply Changes.
3. In shared components, create a list of values.
   • Name – WORKSPACE APPLICATION
   • Database source – Local
   • Source type – SQL query
   • SQL query – select DISTINCT APPLICATION_ID, APPLICATION_NAME from APEX_WORKSPACE_ACTIVITY_LOG L
   • Return – APPLICATION_ID
   • Display – APPLICATION_NAME
   • Default sort – APPLICATION_NAME
4. Add a select list page item to the follow pages:
   • Page #10000 see Home screenshot below for location.
     • Name – <PAGE#>_APP_ID
     • List of Values section Type – Shared Component
     • List of Values section List of Values – WORKSPACE APPLICATION
     • IF page has a timeframe: Locate existing Change Filters dynamic action and include the new <PAGE#>_APP_ID in item(s) under the When section ELSE Create a Change Filters dynamic action and add the new <PAGE#>_APP_ID in item(s) under the When section; and add true refresh action for all affected regions or types.
     • Add dynamic actions to refresh regions when a dialog is closed that affects certain home page regions. Note: Only regions on the left side are affected
   • Page #10010 – Location next to timeframe.
   • Page #10011 – Location next to timeframe
   • Page #10012 – Location next to timeframe.
   • Page #10013 – Location next to timeframe
   • Page #10014 – Location next to timeframe.
   • Page #10022 – hidden. Make sure to refresh the roles after the existing APPLICATION_ID by converting it to a select list and changing the behavior property to dynamic action that will refresh roles for selected application.
   • Page #10033 – Location top of page.

As you can see from the Home page a couple more interactive report informational pages. I also added a classic report region for a list of ACL users and a button to Manage APEX End users along with a few modal window pages for added administrative functionality. Since the vast majority of pages are modal and all pages are referenced from the Home page, I did not see the need for a navigation menu and removed it from the application.

Home Page afterwards

Password Tip

If end users of APEX applications are not prompted to change their passwords on their first login
to the system and user accounts are using native APEX Authentication, and when logging in and
authenticating, APEX fails to prompt them to change their passwords even if the individual user
account property “Require Change of Password on First Use” is set to “Yes.”
Check to verify the “Account Expiration and Locking” property at either the Instance or
Workspace level is not enabled. When the “Account Expiration and Locking” property at either
the Instance or Workspace is not enabled, password expiration or “Require Change of Password
on First Use” policies are not enforced.
In order to enforce the “Require Change of Password on First Use” property on APEX
Authenticated users, the “Account Expiration and Locking” property must be set to “Enabled”
either at the APEX Instance level, or in your individual workspaces. If set to “Enabled” at the
Instance level, it cannot be overridden at the workspace level. If set to “Disabled” at the Instance
level, Workspace Administrators are free to choose whether to enable it in their individually
managed workspaces.

Check APEX Instance Administration

Manage Instance -> Security -> Workspace Login Control -> Require User Account Expiration
and Locking

Check APEX Workspace Administration

Administration -> Manage Service -> Set Workspace Preferences -> Account Login Control ->
Account Expiration and Locking

Information from Oracle Support

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=112397227969565&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=1946117.1&_afrWindowMode=0&_adf.ctrl-state=9led0qaaw_4