Oracle Apps Technical and Reports Interview Questions and Answers

1.         How to attach reports in Oracle Applications?

Ans.:   The steps are as follows:

           ·Design your report.

           ·Generate the executable file of the report.

           ·Move the executable as well as source files to the appropriate product’s folder.

           ·Register the report as concurrent executable.

           ·Define the concurrent program for the executable registered.

           ·Add the concurrent program to the request group of the responsibility.

2.         What are different report triggers and what is their firing sequence?

Ans.:    There are five report trigger:

            ·Before Report

            ·After Report

            ·Before Parameter Form        

            ·After Parameter Form

            ·Between Pages

            The Firing sequence for report triggers is

            Before Parameter Form – After Parameter Form – Before Report –             Between

            Pages – After Report.

   

3.         What is the use of cursors in PL/SQL? What is REF Cursor?

Ans.:    The cursor is used to handle multiple row queries in PL/SQL.         

            Oracle uses implicit cursors to handle all its queries. Oracle uses unnamed memory spaces 

            to store data used in implicit cursors, with REF cursors you can define a cursor variable     which will point to that memory space and can be used like         pointers in our 3GLs.

     

4.         What is record group?

Ans.:    Record group are used with LOVs to hold sql query for your list of values.

            The record group can contain static data as well it can access data from database tables

             thru sql queries.

5.         What is a Flex Field? What are Descriptive and Key Flex fields?

Ans.:   An Oracle Applications field made up of segments. Each segment has an assigned name

           and a set of valid values. Oracle Applications uses flex fields to capture information

           about your organization.

6.        What are Autonomous transactions? Give a scenario where you have used  

            Autonomous transaction in your reports?

Ans.:  An autonomous transaction is an independent transaction started by another transaction,

           the main transaction. Autonomous transactions let you suspend the main transaction, do

           SQL operations, commit or roll back those operations, then resume the main transaction.

           Once started, an autonomous transaction is fully independent. It shares no locks, resources,    

            or commit-dependencies with the main transaction. So, you can log events, increment retry           counters, and so on, even if the main transaction rolls back.

         

           More important, autonomous transactions help you build modular, reusable software

           components. For example, stored procedures can start and finish autonomous transactions   

           on their own. A calling application need not know about a procedure's autonomous  

           operations, and the procedure need not know about the application's transaction context.

           That makes autonomous transactions less error-prone than regular transactions and easier to

           use.     

           Furthermore, autonomous transactions have all the functionality of regular transactions.     

          They allow parallel queries, distributed processing, and all the transaction control statements 

           including SET TRANSACTION.

          Scenario: You can use autonomous transaction in your report for writing error messages in  

          your database tables.

7.       What is the use of triggers in Forms? What are the different types of triggers in forms?

Ans.:  Triggers are used in forms for event handling. You can write PL/SQL code in triggers to   

           respond to a particular event occurred in your forms like when user presses a button or

           when he commits the form.

          The different types of triggers available in forms are:

          · Key-triggers

          · Navigational-triggers

          · Transaction-triggers

          · Message-triggers

          · Error-triggers

          · Query based-triggers

8.       What is the use of Temp tables in Interface programs?

Ans.:  Temporary tables are used in Interface programs to hold the intermediate data. The data is   

           loaded into temporary tables first and then, after validating through the PL/SQL programs,

           the data is loaded into the interface tables.

9.     How to pass parameters to a report? Do you have to register them with AOL?

Ans.: You can define parameters in the ‘define concurrent program form’. There is no need to

          register the parameters with AOL. But you may have to register the value sets for those 

          parameters.

10.    Do you have to register feeder programs of interface to AOL?

Ans.:  Yes! You have to register the feeder programs as concurrent programs to Apps.

11.     What are forms customization steps?

Ans.:  The steps are as follows:

          ·Copy the template.fmb and Appstand.fmb from AU_TOP/forms/us. Put it in custom  

            directory. The libraries (FNDSQF, APPCORE, APPDAYPK, GLOBE, CUSTOM, JE, JA,

            JL, VERT) are automatically attached.

          ·Create or open new Forms. Then customize.

          ·Save this Form in Corresponding Modules.

13.     How to use Flex fields in reports?

Ans.: There are two ways to use Flex fields in report. One way is to use the views (table name +  

          ‘_KFV’ or ’_DFV’) created by apps, and use the concatenated segments column which

           holds the concatenated segments of the key or descriptive flex fields.

          Or the other way is to use the FND user exits provided by oracle applications.

14.     What are Key and Descriptive Flex fields?

Ans.:  Key Flex field:

          # Unique identifier, storing key information

          # Used for entering and displaying key information.

             For example Oracle General uses a key Flex field called Accounting Flex field to uniquely   

             identifies a general account.

           Descriptive Flex field:

           # To capture additional information.

           # To provide expansion space on your form With the help of []. [] Represents descriptive

              Flex field.

15.      Difference between Key and Descriptive Flex field?

Ans.:   Key Flex field                                                       Descriptive Flex field

           1. Unique Identifier                                              1.To capture extra information

           2. Key Flex field are stored in segment                2.Stored in attributes

           3. For key flex field there are flex field                3.Context-sensitive flex field is a feature of  

              Qualifier and segment Qualifier                           DFF. (Descriptive flex field)

13.     What are ad-hoc reports?

Ans.:  Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a  

           particular purpose. For example, ad hoc tax codes or an ad hoc database query

14.       Can you use REF cursors in CUSTOM library?

Ans.: No

18.       Can you call APPCORE library in CUSTOM library?

Ans.:   You cannot attach the APPCORE library to CUSTOM because it would cause a recursion 

            problem (Because CUSTOM is attached to APPCORE). As of Oracle Applications Release

            11i, you may attach the APPCORE2 library to CUSTOM. The APPCORE2 library

            duplicates most APPCORE routines with the following packages:

          · APP_ITEM_PROPERTY2

          · APP_DATE2

          · APP_SPECIAL2

           These packages contain the same routines as the corresponding APPCORE packages.

           Follow the documentation for the corresponding APPCORE routines, but add a 2 to the

           package names.

20.       What are Interface table in AP, AR, GL?

Ans.:   AP INTERFACE TABLES:

                                                      1). AP_INTERFACE_CONTROLS.

                                                      2). AP_INTERFACE_REJECTIONS

                                                      3). AP_INVOICE_INTERFACE

                                                      4). AP_INVOICE_LINES_INTERFACE.

          AR INTERFACE TABLES:

                                                    1). AR_PAYMENTS_INTERFACE_ALL

                                                    2). AR_TAX_INTERFACE

                                                    3). HZ_PARTY_INTERFACE

                                                    4). HZ_PARTY_INTERFACE_ERRORS

                                                    5). RA_CUSTOMERS_INTERFACE_ALL

                                                    6). RA_INTERFACE_DISTRIBUTIONS_ALL

                                                    7). RA_INTERFACE_ERRORS_ALL

                                                    8). RA_INTERFACE_LINES_ALL

                                                    9). RA_INTERFACE_SALESCREDITS_ALL

          GL INTERFACE TABLES:

                                                  1). GL_BUDGET_INTERFACE

                                                  2). GL_DAILY_RATES_INTERFACE

                                                  3). GL_IEA_INTERFACE

                                                  4). GL_INTERFACE

                                                  5). GL_INTERFACE_CONTROL

                                                  6). GL_INTERFACE_HISTORY

9.         Can you add a descriptive Flex Field to a table which already has data in it?

Answer : YES

How can you handle multiple rows without using loops in PL/SQL?

If we want to return multiple rows from a function then we can use ref cursor
function .Example is given below:

create or replace function f1 return sys_refcursor as
r1 sys_refcursor;
begin open r1 for select * from emp;
return (r1);
end;
call the function as:

select f1 from dual

This can be done by a function called TABLE FUNCTION
by means of this you can return more number of rows and columns this is more effective than using ref cursor

What is SQL Loader?

            SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. SQL*Loader supports various load formats, selective loading, and multi-table loads.

When a control file is fed to an SQL*Loader, it writes messages to the log file, bad rows to the bad file and discarded rows to the discard file.

Control file
The SQL*Loader control file contains information that describes how the data will be loaded. It contains the table name, column data types, field delimiters, etc.
controlfile.sql should be used to generate an accurate control file for a given table.
Log File
The log file contains information about the SQL*loader execution. It should be viewed after each SQL*Loader job is complete.

What are the different types of files used in SQL Loader?

            There are 5 files are user in SQL Loader. They are

1.      Data File

2.      Control File

3.      Log file

4.      Bad File

5.      Discard file

Data file and control file is required file.

Example
SQL> sqlldr userid=apps/apps control=XYZ.ctl data=XYZ.csv
                        or

SQL> sqlldr userid=apps/apps control=XYZ.ctl

This control file contains the reference of data file. Log file and bad file automatically created by sql Loader and Discard file is optional.

If the value set if of type –TABLE then how many tables can we attach in the value set?

                        Only One Table

How to take care of null values in SQL Loader?

TRAILING NULLCOLS

What is who column and how can you set the values?

WHO columns are maintained to know who updates the particular rows

1.      last_update_date

2.      last_updated_by

3.      creation_date

4.      created_by

5.      last_update_login

 Are 5 WHO columns by using fnd_standard.set_who procedure we can set the value.

What is External table?

          External tables can be used to load flat files into the database.
Steps:
First create a directory say ext_dir and place the flat file (file.csv) in it and grant read/write access to it.
Then create the table as below:
create table erp_ext_table (
i Number,
n Varchar2(20),
m Varchar2(20)
)
organization external (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields terminated by ,
missing field values are null
)
location (file.csv)
)
reject limit unlimited

What are the different validation types of a value set?

 There are 8 validation type of value set:
Independent, Dependant, Table, None, Pair, Special, Translatable Independent,
Translatable Dependant

What is difference between procedure and function?

function will return a value	procedure may or may not return a value
Function can be use in SQL queries Function call by an Expression	Procedure can't be used in SQL queries Procedure call by Statement
Functions we can't insert, update, delete data from Table	Through procedures we can insert, update, delete data from Table
Functions cannot return images	procedure returns images
function call is part of an expression	procedure call is pl/sql statement by itself

The report output is 10 pages then how many times between pages report trigger will fire?

               9 times between pages report trigger will fire. Between pages report trigger will fires n-1 times.

Can we have a commit statement inside a trigger? If no, why can’t we?

               Yes, you can commit inside the trigger.

               But for this you have to make this trigger transaction to be an Independent transaction from its parent transaction, you can do this by using Pragma. Pragma AUTONOMOUS_TRANSACTION allows you to build the Independent (child) Transaction, started by another. Should be declare in DECLARE section of any subprogram.

Use to make Modular and Reusable Blocks. If you need the example then ask to me.

CREATE OR REPLACE TRIGGER t_trigger

AFTER INSERT ON t1 FOR EACH ROW

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

i PLS_INTEGER; 

BEGIN

SELECT COUNT(*)

INTO i

FROM t1;



INSERT INTO t2

VALUES(i);

COMMIT;

END;

/

CREATE OR REPLACE TRIGGER tab1_trig

 AFTER INSERT ON tab1

 DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

 BEGIN

 INSERT INTO log VALUES (SYSDATE 'Insert on TAB1');

 COMMIT; -- only allowed in autonomous triggers

 END;

 /

Applications User Exits in Reports

We can integrate Oracle reports with Oracle Application Object Library, and run them as concurrent programs from your forms or through standard request submission.

User Exits are used in Oracle APPS to access user profile values and perform proper calculation. Ex. of Other AOL User exits available in Reports are given below.

These are the user exits available in Oracle Reports that makes AOL integration.

1. FND SRWINIT
2. FND SRWEXIT
3. FND FORMAT_CURRENCY
4. FND FLEXIDVAL
5. FND FLEXSQL

• FND SRWINIT
• This is a User Exit which sets your profile option values and allows Oracle AOL user exits to detect that they have been called by oracle repots.
• FND SRWINIT also allows your report to use the correct organization automatically.
• Can be used in BEFORE-REPORT Trigger.
• FND_SRWEXIT
• This user exit ensures that all the memory allocated for AOL user exits has been freed up properly.
• Can be used in AFTER- REPORT Trigger
• FND FORMAT_CURRENCY
• To format the currency amount dynamically depending upon the precision of the actual currency value, standard precision, users profile values and location (country) of the site.
• You obtain the currency value from the database into an Oracle Reports column. Define another Oracle Reports column, a formula column of type CHAR, which executes the FORMAT_CURRENCY user exit to format the currency value. A displayed field has this formula column as its source so that the formatted value is automatically copied into the field for display.

Syntax
FND FORMAT_CURRENCY
CODE=”:column containing currency code”
DISPLAY_WIDTH=”field width for display”
AMOUNT=”:source column name”
DISPLAY=”:display column name”
[MINIMUM_PRECISION=”:P_MIN_PRECISION”]
[PRECISION=”{STANDARD|EXTENDED}”]
[DISPLAY_SCALING_FACTOR=””:P_SCALING_FACTOR”]

• FND FLEXSQL
• This user exits allows you to use Flex fields in Reports.

Syntax:
FND FLEXSQL

CODE="flexfield code"
APPL_SHORT_NAME="application short name"
OUTPUT=":output lexical parameter name"
MODE="{ SELECT | WHERE | HAVING | ORDER BY}"
[DISPLAY="{ALL | flexfield qualifier | segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[NUM=":structure defining lexical" |
MULTINUM="{Y | N}"]
[TABLEALIAS="code combination table alias"]
[OPERATOR="{ = | < | > | <= | >= | != | "||" |
BETWEEN | QBE}"]
[OPERAND1=":input parameter or value"]
[OPERAND2=":input parameter or value"]

• FND FLEXIDVAL

·         This user exits allows you to use Flex fields in Reports

Syntax:
FND FLEXIDVAL
CODE="flexfield code"
APPL_SHORT_NAME="application short name"
DATA=":source column name"
[NUM=":structure defining source column/lexical"]
[DISPLAY="{ALL|flexfield qualifier|segment number}"]
[IDISPLAY="{ALL|flexfield qualifier|segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[VALUE=":output column name"]
[DESCRIPTION=":output column name"]
[APROMPT=":output column name"]
[LPROMPT=":output column name"]
[PADDED_VALUE=":output column name"]
[SECURITY=":column name"]

user exit available to populate key flexfields for display

CODE means Key flexfield code(GL# is for Accounting Flex field, for all other check the table FND_ID_FLEXS)
NUM is the structure of the key flex field(Chart of Accounts Number)
DATA is where you store the retrieved data (your sql output).

Example

SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
OUTPUT=":P_SEC_SEG"
MODE="SELECT"
DISPLAY=":P_SEC_SEG_VAL" ')

The userexit call FND FLEXIDVAL :

SRW.REFERENCE(:SEC_SEG);
SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#"
DATA=":SEC_SEG"
APPL_SHORT_NAME="SQLGL"
VALUE=":SEC_SEG_DISP"
DISPLAY="ALL"
NUM=":P_STRUCT_NUM" ');
return(:sec_seg_disp);

Jay

Time:  30 Min.

1) What is the difference between Per-Query and Post Query?

a)      Post-Query fires only once, Pre-query fires for every record fetched into the block

b)     Pre-Query fires only once, Post-Query fires for every record fetched into the block

c)      Both fires only once for every record fetched into the block

d)     None of above

2) Select the Sequence of triggers that fire for a Text Item

1)      When-New-Item

2)      When-Validate

3)      Key-Next

4)      Pre-Text

a)      1,2,3,4

b)     4,1,3,2

c)      4,1,2,3

d)     3,2,1,4

3) Maximum number of Triggers that you can apply on a table (approx)?

a)      4

b)      8

c)      12

d)     16

4) What is the output of the following SQL statement?

     Select * from EMP where (ROWID, 1) in (Select ROWID, mod(ROWNUM, 2) from EMP);

a)      First two rows form EMP table.

b)      Even rows from EMP table.

c)      Odd rows from EMP table

d)     Last two rows form EMP table

e)      None of the above

5) Which oracle supply packages always you to run jobs that use defined times?

a)      DBMS_RUN

b)     DBMS_JOB

c)      RUN_JOB

d)     SCHEDULE_JOB

6) Why do stored producers and function improve performance (Choose Two).

a)      They reduce network round trips

b)     They postpone PL/SQL parsing until runtime

c)      They allow the application to perform high speed processing locally

d)     They reduce the number of calls to the database and decrease network traffic by using local PL/SQL engine

7) When creating stored procedure and function which construct allows you to transfer values

     to and from the calling environment?

a)      Local variables

b)     Formal arguments

c)      Boolean variables

d)     None

8) Can a Block be associated to multiple objects like a Table, View or Synonym?

a)      True

b)     False

c)      Can’t say

d)     None of the above.

9) When-Validate-Item trigger can have navigational built-ins in them.

a)      True

b)     False

c)      Can’t say

d)     None of the above

10) If the same property is defined in both Property Class and the Visual Attributes then 

      property value defined in which takes precedence over the other.

a)      Visual Attributes

b)      Property Class

c)      Same precedence

d)     None of the above

11) Field variables and form System variables can be referenced in the library by

a)      :Global.<variable name>

b)      Get_Variable()

c)      System_Variable()

d)     None of the above

12) Procedures created by forms builder to ensure Master Detail relationship between

      Master and Detail block are

    1) Query_Master_Details

    2) Check_Master_Detail

3) Clear_All_Master_Details

4) Check_Delete_Master

  

a)      1 & 4

b)      2 & 3

c)      1 & 3

d)     3 & 4

13) Which of the following property determines the type of an item.

a)      Form.Item Type

b)      Block.Item Type

c)      Item Type

d)     None

14) Which of the following function is used to check if a Parameter List is created or not.

a)      Get_Paramaterlist()

b)      Find_Paramaterlist()

c)      Id_Null()

d)     A or B or C

15) Global variables are defined with __________ data type and ________ size.

a)      Long & 2 GB

b)      LOB & 4 GB

c)      Char & 255

d)     Any Data type and Any Size

16) Pick the correct sequence in which the trigger fires when a form loads.

1)      Post-Logon

2)      Pre-Form

3)      When-New-Record-Instance

4)      Pre-Record

a)      1,2,3,4

b)     1,2,4,3

c)      4,3,1,2

d)     2,1,4,3

17) A Data block can be associated with.

a)      Table or View

b)      Procedure

c)      A & B

d)     None

18) Two types of parameters available in report tool are _________ and _________

a)      User & System

b)      User & Global

c)      Global & Local

d)     Local & User

19) The minimum number of groups required to create a Cross Tab Report (Matrix) is

a)      1

b)      2

c)      3

d)     4

e)      Any of the above

20) ________ Function is used to display Error Messages in reports.

a)      Disp_Alert()

b)      Show_Message()

c)      Disp_Error()

d)     Message()

21) __________ Function is used to restrict the number of records fetched by report tool.

a)      Restrict_Rows()

b)      Fetch_Rows()

c)      Set_Maxrows()

d)     None of the above 

22) Pick the output of the following query.

       Select ‘A’ form dual

       Union

       Select 1 from dual;

       a) A1

       b) 1A

       c) A

       d) 1

       e) None of the above

23) CREATE OR REPLACE TRIGGER upd_emp_comm.

      FOR EACH ROW

      Begin

        <<Trigger Body>>

      End;

     

      Which of the following statements must you add to the above trigger definition to 

       make sure that this trigger executes only after updating the commission column of 

       the EMP table.

a)      After Update(Comm) on EMP

b)      After Update on EMP

c)      After Update of Comm on EMP

d)     After Comm Update on EMP

24) What happens to the following trigger?

       CREATE OR REPLACE TRIGGER ins_emp_summary

       After insert ON EMP

       Begin

         Insert into EMP_SUMMARY(empno, period, salary) values (:new.empno, SYSDATE, :new.sal)

       End;

a)      Compilation Error

b)      Created successfully but runtime error

c)      Runtime Error

d)     Trigger fails to Compile

25) State whether the following statements are True or False.

i)        A trigger body cannot have more than 32 lines of PL/SQL code.

ii)      The size of trigger cannot be more than 32K

iii)    Oracle allows upto 32 triggers to Cascade at any one time

a)      F T T

b)      F F F

c)      F F T

d)     F T F

26) What is the output of the following SQL statement?

       Select * from EMP where (ROWID,0) in (Select ROWID, mod(RONUM,4) from EMP);

a)      Top Four rows from EMP table

b)      Last Four rows form EMP table

c)      Four rows with ROWID in Ascending order

d)     Four rows with ROWID in Descending order

e)      None of the above

27) What is the output of the following Queries?

       Select ‘Tom’’s Oracle Site.’ from dual;

       Select ‘A ‘’fat’’ woman.’ from dual;

a)      ‘Tom’’s Oracle Site.

      ‘A ‘’Fat’’ woman’.

b)     ‘Tom’s Oracle Site.

‘A ‘Fat’ woman.

c)      ‘’Tom’’s Oracle Site.

‘A ‘Fat’’ woman.

d)     None of the above.

28) Which type of argument passes a value from a calling environment?

a)      VARCHER2

b)      BOOLEAN

c)      OUT

d)     IN

29) Under which situation do you create a server-side procedure?

a)      When the procedure contains no SQL statements.

b)      When the procedure contains no PL/SQL commands.

c)      When the procedure needs to be used by many client applications accessing several remote databases.

d)     When the procedure needs to be used by many users accessing the same schema objects on a local database.

30) Which two statements about the overloading feature of packages are true?

a)      Only local or packaged sub programs can be overloaded.

b)      Overloading allows different functions with the same name that differ only in their return types.

c)      Overloading allows different subprograms with the same number, type and order of the parameter.

d)     Overloading allows different subprograms with the same name and same number or type of the parameters.

e)      Overloading allows different subprograms with the same name but different in either number or type or order of parameters.