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;

Flexfields – Key

Key Flexfields

Flexfields > Key > Segments

At its most basic, a list of Key Flexfields, without the list of Segments

This SQL is useful as it shows you the underlying application table name associated with the Key Flexfield.

    SELECT fat.application_name
         , fif.id_flex_name flexfield_title
         , fif.id_flex_code
         , application_table_name
         , fif.*
      FROM applsys.fnd_id_flexs fif 
      JOIN applsys.fnd_application_tl fat ON fif.application_id = fat.application_id
     WHERE 1 = 1
       AND fif.id_flex_code = 'GL#'
--       AND fif.id_flex_code = 'MSTK'
--       AND fif.id_flex_name LIKE 'Accounting%'
       AND 1 = 1;

Key Flexfields – Structures

This lists the Structures associated with a Key Flexfield

Each Structure can have different options – e.g. Enabled, Separator etc

    SELECT '------> KEY FLEXFIELD'
         , fat.application_name
         , fif.id_flex_name flexfield_title
         , fif.id_flex_code
         , '------> STRUCTURE'
         , fifsv.id_flex_num
         , fifsv.id_flex_structure_code code
         , fifsv.id_flex_structure_name title
         , fifsv.description
         , fifsv.structure_view_name view_name
         , fifsv.concatenated_segment_delimiter segment_separator
         , fifsv.freeze_flex_definition_flag freeze_flexfield_definition
         , fifsv.cross_segment_validation_flag cross_validate_segments
         , fifsv.enabled_flag enabled
         , fifsv.freeze_structured_hier_flag freeze_rollup_groups
         , fifsv.last_update_date
      FROM applsys.fnd_id_flexs fif 
      JOIN applsys.fnd_application_tl fat       ON fif.application_id = fat.application_id
      JOIN apps.fnd_id_flex_structures_vl fifsv ON fif.id_flex_code =   fifsv.id_flex_code
     WHERE 1 = 1
       AND fif.id_flex_code = 'GL#'
--       AND fif.id_flex_code = 'MSTK'
       AND 1 = 1;

From the above view “Key Flexfield Segments”, if you click into a Structure Name, and then click on “Segments”, you can see the related Segments

For example, for the “System Items” Key Flexfield in Inventory, we can see that the Application Column Name is “SEGMENT1”.

We know that the application table name is “MTL_SYSTEM_ITEMS_B”.

Therefore, the actual item number / reference is stored in the Segment1 column of the MTL_SYSTEM_ITEMS_B table.

Key Flexfields – Structures and Segments

    SELECT '------> KEY FLEXFIELD'
         , fat.application_name
         , fif.id_flex_name flexfield_title
         , fif.id_flex_code
         , '------> STRUCTURE'
         , fifsv.id_flex_num
         , fifsv.id_flex_structure_code segment_code
         , fifsv.id_flex_structure_name segment_title
         , '------> SEGMENTS'
         , fifsvl.segment_num "NUMBER"
         , fifsvl.segment_name name
         , fifsvl.form_left_prompt prompt
         , fifsvl.application_column_name 
         , fnd_set.flex_value_set_name
         , fifsvl.display_flag displayed
         , fifsvl.enabled_flag enabled
      FROM applsys.fnd_id_flexs fif
      JOIN applsys.fnd_application_tl fat       ON fif.application_id =         fat.application_id
      JOIN apps.fnd_id_flex_structures_vl fifsv ON fif.id_flex_code =           fifsv.id_flex_code
      JOIN apps.fnd_id_flex_segments_vl fifsvl  ON fifsvl.id_flex_code =        fifsv.id_flex_code AND fifsvl.id_flex_num = fifsv.id_flex_num
 LEFT JOIN applsys.fnd_flex_value_sets fnd_set  ON fifsvl.flex_value_set_id =   fnd_set.flex_value_set_id
     WHERE 1 = 1
       AND fif.id_flex_code = 'GL#'
--       AND fif.id_flex_code = 'MSTK'
       AND 1 = 1;

From the above, you can see that a Segment can have a List of Values associated with it

When that happens, you can drill down to see the records in that List of Values

Key Flexfields – Structures, Segments and Values

Flexfields > Key > Values

    SELECT '------> KEY FLEXFIELD'
         , fat.application_name
         , fif.id_flex_name flexfield_title
         , fif.id_flex_code
         , fifsv.id_flex_num
         , '------> STRUCTURE'
         , fifsv.id_flex_structure_code
         , fifsv.id_flex_structure_name
         , '------> SEGMENTS'
         , fifsvl.segment_name name
         , fifsvl.form_left_prompt prompt
         , fifsvl.application_column_name 
         , fnd_set.flex_value_set_name
         , fifsvl.display_flag displayed
         , fifsvl.enabled_flag enabled
         , '------> VALUES'
         , fnd_value.flex_value
         , fnd_value_tl.description
         , fnd_value.end_date_active
         , fnd_value.enabled_flag
         , fnd_value.summary_flag parent
         , SUBSTR(replace(replace(fnd_value.compiled_value_attributes,chr(10),''),chr(13),' '),1,1) budg_flag
         , SUBSTR(replace(replace(fnd_value.compiled_value_attributes,chr(10),''),chr(13),' '),2,1) post_flag
      FROM applsys.fnd_id_flexs fif
      JOIN applsys.fnd_application_tl fat          ON fif.application_id =          fat.application_id
      JOIN apps.fnd_id_flex_structures_vl fifsv    ON fif.id_flex_code =            fifsv.id_flex_code
      JOIN apps.fnd_id_flex_segments_vl fifsvl     ON fifsvl.id_flex_code =         fifsv.id_flex_code AND fifsvl.id_flex_num = fifsv.id_flex_num
 LEFT JOIN applsys.fnd_flex_value_sets fnd_set     ON fifsvl.flex_value_set_id =    fnd_set.flex_value_set_id
      JOIN applsys.fnd_flex_values fnd_value       ON fnd_value.flex_value_set_id = fnd_set.flex_value_set_id
      JOIN applsys.fnd_flex_values_tl fnd_value_tl ON fnd_value.flex_value_id     = fnd_value_tl.flex_value_id
     WHERE fif.id_flex_code = 'GL#'
       AND fnd_value.flex_value = 'AA00002';

Flexfields

Flexfield Introduction

There are 3 main types of Flexfields:

  • Key
    • Segments
    • Values
  • Descriptive
    • Segments
    • Values
  • Validation
    • Sets
    • Values

As taken from:

https://docs.oracle.com/cd/E18727_01/doc.121/e12892/T354897T361274.htm

Flexfield Definitions

A flexfield is a field made up of sub-fields, or segments.

There are two types of flexfields: key flexfields and descriptive flexfields.

A key flexfield appears on your form as a normal text field with an appropriate prompt.

A descriptive flexfield appears on your form as a two-character-wide text field with square brackets [ ] as its prompt.

  • When opened, both types of flexfield appear as a pop-up window that contains a separate field and prompt for each segment.
  • Each segment has a name and a set of valid values.
  • The values may also have value descriptions.

Key Flexfields

Most organizations use “codes” made up of meaningful segments (intelligent keys) to identify general ledger accounts, part numbers, and other business entities.

Each segment of the code can represent a characteristic of the entity.

For example, your organization might use the part number PAD-NR-YEL-8 1/2×14″ to represent a notepad that is narrow-ruled, yellow, and 8 1/2″ by 14″.

Another organization may identify the same notepad with the part number “PD-8×14-Y-NR”.

Both of these part numbers are codes whose segments describe a characteristic of the part.

Although these codes represent the same part, they each have a different segment structure that is meaningful only to the organization using those codes.

The Oracle E-Business Suite stores these “codes” in key flexfields. Key flexfields are flexible enough to let any organization use the code scheme they want, without programming.

Descriptive Flexfields

Descriptive flexfields provide customizable “expansion space” on your forms.

You can use descriptive flexfields to track additional information, important and unique to your business, that would not otherwise be captured by the form.

Descriptive flexfields can be context sensitive, where the information your application stores depends on other values your users enter in other parts of the form.

A descriptive flexfield appears on a form as a single-character, unnamed field enclosed in brackets.

Just like in a key flexfield, a pop-up window appears when you move your cursor into a customized descriptive flexfield.

And like a key flexfield, the pop-up window has as many fields as your organization needs.

Each field or segment in a descriptive flexfield has a prompt, just like ordinary fields, and can have a set of valid values.

Your organization can define dependencies among the segments or customize a descriptive flexfield to display context-sensitive segments, so that different segments or additional pop-up windows appear depending on the values you enter in other fields or segments.

Database Info

-- ##############################################################################
--     DBA SYSTEM / SESSION INFO
-- ##############################################################################

SELECT * FROM v$version;
SELECT * FROM v$database;
SELECT * FROM v$instance;
SELECT * FROM v$parameters;
SELECT * FROM global_name;
SELECT * FROM v$parameter ORDER BY NAME;
SELECT * FROM v$session s WHERE s.audsid = USERENV ('sessionid');

-- https://stackoverflow.com/questions/16565829/is-there-a-way-to-get-information-about-a-server-using-sql
-- https://oracle-base.com/articles/misc/identifying-host-names-and-addresses

-- #############################################################################
--        DATABASE DETAILS
-- #############################################################################

-- database version
SELECT * FROM v$version;

-- operating system
select rtrim(substr(replace(banner,'TNS for ',''),1,instr(replace(banner,'TNS for ',''),':')-1)) os
  from v$version
 where banner like 'TNS for %';

-- product details
SELECT * FROM product_component_version;

-- #############################################################################
--        INSTANCE DETAILS
-- #############################################################################

-- instance info (instance name, host name, version, startup time etc.)
SELECT * FROM v$instance;

-- about license limits of the current instance.
SELECT * FROM v$license;

-- #############################################################################
--        DATABASE NAME
-- #############################################################################

-- database name
SELECT * FROM GLOBAL_NAME;

-- database ip address
SELECT UTL_INADDR.get_host_address FROM dual;

--Db Host Name.
SELECT UTL_INADDR.GET_HOST_NAME('80.75.69.150') FROM dual;

-- #############################################################################
--        CLIENT INFO
-- #############################################################################

-- ip address of the client machine
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

-- operating system identifier for the current session. This is often the client machine name
SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

-- host name of the client machine
SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;

-- host name of the server running the database instance
SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;