User Accounts – Basic Details

-- ##############################################################################
--      BASIC USER ACCOUNTS
-- ##############################################################################

     select fu.user_id
          , fu.user_name
          , fu.description
          , fu.email_address
          , fu.employee_id
          , fu.last_logon_date
          , to_char(fu.start_date, 'DD-MON-YYYY') start_date
          , to_char(fu.end_date, 'DD-MON-YYYY') end_date
          , fu.password_lifespan_days pwd_days
          , fu.password_date
          , ppx.full_name hr_full_name
       from fnd_user fu
  left join per_people_x ppx on fu.employee_id = ppx.person_id
      where 1 = 1
--        and lower(fu.email_address) like '%bob%'
        and fu.user_name = 'BOBHOPE'
   order by fu.user_name;

GL Chart of Accounts

-- ##############################################################################
--        GL CHART OF ACCOUNTS - HEADER
-- ##############################################################################

    SELECT gl.ledger_id
         , gl.name ledger_name
         , gl.short_name ledger_short_name
         , gl.description ledger_descripption
         , 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 apps.fnd_id_flex_structures_vl fifsv
 LEFT JOIN gl.gl_ledgers gl ON gl.chart_of_accounts_id = fifsv.id_flex_num
     WHERE id_flex_code = 'GL#';

-- ##############################################################################
--        GL CHART OF ACCOUNTS - SEGMENTS SUMMARY
-- ##############################################################################

      SELECT fifsv.segment_num "NUMBER"
           , fifsv.segment_name name
           , fifsv.form_left_prompt prompt
           , fifsv.application_column_name 
           , fnd_set.flex_value_set_name
           , fifsv.display_flag displayed
           , fifsv.enabled_flag enabled
        FROM apps.fnd_id_flex_segments_vl fifsv
   LEFT JOIN applsys.fnd_flex_value_sets fnd_set ON fifsv.flex_value_set_id = fnd_set.flex_value_set_id
       WHERE 1 = 1
--         AND fifsv.id_flex_num = 50268
         AND fifsv.id_flex_code = 'GL#'
         AND fifsv.application_id = 101
         AND 1 = 1
    ORDER BY fifsv.segment_num;