Oracle Interview Questions and Answers

Oracle Interview Questions and Answers
1. To see current user name
Sql> show user;
2. Change SQL prompt name
SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >
3. Switch to DOS prompt
SQL> host
4. How do I eliminate the duplicate rows ?
SQL> delete from table_name where rowid not in (select max(rowid) from table group by
duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from
table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
or
delete from t1 a where rowid not in (select max(rowid) from ti b where a.no=b.n0)
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith
5. How do I display row number with records?
To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
6. Display the records between two range
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
7. I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?
SQL> select nvl(to_char(comm.),'NA') from emp;
Page 11 of 259
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
8. Oracle cursor :
Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
9. Explicit Cursor attributes
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
10. Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing
SQL statements.
: 2. All are Boolean attributes.
11. Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP
B WHERE a.sal<=b.sal);
or
select min(sal) from(select distinct sal from emp order by sal desc where rownum<=&n)
Enter value for n: 2
SAL
---------
3700
12. To view installed Oracle version information
SQL> select banner from v$version;
13. Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",(' Rs. ' (to_char(to_date(sal,'j'), 'Jsp')) ' only.'))"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
14. Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
Page 12 of 259
4
6
15. Which date function returns number value?
months_between
16. Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
17. What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
18. Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
19. What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
20. What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.
21. What is the maximum number of triggers, can apply to a single table?
12 triggers.

PL/SQL
Basics of PL/SQL
1. What is PL/SQL ?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language
constructs such as iteration, conditional branching.
2. What is the basic structure of PL/SQL ?
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
3. What are the components of a PL/SQL block ?
A set of related declarations and procedural statements is called block.
4. What are the components of a PL/SQL Block ?
Declarative part, Executable part and Execption part.
5. What are the datatypes a available in PL/SQL ?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.
6. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in
the cursor.
The advantages are :
I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
7. What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different
table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.
8. What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which are modelled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.
9. What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
10. Explain the two type of Cursors ?
There are two types of cursors, Implict Cursor and Explicit Cursor.
PL/SQL uses Implict Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
11. What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types,
CLOSE cursor name.
12. What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if no rows are fetched.
These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors.
13. What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from
active set into fields in the record and closes
when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;
14. What will happen after commit statement ?
Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
Page 14 of 259
C1 %notfound;-----
commit;
end loop;
end;
The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.
15. Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a
cursor.
Database Triggers
16. What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit
data modificateions, Log events transparently, Enforce complex business rules Derive column values
automatically, Implement complex security authorizations. Maintain replicate tables.
17. How many types of database triggers can be specified on a table ? What are they ?
Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the retruned boolean value.
18. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
19. What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available. For triggers related to DELETE only OLD.column_name values only available.
20. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Mutation of table occurs.
21. Write the order of precedence for validation of a column in a table ?
I. done using Database triggers.
ii. done using Integarity Constraints.
22. What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of
Predefined execptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.
23. What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.
e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
24. What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an
user_defined error messages from stored sub-program or database trigger.
25. What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occured.
SQLERRM returns the relevant error message of the SQLCODE.
26. Where the Pre_defined_exceptions are stored ?
In the standard package.
Procedures, Functions & Packages ;
27. What is a stored procedure ?
A stored procedure is a sequence of statements that perform specific function.
28. What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION is alway returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.
29. What are advantages fo Stored Procedures /
Extensibility,Modularity, Reusability, Maintainability and one time compilation.
30. What are the modes of parameters that can be passed to a procedure ?
IN,OUT,IN-OUT parameters.
31. What are the two parts of a procedure ?
Procedure Specification and Procedure Body.
32. Give the structure of the procedure ?
PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;
33. Give the structure of the function ?
FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;
34. Explain how procedures and functions are called in a PL/SQL block ?
Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');
35. What is Overloading of procedures ?
The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line
36. What is a package ? What are the advantages of packages ?
Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Applicaton Design, Information. Hiding,. reusability and
Better Performance.
37.What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.
38. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
39. How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any
out/in-out parameters. A function can not be called.
40. Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.

SQL*Loader

SQL*Loader is a tool that is used to load data into a database, it is very powerful and has the following
capabilities
· use it to transform data before it's loaded into the database
· load data from multiple sources i.e. disk, tape and named pipes
· load data across a network
· selectively load from the input file based on conditions
· load part or all of a table
· perform simultaneous loads
· auto the whole process using the Oracle scheduler.doc
· load complex object-relational data

The SQL*Loader can perform several types of data loading
· conventional - reads multiple rows at the same time into a bind array then inserts all at once and then
commits them, basically using insert statements
· direct-path - does not use insert statements, basically loads the data blocks above the high water mark
then adjusts it after it has finished.
· external - the new external tables feature uses SQL*Loader to access external data as if it were part of
the database tables.

direct-path loading is much faster than conventional loading as it bypasses the Oracle SQL mechanism,
however there are few options available when using this option. Direct-load has the following criteria

· loads data directly into the data block en mass by passing the buffer cache and redo and undo logs
· indexes are rebuild after loading
· foreign keys and check constraints are disabled during loading (automatically disabled then re-enabled)
· primary, unique keys and null keys are enabled during loading
· no activity is allowed on the table as it is locked
· triggers do not fire
· clusters are not supported
· foreign keys are disabled when loading
· loading parent and child tables together is not supported
· loading varray or bfile columns is not supported
· you cannot apply SQL functions during loading

Use the below as a guide

There are two steps when using the SQL*Loader select the data that you want to load and create a SLQ*Loader
control file

SQL*Loader control file

This is a simple text file which has specific details about the data load job, such has location on the data
file.There are many option, i have listed a number below but its best to read the Oracle documentation
If your data is already formatted you can use one of the file format parameters

· stream record format - uses the record terminator to indicate the end of the record, this is the slowest of
the methods
· variable record format - you explicity specify the length at the beginning of the record
· fixed record format - you specify that all records are a specific fixed size

Invoking SQL*Loader

You can either use a parameter file or specify all the parameters on the command line

AP Invoice Interface

Excel Sheet -> Data file -> SQL Loader (Control file) -> Staging Tables -> Run Validation program -> Interface Tables -> Run the Payables Open Invoice Import Program -> Base Tables

Staging tables:
Create a custom staging table XX_AP_INVOICES_INTERFACE, XX_AP_INVOICE_LINES_INTERFACE which should be same as seeded interface table AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE.
Interface Tables:
1. AP_INVOICES_INTERFACE
2. AP_INVOICE_LINES_INTERFACE
Base Tables:
1. AP_INVOICES_ALL
2. AP_INVOICE_DISTRIBUTIONS_ALL
3. AP_PAYMENT_SCHEDULES_ALL
Error table:
1.AP_INTERFACE_REJECTIONS
2.AP_INTERFACE_CONTROLS
Process Flow:
1. Excel sheet template is prepared which contains all the columns in AP_INVOICES_INTERFACE and AP_INVOICE_LINES table.
2. Convert the Excel Sheet which contains data to be interfaced given by the user into CSV file format.
3. Format the CSV file so that it contains only the relevant data separated with commas.
4. Rename the CSV into DAT file.
5. Prepare the Control File.
6. Run SQL Loader Script.
7. Check the Log file for any errors. In case of any errors rectify it and re-run the SQL Loader Script.
8. Run the PL/SQL Program to validate staging table data and load to Interface tables.
9. Run the Payables Open Invoice Import Program.
10. Check for the status in the Interface Table.
11. Query for the data in the front-end.

How to submit a concurrent program from command / shell prompt

We can submit concurrent programs from OS command / shell prompt using CONCSUB utility. Heres the full length syntax: We can submit concurrent programs from OS command / shell prompt using CONCSUB utility. Heres the full length syntax:
Examples:
Active Users
CONCSUB apps/[apps_pwd] SYSADMIN “System Administrator” SYSADMIN WAIT=N CONCURRENT FND FNDSCURS
Prints Environment Variables
CONCSUB apps/[apps_pwd] SYSADMIN “System Administrator” SYSADMIN WAIT=N CONCURRENT FND FNDPRNEV APPL_TOP

Oracle Apps E-books

Easy to Learn Oracle Apps Tutorials
==================================== http://www.exforsys.com/content/category/17/260/342/
Oracle Apps Student Guides
==========================
http://www.megaupload.com/?d=YTD92KVF http://files.filefront.com/11i+Student+Guidesrar/;5289744;;/fileinfo.html
Oracle Student Guides
============================
http://www.megaupload.com/?d=WPXTBMMV
Oracle Financials Open Interface Manual
==================================
http://www.megaupload.com/?d=K0QVC3ID
Video Tutorials on Oracle General Ledger
==========================================
http://www.megaupload.com/?d=JOET6PQS
Oracle ADI and 11i Tip Sheets
=================================
http://www.megaupload.com/?d=UYQDBNC9
Oracle Student Guides on HRMS
==============================
http://www.megaupload.com/?d=PT3HT7U2
GL Practices
============
http://www.megaupload.com/?d=7TXUMWZ1
Order Management Demos
===========================
http://www.megaupload.com/?d=DVNKZRL7
OPM Setup Demos
====================
http://www.megaupload.com/?d=AOXMEDDC
Oracle Student Guide on Order-To-Cash Life Cycle
===================================
http://www.megaupload.com/?d=0TADDC1I
Standard Reports in GL,AR,AP,PO
===============================
http://www.megaupload.com/?d=ZT2D0VWX
Good Document on How to Integrate Custom Report with Oracle Apps ==============================
http://www.megaupload.com/?d=ABHRCOTK
SQL How-To's for Practice
=======================
http://www.megaupload.com/?d=RHYD2D3G
White papers on AP
=====================
http://www.megaupload.com/?d=DYQZXAT3 http://d.turboupload.com/d/732112/AP.zip.html
White papers on GL
======================
http://www.megaupload.com/?d=TKV860E3
Oracle Financials Open Interface Manual
======================================
http://www.megaupload.com/?d=K0QVC3ID

Data Load Tutorial Introduction

Data Load Tutorial Introduction:
Dataload is an utlity where you can load data into different Form Based systems especially Oracle APPS. This simple utility works by recording keystrokes, that are necessary for loading data from frontend.
Software be downloaded free from
http://www.dataload.net/downloads/download.php
Advantages:
1. Easy to learn and use
2. Can save time for repetitive processes
3. Can be copied from Excel
4. Can be an option to data edits and complex interfaces if the data are simple
Disadvantages:
5. Can not track mouse movements.
6. Can not perform interactive sessions to much extent.
7. Do not generate an success failure logs.
List Of Commands:

Note 1 DataLoad can send keystrokes to applications faster than they can be processed. If this problem is encountered delays can be added to the load which will pause DataLoad at key times. The *SLN command can be added to the spreadsheet to indicate DataLoad should 'sleep' for a given number of seconds. E.g. '*SL5' will cause a delay in processing for 5 seconds. Decimal numbers can be used for more precise delays, E.g. *SL0.5 will result in a half second delay. A large number of predefined delays are available in DataLoad and these, along with *SL, are described in greater detail in Using delays. To reduce setup work, predefined delays should be used instead of *SL wherever possible.

Note 2 In Oracle Applications it is sometimes necessary to press a button to navigate to another block. This can be achieved by pressing , where X is the letter that is underlined on the button. Any menu item can also be invoked by pressing + the letter underlined on the menu.

To use any combination of the Shift, Control, Alt and right Alt keys one of the following codes should be used. If you want to send the +, ^, % or & keys these characters test must be enclosed in braces {}.

Key Code
SHIFT +
CTRL ^
ALT %
Right Alt &


Case Study:Granting “Application Developer” responsibility to 3 users.

Process.
8. The following example would show how to assign ‘Application Developer” responsibility to the users USER1, USER2 and USER3
9. Try to record the process of assigning the responsibility to an user through key-strokes only. 10. Record the Keystrokes in terms of Data Load commands. Note them sequentially to create the Dataload file (.dlt) as shown in the screenshot below.

5. Execute the Data Load being choosing the right window and command group.

6. Be absolutely sure no other window becomes active during the process of data loading.
After completion of data load the window shows the final status.

DB Procedure to check if file exists

DB Procedure to check if file exists

This script is required when you want to check a file if it physically exists on the server or not. Use to two out parameters when you register this procedure a concurrent program otherwise comment them out. This procedure uses concept of exception handling to return the value when you try to open the file.
When you register it as a concurrent program pass two parameters to the concurrent program as follows.
1. Directory
2. File Name
Concurrent program will comple with status as "Normal" when file exists and as "Warning" when doesn't exist.
Code:
CREATE OR REPLACE PROCEDURE apps.erp_schools_checkfile (
errbuf OUT VARCHAR2, -- This parameter is required only when this procedure is registered as a concurrent program
retcode OUT NUMBER, -- This parameter is required only when this procedure is registered as a concurrent program
p_directory IN VARCHAR2,
p_filename IN VARCHAR2
)
IS
v_file SYS.UTL_FILE.file_type;
v_filename1 VARCHAR2 (100) := NULL;
v_output_dir VARCHAR2 (200) := NULL;
code VARCHAR2 (2000) := NULL;
error VARCHAR2 (2000) := NULL;
BEGIN
v_filename1 := p_filename;
fnd_file.put_line (fnd_file.LOG, 'FILENAME = ' v_filename1);
v_output_dir := p_directory;
fnd_file.put_line (fnd_file.LOG, 'DIRECTORY = ' v_output_dir);
v_file := UTL_FILE.fopen (v_output_dir, v_filename1, 'r');
UTL_FILE.fclose (v_file);
retcode := 0;
errbuf := 'FILE OPENED AND CLOSED SUCCESSFULLY';
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line (fnd_file.LOG, 'INVALID PATH (' v_output_dir);
retcode := 1;
errbuf := 'INVALID PATH ';
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line (fnd_file.LOG, 'INTERNAL ERROR ');
retcode := 1;
errbuf := 'INTERNAL ERROR ';
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line (fnd_file.LOG, 'INVALID FILEHANDLE ');
retcode := 1;
errbuf := 'INVALID FILEHANDLE ';
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line (fnd_file.LOG, 'INVALID MODE ');
retcode := 1;
errbuf := 'INVALID MODE ';
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line (fnd_file.LOG, 'INVALID OPERATION ');
retcode := 1;
errbuf := 'INVALID OPERATION ';
WHEN UTL_FILE.read_error
THEN
fnd_file.put_line (fnd_file.LOG, 'FILE READ ERROR ');
retcode := 1;
errbuf := 'FILE READ ERROR ';
WHEN OTHERS
THEN
error := SQLERRM;
fnd_file.put_line (fnd_file.LOG, 'THE ERROR IS ' error);
retcode := 1;
errbuf := 'THE ERROR IS ' error;
END erp_schools_checkfile;
/

FNDLOAD

FNDLOAD

The Generic Loader (FNDLOAD) is a concurrent program that can transfer Oracle Application entity data between database and text file. The loader reads a configuration file to determine which entity to access. In simple words FNDLOAD is used to transfer entity data from one instance/database to other. for example if you want to move a concurrent program/menu/valuesets developed in DEVELOPMENT instance to PRODUCTION instance you can direct use this command.


Steps to Move a Concurrent program from one instance(Database) to other

· Define your concurrent program and save it in first instance(for how to register a concurrent program click here)
· Connect to your UNIX box on first instance and run the following command to download the .ldt file
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="Concurrent program application short name" CONCURRENT_PROGRAM_NAME="concurrent program short name"

· Move the downloaded .ldf file to new instance(Use FTP)
· Connect to your UNIX box on second instance and run the following command to upload the .ldt file

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct
file_name.ldt

Note: Make sure you are giving proper .lct file in the commands and don’t confuse with .lct and .ldt files

These following are the other entity data types that we can move with FNDLOAD
1 - Printer StylesFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"
2 - LookupsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND" LOOKUP_TYPE="lookup name"
3 - Descriptive Flexfield with all of specific ContextsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"
4 - Key Flexfield StructuresFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"
5 - Concurrent ProgramsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name"
6 - Value SetsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"
7 - Value Sets with valuesFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"
8 - Profile OptionsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"
9- Request GroupsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"
10 - Request SetsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SETAPPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME="request set"
11 - ResponsibilitiesFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"
12 - MenusFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"
13 - Forms PersonalizationFNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES function_name=FUNCTION_NAME
Note: UPLOAD command is same for all except replacing the .lct and passing any extra parameters if you want to pass
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/corresponding.lct
upload_file.ldt

Develop and Register new form

1. Take the template(template.fmb) form server
Generally, Template will be available in AU_TOP
Ex:/m100/app/applmgr/110/kup8989/au/11.0.28/forms/US/
template.fmb
2. Open with form 4.5 or 6i
3. Remove the window, which is having name as "block_name";
Create a new window with the name what u wants like "window_what_u_want"
We may take a window title from window option "title".
4. Go to canvas, remove the canvas, which is having "block_name"
Create a new canvas with the name what u wants like“CANVAS_WHAT_U_WANT"
In the canvas go to canvas options attach window name is “window_what_u_want"
5. Go to block level and delete the "blockname" block (which is system default)
Create a base table data block
· Base table name any example here is "customers"
· Block name data block name what u ever u like.my block name is “customers”
· Canvas "Select canvas name where u want to display" here canvas is “CANVAS_WHAT_U_WANT"
· Select model of form like tabular or form or vertical or horizontal, number of record
6. Go to canvas select all items and change it properties of class to “TEXT_ITEM”
7. Go to Block properties

· First navigation block “give the name of block what to want, here I will give my block name i.e. “customers”
· Block property Class is “block”
8. Go to block level Trigger “PRE_FORM”
Give the window name (window_what_u_want) instead of blockname
Ex:First: app_window.set_window_position ('blockname, 'FIRST_WINDOW');
Change to app_window.set_window_position ('window_what_u_want', 'FIRST_WINDOW');
9. Go to new form instance trigger “NEW-FORM-INSTANCE-TRIGGER”
Remove template.fmb and paste form name eg is “customer_form”
Add the text here
go_block('customers');
execute_query;
10. Go to program units=>apps_custom (package body)
Change the window name to “window_what_u_want”
(Approximately 39 line if condition)
11. Take button and name it as save
When button pressed trigger write “commit_form”
12. Take a button and name it as exit and
When button pressed trigger
Write “exit_form”
13. Take a button and name id as clear button

When button pressed trigger write “clear_form”;
14. Take a button, name it as next and when button pressed, write “next_item”;
15. Take a button, name it as previous and when button pressed trigger white “previous_item”;
16. Comfile form and save it as customer_form
17. Move to server in an application where we need to move
Path :-------- :/m100/app/applmgr/110/kup8989/po/11.0.28/forms/US
Application like oracle po, oracle inv etc.
Generally, we use FTP to move file from local to server and sever to local
18. Go to tel net
It is like cmd prompt
19. Change the path to location where Ur file have
My file in oracle po
So change to oracle po
Command is
Cd /m100/app/applmgr/110/kup8989/po/11.0.28/forms/US
20. Now we need to create fmx file
Command
F60gen module=customer_form userid=userid/passwd@databasename
For oracle form 6i
F45gen module=customer_form userid=userid/passwd@databasename
For oracle 4.5i
Ex: f60gen module=customer_form userid=scott/tiger@kup8989
Go to application developer
21. Application developer=>Application=>forms
22. Enter form name, Application , username and take user form name.
23. Application deleveloper=>application=>function
Give unique function name and user function name; type is form and go to stage 2
Function stage1
Stage 2
Enter form user name (22 point snapshot user function name)
Save the file
24. Take the function name
25. Go to application developer=>application=>menu
Add function name (cust_form) to the menu
26. Go to system administrator and Go to specific responsibility
System administrator=>responsibility=>define
27. Define responsibility , Add menu name to that responsibility
28. Select the responsibility(Test Responsibility), your form will appear……

ORACLE 9I COMMANDS

ORACLE 9I COMMANDS

To create table
Create table (value1 datatype,……);

To insert to table
Insert into values(data1,……);

To view the all the data table
Select * from ;

To view some column
Select from ;

To update data in the table
Update set =data to update where =data to find;

To delete from table
Delete from where =data to find;

To drop the table;
Drop table ;

To set primary key
When you create the table
Create table (value1 datatype primary key,…..);
After creating the table
Alter table add constraint primary key(column-name>;

To set foreign key
When creating the table
Create table (value1 datatype, constraint foreign key(column-name) references );
After creating the table
Alter table add constraint foreign key(column-name) \
References ;

To set the check constraint
When creating the table
1-column type
Create table (column1 datatype constraint
check(column1 operator rule));
2-table type
Create table (column1 datatype, column2 datatype,constraint
check(column1 rule column2 );

after the table created
alter table add constraint check(column-name rule);

to set not null
when creating the table
create table (column1 datatype not null);
to set unique key
when create the table
create table (column1 datatype unique);
after creating the table
alter table add constraint unique(column-name);

to use joins
1-inner join
select column1-table1,column1-table2 from ,
where =;
2-right join
select column1-table1,column1-table2 from ,
where (+)=;
3-left join
select column1-table1,column1-table2 from ,
where =(+);
3-self join
select a.column1,b.column1 from a, b where
a.column1 > b.column1 order by ;

to view the sum
select sum(column-name) from ;

to view the average
select avg(column-name) from ;

to view max
select max(column-name) from ;

to view min
select min(column-name> from ;

to view some character from the column
select substr(column-name,1,3) from ;

to view in upper case
select upper(column name) from ;

to view in lower case
select lower(column-name) from ;

to view the first latter in capital
select initcap(column-name) from ;

to remove spaces from left side of the column
select * from where ltrim(column-name);
to remove spaces from right sode of the column
select * from where rtrim(column-name);

to view the system date
select sysdate from dual;

to view the structure of the table
desc

to add new column to the table
alter table add datatype;

to modify the column in table
alter table modify datatype

to view data using in operator
select from where in(‘value1’,’value2’);
to view data using not in operator
select from where not in(‘value1’,’value2’);

to create sequence
create sequence
increment by
start with
maxvalue ;
/
you can use cycle after the maxvalue so when its finished it can repeat

to modify the sequence
alter sequence
increment by
start with
maxvalue ;
/

to use sequence
insert into (sequence-name.nextval);

to create synonyms
create synonym for username.table-name;

to drop the synonym
drop synonym ;

to drop the constrain
alter table drop constraint ;

to drop the primary key
alter table drop primary key
but if the primary key was referenced to a foreign key you cant drop

to make the constraint enabled or disabled
alter table enable constraint ;
alter table disable constraint ;

to put the up title on the report
ttitle ‘the data you want to write’
to set it off
ttitle off

to put the bottom title
btitle ‘the data you want to write’
to set it of
btitle off

to let the sql print report
set serveroutput on

to save to the tables
set autocommit on

to set the line size for the table
set linesize value

to set how many rows to be displayed
set pagesize value

to set the number of the column width
set numwidth value

to set the format of the column
column format a
to set break between the rows
break on skip on report

to set for average for report
before to set average it must have break on
the on-value must match the compute on value
break on skip
compute avg of on ;

to set for sum for report
before to set sum it must have break on
break on skip
compute sum of on ;

Detailed Reports Information of some reports

UNBOOKED ORDERS REPORT
The unbooked Orders Report reviews orders you have entered but not booked.
Thes report shows you the order number order date ordered item and line amount for each unbooked order line you choose as well as the user who entered the order.
Submission:
in the Order Management Reports window select Unbooked Orders Report is the
Name field.
Parameters:
When you request a Unbooked Orders Report, Order Management provides you
with the following parametes. If you live any of the non requred parameters blank, this report includes all unbooked orders that meet your other parameter criteria.
In order to obtain a single unbooked order, enter the same unbooked order in
the From and To fields.
Sort by; (mandatory)
Choose from created by or order number. The default value is Order Number.
Created By (from/to)
Choose the user ids of the order entry clerk who entered the order that you want
printed in this report.
Order Date(from/to)
Choose the range of order dates you want printed in this report.
Order category
Line Category
Item Display (mandatory)

Column Headings:

Order Number
Order management prints the order number of the item on each order line.
Order Date
Order Management prints the order date of the item.
Created By
Order management print the user who created the order
Item
Order Management prints the Item Flexfield value of the item on each order line
Description
Order Management print the sdescription of the item on each order line.
Extended Line Amount
Order Management prints the extended order line amount for each order line.

Add a summary column to display all unbook orders.
Total of extended line amount
Name it group by currency and order date (it was group by currency and order number)
Order by order date
Application -- Oracle Order Management
Executable -- OEXOEUBR.
Tables
fnd_user
mtl_system_items_vl
oe_order_lines_all
oe_order_headers_all
oe_order_types_v
mtl_customer_items
ra_site_usesbill_site
ra_customerssold_to
per_all_people_fppf_mgr
per_all_assignments_f
per_all_people_f


VENDORS ON HOLD REPORT

The vendor on Hold Report lists all suppliers placed on hold. This report also
prints all purchase orders you still have outstanding with vendor on hold.
Report Submission
In the Submit Requests window. select vendors on hold report in the Name field.
Report parameters
supplier Names From/ To
enter the begining and ending vendor names.
PO Numbers From/To
Enter the beginning and ending purchase order numbers.
Sort by
Use the sort by feature to produce a report custmized to your specific needs.
Choose one of the following the options: sort the report by purchase order number sort the report by supplier.
Dynamic precision Option
Enter the decimal precision for quantities on the report
Application --- Oracle purchasing
Executable--POXVDVOH
REPORT COLUMN

PO order
vendor number
creation date
Hold reason
status
currency
Hold date
Hold by
ammount
New column(tables--PO_LINE-LOCATION,PO_LINES,PO_LINE_TYPE)
(quantity)Amount ordered(PO_LINE_LOCATIONS,PO_LINES)(quantity)
(Quantity)Amount received (PO_LINE_LOCATIONS)
(quantity)Amount due(PO_LINE_LOCATIONS)
Tables
gl_sets_of_books
financials_system_parameters
hr_employees
Po_system_parameters
po_lookup_codes
Po_vendors
po_headers
po_line_locations

ORDERS SUMMARY REPORT

The Order Summary Report reviews orders you have entered.
This report shows you the order number customer Nme, customer op.
Agreement order status, list amount ordered amount shipped amount
created by for each order you choose as well as line sales person rep
(new column added) who entered the order.

Submission
In the Oracel Order Entry Reports window, lelect Orders Summary
Report in the Name field.
Parameters
When you request a Unbooked Orders Report, Order Management provides you
with the following parametes. If you live any of the non requred parameters blank,
this report includes all entered orders that meet other parameter criteria. In order
to uptain a single entered order, enter the same enter order in the from and to
fields.
Sort by (MANDATORY)
Choose from created by or order number. The default value is order date.
Sales person name (from/to)
Order date (from/to)
Order number (from/to)
Ship To Cuountry (from/to)
Order Type (from/to)
Customer PO number (from/to)
Ordered by (from/to)
Order Source (single)
Open Order Only (mandatory dafault val is Yes single)
(Use functional currency only)
Add new column for salesperson of order (got from RA_SALESREPS)
And change order by clause to order by order date (it was order by order number)
APPLICATION - ORACLE ORDER MANAGEMENT
EXECUTABLE - OEXOEOSR

Tables
oe_order_headers_all
oe_order_lines_all
oe_order_types_v
fnd_user
oe_agreements
ra_site_uses
ra_addresses
ra_salesreps (NAME OF SALES REP)
fnd_territories_vl
oe_sold_to_orgs_v


VENDOR PURCHASE SUMMARY REPORT
The Vendor Purchase Summary report lists the numbers and amount of orders you have placed with various suppliers during a particular period. You can use this information to analyze and manage your purchasing valume with your Vendor.
Report Submission
In the Submit Requests window. select Vendor Purchase Summary Report in the
Name field
Report Paramdeters
Creation Dates From/To
Enter the beginning and ending creation dates
Supplier type
Enter the supplier type from the Lookup Codes window to restrict the report to a specific supplier type.
Small business
Enter Yes to restrict the report to small business suppliers.
Minority Owned
Enter Yes to resrtict the report to minoriry owned suppliers.
Women Owned
Enter Yes to restrict the report to women owned suppliers.
Sort by
Use the Sort By fearure to produce a report customized to your specific
needs. Choose one the following options.
Sort the report by supplier name,supplier site, and PO number
Sort the report by supplier type,supplier name,supplier site, and PO number
Application;--Oracle purchasing
Executable-- POXPOVPS
Report column

Vendor name
vendor number
vendor site
PO type
PO number
Creation date
Reserved date
Currency
Description
PO amount
functional amount
add new column for vendor type(Table--PO-LOOKUP-CODES)
Amount ordered( PO_LINE-LOCATIONS,PO_LINES)
Amount received (PO_LINE_LOCATIONS)
Amount due ((PO_LINE_LOCATION)
TABLES
gl_sets_of_books
financials_system_parameters
mtl_default_sets_view
fnd_lookups
fnd_currencies
po_line_locations
po_headers
po_vendor_sites
po_vendors

OVERSHIPMENTS REPORTS
The overshipments Report lists purchase order receipts with a quantity
received greater than the quantity ordered. You can also use the Overshipments
Report to identify service overcharges or suppliers who deliver more than the requested
quantity.

Report Submission
In the Submit Requests window. select Overshipments Report in the Name field
Report Parameters
Receipt Location
Enter the receiipt location to restrict the report to overshipments to a specific internal location
Receiver
Enter the name of tghe employee to restrict the report to overshipments to a specific receiver.
Receipt Date From/To
Enter the beginning and ending receiipt dates to restrict he dreport to a range of receipt dates.
Supplier From/To
Enter the beginning and ending supplier names to restrict the report to a range of suppliers.
Supplier site
Enter the supplier site to restrict the report to overshipments to a specific supplier site.
Organization Name
Enter the organization name to restrict the report to a specific organization.
Sort By
Use the sort By feature to produce a report customized to your specific needs.
Choose one of the following options:
Sort the report by document number. This is the default if you do not choose an option.
Sort the report by supplier.
Quantity Precision Option
Enter the decimal precision for quantities on the report.
Tables
gl_sets_of_books
financials_system_parameters
mtl_default_sets_view
fnd_lookups
po_vendors
poA_vendor_sites
po_lines
mtl_system_items
financials_system_parameters
hr_locations
po_line_types
po_line_locations
po_headers
mtl_categories
po_lookup_codes
po_document_types
po_system_parameters
hr_employees
rcv_transactions
rcv_shipment_headers
po_lookup_codes
mtl_transaction_reasons
org_organization_definition

ORDER/INVOICE SUMMARY REPORT

The Order/Invoice Summary Report reviews summary invoice information about order that have invoiced, including ordered amount, invoiced amount, adjusted receivables and orders that have been invoiced.
Submission
in the order management Reports window, select Order/invoice Summary Report in the Name field.
Parameters
When you request a Order/Invoice Summary Report, Order Management provides you with the following parameters. If you leave any parameters blank. this report includes all orders that meet your other parameter criteria. In order to obtain a single order. Enter the same order in the from and to fields.
Sort by
Choose from customer name, order type, and salesperson to include in this report.
Open Orders only
Choose No if you want to print both open and closed orders in this report. The default value is yes.
Customer Name (From/To)
Choose the ranbe fo customers that you want printed in this report.
Salesperson(From/To)
Choose the range of salespeople that you want printed in this report.
Order Type(From/To)
Choose the range of order type that you want printed in this report.
Attention: Non-invoiced orders print which display a zero(0) balance due. Non-invoiced
orders display the message, No Invoices /exist For this Order.Order/invoice summary Report
Order Number(From/To)
Choose the range of order numbers that you want printed in this report..
Ship-To country
Choose the country for the ship to addresses of the shipments you want printed in this report/
Show in Functional Currency
Choose Yes if you want to print any currency amounts in the functional currency. or No if you
want tro print any currency amounts in the cureency for trhe order. The default value is No.

Column headings

Total Credits/Adjustments
Order Management prints the total amount of receivable credits or adjustments applied to the
invoice.
Balance Due
Order Management prints the outstanding balance for the invoice
Ordered Amount
Order Manafementr prints the quantity requested for each order.
Invoiced Amount
Order management prints the invoiced amount for each order.
Tables
oe_order_lines_all
ra_customer_trx
ra_customer_trx_lines
oe_order_headers_all
oe_order_lines_all
oe_order_types_v
ra_siteuses
ra_addresses
ra_salesreps
fnd_territories_vl
oe_sold_to_org_v

Multi Org Implementation Setups

1.Login as sysadmin responsibility

2.Ddefine required responsibilities Navigation:security->responsibility->define

3.Define user and assign responsibilities to the user. N:Security->user->define.

4.Login as GL Responsibility

5.Define accounting flexfield N:setup->financials->flexfield->key->segments

6.Give values for your segments N:setup->financials->flexfield->key->values.

7.Define Currency N:setup->Curriencies->define

8.Define Calender.N:Setup->financials->calender->Type/Accounting

9.Create SOB N:Setup->financials->book->define

10.Login as HRMS responsibility.

11.Define a location N:Work Structure->Location

12.Define a Business Group N:Works Structure->organization->description

13.Set the following Profile Options to all your responsibilitiesHR:securityHR:bisiness groupHR:User TypeGL:Set of books name

14.Login As Inventoruy responsibility

15.Create legal entity N:Setup->organizations->organizations

16.Create Operatiing unit N:Setup->organizations->organizations

17.Set Profile option Mo:Operating unit for all responsibilites which is worked at operating unit level.

18.Create Work day calender

19.Create inventory Organization. N:Setup->organizations->organizations

20.Login as sysadmin and run replicate seed data program.

Multiorg Concept

Multi-Org is a server-side (applications and database) enhancement that enables multiple business units in an enterprise to use a single installation of Oracle Applications products while keeping transaction data separate and secure. The Multi-Org enhancement uses native database views to build a security layer on top of a single installation of Oracle Applications. In Oracle Applications Release 11i, the following products support
Multi-Org capabilities:
• Cash Management
• Order Management, Shipping Execution and Release Management
• Payables
• Property Manager
• Projects
• Purchasing
• Receivables
• Sales Compensation
• Sales and Marketing
• Service
Basic Business Needs
The Multi-Org enhancement to Oracle Applications provides features necessary to satisfy the following basic business needs. You should be able to:
• Use a single installation of any Oracle Applications product to support any number of business units, even if those business units use different sets of books.
• Support any number of business units within a single installation of Oracle Applications.
• Secure access to data so that users can access only information that is relevant to them.
• Procure products from an operating unit that uses one set of book, but receive them from another operating unit using a different set of books.
• Sell products from an operating unit that uses one set of books, but ship them from another operating unit using a different set of books, automatically recording the appropriate intercompany sales by posting intercompany accounts payable and accounts receivable invoices.
• Report at any level of the organizational structure.

By running the following SQL statement we can know
select multi_org_flag from fnd_product_groups;
The result 'Y' means your database is setup for multiorg.
Limitations Of Multi Org
1.With the exception of data that is shared across organizations,all data is secured and striped by operating unit level.
2.Multiple Organizations enabled products do not support viewing secured data across operating units.
For example you can take Supplier defination ,We can see header information only from other operating unit.It's not possible to share site information.Bank defination also.....But in R12 Bank defination has been changed.Now we can maintain one bank account for N number of operating units under one legal entity.
3.There is no additional support for centralization/decentralization of business functions.

WHEN TRIGGERS

WHEN TRIGGERS
When-Button-Pressed
Description
Fires when an operator selects a button, by clicking with a mouse, or using the keyboard.
Definition Level form, block, or item
When-List-Changed
Description
Fires when an end user selects a different element in a list item or de-selects the currently selected element. In addition, if a
When-List-Changed trigger is attached to a combo box style list item, it fires each time the end user enters or modifies entered
text.
Definition Level form, block, or item
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes
When-Clear-Block
Description
Fires just before Form Builder clears the data from the current block.
Note that the When-Clear-Block trigger does not fire when Form Builder clears the current block during the CLEAR_FORM event.
Definition Level form or block
Legal Commands
SELECT statements, unrestricted built-ins
Enter Query Mode yes
When-Form-Navigate
Description
Fires when navigation between forms takes place, such as when the user changes the focus to another loaded form.
Definition Level form
Legal Commands:
unrestricted built-ins, restricted built-ins
Enter Query Mode:
When-Mouse-DoubleClick
Description
Fires after the operator double-clicks the mouse if one of the following events occurs:
if attached to the form, when the mouse is double-clicked within any canvas or item in the form
if attached to a block, when the mouse is double-clicked within any item in the block
if attached to an item, when the mouse is double-clicked within the item
Six events must occur before a When-Mouse-DoubleClick trigger will fire:
Mouse down
Mouse up
Mouse click
Mouse down
Mouse up
Mouse double-click
Any trigger that is associated with these events will fire before the When-Mouse-DoubleClick trigger fires.
Definition Level form, block, or item
Legal Commands
SELECT statements, restricted built-ins, unrestricted built-ins
Enter Query Mode yes
When-Mouse-Enter
Description
Fires when the mouse enters an item or canvas if one of the following events occurs:
if attached to the form, when the mouse enters any canvas or item in the form
if attached to a block, when the mouse enters any item in the block
if attached to an item, when the mouse enters the item
Definition Level form, block, or item
Legal Commands
SELECT statements, restricted built-ins, unrestricted built-ins
Enter Query Mode yes
When-Mouse-Move
Description
Fires each time the mouse moves if one of the following events occurs:
if attached to the form, when the mouse moves within any canvas or item in the form
if attached to a block, when the mouse moves within any item in the block
if attached to an item, when the mouse moves within the item
Definition Level form, block, or item
Legal Commands
SELECT statements, restricted built-ins, unrestricted built-ins
Enter Query Mode yes
When-New-Block-Instance
Description
Fires when the input focus moves to an item in a different block. Specifically, it fires after navigation to an item, when Form
Builder is ready to accept input in a block that is different than the block that previously had the input focus.
Definition Level form or block
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode no
When-New-Item-Instance
Description
Fires when the input focus moves to an item. Specifically, it fires after navigation to an item, when Form Builder is ready to
accept input in an item that is different than the item that previously had input focus.
Definition Level form, block, or item
Legal Commands
SELECT statements, restricted built-ins, unrestricted built-ins.
Enter Query Mode yes
When-Radio-Changed
Description
Fires when an operator selects a different radio button in a radio group, or de-selects the currently selected radio button, either
by clicking with the mouse, or using the keyboard.
Definition Level form, block, or item
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes
When-Tab-Page-Changed
Description
Fires whenever there is explicit item or mouse navigation from one tab page to another in a tab canvas.
Definition Level form
Legal Commands
unrestricted built-ins, restricted built-ins
Enter Query Mode no
When-Validate-Item
Description
Fires during the Validate the Item process. Specifically, it fires as the last part of item validation for items with the New or
Changed validation status.
Definition Level form, block, or item
Legal Commands
SELECT statements, unrestricted built-ins
Enter Query Mode no
When-Window-Activated
Description
Fires when a window is made the active window. This occurs at form startup and whenever a different window is given focus.
Note that on some window managers, a window can be activated by clicking on its title bar. This operation is independent of
navigation to an item in the window. Thus, navigating to an item in a different window always activates that window, but
window activation can also occur independently of navigation.
Definition Level form
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes
When-Window-Deactivated
Description
Fires when an operator deactivates a window by setting the input focus to another window within the same form.
Definition Level form
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes
When-Image-Pressed
Description
Fires when an operator uses the mouse to:
single-click on an image item
double-click on an image item
Note that When-Image-Activated also fires on a double-click.
Definition Level form, block, or item
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes
When-Checkbox-Changed
Description
Fires when an operator changes the state of a check box, either by clicking with the mouse, or using the keyboard.
Definition Level form, block, or item
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes
When-Create-Record
Description
Fires when Form Builder creates a new record. For example, when the operator presses the [Insert] key, or navigates to the
last record in a set while scrolling down, Form Builder fires this trigger.
Definition Level form or block
Legal Commands
SELECT statements, unrestricted built-ins
Enter Query Mode no
When-Database-Record
Description
Fires when Form Builder first marks a record as an insert or an update. That is, the trigger fires as soon as Form Builder
determines through validation that the record should be processed by the next post or commit as an insert or update. This
generally occurs only when the operator modifies the first item in a record, and after the operator attempts to navigate out of
the item.
Definition Level form or block
Legal Commands
SELECT statements, unrestricted built-ins
Enter Query Mode no
When-Image-Activated
Description
Fires when an operator uses the mouse to:
single-click on an image item
double-click on an image item
Note that When-Image-Pressed also fires on a double-click.
Definition Level form, block, or item
Legal Commands
SELECT statements, unrestricted built-ins
Enter Query Mode no
When-List-Activated
Description
Fires when an operator double-clicks on an element in a list item that is displayed as a T-list.
Definition Level form, block, or item
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes
When-Mouse-Click
Description
Fires after the operator clicks the mouse if one of the following events occurs:
if attached to the form, when the mouse is clicked within any canvas or item in the form
if attached to a block, when the mouse is clicked within any item in the block
if attached to an item, when the mouse is clicked within the item
Three events must occur before a When-Mouse-Click trigger will fire:
Mouse down
Mouse up
Mouse click
Any trigger that is associated with these events will fire before the When-Mouse-Click trigger fires.
Definition Level form, block, or item
Legal Commands
SELECT statements, restricted built-ins, unrestricted built-ins
Enter Query Mode yes
When-Mouse-Down
Description
Fires after the operator presses down the mouse button if one of the following events occurs:
if attached to the form, when the mouse is pressed down within any canvas or item in the form
if attached to a block, when the mouse is pressed down within any item in the block
if attached to an item, when the mouse is pressed within the item
Definition Level form, block, or item
Legal Commands
SELECT statements, restricted built-ins, unrestricted built-ins
Enter Query Mode yes
When-Mouse-Leave
Description
Fires after the mouse leaves an item or canvas if one of the following events occurs:
if attached to the form, when the mouse leaves any canvas or item in the form
if attached to a block, when the mouse leaves any item in the block
if attached to an item, when the mouse leaves the item
Definition Level form, block, or item
Legal Commands
SELECT statements, restricted built-ins, unrestricted built-ins
Enter Query Mode yes
When-Mouse-Up
Description
Fires each time the operator presses down and releases the mouse button if one of the following events occurs:
if attached to the form, when the mouse up event is received within any canvas or item in a form
if attached to a block, when the mouse up event is received within any item in a block
if attached to an item, when the mouse up event is received within an item
Two events must occur before a When-Mouse-Up trigger will fire:
Mouse down
Mouse up
Definition Level form, block, or item
Legal Commands
SELECT statements, restricted built-ins, unrestricted built-ins
Enter Query Mode yes
When-New-Form-Instance
Description
At form start-up, Form Builder navigates to the first navigable item in the first navigable block. A When-New-Form-Instance
trigger fires after the successful completion of any navigational triggers that fire during the initial navigation sequence.
This trigger does not fire when control returns to a calling form from a called form.
In a multiple-form application, this trigger does not fire when focus changes from one form to another.
Definition Level form
Legal Commands
SELECT statements, restricted built-ins, unrestricted built-ins
Enter Query Mode no
When-New-Record-Instance
Description
Fires when the input focus moves to an item in a record that is different than the record that previously had input focus.
Specifically, it fires after navigation to an item in a record, when Form Builder is ready to accept input in a record that is
different than the record that previously had input focus.
Fires whenever Form Builder instantiates a new record.
Definition Level form or block
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes
When-Remove-Record
Description
Fires whenever the operator or the application clears or deletes a record.
Definition Level form, block, or item
Legal Commands
SELECT statements, unrestricted built-ins
Enter Query Mode no
When-Timer-Expired
Description
Fires when a timer expires.
Definition Level form
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes
When-Validate-Record
Description
Fires during the Validate the Record process. Specifically, it fires as the last part of record validation for records with the New or
Changed validation status.
Definition Level form or block
Legal Commands
SELECT statements, unrestricted built-ins
Enter Query Mode no
When-Window-Closed
Description
Fires when an operator closes a window using a window-manager specific Close command.
Definition Level form
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes
When-Window-Resized
Description
Fires when a window is resized, either by the operator or programmatically through a call to RESIZE_WINDOW or
SET_WINDOW_PROPERTY. (Even if the window is not currently displayed, resizing the window programmatically fires the When-
Window-Resized trigger.) This trigger also fires at form startup, when the root window is first drawn. It does not fire when a
window is iconified.
Definition Level form
Legal Commands
SELECT statements, unrestricted built-ins, restricted built-ins
Enter Query Mode yes

Concurrent Program Submission Through Backend

declare
l_request_id number(9) ;
begin
l_request_id := Fnd_Request.submit_request
( 'PO', -Concurrent Prog Application Name
'SQL-USERS', -Conccurrent Prog Short Name
'Users Data', -Concurrent Prog description '',
-start time '',
-sub request 1000,
-first parameter value 2000,
-second parameter value
'CREATION_DATE');-third parameter value
Commit;
if l_request_id = 0
then fnd_file.put_line(fnd_file.log,'Prograqm not sumitted Succesfully');
else
fnd_file.put_line(fnd_file.log,'Prograqm sumitted Succesfully Request ID ='l_request_id);
End If;
Exception
when others then
fnd_file.put_line(fnd_file.log,'Error occured during Porgram submission');
End ;

from Triggers
declare
l_request_id number(9) ;
begin
l_request_id := Fnd_Request.submit_request
( 'PO', -Concurrent Prog ApplciationName
'SQL-USERS', -Conccurrent Prog Short Name
'Users Data', -Concurrent Prog description '',
-start time '',
-sub request 1000,
-first parameter value 2000,
-second parameter value
'CREATION_DATE','','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','');
Commit;
if l_request_id = 0 then
fnd_file.put_line(fnd_file.log,'Prograqm not sumitted Succesfully');
else
fnd_file.put_line(fnd_file.log,'Prograqm sumitted Succesfully Request ID ='l_request_id);
End If;
Exception
when others then
fnd_file.put_line(fnd_file.log,'Error occured during Porgram submission');
End ;
fnd_global.apps_initialize(user_id, Resp_id, Resp_appl_id);
-To initialize the Application Environment by specifying the UserID and RespID system will verify the User Access details based on that it will submit the Program.
1)fnd_Program.executable
2)fnd_program.register
3)fnd_program.request_group
4)fnd_program.add_to_group - Add concurrent Pogram to the Group
5)fnd_program.parameter - To create parameters for concurrent Program
6)fnd_program.incompatibility - TO attach Incompatibility Programs List
7)fnd_program.delete_group - To delete the Request Group
Any Table ,Procedure,Package,view any database Object starting with "FND" then it is relatedfor AOL(Application Obejct Library) , AOL ObjectSchema Name :APPLSYS"fnd" is nothing but foundation

KFF List

Multiorg Tables

Oracle Apps Tables

All about Forms 6i

System Variables
SYSTEM.DATE_THRESHOLD : Represents the database date requery threshold.
Default : 01:00 (Synchronization occurs after one minute of elapsed time.) This does not mean that Form Builder polls the RDBMS once every minute. It means that whenever Form Builder needs to generate the value for the system variables $$DBDATE$$, $$DBDATETIME$$, $$DBTIME$$, or SYSTEM.EFFECTIVE_DATE, it updates the effective date by adding the amount of elapsed time (as measured by the local operating system) to the most previously queried RDBMS value. If the amount of elapsed time exceeds the date threshold, then a new query is executed to retrieve the RDBMS time and the elapsed counter is reset.

SYSTEM.EFFECTIVE_DATE: Represents the effective database date. The variable value must always be in the following format: DD-MON-YYYY HH24:MI:SS
Default: RDBMS date.
SYSTEM.FORM_STATUS represents the status of the current form. The value can be one of three character strings: NEW, QUERY, CHANGED
SYSTEM.SUPPRESS_WORKING suppresses the "Working..." message in Runform, in order to prevent the screen update usually caused by the display of the "Working..." message. The value of the variable is one of the following two CHAR values: TRUE, FALSE
SYSTEM.TRIGGER_RECORD represents the number of the record that Form Builder is processing. This number represents the record's current physical order in the block's list of records. The value is always a character string.
SYSTEM.MODE indicates whether the form is in Normal, Enter Query, or Fetch Processing mode. The value is always a character string. NORMAL, ENTER-QUERY, Enter Query mode.
SYSTEM.FORM_STATUS represents the status of the current form. The value can be one of three character strings: CHANGED, NEW , QUERY.
SYSTEM.MESSAGE_LEVEL stores one of the following message severity levels: 0, 5, 10, 15, 20, or 25. The default value is 0.
PARAMETERS:
When you create a parameter, you specify its name, data type, length, and default value.
To create a parameter:
1. In the Object Navigator, create a parameter.
To create a parameter, select the Parameters node in the Object Navigator and then choose Navigator Create.
2. In the Property Palette, set the desired parameter properties:
In PL/SQL, you can reference and set the values of form parameters using bind variable syntax.
To reference a parameter: Preface the parameter name with the reserved word PARAMETER, as shown in the following examples:
:PARAMETER.parameter_name := 'TOKYO'; OR
:block.item := :PARAMETER.parameter_name;
Canvas Types :
1 Content
2 Stacked
3 Tab
4 Horizontal Toolbar
5 Vertical Toolbar

Visual Attributes :
Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application's interface. Visual attributes can include the following properties:
1 Font properties: Font Name, Font Size, Font Style, Font Width, Font Weight
2 Color and pattern properties: Foreground Color, Background Color, Fill Pattern, Charmode Logical Attribute, White on Black

Every interface object has a Visual Attribute Group property that determines how the object's individual visual attribute settings (Font Size, Foreground Color, etc.) are derived. The Visual Attribute Group property can be set to Default, NULL, or the name of a named visual attribute defined in the same module.
There are several ways to set the visual attributes of objects:
1 In the Property Palette, set the Visual Attribute Group property as desired, then set the individual attributes (Font Name, Foreground Color, etc.) to the desired settings.
2 In the Layout Editor, select an item or a canvas and then choose the desired font, color, and pattern attributes from the Font dialog and Fill Color and Text Color palettes.
3 Define a named visual attribute object with the appropriate font, color, and pattern settings and then apply it to one or more objects in the same module. You can programmatically change an object's named visual attribute setting to change the font, color, and pattern of the object at runtime.
4 Subclass a visual attribute that includes visual attribute properties and then base objects on it that inherit those properties.
5 Create a property class that includes visual attribute properties and then base objects on it that inherit those properties.
About named visual attributes: Setting the Visual Attribute Group property to a named visual attribute defined in the same module specifies that the object should use the attribute settings defined for the named visual attribute. A named visual attribute is a separate object in a form or menu module that defines a collection of visual attribute properties. Once you create a named visual attribute, you can apply it to any object in the same module, much like styles in a word processing program.
Applies to canvas, tab page, item, radio button

Property Classes :
A property class is a named object that contains a list of properties and their settings. Once you create a property class you can base other objects on it. An object based on a property class can inherit the setting of any property in the class that makes sense for that object.
Property class inheritance is an instance of subclassing. Conceptually, you can consider a property class as a universal subclassing parent.
There can be any number of properties in a property class, and the properties in a class can apply to different types of objects. For example, a property class might contain some properties that are common to all types of items, some that apply only to text items, and some that apply only to check boxes.
When you base an object on a property class, you have complete control over which properties the object should inherit from the class, and which should be overridden locally.
Property classes are separate objects, and, as such, can be copied between modules as needed. Perhaps more importantly, property classes can be subclassed in any number of modules.
Menu Item Types
Separator Menu Item :
A separator menu item is displayed as a horizontal line (or other platform-specific visual element), and is useful for visually grouping related items on the same menu. For example, you could use a separator to isolate the radio items of one radio group from other menu items. You must set a separator item's Command Type property to Null. End users cannot select a separator menu item.
Magic Menu Item :
Magic items provide a way to quickly create menu items for standard functions available in most GUI applications. Form Builder provides the following magic item types: About, Copy, Clear, Cut, Paste, Help, Quit, Undo, Window.
Plain Menu Item : Default menu item.
Radio Menu Item
Check Menu Item