What is INDICES OF and VALUE OF (FORALL enhancements in 10g)?

INDICES OF: The INDICES OF clause allows us to load non-contiguous (sparse) arrays by telling Oracle to use just the elements that are populated. Remember in versions prior to 10g that arrays had to be dense and we would use iterators such as [array.FIRST .. array.LAST] or [1 .. array.COUNT] to address them (these are still syntactically valid of course). The INDICES OF clause is simple to use as seen in the example below.

SQL> DECLARE

  2

  3     TYPE aat_rec IS TABLE OF tgt%ROWTYPE

  4        INDEX BY PLS_INTEGER;

  5     aa_recs aat_rec;

  6

  7  BEGIN

  8

  9     /* Load a sparse array... */

 10     SELECT object_id, object_name BULK COLLECT INTO aa_recs

 11     FROM   all_objects

 12     WHERE  ROWNUM <= 10;

 13

 14     aa_recs.DELETE(2);

 15     aa_recs.DELETE(4);

 16     aa_recs.DELETE(6);

What is FORALL?

This instruction allows computing all the rows of a collection in a single pass. FORALL cannot be use on the client-side and can precede one and only one statement at a time.

If an error is raised by the FORALL statement, all the rows processed are rolled back. You can save the rows that raised an error (and do not abort the process) with the SAVE EXCEPTION keyword. Every exception raised during execution is stored in the BULK_EXCEPTIONS collection. This is a collection of records composed by two attributes:

FORALL index IN min_index .. max_index [SAVE EXCEPTION] sql_order

%BULK_EXCEPTIONS(n).ERROR_INDEX which contains the index number

%BULK_EXCEPTIONS(n).ERROR_CODE which contains the error code .

i.e.

SQL> Declare

  2    TYPE   TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE;

  3    tabrec TYP_TAB_TEST;

What is bulk collect?

This keyword ask 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 use 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 serveroutput on

SQL> Declare

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

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

  4    Cursor  C_EMP is Select empno From EMP ;

  5    Pass    Pls_integer := 1 ;

  6  Begin

FND_CLIENT_INFO.SET_ORG_CONTEXT

This is the API used to set the ORG_ID value from the backend, so that system will retrieve the data from the database which is related to mention the organization.

Syntax:

Begin

FND_CLIENT_INFO.SET_ORG_CONTEXT(ORG_ID);

end;

EX: Begin

FND_CLIENT_INFO.SET_ORG_CONTEXT(204);

end;

How can we delete concurrent program which is already registered?

Through the application FE it is not possible to delete CP. From BE we can delete it.

Begin

Fnd_program.delete_program(‘short name’, ‘application’);

Fnd_program.delete_executable(‘short name’, ‘application’);

End;

What are different execution methods of executables?

FlexRpt                                   The execution file is wrnitten using the FlexReport API.

FlexSql                                   The execution file is written using the FlexSql API.

Host                                       The execution file is a host script.

Oracle Reports                        The execution file is an Oracle Reports file.

PL/SQL Stored Procedure       The execution file is a stored procedure.

SQL*Loader                            The execution file is a SQL script.

SQL*Plus                                The execution file is a SQL*Plus script.

SQL*Report                            The execution file is a SQL*Report script.

Spawned                                The execution file is a C or Pro*C program.

Immediate                               The execution file is a program written to run as a subroutine of the

concurrent manager. We recommend against defining new immediate concurrent programs, and suggest you use either a PL/SQL Stored Procedure or a Spawned C Program instead.

PO Tables

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL

PO_LINE_LOCATIONS_ALL

PO_REQUISITION_HEADERS_ALL

PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL

PO_VENDORS

PO_VENDOR_SITES_ALL

PO_VENDOR_CONTACTS

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE

PO_INTERFACE_ERRORS

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES

RCV_SHIPMENT_HEADERS

RCV_TRANSACTIONS
RCV_TRANSACTIONS_INTERFACE

What is Value Set & validation types?

The value set is a collection (or) container of values. Whenever the value set associated with any report parameters. It provides list of values to the end user to accept one of the values as report parameter value. If the list of values needed to be dynamic and ever changing and define a table based values set. Value set defines the segment size, data format and validation technique.

1.     None                ------ validation is minimal.

2.     Independent      ------ input must exist on previously defined list of values.

3.     Dependent        ------ input is checked against a subset of values based on a prior value.

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 APPS File Architecture

Each product (GL, Purchasing, Inventory, etc.) in an installation of Oracle Applications has an associated operating system directory.  All files required to install, upgrade, or run the product are stored in this directory (or related subdirectories).  Each product also has one or more database user accounts which own tables used by the product.  Application Object Library calls this collection of objects an application.

A set of customizations for one or more products is installed as an application.  Thus it has a product directory and an Oracle user account.

Directory Structure:

The directory structure for an installed product is as follows:

                                                                                        $APPL_TOP

                                                                                                                |

                                                                                                <product short name>

What is the difference between a reference cursor and normal cursor?

The difference between these two is:

1. Normal cursor is Static & Ref cursor is dynamic.
2. A cursor is once define its contents are fixed and can't be changed. In dynamic cursors the contents of cursor can be changed dynamically depending upon the situation.
3. A cursor can't be passed as parameter from subroutine to subroutine. Whereas ref cursor can be     passed as parameter to subprograms.
4. A ref cursor can be returned to the client machine. A cursor can't.
5. A cursor can be defined globally. A ref cursor can't.

6. There are two types of Ref cursors:

Week & strong cursor

Type ref_name is Ref cursor [return type]

[return type] means Rowtype

            if Return type is mentioned then it is Strong cursor else weak cursor

7. The Reference cursor does not support For Update clause.

Generally we use ref cursor when we need to return result set. In other cases it is advisable to use static cursor because they give better performance.

What is difference between sub query & correlated sub query?

Subquery is a select statement that is nested within another select statement which returned intermediate result. i.e.

select ename from emp

where empno in (select empno from incr)

Correlated subquery is a nested subquery which is executed once for each ‘candidate row’ by the main query & which on execution use a value from a column in the outer query. i.e.

select empno,ename from emp

where 1 <

(select count(*) from incr where empno =emp.empno);

What are attributes of cursor?

·         SQL%ROWCOUNT: Number of rows affected by most recent SQL statement.

·         SQL%FOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement affects one or more rows.

·         SQL%NOTFOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement does not affect any row.

·         SQL%ISOPEN: Always evaluates to FALSE because P/L SQL closes implicit cursors immediately after they are executed.

Oracle Cursor types?

Implicit Cursor: implicit cursor will make automatically by oracle system it open fetch and close it automatically. Implicit Cursor like SQL%rowcount, SQL%rowtype...

Explicit Cursor: Explicit cursors are user defined cursor. You have made it forcefully like:

Cursor C1 is select ename from emp;

Ref Cursor: Ref Cursor is object name of the cursor type. It’s mainly used for dynamic purpose.

SQL WITH Clause (Subquery Factoring)

‘WITH queryname’ clause is introduced in the oracle 9i release 2.This allows you to assign a name to subquery block and this name can be referenced in multiple places in the main query. Oracle treats this query name as inline view of as a table. You can even given alias to this query name. Actually WITH is introduced as replacement to normal subquery and this technique is called subquery factoring.

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.

A WITH clause is really best used when the result of the WITH query is required more than one time in the body of the query such as where one averaged value needs to be compared against two or three times.

Restrictions on Subquery Factoring:

·         You cannot nest this clause. That is, you cannot specify the subquery_factoring_clause within the subquery of another subquery_factoring_clause. However, a query_name defined in one subquery_factoring_clause can be used in the subquery of any subsequent subquery_factoring_clause.

·         In a query with set operators, the set operator subquery cannot contain the subquery_factoring_clause, but the FROM subquery can contain the subquery_factoring_clause.

User exits in Reports?

User Exits are 3GL programs used to transfer the control from the report builder to Oracle Applications or any and it will perform some actions and return to the report builder. There are five types of user exits:

Ø  FND SRWINIT

Ø  FND SRWEXIT

Ø  FND FORMAT_CURRENCY

Ø  FND FLEXSQL

Ø  FND FLEXIDVAL

FND SRWINIT: Used to write in before report trigger. It is used to fetch the concurrent request information and also used to set the profile options.

FND SRWEXIT: We will write this in after report trigger. Used to free the memory which has been allocated by the other user exits

FND FORMAT_CURRENCY: This is used to convert amounts or currency from one currency to other currency values and also used to display currency amounts in formats.

EX: SRW.USER_EXIT(‘FND FORMAT_CURRENCY’,

How to Register a Table in APPS?

You register your custom application tables using a PL/SQL procedure in the AD_DD package. Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert.

You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables. If you alter the table later, then you may need to include revised or new calls to the table registration routines. To alter a registration you should first delete the registration, and then re-register the table or column. Remember, you should delete the column registration first, then the table registration. You should include calls to the table registration routines in a PL/SQL script. Though you create your tables in your own application schema, you should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.

The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need not register views.

Procedures in the AD_DD Package

Procedure REGISTER_TABLE:

    procedure register_table ( p_appl_short_name in varchar2,

    p_tab_name in varchar2,

   

Application Object Library (AOL)

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

P2P Cycle Query

 SELECT PO_Line_Number                ,
  unit_price purchase_price           ,
  Item_Dupl_Number Item_Number        ,
  Item_Dupl_Descrption Item_Descrption,
  NOT_MATCHED_INVOICE                 ,
  Supplier_Dupl_Name Supplier_Name    ,
  Supplier_Dupl_Number Supplier_Number,
  orgnization_name inv_org            ,
  ON_HAND_QUANTITY                    ,
  buyer_name                          ,
  item_cost std_frozen_cost           ,
  orgnization_name ship_to_org_name   ,
  Receipt_Number                      ,
  Receipt_Date                        ,
  Receipt_shipped_quantity            ,
  Receipt_received_quantity           ,
  invoice_number                      ,
  invoice_date                        ,
  invoice_quantity                    ,
  invoice_amount                      ,
  invoice_paid
   FROM
  (SELECT DISTINCT x.inventory_item_id                                                                                           ,
    x.org_name                                                                                                                   ,
    x.org_id                                                                                                                     ,
    x.orgnization_name                                                                                                           ,

What are the two mandatory parameters that should be incorporated while writing plsql programs?

ERRBUF

RETCODE

What are the common values passed by a procedure in variables ERRBUF, RETCODE?

ERRBUFF It is used to returns the error messages and sent it to the log file.

RETCODE It is used to show the status of the Procedure with 0,1,2.

There are 3 types of RETCODE they are:-

# 0 : Success

# 1 : Warning

# 2 : Error

How do you create a responsibility?

Navigate to System Administrator -- Security: Responsibility -- Define

1. Specify the application in which you want to create responsibility.
2. Specify the data group by which user will login to database.
3. Specify the main Menu which user will see on the screen.
4. Specify the first screen user would see after the successful login.
5. Assign the report security group which decides which reports user has access to. (Optional)

What is a responsibility

Responsibility is a collection of different modules that the user is to access.

OR

Responsibility is collection of menus, request security groups and data groups.

  Menus: collection of forms is nothing but menus (Mandatory)

  Request security groups: collection of programs. (Optional)

  Data groups: is a group of modules to be made accessible by the user through (Mandatory)

  Function & Menu: Exclusion (Optional)

          

Define a new Responsibility:

           System adminàSecurity : Responsibility àdefine

                                       Security : useràdefine