Oracle HRMS – Some Important Tables


1. per_all_people_f
This table will provide us the very basic information about the employee. The very data from the first screen we see when we open the ‘People -> Enter and Maintain’ form goes into this table.

2. per_all_assignments_f
This table will store all the information which is been entered in the employee assignment form.

3. per_addresses
This table will store all the information which is been entered in the employee address form.

4. per_pay_proposals
This table will store all the information which is been entered in the employee salary form.

5. per_person_types_tl
This table is used to find the type of the employee. This table is linked with the per_all_people_f with the person_type_id to find out the type of person.

6. per_jobs_tl
This table will contain the various types of JOBS in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct job name from the employee.

7. per_grades_tl
This table will contain the various types of GRADES in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct grade name from the employee.

8. hr_locations_all
This table will contain the various LOCATIONS in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct location name from the employee.

9. pay_all_payrolls_f
This table will contain the various types of PAYROLLS in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct payroll name from the employee.

10. per_pay_bases
This table will contain the various types of PAY BASES in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct pay basis name from the employee.

11. per_assignment_status_types_tl
This table will contain the various types of assignment types in oracle. The assignment types generally would be ‘Active Assignment’ etc.. This table is been linked with the per_all_assignments_f table to retrieve the correct job name from the employee.

12. per_person_type_usages_f
This table will store the correct person type of the particluar employee. We should never depend on the person type present in the per_all_people_f table. Instead we need to link the person_id with this table and get the correct person type.

Oracle Apps 11i – Keyboard Shortcuts


F4 Exit
F5 Clear Field
F6 Clear Record
F7 Clear Block
F8 Clear Form
F11 Query Enter
F12 Count Query
Ctrl + S Save
Ctrl + L List of Values
Ctrl + F11 Query Run
Ctrl + E Edit
Ctrl + Up Delete Record
Ctrl + Down Insert Record
Ctrl + P Print
Ctrl + U Update Record
Ctrl + B Block Menu
Ctrl + K Display list of Keys
Shift + F5 Duplicate Field
Shift + F6 Duplicate Record
Shift + F8 Next Set of Records (Same as Page Down)
Shift + Page Down Next Block
Shift + Tab Previous Field
Shift + Page Up Previous Block
Shift + Ctrl + E Display Error
Page Down Scroll Down (Same as Shift + F8)
Page Up Scroll Up
Tab Next Field
Down Arrow Next Record
Up Arrow Previous Record
Return Return

Oracle Apps – Custom Reports Development & Customization Process


Report Builder Components

Report Builder Components are

1. Data Model
2. Layout Model
3. Object Navigator
4. Report Triggers
5. Parameter Form
6. Program Units
7. Attached Libraries

Data Model
The Report Editor’s Data Model view enables you to define and modify the data model objects for a report.

Data Model Comprises tool Palette which Comes with several Options They are
1. Select
2. Magnify
3. SQL Query
4. Ref Cursor Query
5. Express Query
6. Summary Column
7. Formula Column
8. Placeholder Column
9. Cross Product
10. Data Link

The tool palette is a set of tools you can use to create and manipulate objects. Click a tool to activate it for a single operation, or double-click a tool to “lock” it for multiple operations. The tools in the tool palette vary depending on the Report Editor view.

Select
The Selector toolbar provides tools to help you select or arrange dimension values that meet your criteria. The Selector toolbar is displayed at the top of the Selector dialog box.

Magnify
To magnify a hard-to-see portion of your report or reduce the image to get a sense of your report’s overall layout.

SQL Query
The SQL Query Statement property is a SQL SELECT statement that retrieves data from the database for your report. Enter a valid SELECT statement not to exceed 64K. The upper limit may vary between operating systems All features of the SELECT statement are supported, except the INTO and FOR UPDATE clauses.

In building your SELECT statement, you can do the following:

1. Use the Tables and Columns dialog box
2. Insert comments
3. Insert bind and lexical references
4. Review SQL errors

Required/Optional : Required

Ref Cursor Query
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor.

You base a query on a ref cursor when you want to:

1. more easily administer SQL
2. avoid the use of lexical parameters in your reports
3. share data sources with other applications, such as Form Builder
4. encapsulate logic within a subprogram

Express Query
——–

Summary Column
A summary column performs a computation on another column’s data.
Using the Report Wizard or Data Wizard, you can create the following summaries:
sum, average, count, minimum, maximum, % total.

You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.

Formula Column
A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

Placeholder Column
A placeholder is a column for which you set the data type and value in PL/SQL that you define.

You can set the value of a placeholder column in the following places:
1. The Before Report Trigger, if the placeholder is a report-level column
2. A report-level formula column, if the placeholder is a report-level column
3. A formula in the placeholder’s group or a group below it

Cross Product
The Cross Product Group property is the group that contains the source groups of the Horizontal and Vertical Repeating Frames. The cross product group correlates values between one or more groups to produce the data in the matrix.

Values Enter a valid cross product group name.
Applies to matrices
Required/Optional required
Default blank

Data Link
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group.
When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link’s Property Palette) that will be added to the child query’s SELECT statement at runtime.

Layout Model
The Report Editor’s Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.

This is the view where you can design your Report Layout.

Object Navigator
The Object Navigator provides a hierarchical display of all major objects in a report or template, including attached libraries and external queries. Using this view, you can take such actions as select an object, bring up the Property Palette for an object, edit an object’s PL/SQL, drag and drop PL/SQL program units, and search for an object by name.

Report Triggers
Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database.

To create or modify a report trigger, use Report Triggers in the Object Navigator. Report triggers must explicitly return TRUE or FALSE. Report Builder has five global report triggers which are fired in the following sequence.

1. Before Parameter Form trigger
2. After Parameter Form trigger
3. Before Report trigger
4. Between Pages trigger
5. After Report trigger

1. Before Parameter Form trigger
The Before Parameter Form trigger fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.
Definition Level Report

On Failure
Displays an error message and then returns to the place from which you ran the report.

2. After Parameter Form trigger
The After Parameter Form trigger fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.

Definition Level Report

On Failure
Returns to the Runtime Parameter Form. If the Form is suppressed, then returns to place from which you ran the report.

3. Before Report trigger
The Before Report trigger fires before the report is executed but after queries is parsed and data is fetched.
Definition Level Report

On Failure
Displays an error message and then returns to the place from which you ran the report.

4. Between Pages trigger
Between Pages trigger fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Runtime Previewer or Live Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.
Definition Level report
On Failure
Displays an error message when you try to go to the page for which the trigger returned FALSE. The Between Pages trigger does not fire before the first page.

5. After Report trigger
The After Report trigger fires after you exit the Runtime Previewer, or after report output is sent to a specified destination, such as a file, a printer, or a mailid. This trigger can be used to clean up any initial processing that was done, such as deleting tables.
Note, however, that this trigger always fires, whether or not your report completed successfully.
Definition Level Report

On Failure
Does not affect formatting because the report is done. You can raise a message, though, to indicate that the report did not run correctly

Note: The After-Report trigger does not fire when you are in the Live Previewer.

Layout Model Properties

In this Layout model, we have several Options

Frames:
Frames are used to surround other objects and protect them from being overwritten or pushed by other objects.
For example, a frame might be used to surround all objects owned by a group, to surround column headings, or to surround summaries.
When you default the layout for a report, Report Builder creates frames around report objects as needed; you can also create a frame manually in the Layout Model view.

Repeating Frames
Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group.
When you default the layout for a report, Report Builder creates repeating frames around fields as needed; you can also create a repeating frame manually in the Layout Model view.

Text
This allows to embed the text in the layout view.

Field
A field is the layout container for each column in the layout. A field is owned by the object surrounding it, typically a repeating frame, unless the field is a summary (in which case it is owned by a frame).

Anchor
Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. The end of the anchor with a symbol on it is attached to the parent object…

Parameter Form
The Report Editor’s Parameter Form view enables you to create a Runtime Parameter Form for your report. You can select pre-defined system parameters for your form using the Parameter Form Builder, or you can create your own.

Program Units
Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.

Stored program units (also known as stored subprograms) can be compiled separately and stored permanently in an Oracle database, ready to be executed. Once compiled and stored in the data dictionary, they are schema objects, which can be referenced by any number of applications connected to that database.
Stored program units offer higher productivity, better performance, memory savings, application integrity, and tighter security. For example, by designing applications around a library of stored procedures and functions, you can avoid redundant coding and increase your productivity.

Stored program units are stored in parsed, compiled form. So, when called, they are loaded and passed to the PL/SQL engine immediately. Also, they take advantage of shared memory. So, only one copy of a program unit need be loaded into memory for execution by multiple users.

Attached Libraries
Attached libraries are external PL/SQL libraries that you have associated with a report or another external library. When an external library is attached, you can reference its packages, functions, and procedures from within your report. For example, if you attached an external library name MYLIB to your report and it contained a function named ADDXY, then you could reference ADDXY from any PL/SQL in the report.

Report Customizations
You can customize business intelligence reports by both content and layout. You also have the option to add columns, filter the data and sort the resulting information. For example, you can add another column for a running total, or insert grand totals and subtotals as you need them. In layout, you can change column headings, report titles and the order of columns. You can also display the data in graphical form, such as bar graphs or pie charts.

Customization is enhancement of new features to the Existing Report Content and Layout depending on the new requirement.

Few Easy and Common Oracle Apps Questions and Answers


What is Set of Books ? What are the four conditions when you change your SOBs?

Chart of Accounts, Currency & Calendar
It is similar to the bank passbook, used to record all the financial transactions. There could be one primary set of books and many reporting set of books. Defining COA, currency and calendar are pre-requisite to define the SOB.

What is an Invoice? How many types of invoices ar…
There are 9 types of Invoices in AP: Standard, Credit Memo, Debit Memo, Prepayment, Withholding Tax, Mixed Invoice, Expense Report, PO default and Quick Match.
There are 6 types of Invoice in AR : Invoice, Credit Memo, Debit Memo, Chargeback, Deposit and Guarantee.

What is the difference between data conversion and data migration?
Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to ur our the target module using the standard import program.

Set ups need to approve invoice in AP
For approving an invoice in AP we have to raise one invoice for raising an invoice we have some mandatory fields like supplier, supplier number, Payment terms, distribution sets, date, payment method, bank, payment document etc. After defining all the above we can raise a invoice, There’s no need of defining the approval group it s not a mandatory,

What is a profile option? What are the types?
The profile options are available to every product in Oracle Applications. For each profile option, we give a brief overview of how Oracle Application Object Library uses the profile’s setting.
1) User Level
2) Responsibility Level
3) Application Level
4) Site Level.

What are _ALL tables in Oracle Apps?
_ALL tables in oracle applications give the info about multiple organizations info about these tables.

What is descriptive flex field and what is the use
Descriptive Flexfileds r used to add additional informations, and these values r stored to the column attributes. Go to sysadmin application-flex fields-descriptive-segments
What is a FlexField? What are Descriptive and Key…
A flexfield is made up of sub-fields or segments.. A flexfield appers on ur form as a pop-up window that contains a prompt for each segment. Each segment has a name and a set of valid values..
Two types of Flex field..
Key Flexfield: Key flexfield are flexible enough to let any organization use the code scheme they want wothout programming.Key flexfield can be used to represent codes that is made up of meaningful segment to identify GL a/c Part no. and other business entities..Oracle app store these codes in key flexfields..
Descriptive Flexfield: They provide customizable “expansion space” on ur forms. You can use desc flexfields to tract additional information important and unique to ur business that would not otherwise be captured by the form.

What Credit memo / Debit Memo
Both Credit and Debit Memo are used for adjusting the suppliers balance and both is a negative amount. Debit Memo is created by you and send to the supplier and credit memo is recieved from the supplier and record it.

What is a request Sets? how to create a request Se..
Request set is a group of requests.It is made to perform the request in a certain sequence.Request se can be created from System administrator responsibility.

What are the types of Concurrent Managers?Ca…
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.
We cannot delete a concurrent manager… but we can disable it… but it’s not recommended.

What is Multiple Organization technical architecture?
Multi Organization :Using a single installation of any oracle application product , to support any number of an organization even it has different Set Of Books Bussiness Level(It Secures Human TransactionInformation) Set Of Books(It secures all Transaction Information In Oracle Genral Ledger) Leagel Entry(All leagal Information in the Organization) Operating Unit(It Uses Order management, Cash managment,AR,AP…it may be Sales Office , Division,Department) Inventry Organization(Inventry Details)
before multi org existed, we could have only one bussiness group,set of books, legal entity, operating unit in one installation of oracle E-bussiness suite, and now with the multi organisation structure in place, we can have multiple bussiness groups, set of books, legal entity and operating unit and the best part is once these are set up intercompany accounting is automatically taken care of, say for example i have Two inventory organisation, and these use a common set of books(to start with) , now if we have sales order on one inventory organisation A1 and if that item is not available in A1, and we have inventory for the item in Inventory org A2, we need to take the order in A1 as internal order and run the order import concurrent program and for the item in inventory A1(org assignment) we need to set up the source(purchase tab) as inventory organisation A2,and also specify shipping network between A1 and A2, and once we have run the order import program and Oracle applications now imports the order to organisation A2, with the ship to address as A1 location. and after you perform pick release and pick confirm process, and run auto invoice in A2, it automatically sends invoice to inventory organisation A1 and now we can receive the item in Inventory organisation A1 from organisation A2.
and all transactions are taken care of, courtesy multi organisation structure.

What are different period types ?
Year Quarter Month Week

What are the different types of files used in SQL
Different types of files are Data File,Control File,Discard File,Bad file

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

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

Difference between Conversions and Interfaces.
Conversion is to bring the data from other (non-OraApps) system to Oracle-Application system. This is one time activity (bring account details, transactions, orders, receipts and so on). So conversion itself is a project.
Interfaces are Concurrent PRograms/Program sets (pl/sql, pro*C, unix scripts, executables) . Interfaces are basically of two types (Oracle supplied (vanila programs eg:Autoinvoice in AR) and created by developer).
Hope this clarifies the concepts.
Conversion means one time activityinterface means periodic activityexample:- to transfer the data old version to new version it is called conversion to transfer the data from staging table to interface table it is called interface , it is process on every day or every hour ……..

Which module is not a multiorg?
General Ledger and CRM Foundation Modules

What are the types of Concurrent Managers?Can we delete a Concurrent Manager?
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.
We cannot delete a concurrent manager… but we can disable it… but it’s not recommended.

Trading community Architecture(TCA)
ICA (Internet Computing Architecture)

What are AP setup steps ?
setup—->suppliers….>invoices..>payments…..>reports……>periods…..>transfer to GL

What does US mean in appl_top/au/11.5.0/reports/US?
US is the language directory specifying that the source files to be placed under this dir is for English/American Language
This is the standard of apps directory structure that for very language you implement oracle apps there should be a language specific folder

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

What are the Back ground processes in Oracle and what are they.
There are basically 9 Processes.They do the house keeping activities for the Oracle and are common in any system.The various background processes in oracle are:
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is commited.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup.This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor peforms process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the
most recent checkpoint
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.

How many types of Sql Statements are there in Oracle
There are basically 6 types of sql statments.They are
a) Data Defination Language(DDL) :: The DDL statments define and maintain objects and drop objects.
b) Data Manipulation Language(DML) :: The DML statments manipulate database data.
c) Transaction Control Statements :: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g, Alter Statements,Set Role
e) System Control Statements :: Change Properties of Oracle Instance .e.g, Alter System
f) Embedded Sql :: Incorporate DDL,DML and T.C.S in Programming Language.e.g, Using the Sql Statements in languages such as ‘C’, Open,Fetch, execute and close

What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly commited or rolled back.

Key Words Used in Oracle
The Key words that are used in Oracle are ::
a) Commiting :: A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transactino into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explcit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache :: Databese Buffer of SGA stores the most recently used blocks of datatbase data.The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a ‘thread of control’ or mechansim in Operating System that executes series of steps.

What are Procedure,functions and Packages
* Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
* Procedures do not Return values while Functions return one One Value
*Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

What are Database Triggers and Stored Procedures
Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
e.g:: operations insert,update ,delete 3
before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures :: Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules :: The Third Intigrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

What are the Various Master and Detail Relation ships.
The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is exisiting
b) Isolated :: The Master can be deleted when the child is exisiting
c) Cascading :: The child gets deleted when the Master is deleted.

What are the Various Block Coordination Properties
The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deffered with No Auto Query
The operator must navigate to the detail block and explicitly execute a query

What are the Different Optimisation Techniques
The Various Optimisation techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint ::
set_item_property(‘DeptBlock’,OPTIMIZER_HINT,’FIRST_ROWS’);
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No

Restrict ‘&’ in Sql*Plus
sql> set define off
This will restrict the use of ‘&’ in the sql*plus for that particular session