LEFT OUTER JOIN: Oracle HR Employee Phones

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 1=1

   AND 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 1=1

   AND lookup_type  = 'PHONE_TYPE'

   AND enabled_flag = 'Y';

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.

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.