Oracle Apps Interview Question and Answer - D2K Forms

TEMPLATE FORM 

·         The TEMPLATE form is the required starting point for all development of new forms.

·         The development of a new form is started by copying the TEMPLATE.fmb file, located in $ AV_top / forms/ us, to a local directory and renaming it as appropriate.

TEMPLATE FORM CONTAINS THE FOLLOWING:-

* platform-independent references to object groups in the APPSTAND form

                             (STANDARD _PCAND_VA,

                              STANDARD_TOOLBAR,

                             STANDARD_CALENDAR)

* platform – independent attachments of several libraries 

                   FND SQF

                   APPCORE

                   APPDAYPK

* several form level triggers with requited code

* program units that include a specification and a body for the package APP_CUSTOM, which contains default behaviors for window opening and closing events.

·         In general this code should be modified for the specific form under development.

* The application’s color pallet, containing the two colors required by the referenced visual attributes.

* Many referenced objects that support the calendar, the toolbar, alternative regions and the menu.

* Template form contains simple objects that show typical items and layout cosmetics.

·         These are deleted after developing the form

Blocks : Block Name, Detail Block

Window : Block Name

Canvas _view :Block Name

* Template form includes plat form-independent attachments of several libraries.

·         Some of the libraries are attached directly to the TEMPLATE (FNDSOF, APPCORE, APPDAYPK) while the others are attached to these libraries.

APPCORE, APPDAYPK, FNDSOF, CUSTOM, GLOBE, VERT, JA, JE, JL

APPCORE

·         Contains the packages and procedures that are required of all forms to support the Menu, Toolbar and other required standard behaviors.

·         Procedures and functions in APPCORE have names beginning with APP.

APPDAYPK

·         Contains the packages that control the Oracle applications calendar feature.

FINDSQF

·         Contains packages and procedures for message dictionary, florfields, profiles and concurrent processing.

·         It also has various other utilities for navigation, MRG, WHO etc.

·         Procedures and functions have names beginning with FND.

CUSTOM

·         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.

1.   Which trigger will fire when cursor moves from one block to another block?

WHEN_NEW_BLOCK_INSTANCE

What are the triggers used in CUSTOM.Pll?

CUSTOM.Pll contains (CUSTOM package) the following functions and procedures.

     CUSTOM.ZOOM_AVAILABLE à FUNCTION

     CUSTOM.STYLEà FUNCTION

CUSTOM.EVENTà PROCEDURE.                              

* Triggers in Custom.Pll:-

·         WHEN _NEW_FORM_INSTANCE

·         WHEN_FORM_NAVIGATE

·         WHEN_NEW_BLOCK_INSTANCE

·         WHEN_NEW_RECORD_INSTANCE

·         WHEN_NEW_ITEM_INSTANCE

·         ZOOM

·         EXPORT

·         SPECIAL 1-45

·         KEY_Fn (n is a number between 1 and 8)

2.   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.

List of some API’S

FND_PROGRAM.EXECUTABLE

FND_PROGRAM.REGISTER

FND_PROGRAM.PARAMETER

FND_PROGRAM.ADD_TO_GROUP

FND_REQUEST.SUBMIT_REQUEST

FND_PROFILE.VALUE

FND_PROFILE.GET

3.   How to get second parameter value based on first parameter?

          $fle x $ value setname.

4.   What is Ref Cursor

Ref cursor is a data type and executed at server side and with ref cursor multiple select statements can be executed.

By increase the binary size, that number of records that are committed can be increased by using control file.

Forms can be development in APPS in two ways

A)   Customization by extension (using template.fond)

B)   Customization by modification (using custom.pll)

5.   What are the advantages of packages?

• Packages bundle related Pl/SQL types, items and sub-programs in to one container.
• A package should have its specification and body, stored separately in the database.
• Specification is the interface to the applications.
• A declares the types, variables, constants, exceptions, cursors and sub programmers available for use. The body fully defines the cursors and sub programmers and so implements the specification.
• Once written and compiled the contents can be shared by many applications.
• When a packaged PL/SQL construct is called for the first time the whole package is loaded in to memory, thus later calls to constructs in the same package require no disk I/O.
• Public package constructs can be referenced from any Oracle server environments.
• Private package constructs can be referenced only by other constructs which are part of the same package.

Advantages

·         Modularity

·         Easier Application Design

·         Hiding Information by using public and private.

·         Added functionality.

·         Better performance.

·         Over loading.

Procedures and functions can be over loaded i.e. creating multiple sub programmes with the same name in the same package each taking parameters of different number or data type.

6.   How to call WHO columns into the form

By using FND_STANDARD API’S

1. FND_STANDARD.FORM_INFO

          Provides information about the form.

          Should be called form when_new_form – instance – 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.

                             (FND_GLOBAL.WHO)

3. FND_STANDARD.SYSTEM_DATE

          This is a function which returns date.

          Behave exactly like SYSDATE built-in.

4. FNID_STANDARD.USER

          This is a function which returns varchar2

          Behaves exactly like built in USER.

APPCORE API’S

APP_COMBO

APP_DATE

APP_EXCEPTION

APP_FIELD

APP_FIND

APP_ITEM

APP_ITEM_PROPERTY

APP_NAVIGATE

APP_RECORD

APP_REGION

APP_STANDARD

APP_WINDOW

FNDSQF API’S

FND_CURRENCY

FND_DATE

FND_GLOBAL

FND_ORG

FND_STANDARD

FND_UTILITIES.OPEN_URL

FND_UTILITIES. PARAM_EXISTS

7.   How to call flex fields in the form?

By using FND_FLEX.EVENT (EVENT varchar 2)

8.   How to generate fmx  at OS level?

-          Forms can be generated on the forms server as the APPLMGR user.

-          Generating the form on the Form server, $ FORM60_PATH

Should be set and the current directory should be $AV_TOP/forms?us.

Syntax à $F60 gen userid=apps/appsp     module=<form_name>. Fmb

              Output_file=<schema_top>/forms?<language>/<form_name>.fmx

              Module_type=form bath =no compile_all=special

9.    How to call a form from another form?

-          to invoke another form with in a form the function security routines should be used which are available in FND_DUNCTION package.

* for this purpose, CALL_FORM built-in cannot be used since the Oracle Applications libraries do not support it.

*FND_FUNCTION.EXECUTE  should be used to open a new session of a form (CALL_FORM/ OPEN_FORM do not be used)

*APP_NAVIGATE.EXECUTE procedure also can be used to open a form where an instance of the same form is reused, that has already been opened.

*APP_NAVIOGATE.EXECUTE is similar to FND_FUNCTION.EXECUTE, except that is allow a form to be restarted if it is invoked a second time.

*FND_FUNCITON.EXECUTE always starts a new instance of a form.

Syntax à procedure APP_NAVIGATE.EXECUTE

                   (Function_name in varchar2,

                   open_flag in varchar2 default ‘y’

                   sesson_flag in varchar2 default ‘SESSION’

                   other_params in varchar2 default null,

                   activate_flag in varchar2 default ‘ACTIVATE’

                   pinned in boolean  default FALSE);

Syntaxà Procedure FND_FUNCTION.EXECUTE

                   (function name in varchar2,

                    open flag in varhcar2 default ‘y’

                    session_flag in varhcar2 default’session’

                   other_params in varchar2 default null,

                   activate in varchar2 default ‘Active’

                   browser_target in varchar2 default null);

10.   what are the triggers that fire on item?

1. Pre_Text_Item
2. when_New_Item_Instance
3. When_validate_Item
4. key_Next_Item
5. post_text_Item
6. post_Change
• execute fnd_client_info.set_org_contest (‘Org_Id’)
• execute dbms_application_info.set_client_info (‘Org_Id’)

11.    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 would 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 at Transaction triggers are

1.   ON_LOCK

2.   ON_UPDATE

3.   ON_INSERT

4.   ON_DELETE

12.    Which triggers will fire when censor 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_Recrd_Instance                         Block

          10.When_new_Item_Instance                                  Form

13.    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

(i) PRE_COMMIT (ii) ON_COMMIT (iii) POST_COMMIT

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 trigger fires in Post and Commit transactions.