Roles Page - Validations and Primary Key

Roles page is a master detail pair of Interactive Grids.

Master is the Roles. Detail is the Admins.

In the Roles IG properties, the Master Region is the Roles Region.  This appears to be the only setting required to do the master detail coordination.

Validations required

role_name - must be lower case, alphabetic characters, numerals and underscore.  No leading or trailing spaces.

role_id - populated from sequence.

email_address - must be lower case with @oracle.com the domain, No leading or trailing spaces.

admin_id - populated from sequence.  This column was added to get the master detail coordination working in Apex, normally I wouldn't have used it, the original admin primary key of role_id with email_address didn't work in apex, the email address field would get populated with text of t100x by default. It seems there is some setting possibly of user updateable columns that I found on googling the issue but didn't play with it.

role_description - required.

to implement validation on role name, added Validation on the column in the interactive grid.

regular expression: ^[a-z0-9\_]+$
Error message: Role name is required, must be lower case with only a-z, 0-9 and underscores allowed.
The interactive grid provides a property named trim spaces. set it to trim leading and trailing

to implement validation on email address, added Validation on the column in the interactive grid.

regular expression: ^[a-z0-9._%+-]+@oracle.com$
Error message: Email address is required, it must be in lower case and must include the @oracle.com domain. No other domains allowed.
The interactive grid provides a property named trim spaces. set it to trim leading and trailing

Interactive grid provides default value and option of sequence. Used this for role_id and admin_id

To lower case a column value, the when using interactive grid, you create a process.  It's in the page processing section and you must ensure the sequence is earlier numerically than the generated save interative grid data processes.  The type is PL/SQL code, the actual code is:

:ROLE_NAME := lower(:ROLE_NAME);

In this case, because the regular expression only allowed lower case characters, this logic isn't needed.  An option would be to allow any case in the regular expression but convert it via the process (or a trigger) to lower case in the database.

Here's the syntax for a trigger to populate the id columns from a sequence as well as trimming and lowercasing the email address.

CREATE OR REPLACE TRIGGER INSERT_ADMIN_USERS
BEFORE INSERT ON ui_admin_users
FOR EACH ROW
DECLARE  admin_id NUMBER;
BEGIN  SELECT ui_config_seq.NEXTVAL
    INTO admin_id
    FROM dual;
  :NEW.admin_ID := admin_id;
END;
/

CREATE OR REPLACE TRIGGER admin_email_trim
BEFORE INSERT OR UPDATE ON ui_admin_users
FOR EACH ROW
BEGIN  :new.email_address := trim(lower(:new.email_address));
END;
/