Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Monday, 26 December 2016

Oracle Forms FAQS

1) Types of Canvases (Stacked/Content Difference)
(i) Content Canvas (Default Canvas) [A content canvas is the required on each window you create]
(ii) Stack Canvas [you can display more than one stack canvas in a window at the same time]
(iii) Tab Type Window [In Tab canvas that have tab pages and have one or more then tab page]
(iv) Toolbar Canvas [A toolbar canvas often is used to create Toolbar Windows. There are two type of Toolbar window.
a. Horizontal Toolbar Canvas: - Horizontal Toolbar canvases are displayed at the top of the window, just under the Main Menu Bar.
b. Vertical Toolbar Canvas: - While vertical Toolbar are displayed along the Left Edge of the window.

2) Master-Detail Relation (Triggers/Procedures/Properties)
On-Check-Delete-Master: - Fires when Form Builder attempts to delete a record in a block that is a master block in a master-detail relation.
On-Clear-Details: - Fires when Form Builder needs to clear records in a block that is a detail block in a master-detail relation because those records no longer correspond to the current record in the master block.
On-Populate-Details: - Fires when Form Builder needs to fetch records into a block that is the detail block in a master-detail relation so that detail records are synchronized with the current record in the master block.

(i) Isolated: - Masters Can be deleted when Child is existing
Triggers: - On Populate details Block
On Clear Details Form
Procedure Check Package Failure
Clear all master Detail
Query Master Detail
(ii) Non- Isolated: - Masters Cannot be deleted when Child is existing.
Triggers: - On Populate details Block
On Check Delete master Block
On Clear Details Form
Procedure Check Package Failure
Clear all master Detail
Query Master Detail
(iii) Cascading: - Child Record Automatically Deleted when Masters is deleted.
Triggers: - On Populate details Block
Pre Delete Block
On Clear Details Form
Check Package Failure
Clear all master Detail
Query Master Detail

3) Dynamically create LOV/List Item
*You can also add list elements individually at run time by using the ADD_LIST_ELEMENT built-in subprogram, or you can populate the list from a record group at run time using the POPULATE_LIST built-in. If you populate the list from a record group, be sure that the record group you are using to populate the list contains the relevant values before you call POPULATE_LIST. If the record group is a static record group, it will already contain the appropriate values. Otherwise, you should populate the group at run time using one of the record group subprograms.

4) Key-next/Post-Text (Difference)
*Post-Text–Item: Fires during the Leave the Item process for a text item. Specifically, this trigger fires when the input focus moves from a text item to any other item.
Key-Next-Item: The key-next is fired as a result of the key action. Key next will not fire unless there is a key event.

5) Call Form/New Form/Open Form (Difference)
*Call Form: Runs an indicated form while keeping the parent form active. Form Builder runs the called form with the same Runform preferences as the parent form. When the called form is exited Form Builder processing resumes in the calling form at the point from which you initiated the call to CALL_FORM.
PROCEDURE CALL_FORM (formmodule_name VARCHAR2, display NUMBER, switch_menu NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name/id VARCHAR2);
New Form: Exits the current form and enters the indicated form. The calling form is terminated as the parent form. If the calling form had been called by a higher form, Form Builder keeps the higher call active and treats it as a call to the new form. Form Builder releases memory (such as database cursors) that the terminated form was using.
Form Builder runs the new form with the same Runform options as the parent form. If the parent form was a called form, Form Builder runs the new form with the same options as the parent form.
PROCEDURE NEW_FORM (formmodule_name VARCHAR2, rollback_mode NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name/id VARCHAR2);
Open Form: 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.
PROCEDURE OPEN_FORM (form_name VARCHAR2, activate_mode NUMBER, session_mode NUMBER, data_mode NUMBER, paramlist_id/name PARAMLIST);

6) Object Groups (Use)
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module.
Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects. For example, you might build an appointment scheduler in a form and then decide to make it available from other forms in your applications. The scheduler would probably be built from several types of objects, including a window and canvas, blocks, and items that display dates and appointments, and triggers that contain the logic for scheduling and other functionality. If you packaged these objects into an object group, you could then copy them to any number of other forms in one simple operation.
You can create object groups in form and menu modules. Once you create an object group, you can add and remove objects to it as desired.

7) Object Libraries (Use/Benefits)
The Object Library provides an easy method of reusing objects and enforcing standards across the entire development organization.
Object Library can be used to:
1. Create, store, maintain, and distribute standard and reusable objects.
2. Rapidly create applications by dragging and dropping predefined objects to your form.

There are several advantages to using object libraries to develop applications:
1. Object libraries are automatically re-opened when you startup Form Builder, making your reusable objects immediately accessible.
2. You can associate multiple object libraries with an application. For example, you can create an object library specifically for corporate standards, and you can create an object library to satisfy project-specific requirements.
3. Object libraries feature Smart Classes-- objects that you define as being the standard. You use Smart Classes to convert objects to standard objects.

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

9) How to attach same LOV to multiple items
We can use the same LOV for 2 columns by passing the return values in global values and using the global values in the code.

10) Static & Dynamic LOV
The static LOV contains the predetermined values while the dynamic LOV contains values that come at run time

11) Format Triggers (What are they)
A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object.

12) What is the sequence of events fired while cursor moves from an item in 1st block to an item in 2nd block?
          When validate item of that item A
          Post_text_item of A
          When validate record
          Post record
          Post block
          Pre block
          Pre record
          Pre text item

13) What are the forms customization steps?
Steps are as follows:
A) Copy the “Template fmb”1 and “Appstand.fmb” from AU_top/Forms/us. Put it in custom directory .the Libraries (FNDSQF, APPCORE, APPDAYPK, GLOBE, CUSTOM, JE, JA, VERT) are automatically attached
B) Create/open new forms, then customize
Save this form in corresponding module.

14) What is template?
a) The TEMPLATE form is the required starting point for all development of newForms.
b) 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
APPSDAYPK:- It contains packages that control the oracle applications CALENDER FEATURES.
FNDSQF :- it contains packages and procedures for MESSAGE DICTONARY, FLEX FIELDS,
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)

15)How do I open a form in query-only mode?
Navigate to the Form Functions form (Application->Function)
Query the particular form and add the parameter QUERY_ONLY=YES
Not all forms will accept this parameter however.

Custom library allows extension of Oracle Applications forms without modification of Oracle applications code.
-         Custom library can be used for customizations such as ZOOM (Moving to another form from one form and querying up specific records), enforcing business rules.
Ex:-  Supplier name must be in upper case) and disabling  fields that are not required for a
   particular site.
-         All logic must branch based on the form and block for which it is run.
-         Oracle applications send events to the Custom library.
-         Custom code can take effect based on the events.
* Triggers in Custom.Pll:-
6        ZOOM
7        EXPORT
8        SPECIAL 1-45
9        KEY_Fn (n is a number between 1 and 8)

17) What is the difference between pre-query and post -query?
* Pre-query executes only once for the statement whereas post-query executes for each record.

18) How to get second parameter value based on first parameter?
          $flex $ value set name.

19) Forms can be development in APPS in two ways
A)  Customization by extension (using template.fmb)
B)   Customization by modification (using custom.pll)

20) How to call WHO columns into the form?
          Provides information about the form.
          Should be called form when_newform _ instance   trigger.
2. FND_standard.set_who
          loads WHO columns with proper user information.
          Should be called from PRE_UPDTE and PRE_INSERT
          Triggers for each block with WHO fields
          If this is used FND-GLOBAL need not be called.
          This is a function which returns date.
          Behave exactly like SYSDATE built-in.
          This is a function which returns varchar2
          Behaves exactly like built in USER.

21) How to call flex fields in the form?
By using FND_FLEX.EVENT (EVENT varchar 2)

22) How to convert a form from  4.5 to 6.0?
- To upgrade forms, the form can be directly compiled in the next release.
Form can be compiled by using ifcmp 60.exe
FLINT 60 bath executable can be used to check whether the form is compatible to Apps or not.

23) What are the triggers that fire on item?
1.     Pre_Text_Item
2.     when_New_Item_Instance
3.     post_text_Item
4.     post_Change
5.     When_validate_Item
6.     key_Next_Item
·        execute fnd_client_info.set_org_contest (‘Org_Id’)
·        execute dbms_application_info.set_client_info (‘Org_Id’)

Transactional triggers in forms
-         Transactional triggers are the triggers that are related to accessing a data source.
-         These triggers fire for each record that is marked for inset, update or table when forms whould typically insert. Update of delete statements.
-         Internally forms would be calling its internal insert_record, update_Record and Delete_Record built_ins as appropriate to perform the default processing .
* Important Transaction triggers are
1.     ON_LOCK
2.     ON_UPDATE
3.     ON_INSERT
4.     ON_DELETE

24) Which triggers will fire when cursor moves from one block to another block?
          Trigger Firing Order                                 Level
          1. Post_Test_Item                                                Item
          2. Post_Record                                           Block
          3. Post_Block                                                       Block
          4.When_Create_Record                              Block
          5. Pre_ Block                                                       Block
          6. Pre_Record                                                      Block
          7. Pre_Text.Item                                         Block
          8.When_New_Block_Instance                    Block
          9.When_New_Record_Instance                           Block
          10.When_new_Item_Instance                              Form

25) What is the difference between PRE_COMMIT and POST_COMMIT triggers?
* ‘POST_FORMS_COMMIT triggers is the new name for the POST_COMMIT triggers.
*When a form is being committed the following triggers are fired

Pre – Commit Trigger
This trigger fires once during the Post and Commit transaction process. Before form builder processes any (changes) records to change.Specifically it fires after form builder determines that there are inserts, updates or deletes in the form to post or commit, but before it commits the changes.This trigger doesn’t fire when there is an attempt to commit, but validation determines that there are no changed records in the form.
This is a form level trigger.Enter query mode should be set as ‘No’This can be used to perform an action, such as setting up special locking requirements, at any time a database commit is going to occur.If this trigger fails, the post and commit processes fail, no records are written to the database and focus remains in the current item.If a DML is performed in a pre-commit trigger and it fails, ten manual rollback must be performed, because form builder doesn’t perform an automatic roll back.
This trigger fires in post and commit transactions.

Post – Commit Trigger
This is also known as post-commit trigger.
Post-commit trigger fires once during the post and commit transactions.
If there are records in the form that have been marked as inserts, updates or deletes, the post-forms-commit trigger fires after these changes have been written to the database but before form builder issues the database commit to finalize the transaction.If the operation or application initiates a commit when there are no records in the form have been marked as inserts, updates or deletes, form builder fires post-forms-commit trigger immediately, without posting changes to the database.This is a form level trigger.Enter query mode should be set to ‘No’Post-forms-commit trigger should be used to perform an action, such as updating an audit trial any time a database commit is about to occur. If this trigger fails, post and commit processing aborts and form builder issues a ROLLBACK and decrements the internal save point counter. This triggers fires in Post and Commit transactions.

26) What are Mandatory triggers to write in the block, which is created on view?
If any block is created from a view all the transactional triggers are mandatory.

27) What are the Major differences between appcore.pll and appcore2.pll?
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

Share this article :


  1. Hello Dayakar,

    Your blog is such a complete read. I like your approach with Oracle Forms FAQS. Clearly, you wrote it to make learning a cake walk for me.

    How to find first non-space character from end of the string

    ex: 'abc def g '

    I should get 4 in this case. (the first nonspace character from backwards in the above example is g and the position is 4 from backwards.
    Package is a database object that groups logically related procedures. The advantages of packages are Modularity, Easier Application Design, Information. Hiding, reusability and Better Performance.

    Could you help me in getting this?
    I am so grateful for your blog. Really looking forward to read more.

    Best Regards,

  2. Hi There,

    What you’re saying is absolutely correct Oracle Forms FAQS, but this isn’t the exact situation everywhere. Where most smart folk work on a project - why can’t you do this the Boss asks :).

    I have employee table which contain employee details . When i tried with below it gives me different couts

    1) select count(*) emp_count,
    count(department_id) nut_null_dept_id
    from employees;
    e is difference for those values ?
    i am getting emp_count=107 and nut_null_dept_id=106 . why there?

    Excellent tutorials - very easy to understand with all the details. I hope you will continue to provide more such tutorials.

    Thanks a heaps,

  3. Marhaba,,

    Fully agree on #topic. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    I want to execute the different files at the run time in the SQL PLUS

    Ex: I have two different files like ABC.SQL, XYZ.SQL,

    I had declared one bind variable to hold file_name .

    var file_name VARCHAR2(200);
    sr VARCHAR2(20) :='ABC';
    IF sr ='ABC' THEN
    :file_name :='ABC.SQL';
    :file_name :='XYZ.SQL';
    END IF;
    PRINT file_name

    Now i want to execute the file which is there in :file_name bind variable


    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!


  4. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.