Query to find DFF

Let's say, we need to find Descriptive Flexfield (DFF) called, "Further Job Information". In the following example, I am trying to get all the information for "US" context code.

The following query will display the DFF related information. You can try changing "fdfv.title" value too see different DFF.

-----------------------------------------------------------------------------

-- Query to find DFF information

-----------------------------------------------------------------------------

SELECT

       fdf.title                             "DFF Title",

       fdf.application_table_name            "Application Table",

       fdf.context_column_name               "Context Column Name",

       --

       fdfcu.descriptive_flex_context_code   "DFF Context Code",

       fdfcu.column_seq_num                  "Sequence",

       fdfcu.end_user_column_name            "Segment Name",

       fdfcu.application_column_name         "Column Name",

       --

       ffv.flex_value_set_name               "Value Set Name"

  FROM

       fnd_descr_flex_col_usage_vl   fdfcu,

       fnd_descriptive_flexs_vl      fdf,

       fnd_flex_value_sets           ffv

 WHERE

       1 = 1

   --

   AND fdf.title = 'Further Job Information'        -- <change it>

   AND fdfcu.descriptive_flex_context_code = 'US'   -- <change it>

   AND fdfcu.enabled_flag = 'Y'

   --

   AND fdfcu.flex_value_set_id = ffv.flex_value_set_id

   AND fdfcu.descriptive_flexfield_name = fdf.descriptive_flexfield_name

   AND fdfcu.application_id = fdf.application_id

   --

 ORDER BY

       fdfcu.descriptive_flexfield_name,

       fdfcu.descriptive_flex_context_code,

       fdfcu.column_seq_num;

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.

BI Publisher Bursting

The following article, written by Gareth Roberts, is probably one of the best and most elaborate articles I have ever come across for BI Publisher's bursting feature. Very well written, step by step with screenshots and explanations.

http://garethroberts.blogspot.com/2008/03/bi-publisher-ebs-bursting-101.html