Application developers can define their own Profile Options, and it’s often useful to define a list of values (LOV) to validate the value assigned to the profile option.
Conventional SQL is used to define these LOV’s, but the exact syntax is a little Applications-specific.
Here is an example, which is entered (Responsibility: Application Developer; Navigation Path: Profile) in the “SQL Validation used for the Profile Option’s List of Values” field:
SQL=" SELECT SUBSTR(qrslt.display_name||' ('||qrslt.email_address||')',1,80) \"Full Name\" , SUBSTR(qrslt.name,1,30) \"Name\" INTO :visible_option_value , :profile_option_value FROM ( SELECT display_name , name , email_address FROM wf_users WHERE orig_system = 'FND_USR' AND email_address IS NOT NULL AND status = 'ACTIVE' AND TRUNC(SYSDATE) >= TRUNC(start_date) AND SYSDATE < NVL(expiration_date, SYSDATE + 1) AND name = 'APHELPDESK' UNION SELECT display_name , name , email_address FROM wf_users WHERE orig_system = 'PER' AND email_address IS NOT NULL AND status = 'ACTIVE' AND TRUNC(SYSDATE) >= TRUNC(start_date) AND SYSDATE < NVL(expiration_date, SYSDATE + 1) ) qrslt ORDER BY 1" COLUMN="\"Full Name\"(30),\"Name\"(30)"