,

Oracle Application Directory Structure

Oracle Application has a file system as shown in the below picture for the APPL_TOP Directory.

GL_TOP: (APPL_TOP/GL/11.5.0) is one of the Module Directory of Oracle Applications. It consists of a release directory (i.e. 11.5.0) under which Forms, Reports, BIN, LIB, SQL, etc.,
Forms/US: Forms directory to store all .FMX (Compiled) Form files of a specific module.
Reports/US: Reports directory to capture all the .RDF (Compiled) Report files of a specific module directory. US is a language specific directory.
BIN: Contains executable code of concurrent programs written in a programming language such as C, Pro*C, Fortran, SQL *LOADER or an operating system script.
LIB: Contains compiled object code (.OBJ files) of your concurrent programs.
SQL: Contains concurrent programs written in SQL*Plus and PL/SQL scripts.
HTML: Contains all .HTML, .HTM web files.
LOG: Contains all .LOG files of concurrent programs.
OUT: Contains output files from concurrent program.
Message: Holds your application message files for Message dictionary.

/by
, ,

Verifying Profile Option Values

SELECT DECODE (fpov.level_id,
               10001, ‘Site’,
               10002, ‘Appl’,
               10003, ‘Resp’,
               10004, ‘User’,
               ‘Unkown’
              ) “Level”,
       DECODE (fpov.level_id,
               10002, fa.application_name,
               10003, fr.responsibility_name,
               10004, fu.user_name,
               ‘-‘
              ) “Location”,
       fpov.profile_option_value “Value”
  FROM apps.fnd_profile_option_values fpov,
       apps.fnd_profile_options fpo,
       apps.fnd_profile_options_tl fpot,
       apps.fnd_responsibility_tl fr,
       apps.fnd_user fu,
       apps.fnd_application_tl fa
 WHERE fpov.profile_option_id = fpo.profile_option_id
   AND fpo.profile_option_name = fpot.profile_option_name
   AND fpov.level_value = fr.responsibility_id(+)
   AND fpov.level_value = fu.user_id(+)
   AND fpov.level_value = fa.application_id(+)
   AND fpot.user_profile_option_name = ‘<Profile Option Name>‘;
/by
, ,

Verifying Value Sets

— To Check Value Sets (Table Type)

SELECT *
  FROM apps.fnd_flex_validation_tables
 WHERE flex_value_set_id IN (
                             SELECT flex_value_set_id
                               FROM apps.fnd_flex_value_sets
                              WHERE flex_value_set_name =
                                                         ‘<VALUE SET NAME>’)
                                                       
— To Check Value Sets (Independent)

SELECT *
  FROM apps.fnd_flex_values
 WHERE flex_value_set_id IN (
                             SELECT flex_value_set_id
                               FROM apps.fnd_flex_value_sets
                              WHERE flex_value_set_name =
                                                         ‘<VALUE SET NAME>’)
ORDER BY flex_value         

/by
,

WHO COLUMNS

WHO columns are used to track the information updated or inserted by the users against the tables. FND_STANDARD package is used for this purpose. FND_STANDARD.SET_WHO Procedure is used to update the WHO columns in a Table when a DML operation s (i.e. INSERT, UPDATE) performed.
        1) Created by
        2) Creation date
        3) Last _updated_by
        4) last_update_date
        5) last_update_login

  • Use fnd_profile.VALUE (‘USER_ID’) for retrieving the user_id which will be used by created_by column.

  • Creation date and last_update_date will be normally SYSDATE.

  • last_updated_by is same as created_by.

  • Use USERENV (‘SESSIONID’) for getting the last_update_login id.

    • /by
    , ,

    Query to fetch Responsibilities attached to a User

    The below query will fetch the responsibilities assigned to a particular user.
    SELECT
        fu.user_id,
        fu.user_name,
        fr.responsibility_name,
        fr.description,
        fa.application_name
    FROM fnd_user fu,
         fnd_user_resp_groups g,
         fnd_application_tl fa,
         fnd_responsibility_tl fr
    WHERE
         g.user_id(+) = fu.user_id
         AND g.responsibility_application_id = fa.application_id
         AND fa.application_id = fr.application_id
         AND g.responsibility_id = fr.responsibility_id
         AND fu.user_name  =UPPER(‘User_Name‘);
    /by