Oracle Applications Interview Questions

1. Are you familiar with User exits in Reports?

User Exists are 9 Types.

• FND SRWINIT: Fetches concurrent request information and sets up the profile options, in Before Report Trigger

• FND SRWEXIT: Ensures that all the memory allocated for Oracle AOL user exits has been freed up properly- After Report Trigger

• FND FORMAT_CURRENCY: This is used for MULTIPLE REPORTING CURRNECY.

• FND FLEXSQL: Before Report Trigger - these user exits allow you to use flex fields in your reports

• FND FLEXIDVAL: used to display flex field information like prompt, value etc

User exit, when called from Report triggers, moves the control from report to the outside program, there it performs the action programmed, and returns back to report environment. The program makes USER to EXIT from ongoing environment to perform certain.

2. Do you know what are User Profiles in Apps? Any e.g.

User Profile: It is a set of changeable options through which your application runs. User profile can be defined at various levels. They are

1. System 2. Application 3. Responsibility 4. User

2.1. What is the difference between AFTER PARAMETER TRIGGER and BEFORE REPORT TRIGGER?

After parameter Trigger: It will fire after the parameter form is displayed. Here we can do validation on parameter values

Before Report Trigger: It will fire before the report is executed and after the query is parsed and data is fetched.

2.2. How are text files read and written in Oracle PL/SQL?

Yes there is a facility UTL_FILE , using this package you can read and write data into text files.

Or TEXT_IO package also there. UTL_FILE is for backend procedures and this writes and reads from DB server, while TEXT_IO package is used in forms to read and write from Client machine

2.3 What are the types of exceptions you face in utl_file ?

UTL_FILE.INVALID_PATH -- Specified path does not exist or is not visible to Oracle

UTL_FILE.FILE_OPEN -- File is already open

invalid_filename -- A file with the specified name does not exist in the path

access_denied -- Access to the file has been denied by the operating system

Path is defined in v$parameter where name = 'utl_file_dir'

A	Append Text
AB	Append Byte Mode
R	Read Text
RB	Read Byte Mode
W	Write Text
WB	Write Byte Mode

*** How do i force to use an index? – Using concept of Oracle HINTS

2.4 Bulk Collect with PL/SQL

In PL/SQL, using bulk collect, it is possible to select the values from mutliple rows in one go.

Query to find trace file in Oracle Apps.

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
--'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

Find Current Query for a Concurrent Program.

Following query gives us the current running query for a concurrent program in Oracle Applications:

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = 30600658
AND a.phase_code = 'R'

select /*+ ORDERED */ sql_text
from v$session s, v$sqltext t
where t.address = s.sql_address
and s.sid = 2348
order by piece

SQL Query to get Responsibility Name for Concurrent Program

SELECT DISTINCT
  FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
  APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
    FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE 'Conc Prog Name'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';