Lookup Values

-- ##############################################################################
--      LOOKUPS
-- ##############################################################################

     select flt.lookup_type
          , fltt.description
          , fat.application_name app
          , flv.lookup_code
          , flv.meaning
          , flv.enabled_flag
          , TO_CHAR(start_date_active, 'DD-MON-RRRR') from_
          , TO_CHAR(end_date_active, 'DD-MON-RRRR') to_
          , flv.creation_date
          , fu_cr.user_name cr_by
          , flv.last_update_date
          , fu_up.user_name up_by
       from fnd_lookup_types flt 
       JOIN fnd_lookup_types_tl fltt ON flt.lookup_type = fltt.lookup_type
       JOIN fnd_application_tl fat on flt.application_id = fat.application_id
       JOIN fnd_lookup_values flv ON flv.lookup_type = flt.lookup_type
       JOIN fnd_user fu_cr ON flv.created_by = fu_cr.user_id
       JOIN fnd_user fu_up ON flv.last_updated_by = fu_up.user_id
      WHERE 1 = 1
        AND flt.LOOKUP_TYPE = 'BOB_HOPE_JOKES'
        AND 1 = 1;

User Logins

If a user is end-dated on a test system, and you remove the end-date from their account but don’t reset their password, you will show as the person who last updated the record even if that user logs in.

If you reset a user’s password, then when they log in they have to choose a new password. They then show as the last person to update their FND user account.

-- user, login details, login count

select fu.user_name
     , (select max(start_time) from applsys.fnd_logins fl where fl.user_id = fu.user_id) last_login
     , (select count(*) from applsys.fnd_logins fl where fl.user_id = fu.user_id) login_count
     , (select count(*) from applsys.fnd_logins fl where fl.user_id = fu.user_id AND start_time > TRUNC(SYSDATE)) login_count_today 
  from applsys.fnd_user fu
 where 1 = 1
--   AND fu.user_name IN ('BOBHOPE')
   AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE
   ORDER BY 3 DESC;

-- login details line level

SELECT fu.description
     , fu.user_name
     , fl.start_time
     , fl.*
  FROM applsys.fnd_logins fl
  JOIN applsys.fnd_user fu ON fl.user_id = fu.user_id 
 WHERE 1 = 1
   AND fl.login_type = 'FORM'
   AND fl.user_id > 0 -- NOT SYSADMIN
--   AND fu.user_name = 'BOBHOPE'
   AND fl.start_time > TRUNC (SYSDATE) - 1
   AND 1 = 1
ORDER BY fl.start_time DESC;

-- logins last x days

SELECT fu.description
     , fu.user_name
     , count(*) ct
  FROM applsys.fnd_logins fl
  JOIN applsys.fnd_user fu ON fl.user_id = fu.user_id
 WHERE 1 = 1
   AND fl.start_time > TRUNC (SYSDATE) - 5
   AND fl.login_type = 'FORM'
   AND fl.user_id > 0 -- NOT SYSADMIN
--   AND fu.user_name = 'BOBHOPE'
   AND 1 = 1
GROUP BY fu.description
     , fu.user_name
ORDER BY 3 DESC;

-- unsuccessful logins

SELECT fu.description
     , fu.user_name
     , ful.*
  FROM applsys.fnd_unsuccessful_logins ful
  JOIN applsys.fnd_user fu ON ful.user_id = fu.user_id 
 WHERE 1 = 1
--   AND fu.user_name = 'BOBHOPE'
ORDER BY ful.attempt_time DESC;


HR Locations

-- ##############################################################################
--        LOCATIONS BASIC DETAILS
-- ##############################################################################

      SELECT bus_gp.NAME bus_gp
           , inv_org.NAME inv_org
           , hla.location_code
           , hla.description
           , hla.inactive_date
           , hla.address_line_1
           , hla.address_line_2
           , hla.address_line_3
           , hla.town_or_city
           , hla.postal_code
        FROM hr.hr_locations_all hla
   LEFT JOIN hr.hr_all_organization_units_tl bus_gp ON hla.business_group_id = bus_gp.organization_id
   LEFT JOIN hr.hr_all_organization_units_tl inv_org ON hla.inventory_organization_id = inv_org.organization_id
        JOIN applsys.fnd_user fu ON hla.created_by = fu.user_id;

-- ##############################################################################
--      LOCATIONS - HR STAFF ASSIGNMENT LINKS
-- ##############################################################################

-- count

      SELECT hla.location_code
           , hla.description description
           , COUNT(*) assignment_count
        FROM hr.hr_locations_all hla
        JOIN hr.per_all_assignments_f paaf ON hla.location_id = paaf.location_id
        JOIN hr.per_all_people_f papf ON paaf.person_id = papf.person_id AND (SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date) AND (SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
       WHERE paaf.primary_flag = 'Y'
         AND paaf.assignment_type = 'E'
    GROUP BY hla.description
           , hla.location_code
    ORDER BY 3 DESC;

-- details

      SELECT papf.employee_number
           , papf.full_name
           , paaf.assignment_number
           , hla.location_code
           , hla.description description
        FROM hr.hr_locations_all hla
        JOIN hr.per_all_assignments_f paaf ON hla.location_id = paaf.location_id
        JOIN hr.per_all_people_f papf ON paaf.person_id = papf.person_id AND (SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date) AND (SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
       WHERE paaf.primary_flag = 'Y'
         AND paaf.assignment_type = 'E'
    ORDER BY 3 DESC;

-- ##############################################################################
--        LOCATIONS - HR ORG LINKS
-- ##############################################################################

-- count

      SELECT hla.location_code
           , hla.description description
           , COUNT(*) ct
        FROM hr.hr_locations_all hla
        JOIN hr.hr_all_organization_units haou ON haou.location_id = hla.location_id
    GROUP BY hla.description
           , hla.location_code
    ORDER BY 3 DESC;

-- details

      SELECT haou.name hr_org
           , hla.location_code
           , hla.description description
        FROM hr.hr_locations_all hla
        JOIN hr.hr_all_organization_units haou ON haou.location_id = hla.location_id;

SQL to Check for Invalid Email Settings for Users

Sometimes users have incorrect notification preferences set, so don’t receive email notifications. Or users might have incorrectly formatted email addresses. The SQLs can help with finding such issues.

-- ##############################################################################
--         INCORRECT E-MAIL SETTINGS
-- ##############################################################################

SELECT *
  FROM applsys.wf_local_roles
 WHERE notification_preference <> 'MAILHTML'
   AND orig_system = 'PER';
   
-- ##############################################################################
--        USERS WHERE NOTIFICATION_PREFERENCE IS NOT SET TO ‘MAILHTML’
-- ##############################################################################

SELECT fu.user_id
     , fu.user_name
     , wlr.notification_preference
     , fu.email_address fu_email
     , wlr.email_address wlr_email
  FROM applsys.fnd_user fu
     , applsys.wf_local_roles wlr
 WHERE wlr.NAME = fu.user_name
   AND wlr.orig_system = 'PER'
   AND wlr.notification_preference <> 'MAILHTML';

-- ##############################################################################
--        USERS WHERE THE EMAIL ADDRESS CONTAINS INVALID CHARACTERS.
--        https://community.oracle.com/thread/658934
-- ##############################################################################

SELECT fu.user_id
     , fu.user_name
     , wlr.notification_preference
     , wlr.email_address
     , DECODE(
          TRANSLATE(
             LOWER(wlr.email_address)
           , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'
           , '+'
          )
        , ' ', 'SPACE'
        , (
           TRANSLATE(
              LOWER(wlr.email_address)
            , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'
            , '+'
           )
          )
       ) invalid_character
  FROM applsys.fnd_user fu
     , applsys.wf_local_roles wlr
 WHERE wlr.NAME = fu.user_name
   AND wlr.orig_system = 'PER'
   AND TRANSLATE(
          LOWER(wlr.email_address)
        , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'
        -- + followed by all the acceptable characters
       ,  '+'
       ) IS NOT NULL;

--- another version

SELECT   fu.user_id
       , fu.user_name
       , wlr.notification_preference
       , wlr.email_address
       , NVL
            (
            DECODE(
               TRANSLATE(
                  LOWER(wlr.email_address)
                , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'''
                , '+'
               )
             , ' ', 'SPACE'
             , (
                TRANSLATE(
                   LOWER(wlr.email_address)
                 , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'''
                 , '+'
                )
               )
            )
          , 'NONE'
         ) invalid_character
       , CASE
            WHEN TRANSLATE(
                   LOWER(wlr.email_address)
                 , '+0123456789abcdefghijklmnopqrstuvwxyz/-'''
                 , '+'
                ) LIKE '%@.%'
            AND TRANSLATE(
                   LOWER(wlr.email_address)
                 , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'''
                 , '+'
                ) IS NULL
               THEN 'VALID'
            ELSE 'INVALID'
         END email_format
    FROM applsys.fnd_user fu
       , applsys.wf_local_roles wlr
   WHERE wlr.NAME = fu.user_name
     AND wlr.orig_system = 'PER'
     AND (
             (
              TRANSLATE(
                 LOWER(wlr.email_address)
               , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'''
               , '+'
              ) IS NOT NULL
             )
          OR (
              TRANSLATE(
                 LOWER(wlr.email_address)
               , '+0123456789abcdefghijklmnopqrstuvwxyz/-'''
               , '+'
              ) NOT LIKE '%@.%'
             )
         )
ORDER BY fu.user_id;

Operating Units

The following SQL won’t work, unless you either run this first: ALTER SESSION SET nls_language = 'AMERICAN';, or have your NLS Language setting set to American – e.g. in SQL Developer Settings:

SQL Developer NLS Settings

-- ##############################################################################
--      OPERATING UNITS
-- ##############################################################################

SELECT * FROM apps.hr_operating_units;

Business Groups

-- ##############################################################################
--      BUSINESS GROUPS
-- ##############################################################################

    SELECT hrl.country
         , hroutl_bg.name bg
         , hroutl_bg.organization_id
         , lep.legal_entity_id
         , lep.name legal_entity
         , hroutl_ou.name ou_name
         , hroutl_ou.organization_id org_id
         , hrl.location_id
         , hrl.location_code
         , hrl.description
         , glev.flex_segment_value
      FROM xle.xle_entity_profiles lep
      JOIN xle.xle_registrations reg ON lep.legal_entity_id = reg.source_id
      JOIN hr.hr_locations_all hrl ON hrl.location_id = reg.location_id
      JOIN ar.hz_parties hzp ON lep.party_id = hzp.party_id
      JOIN apps.hr_operating_units hro ON lep.legal_entity_id = hro.default_legal_context_id
      JOIN hr.hr_all_organization_units_tl hroutl_bg ON hroutl_bg.organization_id = hro.business_group_id
      JOIN hr.hr_all_organization_units_tl hroutl_ou ON hroutl_ou.organization_id = hro.organization_id
 LEFT JOIN gl.gl_legal_entities_bsvs glev ON glev.legal_entity_id = lep.legal_entity_id
     WHERE lep.transacting_entity_flag = 'Y'
       AND reg.source_table = 'XLE_ENTITY_PROFILES';

Folders

-- folders

    SELECT DISTINCT
           ff.object folder_set
         , ff.name folder_name
         , ff.public_flag public_
         , NVL2 (fdf.object, 'Y', '') default_folder
         , DECODE (ff.autoquery_flag,  'A', 'Ask',  'N', 'Never',  'Y', 'Always') autoqry
         , ff.creation_date cr_date
         , fu.description || ' (' || fu.user_name || ')' cr_by
      FROM applsys.fnd_folders ff
 LEFT JOIN applsys.fnd_default_folders fdf ON ff.folder_id = fdf.folder_id 
      JOIN applsys.fnd_user fu             ON ff.created_by = fu.user_id
     WHERE ff.name LIKE 'Misc%'
  ORDER BY ff.object, ff.name;

-- folders and columns

  SELECT ff.object folder_set
       , ff.folder_id
       , ff.creation_date cr_date
       , ff.name folder
       , ffc.item_name
       , ffc.item_prompt
       , ffc.sequence
       , ff.public_flag public_
       , DECODE (ff.autoquery_flag,  'A', 'Ask',  'N', 'Never',  'Y', 'Always') autoqry       
    FROM applsys.fnd_folders ff
    JOIN applsys.fnd_folder_columns ffc ON ff.folder_id = ffc.folder_id
   WHERE 1 = 1
     AND ff.name = 'MiscTransactions - non-project'
     AND 1 = 1
ORDER BY ff.object
       , ff.name
       , ff.folder_id
       , ffc.sequence;

-- col count per folder
  SELECT ff.object folder_set
       , ff.folder_id
       , ff.name folder
       , COUNT(*) ct       
    FROM applsys.fnd_folders ff
    JOIN applsys.fnd_folder_columns ffc ON ff.folder_id = ffc.folder_id 
GROUP BY ff.object
       , ff.folder_id
       , ff.name
ORDER BY ff.object
       , ff.name;

-- shared with...

  SELECT DISTINCT b.application_short_name appl
                , c.name folder_name
                , c.object folder_set
                , a.creation_date share_date
                , CASE
                     WHEN a.user_id LIKE '-%' THEN 'Resp'
                     WHEN a.user_id NOT LIKE '-%' THEN 'User'
                  END
                     shared_with      
                , CASE
                     WHEN a.user_id LIKE '-%'
                     THEN
                        (SELECT responsibility_name
                           FROM applsys.fnd_responsibility_tl frt
                          WHERE frt.responsibility_id = - (a.user_id))
                     WHEN a.user_id NOT LIKE '-%'
                     THEN
                        (SELECT description
                           FROM applsys.fnd_user fu
                          WHERE fu.user_id = a.user_id)
                  END
                     shared_with_details
    FROM apps.fnd_default_folders a
    JOIN apps.fnd_application b ON a.application_id = b.application_id
    JOIN apps.fnd_folders c     ON a.folder_id =      c.folder_id 
   WHERE b.application_short_name = 'PA'
ORDER BY 1,2,6;

-- FOLDERS SHARED WITH RESPONSIBILITIES
-- http://onlyappsr12.blogspot.com/p/usefull-fnd-queries.html
-- administer folders SQL

SELECT   b.application_short_name
       , d.responsibility_name
       , a.OBJECT
       , c.NAME folder_name
       , a.creation_date share_date
    FROM apps.fnd_default_folders a
    JOIN apps.fnd_application b       ON a.application_id =    b.application_id
    JOIN apps.fnd_folders c           ON a.folder_id =         c.folder_id
    JOIN apps.fnd_responsibility_vl d ON d.responsibility_id = -(a.user_id) 
   WHERE 1 = 1
ORDER BY b.application_short_name
       , d.responsibility_name;

Flexfields – Key – Find Natural and Balancing Segments

-- http://orclapp.blogspot.co.uk/2012/11/11510-sql-script-to-find-values-of.html
-- find natural and balancing segments for Key Flex Fields

  SELECT DISTINCT fifs.ID_FLEX_STRUCTURE_CODE,
         fsav.APPLICATION_COLUMN_NAME,
         ffsg.SEGMENT_NAME,
         DECODE (fsav.SEGMENT_ATTRIBUTE_TYPE,
                  'FA_COST_CTR', 'Cost Center Segment',
                  'GL_ACCOUNT', 'Natural Account Segment',
                  'GL_BALANCING', 'Balancing Segment',
                  'GL_INTERCOMPANY', 'Intercompany Segment',
                  'GL_SECONDARY_TRACKING','Secondary Tracking Segment',
                  'GL_MANAGEMENT', 'Management Segment',
                  fsav.SEGMENT_ATTRIBUTE_TYPE)
    FROM FND_SEGMENT_ATTRIBUTE_VALUES fsav,
         FND_ID_FLEX_STRUCTURES fifs,
         FND_ID_FLEX_SEGMENTS ffsg
   WHERE 1 = 1
     AND UPPER(fifs.ID_FLEX_STRUCTURE_CODE) = 'XX_ACCOUNTING_FLEXFIELD'
     AND fsav.ATTRIBUTE_VALUE = 'Y'
     AND segment_attribute_type NOT IN ('GL_GLOBAL', 'GL_LEDGER')
     AND fsav.ID_FLEX_NUM = fifs.ID_FLEX_NUM
     AND ffsg.ID_FLEX_NUM = fifs.ID_FLEX_NUM
     AND ffsg.APPLICATION_COLUMN_NAME = fsav.APPLICATION_COLUMN_NAME
ORDER BY application_column_name;

Flexfields – Validation

Value sets

Flexfields > Validation > Sets

-- SIMPLE LIST OF VALUE SETS

SELECT *
  FROM applsys.fnd_flex_value_sets ffvs
 WHERE 1 = 1 -- ffvs.flex_value_set_name = 'CE_BANK_BRANCHES'
ORDER BY flex_value_set_name;

Value Set Definition

Setup > Financials > Flexfields > Validation > Sets

-- VALUE SET DEFINITION
-- Setup > Financials > Flexfields > Validation > Sets

    SELECT ffvs.flex_value_set_name name
         , ffvs.description
         , DECODE (ffvs.longlist_flag
                 , 'N', 'List of Values'
                 , 'X', 'Poplist'
                 , 'Y', 'Long List of Values'
                 , 'Other')
              list_type
         , DECODE (ffvs.security_enabled_flag
                 , 'N', 'No Security'
                 , 'Y', 'Non-Hierarchical Security'
                 , 'H', 'Hierarchical Security'
                 , 'Other')
              security_type
         , DECODE (ffvs.format_type
                 , 'C', 'Char'
                 , 'D', 'Date'
                 , 'T', 'DateTime'
                 , 'N', 'Number'
                 , 'X', 'Standard Date'
                 , 'Y', 'Standard DateTime'
                 , 'I', 'Time'
                 , 'NULL')
              format_type
         , DECODE (ffvs.validation_type
                 , 'Y', 'Translatable Dependent'
                 , 'X', 'Translatable Independent'
                 , 'F', 'Table'
                 , 'U', 'Special'
                 , 'D', 'Dependent'
                 , 'I', 'Independent'
                 , 'N', 'None'
                 , 'P', 'Pair')
              validation_type
         , ffvs.maximum_size max_size
         , ffvs.number_precision precision
         , CASE WHEN ffvs.format_type = 'N' THEN 'Y' ELSE 'N' END numbers_only
         , ffvs.numeric_mode_enabled_flag right_justify
         , ffvs.uppercase_only_flag uppercase
         , ffvs.protected_flag
         , ffvs.security_enabled_flag
         , ffvs.uppercase_only_flag
         , ffvs.dependant_default_value
         , ffvs.dependant_default_meaning
         , ffvs2.flex_value_set_name independent_value_set
      FROM applsys.fnd_flex_value_sets ffvs
 LEFT JOIN applsys.fnd_flex_value_sets ffvs2 ON ffvs.parent_flex_value_set_id = ffvs2.flex_value_set_id 
     WHERE ffvs.flex_value_set_name LIKE 'XX_ACCOUNT%'
       AND 1 = 1;

Basic List of Values

Flexfields > Validation > Values

If you look at the Pick List, the list of items in the LOV is not your full list of Sets of Values as defined in the SQL above.

It only returns a list of List of Values where:

Validation Type IN ('Dependent'
, 'Independent'
,''Translatable Independent
,'Translatable Dependent')

PLUS anything where the Validation Type = 'Table'
AND the summary_allowed_flag on the fnd_flex_validation_tables = 'Y'

-- BASIC LIST OF VALUES

  SELECT flex_value_set_name
       , fnd_value.flex_value account_code
       , fnd_value_tl.description
       , fnd_value.enabled_flag
       , fnd_value.creation_date
       , fu.description created_by
    FROM applsys.fnd_flex_value_sets fnd_set
    JOIN applsys.fnd_flex_values fnd_value       ON fnd_set.flex_value_set_id = fnd_value.flex_value_set_id  
    JOIN applsys.fnd_flex_values_tl fnd_value_tl ON fnd_value_tl.flex_value_id = fnd_value.flex_value_id 
    JOIN applsys.fnd_user fu                     ON fnd_value.created_by = fu.user_id
   WHERE 1 = 1
     AND flex_value_set_name IN ('XX_PURCHASING_UNIT')
--     AND fnd_value_tl.description = 'My Cheese Collection'
     AND 1 = 1;

Table Validation Details

-- TABLE VALIDATION DETAILS

    SELECT ffvs.flex_value_set_name name
         , NVL (fat.application_name, 'n/a') tbl_app
         , ffvt.application_table_name tbl_name
         , ffvt.value_column_name
         , ffvt.meaning_column_name
         , ffvt.id_column_name
         , ffvt.additional_where_clause where_
         , ffvt.summary_allowed_flag
      FROM applsys.fnd_flex_value_sets ffvs
      JOIN applsys.fnd_flex_validation_tables ffvt ON ffvs.flex_value_set_id =    ffvt.flex_value_set_id 
 LEFT JOIN applsys.fnd_application_tl fat          ON ffvt.table_application_id = fat.application_id  
     WHERE 1 = 1
       AND ffvs.flex_value_set_name = 'XX_SCHOOL'
       AND 1 = 1;

Flexfields – Descriptive

Various parts of Oracle EBS allows you to link what are called Descriptive Flexfields or “DFFs” to data entities such as Purchase Orders, Requisitions, Purchase Orders, Receipts, Invoices and so on.

Flexfield Header

Flexfields > Descriptive > Segments

At its most basic level, this lists the Applications and Titles linked to a DFF

SELECT fat.application_name
     , fdfv.title
     , fdfv.descriptive_flexfield_name dff_name
     , fdfv.freeze_flex_definition_flag frozen_tick
     , fdfv.concatenated_segment_delimiter separator
     , fdfv.form_context_prompt prompt
     , fdfv.default_context_field_name ref_field
     , fdfv.context_required_flag required_tick
     , fdfv.context_user_override_flag displayed_tick
     , fdfv.context_synchronization_flag sync_tick
  FROM apps.fnd_descriptive_flexs_vl fdfv
  JOIN applsys.fnd_application_tl fat ON fdfv.application_id = fat.application_id 
 WHERE 1 = 1
--   AND fdfv.title = 'PO Headers'
   AND fat.application_name = 'Application Object Library'
   AND fdfv.title = 'Flexfield Segment Values'
   AND 1 = 1;

Flexfield Header and Contexts

Flexfields > Descriptive > Segments

Search for relevant Title, second half of the screen, under “Context Field Values” lists the main parts of the Flexfield

SELECT fat.application_name
     , fdfv.title
     , fdfv.descriptive_flexfield_name dff_name
     , fdfcv.descriptive_flex_context_code code
     , fdfcv.descriptive_flex_context_name name
     , fdfcv.description description
     , fdfcv.enabled_flag enabled
  FROM apps.fnd_descriptive_flexs_vl fdfv
  JOIN applsys.fnd_application_tl fat        ON fdfv.application_id =              fat.application_id 
  JOIN apps.fnd_descr_flex_contexts_vl fdfcv ON fdfcv.descriptive_flexfield_name = fdfv.descriptive_flexfield_name 
 WHERE 1 = 1
--   AND fdfv.title = 'PO Headers'
--   AND fdfcv.descriptive_flex_context_code = 'ACTIVITY'
   AND fat.application_name = 'Application Object Library'
   AND fdfv.title = 'Flexfield Segment Values'
   AND 1 = 1;

Flexfield Header, Contexts and Segments

Flexfields > Descriptive > Segments

Search for relevant Title, second half of the screen, under “Context Field Values” lists the main parts of the Flexfield

Click into a name on the “Context Field Values” section in the lower part of the screen, and click “Segments”

This lists the bits users see in Core Applications when they click into the DFF plus shows if there is a LOV linked to the field

I find this userful if I can see e.g. a prompt on a DFF and don’t know which field on the related table it sites on, I can search for the prompt on the DFF to find out.

      SELECT fat.application_name
           , fdfv.title
           , fdfcv.descriptive_flex_context_code
--           , fdfcv.description
           , fdfcuv.column_seq_num
           , fdfcuv.end_user_column_name
           , fdfcuv.application_column_name
           , ffvs.flex_value_set_name
           , ffvs.description value_set_description
           , fdfcuv.required_flag
           , fdfcuv.display_flag
           , fdfcuv.enabled_flag
           , fdfcuv.security_enabled_flag
           , fdfcuv.default_value
           , fdfcuv.last_update_date
           , fu.description
        FROM apps.fnd_descriptive_flexs_vl fdfv
        JOIN applsys.fnd_application_tl fat          ON fdfv.application_id =                 fat.application_id 
        JOIN apps.fnd_descr_flex_contexts_vl fdfcv   ON fdfv.descriptive_flexfield_name =     fdfcv.descriptive_flexfield_name
        JOIN apps.fnd_descr_flex_col_usage_vl fdfcuv ON fdfcv.descriptive_flexfield_name =    fdfcuv.descriptive_flexfield_name     AND fdfcuv.descriptive_flex_context_code =  fdfcv.descriptive_flex_context_code
        JOIN applsys.fnd_user fu                     ON fu.user_id =                          fdfcuv.last_updated_by
   LEFT JOIN applsys.fnd_flex_value_sets ffvs        ON fdfcv.descriptive_flex_context_code = fdfcuv.descriptive_flex_context_code  AND fdfcuv.flex_value_set_id =              ffvs.flex_value_set_id
       WHERE 1 = 1
--         AND fdfcuv.application_column_name = 'ATTRIBUTE1'
--         AND fat.application_name = 'Receivables'
--         AND fdfv.title = 'Flexfield Segment Values'
--         AND fdfcv.description = :bbbb
         AND LOWER(fdfcuv.end_user_column_name) = 'building'
--         AND fdfv.title = 'Flexfield Segment Values'
--         AND fdfv.title = 'Transaction Type Information'
--         AND fdfcuv.end_user_column_name = 'Budget Holder'
--         AND fdfcuv.end_user_column_name = 'Prepayment'
--         AND fdfcv.descriptive_flex_context_code = 'ACTIVITY'
--         AND fdfcuv.descriptive_flexfield_name = 'OKC Rule Developer DF'
--         AND fdfcuv.descriptive_flex_context_code = 'DST'
         AND 1 = 1
    ORDER BY fat.application_name
           , fdfv.title
           , fdfcv.descriptive_flex_context_code
           , fdfcuv.column_seq_num;