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)"
