Friday, 29 April 2016

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.

/* Formatted on 4/29/2016 5:11:40 PM (QP5 v5.114.809.3010) */
  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;

Query to find Legal Entity, Organization, Company Code

/* Formatted on 4/29/2016 5:04:05 PM (QP5 v5.114.809.3010) */
  SELECT   xep.legal_entity_id "Legal Entity ID",
           xep.name "Legal Entity",
           hr_outl.name "Organization Name",
           hr_outl.organization_id "Organization ID",
           hr_loc.location_id "Location ID",
           hr_loc.country "Country Code",
           hr_loc.location_code "Location Code",
           glev.flex_segment_value "Company Code"
    FROM   xle_entity_profiles xep,
           xle_registrations reg,
           --
           hr_operating_units hou,
           -- hr_all_organization_units      hr_ou,
           hr_all_organization_units_tl hr_outl,
           hr_locations_all hr_loc,
           --
           gl_legal_entities_bsvs glev
   WHERE       1 = 1
           AND xep.transacting_entity_flag = 'Y'
           AND xep.legal_entity_id = reg.source_id
           AND reg.source_table = 'XLE_ENTITY_PROFILES'
           AND reg.identifying_flag = 'Y'
           AND xep.legal_entity_id = hou.default_legal_context_id
           AND reg.location_id = hr_loc.location_id
           AND xep.legal_entity_id = glev.legal_entity_id
           --
           -- AND hr_ou.organization_id         =  hou.business_group_id
           AND hr_outl.organization_id = hou.organization_id
ORDER BY   hr_outl.name