API vs Open Interface

What are Open Interfaces?

• The term Open Interfaces actually refers a programming interface, usually a database table, which automates the execution of Oracle APIs.
• Open Interfaces provide a single, simple interface for a specified business procedure.
• There are interface tables that we need to populate and run a concurrent request that will pick records from these tables do validation and insert it in the base tables.

 What are the Oracle APIs?

• These are called as a collection of “Black Box” interfaces that provide programmatic access into the Oracle ERP database.
• The term API refers to stored procedure driven interfaces, where you call a stored procedure to perform an action within an Oracle Module, and the data from your external application is passed through the stored procedure’s parameters.
• APIs are for not meant for mass data loading and they load a single record (not necessarily a single row) at a time, we have to pass parameters and generally they need data in a row/table type format to make use of APIs for mass loading we have to run it in a loop reading from a .csv file.

Why use Open Interfaces?

•            In EBS one Open Interface may run many API calls.
•           Open Interface run asynchronously.
•                  The good is that if there is failure of record, they remain in the table until either fixed or purged.
•                      They automate the interface into the APIs.
•                     This requires less work and less code as few SQL DML would simply.

 Why use APIs?

•          When there is no corresponding Open Interface.
•          Normally all Oracle APIs run synchronously, and provide immediate responses, therefore mechanism to be provided to handle such situation.
•          APIs requires custom error handling routine.
•          This may requires lot more effort as these need fine grain control approach.
•          API is faster than Open Interface.

Note: 

• The APIs are also used by the front end screens, and in the same way, will require all the appropriate prerequisites to be implemented.
• Cannot use APIs as an alternative to implementation.

OA Framework Best Practices:

OA Framework Best Practices

·         AM Retention : Retaining the AM where not required, Not retaining it wherever required – both are dangerous!!            

The AM should be retained whenever you are navigating away from a page and when you know that there is a possibility to come back to the page again and data is to be retained. Example :  Any navigation link that opens in a new page or any navigation which has a back button to come back to the initial page.         

The AM should not be retained for two independent pages, especially if they have common VOs which fetch different result sets. In such cases, retaining the AM may not remove the cache of VOs and so the result may not be as expected.

 

·         Setting dynamic whereClause : Avoid setting where clauses dynamically as far as possible. And when required, set the where clause and where clause params to null before setting your new where clause. Remember that the where clause once set will be retained through out. So it’s a best practice to make it null wherever it is not required. Creating two VOs is always better than using a single VO with two dynamic where clauses.

 

·         Fetch Profile Value : Fetching the values like profile , orgId, testFunction etc can be done in two ways.

1.      Fetching it from pageContext.   

                Syntax : pageContext.getProfile(‘XXWHO_ENTITY’);

2.      Write a VO to query the values directly from the data base.

               Syntax: SELECT FND_PROFILE.VALUE(‘XXWHO_ENTITY’) FROM DUAL

          The first option brings the data from the cache, so it takes time for the changed values to be reflected in front-end. But the second option is costly performance wise. So its better to invalidate cache regularly and use the first option to fetch such kind of values.

 

·         Whenever you are executing a VO or creating rows in a VO in the process request, do it conditionally. Let it not get executed every time the process request is called.                

Ex:    if(fromPage == “CorrectPage”) {

                      vo.executeQuery();

        }                                    

This avoids duplicate data or data loss whenever your page is rendered from a dialog page or back button etc.

 

·         If you want any values of your CO to be available in your EO, put those values in transaction. Do this only if it is very much required. Should not create unnecessary transactions anywhere. Only get the already existing transaction object from AM using getTransaction() Method.

 

·         Make it a point to remove all the session parameters and transaction parameters soon after their utilization. Should be very careful with session parameters because they result in unnecessary data and it’s often difficult to debug.

 

·         Passing too many parameters through hashmap not only effects the performance but it also effects the UI

 

·         Avoid writing huge code in CO. No business logic should be written in the CO or AM. Try creating private methods in controller wherever you have redundant code.

·         Create separate interface per module for declaring all your constant variables rather than hardcoding the values in the code.

Ex : You want to use a lookup name n number of times in your coding. Instead of hardcoding it everywhere, declare it in your constants interface and use the constant in your code. Tomorrow, If you have to change the lookup, you can change it at a single place (in the constants interface) and save time.

 

·         Whenever you have to get or set values of a VO use get/set<Attribute_name> method instead of getAttribute(index) method. Because the getAttribute(index) traverses through the entire attributeset in order to find the required attribute which is a performance issue

Ex: Say, you have an attribute called SelectFlag with attribute index as n . To get the value, use getSelectFlag instead of getAttribute(n).

 

·         HGrid cache: Hgrid is one of the components in OAF which gives major surprises to the developer everytime. Should be very careful while creating the viewlink. Remember to clear the cache of the hgrid each time the hgrid is rendered or the hgrid query is executed.

Syntax :  hgridBean.clearCache();

To expand the hgrid upto nth level by default (i.e to expand the tree structure upto nth child), use the following syntax :

                   hgridBean.setAutoExpansionMaxLevels(n); 

·         Better to call sequences from the create method of EO rather than calling them from custom methods in AM. Infact, set all the default values or who columns for attributes here only.

 

·         Always reset a VO before iterating it. Especially when getting the values of a radio button selection etc.

Ex :

vo.reset();

while(vo.hasNext()){ 

         // get handle of the vo’s current row and perform ur operations;  

        // break the loop after reaching the last record of the current rowset.

} 

 

·           Set any VO’s where clause and where clause params in VOImpl class rather than creating directly in CO. This increases reusability and modularity. But you don’t have to set any where clause or where clause params and should directly execute the query, then need not use VOImpl for calling the executeQuery() method.

 

·           Exception Handling : Proper exception handling is a must. Put your code in proper try –catch blocks wherever possible.

If  your code ,which is in try-catch block, have to throw OAException anywhere, that will be caught and will not be shown to the user. Hence use the following statement in the catch block to throw the OAExceptions to the user :

                 try{
                          throw new OAException(“APP_CODE”, “ERROR_MSG”); 
                     }catch(Exception _exception) {      
                          throw OAException.wrapperException(_exception);   
                   }

Use the following syntax for logging your exception in the log file

            if(pageContext.isLoggingEnabled(4)) {
               pageContext.writeDiagnostics(getClass().getName()+”.processFormRequest”, “Exception Details :  “, 4); 
              }

Do not catch your exceptions wherever they occurred (ex in AM). The exception must always be thrown back to the calling method . So all the exceptions must be finally caught in the CO.

 

      Calling AM methods in CO : Those of you who have used invokeMethod() in CO to call the AM methods might have observed that it’s the most tedious way of calling methods, especially when you have to send serialized parameters. Importing the AMImpl class in your controller allows you to access the AM methods directly without the use of invokeMethod() but it breaks the basic OAF standards coz u r accessing a class in the server package from a class in the webui package. To overcome the above, the best practice is to create an Interface of the AMImpl (which neither belongs to server nor webui) and use this AM Interface in your CO rather than AMImpl class. To create an interface in Jdeveloper, double click on the AM , select ‘client methods’ and shuttle the required methods to right side. It automatically creates an interface for you with the selected methods.

Example for getting the object of the interface :

           XXWPHomeWorkPlanAM workPlanHomeAM = (XXWPHomeWorkPlanAM)pageContext.getApplicationModule(webBean).findApplicationModule(“XXWPHomeWorkPlanA

Oracle Apps Interview Questions

What is MULTI-ORG and what is structure of multi-org?

Use a single installation of any oracle applications product to support any number of organizations. If those organizations use different set of books. Support any number or legal entities with a single installation of oracle applications. Secure access to data so that users can access only the information that is relevant to them.

Structure:

·         Business Unit -HRMS(Employee)

·         GL(Set of Books)(Currency, Calendar, Chart of Account, Accounting conventions)|Balancing Segment(You can do multiple balancing segment)

·         Operating Units (Purchase, Selling, Fixed Asset, Payable, Receivables)

·         Inventory Organizations (Storing Items, Transaction Happening, Ware Housing)
(Note:- Means if you maintaining GL(set of book id), If u have operating unit, if you have inventory then it’s called MULTI-ORG).

What is difference between ORG_ID and ORGANIZATION_ID in Multi-Org. At where we can set ORG_ID and ORGANIZATION_ID level it comes in the structure.

A Global Variable exists in the oracle database called CLIENT_INFO, which is 64 bytes long. The first 10 bytes are used to store the operating unit ID (or ORG_ID) for the multiple organization support feature.
Multi-Org views are partitioned by ORG_ID. The ORG_ID value is stored in CLIENT_INFO variable. (It comes in AP, PO, AR, OM level)
ORGANIZATION_ID – It is for Inventory, Mfg, & BOM.

What is Client Info?

By calling this Program in SQL*PLUS or reports with correct parameters user can achieve concurrent program environment for testing.

FND_CLIENT_INFO.setup_client_info(application_id Number,Responsibility_id Number,User_id Number,Security_Group_id Number);

What is AD_DD package?

AD_DD Package is used to register the Table, Columns, and Primary Key in Oracle Applications.

What are the steps in Registering Concurrent Program?

Go to Programs and Define Executables.

Go to Programs and Define Concurrent Program

Go to Responsibility and attach the Request group you want.

What are the different types of executable available in Concurrent Program?

FlexRpt The execution file is written using the FlexReport API. FlexSql The execution file is written using the FlexSql API.

Host: The execution file is a host script.

Oracle Reports: The execution file is an Oracle Reports file.

PL/SQL Stored Procedure: The execution file is a stored procedure.

SQL*Loader: The execution file is a SQL script.

SQL*Plus: The execution file is a SQL*Plus script.

SQL*Report: The execution file is a SQL*Report script.

Spawned: The execution file is a C or Pro*C program.

Immediate the execution file is a program written to run as a subroutine of the concurrent manager. We recommend against defining new immediate concurrent programs, and suggest you use either a PL/SQL Stored Procedure or a Spawned C Program instead.

What are Request Sets and where do you define it?

Request set is a collection of Reports/Programs that you group together and can be submitted to run is a single interaction.

Request sets allow you to submit multiple requests together using multiple execution paths. A request set is a collection of reports and /or programs that are grouped together. A stage is a component of a request set used to group requests within the set. All of the requests in a given stage are executed in parallel. Advantages of stages are the ability to execute several requests in parallel and then move sequentially to the next stage.
Responsibility: System Administrator
Navigation: Concurrent -> Set

Define Request Group?

A request security group is the collection of requests, request sets, and concurrent programs that a user, operating under a given responsibility, can select from the Submit Requests window.

What are the different API‟s for Concurrent Programming?

FND_CONCURRENT

FND_FILE

FND_PROGRAM

FND_SET

FND_REQUEST

FND_REQUEST_INFO

FND_SUBMIT

What is Profile? Explain different levels of Profile.

A user profile is a set of changeable options that affects the way your applications run. Oracle Application Object Library establishes a value for each option in a user’s profile when the user logs on or changes responsibility. Your user can change the value of profile options at any time

a) To create Profile Option (Profile Option can be create by developer in application developer area)

b) Set the value (Values of the profile option, who will have what value at various levels is set by SYSADMIN). Oracle Application Object Library provides many options that. (Edit profile feature for every user is available to set any value to allow the user).your users can set to alter the user interface of your applications to satisfy their individual preferences. Profile Option – set at run time like – User Related, responsibility, Sequence, Printer, Security.

Values in 4 Levels (HIEARCHY WISE):

USER
RESPONSIBILITY
APPLICATION
SITE
Application Developer create the profile. System Administrator make profile option. (NOTE: If any change in value, it will active when you re-login or switch to the responsibility.)
(Usage in the multi-tier, the profile is biggest impact)

What are the two mandatory parameters required for PL/SQL stored Procedure Concurrent Program?

ERRBUF and RETCODE two OUT Parameters are required while defining PL/SQL stored Procedure Concurrent Program.
ERRBUF -- Returns any error message
RETCODE -- Returns completion status (Returns 0 for success, 1 for warnings and 2 for error).

Significance of ALL in apps tables.

Tables which are related with Multiorg is suffixed with ALL.

What is TCA (Trading Community Architecture)?

Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications.

Difference between Application Developer and System Administrator?

Role of Technical Consultant:
1. Designing New Forms, Programs and Reports
2. Forms and Reports customization
3. Developing Interfaces
4. Developing PL/SQL stored procedures
5. Workflow automations
Role of System Administrator:
1. Define Logon Users
2. Define New/Custom Responsibility
3. Define Data Groups
4. Define Concurrent Managers
5. Define Printers
6. Test Network Preferences
7. Define/Add new Modules
Role of an Apps DBA:
1. Installing of Application
2. Upgradation
3. Migration
4. Patches
5. Routing maintenance of QA
6. Cloning of OA

What are Flexfields?

A Flexfield is a customizable field that opens in a window from a regular Oracle Applications window. Defining flexfields enables you to tailor Oracle Applications to your own business needs.

By using flexfields, you can:

(a) Structure certain identifiers required by oracle applications according to your own business environment.

(b) Collect and display additional information for your business as needed.

Key Flexfields: You use key flexfields to define your own structure for many of the identifiers required by Oracle Applications.

Profile – „Flexfields: Open Key Window‟ (FND_ID_FLEXS)
Descriptive Flexfield: You use descriptive flexfields to gather additional information about your business entities beyond the information required by Oracle Applications. Profile – Flexfields: Open Descr Window” (FND_DESCRIPTIVE_FLEXS)

Value Sets in Oracle Apps?

Oracle Application Object Library uses values, value sets and validation tables as important components of key flexfields, descriptive flexfields, FlexBuilder, and Standard Request Submission. When you first define your flexfields, you choose how many segments you want to use and what order you want them to appear. You also choose how you want to validate each of your segments. The decisions you make affect how you define your value sets and your values.
You define your value sets first, either before or while you define your flexfield segment structures. You typically define your individual values only after your flexfield has been completely defined (and frozen and compiled). Depending on what type of value set you use, you may not need to predefine individual values at all before you can use your flexfield.
You can share value sets among segments in different flexfields, segments in different structures of the same flexfield, and even segments within the same flexfield structure. You can share value sets across key and descriptive flexfields. You can also use value sets for report parameters for your reports that use the Standard Report Submission feature.
Navigation Path: Application Developer -> Application -> Validation -> Set

What are the validation types?

       There are 8 types of validation types
                None (Non Validate at all) (Validation is Minimal)
                Independent (Input must exist on previous defined list of values)
                Dependent (Input is checked against a subset of values based on prior Value).

Table (Input is checked against a subset of values in an application table)
Special (advanced) (Value set uses a flexfield itself)
Pair (advanced) (Two Flexfields together specify a range of valid values)
 Translatable Independent (Input must exist on previous defined list of values. Translated value can be used)
Translatable Dependent. (Input is checked against a subset of values based on a prior value; translated value can be used)
(Note: When you first define your flexfields, you choose how many segments
you want to use and what order you want them to appear. You also
choose how you want to validate each of your segments. The decisions
you make affect how you define your value sets and your values.)

How will you migrate Oracle General Ledger Currencies and Sets of Books Definitions from one environment to another without reKeying? Will you use FNDLOAD?

 FNDLOAD can not be used in the scenario. You can use migrator available in "Oracle iSetup" Responsibility.

Which responsibility do you need to extract Self Service Personalizations?

                Functional Administrator

What is template Form?

a) The TEMPLATE form is the required starting point for all development of new Forms.

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, 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 without 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).

What are ad-hoc reports?

Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a particular purpose. For example, ad hoc tax codes or an ad hoc database query.

What is responsibility?

Is collection of menus, request security groups and data groups.

Menus: collection of forms is nothing but menus

Request security groups: collection of programs.

Data groups: is a group of modules to be made accessible by the user through Responsibility

System admin

What is multi org?

“Legal entity has more than one operating unit is called as multi org”

a) Business group --- Human resources information is secured by Business group

b) Legal entity. --- Inter-company and fiscal/tax reporting.

Operating unit.

c) Operating unit --- secures AR, OE, AP, PA and PO Information.

d) Organizations --- is a specialize unit of work at particular locations.

In which tables Flexfields are stored?

A) FND - ID - FLEXS

B) FND-ID-FLEX-STRUCTURES

How to get second parameter value based on first parameter?

$FLEX$.<Value set name> in the where condition

How to call WHO columns into the form

   By using FND_STANDARD API‟S

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

FND_STANDARD.FORM_INFO Provides information about the form. Should be called form when_new_form - instance - instance trigger.

FND_STANDARD.SYSTEM_DATE This is a function which returns date. Behave exactly like SYSDATE built-in.

FNID_STANDARD.USER This is a function which returns varchar2 Behaves exactly like built in USER.

How to write to a file through concurrent program?

* By using FND_FILE package and it can be used only for log and output files.

1. FND_FILE.PUT

- This is used to write text to a file without a new line character

- Multilane calls to FND_FILE.PUT will produce consummated text.

Procedure FND_FILE.PUT (which IN Number, Buff IN varchar2);

- can be FND_FILE.LOG or FND_FILE.OUTPUT.

2. FND_FILE.PUT_LINE 11

- this procedure as used to write a line of text to a file followed by a new line character.

Procedure FND_FILE.PUT_LINE (which IN number, buff IN varchar2);

Example: FND_FILE.PUT_LINE( FND_FILE.LOG, find_message_get);

3. FND_FILE.NEW_LINE

- this procedure is used to write line terminators to a file

Procedure FND_FILE.NEW_LINE (which IN number LINES IN NATURAL:=1);

Example: to write two newline characters to a log file

Fnd_file.new_line (fnd_file.log,2);

4. FND_FILE.PUT_NAMES

- This procedure as used to set the temporary log file and output filenames and the temporary directory to the user specified values.

- This should be called before calling my other FND_FILE procedure and only once per a session.

How do you find that muliorg is installed?

- Multi organization architecture is meant to allow muliple companies or subsidiaries to store their records within a single data base.

- Multiple organization Architecture allows this by partitioning data through views in APPS schema.

- Implementation of Multi org generally includes more than one business group.

* To know whether multiorg is existing or not

Select multi_org_flag

Form fnd_product_groups)

- If the result is „Y‟ means the database is group for multiorg.

What is a Data Group?

- A data group is a group of oracle applications and the Oracle ID‟s of each application

- Oracle ID grants access privileges to tables in an Oracle Database

- Data group determines which Oracle Data base accounts a responsibilities form, concurrent programs and reports connect to.

What are security attributes?

Security Attributes are used by Oracle self service web Applications to allow rows of data to be visible to specified users responsibilities based on the specific data contained in the row.

What are the types of Concurrent Managers?

       There are 3 types Concurrent Managers

                1. Internal Concurrent 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 incompatibility checks.

We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

What is the relation between Responsibility, Menu and Request Group?

Responsibility: A responsibility is a set of authority in Oracle Apps that lets users access only that functionality of the application appropriate to their roles.

Menu: A menu is a hierarchical arrangement of functions and menus of functions that appears in the Navigator. Each responsibility has a menu assigned to it.

Request Group: it is a collection of reports or concurrent programs. A system Administrator defines report groups in order to control user access to reports and concurrent programs. Only a system administrator can create a request group.

What is a function, how to create one?

A function is apart of an application’s functionality that is registered under a unique name for the purpose of assigning to it to, or excluding it from, a menu (and by extension, responsibility). There are several types of functions: - Form Functions, SubFunctions, and Non-form functions. We often refer to a form function simply as a form.

When is Custom.pll used?

Custom.pll is used while making new or customizing standard oracle forms in apps. It contains all the forms libraries for apps.

What are profile options; at what levels can these be set?

A user profile is a set of changeable options that affects the way the applications run. Oracle Applications object Library establishes a value for each option in a user’s profile when the User logs on or changes responsibility.

System Profile: Profile option can be set for the user community.

User Profile: Provide Oracle Apps with standard information which describes a user, Application, Responsibility and site. At each profile level user profile options can be set.

Where are Views and Procedures created?

                Views: Views are to be created only in APPS.

Procedures: In custom schema and the grant it to APPS schema.

How do you register a report? Explain passing of parameters between a concurrent program Definition and report?

After developing the report (.rdf), FTP it to the UNIX server.

Define executable.

Define concurrent program and attach the executable.

Attach the concurrent program to a request group.

What is the approach to create a new form to be used in Oracle Apps?

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

Developing each new form by copying the TEMPLATE.fmb file, located in

$AU_TOP/forms/US (or your language and platform equivalent), to local directory and

Rename it as appropriate.

Within a PL/SQL procedure which API is to be used to extract a profile value?

FND_PROFILE.GET

How do you set the operating unit context in a report?

Begin

   Dbms_application_info.set-client-info(<Organization_Id>);

End;

Explain how to generate a trace file for a pl/sql concurrent program for tuning?

Check the “Enable Trace‟ check box in concurrent program registration window

How do you “write” to the concurrent request Log and Output file?

FND_FILE.PUT(FND_FILE.LOG or FND_FILE.OUTPUT, <Text>);

What is the difference between Operating Unit and Inventory Organization?

Operating Unit: An Organization that uses Oracle Cash management, Order management and Shipping Execution, Oracle Payables, Oracle Purchasing, and Oracle Receivables. It may be a sales Office, a division, or a dept. An operating unit is associated with a legal entity. Information is secured by operating unit for these applications. Each user sees information only for their operating unit. To run any of these applications, you choose a responsibility associated with an organization classified as an operating unit.

Inventory Organization: An organization for which you track inventory transactions and balances, and/or an organization that manufactures or distributes products. Examples, include (but are not limited to) manufacturing plants, warehouses, distribution centers, and sales offices. The following applications secure information by inventory organization: Oracle inventory, Bills of Material, Engineering, and Work in Process, Master Scheduling/MRP, Capacity, and Purchasing receiving functions. To run any of these applications, you must choose an organization that has been classified as an inventory organization.

What is Set of Books?

A financial reporting entity that uses a particular chart of accounts, functional currency, Accounting conventions and accounting calendar. Oracle General Ledger secures transaction information (such as journal entries and balances) by set of books. When you use Oracle General Ledger, you choose a responsibility that specifies a set of books. You then see information for that set of books only.

What is Inventory Master Organization?

Items are defined in an Inventory Master Organization.

What is the difference between key flexfield and Descriptive flexfield?

Key Flexfield is used to describe unique identifiers that will have a better meaning than using number IDs. E.g. a part number, a cost centre etc Desc Flex is used to just capture extra information. Key Flexfields have qualifiers whereas Desc Flexfields do not. Desc Flexfields can have context sensitive segments while Key flexfields cannot. And one more difference that KFF displays like text item but DFF displays like [ ].

Which procedure should be used to make the DFF read only at run time?

FND_DESCR_FLEX.UPDATE_DEFINITION().

Which procedure should be called to enable a DFF in a form?

FND_DESCR_FLEX.DEFINE (BLOCK => 'BLOCK_NAME' ,FIELD => 'FORM_FIELD_NAME' ,APPL_SHORT_NAME => 'APP_NAME' ,DESC_FLEX_NAME => 'DFF_NAME' );

What is the difference between flexfield qualifier and segment qualifier?

Flexfiled qualifier identifies segment in a flexfield and segment qualifier identifies value in a segment.

   There are four types of flexfield qualifier

1) Balancing segment qualifier

2) Cost center

3) Natural account and

4) Intercompany

Segment qualifier:

   There are five types of segment qualifier

1) Allow budgeting

2) Allow posting

3) Account type

4) Central account and

5) Reconciliation flag