Bind references are used to replace a single value in SQL or PL/SQL. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,CONNECT BY, and START WITH clauses of queries. Binds may not be referenced in the FROM clause.
An example is:
SELECT Col1,Col2
FROM XX_table
WHERE Col1 = :P_col1

Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY , ORDER BY , HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value.
An example is:
SELECT Col1,Col2
FROM &ATABLE

Requirement: From OAF page i have to call a custom procedure for some activity.

Solution:
Create a button through OAF personalization like below. Concurrent program name is XXPRCTEST. I register this under Application Developer.

ID: TestBtn
Prompt: Submit Report
OA.jsp?akRegionCode=FNDCPPROGRAMPAGE&akRegionApplicationId=0&programApplName=FND&programRegion=Hide&scheduleRegion=Hide¬ifyRegion=Hide&printRegion=Hide&&programName=XXPRCTEST
Target Frame: _blank

This will open a new submit window.

Libraries – Core

($) Download from $AU_TOP/resource
($) Set FORMS60_PATH on desktop to location of local libraries
($) APPCORE.pll Application Standards, toolbar
   VERT.pll Specific industry features
   GLOBE.pll Global or regional features
     JA.pll Asia/Pacific features
     JE.pll Europe/Middle East/Africa features
     JL.pll Latin America Code
   CUSTOM.pll Customizations of standard forms
($) FNDSQF.pll Flexfields, Profiles, Currency, Concurrent Request Submission, Record History, Message Dictionary
($) APPDAYPK.pll Calendar
($) APPFLDR.pll Packages for Folder Blocks

Libraries – Application Specific

($) Additional libraries attached to TEMPLATE.fmb
($) All libraries are available in $AU_TOP/resource
($) FV.pll Federal Financials
($) HRKPI.pll Human Resources Knowledge Provider
($) GHR.pll Global Human Resources
($) GMS.pll Encumbrances
($) IGILUTIL.pll International Public Sector
($) IGILUTIL2.pll International Public Sector
($) PSAC.pll Public Sector Applications
($) PQH_GEN.pll Public Sector Human Resources
($) PSA.pll Public Sector Applications
($) PSB.pll Public Sector Budgeting
($) OPM.pll Process Manufacturing
($) APPCORE2.pll Application standards (for CUSTOM.pll)
($) List of application specific libraries are not all inclusive
($) Additional libraries are attached based on your implementation
   Multiple Countries
   Oracle Industry Applications
($) Oracle does not support the use of APPFLDR, VERT, GLOBE, PSAC, PQH_GEN, GHR, JA, JE and JL for custom forms


($) *NEVER MODIFY ORACLE APPLICATIONS LIBRARIES OTHER THAN CUSTOM.pll*


APPCORE: APPCORE contains the packages and procedures that are responsible for the standard ‘Oracle Applications’ behavior of the forms. The forms have to support the menu, toolbar, and other required standard behaviors in compliance with oracle applications. The Oracle Applications forms also have to display specific runtime behaviors in accordance with the Oracle Applications Interface Standards, such as the way in which fields are enabled, behaviors of specific types of windows etc. APPCORE contains the procedures to support this standard runtime behavior. APPCORE also contains various other utilities for exceptional handling, message levels, and so on.


GLOBE: The GLOBE library allows Oracle Applications developers to incorporate global or regional features into Oracle Applications forms
without modification of the base Oracle Applications form. Oracle Applications sends events to the GLOBE library. Regional code can
take effect based on these events. The GLOBE library calls routines in
the JA, JE, and JL libraries.


JE: The JE library contains code specific to the EMEA (Europe/ MiddleEast/ Africa) region and is called by the GLOBE library.


FNDSQF: FNDSQF contains packages and procedures for Message Dictionary, flexfields, profiles, and concurrent processing. It also has various other utilities for navigation, multicurrency, WHO, etc.


JL: The JL library contains code specific to the Latin America region and is called by the GLOBE library.


JA: The JA library contains code specific to the Asia/Pacific region and is called by the GLOBE library.


VERT: The VERT library allows Oracle Applications developers to incorporate vertical industry features (for automotive, consumer packaged goods, energy, and other industries) into Oracle Applications forms without modification of the base Oracle Applications form. Oracle Applications sends events to the VERT library. Vertical industry code can take effect based on these events. The VERT library calls routines in various other libraries.


GHR: The GHR library contains code specific to Event Dispatcher  for HRMS Localization

APPCORE2: There is no basic difference between appcore.pll and appcore2.pll. Appcore2.pll is a replica of appcore.pll. While using custom.pll we can’t reference appcore.pll as it cause cross reference problem so to use appcore.pll in custom.pll we make use of appcore2.pll


HRKPI: This HRKPI library contains packages for humary resource knowledge provider. If the HR_STAND is attached with the form, then this may be must.
PQH_GEN.pll The PQH_GEN library contains code specific to Public Sector Human Resources
PSAC.pll The PSAC library contains code specific to Public Sector Applications
PSB.pll The PSB library contains code specific to Public Sector Budgeting
PSA.pll The PSA library contains code specific to Public Sector Applications
GMS.pll The GMS library contains code specific to Encumbrances
FV.pll The FV library contains code specific to Federal Financials

IGILUTIL.pll The IGILUTIL library contains code specific to International Public Sector

IGILUTIL2.pll The IGILUTIL2 library contains code specific to International Public Sector

OPM.pll The OPM library contains code specific to Process Manufacturing

Overview of the Template Form

The TEMPLATE form is the starting point for all development of new forms. The first step in creating a form for use in Oracle Applications is to copy the template form from $AU_TOP/forms/US, to a local directory and renaming it.

The Template form is unique because it contains some special libraries and triggers that render the application using the template form some standard characteristics. The components of the template form are:

  • References to object groups: The template form contains platform–independent references to predefined standard object groups in the APPSTAND form(STANDARD_PC_AND_VA,STANDARD_TOOLBAR, and STANDARD_CALENDAR).
  • Libraries: The template form contains platform–independent attachments of several libraries (including FNDSQF, APPCORE, and APPDAYPK).
  • Special triggers: The template form contains several form–level triggers with required code. These are responsible for standard the behavior of the form.
  • Predefined Program Units: The template form contains predefined program units that include a spec and a body for the package APP_CUSTOM, which contains default behavior for window opening and closing events.
  • Applications Color Palette: The template form contains the application color palette. This gives the forms developed using the template form the look and feel of Oracle applications.
  • Many referenced objects (from the object groups) that support the Calendar, the toolbar, alternative regions, and the menu. These objects include LOVs, blocks, parameters, and property classes, and so on.
  • The TEMPLATE form contains sample objects that can be seen as examples for the expected layout cosmetics. These samples can be completely removed from theform later as they are only examples and are not required. The following objects are the samples and can be removed:
    • Blocks: BLOCKNAME, DETAILBLOCK
    • Window: BLOCKNAME
    • Canvas–view: BLOCKNAME
Hence, the template form comes along with many attachments, predefined program units, and defined visual attributes as well as examples that not only give the forms that are developed using the template.fmb a standard look and feel, but also make it easier to develop forms with consistent and standard functionality.
Forms Builder provides various components, such as Data Block, Items, Property Palette, Canvases and Windows, Triggers, and Program Units that enable you to create a form.

Data Block
A data block is a virtual data set that represents the database table. You need to create the associated block in the Object Navigator for every table used in the form. A data block is bound to a table or a view in a database or a set of procedures. The association of a data block and a database allows a form to manipulate the data in a database. You can create a data block manually or using the Data Block wizard.
Items
An item is an interface object that helps display information in a GUI application. It enables you to store, insert, modify, and delete information in a database. Each item belongs to a block. The items in a block are bound to columns in a base table. They display the data stored in these columns. You may also enter the data in these items for later processing. An item may need not necessarily be bound to columns of base table, such as sum of fields. These items are known as non-database items. These items are used to display information from the tables associated with base tables.
Canvases and Windows
A canvas is a physical container or a layout on which you can place items. End users interact with the items on the canvas when a form is executed. You can create a canvas manually using the Layout Editor or the Layout wizard. A canvas can be displayed in different windows.
A Window is the basic Document Interface in the Forms Builder. For each window, you must create at least one canvas. The Windows are assigned appropriate properties to determine whether the application is a Multiple Document Interface (MDI) or Single Document Interface (SDI).
You need to place the canvas in a specific window to view the canvas and the items in it, when you execute a form. By default, a canvas is assigned the window called WINDOW1. You can assign a different window using the Window property in the property palette of the canvas.
Forms Builder enables you to create the following types of canvases:

  • Content
  • Stacked
  • Tab
  • Toolbars