Functions in Oracle AOL


Functions in Oracle AOL

What is Function?

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 responsibility.

What are the Function Types?

There are two types of functions:

a] Form (Form Function)

  •   A form function (form) invokes an Oracle Forms form.
  •   Form functions appear in the Navigate window and can be navigated to.

b] Subfunction (Non–Form Function)

  •   A non–form function (subfunction) is a securable subset of a form’s functionality: in other words, a function executed from within a form.
  •   Subfunctions are frequently associated with buttons or other graphical elements on forms.

Forms vs. Subfunctions?

  • Forms appear in the Navigate window and can be navigated to. Subfunctions do not appear in the Navigate window and cannot be navigated to.
  • Forms can exist on their own. Subfunctions can only be called by logic embodied within a form; they cannot exist on their own.
  • A form as a whole, including all of its program logic, is always designated as a function. Subsets of a form’s program logic can optionally be designated as subfunctions if there is a need to secure those subsets.

Advantages of Form Functions

If you want to open the Form in different modes without creating the copies, we can create a Form Function and pass the parameters based on the requirement. The parameter, which is passed in the form function, must be already defined in the form while designing the Form. A menu can be assigned to more than one Responsibility. If you want to restrict some of the Forms from a particular responsibility, we can include Form Function of those Forms in Menu Exclusions of the Responsibility.

What are the standard function types?

  • FORM                                    — Oracle Applications form functions are registered with a type of FORM.
  • SUBFUNCTION                   — Subfunctions are added to menus
  • JSP                                        — JSP functions
  • WWW                                     —PL/SQL functions
  • WWK                                      —PL/SQL functions that open a new window
  • WWR or WWL                      —used for some products in the Oracle Self–Service Web Applications
  • WWJ                                      —OA Framework JSP portlet
  • SERVLET                              —Servlet functions
  • DBPORTLET                        —Database provider portlet
  • WEBPORTLET                    —Web provider portlet
Advertisements

AOL Terminology


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.

Registering Reports in Oracle Application


Registering Reports in Oracle Application

 

Steps Required for Registering a Simple Report

1. Create a Report using Report Builder.

2. Compile and copy .RDF file in module specific directory.

3. Register the executable with System Administrator Module.

4. Define the Concurrent Program.

5. Assign the executable to Concurrent Program.

6. Assign the Concurrent Program to Request Group.

7. Assign the Request Group to the Responsibility.

8. Assign the Responsibility to the User.

 

Registering Parametric Reports

1. Create a Report using Report Builder with parameters.

2. Compile and copy .RDF file in module specific directory.

3. Register the executable with System Administrator Module.

4. Define the Value set to validate the parameters.

5. Define the Concurrent Program.

6. Assign the executable to Concurrent Program.

7. Define Parameters.

8. Assign Value Set to the Parameters.

9. Assign bind parameter of yours to the TOKEN.

Note: Token is used to map bind parameters with the formal parameters of the Concurrent Program.

10. Assign the Concurrent Program to Request Group.

11. Assign the Request Group to the Responsibility.

12. Assign the Responsibility to the User.

To reference the values of Prior Parameters of a particular program into the values of other parameter is based on the Value Set. The value of the 1st parameter is to be referenced in where clause of the 2nd Parameter.

WHERE Deptno = :$FLEX$.First_Parameter

Oracle Application Directory Structure


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.

WHO COLUMNS


 

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.
  • Key FND Tables in Oracle Application


    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.

    Flex mode and Confine mode


    Flex mode and Confine mode

    Confine mode:

    On: child objects cannot be moved outside their enclosing parent objects.
    Off: child objects can be moved outside their enclosing parent objects.

    Flex mode:

    On: parent borders “stretch” when child objects are moved against them.
    Off: parent borders remain fixed when child objects are moved against
    them.

     

    Attachment in Oracle Application


    Attachment in Oracle Application

    What is attachment in oracle application?

    The attachments feature in oracle application enables users to link unstructured data, such as images, word-processing documents, spreadsheets, or text to their application data. For example, users can link images to items or video to operations as operation instructions.

    Where to find an attachment?

    There is an attachment icon in the oracle application toolbar that indicates whether the Attachments feature is enabled in a form block. When the button is dimmed, the Attachment feature is not available. When the Attachment feature is enabled in a form block, the icon becomes a solid paper clip. The icon switches to a paper clip holding a paper when the Attachment feature is enabled in a form lock and the current record has at least one attachment.

    Attachment types:

    An attached document can be:

    1] Short Text

    Text stored in the database containing less than 2000 characters.

    2] Long Text

    Text stored in the database containing 2000 characters or more.

    3] Image

    An image that Oracle Forms can display, including: bmp, cals, jfif, jpeg, gif, pcd, pcx, pict, ras, and tif.

    4] OLE Object

    An OLE Object that requires other OLE server applications to view, such as Microsoft Word or Microsoft Excel.

    5] Web Page

    A URL reference to a web page which you can view with your web browser.

    Tables Involved:

    For Importing Attachments in oracle application one has to populate following tables.

    1. FND_DOCUMENTS

    2. FND_ATTACHED_DOCUMENTS

    3. FND_DOCUMENTS_TL

    4. FND_DOCUMENT_DATATYPES.

    5. FND_DOCUMENT_CATEGORIES

    6. FND_DOCUMENTS_LONG_TEXT (Long text type attachment).

    7. FND_DOCUMENTS_SHORT_TEXT (Short text type attachment).

    8. FND_DOCUMENTS_LONG_RAW

    9. FND_LOBS (File type attachments).

    FND_DOCUMENTS:

    FND_DOCUMENTS stores language-independent information about a document. For example, each row contains a document identifier, a category identifier, the method of security used for the document (SECURITY_TYPE, where 1=Organization,2=Set of Books, 3=Business unit,4=None), the period in which the document is active, and a flag to indicate whether or not the document can be shared outside of the security type (PUBLISH_FLAG).

    Other specifications in this table include: datatype (DATATYPE_ID, where 1=short text,2=long text, 3=image, 4=OLE object), image type, and storage type (STORAGE_TYPE, where 1=stored in the database, 2=stored in the file system).

    The document can be referenced by many application entities and changed only in the define document form (USAGE_TYPE=S); it can be used as a fill-in-the-blanks document, where each time you use a template, you make a copy of it (USAGE_TYPE=T); or it can be used only one time (USAGE_TYPE=O).Images and OLE Objects cannot be used as templates.

    FND_ATTACHED_DOCUMENTS:

    FND_ATTACHED_DOCUMENTS stores information relating a document to an application entity.  For example, a record may link a document to a sales order or an item. Each row contains foreign keys to FND_DOCUMENTS and FND_DOCUMENT_ENTITIES. There is also a flag to indicate whether or not an attachment was created automatically.

    FND_DOCUMENTS_TL:

    FND_DOCUMENTS_TL stores translated information about the documents in FND_DOCUMENTS. Each row includes the document identifier, the language the row is translated to, the description of the document, the file in which the image is stored, and an identifier (MEDIA_ID) of the sub-table in which the document is saved (FND_DOCUMENTS_SHORT_TEXT, FND_DOCUMENTS_LONG_TEXT, or FND_DOCUMENTS_LONG_RAW).

    FND_DOCUMENT_DATATYPES:

    FND_DOCUMENT_DATATYPES stores the document datatypes that are supported. Initial values are: short text, long text, image, and OLE Object (DATATYPE_ID=1, 2, 3, or 4). Customers can add datatypes to handle documents stored outside of Oracle and use non-native Forms applications to view/edit their documents. The table uses a “duplicate record” model for handling multi-lingual needs. That is, for each category there will be one record with the same CATEGORY_ID and CATEGORY_NAME for each language.

    FND_DOCUMENT_CATEGORIES:

    FND_DOCUMENT_CATEGORIES stores information about the categories in which documents are classified. For example, documents may be considered “Bill of Material Comments”, “WIP Job Comments”, etc. Document categories are used to provide a measure of security on documents. Each form that enables the attachment feature lists which categories of documents can be viewed in the form. This table uses a “duplicate record” model for handling multi-lingual needs.

    FND_DOCUMENTS_LONG_TEXT:

    FND_DOCUMENTS_LONG_TEXT stores information about long text documents.

    FND_DOCUMENTS_SHORT_TEXT:

    FND_DOCUMENTS_SHORT_TEXT stores information about short text documents.

    FND_DOCUMENTS_LONG_RAW:

    FND_DOCUMENTS_LONG_RAW stores images and OLE Objects, such as Word Documents and Excel spreadsheets, in the database.

    FND_DOCUMENT_ENTITIES:

    FND_DOCUMENT_ENTITIES lists each entity to which attachments can be linked. For example, attachments can be linked to Items, Sales Orders, etc. Since the table uses a “duplicate record” model for handling multi-lingual needs, for each document entity there will be one record with the same DOCUMENT_ENTITY_ID and DATA_OBJECT_CODE for each language.

    Queries:

    1] To find all Long Text attachments:

    SELECT
            FAD.SEQ_NUM "Seq Number",
            FDAT.USER_NAME "Data Type",
            FDCT.USER_NAME "Category User Name",
            FAD.ATTACHED_DOCUMENT_ID "Attached Document Id",
            FDET.USER_ENTITY_NAME "User Entity",
            FD.DOCUMENT_ID "Document Id",
            FAD.ENTITY_NAME "Entity Name",
            FD.MEDIA_ID "Media Id",
            FD.URL "Url",
            FDT.TITLE "Title",
            FDLT.LONG_TEXT "Attachment Text"
    FROM
            FND_DOCUMENT_DATATYPES FDAT,
            FND_DOCUMENT_ENTITIES_TL FDET,
            FND_DOCUMENTS_TL FDT,
            FND_DOCUMENTS FD,
            FND_DOCUMENT_CATEGORIES_TL FDCT,
            FND_ATTACHED_DOCUMENTS   FAD,
            FND_DOCUMENTS_LONG_TEXT FDLT
    WHERE
            FD.DOCUMENT_ID          = FAD.DOCUMENT_ID
            AND FDT.DOCUMENT_ID     = FD.DOCUMENT_ID
            AND FDCT.CATEGORY_ID    = FD.CATEGORY_ID
            AND FD.DATATYPE_ID      = FDAT.DATATYPE_ID
            AND FAD.ENTITY_NAME     = FDET.DATA_OBJECT_CODE
            AND FDLT.MEDIA_ID       = FD.MEDIA_ID
            AND FDAT.NAME           = 'LONG_TEXT';
    

    2] To find all Short Text attachments:

    SELECT
            FAD.SEQ_NUM "Seq Number",
            FDAT.USER_NAME "Data Type",
            FDCT.USER_NAME "Category User Name",
            FAD.ATTACHED_DOCUMENT_ID "Attached Document Id",
            FDET.USER_ENTITY_NAME "User Entity",
            FD.DOCUMENT_ID "Document Id",
            FAD.ENTITY_NAME "Entity Name",
            FD.MEDIA_ID "Media Id",
            FD.URL "Url",
            FDT.TITLE "Title",
            FDST.SHORT_TEXT "Attachment Text"
    FROM
            FND_DOCUMENT_DATATYPES FDAT,
            FND_DOCUMENT_ENTITIES_TL FDET,
            FND_DOCUMENTS_TL FDT,
            FND_DOCUMENTS FD,
            FND_DOCUMENT_CATEGORIES_TL FDCT,
            FND_ATTACHED_DOCUMENTS   FAD,
            FND_DOCUMENTS_SHORT_TEXT FDST
    WHERE
            FD.DOCUMENT_ID          = FAD.DOCUMENT_ID
            AND FDT.DOCUMENT_ID     = FD.DOCUMENT_ID
            AND FDCT.CATEGORY_ID    = FD.CATEGORY_ID
            AND FD.DATATYPE_ID      = FDAT.DATATYPE_ID
            AND FAD.ENTITY_NAME     = FDET.DATA_OBJECT_CODE
            AND FDST.MEDIA_ID       = FD.MEDIA_ID
            AND FDAT.NAME           = 'SHORT_TEXT';
    

    Attachment upload through API:

    Attachments can also be uploaded through an oracle provided API called  FND_ATTACHED_DOCUMENTS_PKG.

    It consist of three procedures

    1)  Insert Row

    2)  Update Row

    3)  Lock Row

    Names of these procedures are self explanatory. insert row is used to insert a new row for attachment data, update row is used to update existing row for a particular row and Lock Row is used to lock a existing row for further modification.

     

    Query to fetch Responsibilities attached to a User


    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');