, ,

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
    , ,

    Key FND Tables in Oracle Application

    Here there are few key FND tables that we use in our AOL queries.

    FND_APPLICATION:
     Stores applications registered with Oracle Application Object Library.
    FND_APPLICATION_TL:
    Stores translated information about all the applications registered with Oracle Application Object Library.
    FND_APP_SERVERS:
    This table will track the servers used by the E-Business Suite system.
    FND_ATTACHED_DOCUMENTS:
    Stores information relating a document to an application entity.
    FND_CONCURRENT_PROCESSES:
    Stores information about concurrent managers.
    FND_CONCURRENT_PROCESSORS:
    Stores information about immediate (subroutine) concurrent program libraries.
    FND_CONCURRENT_PROGRAMS:
    Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
    FND_CONCURRENT_PROGRAMS_TL:
    Stores translated information about concurrent programs in each of the installed languages.
    FND_CONCURRENT_QUEUES:
    Stores information about concurrent managers.

    FND_CONCURRENT_QUEUE_SIZE:
    Stores information about the number of requests a concurrent manager can process at once, according to its work shift.
    FND_CONCURRENT_REQUESTS:
    Stores information about individual concurrent requests.
    FND_CONCURRENT_REQUEST_CLASS:
    Stores information about concurrent request types.
    FND_CONC_REQ_OUTPUTS:
    This table stores output files created by Concurrent Request.
    FND_CURRENCIES:
    Stores information about currencies.
    FND_DATABASES:
    It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.
    FND_DATABASE_INSTANCES:
    Stores instance specific information. Every database has one or more instance.
    FND_DESCRIPTIVE_FLEXS:
    Stores setup information about descriptive flexfields.
    FND_DESCRIPTIVE_FLEXS_TL:
    Stores translated setup information about descriptive flexfields.
    FND_DOCUMENTS:
    Stores language-independent information about a document.
    FND_EXECUTABLES:
    Stores information about concurrent program executables.
    FND_FLEX_VALUES:
    Stores valid values for key and descriptive flexfield segments.
    FND_FLEX_VALUE_SETS:
    Stores information about the value sets used by both key and descriptive flexfields.
    FND_LANGUAGES:
    Stores information regarding languages and dialects.
    FND_MENUS:
    It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.
    FND_MENUS_TL:
    Stores translated information about the menus in FND_MENUS.
    FND_MENU_ENTRIES:
    Stores information about individual entries in the menus in FND_MENUS.
    FND_PROFILE_OPTIONS:
    Stores information about user profile options.
    FND_REQUEST_GROUPS:
    Stores information about report security groups.
    FND_REQUEST_SETS:
    Stores information about report sets.
    FND_RESPONSIBILITY:
    Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.
    FND_RESPONSIBILITY_TL:
    Stores translated information about responsibilities.
    FND_RESP_FUNCTIONS:
    Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.
    FND_SECURITY_GROUPS:
    Stores information about security groups used to partition data in a Service Bureau architecture.
    FND_SEQUENCES:
    Stores information about the registered sequences in your applications.
    FND_TABLES:
    Stores information about the registered tables in your applications.
    FND_TERRITORIES:
    Stores information for countries, alternatively known as territories.
    FND_USER:
    Stores information about application users.
    FND_VIEWS:
    Stores information about the registered views in your applications.

    /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
    , ,

    AOL Terminology

    Responsibilities:
    A responsibility is a level of authority in Oracle Applications that lets users access only those Oracle Applications functions and data appropriate to their roles in an organization.
    Request Group:
    It is a collection of concurrent Programs. It is used to request programs from the responsibility.
    Data Group:
    It is a collection of Modules used to integrate one or more Modules for cross application transfer of data, cross application reporting and cross application reference. If we want to get data from other Modules we need to define those modules in the Data Group.
    Functions:
    A function is a part of an application’s functionality that is registered under a unique name for the purpose of assigning it to, or excluding it from, a menu (and by extension, a responsibility).
    Menu:
    A menu is a collection of Sub‐Menus and Functions.
    Concurrent Program:
    It is an instance of an execution file, along with parameter definitions and incompatibilities. Several concurrent programs may use the same execution file to perform their specific tasks, each having different parameter defaults and incompatibilities.
    Concurrent Program Executable:
    It is an executable file that performs a specific task. The file may be a program written in a standard language, a reporting tool or an operating system language.

    Concurrent Request:
    It is a request to run a concurrent program as a concurrent process.
    Concurrent Process:
    It is an instance of a running concurrent program that runs simultaneously with other concurrent processes.
    Concurrent Manager:
    It is a program that processes user’s requests and runs concurrent programs. System Administrators define concurrent managers to run different kinds of requests.
    Value Set:
    The value set is a collection (or) container of values. It provides list of values to the end user to accept one of the values as report parameter value.
    Profiles:
    A user profile is a set of changeable options that affects the way your applications run. Oracle Application Object Library establishes a value for each option in a user’s profile when the user logs on or changes responsibility.
    Key Flexfields:
    They are used to capture mandatory or Key Business information of the Organization. Each Key Flex Field is having its own base Table.
    Descriptive Flexfields:
    They are used to capture the additional or extra Business information of the organization. DFF are used to add extra accounts, those changes from one business to another business. All DFF columns are defined as Attribute Columns. All these columns are defined in the transaction table itself. There are around 5000+ DFF available.
    FlexField Qualifiers:
    A Flex field qualifier identifies a particular segment of a key flex field. These are based on Key Flex Fields (KFF). FFQs are varry from one KFF to another KFF and it is not compulsory that all the KFF should have FFQs.
    Segment Qualifiers:
    A Segment Qualifier identifies a particular type of value in a single segment of a key flex field. Segment Qualifier is based on FFQs and it is not compulsory that all the FFQs should have Segment Qualifiers.
    Dynamic Insertion:
    Dynamic Insertion is the insertion of new valid combination into a Key Flexfields Combinations Table from a form other than the combinations form.
    Alerts:
    Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts. Oracle Alert will send messages or perform predefined actions in an action set when important events occur. Alert is a mechanism that checks your database for a specific exception condition. Alerts are used to monitor your business information and to notify you of the information you want.
    Standard Request Submission:
    SRS provides you with a set of windows for running reports and Programs and a set of windows for creating groups of reports and programs to run together.
    /by