A typical database schema contains dozens of tables, each with several columns of various data types. We develop applications (forms, reports, menus, etc.) to make the job of manipulating data in those tables easier for users. In Oracle Forms, a form (or data entry form) acts like a window into the database schema. An individual focuses the attention of the user to one or a few of the tables at a time. In addition, a form can give prompts so the user knows what kind of input is expected and how data is to be entered and manipulated.
By default, every form in Oracle Forms has the capability to query existing data in a table, modify existing data and add new data (records) to the table. A form is built up using one or more data blocks that correspond to tables in the database. Fields within the data block correspond to columns in the database table. A data block is similar to a DataWindow object in PowerBuilder. In the following figure, a typical form is shown.
Oracle Developer9i provides various Graphical User Interface (GUI) tools to create functional applications from database definitions, without writing any code. You can use these tools to create, manage, and deploy integrated applications.
Some tools provided by the Oracle Developer9i are:

  1.       The Forms Builder
  2.       The Reports Builder
  3.       The Discoverer Administrator
  4.       The Discoverer Desktop

The Forms Builder
You can use the Forms Builder to easily and quickly construct multi-lingual and interactive database forms, charts, and business logic. Applications developed using Oracle Forms Builder can be instantly deployed over the Internet using the Oracle Internet Application Server.
Some features of the Forms Builder are:
Legacy Application Integration: Enables migration of existing client-server applications to the Internet using rich Java user interfaces.
Developer Productivity: Develop applications quickly with:
            Easy-to-use re-entrant wizards to create blocks and relations.
            Property pallets to set visual attributes.
            Shared libraries and templates to enforce standards.
            Easy PL/SQL scripting for triggers and business logic.
            Debugging across application and database logic.
            PL/SQL code exchange between application and database.
            Live previewers to test your application and version control and impact analysis.
Oracle8i Integration: Enables you to leverage Oracle8i features, such as transaction management, resource management, advanced queuing, and parallel server to share application resources.
Scalability: Enables you to deliver scalable applications that perform across any network inside or outside firewall.
Manageability: Enables you to manage and configure multiple application servers from a single location with Oracle Enterprise Manager.
Internationalization: Enables you to develop applications that can be translated easily with multi-language support.
Enterprise Application Development: Supports application development through a built-in forms server, which receives CGI requests from thin clients, such as browsers and serve the appropriate request.
Interoperability: Is completely interoperable with Java and contains Enterprise Java Beans, Servlets and hosts smart applets. It is also compatible with object technologies, such as CORBA and COM+.
Reduces downtime: Supports previously offline DBA routines, such as database backup, restore, DDL statements through application routines. This reduces the downtime for the Oracle Database Server.
Form Types
There are four main types of forms that can be designed

Form Personalization – How to Change Field Name

Form Personalization feature allows us to alter the behavior of Forms-based screens, including changing properties, displaying messages etc.
For a single form-function
(a form running in a particular context based on parameters passed to it defined at function level) we can specify one or more Rules. Each Rule consists of an Event, an optional Condition, the Scope for which it applies, and one or more Actions to perform.

Here we will discuss about how can we change the field display name.

Basic Requirement

Our basic requirement is to change name the ‘Latest Start Date field to ‘ABCD‘ in people Screen. Remember this name change should only be applicable for persons who are using ‘UK HRMS Manager’.

Solution ApproachForm Personalization feature is declarative and any personalization to form may interfere with base code of a  form.
before we start personalization please ensure that the following security profiles are properly set   1) FND_HIDE_DIAGNOSTICS (Hide Diagnostics menu entry)
  2) DIAGNOSTICS (Utilities:Diagnostics)


a) Now open the people & Assignment form from the navigator menu. Click on the ‘Latest Start Date field’. Now go to  Help >> Diagnostics >>  Properties >>  Item.
    It will display the ‘Object Properties’ window. Note Down the  Object2 value (HIRE_DATE) which is nothing but the name of the item.

b) Now to personalize the screen, go to  Help >> Diagnostics >> Custom Code >> Personalize
 Set the following values

Condition TabSeq:- Next highest available number.
Description:-
Test Personalization
Level:-
Function
Trigger Event:- WHEN-NEW-FORM-INSTANCE
Trigger Object**:-
Condition:-
Processing Mode:- Both
Scope:- Site

** Depending on the Trigger Event, this field may be Disabled, or Enabled and Required in which case it will validate against a List of Values. For example, if Trigger Event WHEN-NEW-ITEM-INSTANCE is selected, then we must enter a specific block.field for that trigger to be processed.

Action Tab
Seq:- 10
Type:- Property
Description:-
Language***:- All
Object Type:- Item
Target Object:- PERSON.HIRE_DATE (Search with string that we copied from step a)
Property Name:- PROMPT_TEXT
Value:- ABCD

 ***  Select All’ to have the action processed for any language, or select a specific language.Generally text-related personalizations are applied for a specific
       language
.

c) Validate the design and click on Apply Now.

Note:- 1) Since we have selected processing mode as ‘Both’, hence the field name ‘ABCD’ will appear under both the condition ‘New form is open’ and
             ‘Enter-Query Mode’.

                If we select the processing mode as ‘Only in Enter-Query Mode’, then we will see the original name of the field while opening the form.Where as  if we
            query the form it will execute the trigger event and change the name of the field.

      2) Each Rule consists of one or more Scope rows, and one or more Actions. If a Rule has no Scope rows or Action rows, it is not processed. Note that
         upon saving a Rule, if no Scope rows have been entered the form will automatically create a row at the Site level. If any scope matches the current
         runtime context then the Rule will be processed.

Custom Library (custom.pll) allows to extend/customize Oracle Applications form(Oracle Form) without changing or modifying Oracle Applications code. Examples may include enforcing a new business rule, opening a form using zoom etc. Most of the things that we can do using custom.pll, we can achieve that using Forms Personalization. Since Custom.pll takes the full advantage of PL/SQL so it is having an edge over Forms Personalization for complex customizations.
CUSTOM.pll is used to add extensions to Oracle’s form Functionality. Some of the common scenarios where CUSTOM.pll can be used are:-
1. Enabling/Disabling the fields
2. Changing the List of Values in a LOV field at runtime
3. Defaulting values
4. Additional record level validations
5. Navigation to other screens
6. Enabling Special Menu

Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.
How to add code to this?
Open this pll using the Form builder and make changes to the program units.

How to compile this PLL?
 Once you make changes you need to compile the pll. Use the F60gen to compile it
f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special
While writing code inside custom.pll we should consider following things:
1. We should not run any SQL statement inside this, we can use record group.
2. We should not perform any DML operations, instead we should call database procedure and functions for the same.

For following Events call will go to CUSTOM Library:
 
WHEN–FORM–NAVIGATE
WHEN–NEW–FORM–INSTANCE
WHEN–NEW–BLOCK–INSTANCE
WHEN–NEW–RECORD–INSTANCE
WHEN–NEW–ITEM–INSTANCE
WHEN–VALIDATE–RECORD
SPECIALn (where n is a number between 1 and 45)
ZOOM
EXPORT
KEY–Fn (where n is a number between 1-8)

Custom Library contains Custom Package which is having two Functions and one procedure.
1] ZOOM_AVAILABLE:
This function allows you to specify if zooms exist for the current context. If zooms are available for this block, then return TRUE else return FALSE. This routine is called on a per-block basis within every Applications form from the WHEN-NEW-BLOCK-INSTANCE trigger. Therefore, any code that will enable Zoom must test the current form and block from which the call is being made. By default this routine must return FALSE.

Sample code1:

function zoom_available return Boolean is
form_name  varchar2(30) := name_in(‘system.current_form’);
block_name varchar2(30) := name_in(‘system.cursor_block’);
begin
if (form_name = ‘DEMXXEOR’ and block_name = ‘ORDERS’) then
return TRUE;
else
return FALSE;
end if;
end zoom_available;

Sample code2:

function zoom_available return Boolean is
form_name  varchar2(30) := name_in(‘system.current_form’);
block_name varchar2(30) := name_in(‘system.cursor_block’);
begin
if (form_name = ‘APXINWKB’ and block_name = ‘INV_SUM_FOLDER’)
then
return TRUE;
elsif (form_name = ‘APXINWKB’ and block_name = ‘LINE_SUM_FOLDER’)
then
return TRUE;
else
return FALSE;
end if;
end zoom_available;


2] STYLE:
This function returns a integer value. This function allows to override the execution style of Product specific events, but it doesn’t effect generic events like when-new-form-instance. Possible return values are:
1. custom.before
2. custom.after
3. custom.override
4. custom.standard

By default it returns custom.standard.

Sample code:
 
function custom.style(event_name varchar2) return integer is
begin
if event_name = ’MY_CUSTOM_EVENT’ then
return custom.override;
else
return custom.standard;
end if;
end style;

3] EVENT: 
This procedure allows you to execute your code at specific events including:

  –    ZOOM
  –    WHEN-NEW-FORM-INSTANCE
  –    WHEN-NEW-BLOCK-INSTANCE
  –    WHEN-NEW-RECORD-INSTANCE
  –    WHEN-NEW-ITEM-INSTANCE
  –    WHEN-VALIDATE-RECORD
By default this routine must perform ‘null;’

Sample code:
procedure event(event_name varchar2) is
form_name varchar2(30) := name_in(’system.current_form’);
block_name varchar2(30) := name_in(’system.cursor_block’);
begin
if (form_name = ‘XXBI’ and block_name = ‘xxcc’) Then
if(event_name = ‘WHEN-NEW-FORM-INSTNACE’)
–Write your code here
elsif(event_name = ‘WHEN-VALIDATE-RECORD’)THEN
–Write your code here
else
null
end if;
end if;
end;


How to make the changes get affected?

Once you make all the necessary changes, compile the pll and generate the PLX file. Since the CUSTOM library is loaded once for a given session, a user must log out of the application and sign-on again before any changes will become apparent.
Forms Personalization: an alternative of custom.pll
In older versions, prior to 11i, Custom.PLL was most prominently used for adding additional features in the seeded form but the latest version of Oracle EBS comes with the feature called as Forms Personalization which allows even an end user to alter the seeded forms functionality using an user interface called the Personalization form.
Advantages of Forms Personalization over Custom.PLL:

  • Forms personalization can be used by an user with limited PL/SQL knowledge. 
  • Changes take place immediately on reopening the form.
  • Anything which can be done using Custom.PLL can be done using Forms Personalization also.
  • Personalizations are stored in base tables related to Form Personalization.
  • CUSTOM.pll is a single file/entity, hence only one developer can make changes to CUSTOM.pll at any given point in time. This is not a restriction in Forms personalization.
  • Easy to disable/enable with click of a button.
  • Can be moved easily through FNDLOAD from one instance to other.
  • Can be restricted at site/responsibility/user level.
  • Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.

In Oracle EBS development, the terms Personalization, Customizations & Extensions are often used interchangeably. It often creates confusion among developers regarding the meaning of these terms. These terms are critically important terms that developers must understand and use properly. Let’s discuss them briefly here to simply understand what they are.
What is Personalization?
Personalization is the process of making changes to the User Interface (UI) from within an Oracle E-Business Suite Form/Page. It is possible to make personalization to both Form-based and OA Framework based pages.
What is Extension?
Extension is the process of making changes to the programmatic (i.e., PL/SQL or Java) elements of an E-Business Suite form/page. It is possible to extend both Forms based and OA Framework-based pages.
What is Customization?
Customization is the process of creating new forms/pages. While Oracle does provide tools to do this (i.e., Oracle Forms and JDeveloper 10g with OA Extension), this is the least supported option.

What is the difference between Pre-Form and When-New-Form-Instance trigger?
Pre-Form trigger will be fired before entering into the form. It is the first trigger that fires when a form is run; fires before the form is visible. It is useful for setting access to form items, initializing global variables, and assigning unique primary key from an Oracle sequence.
When-New-Form-Instance trigger will be fired whenever form is ready to accept the data from the user.
Another main difference between the two is that you cannot navigate in a pre-from trigger (restricted) whereas you can navigate in a when-new-form-instance trigger. For example : go_block and execute-query will only work in when-new-form-instance trigger not in pre-from trigger.
What are the triggers fired while creating Master Detail form?
On-Clear Details (Form Level)
On-Populate-Details (Block Level)
On-Check-delete-Master (Block Level)
How will you get the block Name in a form?
By using SYSTEM.CURRENT_BLOCK
What is the difference between Pre-insert and On-insert trigger?
Pre-insert trigger fires during the Post and Commit Transactions process, before a row is inserted. It fires once for each record that is marked for insert. On-insert trigger fires during the Post and Commit Transactions process when a record is inserted. Specifically, it fires after the Pre-Insert trigger fires and before the Post-Insert trigger fires, when Form Builder would normally insert a record in the database. It fires once for each row that is marked for insertion into the database.

What is the difference between Pre-Query and Post-Query trigger?
Pre-Query trigger validates the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database. Post-Query trigger perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. It fires once for each record fetched into the block.
What is the trigger sequence while opening a form?
Pre-Form,
Pre-Block,
Pre-Record,
Pre-Item,
When-new-form-Instance,
When-new-block-Instance,
When-new-Record-instance,
When-new-Item-instance,
Post-item,
post-record,
Post-block,
Post-form
What is the difference between new form, open form & call form?
New_form:-Once we move into the destination automatically source will be closed.
Open_form:- It is a two way connection between source and destination. Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.
Call_form:- Call_form() runs an indicated form while keeping the parent form active. Without closing the destination one cannot come back to the source.
How to call a Report from a form?
By using RUN_PRODUCT Built-in.
Can we write Commit statement in forms triggers?
Yes
What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS ?
These triggers are executes when inserting, deleting and updating operations are performed and can be used to change the default function of insert, delete or update respectively.
A query fetched 10 records. How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed ?
PRE-QUERY fires once. POST-QUERY fires 10 times.
How can you execute the user defined triggers in forms?
To execute a user-named trigger, you must call the EXECUTE_TRIGGER built-in procedure, as shown here: Execute_Trigger(‘my_user_named_trigger’);
What are Restricted Built-in Subprograms in forms?
Any built-in subprogram that initiates navigation is restricted. This includes subprograms that move the input focus from one item to another, and those that involve database transactions. Restricted built-ins are not allowed in triggers that fire in response to navigation.
For example, the GO_ITEM and NEXT_SET built-ins are both restricted procedures. GO_ITEM moves the input focus from a source item to a target item, which requires navigation. Similarly, the NEXT_SET procedure causes Oracle Forms to navigate internally to the block level, fetch a set of records, and then navigate to the first item in the first record. (Note that this navigation happens internally as a result of default processing, and may not be apparent to the form operator.) Because GO_ITEM and NEXT_SET both initiate navigation, they cannot be called from triggers that fire in response to internal navigational events; that is, triggers that fire while navigation is already occurring. Thus, a restricted procedure cannot be called from a Pre-Block trigger, because the Pre-Block trigger fires during internal navigation. In fact, restricted subprograms are not allowed from any PRE- or POST- navigational triggers. You can, however, call a restricted built-in subprogram from a When-New-Instance trigger. For example, the When-New-Item-Instance trigger fires after navigation to an item has succeeded, when the form is waiting for input.
What are System Variables in forms?
A system variable is a Oracle Forms variable that keeps track of an internal Oracle Forms state. You can reference the value of a system variable to control the way an application behaves.
List system variables available in forms 10g?
SYSTEM.CURRENT_FORM
SYSTEM.CURRENT_ITEM
SYSTEM.CURRENT_VALUE
SYSTEM.CURSOR_BLOCK
SYSTEM.CURSOR_ITEM
SYSTEM.CURSOR_RECORD
SYSTEM.CURSOR_VALUE
SYSTEM.FORM_STATUS
SYSTEM.LAST_FORM
SYSTEM.LAST_QUERY
SYSTEM.LAST_RECORD
SYSTEM.MASTER_BLOCK
SYSTEM.MESSAGE_LEVEL
SYSTEM.SUPPRESS_WORKING…..etc.
What are the triggers associated with the image item?
When-Image-activated(fires when the operator double clicks on an image Items)
When-image-pressed(fires when the operator selects or deselects the image item)
What are the built-in routines available in forms to create and manipulate a parameter list?
Add_parameter
Create_Parameter_list
Delete_parameter
Destroy_parameter_list
Get_parameter_attr
Get_parameter_list
set_parameter_attr
What are the built-ins used to trapping errors in forms?
Error_type : Returns the error message type(character)
Error_code : Returns the error number
Error_text  : Returns the message text of the Oracle Forms error
Dbms_error_code : Returns the error number of the last database error that was detected.
Dbms_error_text : Returns the message number (such as ORA-01438) and message text of the database error
What is the predefined exception available in forms ?
The FORM_TRIGGER_FAILURE exception is a predefined PL/SQL exception available only in Oracle Forms.
What are the Built-ins used for sending Parameters to forms?
You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.
How do you reference a Parameter?
In pl/sql, You can reference and set the values of form parameters using bind variables syntax.
How do you reference a parameter indirectly?
To indirectly reference a parameter use the NAME_IN or COPY built-ins.
What is forms_DDL?
It issues dynamic sql statements at run time, including server side pl/sql and DDL
What is Text_io Package?
It allows you to read and write information to a file in the file system.
What is When-Database-Record trigger?
It fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. It generally occurs only when the operator modifies the first item in the record, and after the operator attempts to navigate out of the item.
What is the difference between $$DATE$$ & $$DBDATE$$?
$$DBDATE$$ retrieves the current database date
$$DATE$$ retrieves the current operating system date.
What is a timer?
Timer is a “internal time clock” that you can programmatically create to perform an action each time the timer expires.
What are built-ins associated with timers?
find_timer
create_timer
delete_timer
What is the difference between post database commit and post-form commit?
Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions.
What is the form development process?
a) open template form 
b) Save as <your form>.fmb
c) Change the form module name as form name.
d) Delete the default blocks, window, and canvas
e) Create a window.
f) Assign the window property class to window
g) Create a canvas   
h) Assign canvas property class to the canvas
i) Assign the window to the canvas and canvas to the window
j) Create a data block       
k) Modify the form level properties. (sub class item à Text item)
l)  Modify the app_cusom package. In the program unit.
m) Modify the pre-form trigger (form level)
n) Modify the module level properties
o) Save and compile the form.
p) Place the .fmx in the server directory.
q) Register in the AOL
APPLICATION à FORMà FUNCTIONà MENU
What is template?
The TEMPLATE form is the required starting point for all development of new Forms. The TEMPLATE form includes platform–independent attachments of several Libraries.
APPSCORE :- It contains package and procedures that are required of all forms to support  the MENUS ,TOOLBARS.
APPSDAYPK :- It contains packages that control the oracle applications CALENDER FEATURES.
FNDSQF  :- It contains packages and procedures for MESSAGE DICTONARY, FLEX FIELDS, PROFILES AND CONCURRENT PROCESSING.
CUSTOM :- It allows extension of oracle applications forms with out modification of oracle application code, you can use the custom library for customization such as zoom    ( such as moving to another form and querying up specific records)
Where exactly you place your forms in APPS environment?
/apps/visappl/au/11.5.0/forms/US
What are the Different PLL’s used in Forms?
CUSTOM.pll
FNDSQF.pll
APPCORE.pll
APPCORE2.pll
appdaypk.pll
APPSTAND.pll
What are the triggers that can be modified during Forms Customization?
Pre-Forms
When-New-Form-Instance
Query_Find
Post-Form
Key-Clrfrm
Accept
What are the triggers that cannot be modified during Forms Customization?
STANDARD_ATTACHMENT
ZOOM
FOLDER_ACTION
KEY-HELP
KEY-EXIT
KEY-COMMIT
WHEN-WINDOW_CLOSED
CLOSE_WINDOW