Application Object Library (AOL) in Oracle Apps R12

AOL mainly contains three things:

• Operating profiles (System Profiles)
• Application security (User, Function, and Responsibility Security)
• Concurrent Processing (Concurrent manager, request, program, process)

AOL is a powerful library having components of code (objects) used in one or more applications. 

AOL contains the following components:

• Users
• Responsibilities
• Request Group
• Request Sets
• Concurrent Programs
• Concurrent Managers
• Concurrent Program Executables
• Value Sets
• Flex Fields
• Form and Functions
• Menus
• Messages
• Table Registration
• Profiles
• Data Group

Responsibility

• It is nothing but a collection of three things – menu, data group, and request group which is attached to a specific user as per his/her level of access.

Request Group

• A request group is a collection of concurrent programs or request sets. It is attached to a responsibility to control the user’s access to concurrent programs/requests.

Request Set

• A request set is a collection of reports and/or programs that you group together. You can submit the reports and/or programs in a request set all at once using a single transaction.

Concurrent Program

• A concurrent program is a program that does not require continued interaction on your part to perform a specific task. In Oracle Applications, for example, the concurrent program may be a program written to create a report or to post a batch of general ledger journal entries.

Concurrent Manager

• The concurrent manager is a component of concurrent processing that monitors and runs tasks without tying up your computer.

Concurrent Process

• The concurrent process is an instance of a running concurrent program. Each time a concurrent manager receives a request and runs a concurrent program, it creates a new concurrent process. A concurrent process can run simultaneously with other concurrent processes

Concurrent Request

• The concurrent request is a request that you submit to run a concurrent program as a concurrent process.

Value Set

• A set of predefined or validated values assigned to a field (parameter) that restricts the user to enter from entering invalidated data.

Menu

• A  menu is a hierarchical arrangement of functions and menus of functions that appears in the Navigator.
• A  menu entry is a menu component that identifies a function or a menu of functions.
• Each responsibility has a menu assigned to it.

Functions

• A function is a set of Oracle Applications that is executed only if the name of the function is present in a list maintained within a responsibility.
• Because all users should not have access to every business function in a form, Oracle Applications provide the ability to identify pieces of applications logic as functions.
• Function security lets you restrict application functionality to authorized users.

Data Group

• The data group is a collection of pairings of Applications with Oracle ID.
• An Oracle ID is a username and password that allows access to application tables in an Oracle database.
• Concurrent managers use a data group to match the application that owns a report or concurrent program with a unique Oracle ID.

What are the WHO columns?

• WHO columns are used to track the changes to your data in the application tables. 
• WHO columns exist in all Oracle Applications standard tables. The following five are considered WHO columns:

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

Oracle PL/SQL BULK COLLECT

• This keyword asks the SQL engine to return all the rows in one or several collections before returning to the PL/SQL engine so, there is one single roundtrip for all the rows between SQL and PL/SQL engine.
• BULK COLLECT cannot be used on the client side.

(Select)(Fetch)(execute immediate) … BULK COLLECT Into collection_name [,collection_name, …] [LIMIT max].

• LIMIT is used to limit the number of rows returned
• BULK COLLECT can also be used to retrieve the result of a DML statement that uses the RETURNING INTO clause.

SQL> set server output on

SQL> Declare

     TYPE    TYP_TAB_EMP IS A TABLE OF EMP.EMPNO%Type ;

     Temp_no TYP_TAB_EMP; -- a collection of EMP.EMPNO%Type

     Cursor  C_EMP is Select empno From EMP ;

      Pass    Pls_integer := 1 ;

    Begin

      Open C_EMP ;

    Loop

        -- Fetch the table 3 by 3 --

       Fetch C_EMP BULK COLLECT into Temp_no LIMIT 3 ;

       Exit When C_EMP%NOTFOUND ;

       For i In Temp_no.first..Temp_no.last Loop

         dbms_output.put_line( 'Pass ' || to_char(Pass) || ' Empno= ' || Temp_no(i) ) ;

       End loop ;

       Pass := Pass + 1 ;

     End Loop ;

   End ;

 

Pass 1 Empno= 9999

Pass 1 Empno= 7369

Pass 1 Empno= 7499

Pass 2 Empno= 7521

Pass 2 Empno= 7566

Pass 2 Empno= 7654

Pass 3 Empno= 7698

Pass 3 Empno= 7782

Pass 3 Empno= 7788

Pass 4 Empno= 7839

Pass 4 Empno= 7844

Pass 4 Empno= 7876

Pass 5 Empno= 7900

Pass 5 Empno= 7902

Pass 5 Empno= 7934

PL/SQL procedure successfully completed.

You can use the LIMIT keyword to preserve your rollback segment:

Declare

  TYPE    TYP_TAB_EMP IS A TABLE OF EMP.EMPNO%Type ;

  Temp_no TYP_TAB_EMP ;

  Cursor  C_EMP is Select empno From EMP ;

  max_lig Pls_Integer := 3 ;

Begin

  Open C_EMP ;

  Loop

    Fetch C_EMP BULK COLLECT into Temp_no LIMIT max_lig ;

    Forall i In Temp_no.first..Temp_no.last

        Update EMP set SAL = Round(SAL * 1.1) Where empno = Temp_no(i) ;

    Commit ; -- Commit every 3 rows

    Temp_no.DELETE ;

    Exit When C_EMP%NOTFOUND ;

  End Loop ;

End ;

BULK COLLECT can also be used to retrieve the result of a DML statement that uses the RETURNING INTO clause:

SQL> Declare

       TYPE    TYP_TAB_EMPNO IS TABLE OF EMP.EMPNO%Type;

       TYPE    TYP_TAB_NOM TABLE OF EMP.ENAME%Type; 

       Temp_no TYP_TAB_EMPNO;

       Tnoms   TYP_TAB_NOM ; 

    Begin

       -- Delete rows and return the result into the collection --

       Delete From EMP where sal > 3000

       RETURNING empno, ename BULK COLLECT INTO Temp_no, Tnoms ;

      For i in Temp_no.first..Temp_no.last Loop

         dbms_output.put_line( 'Fired employee : ' || To_char( Temp_no(i) ) || ' ' || Tnoms(i) ) ;

      End  loop ;

   End ;

 

Fired employee: 7839 KING

PL/SQL procedure successfully completed.

Types of Purchase Orders in Oracle Apps R12

There are 4 types of Purchase Orders:

1. Standard PO: A Standard PO is created for one–time purchase of various items.
2. Planned PO: A Planned PO is a long–term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.
3. Blanket agreement: A Blanket PO is created when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not know the detail of your delivery schedules.
4. Contract agreement: Contract purchase agreements are created with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing.

What is Pragma EXECPTION_INIT? Explain the usage?

Pragma exception_init Allows you to handle the Oracle predefined message with your own message. This means you can instruct the compiler to associate the specific message to the oracle's predefined message at compile time. This way you improve the readability of your program and handle it according to your own way.

It should be declared in the DECLARE section.

declare

salary number;

FOUND_NOTHING exception;

Pragma exception_init(FOUND_NOTHING ,100);

begin

select sal into salary from emp where ename =' ANURAG';

dbms_output.put_line(salary);

exception

WHEN FOUND_NOTHING THEN

dbms_output.put_line(SQLERRM);

end;