How can we Integrate OracleApps with Custom code by using public API’s? What skill is required for E-Business Suite Integration with API’s?

These are the question asked by someone who never exposed to OracleApps development environment.

This was topic I discussed with client IT department folks which includes people who recently inducted in Oracle Group from other technology area, college pass outs and other group like Middleware who are also responsible to putting some nut and bolts in Oracle.Thoughts to convert this in post for newbie and Inhouse IT folks who always struggling for right information for the API Integration.So here to go:

Important Points for those working first time with API’s

• First and foremost Important, you need to understand API Definition, which includes purpose, input parameter, output parameter, in/out parameter as well as default values for parameter
• Next is mapping : This is where you map input data from source/staging/temp table to input parameter
• Next is development of code , you should use cursor/temporary table to load data from user’s table and use Debugging information to detect and diagnose errors.

Read the rest of this entry »

I was recently asked by a reader if I would be willing answer his questions, which was :

“Most of us are / have had worked with API’s & Open Interfaces. Both do the similar job, but have we ever wondered why would we use API’s when it can be done using Open Interface or vice versa? And, which is better” ?

….So, here you go.

..My dear friend, there is hardly any difference the way both is working,do check my previous post. Anyway here is back to basic :

What are Open Interfaces?
The term Open Interfaces actually refers a programming interface, usually a database table, that automates the execution of Oracle APIs.

Open Interfaces provide a single, simple interface for a specified business procedure.

What are the Oracle APIs?

These are called as a collection of “Black Box” interfaces that provide programatic 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.

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 machism to be provided to handle such situation.
• That requires custom error handling routine.
• This may requires lot more effort as these need fine grain control approach.

Remember, the APIs are also used by the front end screens, and in the same way, will require all the appropriate prerequisites to be implemented.

Important to note, you cannot use APIs as an alternative to implementation.

Releated Post:

• Data Conversion,Migration and interface ..Why important
• EBS - Integration/Interface options
• The world of Oracle API
• E-Business Suite Integration Repository

There are 3 types of APIs exist in EBS.

Private APIs : Private API’s are one which Oracle normally using internal, development purpose only. Details are not provided to anyone outside of the immediate development environment, nor are they intended for use by anyone outside of the e-Business Suite development environment.

Public APIs : These are designed for customers and Oracle consultants to integrate non-Oracle systems into Oracle e-Business Suite or to extend the functionality of the base products. Oracle does not support public APIs unless they are published in a reference manual.

Public, published APIs : These are one which Oracle guaranteed to remain valid from release to release, and patches will not alter the API behaviour. Public, published APIs are supported by Oracle to the same extent as released software.

Is there any way find out whether a standard API is PUBLIC or not in Oracle Application?

Yes, there is way, what you have do ,once you are able to find the information for API from irep, the next you have to find the file name and then you need to pull all information from specification header to know which one is public.

Take a simple case, you need to find API FND_USER_PKG which is defined in file AFSCUSRB.pls

logon to Unix box, and release this sort of command

grep -i public $FND_TOP/patch/115/sql/AFSCUSRB.pls

Based on the above result one can determine whether API is PUBLIC or not.

Simple example for checking AR Public APIs for finding the status

grep -i public $AR_TOP/patch/115/sql/ARXPRELB.pls
grep -i public $AR_TOP/patch/115/sql/ARXPRELS.pls
grep -i public $AR_TOP/patch/115/sql/ARXPRECS.pls
grep -i public $AR_TOP/patch/115/sql/ARXPRECB.pls

Important to Note:

For non-published APIs, Oracle expressly does not provide any guarantees regarding consistency of naming, usage, or behaviour of any API (public or private) between releases.

It Might be possible that a patch could alter any characteristic of any non-published e-Business Suite API.

Where are APIs located ?

For Oracle release 10.7, the APIs are located in the operating system directories such as:

$APPL_TOP/patchsc/107/sql

For Oracle release 11 and release 11i, the APIs are located in the operating system directories:

$APPL_TOP/patch/xxx/sql

where xxx represents the release 110 or 115.

Is there any tracking mechanism for API versions in different Applications releases?

As confirmed by some time back by Oracle support team , there is no such database object in Oracle Applications that keep such kind of information.

All APIs are owned and managed by different product groups within Oracle.

Normally each release comes with either product update notes, or and “About” note. You would need to review these documents for each E-Business Product.

The most comprehensive are the family pack “About” notes, as they in turn reference each individual product “About” note, which lists things like “Changes”.

Importing of Asset information into the Oracle Assets module is achieved by the transfer of the following five segments of data:

1. Adjusted current earnings (ACE) information
2. Budget data
3. Mass additions of Assets
4. Production Information which is typically related to depreciation
5. Physical Inventory data.

Each of the procedures are described in detail below.

Budget Data

The budget information can be entered manually, or it can be maintained in another system and the information uploaded using the budget interface. The budget information is prepared and analyzed on any feeder system and then automatically transferred into Oracle Assets. This information can be used to project depreciation expense for the capital budgets and to compare actual and planned capital spending in Oracle Assets.

• Manual Loading of the Budget Data

Manual loading of Budget Data is achieved by the following five step process: Open the Capital Budgets window. Choose the budget Book, asset Category, and general ledger Expense Account for which you want to budget. Enter or update the budget amounts for this period. The budget amount is the amount you plan to spend on new assets in this category in this period for this expense account. Save your work. Review the capital budget for the year using the Budget Report

• Automatic Loading of the Budget Data:

Transfer of Budget information is achieved by transferring the data using a file transfer method. The Budget information from various systems is first copied into an ASCII file which is transferred into the Budget interface file FA_BUDGET_INTERFACE. This file is used to load the Budget data into the Oracle Assets system.

Uploading budgets from other systems (such as a spreadsheet on a personal computer) into Oracle Assets is a five step process. A file transfer program is used to upload the ASCII budget file from any personal computer to the computer where Oracle Assets resides. SQL*Loader is then used to move the budget data into the Budget Interface. The Upload Capital Budget window is used to move the budget into the Budget Worksheet. ‘Delete Existing Budget’ needs to be checked if replacing an existing budget. The Capital Budgets window can be used to review or change the budget. The Capital Budget window is then used to move the budget into a budget book.

ACE Information

Oracle Assets looks at the asset’s financial information in either the Alternate Minimum Tax(AMT) or the federal book to determine new ACE information. If an asset is depreciating in the federal tax book using ACRS, Oracle Assets uses the federal book information. If the asset is depreciating in the federal book using MACRS, Oracle Assets uses the AMT book information. Oracle Assets automatically updates your assets when you run the Update ACE Book program.

For transferring the ACE information from the legacy system, it can either be entered manually, or it can be calculated by the Oracle Assets program. The difference is the process of populating the ACE interface table.

• Automatic population of the ACE conversion table

Manually, create an ACE tax book with ACE depreciation rules. Using the Mass Copy program, the assets are copied into the ACE book from the corporate book. The ACE conversion table (FA_ACE_BOOKS) is populated using the Populate ACE Interface Table program. The Update ACE Book program is then run to update the information in the ACE conversion table.

• Manually Loading the ACE conversion table

Manually, create an ACE tax book with ACE depreciation rules. Using the Mass Copy program , the assets are copied into the ACE book from the corporate book. The ACE conversion table is populated by loading the table manually with the ACE information from the previous system. The Update ACE Book program is then run to update the information in the ACE conversion table.

Mass Addition of Assets

The mass additions process lets the addition of new assets or cost adjustments from other systems to your system automatically without reentering the data. For example, new assets can be added from invoice lines brought over to Oracle Assets from Oracle Payables, or from CIP asset lines sent from Oracle Projects. Oracle Assets is already integrated with Oracle Payables; and it can easily be integrated with other payables systems. The mass additions process can also be used to convert assets from an outside system to Oracle Assets. Assets data can be transferred in one of the following three methods, depending on where the source is located.

• Creating Assets From Oracle Payables

The Create Mass Additions program creates mass additions from invoice information in Oracle Payables. The concurrent process places the new mass additions in a holding area (the table FA_MASS_ADDITIONS) interface tables, so that the mass additions are reviewed and approved, before they become asset additions.

• Creating Asset Additions From Another Payables System

To integrate Oracle Assets with another system, a program is created to add mass additions to the FA_MASS_ADDITIONS table. This new Custom Concurrent process has to be defined and added to the Oracle Assets menu in order to run it, when needed.

• Converting Assets From Other Systems

Oracle Assets allows the conversion of assets data from non-Oracle asset systems by using mass additions. Instead of loading the asset information into multiple Oracle Assets tables, the information is loaded into the FA_MASS_ADDITIONS table and the mass additions process is used to simplify the work.

Production Information

Typically, production information relates to the depreciation of assets in a production environment, where assets are depreciated depending on the number of units produced by the asset.
The production information can be entered manually, or it can be maintained in another system and the information uploaded into the Oracle Assets, using the production interface. Oracle Assets uses the information to calculate the depreciation of the units of production assets.

• Automatic Loading of the Production Information

The automatic loading is achieved in the following 4-step process:

1. An import program or utility is used to export data from the feeder system to populate the FA_PRODUCTION_INTERFACE table.
2. The Upload Production program is run to move the production information into Oracle Assets.
3. The Production History report is run to review the status of all imported items.
4. The Periodic Production window can be used to review or change the production information.

• Manual Loading of the Production Information

The manual entry involves the following tasks: Open the Periodic Production window; Find assets within a corporate Book for which you the production information is being entered; Enter the from and to date and the total Production for an asset; Optionally enter production for multiple non-overlapping date ranges within a single period; Save your work.

Physical Inventory

Physical inventory is the process of ensuring that the assets a company has listed in its production system match the assets it actually has in inventory. A company takes physical inventory by manually looking at all assets to ensure they exist as recorded, are in the appropriate locations, and consist of the recorded number of units. The Physical Inventory feature in Oracle Assets assists in comparing and reconciling your physical inventory data. The physical inventory data can be loaded into Oracle Assets using one of the following methods:

• Importing data from an Excel spreadsheet using ADI

The physical inventory data is entered into an Excel spreadsheet and exported to Oracle Assets using the Applications Desktop Integrator (ADI). ADI is a spreadsheet-based application that allows the user to format data inside a Microsoft Excel spreadsheet and then upload into Oracle Applications. ADI uses Wizards and Templates to simplify the data entry.

• Entering data in the Physical Inventory Entries window

Data for each asset is entered directly into Oracle Assets using the Physical Inventory Entries window. The limiting factor is that data for only one asset can be entered at a time when using this method

• Importing data from a non-Oracle system using SQL*Loader

SQL Loader is used to import the Physical inventory data in the following way: A single interim table it be used, if possible. Multiple tables may be used if the data exists in multiple tables or files in the system from which data is being loaded. In either case, the data must eventually be placed in a single table, the FA_INV_INTERFACE table. If preferred, data can be loaded directly into the FA_INV_INTERFACE table, but it is more difficult due to the complexity of the table SQL*Loader is used to import information from outside the Oracle database. SQL*Loader accepts a number of input file formats and loads the physical inventory data into the interim table. If the data already resides within an Oracle database, there is no need to use SQL*Loader. The physical inventory information is consolidated in the interim table using SQL*Plus or imported by any other method.

Here is reference note for API table definitions based out of my for previous article for Oracle API Availability -Oracle Assets (FA).

FA_ACE_BOOKS

FA_ACE_BOOKS, the ACE conversion table, is organized into the following columns which store ACE information:

 

FA_BUDGET_INTERFACE

FA_BUDGET_INTERFACE, the budget interface table, is organized into columns in which Oracle Assets stores budget information.

FA_INV_INTERFACE

To use the Physical Inventory feature in Oracle Assets, you must load physical inventory data that you have collected into the FA_INV_INTERFACE table in Oracle Assets.

FA_PRODUCTION_INTERFACE

FA_PRODUCTION_INTERFACE, the production interface table, is organized into columns in which Oracle Assets stores production information. Enter values for the following required columns

Name Null? Type
ASSET_NUMBER NOT NULL VARCHAR2(30)
PRODUCTION NOT NULL NUMBER
START_DATE NOT NULL DATE
END_DATE NOT NULL DATE

Some time back in last post we have already seen the definition of interfaces and there types.Just to summarize here are the high points for interface :

• Import data from any environment , including own system or old system or from data collection device or external system or any others in the easier way possible,
• This consist of review the result of import run which as whole include verification ,validation, review, reimport and error identification

Guess the next question in your mind will be what are the options available for interfacing with oracle ERP or any other ERP? The way integration and interfacing works in typical commercial application have almost similar in nature. Lets emphasize some options that technology offer in Oracle application. But the option will be drived on the basis of nature of system and process how its works , moreover Cost is yet another driving factors. From the integration and interfacing these important factors are :

Is this really driving the integration decisions for choice.? …The answer is YES, on the top of other factors like cost, support etc…etc.But how…lets understand what is really means and how they really affect in :

• Frequency - The frequency is important in term of running the interface. Many times a interface need to run during month end processing, many times it runs in nightly basis as most of airlines industry updations happen in night only, or may be weekly only.
• Batch - It should be real time, one time in day or one in batch …somehow it is linked with Frequency.
• Volume - This is yet another important to understand what volume data need to interface. This is very very important which is affect how technology is helpful in handling such a big volume.
• Technology : This is based out of all above three factors ,
• Accessing data :What access is there to the data? Typically points considered here are like;
  • One way sync (Read Only)
  • vs. bi-directional (Read Write)
  • vs. access in place

Under these set of schema , a developer and designer have to take a call which options should he/she can go for interfacing?? Lets take some of the options :

Before exploring the some options, note that integration is broadly categorize into two types:

• Data Integration
• Application Integration

We are not going to discuss details her, lets take some of the options as for integration and interfacing in EBS.

Options 1: Open Interface Table (OIT)?

This is the the most standard way for doing interfacing in oracle application till day.This consist of developed interface table and programs. The underline interface table allow you to load data and then call concurrent program if any else use the standard AOL API’s and have a custom concurrent program. The standard/custom concurrent program will take data from the interface table validate it and the data into application base products table.

This kind of Open Interface Table satisfy in most of time, as design and development does not requires much efforts.In oracle each product has number of OIT available to cater most of interfacing/integration need.

Options 2 : Application Programming Interface(API’s)

Many time we need a tighter integration to oracle application that can be provided with interface table. In those cases, Oracle Application API’s are indeed a good Options .APIs allow for tight integration into oracle application product. There are also 3rd party products that need to bolt-on into oracle apps with its integration capability. A typical API’s works as figure in the right.

Options 3 : Enterprise Application Integration Tools (EAI) /Middleware Tools

When there is no standard Oracle process (no interface tables) as well no API’s to import data.In that case, you have to write a program that inserts data directly into the Oracle Base Tables.Though this is not recommended by oracle as it any changes from oracle standard functionality may malfunction during patch or upgrade version. So there is another options where we can use some EAI or middle ware tool . EAI/Middle ware tools typically works as adaptor or connector which then take care of any insert /update in oracle base table without any risk.There are number of adapter available to integrate with oracle application.

These tools normally provide robust visual development GUI as well as have strong functionality to provide integration monitoring and alerting tools. I personally know Taviz( How good this is..i really like this ..sorry guys i am not sells person but Still i will recommode). Normally these tools are higher-quality than a options discussed in OIT or API’s.

There are EAI vendors which has capability with interface and integrate with oracle application. Some of them are oracle cerified partners.

• Cast Iron
• WebMethods
• Tibco
• Informatica
• Blue Wolf
• Taviz(formerly SmartDB)
• Crossroads
• SeeBeyond (formerly STC)
• Vitria

Normally most of EAI products provide a tool called “Studio” which uses a graphical environment for you to design integration process flows called orchestrations (integrations). Integrations are deployed to a provided ‘Application Router’, which is an integration appliance pre-configured before it is installed at the customer’s site.Provides native ‘Connector’ to Oracle E-Business Suite.

Options 4 : EDI

EDI (Electronic Data Interchange) uses industry standard data definitions(US/ANSI/X.12) for transmission of documents such as PO’s, Invoices, Sales Order, etc.Oracle provides some EDI transactions through EDI Gateway.This is also a good options. More details can be found in my last post.1.2

A bit on other options

Options 5 : BPEL Process Manager

• BPEL stands for Business Process Execution Language (This was developed by IBM, BEA, and MSFT)
• Development tools and server acquired with Oracle’s purchase of Collaxa Inc.
• Requires extra licenses beyond Oracle application licenses
• Uses industry standards, web services, and business process design to orchestrate integration development
• Marketed very heavily by Oracle as their current and future integration standard

It is typically as similar to EAI but i personally found bit simpler. It Provides “Dashboard” for server maintenance and monitoring and moreover this is exposes Oracle APIs as web services. Only drawback is extra license and relatively higher learning curve may lead to higher initial costs

Options 6 : other Oracle Technology

By means of making a either PL/sql or stored procedure or hybrid process.

• PL / SQL
  • Oracle Corporation’s proprietary server-based procedural extension to the SQL database language
  • Relatively sophisticated procedural programming language
  • Compiled to DB; allows for syntax checking and high-performance DB integration
  • Myriad of purposes in Oracle applications: application logic, integration logic, exception handling
  • Exposes an API layer to Oracle application logic
• Stored Procedures
  • Can be implemented as server-side Java
  • Capable of calling PL/SQL using JDBC; capable of being called by PL/SQL
  • Higher computational performance than PL/SQL
  • Allows for use of an industry standard technology instead of proprietary PL/SQL
  • Often coupled with PL/SQL as a hybrid approach to Oracle application integration
• Oracle Advanced Queuing (AQ)
  • Database Integrated Messaging
  • Integrated data, message, and message history
  • Recoverability(any point in time recovery)
  • Auditing,Tracking, Non-repudiation
  • Business Intelligence on message store
  • High Performance
  • Integrated with MQSeries via Oracle Messaging Gateway

Will take some deep drive for options 4 and options 5 in some other post.

What is FND_FILE

An API that contains procedures to write text to log and output files which can be viewed once the Concurrent program get completed in output or log . These procedures can only be used within PL/SQL Procedures which, are concurrent programs.

Internally its uses the oracle supplied database package UTL_FILE

• utl_file_dir parameter must be set to include the log and out directories for the applications that will run concurrent requests using FND_FILE
• Database owner must have write privileges on those directories
• Take a note that FND_FILE supports a maximum buffer line size of 32K for both log and output files.

Here are the list of Options available within API.

• FND_FILE.PUT

Useful for writing text to a file without a new line character. Multiple calls will concatenate the text .
Does not include new line character

FND_FILE.PUT (which IN NUMBER, buff IN VARCHAR2)
Which - Means LOG file or OUTPUT file.

• FND_FILE.LOG
• FND_FILE.OUTPUT

Buff - Means text to write.

Usage
FND_File.Put(FND_File.Log, ‘This text written to Log file’);

FND_File.Put(FND_File.Output, ‘This text written to Log file’);

• FND_FILE.PUT_LINE

This is used to write a line of text to a file followed by a new line character

FND_FILE.PUT_LINE (which IN NUMBER, buff IN VARCHAR2)

Usage
FND_File.Put_Line(FND_File.Out, ‘This text written to Out file’);

• FND_FILE.NEW_LINE

FND_FILE.NEW_LINE( which IN NUMBER, LINES IN NATURAL:= 1)
Writes line terminators (new line characters) to a file.
Eg. FND_FILE.NEW_LINE(FND_FILE.LOG,2)

• FND_File.PUT_NAMES

This is used to sets temporary log and out file names and directory, which normally used during test and debug phase.

FND_FILE.PUT_NAMES(p_log in varchar2,
p_out in varchar2,
p_dir in varchar2)
Usage
FND_File.Put_Names(‘Order123.log’, ‘Order123.out’, ‘/usr/temp/outfiles’);

Example
BEGIN
fnd_file.put_names(’test.log’, ‘test.out’,'‘/usr/temp/outfiles’’);
fnd_file.put_line(fnd_file.output,’Called stored procedure’);
/* add logic, etc… */

fnd_file.put_line(fnd_file.output, ‘transaction locked at point A’);
/* More logic, etc… */

• FND_FILE.CLOSE
  This will closes open files.

This is normally used only during test and debug phase

Usage:
FND_File.Close;

Many times, we need to submit concurrent requests and request sets from non SRS window like operating system or PL/sql . In Oracle Applications there are API’s to take these options. Here are some of the API’s function and usage..

• FND_REQUEST
  • This API is used Submit concurrent requests from backend. As we know there are couple of options avaible in oracle application to submit a concurrent requset like SRS, CONSCUB and FND’s API. This is API offers some great functionality to use via PL/sql or any other technology like , Host Program or java program.
• FND_CONCURRENT
  • Retrieve information about concurrent requests
  • Set information about concurrent requests
    
• Set_Options ·
  • This is a function of boolen type
  • Set request options
  • This is Call before submitting the request

usage:

FND_REQUEST.SET_OPTIONS(IMPLICIT = > ‘ERROR’);

• Set_Repeat_Options ·
  • Function, returns TRUE or FALSE
  • Set repeat options
  • Call before submitting the request
    
• Set_Print_Options ·
  • Function, returns TRUE or FALSE
  • Set print options (note, some options can not be over-ridden)
  • Call before submitting the request

Called before submitting request if the printing of output has to be controlled with specific printer/style/copies etc., Optionally call for each program in the request set.

Usage :

fnd_submit.set_print_options(NULL,NULL,NULL,TRUE,’N');

or

• fnd_request.set_print_options (printer =>’<printer name>’,
  style =>’LANDSCAPE’,
  copies=>1,
  save_output => TRUE,print_together => ‘N’)

• Add_printer
  • Called after set print options to add a printer to the print list.Optionally call for each program in the request set.
  • Returns TRUE on successful completion, and FALSE otherwise

Usage

Function FND_SUBMIT.ADD_PRINTER (printer =>’Printer Name’,
copies => null) ;

• Submit_Request
  • Function, returns Request Id or 0·
  • If submitting from a form, all Arguments must be specified
    
• Set_Mode (server only)
  • Function, returns TRUE or FALSE
  • Must use if request is submitted from a database trigger.
  • Call before submitting the request
  • Failure in the database trigger call of FND_SUBMIT.SUBMIT_SET does not rollback changes
    
• Get_Request_Status
  • Function returns TRUE or FALSE
  • Returns status information in function output parameters
    
• Wait_For_Request

When submitting concurrent requests using PL/SQL, it is often desired to have the parent process wait until all the child processes have completed before completing itself.

This function Wait for the request completion, then return the request phase/status and completion message to the caller. Also call sleep between database checks.

FUNCTION WAIT_FOR_REQUEST (request_id IN number default NULL,
interval IN number default 60,
max_wait IN number default 0,
phase OUT varchar2,
status OUT varchar2,
dev_phase OUT varchar2,
dev_status OUT varchar2,
message OUT varchar2) return Boolean;

This API Wait for the request completion, then return the request phase/status and completion message to the caller. Also call sleep between database checks.

• Arguments (input)
  • request_id :The request ID of the program to wait on.
  • interval :Time to wait between checks. This is the number of seconds to sleep. The default is 60 seconds.
  • max_wait :The maximum time in seconds to wait for the requests completion.
• Arguments (output) :
  • phase :The user friendly request phase from FND_LOOKUPS.
  • status :The user friendly request status from FND_LOOKUPS.
  • dev_phase :The request phase as a constant string that can be used for program logic comparisons.
  • dev_status :The request status as a constant string that can be used for program logic comparisons.
  • message :The completion message supplied if the request has completed.

Usage

IF request_id = 0 THEN
fnd_file.put_line(fnd_file.log, ‘Request Not Submitted.’);
ELSE

call_status := fnd_concurrent.wait_for_request(request_id, v_interval, v_max_wait, request_phase, request_status, dev_request_phase, dev_request_status, request_status_mesg);

END IF;

IF call_status = TRUE THEN
IF dev_request_phase!= ‘Completed’ OR
dev_request_status IN (’Cancelled’,'Error’,'Terminated’) THEN
DBMS_OUTPUT.PUT_LINE(’STATUS=JOB FAILED’);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(’WAIT FOR REQUEST FAILED - STATUS UNKNOWN’);
DBMS_OUTPUT.PUT_LINE(’STATUS=JOB FAILED’);
END IF;

• Set_Complete_Status (server only)
  • Function, returns TRUE or FALSE
  • Called from a concurrent program to set its own completion status
  • These have three status
    • NORMAL
    • WARNING
    • ERROR
• Add_notification
  • Called before submission to add a user to the notify list.
  • Optionally call for each program in the reques set.
  • ReturnsTRUE on sucessful completion, and FALSE otherwise.

Usage
FND_SUBMIT.ADD_NOTIFICATION (user ==>’ANAND’) ;

Make sure the name should be from fnd_user table

• SET_NLS_OPTIONS
  • Called before submitting request to set request attributes.
  • Optionally call for each program in the request set.
  • Returns TRUE on successful completion, and FALSE otherwise.

Usage
FND_SUBMIT.SET_NLS_OPTIONS (language =>’US’,
territory => NULL);

In oracle user Profile functionality is provided in the FND_PROFILE package and the FNDSQF library.

What is inside this API:

• Retrieve user profile values for the current run-time environment
• Set user profile values for the current run-time environment

There are various Objects that can be used with this API’s. These are discussed below:

1. Put :This can be used to put a value to the specified user profile option.

Usage:

• FND_Profile.Put(‘PROFILE_NAME’, ‘New_Value’)
• FND_Profile.Put(’USERNAME’, Usr_Name)
• FND_Profile.Put(’RESP_ID’, Resp_ID)
• FND_Profile.Put(’RESP_APPL_ID’, Resp_App_ID)
• FND_Profile.Put(’USER_ID’, User_ID)

2.DEFINED : this is function returns TRUE if a value has been assigned to the specified profile option.

Usage:

• SELECT fnd_profile.defined(’ACCOUNT_GENERATOR:DEBUG_MODE’) ACC_GEN_DEBUG_SESSION_MODE FROM DUAL;

3.GET :This is used to retrieve the current value of the specified user profile option

Usage :

Different type of options can be retrieved like

• FND_Profile.Get(‘PROFILENAME’, Profile_name);
• FND_Profile.Get(’CONC_LOGIN_ID’, Conc_login_id);
• FND_Profile.Get(’LOGIN_ID’, loginid);

4.VALUE : This is function which returns a character string. Used to retrieve the current value of the specified user profile option.

Usage:

• fnd_profile.value(‘PROFILEOPTION’)
• fnd_profile.value(’MFG_ORGANIZATION_ID’)
• fnd_profile.value(’login_ID’)
• fnd_profile.value(’USER_ID’)
• fnd_profile.value(’USERNAME’)
• fnd_profile.value(’CONCURRENT_REQUEST_ID’)
• fnd_profile.value(’GL_SET_OF_BKS_ID’)
• fnd_profile.value(’ORG_ID’)
• fnd_profile.value(’SO_ORGANIZATION_ID’)
• fnd_profile.value(’APPL_SHRT_NAME’)
• fnd_profile.value(’RESP_NAME’)
• fnd_profile.value(’RESP_ID’)

5.VALUE_WNPS: This is a function, returns a character string. This is Used to retrieve the current value of the specified user profile option without caching it.

6.SAVE_USER :This is function used to save a value for a profile option permanently to the database, for the current user level. It is necessary to explicitly issue a commit when using this function. Returns TRUE if profile option is successfully saved, otherwise FALSE.

7.SAVE :This is function used to save a value for a profile option permanently to the database, for a specified level. It is necessary to explicitly issue a commit when using this function. Returns TRUE if profile option is successfully saved, otherwise FALSE.

Usage

• fnd_profile.save(’GUEST_USER_PWD’, ‘GUEST/ORACLE’, ‘SITE’);

8.INITIALIZE :This is used by internal Applications Object Library to initialize the internal profile information at the level context.
The cache is first cleared of all database options.

Usage:

• fnd_profile.initialize(user_id);

9.PUTMULTIPLE :This is used by internal Applications Object Library to set multiple pairs of profile options and values.

In Oracle application messaging is typically handled in different way. If you are working in Oracle application you may notice different types of alert window you will get depending upon the condition. In oracle application this would be typical handled by whole set of bundle API , which is called as FND_MessageAPI.

Then the very next question is what is FND_MESSAGE

In simple word , Messaging functionality controlled by FND_MESSAGE package and in the FNDSQF library.

What this API’s do

-Manipulate messages which normally defined in the Message Dictionary under application developer responsibility.
-Client and Server-side procedures place messages on the message stack or in the global area.
-Client-side procedures retrieve messages from the stack and display them from the currently running form

Depending upon the development need , lets understand the various function in three different way.

1. Server side
2. client Side

Here are the list of Procedure and Functions which can be used for retrieving and Buffer Messages at Client Side.

1. Set_Name :Retrieves message from Message Dictionary and sets it on the stack
2. Set_String :Procedure, used to place the passed string on the message stack.
3. Set_Token :Substitutes token with specified value
4. Retrieve :Retrieves a message from the server-side buffer, translates and substitutes tokens, and sets message on the stack
5. Get (function) :Retrieves a message from the stack. Returns the retrieved message
6. Clear : Clears the message stack
7. Get_Encoded :This is a function, returns an encoded message from the message stack.
8. Parse_encoded :This is a procedure used to parse the Application Short Name and the message Name out of an encoded message

For the retrieving and Buffer Messages at Server Side here are some standard procedure:

1. Set_Name Sets a message name in the global area without retrieving it from the Message Dictionary
2. Set_Token Adds a token/value pair to the global area without doing the substitution
3. Clear Clears the message stack
4. Get_Number :This is function, returns number of the specified message. Function returns 0 if the message has no number, or the message number is zero. If the message can not be found, function returns NULL
5. Raise_error :This is Procedure that can raises an application error, returning the message information contained in the package variables

In fact for displaying purpose these are the standard procedures and Functions we can use from API, these are mostly used for displaying message at Client Side.

• Error :Displays an error message in a forms modal window.
• Show :Displays an informational message in a forms modal window.:
• Warn (function) :Displays a warning message in a forms modal window. Allows user to accept or cancel the operation. Returns TRUE if user accepts.:
• Question :Displays a message and up to three buttons in a forms modal window. Similar to a Forms Alert.
• Hint :Displays a message in the forms status line.
• Erase :Clears the forms status line.
• Debug :Used for debugging. Display the specified string.
  Example: FND_Message.Debug(’here is error…’);

Apart from this you may also used few other like:

• WORKING :This is used to display a message in a dialog box without any buttons. The dialog box must be closed explicitly with WORKING_CLOSE.
• WORKING_CLOSE This can used to close the dialog box invoked by the WORKING procedure.
• HISTORY :used to display a message in a dialog box, with the History button. If the user presses this button they see all the
  messages on the stack

Is there any way to displaying Messages from Concurrent Programs

Yes, there are two procedure which can be used :

• Show :Displays top message on the stack in the out file. Does not print message number
• Error :Displays top message on the stack in the log file. Prints the message number if it is not zero

Hope this would be great help in understanding and utilizing the functionality. Next will discuss how to implement these.