Thursday, March 11, 2010

Oracle Apps - UTL_FILE Example

DECLARE
FH UTL_FILE.FILE_TYPE;
LN VARCHAR2(300);
V_NAME VARCHAR2(30);
V_JOB VARCHAR2(30);
V_SAL NUMBER(7,2);
POS1 NUMBER(2);
POS2 NUMBER(2);
BEGIN
FH :=UTL_FILE.FOPEN (
'D:\ORACLE\VISDB\8.1.6\PLSQL\TEMP',
'A.LST',
'R');
LOOP
UTL_FILE.GET_LINE(FH, LN);
POS1 := INSTR(LN,',',1,1);
POS2 := INSTR(LN,',',1,2);
V_NAME := SUBSTR(LN,1,POS1-1);
V_JOB := SUBSTR(LN,POS1+1,POS2 - POS1 -1);
V_SAL := RTRIM(SUBSTR(LN,POS2+1));
INSERT INTO UTLX
VALUES
(V_NAME, V_JOB, V_SAL);
-- DBMS_OUTPUT.PUT_LINE(V_NAME||' '||V_JOB||' '||V_SAL);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(FH);
COMMIT;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;
/

Oracle Apps- Multi Org Setup FAQ

1. What is multi org architecture?



The multiorg architecture is meant to allow multiple companies or subsidiaries to store their records within a single database. The multiple Organization architecture allows this by partitioning data through views in the APPS schema. Multiorg also allows you to maintain multiple sets of books. Implementation of multiorg generally includes defining more than one Business Group.



2. How can I know if I am using multi org?



Run the SQL statement: select multi_org_flag from fnd_product_groups;



The result 'Y' means your database is setup for multiorg.



3. What are the relationships I can define in a multi org environment?



You define the relationships among inventory organizations, operating units, legal entities, Business Groups, and sets of books to create a multilevel company structure:



Business Groups separate major segments of a business. Each can have its own set of books. Each Group will also have a structure of other organizations classifications assigned to it.

1. Legal Entities post to a Set of Books
2. Operating Units are part of a Legal Entity
3. Inventory Organizations are part of an Operating Unit
4. Inventory Organizations define and maintain items used by other manufacturing modules (Order Entry, Purchasing, MRP, etc.). They also collect and pass data to the Financials modules.



4. I want to setup additional organizations, do I have to setup multiorg?



No, you do not need to setup multiorg. You do not have to be multi-org to have multiple organizations only if you intend to have multiple sets of books.



5. When my organization hierarchy changes, can I move an organization from one set of books or legal entity to another?



No, you should not try to move an organization from one set of books or legal entity to another because your data may not be valid for the new set of books or legal entity. Instead, you should disable the old organization and create a new organization for the appropriate set of books or legal entity. The new organization will contain your new data, and the disabled organization will act as an "old" or "history" entity that stores past transactions.



6. How can I setup a child org to be its own costing organization?



Steps:

1. Change the attribute control for "costing enabled" to organization level
2. Change the attribute control for "inventory asset value" to organization control Then you can make a child organization it's own costing organization by entering the organization name in the column labeled "Costing Organization"



7. How can I define organization restriction?



Use the Organization Access form (INVSDORA) to restrict the list of organizations displayed for each responsibility level. Refer to Oracle Inventory User's Guide for steps and important notes.



**WARNING** If you populate any rows in this form, you MUST populate a row for EACH responsibility that you wish to have access to that Organization.



8. What responsibility do I need to use to setup organization?



Use the General Ledger responsibility to define the Set of Books Use the Inventory Responsibility to define Organizations and other related information such as Inventory Key Flexfields, Locations, Workday calendar, other Organization Classifications, and other inventory information.



9. What are the main profile options relating to Organization setup and what are they used for?



1. HR: User Type = HR User This is necessary to allow the Inventory responsibility to complete the organization setup. Setting the profile to a value of 'User' as opposed to 'Payroll & User' will restrict the Inventory user from accessing any Payroll information if Oracle Payroll is installed.
2. HR: Business Group = {the users Business Group name} This points the responsibility to the appropriate Business Group. When multiple Business Groups are defined, you must associate each responsibility with one and only one Business Group. A responsibility can not see organization data from more than one Business Group.
3. MO: Operating Unit = {the users Operating Unit name} Used primarily in a multiorg environment.



This points the responsibility to the appropriate Operating Unit. Set the site level to the desired default operating unit If there is more than 1 Operating Unit Defined, this profile option must be set at the responsibility level for each responsibility.

SQL Loader Mostly asked Interview questions

1. What is SQL*Loader and what is it used for?



SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.



2. How does one use the SQL*Loader utility?



One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters.

Look at the following example:

sqlldr scott/tiger control=loader.ctl



This sample control file (loader.ctl) will load an external data file containing delimited data:

load data

infile 'c:\data\mydata.csv'

into table emp ( empno, empname, sal, deptno )

fields terminated by "," optionally enclosed by '"'



The mydata.csv file may look like this:

10001,"Scott Tiger", 1000, 40

10002,"Frank Naude", 500, 20



Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file.

load data

infile *

replace

into table departments ( dept position (02:05) char(4), deptname position (08:27) char(20) )

begindata

COSC COMPUTER SCIENCE

ENGL ENGLISH LITERATURE

MATH MATHEMATICS

POLY POLITICAL SCIENCE



3. Is there a SQL*Unloader to download data to a flat file?



Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on

spool oradata.txt

select col1 || ',' || col2 || ',' || col3 from tab1 where col2 = 'XYZ';

spool off



Alternatively use the UTL_FILE PL/SQL package:



rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter

declare

fp utl_file.file_type;

begin

fp := utl_file.fopen('c:\oradata','tab1.txt','w');

utl_file.putf(fp, '%s, %s\n', 'TextField', 55);

utl_file.fclose(fp);

end;

/



You might also want to investigate third party tools like TOAD or ManageIT Fast Unloader from CA to help you unload data from Oracle.

Monday, March 08, 2010

Unix Interview Questions

1)I know the name of a UNIX command, but I don't know what it does?
Use the 'whatis' command
whatis will give a brief description of the command
Example:
$ whatis grep
grep grep (1) - search a file for a pattern
2)I know what a UNIX command does, but I can't remember the name?
Use 'man -k'
This will do a keyword search of the man pages
Example:
$ man -k grep
egrep egrep (1) - search a file for a pattern using full regular expressions

fgrep fgrep (1) - search a file for a character string
fmlgrep fmlgrep (1f) - search a file for a pattern
grep grep (1) - search a file for a pattern
nisgrep nismatch (1) - utilities for searching NIS+ tables
3)What are .o files? .a files?

When a programmer writes a program, he begins by writing the code in a programming language. This 'source code' is kept in a text file. When he is ready to build the program, he runs the source code text through a compiler. This translates the source code into 'object code'. Object code is in a format that is understandable by the machine. ('Machine language') Note that object code is not portable across platforms. The same source code compiled on a Solaris machine will produce a different .o file when compiled on a HP-UX machine. All object files (.o, .a, .so) cannot be copied across different platforms.



When all the object code is compiled, the programmer next collects all the object files, and runs them through the linker. The linker basically collects all the object code, along with any other necessary code, and produces an executable (or 'binary') Object code can also be collected into a library file, or archive. (.a file) This file is created by the 'ar' command. It just collects all the object code into one file, and adds a table of contents to it. Library files can be linked into an executable the same way as .o files. Oracle delivers its code (except for the NT platform) as .o or .a files. These files are run through the linker to produce executables.

4)What are .so files?
.so files contain object code like .a and .o files. However, these libraries are not linked in when the programmer creates the executable. Instead, they are loaded in when the program runs. This allows the library to be shared among multiple programs, hence the name 'shared libraries' (on Windows, these are called DLLs) If a program cannot find the shared libraries it needs, it will not run. See also the questions on ldd and LD_LIBRARY_PATH.

5)How do I tell an HP10.20 object file from an HP11.0 object file?
The preferred way to tell whether an object was built with HP-UX 10.20 or HP-UX 11.0 is use the odump command.

odump -comp The -comp switch will give you the flags with which the object was compiled as will as the revision. The revision number for objects built with 10.x will appear as "A.10.xx" where xx is dependent on the exact revision and patch level. For 11.0 objects, the revision number will appear as "A.11.xx". 6)What is the best way to kill a process? First, use ps to get the process id (PID) Try using: kill This will give the process a chance to clean up after itself, like removing temp files, etc. Some processes will not accept or ignore this signal. If the process does not go away, use: kill -9 This signal cannot be ignored; it will definitely kill the process. The process will not have a chance to clean up after itself though. Use kill -9 only if necessary. Note that you must be the process owner or the superuser to kill a process. 7)How do I get the value of SHMMAX? Solaris: use 'sysdef | grep SHMMAX' HP-UX: use 'grep SHMMAX /usr/conf/master.d/core-hpux' 8)I want to eject the cdrom, but it says it is busy. How do I find who has it locked? Use the 'fuser' command Type: fuser -u /cdrom (or whatever mount point the cdrom is on) This will give a process ID, followed by a letter The letter 'c' indicates this process is using the /cdrom directory as it's current directory From the process id, you should be able to find the user 9)I'm trying to uncompress a file that I know is in this directory, but it keeps telling me the file cannot be found? Make sure the file has an extension of .Z (capital Z). Uncompress will try to add the extension if it does not have it and you will get something like this: $ uncompress 504305t.z 504305t.z.Z: No such file or directory 10)How do I remove a file that begins with a hyphen? (Like -filename) rm will normally complain about an invalid option if you say: rm -filename Most UNIX commands will bhave this way, treating the characters after the hyphen as an option. rm has an option to say 'end of options, the next parameter is a filename' The option is '--' (double hyphen) So you can use: rm -- -filename Most UNIX commands have a -- option like this to signify end of options. 11)My Escape key does not work in vi? Help! Go to your NCD boot server machine (orlsun1, 2, 10, or 11) There should be a file called 'xh' in your home directory. Add these two lines to the file: /usr/openwin/bin/xmodmap -e 'keycode 9 = grave asciitilde' & /usr/openwin /bin/xmodmap -e 'keycode 14 = Escape' & Reboot your NCD. Now, the ~ key (at the top left of the keyboard) is now the Escape key. The ~ and ` are on the key between the Shift key and Z. (Control-[ also works as Escape) Shell Program – UNIX Unix shell script is nothing but a collection of unix commands written as a block to be executed sequentially Simple Commands: ls; grep; cut; cat 12)Where to get the list of commands: /usr/bin Help about these commands: man Accessing shell scripts from Oracle Apps 13)What can be done using a shell script To call a sql*loader script, to call a sql*plus program, to do an FTP, send email with attachment, submit a concurrent program…. FILENAME=$APPLCSF/out/arjnltrx.out ATTACHMENT=arjnltrx SUBJECT="Receivables Posting“ RECIPIENT= amudhan@crm.hcltech.com CARBONS= srini@crm.hcltech.com DATE=`date` if test -f $FILENAME then uuencode "$FILENAME" "$ATTACHMENT"|mailx -s "$SUBJECT" -c "$CARBONS" "$RECIPIENT"; chown applfin:dba $FILENAME Else echo "No new file on $DATE"|mailx -c "$CARBONS" "$RECIPIENT“ fi ret_val=`CONCSUB apps/apps SQLAP 'Payables Manager' AMANICKA WAIT=Y CONCURRENT SQLAP APXINRIR America Standard " " BWEBB '10-JAN-2001' 10-JAN-2001' 'Jan-01' 'No' 'No'` request_id=`echo $ret_val | cut -d ' ' -f3` status=`sqlplus -s apps/apps < /tmp/tempfile1
cut -d"~" -f1,2,3,4,6,7,8,9,10,11,12 /tmp/tempfile1 > $FILENAME.dat
}
load_work_table()
{
sqlldr -s userid=$LOGINID control=$ldrctl log=$ldrlog data=$FILENAME.dat bad=$ldrbad discard=$ldrdis
# Copy loader log file to the Concurrent request log file
echo "Loaded log for file : $FILENAME" >> $C_CS_TOP/log/l$REQID.req
cat $ldrlog >> $C_CS_TOP/log/l$REQID.req echo " “
}
load_file()
{
prepare_file;
if [ -s $FILENAME.dat ]; then
load_work_table;
fi
}

# MAIN Processing starts here

inputstring=$1
set_environment;
echo "Execution Timestamp $timestamp "
for FILENAME in `ls $indir/in`
do
echo "Processing file : $FILENAME"
load_file
Done

echo "Program complete“
exit 0

Use the following format to create the header section
#!/bin/sh
#
# NAME
# AUTHOR
# MODIFICATION HISTORY
# SYNOPSIS
# DESCRIPTION
# RETURN VALUE
#######################################

Test the status of command execution as a condition for proceeding. Use the $? variable to test for for zero (success). For ex: $? = 0 is success.

Use the exit command with a exit status code to end the shell script. Use exit 0 for success and exit 1 for error

Make the script executable. Change the permissions of the file to user and group read and execute (chmod 550).




Apps Interview Questions

Question: How will you migrate Oracle General Ledger Currencies and Sets of Books Definitions fromone environment to another without reKeying? Will you use FNDLOAD?
Answer: FNDLOAD can not be used in the scenario. You can use migrator available in "Oracle iSetup" Responsibility

Question: 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?
Answer: "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"

Question: Which responsibility do you need to extract Self Service Personalizations?
Answer:Functional Administrator

Question: Can you list any one single limitation of Forms Personalization feature that was delivered with 11.5.10
Answer:You can not 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.

Question: 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?
Answer:
a) Define a request group, lets 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.


Question: Does Oracle 10g support rule based optimization?
Answer: The official stance is that RBO is no longer supported by 10g.


Question: Does oracle support partitioning of tables in Oracle Apps?
Answer: 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).


Question: What will be your partitioning strategy on GL_BALANCES? Your views please?
Answer: 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.


Question: Does Oracle support running of gather stats on SYS schema in Oracle Apps?
Answer: 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.


Question: Can you use concurrent program "Gather Schema Statistics" to gather stats on sys schema in oracle apps?
Answer: No, "Gather Schema Statistics" has no parameters for SYS schema. Please use dbms_job.


Question: Which table is used to provide drill down from Oracle GL into sub-ledger?
Answer: GL_IMPORT_REFERENCES

Question: What is the significance of profile option “Node Trust Level” in Oracle Apps.
Answer: 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.


Question: What is the significance of profile option “Responsibility Trust Level”.
Answer: 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.


Question: What else can you suggest to restrict the access to screens from external web tiers?
Answer: You may use URL filtering within Apache.


Question: What is the role of Document Manager in Oracle Purchasing?
Answer: POXCON is an immediate concurrent program. It receives pipe signal from the application when a request is made for approval/reservations/receipts.


Question: How to debug a document manager in Oracle Apps?
Answer: 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.


Question: 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.
Answer: In the options field of the concurrent program you can enter something similar to below.
-cp :/home/xxvisiondev/XXDEVDB/comn/java/appsborg.zip:/home/xxvisiondev/XXDEVDB/comn/java


Question: How will you open a bc4j package in jdeveloper?
Answer: 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.


Question: In OA Framework Self-Service screen, you wish to disable a tab. How will you do it?
Answer: Generally speaking, the tabs on a OA Framework page are nothing but the SubMenus. By entering menu exclusion against the responsibility, you can remove the tab from self service page.

Question: 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?
Answer: 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)


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.


Question: Can you extend and substitue a root AM ( Application Module) in OA Framework using JDeveloper.
Answer: 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.

Question: 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?
Answer: 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;


Question: How to make concurrent program end with warning?
Answer: 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");


Question: How do you link a Host type concurrent program to Concurrent Manager?
Answer: Assuming your executable script is LOADPO.prog, then use the commands below
cd $XXPO_TOP/bin
ln -s $FND_TOP/bin/fndcpesr $XXPO_TOP/bin/LOADPO


Question: How do you know if a specific Oracle patch has been applied in apps to your environment.
Answer: 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') ;


Question: How do you send a particular Oracle Apps Workflow Activity/Function within a workflow process into background mode.
Answer: 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.

Question: What are the various ways to kick-off a workflow
Answer: You can eiter use wf_engine.start_process or you can attach a runnable process such ghat it subscribes to a workflow event.

Question: 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
,'');
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.


Question: On 10g, how will you use awr?
Answer: 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

Question : How will you configure Apache to run in Debug mode, specifically usefull when debugging iProcurement ( prior to 11.5.10).
Answer: 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/<>/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


Question: How will you add a new column to a List Of Values ( LOV ) in Oracle Applications Framework? Can this be done without customization?
Answer: 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.


Question: Can you do fnd_request.submit_request from SQL Plus in Oracle?
Answer: 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 =>
,RESP_ID =>
,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.

Question: 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.
Answer: 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
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.




Question: What is the difference between running Gather Stats and “Program – Optimizer[RGOPTM]” in Oracle General Ledger?
Answer: “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,


Question: 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?
Answer: 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


Question: What is the key benefit of punching out to suppliers catalogs rather than loading their catalogs locally in Oracle iProcurement?
Answer: Punchout 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.


Question: Does oracle have a test environment on exchange?
Answer: http://testexchange.oracle.com


Question: Does Oracle Grants use its own schema or does it uses Oracle Project Accounting schema?
Answer: 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.


Question: How to make an Oracle Report Type concurrent program produce an excel friendly output?
Answer: 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;


Question: What are the settings needed for printing bitmap reports?
Answer: Get your DBA to configure two files i.e. uiprint.txt & default.ppd
For details, refer to Metalink Note 189708.1


Question: For a PL/SQL based concurrent program do you have to issue a commit at the end?
Answer: 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.


Question: What is the best way to add debugging to the code in apps?
Answer: 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


Question: 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?
Answer: 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


Question: In Oracle OA Framework, is the MDS page/document definition stored in database or in the file system?
Answer: 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 XMLImporter, as detailed in XMLImporter Article


Question: 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?
Answer: The data link is an Outer Join by default.


Question: How does substitution work in OA Framework?
What are the benefits of using Substitution in OA Framework?
Answer: 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.

Question: In OA Framework, once your application has been extended by substitutions, is it possible to revert back to remove those substitutions?
Answer: yes, by setting profile option “Disable Self-Service Personal%” to Yes, keeping in mind that all your personalizations 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.

Question: How can you import invoices into Oracle Receivables?
Answer: 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.

Question: How do you setup a context sensitive flexfield
Answer: 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).

Question: Does Oracle iProcurement use same tables as Oracle Purchasing?
Answer: Yes, iProcurement uses the same set of requisition tables as are used by Core Purchasing.

Question: What is the name of the schema for tables in tca
Answer: AR (at least till 11.5.10, not sure about 11.5.10).

Question: Are suppliers a part of TCA?
Answer: Unfortunately not yet. However, Release 12 will be merging Suppliers into TCA.

Question: What is the link between order management and purchasing
Answer: Internal Requisitions get translated into Internal Sales Orders.

Question: How would you know if the purchase order XML has been transmitted to vendor, looking at the tables.
Answer: 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

Question: You have done forms personalization, now how will you move it from one environment to another?
Answer: Use FNDLOAD. For examples visit FNDLOAD Article


Question: What are the key benefits of forms personalization over custom.pll?
Answer:
-->Multiple users can develop forms personalization at any given point in time.
-->It is fairly easy to enable and disable forms personalizations.
-->A programmer is not required to do simple things such as hide/disable fields or buttons.
-->Provides more visibility on customizations to the screen.

Question: Tell me some limitations of forms personalization when compared to CUSTOM.pll?
Answer:
-->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.


Question: Give me one example where apps uses partitioning?
Answer: WF_LOCAL_ROLES


Question: Give me one example of securing attributes in iProcurement.
Answer: 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 Punchout suppliers that belong to the realm against their securing attributes.


Question: Can you send blob attachments via workflow notifications?
Answer: Yes, you can send BLOB Attachments.
(Source : Anilpassi)

Component- Versions

Q: How to find Version of Apache used with oracle apps 11i ? Log to Application tier as Operating System Usually called as APPLMGR ; go to location $IAS_ORACLE_HOME/Apache/Apache/bin and run command
./httpd -version
You will see output like

Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19

Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server

Above is output If you have installed 10g Application Server with 11i



Q: How to find Jinitiator Version ?

Check for file like appsweb_SID_HOSTNAME.cfg under $OA_HTML/bin defined by environment variable FORMS60_WEB_CONFIG_FILE & search for entry like jinit_ver_name , you will see entry like
jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ; if your version is 1.3.1.18 you will see entry like 1,3,1,18


Q: How to find Forms Version in 11i ?

Login to forms from frontend , on top menu bar of forms click on "Help" & Select "About Oracle Applications" go to "Forms Server " section. You should see entry like below depending on your forms version
Oracle Forms Version : 6.0.8.26.0
Which mean you are on forms version 6.0.8.26 . If you want to know whats your forms patchset level then subtract 9 from fourth digit which means for above case form patchset 17 is applied.


Q: How to find Forms Version in Apps from command Line ?

Enter "f60gen" on Forms Server and check for first line in output like
Forms 6.0 (Form Compiler) Version 6.0.8.26.0 (Production)
This confirms that you are on forms server version 6.0.8.26.0 and patch set 17. ( Patch Set = Fourth Digit - 9)

DBA Tables

Concurrent Manager

FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

FND

FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

AD / Patches

AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

Forms and Financials

How do you pass the parameters from one form to another form?
To pass one or more parameters to a called form, the calling form must perform the following steps in a trigger or user named routine execute the create_parameter_list built-in function to programmatically. Create a parameter list to execute the add parameter built-in procedure to add one or more parameters list. Execute the call_form, New_form or run_product built_in procedure and include the name or id of the parameter list to be passed to the called form.

What is a Layout Editor?
The Layout Editor is a graphical design facility for creating and arranging items and boilerplate text and graphics objects in your application's interface.

List the Types of Items?
Text item.
Chart item.
Check box.
Display item.
Image item.
List item.
Radio Group.
User Area item.

List system variables available in forms 4.0, and not available in forms 3.0?
System.cordination_operation
System Date_threshold
System.effective_Date
System.event_window
System.suppress_working

What are the display styles of an alert?
Stop, Caution, note

What built-in is used for showing the alert during run-time?
Show_alert.

What built-in is used for changing the properties of the window dynamically?
Set_window_property
Canvas-View

What are the different types of windows?
Root window, secondary window.

What is a predefined exception available in forms 4.0?
Raise form_trigger_failure

What is a radio Group?
Radio groups display a fixed no of options that are mutually Exclusive. User can select one out of n number of options.

What are the different type of a record group?
Query record group
Static record group
Non query record group

What are the menu items that oracle forms 4.0 supports?
Plain, Check,Radio, Separator, Magic

Give the equivalent term in forms 4.0 for the following. Page, Page 0?
Page - Canvas-View
Page 0 - Canvas-view null.

What triggers are associated with the radio group?
Only when-radio-changed trigger associated with radio group
Visual Attributes.

What are the triggers associated with a check box?
Only When-checkbox-activated Trigger associated with a Check box.

Can you attach an alert to a field?
No

Can a root window be made modal?
No

What is a list item?
It is a list of text elements.

List some built-in routines used to manipulate images in image_item?
Image_add
Image_and
Image_subtract
Image_xor
Image_zoom

Can you change the alert messages at run-time?
If yes, give the name of the built-in to change the alert messages at run-time. Yes. Set_alert_property.

What is the built-in used to get and set lov properties during run-time?
Get_lov_property
Set_lov_property
Record Group

What is the built-in routine used to count the no of rows in a group?
Get_group _row_count
System Variables

Give the Types of modules in a form?
Form
Menu
Library

Write the Abbreviation for the following File Extension 1. FMB 2. MMB 3. PLL?
FMB ----- Form Module Binary.
MMB ----- Menu Module Binary.
PLL ------ PL/SQL Library Module Binary.

List the built-in routine for controlling window during run-time?
Find_window,
get_window_property,
hide_window,
move_window,
resize_window,
set_window_property,
show_View

List the built-in routine for controlling window during run-time?
Find_canvas
Get-Canvas_property
Get_view_property
Hide_View
Replace_content_view
Scroll_view
Set_canvas_property
Set_view_property
Show_view
Alert

What is the built-in function used for finding the alert?
Find_alert
Editors

List the editors availables in forms 4.0?
Default editor
User_defined editors
system editors.

What buil-in routines are used to display editor dynamically?
Edit_text item
show_editor
LOV



What is an Lov?
A list of values is a single or multi column selection list displayed in a pop-up window

What is a record Group?
A record group is an internal oracle forms data structure that has a similar column/row frame work to a database table

Give built-in routine related to a record groups?
Create_group (Function)
Create_group_from_query(Function)
Delete_group(Procedure)
Add_group_column(Function)
Add_group_row(Procedure)
Delete_group_row(Procedure)
Populate_group(Function)
Populate_group_with_query(Function)
Set_group_Char_cell(procedure)

List the built-in routines for the controlling canvas views during run-time?
Find_canvas
Get-Canvas_property
Get_view_property
Hide_View
Replace_content_view
Scroll_view
Set_canvas_property
Set_view_property
Show_view
Alert

System.effective_date system variable is read only True/False?
False

What are the built_in used to trapping errors in forms 4?
Error_type return character
Error_code return number
Error_text return char
Dbms_error_code return no.
Dbms_error_text return char

What is Oracle Financials? (for DBA)
Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can meet accounting management demands with:
Oracle Assets: Ensures that an organization's property and equipment investment is accurate and that the correct asset tax accounting strategies are chosen.
Oracle General Ledger: Offers a complete solution to journal entry, budgeting, allocations, consolidation, and financial reporting needs.
Oracle Inventory: Helps an organization make better inventory decisions by minimizing stock and maximizing cash flow.
Oracle Order Entry: Provides organizations with a sophisticated order entry system for managing customer commitments.
Oracle Payables: Lets an organization process more invoices with fewer staff members and tighter controls. Helps save money through maximum discounts, bank float, and prevention of duplicate payment.
Oracle Personnel: Improves the management of employee- related issues by retaining and making available every form of personnel data.
Oracle Purchasing: Improves buying power, helps negotiate bigger discounts, eliminates paper flow, increases financial controls, and increases productivity.
Oracle Receivables:. Improves cash flow by letting an organization process more payments faster, without off-line research. Helps correctly account for cash, reduce outstanding receivables, and improve collection effectiveness.
Oracle Revenue Accounting Gives an organization timely and accurate revenue and flexible commissions reporting.
Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.

What are the design facilities available in forms 4.0?
Default Block facility.
Layout Editor.
Menu Editor.
Object Lists.
Property Sheets.
PL/SQL Editor.
Tables Columns Browser.
Built-ins Browser.

What is the most important module in Oracle Financials? (for DBA)
The General Ledger (GL) module is the basis for all other Oracle Financial modules. All other modules provide information to it. If you implement Oracle Financials, you should switch your current GL system first.GL is relatively easy to implement. You should go live with it first to give your implementation team a chance to be familiar with Oracle Financials.

What are the types of canvas-views?
Content View, Stacked View.

What is the MultiOrg and what is it used for? (for DBA)
MultiOrg or Multiple Organizations Architecture allows multiple operating units and their relationships to be defined within a single installation of Oracle Applications. This keeps each operating unit's transaction data separate and secure.
Use the following query to determine if MuliOrg is intalled:
select multi_org_flag from fnd_product_groups;

What is the difference between Fields and FlexFields? (for DBA)
A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field.
A flexfield is an Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization. There are two types of flexfields: key flexfields and descriptive flexfields.

Explain types of Block in forms4.0?
Base table Blocks.
Control Blocks.
1. A base table block is one that is associated with a specific database table or view.
2. A control block is a block that is not associated with a database table. ITEMS

What is an Alert?
An alert is a modal window that displays a message notifies the operator of some application condition

What are the built-in routines is available in forms 4.0 to create and manipulate a parameter list?
Add_parameter
Create_Parameter_list
Delete_parameter
Destroy_parameter_list
Get_parameter_attr
Get_parameter_list
set_parameter_attr

What is a record Group?
A record group is an internal oracle forms data structure that has a similar column/row frame work to a database table

What is a Navigable item?
A navigable item is one that operators can navigate to with the keyboard during default navigation, or that Oracle forms can navigate to by executing a navigational built-in procedure.

What is a library in Forms 4.0?
A library is a collection of Pl/SQL program units, including user named procedures, functions & packages

How image_items can be populate to field in forms 4.0?
A fetch from a long raw database column PL/Sql assignment to executing the read_image_file built_in procedure to get an image from the file system.

What is the content view and stacked view?
A content view is the "Base" view that occupies the entire content pane of the window in which it is displayed. A stacked view differs from a content canvas view in that it is not the base view for the window to which it is assigned

Saturday, March 06, 2010

How to check if the employee is rehired - Script

CREATE OR REPLACE PACKAGE rn_rehire_flag_pkg AS

FUNCTION get_rehire_flag_fnc

( p_business_group_id IN NUMBER

, p_assignment_id IN Number

)

RETURN VARCHAR2 ;

END rn_rehire_flag_pkg;

/

CREATE OR REPLACE PACKAGE Body rn_rehire_flag_pkg AS

FUNCTION get_rehire_flag_fnc

( p_business_group_id IN NUMBER

, p_assignment_id IN Number

)

RETURN VARCHAR2

IS

v_error_desc varchar2(250);

v_error_code varchar2(10);

v_flag varchar2(2) :=NULL ;

v_ohire_date date;

v_rhire_date date;

BEGIN

v_flag :=null;



begin

SELECT Original_Date_of_hire INTO v_ohire_date

FROM per_people_x

WHERE person_id = (SELECT DISTINCT person_id

FROM per_assignments_x

WHERE assignment_id = p_assignment_id)

AND business_group_id=p_business_group_id;



SELECT date_start INTO v_rhire_date

FROM per_periods_of_service

WHERE person_id = (SELECT DISTINCT person_id

FROM per_assignments_x

WHERE assignment_id = p_assignment_id)

AND business_group_id=p_business_group_id;

exception

when others then null;

end;

IF v_rhire_date =v_ohire_date THEN

v_flag:='N';

ELSIF v_rhire_date > v_ohire_date THEN

v_flag:='Y';

END IF;



RETURN (v_flag);

EXCEPTION

WHEN OTHERS THEN

-- v_error_code := SQLCODE;

-- v_error_desc := SQLERRM;

--dbms_output.put_line(v_error_code','v_error_desc);

RETURN 'N';

END get_rehire_flag_fnc;

END rn_rehire_flag_pkg;

/

How to register shell script as concurrent program?

Below is the step by step process of registering shell script as a host program or concurrent program in Oracle Applications.

1. Create a shell script ( say xyz) and move it to the appropriate BIN directory in Oracle Application.
2. The parameters in the shell scripts should start with $5 , $6 Onwards.
3. Rename the shell script to *.prog ( xyz.prog).
4. Change Permissions to 755 for the *.prog file.
5. Create Link to the shell Script ( ln -s $FND_TOP/bin/fndcpesr/xyz.prog) to create xyz file.
6. Now Register this executable in the application as a host executable (Using System Administrator Responsibility).

While registering the shell script as a concurrent program make sure that the first parameter in the concurrent program is passed as the Fifth parameter in the shell scripts because the first four parameters are allocated to userid,request_id,resp_id,resp_appl_id.

Apps Interview Questions

A flexfield is a field made up of sub-fields, or segments. A flexfield appears on your form as a pop-up window that contains a prompt for each segment. Each segment has a name and a set of valid values. There are two types of flexfields: key flexfields and descriptive flexfields.

BASIC FLEXFIELD CONCEPTS

Segment - A segment is a single sub-field within a flexfield. You define the appearance and meaning of individual segments when customizing a flexfield. A segment is represented in your database as a single table column.

For a key flexfield, a segment usually describes a particular characteristic of the entity identified by the flexfield. For example, you can have a key flexfield that stores part numbers. The key flexfield can contain the part number PAD-YEL-NR-8 1/2x14, which represents a yellow, narrow ruled, 8 1/2" x 14" note pad. Each section in the part number, separated by a hyphen, describes a characteristic of the part. The first segment describes the object, a note pad, the second segment describes the color of the object, yellow, and so on.

Note that we also refer to the fields in a descriptive flexfield pop-up window as segments even though they do not necessarily make up meaningful codes like the segments in key flexfields. However, they do often describe a particular characteristic of the entity identified elsewhere on the form you are using.

Values, Validation and Value Sets - Your end user enters a segment value into a segment while using an application. Generally, the flexfield validates each segment against a set of valid values (a "value set") that are usually predefined. To "validate a segment" means that the flexfield compares the value a user enters in the segment against the values in the value set for that segment.

You can set up your flexfield so that it automatically validates segment values your end user enters against a table of valid values. If your end user enters an invalid segment value, a list of valid values appears automatically so that the user can choose a valid value. You can think of a value set as a "container" for your values. You choose what types of values can fit into your value set: their length, format, and so on.

A segment is usually validated, and usually each segment in a given flexfield uses a different value set. You can assign a single value set to more than one segment, and you can even share value sets among different flexfields. For most value sets, when you enter values into a flexfield segment, you can enter only values that already exist in the value set assigned to the segment.

Structure - A flexfield structure is a specific configuration of segments. If you add or remove segments, or rearrange the order of segments in a flexfield, you get a different structure.

You can define multiple segment structures for the same flexfield (if that flexfield has been built to support more than one structure). Your flexfield can display different prompts and fields for different end users based on a data condition in your form or application data. Both key and descriptive flexfields may allow more than one structure.

In some applications, different users may need a different arrangement of the segments in a flexfield (key or descriptive). Or, you might want different segments in a flexfield depending on, for example, the value of another form or database field.

Your Oracle General Ledger application, for example, provides different Accounting Flexfield (Chart of Accounts) structures for users of different sets of books. The Oracle General Ledger application determines which flexfield structure to use based on the value of the GL Set of Books Name user profile option.

Key Flexfields - Most organizations use "codes" made up of meaningful segments (intelligent keys) to identify general ledger accounts, part numbers, and other business entities. Each segment of the code can represent a characteristic of the entity. For example, your organization might use the part number PAD-NR-YEL-8 1/2x14" to represent a notepad that is narrow-ruled, yellow, and 8 1/2" by 14". Another organization may identify the same notepad with the part number "PD-8x14-Y-NR". Both of these part numbers are codes whose segments describe a characteristic of the part. Although these codes represent the same part, they each have a different segment structure that is meaningful only to the organization using those codes.

The Oracle Applications store these "codes" in key flexfields. Key flexfields are flexible enough to let any organization use the code scheme they want, without programming.

When your organization initially installs Oracle Applications, you and your organization's implementation team customize the key flexfields to incorporate code segments that are meaningful to your business. You decide what each segment means, what values each segment can have, and what the segment values mean. Your organization can define rules to specify which segment values can be combined to make a valid complete code (also called a combination). You can also define relationships among the segments. The result is that you and your organization can use the codes you want rather than changing your codes to meet Oracle Applications' requirements.

Registration - Register a key flexfield after defining the flexfield combinations table in the database, and after registering your table with the Tables form.

Attention: Do not modify the registration of any key flexfield supplied with Oracle Applications. Doing so can cause serious application errors. To enable an Oracle Applications key flexfield, define and freeze it using the Key Flexfield Segments window.

Attention: Do not attempt to make a copy of an Oracle Applications key flexfield (using the same table, same title, or same flexfield code), since the duplicates may cause errors in forms that call these flexfields.

If you are using segment qualifiers with your flexfield, you should define the QuickCode values for your segment types using the Lookups window. You name your flexfield and associate it with an application and a database table. You also specify which table column you want to use as a unique ID column and which table column you want to use as a structure column.

Find below the Interview questions related to oracle apps technical interview. The Questions are primarily on Sysadmin and AOL (Application Object Library) Responsibilities. The questions are in MCQ style. The answers are written at the end of each question.
1. Which of the following is not associated with a responsibility?
A. Menu
B. Request group
C. Data group
D. Password expiration
Ans:D

2. What do you need to do to allow a different user to see your output file?
A. The user must log on as a system administrator.
B. The user must have the View My Requests form in system administrator mode.
C. The user must be logged on with the same responsibility as the user that generated the output file, and the profile option Concurrent: Report Access Level value must be set to Responsibility.
D. The user must be logged on as the same responsibility as the one that generated the output file and profile option Concurrent: Report Access Level value is User.
Ans:C

3. When will a request group become a request security group?
A. When the request group is assigned to a responsibility
B. When the request group is specified for the Submit Request form
C. When the request group is defined with request sets
D. When the request group is defined with stage functions
Ans:A

4. How can you customize the SRS process?
A. Change the form title
B. Restrict it by request group
C Eliminate the Submit Another Request dialog window
D. All of the above
Ans:D

5. Which is not a component of a request set?
A. Stage
B. Group
C. Request
D. Parameter Ans:B

6. Which of the following operations cannot be performed with the internal concurrent manager?
A. Restart
B. Verify
C. Deactivate
D. Terminate
Ans:A

7. Which one of the following will be the correct outcome with two include rules in a combined rule?
A. Program will be included if the program is in one of the include rules
B. Program will be included if the program is in both of the include rules
C. Program will be included if the program is run by one of the two Oracle IDs in the include rules
D. None of the above
Ans:B

8. When will the number of actual processes be less than the number of target processes?
A. When the concurrent manager is down
B. When the concurrent manager is coming up
C. When there are not enough requests
D. All of the above
Ans:D

9. Which of the following is not a component of parallel concurrent processing?
A. Internal monitor
B. Transaction manager
C. Primary node
D. Secondary node
Ans:B

10. Which of the following is the overriding level for profile option values?
A. Site
B. Application
C. Responsibility
D. User
Ans:D

11. Which of the following is the correct sequence for setting up database change audits? A. Audit installations, audit tables, audit groups, audit trail update tables request
B. Audit installation, audit groups, audit tables, audit trail update tables request
C. Audit trail update tables request, audit installation, audit groups, audit tables
D. Audit groups, audit tables, audit trail update tables request, Audit Installation
Ans:B

12. Which of the following is not a document sequence type?
A. Category
B. Automatic
C. Gapless
D. Manual
Ans:A

13. Which of the following determines the subdirectory for an executable?
A. Application name
B. Execution method
C. Subroutine
D. Execution filename
Ans:B

14. For what is the Token field used?
A. Default value
B. Profile option value
C. Oracle Reports parameter
D. Value set
Ans:C

15. How are output files associated with various printer components?
A. Through the printer type of the selected printer and the selected print style
B. Through the selected print style and the selected printer driver
C. Through the selected printer type and the selected print style
D. Through the printer type of the selected printer and the selected
Ans:A

UTL_FILES:

Qns: What is UTL_FILES?
Qns: What are Exceptions in UTL_FILES
Qns : What do you Mean by File Handler in UTL Files?
Qns : What is the syntax of UTL_FILES?
Qns: Where you Defined Path of UTL_FILE?

INTERFACE DESIGN:

Qns: What is Difference between Inbound Interface and Outbound Interface?
Qns: What is the methodology of Design an Interface?
Qns: What are Validation, Interface table and Concurrent Program for GL_INTERFACE?
Qns: How will you Migrate Vendors?
Qns: How will you Migrate Vendor Site and Contacts?
Qns: How will Load Open Invoices?
Qns : How will You Validate Source During open Invoice conversion?
Qns: How will you Load ON Hand Qty.
Qns: What do you Mean Auto lock box?
Qns: Auto invoice Interface
Qns: How will you Load master Items?
Qns: How will You Migrate Customer Data.?
Qns : Whats is the Difference between Interface and Conversions
Qns : How will you Migrate Reports?

AOL:

Qns: What is Difference between Data Group and Request Groups?
Qns: What is Difference between Request Group and Request Set?
Qns : How will you Register Custom table in Apps?
Qns : What do you Mean by Profile options?
Qns : What do you Mean by Value Sets and Validation Type?
Qns: How will you Make Table type Dependent Value Sets?
Qns : What do you Flex fields?
Qns : What is difference between $FLEX$ and $PROFILES$.
Qns : What is Difference between Key Flex fields and Descriptive Flex field?
Qns : What are WHO Columns?


GL:
Qns : Cycle of GL modules?
Qns : What do you mean by Dynamic Insertions?
Qns : In which Condition You Require More Than One Set Of Books?
Qns : What do you Mean by Flexfield Qualiier?
Qns : After Posting data goes to Which tables?


MULTI ORG:

Qns : We are able to see the data from front end but querying from backend data is not Coming?
Qns: Whats is Hierarchy of MULTI ORG?
Qns : What is difference between ORG_ID and ORGANIZATION_ID
Qns : What is the Difference between AP_INVOICES_ALL and AP_INVOICES
Qns : What is the Importance of MULTI_ORG?
Qns : How will you Design MULTI_ORG Reports?
Qns : In Which Table MULTI_ORG_FLAG Stored:


FORMS:

Qns: How will you Register The Forms?
Qns : How will you generate fmx on UNIX?

ALERTS:

Qns: What is Alerts? What is the business Purpose of Alerts?
Qns : What are Types of Alerts?
Qns : What are the Action Level in Alerts?
Qns : What are Action types in ALERTS?
Qns : How will you Schedule the Alerts?
Qns : How will you Run Periodic Alerts?
Qns : What do you Output Variable and Input Variables in Alerts?
Qns : How will you Design Alerts?
QUESTIONS
---------
1) How many Key Flexfields are there in the HRMS Suite and what are they?
2) Which Key Flexfields are required for setup prior to configuration and use
of the application?
3) Which Descriptive Flexfields are required for setup prior to configuration
and use of the application?
4) What are value sets and are these required?
5) Can you control which values are entered on any segment?
6) Is there anything you have to do after you have completed setup and
configuration of all your flexfields?
7) Should you check 'Allow Dynamic Inserts' on all HRMS flexfields?
8) After you have configured the structure, segments, value sets, etc, what
should you do next to validate the flexfield structure?
9) Entering Assignment form gives error:
APP-FND-00668: The data that defines the flexfield on this field may be inconsistent.
Action: FDFRKS could not find the structure definition for the flexfield specified by
Application = &APPL, Code = GRP and Structure number = 101 (APPID=801)
10) How do you know which descriptive flexfields are available on any form?
11) Can the same flexfield structure have a different set of segments?
12) Which table holds the data for each Key Flexfield structure?
13) Why aren't any of the key or descriptive flexfield windows opening when you click on them?
14) Is there a profile option or functionality to temporarily turn off flexfield validation?
15) You have defined your Job Key Flexfield structure and segments. When you navigate to Assignment form, the Job field has no list of values.


ANSWERS
-------

1) There are 6 Key Flexfields in HRMS application. They are:

A. Job
B. Position
C. Grade
D. People Group
E. Cost Allocation
F. Personal Analysis

2) All Key Flexfields are required for setup.

3) No Descriptive Flexfields are required for setup.

4) Value Sets are a way to define a set of values which can be used to validate
against segments of your flexfields. Value Sets can be shared by different
segments of the same flexfield, or by segments of any other flexfield. You do
not need to use value sets for validation. If no value sets are used, then
users can enter any alphanumeric value up to 150 characters.

5) Yes. You can define cross validation rules in your value sets to control
which combinations of values that can be selected or entered.

6) Yes. After you have completed the definition of a key flexfield, you need
to run the Create Key Flexfield Database Items process concurrent process to
generate Database Items for the individual segments of the Flexfield. This
applies to your Job, Position, Grade and People Group Key Flexfields only. As
with any concurrent process, this is run under the navigation of Process and
Reports > Submit Processes and Reports.

7) Yes. If this is not checked, you will not be able to enter new records in
the respective flexfield windows.

8) You should freeze and compile the flexfield. Check your compilation request
under View > My Requests to ensure the compilation completed without error.

9) This error occurs because at least one segment hasn?t been defined for the
People Group flexfield. You will need to define at least one segment. If you do
not need this 'dummy' segment, you can define the segment as 'Enabled' but
leave 'Displayed' unchecked.

10) Enter into any HR form. From the menu, select Help > Diagnostics > Examine.
For the Block field, click the list of values (LOV) and select value
$DESCRIPTIVE_FLEXFIELD$. Now click on the LOV for the Field field. In this
LOV, you?ll see all descriptive flexfields available on the form. Upon clicking
any value here, this will populate the Value field with the full name of the
descriptive flexfield.

11) Yes. These are context-sensitve segments. These appear only when a
defined context exists. This can manually be where the user selects the
context. Or it can be automatic by referencing another segment and its value.

12) The following are the flexfield/table relationships:

Flexfield: Table:
Job PER_JOB_DEFINITIONS
Position PER_POSITION_DEFINITIONS
Grade PER_GRADE_DEFINITIONS
People Group PAY_PEOPLE_GROUPS
Cost Allocation PAY_COST_ALLOCATION_KEYFLEX
Personal Analysis PER_ANALYSIS_CRITERIA

13) Make sure the following profile options in System Administrator are set to
Yes at the Site level:

Flexfields:Open Descr Window
Flexfields:Open Key Window

14) No. There is no functionality to flag validation one way or another.

15) You need to create the Job flexfield data on the Job form.
Navigation: US HR Manager > Work Structures > Job > Description

After this has been defined, you should now see data from Job field list of
values on Assignment form. The same is applicable for Position and Grade on
their respective forms. Out of all flexfields on Assignment form, the People
Group flexfield you can create your records without having to go to a separate
form.

Below is a list of 25 Important questions that is asked in almost every financials technical interview question. I have also included some of the functional questions that are asked.Answers are included.

Qns: What is Flex field? What are different types of Flex field?
Ans: Flex field is used to capture information of your Organisations.

Qns: Difference between KFF and DFF.
KFF
Unique identifier
Stored in segment Column

DFF
Is used to capture additional information
Stored in attribute Column

Qns: How many KFF are in GL. AP , AR.
Ans:
Module KFF
GL Accounting FF
AP No KFF
AR Sales tax Location FF
Territory Flexfield.

Qns: What is symbol of DFF in the Forms?
Ans: Square Bracket [ ].

Qns: What is structure of KFF in the Accounting Flexfields.
Ans: Company
Cost center
Account
Product
Future use.

Qns: How many segments are in AFF.
Ans: max 30 segments and min two.

Qns: What are flexfield Qualifiers.
Ans: Flexfield Qualifiers is used to identify the segments. Various types of flexfield qualifiers are listed below:
a) Balancing Segment Qualifier.
b) Cost Center segment Qualifier.
c) Natural Account Segment Qualifier.
d) Intercompany Segment Qualifier.

Qns: What is Dynamic Insertions?
Ans: u can create Code Combinations at run time.

Qns: In which table Code Cominations id is stored.
Ans: GL_CODE_COMBINATIONS.

Qns: In which table flex values are stored.
Ans: 1. fnd_ flex_Values
2. fnd_ flex_Values_tl

Qns: What is set of Books and in which table set of book is stored.
Ans : Set of Books is a Financial Reporting entity which Consist of three C.
a) Chart Of Accounts
b) Currency
c) Calendar.
Set of Books is stored in GL_SETS_OF_BOOKS


Qns: In which table Currency and Period Type Name are stored.
Currency - FND_CURRENCIES
Period - GL_PERIOD_STATUSES


Qns: In which table Segment Values are stored and concatenated values are stored.
Ans: 1. GL_CODE_COMBINATIONS
2. GL_CODE_COMBINATIONS_KFV.

Qns: What are different types of Currency.
Ans: Functional Currency
Foreign currency.

Qns: What are different types of Calendars .
Ans: Different types of Calendars are listed below
a) Fiscal
b) Accounting

Qns: How will you attach set of Books to the Responsibility?
Ans: through Profile. GL SETS OF Books Name.

Qns: What is Profile and what are different types of Profiles.
Ans: Profile: Profile is the changeable option that affects the way your application runs. There are two types of profile.
1. System defined
2. User defined

Qns: What are different Profiles Level available in oracle apps.
Ans: Below are the Profiles Level available in oracle apps
1. Site(Lowest level)
2. Application
3. Responsibility
4. User.

Qns: Write Name of some Profile options.
Ans:
1. GL Sets of Books Name
2. GL sets of Books id
3. MO:Operating unit (multi org).
4. HR:User type.

Qns: What is cycle of GL.?
Ans: In simple and layman words-
1. Open the period
2. Create Journal Enteries
3. Post the Journals.

Qns: In Which tables Journal entries created.
Ans: Important tables are-
1. Batch: GL_JE_BATCHES
2. Header: GL_JE_HEADERS
3. Lines : GL_JE_LINES

Qns: After Posting data goes in which tables.
Ans: GL_BALANCES.( Column Period_net_cr, period_net_dr).

Qns: What are Important tables in GL.
Ans:
1. GL_JE_BATCHES
2. GL_JE_HEADERS
3. GL_JE_LINES
4. GL_BALANCES
5. GL_SETS_OF_BOOKS
6. GL_CODE_COMBINATIONS
7. GL_PERIOD_STATUES
8. GL_INTERFACE

Qns: In which table Supplier information’s is stored.
Ans: Supplier information can be found in following tables
1. PO_VENDORS
2. PO_VENDOR_SITES_ALL
3. PO_VENDOR_CONTACTS

Qns: What is difference org_id and Organization_id.
Ans: Org_id is for operating unit and organization_id is for inventory organization.

Please share your views about this Article.For more articles visit About Oracle Apps


Qns1 : What are the important tables in AP (not interface tables):
1. AP_INVOICES_ALL
2. AP_CHECKS_ALL
3. AP_INVOICE_DISRIBUTIONS_ALL
4. AP_BATCES_ALL
5. AP_PAYMENT_DISRIBUTIONS_ALL

Qns2 : What do you mean by _ALL in tables.
Ans : It means data is separated by Operating unit.

Qns3 : What are the important tables in AR (not interface tables):
1. AR_CASH_RECEIPT_ALL
2. RA_CUSTOMERS
3. RA_ADDRESSES_ALL
4. RA_CONTACTS
5. RA_PHONES

Qns4 : What are the important tables in GL (not interface tables):
1. GL_BALANCES
2. GL_JE_LINES
3. GL_JE_HEADERS
4. GL_JE_BATCHES
5. GL_PERIODS
6. GL_SETS_OF_BOOKS

Qns5 : What are the important tables in FA (not interface tables):
1.FA_CATEGORIES
2.FA_MASS_ADDITIONS.

Qns 6: Why SQL Loader is used.
Ans : To transfer data from text file to Temporary table or interface table

Qns 7: How do you design an Interface in Oracle Applications .Explain the various steps in detail.

Step-01
Create a staging table and populate the records received from customer using SQL Loader.

COMMAND LINE: sqlldr userid/user@pass control=CHP_ITEMS.ctl data=CHP_ITEMS.csv

LOAD DATA
INFILE 'C:\CHP_ITEMS.csv'
INSERT
INTO TABLE CHP_MTL_SYSTEM_ITEMS
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(ITEM_NUMBER
, DESCRIPTION
, TEMPLATE_NAME
, PRIMARY_UNIT_OF_MEASURE
, LIST_PRICE_PER_UNIT
)

Step-02
After that I wrote cursor script to load from staging table to interface table.

Step-03
Assuming that I am designing Customer interface. After that I run standard Customer Interface Program to load the data into transaction table. Customer Interface Program validate the data and transfer into Transaction table in apps and customization to standard programs is not recommended or supported by Oracle.

Qns 8: Tell me any one Validation used in customer interface:

INSERT_UPDATE_FLAG
Enter a value to indicate whether you are inserting a new record or updating an existing record.’I’ for insert, ’U’ for update

Qns9 : What are various interface tables in AR.

1. RA_CUSTOMERS_INTERFACE
2. RA_CONTACT_PHONES_INTERFACE
3. RA_CUST_PAY_METHOD_INTERFACE
4. RA_CUSTOMER_BANKS_INTERFACE
5. RA_CUSTOMER_PROFILES_INTERFACE

Qns10 : What are various base table Updated by Customer Interface:
AR_CUSTOMER_PROFILES
AR_CUSTOMER_PROFILE_AMOUNTS
RA_ADDRESSES
RA_CONTACTS
RA_CUSTOMERS
RA_CUSTOMER_RELATIONSHIPS
RA_CUST_RECEIPT_METHODS
RA_PHONES
RA_SITE_USES
AP_BANK_ACCOUNT_USES
AP_BANK_ACCOUNTS
AP_BANK_BRANCHES
Additional Notes-
To import a customer with multiple telephone numbers you need to enter multiple records into
RA_CONTACT_PHONES_INTERFACE with identical ORIG_SYSTEM_CUSTOMER_REF values, but different ORIG_SYSTEM_TELEPHONE_REF values.

To import an address with multiple business purposes you need to enter multiple records into RA_CUSTOMERS_INTERFACE with identical ORIG_SYSTEM_CUSTOMER_REF and
ORIG_SYSTEM_ADDRESS_REF values, but different SITE_USE_CODES values.

To import an address with multiple contacts you need to enter multiple records into
RA_CONTACT_PHONES_INTERFACE with identical ORIG_SYSTEM_CUSTOMER_REF and
ORIG_SYSTEM_ADDRESS_REF values, but different ORIG_SYSTEM_CONTACT_REF values.

Please share your views about this Article.For more articles visit About Oracle Apps
Oracle Receivables is not an easy module to set-up. To crack the interview is bit more tedious.Below are some of the Functional questions pertaining to Oracle AR module. Just go through them and brush up your skills.

1. How many Address’s can have one Customer?
Primary Address, Bill – To – Address, Ship – To – Address

2. Customer Number Always Automatic / Manual?
Any thing Either Manual or Automatic

3. What are the Mandatory Address’s you should create a Customer for Communicate him?
Remit – To – Address

4. Can U Merge the Customers? If How?
Using the Merging Customer Window

5. What is Accounting Rules?
It is For Generating Revenue Recognition Programs like Monthly, Quarterly

6. What is Invoicing Rules?
The Invoicing Rules Helps you when you receive data from Outside systems like Auto Invoices how the data should insert and It contains 2 rules Advance Invoice, Arrears Invoice.

7. Where should the customers send the payments of Invoices?
Remittance Banks or Vendor Places

8. What is the Transaction Type?
It describes us Whether you want transfer the Receivables data into General Ledger or not. And also when ever you create an invoice what are the accounts should be effected and also the sign of Transaction also.

9. What is a Transaction Source?
It is For Invoice or Invoice Batch numbers whether automatically or manually

10. How many Transactions we have?
Six, Credit Transactions: Invoice: Debit Memo: Charge back: Guarantee: Deposit

11. How can i reduce the Invoice amount?
Using with Credit Transactions

12. What are the Accounts to be use in Transaction Types (Few)?
Revenue, Receivables, Freight, Tax, Clearing, Unearned, Unbilled

13. How can i Assign a Deposit amount to an Invoice?
In the Invoice Window “Commitment” Region

14. What is the Guarantee?
It is agreement between both sides for goods or services in the future , specific range of periods

15. Give the Navigation for Credit Transactions?
Transactions/Credit Transactions

16. How many ways you can apply the Receipt Amount?
Application: Mass Apply

17. How will you know a Customer Balance Amount?
Using with the Customer Account Overview window

18. Can U Define Customer Agreements using with AR?
No, In the Oracle Order Entry Module

19. What are Aging Buckets?
It is for Outstanding Reports purpose the no of days in various ranges

20. How will U View the Outstanding Balance of a Customer?
Generating the Aging Buckets Report

COMMON QUESTIONS OF PL/SQL & CMM

1. What are the features of OOPS used in PL/SQL ?
Ans: Inheritance – Reusability
Abstract Datatype
Method Overloading

2. What are the built in packages available in PL/SQL ?
Ans: DBMS_OUTPUT, DBMS_SQL, DBMS_STANDARDS, DBMS_UTL, DBMS_LOCK, DBMS_LOB, DBMS_JAVA, UTL_FILE,

3. Diff. In Function and Procedures?
Ans: 1. Function must return a value where as procedure does not.
2. Function can be used in SQL statement where as Procedure does not.

4. What is the structure of the PL/SQL block?
Ans: <> (Optional)
Variable & Cursor Declaration
<>
Executable Statements
<> (Optional)
Exception handling
End;

5. Can we use label for anonymous PL/SQL block?
Ans: Yes, use it in “<>” structure

6. What are the exceptions in PL/SQL Block?
Ans: CURSOR_ALREADY_OPEN, DUP_VAL_ON_INDEX, INVALID_CURSOR, INVALID_NUMBER, NO_DATA_FOUND,
PROGRAM_ERROR, VALUE_ERROR, ZERO_DIVIDE

7. What are the types of Variable binding?
Ans: Two types of binding
1. Early Binding (at compile time)
2. Late Binding (Runtime)

8. What are the PL/SQL table and nested table ?
Ans:

9. What are the Scalar type of variables?
Ans: Number, Char, Varchar2, rowed, urowid, raw, long, longraw, blob, clob, bfile, Nchar, nvarchar2, date

10. What are the user defined data types ?
Ans: 1. Structured Types
• Object Types
2. Collection Types
• Varray
• Nested Tables
3. REFS (To object Types)

11. How can we use label in PL/SQL block?
Ans: We can declare a label in “<>” format & can pass control to it using “GOTO” statement.

12. How can we know that proc has passed a value ?
Ans: We can check it using “IN” or “OUT” or “INOUT” parameter.

13. What is the difference between REF Cursor & PL/SQL Table.
Ans: REF Cursor is like Pointer whereas PL/SQL Table is like ARRAY.
REF Cursor can pass to a procedure/function as a parameter directly whereas in PL/SQL table one record has to be passed each time.

14.

15.
=============================================================CMM ACTIVITIES FOR INTERVIEW============================================================= 1. Your contributation to CMM? In which activities u r involved? DS, FS, Defect Preventation, Test Cases, Replying IR 2. How many KPA's are there in each level?? & Describe them? Describe in detail any one KPA 3. Software Configuration Management (SCM) Activities? How to do it? PVCS & Version control activities 4. Diff b/w Level 3 - 4 & 5 5. Various groups like SEPG, DP, SQA, BPAD & their interaction in Project Development Life Cycle 6. What documents are referred in coding ?? DS, FS, CODING STANDARDS, CHECK LIST

KISHOR PATIL INTERVIEW
1. tell me some thing about u!2. how old r u?3. Y r u looking for change?4. Write a query to delete Duplicate rows. (Write using ROWID)
5. Where do u decalare Global Variable in Package? Ans: Package Specification
6. Can u create procedure or function without declaring it in Package specs? Ans: YES, It is called private procedure.
7. what is private function and public functions in package? Ans: If the function is declared in Package Specification then it is called Public Function. Public function can be called outside of Package. If the function is not declared in Package Specification then it is called Private Function. Private function can not be called outside of Package.
8. how do u call private functions in package?Ans: pack spcs p1... func f1(); -- Public function func f2(); -- Public function end;
pack body p1... func f1(){}; -- public func f2(){}; -- public func f3(){}; -- Private func f4(){}; -- Private end; to call private call it in public function and public fun can be called from outside.
9. how do u create tab in forms 4.5 where as in 4.5 there is no tab control provided i? Ans: Will create buttons & programatically make it like Tab Page
10. create a syquence, open a new session and execute first statement as select sequence.currval from dual; what will happene? Ans: It will give an error. First time we have to fire next val & then only we can use currval. 11. I have t1 table in scott .. and same i have scott1 schema with same name... i grant select on scott1.t1 to scott, now i create a synonym for scott1.t1, what happenes when it is created. will it give runtime error or error while creating synonym?Ans: This will give an error, Same name can not be used
12. How many types of Triggers... (24)
13. what do u mean by mutating table? what is work around for the same..?
14. tell me diff between .. 7.x, 8, 8i, 9i ( undo tablespace) Ans: 9i New Features-------------- 1. Rollback Segment is being replaced by Undo Tablespace. (We can use either) 2. Flashback Query - It is used to retrack wrongly committed transaction 3. Online Table Creation (e.g. Create table as select * from ....... will generate updated table) 4. List Partition - Table can be partitioned on a list 5. Buffer catche size is now dynamic (upto 4 different sizes can be specified for buffers)
15. tell me diff between .. forms 3, 4.5, 5, 6i
16. what is view? Ans: View is a virtual table (or logical container of data), which does not physically store data.
17. Why instade of trigger is created? Ans: To Insert/Update/Delete record from base table for any view operation
18. Are views updatable?Ans: Yes (Only if the view is based on one table, but it is NOT if more than one tables)
19. what u know about oracle arch? physical and logical arch!
20. types of tuning?Ans: Application Tuning, Database Tuning, Memory Tuning, O/S Tuning
=========================================================================RAMESH PADHIYAR INTERVIEW WITH MBT=========================================================================
1. what is difference is view & synonyms
2. Cursor/PLSQLtable/REF cursor [Def., Syntax, How to use all.]
3. Difference of procedure & packagesAns:
1. Various Master detail relationshipAns:
2.locking mode property of block(Immediate/Automatic/Delayed)

Automatic (default):- Identical to Immediate if the datasource is anOracle database. For other datasources, Form Builder determines theavailable locking facilities and behaves as much like Immediate aspossible.
Immediate:- Form Builder locks the corresponding row as soon as theend user presses a key to enter or edit the value in a text item.
Delayed:- Form Builder locks the row only while it posts thetransaction to the database, not while the end user is editing therecord. Form Builder prevents the commit action from processing ifvalues of the fields in the block have changed when the user causes acommit action.

3.trigger execution
form level
block level
item level
4.mouse triggers

When-Custom-Item-Event
When-Mouse-Click
When-Mouse-DoubleClick
When-Mouse-Down
When-Mouse-Enter
When-Mouse-Leave
When-Mouse-Move
When-Mouse-Up

5.What is visual property?
6.what is LOV?
ans: lov is List of values. It can be query based or static values.to restrict users with fixed set of value.
7.Diff. b'ween open_form,call_form,new_form?
Open form: It will allow user to navigate between called form andcalling form.
call_form: It will not allow user to navigate on calling form.
New form: It will exit the called form and control goes to new form.
8.


-- Current project/role & responsibility
-- CMM LEVELS AND IT'S IMPLEMENTATIONS




1. cURRENT PROJECT :FUNCTIONALITY
2. ORACLE ARCHITECTURE.
3. DBA - PERFORMANCE RELATED
4. PL/SQL.
5. SQL
6. FORMS


1. CURRENT PRJECT
- FUNCTIONALITY
- ROLE
- RESPONSIBILITY

2. ORACLE ARCHITECTURE
- PROCESSES & WORKING OF EACH PROCESS
- MEMORY STRUCTURE

3. DBA - PERFORMANCE RELATED
- HOW TO TUNE A SYSTEM?
INDEXING, EXPLAIN PLAN, APPLICATION TUNING, O/S TUNING
-

4. PL/SQL
- REF CURSOR (IN DETAIL WITH USAGE)
- DIFF B/W REF CURSOR & PL/SQL TABLE
- VIEWS (IN DETAIL)
- DIFF B/W VIEW & MATERIALIZED VIEW
- PSUEDO COLUMN (Def.)


5. SQL
- SCENARIO BASED (TABLE STRUCTURE GIVEN & QUERY ISDEVELOPED ON THAT BASIS)
- SUM/AVERAGE HOW TO COUNT??

6. FORMS
- MOUSE TRIGGERS & OTHER TRIGGERS WITH THEIR SEQUENCES
- HOW TO CREATE DYNAMIC LOV
RECORD GROUP CAN BE DYNAMICALLY CHANGED/CREATED BUT LOV CAN NOT BE
CREATED DYNAMICALLY
- WHICH TRIGGERS ARE RESTRICTED IN WEB BASED
- FORMS ARCHITECTURE

1. In the Price type quickpick in the Purchase Order Lines zone in enter
Purchase Order form, there is a value called COST PLUS FEE. What is the
COST PLUS FEE price type? How is Purchase Order treating this
price type?
Answer
------
The functionality for COST PLUS FEE price type is not in Oracle Purchasing at
this point of time. PO does not do anything with this price type.
It is an enhancement request that is being looked at for future releases.
2. What is 2-way, 3-way, 4-way matching? How is this set-up?
Answer
------
2-way matching verifies that Purchase order and invoice information match within your tolerances as follows:
Quantity billed <= Quantity Ordered Invoice price <= Purchase order price (<= sign is used because of tolerances) 3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined: Quantity billed <= Quantity received 4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined: Quantity billed <= Quantity accepted. (Acceptance is done at the time of Inspecting goods). Whether a PO shipment has 2-way, 3-way or 4-way matching can be setup in the Shipment Details zone of the Enter PO form (character) Receipt required Inspection required Matching Yes Yes 4-way Yes No 3-way No No 2-way In GUI, Shipments block, alternative region, click on More...this brings up a region with 'Invoice matching' column where you can choose 2-way, 3-way or 4- way match. You can find more detailed information about matching in the Oracle Payables Reference Manual (Volume 3) Topical Essay on Integrating your Payables and Purchasing Information. 3. How do you reflect discount from a supplier on a Purchase orders? Answer ------ The legal document is the Purchase order not the quote and therefore the purchase order should reflect agreed upon price. Secondly if the discount comes after the PO then AP should handle it when invoicing. If you are using a blanket Purchase order then you can use price breaks. 4. How do you change the created date on a Purchase Order? Answer ------ The 'Created' date on the Purchase order is not an updateable field, due to it being a legal document once approved. 5. What is the sorting order for requisition lines in autocreation of a Purchase Order? Answer ------ Requisition lines first meet search criteria determined by "find" screen, and then are grouped together by item and item category, then by need-by date. After you choose an action, enter any search criteria to narrow the selection of requisition lines. Oracle Purchasing chooses requisition lines from the requisition pool that meet your search criteria. All requisition lines for the same item and item category are grouped together, but they are not necessarily in order alphabetically. Within each grouping of item and item category, requisition lines are ordered by need-by date. 6. What does the error 'APP-50022: Oracle Human Resources could not retrieve a value for User Type profile option' mean? Answer ------ The profile HR: User Type needs to be set. Set the profile option 'HR: User Type' at the Responsibility level. 7. In the Enter Receipts (RCVRCERC) form and using different responsibilities produces different values in the action required field. The Purchasing Responsibility shows Direct and Standard while the Inventory Responsibility shows just Direct. Why are the action options for creating receipts different in Inventory compared Purchasing? Answer ------ Set the Profile option RCV: Allow routing override to 'YES' at the application level. 8. How do you purge Cancelled Requisitions? Answer ------ To purge any Purchase orders or Requisitions: - assign the Payables Purge GUI responsibility to the user - have the user choose that responsibility - then choose Purge from the menu - under the Category field do a List of Values and pick Simple Requisitions for cancelled requisitions The choices are listed. 9. On the requisition form in the source details zone, there is a field labeled supplier item. Where does this information on the quickpick come from and how can this information default in? Answer ------ The supplier item field on the requisition can be populated by using either the supplier item catalog or ASL. Take an item from the supplier item catalog that has an associated supplier item, add it to the order pad and then add it to the requisition. After doing this go to the source details zone. The supplier item is defaulted in for that item you choose from the catalog. Navigation: --> Purchasing --> Supplier Item catalog
10. You are entering a requisition and are unable to find the header or line
note fields. Was this replaced in GUI by attachments?
Answer
------
The attachments have replaced notes. The attachments are better because at the
header level, the requisition notes were not coming over to the Purchase Order
whereas the attachment does come over to the Purchase Order.
11. When you select the Notifications form to review approvals and click on
approve button for a Requisition waiting for approval, the 'Reject' box is
greyed out. How does the approver reject a requisition?
Answer
------
You cannot reject Requisitions submitted to yourself. You can only reject those
from others.
12. What are standard Quotations used for?
Answer
------
Standard quote is one that you can tie back to a PO. For example, navigate to
RFQ -> Auto create -> enter a PO and reference it back.
13. When do you see the status of a Purchase order as Archived?
Answer
------
You must approve or print a specific purchase order before you can see it in
the history information.
14. Where are standard notes in GUI?
Answer
------
Go to Setup --> Attachments -->Create attachment. Go to Purchase Order and
attach the attachment to the Purchase Order. In GUI, notes are replaced by
attachments.
15. In Oracle Purchasing, where is the automatic numbering for Purchase Order
defined and maintained?
Answer
------
The navigation path for GUI is:
Setup --> Organizations --> Purchasing Options, Numbering alternate region
The navigation path for Character is:
\ Navigate Setup Purchasing Options Purchasing, Numbering options
16. There is a field for the organization code. What is this code?
Answer
------
This code is a short abbreviation for the organization.
17. Can the original Purchase Order can be viewed in any way without resorting
to SQL, for a revised Purchase Order?
Answer
------
The original version of a revised PO cannot be viewed from the PO form or PO
summary form. Information on the original PO can be obtained from the
PO_HEADERS_ARCHIVE and PO_LINES_ARCHIVE tables using the PO_HEADER_ID column as
a common reference using SQL only.
2.
3. 1. How do you create a Chargeback Invoice ?
A. Using Receipts window

2. How do you adjust the amount of advance already received against an Invoice ?
A. Create an Invoice for Deposit and receive an amount against this deposit Invoice. Then Create the regular invoice and in the column of Commitments enter the number of Deposit Invoice. The Regular Invoice gets matched with the Deposit Invoice for the amount of Regular invoice or deposit invoice which ever is lower

3. How do you write off small amounts while accounting the receipt against an invoice?
A. Using Receipts window. You have the button for write offs.

4. How do you Account for bank charges deducted from amount received against an invoice?
A. Using Quick cash window also you can enter receipts. Here you have the option of accounting the bank charges deducted on receipt. However, this has to be enabled by putting the Value in profile option AR: Create Bank Charges = YES.

5. How do you create a credit note against an invoice ?
A. You have separate window to create such credit note. Navigation Transactions => Credit Transactions.

6. How do you adjust a regular Invoice with a Credit Note ? Enter the amount 0 in receipt window and in invoice matching window select the invoice as well as the credit note. This will knock off the invoice against the credit note.

7. What are different types of Receipt Reversals ? What is the difference between them ? What are the accounting entries ?
A. Standard Reversal and Debit Note Reversal. Standard Reversal reopens the invoice matched in that receipt. You can match a new receipt against this invoice.
Debit Note Reversal does not reopens the earlier matched invoice but it creates a new debit note which can be matched with another receipt.

8. When are the following accounts are used
Unbilled Receivable
Unearned Revenue
A. When you use Invoicing Rule, the receivables are accounted on different dates as defined in the rule. Till such time receivables are accounted, the amount is debited to Unbilled Receivables.
If you use Accounting Rule, the revenue is accounted on different dates. Till such time revenue is accounted, the amount is credited to Unearned Revenue..


9. What is the difference between Unidentified Receipt, Unapplied Receipts and On Account Receipts ? What are the Accounting Entries for each of this ?
A. Unidentified Receipts: The Customer is yet to be identified and so receipt is not matched.
Unapplied Receipts: The Customer is identified and entered but the amount is not matched with any of his invoice.
On Account Receipts: The Customer is entered and instead of matching the amount to any of his invoice it is matched with the On Account option. This option is available as first item in the pick list of invoices.

10. What is the difference between earned discounts and Unearned discounts ? How do you account the Unearned discount? What are the accounting entries involved ?
A. The cash discounts are mentioned in the payment terms. Considering the due date and the receipt date the discount is automatically calculated while entering the receipt. If however you want to increase the amount of invoice you can manually increase it. The amount automatically calculated as per terms is the Earned Discount. The manually added amount is the Unearned Discount. This however, depends upon the option given in System Options in Setup.


11. What are the Key Flexfields in AR.

A. Sales Tax Location Flexfield
Territory Flexfield

AR Setup Related

12. What is difference between transaction type and transaction source ?

A. Transaction type can either be Invoice, Credit Note, Debit Note, Deposit. Etc. This also decides whether to account in GL, Whether to consider in receivables, the accounts to be debited, credited, the tax calculation options, and some other options which are defauled.
Transaction Source decides whether the source of entry is manual or automatic. It also mentions whether transaction and batch numbering is manual or automatic.
If automatic the last entered number is to be mentioned to start the automatic numbering. IF the source is automatic, that means the transactions are to be uploaded through interface either from OE or legacy system. Then some other options are to be set.

13. What is AutoAccounting ?

A. This is the account generator in Accounts receivables. This decides accounts for
Receivables
Revenue
AutoInvoice Clearing
Freight
Tax
Unbilled Receivable
Unearned Revenue

From where the accounts are defaulted ?
Sales reps
Transaction Lines
Transaction Types
Taxes
Or you can have Constant values

14. What is AutoCashRule Set ?
This decides the sequence of the invoice matching rules for unmatched receipts entered though quich cash or AutoLockBox(interface). The rules are already defined in the system. Like “Match Payment with Invoice”, “Apply to the Oldest Invoice First”, “Clear the Account”, etc.

15. Where do you attach the Set Of Books ? Can you attach more than one set of books ? if you have more then one set of books then how Receivables is configured ?
You attach the set of books in System Options in Setup. Only one set of books can be attached. If you have more then one set of books then AR is to be set in multi org environment. By specifying the “MO Operating Unit” and the “GL Set of Books Name”.

16. How do you define document numbering for receipts ? can you have different sequences for each Payment Method.
In system administration. You can have different sequences for different payment methods.
4.
5. ) How much do u rate urself in PL/SQL?

3) What is autonoumous transaction?

4) What are Indexes and types of Indexes?
Ans Simple Index, Binary Index and Function Index.
5) What is difference between Primary Key and Unique Key?
Ans Unique key can be null but primary key cannot be null
6) What is Data Dictionary?
Ans Its the meta data about the objects of the database, its the data about data.
7) How do u eleminate duplicate records from the table? (Concept)
Ans Delete from tablename a where rowid>(select min (rowid) from tablename b where a.colname=b.colname);
8)What are the attributes of a cursor?
Ans %isopen, %found, %notfound, %count.
9) What is difference between Delete and Truncate?
Ans Delete keeps the rollback info but truncate doesn't. So rollback the delete statement but truncate cannot be rollbacked.
10) What are the different types of Exceptions?
Ans User Defined exceptions and predefined exceptions
User Defined exceptions are defined explecitly by the user in the declare section and called in the begin section
and the code is written exception section.
Eg]-
Declare
...
myexception exception;
Begin
...
raise myexception;
Exception
when myexception then
PL/SQL code..
End

Predefined exceptions are oracle defined exceptions like no data found, too many rows, invalid cursor, value_error, dup_val on index
11) What is a transaction? How does it end?
Transaction is a series of SQL statements which can be succeeded and failed as a unit.
It ends with commit or rollback.
12) What is DBMS packages? How many of them have u used?
DBMS packages are used
13) What are post functions?

14) What are the different tyes of canvases?
Ans Content, Tab, Stacked, Horizontal toolbar, Vertical toolbar
15) Difference between pre querry and post querry?
Ans pre querry fires before the querry is executed and post querry is fired after the querry is executed
16) What are the different SRW packages?
Ans SRW.Reference, SRW.Userexit, SRW.Runreport, SRW.SetAttr, SRW.Getpagenum,SRW.Message.....
17) How do u define SRW.message?
Ans SRW.message(msg no, msg string);
18) What is an anchor? What are the different types of anchor?
Ans Anchor is an object which is used to attach two different objects like frames to keep its relative position with respect to each other. Types of anchor are horizontal and vertical.
19) What are the types of Master Detail relation? What is isolated MD relation?
Ans Isolated, Non Isolated and Cascade.
Isolated- Master record can be deleted without deleting the detail record.
Non Isolated- Master record cannot be deleted without deleting the detail record.
Cascade- When the master record is deleted the detail record is deleted automatically.
20) What is object group?
Ans Object group is a container for a grooup of objects like canavas, block and window they can be grouped to create an object group
21) What are various block coordination properties?
Ans Immediate, Deffered with autoquerry, Deffered with No-autoquerry
Immediate- When u querry the master record the detail will be automatically querried
Deffered with autoquerry- Unless and Untill u navigate to the detail block it will not be querried.
Deffered with No-autoquerry- U have to explicitlly querry both master and detail block.
22) How to open form from form?
Ans It can be opened from
Call- Existing form is kept in background.
Open- It opens a new form and u can navigate between them.
New- It replaces the orginal form.
23) What are different item level triggers?
Ans pre-text-item, when-new-item-instance, key-next-item, when-validate-item, post-text-item.
24) What is flex mode?
Ans When the flex mode is on the objects can be moved outside its parent frames( across frames)
25) What is the difference between key-next-item and post-item?

26)How do u used DDL in forms?
Ans Forms_DDL, Exec_SQL
Forms_DDL:- Only database the database u are connected to
Exec_SQL:- Another database, and u can also call non-oracle procedure.
27)What is are record groups?
Ans Its a internal oracle forms datastructure, which can be used in LOV's to populate the querries at runtime.
Types:-
Querry record group:- Querried from the database
Non querry record group:- its created and changed programmatically
Static querry record group:- Harcoded values
28) What is property class and visual attributes?Whats the difference?
Ans Property class is a named object that consist of a list of properties and their setting, it cannnot be changed programatically.
Visual Attributes are properties like fonts, colours, patterns for forms and menu object, it can be changed programatically
29) What are different report triggers and give the order of execution?
Before Parameter trigger
After Parameter trigger
Before report trigger
Between Pages trigger
After report trigger
30) Different types of reports?
Form like
Tabular
Group above
Group left
Maliling lablel
Matrix
Form letter
Matrix with group
31) What is matrix report?
Ans Its a cross tab report.
It contains four groups they are:-
Cross product
Column
Row
Cell value
32) How to call report form a form?
Run_product
33) Different types of parameters in report?
User Parameter and System parameter
System paramters:-
Destype
Desname
Desformat
Decimal
Copies
Currency
Background
Mode
Orientation
Printjob
Thousands
34) What is lexical parameter, bind parameter and user exit?
Ans
35) How do u define paramteres in Concurrent Request?
Ans
36) What are profiles? What is its order?
Ans Its a set of changable options which define a behavior of ur application
Its order in sense of Generic is
Site, Application,Resposibility and User

37) What are different value sets?
Ans Independent, Dependent, Pair, None, Translate Independent, Translate Dependent, Special, Table
38) What is custom.pll? What does ZOOM do?
Ans Its a library used in forms which can be used for customization purpose. It contains the procedure
Style, Event and Zoom
Zoom is used to call another form from a form.
39) What is DFF and KFF?