1)
How will you migrate Oracle General Ledger
Currencies and Sets of Books Definitions from one environment to another
without rekeying? Will you use FNDLOAD?
FNDLOAD can’t be used in the scenario. You can use migrator
available in "Oracle iSetup" Responsibility
2)
This is a very tough one, almost impossible to
answer, but yet I will ask. Which Form in Oracle Applications has most number
of Form Functions?
"Run
Reports". And why not, the Form Function for this screen has a parameter
to which we pass name of the "Request Group", hence securing the list
of Concurrent Programs that are visible in "Run Request" Form. Just
so that you know, there are over 600 form functions for "Run Reports"
3)
Which responsibility do you need to extract
Self Service Personalization’s?
Functional
Administrator
4)
Can you list any one single limitation of
Forms Personalization feature that was delivered with 11.5.10
you can’t implement interactive
messages, i.e. a message will give multiple options for Response. The best you
can get from Forms Personalization to do is popup up Message with OK option.
5)
You have just created two concurrent programs
namely "XX PO Prog1" & "XX PO Prog2". Now you wish to
create a menu for Concurrent Request submission such that only these two Concurrent
Programs are visible from that Run Request menu. Please explain the steps to
implement this?
a) Define a request group, let’s
say with name "XX_PO_PROGS"
b) Add these two concurrent programs to the request group
"XX_PO_PROGS"
c) Define a new Form Function that is attached to Form "Run Reports"
d) In the parameter field of Form Function screen, enter
REQUEST_GROUP_CODE="XX_PO_PROGS"
REQUEST_GROUP_APPL_SHORT_NAME="XXPO"
TITLE="XXPO:XX_PO_PROGS"
e) Attach this form function to the desired menu.
6)
Does Oracle 10g support rule based
optimization?
The official stance is that RBO is
no longer supported by 10g.
7)
Does oracle support partitioning of tables in
Oracle Apps?
Yes, Oracle does support
partitioning of tables in Oracle Applications. There are several
implementations that partition on GL_BALANCES. However your client must buy
licenses to if they desire to partition tables. To avoid the cost of licensing
you may suggest the clients may decide to permanently close their older GL
Periods, such that historical records can be archived.
Note: Before running the archival process the second time, you must clear down
the archive table GL_ARCHIVE_BALANCES (don’t forget to export archive data to a
tape).
8)
What will be your partitioning strategy on
GL_BALANCES?
This really depends upon how many periods are regularly reported upon, how many
periods are left open etc. You can then decide to partition on period_name, or
period ranges, or on the status of the GL Period.
9)
Does Oracle support running of gather stats on
SYS schema in Oracle Apps?
If your Oracle Applications instance is on 10g, then you can decide to run
stats for SYS schema. This can be done by exec
dbms_stats.gather_schema_stats('SYS');
Alternately using command dbms_stats.gather_schema_stats('SYS', cascade=>TRUE,
degree=>20);
I will prefer the former with default values.
If you wish to delete the stats for SYS use exec dbms_stats.delete_schema_stats
('SYS');
You can schedule a dbms_job for running stats for SYS schema.
10) Can you use concurrent
program "Gather Schema Statistics" to gather stats on sys schema in
oracle apps?
No, "Gather Schema
Statistics" has no parameters for SYS schema. Please use dbms_job.
11) Which table is used to
provide drill down from Oracle GL into sub-ledger?
GL_IMPORT_REFERENCES
12) What is the
significance of profile option “Node Trust Level” in Oracle Apps.
If this profile option is set to a value of external against a server, then it
signifies that the specific mid-tier is External i.e. it will be exposed to the
www. In other words this server is not within the firewall of your client. The
idea behind this profile option is to flag such middle-tier so that special
restrictions can be applied against its security, which means a very restricted
set of responsibilities will be available from such Middle-Tier.
13) What is the
significance of profile option “Responsibility Trust Level”.
In order to make a responsibility
accessible from an external web tier, you must set profile option
“Responsibility Trust Level” at responsibility level to “External”. Only those
responsibilities that have this profile option against them will be accessible
from External Middle tiers.
14) What else can you
suggest to restrict the access to screens from external web tiers?
You may use URL filtering within
Apache.
15) What is the role of
Document Manager in Oracle Purchasing?
POXCON is an immediate concurrent
program. It receives pipe signal from the application when a request is made
for approval/reservations/receipts.
16) How to debug a
document manager in Oracle Apps?
Document manger runs within the
concurrent manager in Oracle Applications. When an application uses a
Document Manager, it sends a pipe signal which is picked up by the document
manager.
There are two mechanisms by which to trace the document manager
1. Set the debugging on by using profile option
STEP 1. Set profile option "Concurrent: Debug
Flags" to TCTM1
This profile should only generate debugs when set at Site
level(I think, as I have only tried site), because Document Manager runs
in a different session.
STEP 2. Bounce the Document Managers
STEP 3. Retry the Workflow to generate debugs.
STEP 4. Reset profile option "Concurrent: Debug
Flags" to blank
STEP 5. have a look at debug information in table
fnd_concurrent_debug_info
2. Enable tracing for the document managers
This can be done by setting profile option “Initialization SQL Statement –
Custom” against your username before reproducing the issue. The value of this
profile will be set so as to enable trace using event 10046, level 12.
17) You have written a
Java Concurrent Program in Oracle Apps. You want to modify the CLASSPATH such
that new class CLASSPATH is effective just for this program.
In the options field of the
concurrent program you can enter something similar to below.
-cp <your custom lib path used by Java Concurrent Program>
:/home/xxvisiondev/XXDEVDB/comn/java/appsborg.zip:/home/xxvisiondev/XXDEVDB/comn/java
18) How will you open a
bc4j package in jdeveloper?
Oracle
ships a file named server.xml with each bc4j package. You will need to ftp that
file alongside other bc4j objects (VO’s, EO’s, AM, Classes etc).
Opening the server.xml will load the complete package starting from AM (application
module). This is a mandatory step when building Extensions to framework.
19) In OA Framework
Self-Service screen, you wish to disable a tab. How will you do it?
Generally
speaking, the tabs on a OA Framework page are nothing but the Sub Menus. By
entering menu exclusion against the responsibility, you can remove the tab from
self service page.
20) In self service, you
wish to change the background color and the foreground text of the OA Framework
screens to meet your corporate standards. How will you do it?
You
will need to do the below steps
a….Go to Mid Tier, and open $OA_HTML/cabo/styles/custom.xss
b…Enter below text( change colours as needed)
<style name="DarkBackground">
<property
name="background-color">#000066</property>
</style>
<style name="TextForeground">
<property
name="color">#0000FF</property>
</style>
c… cd $OA_HTML/cabo/styles/cache
d…Take a backup of all the css files.
e…Delete all the files of following pattern oracle-desktop*.css
The idea here is to delete the cache. Next time when you logon to Oracle Apps
Self Service, the Framework will rebuild the css file if found missing for your
browser.
21) Can you extend and substitute
a root AM (Application Module) in OA Framework using JDeveloper.
0 You can extend the AM in
jDeveloper, but it doesn’t work (at least it didn’t work in 11.5.9). I am
hopeful that Oracle will deliver a solution to this in the future.
22) In a workflow
notification, you have a free text response field where the user enters the
Vendor Number for the new vendor. You want to validate the value entered in the
notification response field upon the submission of a response. How will you do
it?
You
will need to attach a post notification function to the Workflow Notification.
The PL/SQL code will look similar to below:-
The below code will display an error in the notification when user attempts to
create a Duplicate Vendor Number.
PROCEDURE validate_response_from_notif
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
) IS
l_nid
NUMBER;
l_activity_result_code VARCHAR2(200);
v_newly_entered_vendor_num VARCHAR2(50);
CURSOR c_get_response_for_new_vendor IS
SELECT wl.lookup_code
FROM wf_notification_attributes wna
,wf_notifications
wn
,wf_message_attributes_vl wma
,wf_lookups
wl
WHERE wna.notification_id = l_nid
AND wna.notification_id =
wn.notification_id
AND wn.message_name = wma.message_name
AND wn.message_type = wma.message_type
AND wna.NAME = wma.NAME
AND wma.SUBTYPE = 'RESPOND'
AND wma.format = wl.lookup_type
AND wna.text_value = wl.lookup_code
AND wma.TYPE = 'LOOKUP'
AND decode(wma.NAME, 'RESULT', 'RESULT',
'NORESULT') = 'RESULT';
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;
OPEN c_get_response_for_new_vendor;
FETCH c_get_response_for_new_vendor
INTO l_activity_result_code;
CLOSE c_get_response_for_new_vendor;
v_newly_entered_vendor_num :=
wf_notification.getattrtext(l_nid,'NEWLY_ENTERED_VENDOR_NUM_4_PO');
IF l_activity_result_code = 'NEW_VENDOR'
AND does_vendor_exist(p_vendor =>
v_newly_entered_vendor_num)
THEN
RESULT := 'ERROR: VendorNumber you entered
already exists';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RESULT := SQLERRM;
END validate_response_from_notif;
23) How to make concurrent
program end with warning?
If the concurrent program is of type PL/SQL, you can assign a value of 1 to the
“retcode” OUT Parameter.
For a Java Concurrent program, use the code similar to below
ReqCompletion lRC;
//get handle on request completion object for reporting status
lRC = pCpContext.getReqCompletion();
lRC.setCompletion(ReqCompletion.WARNING, "WARNING");
24) How do you link a Host
type concurrent program to Concurrent Manager?
Assuming your executable script is LOADPO. program, then use the commands below
cd $XXPO_TOP/bin
ln -s $FND_TOP/bin/fndcpesr $XXPO_TOP/bin/LOADPO
25) How do you know if a
specific Oracle patch has been applied in apps to your environment?
Use
table ad_bugs, in which column bug_number is the patch number.
SELECT bug_number
,to_char(creation_date, 'DD-MON-YYYY
HH24:MI:SS') dated
FROM apps.ad_bugs
WHERE bug_number = TRIM('&bug_number') ;
26) How do you send a
particular Oracle Apps Workflow Activity/Function within a workflow process
into background mode?
If
cost of the workflow activity is greater than 50, then the workflow activity
will be processed in background mode only, and it won’t be processed in online
mode.
27) What are the various
ways to kick-off a workflow
You can eiter use wf_engine.start_process or you can attach a Runnable process
such that it subscribes to a workflow event.
28) When starting (kicking
off) an oracle workflow process, how do you ensure that it happens in a
background mode?
--a)if
initiating the process using start_process, do the below
wf_engine.threshold := -1;
wf_engine.createprocess(l_itemtype
,l_itemkey
,'<YOUR PROCESS NAME>');
wf_engine.startprocess(l_itemtype, l_itemkey)
--B) When initiating the workflow process through an event subscription,
set the Execution Condition Phase to be equal to or above 100 for it to be
executed by background process.
29) On 10g, how will you
use awr?
By
running below scripts. These are both the same scripts, but with differing
parameters.
$ORACLE_HOME/rdbms/admin/awrrpt.sql
$ORACLE_HOME/rdbms/admin/awrrpti.sql
30) How will you configure
Apache to run in Debug mode, specifically useful when debugging iProcurement (prior
to 11.5.10).
After
11.5.10, FND Logging can be used for debugging Oracle iProcurement.
Prior to 11.5.10
----STEPS IN A NUTSHELL-----
cd $ORACLE_HOME/../iAS/Apache
vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/ssp_init.txt
DebugOutput=/home/<<SID>>/ora9/iAS/Apache/Apache/logs/debug.log
DebugLevel=5
DebugSwitch=ON
vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.conf
ApJServLogLevel debug
vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.properties
log=true
31) How will you add a new
column to a List Of Values ( LOV ) in Oracle Applications Framework? Can this
be done without customization?
Yes,
this can be done without customization, i.e. by using OA Framework Extension
coupled with Personalization. Implement the following Steps :-
a) Extend the VO (View Object ), to implement the new SQL required to support
the LOV.
b) Substitute the base VO, by using jpximport [similar to as explained in Link]
c) Personalize the LOV Region, by clicking on Add New Item. While adding the
new Item, you will cross reference the newly added column to VO.
32) Can you do
fnd_request.submit_request from SQL Plus in Oracle?
You will need to initialize the
global variables first using fnd_global.initialize
DECLARE
v_session_id INTEGER := userenv('sessionid') ;
BEGIN
fnd_global.initialize
(
SESSION_ID =>
v_session_id
,USER_ID
=> <your user id from
fnd_user.user_id>
,RESP_ID
=> <You may use Examine from the
screen PROFILE/RESP_ID>
,RESP_APPL_ID
=> <You may use Examine from the screen
PROFILE/RESP_APPL_ID>
,SECURITY_GROUP_ID =>
0
,SITE_ID
=> NULL
,LOGIN_ID
=> 3115003--Any number here
,CONC_LOGIN_ID
=>
NULL
,PROG_APPL_ID
=>
NULL
,CONC_PROGRAM_ID
=>
NULL
,CONC_REQUEST_ID
=>
NULL
,CONC_PRIORITY_REQUEST =>
NULL
) ;
commit ;
END ;
/
Optionally you may use fnd_global.apps_initialize, which internally calls
fnd_global.initialize
fnd_global.apps_initialize(user_id => :user_id,
resp_id => :resp_id,
resp_appl_id => :resp_appl_id,
security_group_id => :security_group_id,
server_id => :server_id);
By doing the above, your global variables upon which Concurrent Managers
depend upon will be populated. This will be equivalent to logging into Oracle
Apps and submitting the concurrent request from a responsibility.
33) You are told that the
certain steps in the Oracle Apps Form/Screen are running slow, and you are
asked to tune it. How do you go about it.
First thing to do is to enable
trace. Preferably, enable the trace with Bind Variables. This can be done by
selecting menu Help/Diagnostics/Trace/”Trace With Binds and Wait”
Internally Oracle Forms issues a statement similar to below:-
alter session set events='10046 trace name context forever, level 12' ;
Enable Trace
with Bind Variables in Apps
This will enable the trace with Bind Variable values being shown in the trace
file.
The screen in Oracle Apps will also provide the name of the trace file which is
located in directly identified by select value from v$parameter where name like
'%us%r%dump%'
Doing a tkprof with explain plan option, reviewing plans and stats in
trace file can help identify the slow performing SQL.
34) What is the difference
between running Gather Stats and “Program – Optimizer [RGOPTM]” in Oracle
General Ledger?
“Gather Stats” will simply gather
the stats against existing tables, indexes etc. However Gather Stats does not
create any new indexes. But “Program – Optimizer [RGOPTM]” can create indexes
on GL_CODE_COMBINATIONS, provided accounting segment has the indexed flag
enabled,
35) You have written a
piece of code in POR_CUSTOM_PKG for Oracle iProcurement, but its not taking any
effect? What may be the reason?
Depending upon which procedure in
POR_CUSTOM_PKG has been programmed, one or more of the below profile options
must be set to Yes
POR: Enable Req Header Customization
POR: Enable Requisition Line Customization
POR: Enable Req Distribution Customization
36) What is the key
benefit of punching out to supplier’s catalogs rather than loading their
catalogs locally in Oracle iProcurement?
Punch out has several advantages
like, Catalogs don’t need to be loaded locally saves space on your system. You
can get up-to-date list of catalogs by punching out and also you get the
benefit of up-to-date pricing information on vendor items.
38) Does Oracle Grants use
its own schema or does it uses Oracle Project accounting schema?
Although Oracle Grants has its own
schema i.e. GMS, it reuses many of the tables with in Oracle Projects Schema
like PA_PROJECTS_ALL, PA_EXPENDITURE_ITEMS_ALL, PA_EXPENDITURE_TYPES etc.
39) How to make an Oracle
Report Type concurrent program produce an excel friendly output?
Comma can be concatenated between
the column values, however a better option is to create tab delimited file, as
it takes care of commas within the string.
For this, use SQL similar to below in the report
select 'a' || chr(9) || 'b' from dual;
40) What are the settings
needed for printing bitmap reports?
Get your DBA to configure two
files i.e. uiprint.txt & default.ppd
For details, refer to Meta link Note 189708.1
41) For a PL/SQL based
concurrent program do you have to issue a commit at the end?
The concurrent program runs within
its own new session. In APPS, the default database setting enforces a commit at
the end of each session. Hence no explicit COMMIT is required.
42) What is the best way
to add debugging to the code in apps?
Use fnd_log.string , i.e. FND Logging. Behind the scenes Oracles FND Logging
uses autonomous transaction to insert records in a table named
fnd_log_messages.
For example
DECLARE
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => 'xxxx ' || 'pkg/procedurename '
,message => 'your debug message here');
END ;
Three profile options effecting FND Logging are
FND: Debug Log Mode
FND: Debug Log Enabled
FND: Debug Log Module
43) If you wish to trigger
of an update or insert in bespoke table or take some action in response to a
TCA record being created or modified, how would you do it? Will you write a
database triggers on TCA Tables?
There are various pre-defined
Events that are invoked from the Oracle TCA API’s.
TCA was Oracle’s first initiative towards a fully API based approach, which
means the screen and the processes all use the same set of APIs for doing same
task.
In order to take an action when these events occur, you can subscribe a custom
PL/SQL procedure or a Custom Workflow to these events. Some of the important
TCA events are listed below:-
oracle.apps.ar.hz.ContactPoint.update
oracle.apps.ar.hz.CustAccount.create
oracle.apps.ar.hz.CustAccount.update
oracle.apps.ar.hz.CustAcctSite.create
oracle.apps.ar.hz.CustAcctSite.update
oracle.apps.ar.hz.CustAcctSiteUse.create
oracle.apps.ar.hz.CustAcctSiteUse.update
oracle.apps.ar.hz.Location.create
oracle.apps.ar.hz.Location.update
oracle.apps.ar.hz.Organization.create
oracle.apps.ar.hz.Organization.update
oracle.apps.ar.hz.PartySite.create
oracle.apps.ar.hz.PartySite.update
oracle.apps.ar.hz.PartySiteUse.create
oracle.apps.ar.hz.PartySiteUse.update
oracle.apps.ar.hz.Person.create
oracle.apps.ar.hz.Person.update
44) In Oracle OA
Framework, is the MDS page/document definition stored in database or in the
file system?
0
The MDS document details are loaded into database, in the following sets of
tables.
JDR_ATTRIBUTES
JDR_ATTRIBUTES_TRANS
JDR_COMPONENTS
JDR_PATHS
The Document is loaded via XML Importer, as detailed in XML Importer Article
45) In a Oracle Report data group, you have a “data link” between
two queries. How do you ensure that the data link is made Outer Joined?
The
data link is an Outer Join by default.
46) How does substitution work in OA Framework? What are the
benefits of using Substitution in OA Framework?
Based
on the user that has logged into OA Framework, MDS defines the context of the
logged in user. Based upon this logged in context, all applicable
personalization are applied by MDS. Given that substitutions are loaded as site
level personalizations, MDS applies the substituted BC4J objects along with the
personalizations. The above listed steps occur as soon as Root Application
module has been loaded.
The benefit of using Substitution is to extend the OA Framework without
customization of the underlying code. This is of great help during Upgrades.
Entity Objects and Validation Objects can be substituted. I think Root AM’s
can’t be substituted given that substitution kicks off after Root AM gets
loaded.
47) In OA Framework, once your application has been extended by
substitutions, is it possible to revert back to remove those substitutions?
yes,
by setting profile option “Disable Self-Service Personal%” to Yes, keeping in
mind that all your personalization’s will get disabled by this profile option.
This profile is also very useful when debugging your OA Framework based
application in the event of some error. By disabling the personalization via
profile, you can isolate the error, i.e. is being caused by your
extension/substitution code or by Oracle’s standard functionality.
48) How can you import invoices into Oracle Receivables?
You can either use AutoInvoice by populating tables
RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL &
RA_INTERFACE_SALESCREDITS_ALL.
Alternately you may decide to use API ar_invoice_api_pub.create_single_invoice
for Receivables Invoice Import.
49) How do you setup a context sensitive flexfield
Note:
I will publish a white paper to sho step by step approach.
But for the purpose of your interview, a brief explanation is…a)Create a
reference field, b) Use that reference field in “Context Field” section of DFF
Segment screen c) For each possible value of the context field, you will need
to create one record in section “Context Field Value” ( beneath the global data
elements).
50) Does Oracle iProcurement use same tables as Oracle Purchasing?
Yes, iProcurement uses the same
set of requisition tables as are used by Core Purchasing.
51) What is the name of the schema for tables in tca
AR (at least till 11.5.10, not sure about 11.5.10).
52) Are suppliers a part of TCA?
Unfortunately
not yet. However, Release 12 will be merging Suppliers into TCA.
53) What is the link between order management and purchasing
Internal
Requisitions get translated into Internal Sales Orders.
54)
How
would you know if the purchase order XML has been transmitted to vendor,
looking at the tables?
The
XML delivery status can be found from a table named ecx_oxta_logmsg. Use the
query below
SELECT
edoc.document_number
,decode(eol.result_code, 1000, 'Success', 'Failure') AS status
,eol.result_text
FROM
ecx_oxta_logmsg eol
,ecx_doclogs edoc
,ecx_outbound_logs eog
WHERE
edoc.msgid = eol.sender_message_id
AND
eog.out_msgid = edoc.msgid
ORDER
BY edoc.document_number
55) You have done forms personalization, now how will you move it
from one environment to another?
Use
FNDLOAD. For examples visit FNDLOAD Article
56) What are the key benefits of forms personalization over
custom.pll?
-->Multiple
users can develop forms personalization at any given point in time.
-->It is fairly easy to enable and disable forms personalization’s.
-->A programmer is not required to do simple things such as hide/disable
fields or buttons.
-->Provides more visibility on customizations to the screen.
57) Tell me some limitations of forms personalization when compared
to CUSTOM.pll?
-->Can't
create record group queries, hence can’t implement LOV Query changes.
-->Can't make things interactive, i.e. can’t have a message box that gives
multiple choices for example Proceed or Stop etc.
58) Give me one example where apps use partitioning?
WF_LOCAL_ROLES
59) Give me one example of securing attributes in iprocurement.
You can define Realm to bundle
suppliers into a Category. Such realm can then be assigned to the User using
Define User Screen. Security Attribute ICX_POR_REALM_ID can be used. By doing
so, the user will only be made visible those Punch out suppliers that belong to
the realm against their securing attributes.
60) Can you send blob attachments via workflow notifications?
Yes,
you can send BLOB Attachments.
61) Receiving items with less than PO price
After
we raise a PO and the po is approved and we do receiving but at the time of
receiving if the price of the item is less than the PO price do we update the
price in receipt and can anyone give the steps.
While
receiving if Invoice received is different than PO price Standard Purchase
price variance (PPV) report can be used to report the difference.
Optionally you can revise PO (it will create a new revision for PO) and get approvals
before receiving it.
Go to System Profiles:
Now the system profile form is opened
After applying the profile, save it.
