If you ever worked with PER_PHONES table in Oracle EBS to get the employee phones, you must have seen that PER_PHONES table contains multiple lines for different phone types for an employee.
For example, if I run the following query for an employee whose PERSON_ID is 1444, I get three records:
SELECT *
FROM PER_PHONES p
WHERE p.parent_id = 1444;
You can run the following query to get the meaning of the phone types:
SELECT lookup_type,
lookup_code,
meaning
FROM HR_LOOKUPS
WHERE enabled_flag = 'Y'
AND lookup_type = 'PHONE_TYPE';
Now the problem is, when you connect this PER_PHONES (parent_id) table with PER_ALL_PEOPLE_F (person_id) table , you end up with multiple rows. In the above example, it will return three rows.
Now of course, the client will never want to see more than one row per employee. In my case, the client specifically asked for a condition which goes like: pick up Mobile Phone of the employee first; if Mobile Phone is NULL, then pick up Home Phone; if Home Phone is NULL, then pick up Work Phone, and so on.
I assumed that there would already be a view for this from Oracle. Since I did not find any, I had to come up with a query (to create a custom view) to avoid multiple rows per employee, using LEFT OUTER JOIN function.
Hopefully, this is helpful. However, if there is any other query which may be useful, I would certainly love to hear or know about it.
Now of course, the client will never want to see more than one row per employee. In my case, the client specifically asked for a condition which goes like: pick up Mobile Phone of the employee first; if Mobile Phone is NULL, then pick up Home Phone; if Home Phone is NULL, then pick up Work Phone, and so on.
I assumed that there would already be a view for this from Oracle. Since I did not find any, I had to come up with a query (to create a custom view) to avoid multiple rows per employee, using LEFT OUTER JOIN function.
SELECT
pp.parent_id parent_id,
pp.parent_table parent_table,
--
ppm.phone_number mobile_phone,
pph.phone_number home_phone,
ppw.phone_number work_phone,
ppo.phone_number other_phone
FROM
(SELECT DISTINCT
parent_id,
parent_table
FROM per_phones
) pp
-- mobile phone
LEFT OUTER JOIN per_phones ppm
ON ( ppm.phone_type = 'M'
AND ppm.parent_id = pp.parent_id
AND ppm.parent_table =
pp.parent_table
)
-- home phone
LEFT OUTER JOIN per_phones pph
ON ( pph.phone_type = 'H1'
AND pph.parent_id = pp.parent_id
AND pph.parent_table =
pp.parent_table
)
-- work phone
LEFT OUTER JOIN per_phones ppw
ON ( ppw.phone_type = 'W1'
AND ppw.parent_id = pp.parent_id
AND ppw.parent_table =
pp.parent_table
)
-- other phone
LEFT OUTER JOIN per_phones ppo
ON ( ppo.phone_type = 'O'
AND ppo.parent_id = pp.parent_id
AND ppo.parent_table =
pp.parent_table
)
WHERE 1 = 1;
Hopefully, this is helpful. However, if there is any other query which may be useful, I would certainly love to hear or know about it.