SQL Validation used for a Profile Option’s List of Values

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s