Basics

Oracle Application:

1. Middleware:  Middleware provides a link between different software applications.  Its a software that lies between OS and applications.

1. RICE stands for Reports,Interfaces,Customization and extensions.

1. Responsibility in oracle applications is a level of authority.  Its a collection of menus and data-groups.

1. Number of responsibility in a module is unlimited. and it can be assigned to multiple users.

1. Components of a responsibility are menus, request group and data group. 

1. Responsibility defines user access level.

Difference Between Oracle apps 11i/R12
Summary of Changes
Changes in Detail

What’s new in Oracle Apps R12
Key points to Note:

1. Oracle Applications Release 12 is the latest release in the chain of E-Business Suite Releases by Oracle.

1. This release came up with the new file system model

1. Autoconfig will not write anything in APPL_TOP, COMMON_TOP area in R12.

1. All instance specific configurations, log files are written in INST_TOP area. This home provides the ability to share Applications and technology stack.

R12 new features

1. Applications Server 9i is replaced by 10g (10.1.3.X)

1. Forms and Reports Version 6i (8.0.6) are replaced by Form/Reports Version 10g i.e. 10.1.2.X

1. mod_jserv is replaced by oc4j

1. Java/ JDK version 1.3.X or 1.4.X will be replaced by JDK 1.5.X

• Database (RDBMS_ORACLE_HOME) - 10.2.0.2

• FORMS ORACLE_HOME (8.0.6 ORACLE HOME equivalence) - 10.1.2

• OC4J_ORACLE_HOME (iAS ORACLE_HOME equivalence) - 10.1.3

File system level changes

• A new top INSTANCE_TOP is introduced in Release 12 for configuration and log files along with the other TOP's in existing in 11i.

• All instance specific configurations, log files are written in INST_TOP area. 

• This home provides the ability to share Applications and technology stack.

What is INSTANCE TOP 

• Instance home is the top-level directory for an applications instance which is known as Instance Home and is denoted with the environment variable $INST_TOP. 

• This contains all the config files, log files, SSL certificates etc.

Advantages of new INSTANCE HOME

• The additional Instance Home makes the middle tier easier to manage and organized since the data is kept separate from the config files.

• The Instance Home also has the ability to share the Applications and Technology stack code across multiple instances.

• Another advantage of the Instance Home is that the autoconfig writes only in INST_TOP so APPL_TOP and ORACLE_HOME can also be made read only file system if required.

SQL
Using the [charlist] Wildcard
Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%'
The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE '[!bsp]%'
The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

Joins:  Join keyword in SQL is used to query data from two or more tables based on the relationship between certain columns in these tables.Look at the "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.
Joins:  Join keyword in SQL is used to query data from two or more tables based on the relationship between certain columns in these tables.Look at the "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.
Types of Joins:  
* Join
* Left Join
* Right Join
* Full Join.

Join:  A.k.A Inner join return rows when there is at-least one match in both the tables.

SQL INNER JOIN Example
The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons with any orders.
We use the following SELECT statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

Left Join: A.k.a left outer join return rows from left table even if there is no match in the right table.

SQL LEFT JOIN Example
The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove

Right Join:  A.k.a right outer join return rows from right table even if there is no match in the left table.

SQL RIGHT JOIN Example
The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the orders with containing persons - if any, from the tables above.
We use the following SELECT statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
		34764

Full Join: Returns row when there is match in one of the tables

SQL FULL JOIN Example
The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons and their orders, and all the orders with their persons.
We use the following SELECT statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove
		34764

Union operator:  Union operator combines the result of two or more sql queries.

SQL UNION Example
Look at the following tables:
"Employees_Norway":

E_ID	E_Name
01	Hansen, Ola
02	Svendson, Tove
03	Svendson, Stephen
04	Pettersen, Kari

"Employees_USA":

E_ID	E_Name
01	Turner, Sally
02	Kent, Clark
03	Svendson, Stephen
04	Scott, Stephen

Now we want to list all the different employees in Norway and USA.
We use the following SELECT statement:
SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA
The result-set will look like this:

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

Union operator will retrieve distinct values by default.  To retrieve duplicate values as well we can use union all operator.

SQL UNION ALL Example
Now we want to list all employees in Norway and USA:
SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA
Result

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen

SQL Constraints: Constraints are used to limit the type of data that goes into a table.

Constraints can be added when the table is created or using Alter Table command.
Types of constraints:
* Not Null :  Column cannot contain null values.
* Unique:  Column can contain only unique values.  Multiple unique key constraints are allowed on a table but only a single Primary Key constraint.
* Primary Key: Column should contain unique Not Null values.  There can be only one Primary key constraint on a table.
* Foreign Key: Prevent actions that would destroy the links between tables.  Foreign key in one table is a primary key in another table.
* Check:  Check constraint is used to limit the range of values that can go into that column.
* Default.  Constraint to add default values into a column.  This value is used when no other value is added to that column.

VIEWS:     View is a window to the real time DB tables.  Views are created using SQL queries querying DB table.  Every time the view is accessed, SQL associated with the view is run by the DB engine.

SQL Updating a View
You can update a view by using the following syntax:
SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

---

SQL Dropping a View
You can delete a view with the DROP VIEW command.
SQL DROP VIEW Syntax
DROP VIEW view_name
Difference Between a view and materialized view:

View	Materialized View
Since a view is based on a SQL query, every time the view is accessed, the underlying SQL is run.	Materialized view is based on a query but the query result is stored in the DB.  When materialized view is accessed, the rows are retrieved from DB and not from the table.
View does not occupy any space but for its definition.	Materialized view occupies space.  Its stored in the same way as a table and can be indexed and partitioned
View always reflects the latest data from the table.	Materialized view does not reflect real-time data.  Its freshness depends on the refresh option specified during its creation.
Views can be used when we want to restrict the access to the data or we want to hide a complex sql query	Materialized view can be used when we want faster response. Ex: data warehousing or BI

Query to find nth highest salary:
select distinct a.sal from emp a
where n = (select count (distinct b.sal) from emp b
                                 where a.sal =b.sal);
Query to delete duplicates:
delete from table a where rowid in (select min(rowid) from table b where a.key_col = b.key_col)

Table Partition:
The process of decomposing a huge table into smaller and manageable pieces called partitions.
Tables can be partitioned into up to 64,000 separate partitions. Any table can be partitioned except those tables containing columns with LONG orLONG RAW datatypes. You can, however, use tables containing columns with CLOB or BLOB datatypes.
Types of Partitions:
1. Range Partitions.
2. List Partitions.
3. Hash Partitions.
4. Composite Partitions.
1. Range Partitioning Example
CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);
2. List Partitioning Example
CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT)
);
3. Hash Partitioning Example

CREATE TABLE sales_hash
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
week_no       NUMBER(2)) 
PARTITION BY HASH(salesman_id) 
PARTITIONS 4 
STORE IN (data1, data2, data3, data4);

CREATE TABLE sales_hash
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
week_no NUMBER(2))
PARTITION BY HASH(salesman_id)
PARTITIONS 4
STORE IN (data1, data2, data3, data4);

3. Composite Partitioning Range-Hash Example

CREATE TABLE sales_composite 
(salesman_id  NUMBER(5), 
 salesman_name VARCHAR2(30), 
 sales_amount  NUMBER(10), 
 sales_date    DATE)
PARTITION BY RANGE(sales_date) 
SUBPARTITION BY HASH(salesman_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE data1,
SUBPARTITION sp2 TABLESPACE data2,
SUBPARTITION sp3 TABLESPACE data3,
SUBPARTITION sp4 TABLESPACE data4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))
 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))
 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))
 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
 PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));

3. Composite Partitioning Range-Hash Example
CREATE TABLE sales_composite
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
SUBPARTITION BY HASH(salesman_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE data1,
SUBPARTITION sp2 TABLESPACE data2,
SUBPARTITION sp3 TABLESPACE data3,
SUBPARTITION sp4 TABLESPACE data4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));

DATABASE:    
RDBMS is a database management system based on a relational model.  Relational model represents data in terms of tables which are related to each other.
Normalization:  It is a process of organizing fields and tables in a relational database to eliminate/reduce redundancy and dependency.
1st Normal form:
identify and separate group of related data into different tables and identify the PK for each table.
2nd Normal form:
A table is in 2nd normal form if it is in 1st normal form and all the non-primary columns of the table are completely dependent on the primary key column.
3rd Normal form :
A table is in third normal form if it is in 1NF and 2NF and remove any column which is not fully dependent on the PRIMARY KEY.




PL/SQL: 

1. Rowid is a unique 18 character hexadecimal number generally used for searching.  This represents physical address of the row in the DB. 

1. ROWNUM  is unique integer number generally used for sorting.  It represents the order in which the rows are retrieved from DB.

1. Standalone procedures/functions are stored in the DB as database objects and they can be reused across.  Application procedures/functions are specific to applications.

1. ROWNUM, ROWID, NEXTVAL and CURRVAL are all called as pseudo-columns.

1. Autonomous transactions are subprograms marked with PRAGMA.  They can perform SQL operation, commit/rollback without committing or rolling back the main transaction/program.

1. PRAGMA signifies that the transaction is a compiler directive.  Compiler directive means statements processed at compile time and not at run time.  These statements pass information to the compiler.

1. Autonomous PRAGMA cannot be applied to the entire package.  It can be used only for subprograms within a package.

1. Autonomous triggers can have commit/rollback unlike normal triggers.  They can also have DDL statements like create, drop etc. through execute immediate statements.

1. Autonomous transactions are visible to the main transaction once it commits/rolls back.  But if we want to avoid this, we can set the isolation level of the main transaction to SERIALIZABLE.

Top 10 performance tuning tips for relational databases
1. Database statistics:
DB statistics help SQL optimizer to choose the least expensive path to satisfy a query.

Oracle:

ANALYZE command or DBMS_UTILITY package

2. Create optimized index.
No index will degrade performance of your SELECT statements and too many indexes will slow down your DML (INSERT, UPDATE, and DELETE) queries.

3. Predetermine the expected growth:
Indexes will have negative impact on DML operations.  When new values are added to the indexed columns, DB has to reorganized the storage for new rows, this process will take additional toll on the DML operations.  But if we expect the table to have frequent DML operations then we can specify expected growth for an index using PCTFREE.

Oracle:	PCTFREE - Percent Free

4.  Use optimizer hints in the select query.

SELECT /*+ INDEX(patients sex_index) use sex_index because there are few
male patients */ name, height, weight
FROM patients
WHERE sex = 'm';

5. Explain Plan: Explain plan is created by the optimizer and its useful in fine tuning the SQL queries

Oracle:

EXPLAIN PLAN FOR >Your query<

6. Avoid foreign key constraints:
Foreign keys constraints ensure data integrity at the cost of performance.

7. Two heads are better than one.
Hard disk I/O are among the slowest resource on a computer.  Split the database on two or more physical hard drives.

8.  Avoid functions on the right hand side of the operator.
Consider this
select *
from Customer 
where YEAR(AccountCreatedOn) == 2005 
and  MONTH(AccountCreatedOn) = 6 
Though AccountCreatedOn column has an index it will not be used when a function is used on RHS.
9. Limit the data in the SQL queries by writing Properly tuned SQL statements.
10. Drop the indexes before load data.


Collections In PL/SQL
Collections are ordered list of elements of same type.  Each element has a unique subscript that determines its position in the collection
Types of collections:
1. Index by Table:  Associative Array or PL/SQL Table are a set of key and value pair.  Each key is unique and used to locate the corresponding value in the array.  Key can be a sting or integer.
DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;
which VARCHAR2(64)
BEGIN
country_population('Greenland') := 100000;
country_population('Iceland') := 750000;
howmany := country_population('Greenland');
continent_population('Australia') := 30000000;
continent_population('Antarctica') := 1000; -- Creates new entry
continent_population('Antarctica') := 1001; -- Replaces previous
value
which := continent_population.FIRST; -- Returns 'Antarctica'
-- as that comes first alphabetically.
which := continent_population.LAST; -- Returns 'Australia'
howmany := continent_population(continent_population.LAST);
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
END;
/


2. Nested table:
Unbounded one dimensional arrays.  They are similar to a one column table.




3. VARRAYs
They have a fixed upper bound.

	Index-By Table	Nested Table	VARRAY
Dimensionality	Single	Single	Single
Usable in SQL?	No	Yes	Yes
Usable as column datatype in a table?	No	Yes; data stored "out of line" (in separate table)	Yes; data stored "in line" (in same table)
Uninitialized state	Empty (cannot be null); elements undefined	Atomically null; illegal to reference elements	Atomically null; illegal to reference elements
Initialization	Automatic, when declared	Via constructor, fetch, assignment	Via constructor, fetch, assignment
In PL/SQL, elements referenced via	BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647)	Positive integer between 1 and 2,147,483,647	Positive integer between 1 and 2,147,483,647
Sparse?	Yes	Initially, no; after deletions, yes	No
Bounded?	No	Can be extended	Yes
Can assign value to any element at any time?	Yes	No; may need to EXTEND first	No; may need to EXTEND first, and cannot EXTEND past upper bound
Means of extending	Assign value to element with a new subscript	Use built-in EXTEND procedure (or TRIM to condense), with no predefined maximum	EXTEND (or TRIM), but only up to declared maximum size
Can be compared for equality?	No	No	No
Retains ordering and subscripts when stored in and retrieved fromdatabase?	N/A	No	Yes

Example: Declaring Nested Tables, Varrays, and Associative Arrays
DECLARE
TYPE nested_type IS TABLE OF VARCHAR2(20);
TYPE varray_type IS VARRAY(50) OF INTEGER;
TYPE associative_array_type IS TABLE OF NUMBER
INDEXED BY BINARY_INTEGER;
v1 nested_type;
v2 varray_type;
v3 associative_array_type;

Miscellaneous:

What is metadata?
The term Metadata is defined as " data about data". It describes the content, quality, condition, and other characteristics about data written in a standard FGDC format. Metadata helps a person to locate and understand data.
Metadata provides data history. It describes the Who, What, Where, Why and How of the data.

-Who created and maintains the data?
-What is the content and structure of the data?
-When was the data collected? Published?
-Where is the geographic location? Storage location?
-Why were the data created?
-How were the data produced? Processed? Raw or modeled data? 
Find information describing:
Data Currency
Data Utility
Data Processing Steps
Status and Development of Projects
Estimated Development Costs
Source File Availability
Research Benefits: compare, verify and query data

Software development process
A software developer at work
Activities and steps
Requirements   Specification   Architecture Construction   Design   Testing   Debugging Deployment   Maintenance
Methodologies
Waterfall   Prototype model   Incremental Iterative   V-Model   Spiral   Scrum Cleanroom   RAD   DSDM   RUP   XP   Agile Lean   Dual Vee Model   TDD   FDD
Supporting disciplines
Configuration management   Documentation Quality assurance (SQA) Project management   User experience design
Tools
Compiler   Debugger   Profiler   GUI designer IDE   Build automation

ADF:
ADF stands for application development framework.

Business components:  Business components are a set of APIs used to govern the interaction between application and data stored in the database, providing validations, specific services and other business logic.
Java: Java is an object oriented programming language designed to build applications in heterogeneous, distributed environments.

OAF:

Oracle application framework is a framework developed by ORACLE Corp.  for application development within E-business suite.

OAF is available to customers for customization, personalization and custom application development.
OAF is a MVC framework built using J2EE technologies.
Advantages of OAF:
1. Enterprise-Grade performance and scalability.
2. Improved end-user productivity.
3. Highly extensible architecture.
4. Browser look and feel for all the applications.
5. Open industry standards such as HTML, XML, SQL, java, JSP and web services.

OA framework architecture is based on MVC design pattern.

Components of MVC Architecture:
Model:
* Data
* Implemented using BC4J- Business components for java.
View:
*User interface
* Implemented using oracle UIX - User interface XML

Controller:
*Code
* User actions are handled by OA controller.
(ex: clicking of a submit button)


Interaction Between Model, View and Controller:



AM : Application Module.
EO : Entity Object.
VO: View Object.

EO: Entity Object:
*Entity objects are based on database tables or data source
* Entity objects contains attributes which represent database columns.
* All DML operations insert/update/delete are process through EOs to database.

VO: View Objects:
Types of VOs
* Query Based VOs
* Entity based VOs
*VO represents a query result.
* VO are used for filtering, projecting, joining and sorting business data.
* VOs can be based on many number of EOs and provide access to EOs.

Application Module:
* Container of VOs
* Every page must have a root application module.
* Application module handles transactions.




Controller (CO) in MVC Architecture:
* Responds to user action and directs application flow.
* Model objects EOs and VOs cannot be accessed directly from Controller, except AM.
*Contains methods such as
ProcessRequest:  Fires when OAF page loads for the first time.
ProcessFormRequest:  When user submits a page (Ex:  when submit button is clicked)


MVC Architecture:




Profile Options:
* FND: Personalization Region Link Enabled
Enables the "Personalize Region" links on a page if the
Personalize Self-Service Definition.
* FND: Diagnostics
Renders the "About this page" link at the bottom of every OA
Framework page.

Component	Release 11i	Release 12
Database	9.2	10.2
Developer	6i	10i
Application Server	1.0	10.1
Client Plug-in	Jinitiator	SUN JRE
Java Containers	Jserv	OC4J

APPMGR
	OLD	NEW
APPL_TOP	$HOME/appl	$HOME/apps/apps_st/appl
COMMON_TOP	$HOME/comn	$HOME/apps/apps_st/comn
ORACLE_HOME	$HOME/ora/8.0.6	$HOME/apps/tech_st/10.1.2
IAS_ORACLE_HOME	$HOME/ora/iAS	$HOME/apps/tech_st/10.1.3
ORACLE
	OLD	NEW
ORACLE_HOME	$HOME/db/10.2.0	$HOME/db/tech_st/10.2.0
ORADATA	$HOME/data	$HOME/db/apps_st/data
INST_TOP (New Addition)
	OLD	NEW
INST_TOP	NA	$HOME/inst/apps/

Techstack Components Changes

First the basic difference between personalization and customization !
Personalization is oracle provided flexibility to change standard behavior as personal need. using personalization we do not require to write any custom code.whileCustomization is process to write custom code to fulfill our need.

Below are the 3 basic components of OAF pages.
1) View Object (VO) ,
2) Controler (CO) and
3) Application Module (AM)  , components in  MVC architecture.     

in OAF Customization we need to write custom code for any of the above 3 based on the requirement.

Now checkout the existing CO or AM name (From About this page at the left below of each html page) and download the class file then ftp to your local system.

(assume QuickUpdateCO.java is standard file) 

use any decompiler and get the source java code and follow below steps -
1) Rename the standard CO/AM (QuickUpdateCO.java) with custom name XXXXXQuickUpdateCO.java
2) extend the standard CO/AM
3) import the standard package
4) define constructor for the custom program
5) define the package name (location where code resides) 

package xxxxx.oracle.apps.pos.supplier.webui;
import oracle.apps.pos,suppliers.webui.QuickUpdateCO.java;
XXXXXQuickUpdateCO.java extends QuickUpdateCO.java;
{ 
XXXXXQuickUpdateCO{};
ProcessRequest(){
super();
};
ProcessFormRequest{
super();
};
}  

6) Write code in processRequest (if customization need to be invoked on page display) or in processFormRequest (if customization required on any event on the page).
7)FTP the new java code and compile using javac XXXXXQuickUpdateCO.java
8)Place the class file at xxxxx.oracle.apps.pos.supplier.webui (package defined at top of CO)

CO -:
a) Click on personalization link
b) Click on pencil button against page layout property
c) Give the new controller name along with package name at the desired level  
d) Tab out and apply

VO/AM-:
Oracle does not provide option to extend the VO and AM using personalization.Though there is concept of SUBSTITUTION which is being used to extend the standard VO and AM.
You can use JDeveloper to generate the substitution and then import substitution using  jpximport .