API Error Handling:
Every API has 3 out parameters as x_return_status, x_msg_count and x_msg_data.
Using these 3 parameters, one can use below code to log or debug error in API's
Every API has 3 out parameters as x_return_status, x_msg_count and x_msg_data.
Using these 3 parameters, one can use below code to log or debug error in API's
dbms_output.put_line (SubStr('x_return_status = '||x_return_status, 1, 255));
dbms_output.put_line ('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line (SubStr('x_msg_data = '||x_msg_data, 1, 255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I ||'.'|| SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;
Explain The API In R12
You can run the following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP:
SQL> select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AP_%API%'
order by
a.owner, a.name;
Overview - API In R12
1) The Application Programming Interface or API is a PL/SQL packaged procedure which can be used as an alternative entry point into the system to the traditional online forms
2) The advantage being that the same logic used by the seeded online forms can also be used by other interfaces into the system, thus ensuring that the integrity of the data remains intact
Calling API
1) Ensure it is appropriate to allow that particular business operation
2) Validate the data passed to the API
3) Each API has a number of parameters, most of them mapped with DB column. Every parameter name starts with p_. If the parameter maps onto a database column, the remaining part of the name is usually the same as the column name
4) When calling the APIs, it is strongly recommended that you use Named Notation, instead of Positional Notation
Private APIs
Private APIs are
for internal, development use 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 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 such as this one. The user accepts all risk and responsibility
for working with non-published public APIs.
Public Published
APIs are guaranteed by Oracle to remain valid from release to release and that
patches will not alter the API behavior. Public, published APIs are supported
by Oracle to the same extent as released software.
For non-published APIs, Oracle expressly does not
provide any guarantees regarding consistency of naming, usage, or behavior of
any API (public or private) between releases. It is also possible that a patch
could alter any characteristic of any non-published e-Business Suite API. As
such, those who choose to use these APIs do so at their own risk. However,
Oracle does attempt to minimize all changes to public APIs, even if not
published.
Note: In Applications
release 11.5.9, many of the Applications' PL/SQL server side APIs have been
enhanced to utilize the pass by reference semantics of PL/SQL. This improves
performance considerably and reduces memory consumption. In the normal
processing case (i.e. success), there is no change of behavior, and callers of
these APIs are not impacted. However, in the case of exceptions, there is a
behavior change which results in assignments being exposed to the caller, which
are made in the API prior to any exceptions being raised. The previous behavior
would rollback these assignments made by the API if an exception occurred in
this API. Developers writing custom extensions to Oracle Applications, or third
party integrators which use the standard Applications' APIs should be aware of
this change in semantics.
Each published API provides an API specification,
and definitions as for its parameters, data structures, and status messages. Sample
scripts and documented process flow diagrams are included where applicable.
Note: The words procedure and API are
used interchangeably in this document.
·
Allows an API to differentiate between changes that
require you to change your API calling code and those that don't.
·
Allows an API to detect incompatible calls.
·
Allows you to quickly determine if calling a new
version of an API requires you to change any of your code.
·
Allows you to easily figure out which version of an
API you need to call to take advantage of new features.
API version numbers consist of two segments
separated by a decimal point. The first segment is the major version number;
the second segment is the minor version number. The starting version number for
an API is always 1.0.
The following table shows an example of an API
Version number and the major and minor version derived from the API version.
API Version Number
|
Major Version
|
Minor Version
|
1.0
|
1
|
0
|
2.4
|
2
|
4
|
If the major version number has changed, then you
probably need to modify your programs that call that API. Major version changes
include changes to the list of required parameters or changing the value of an
API OUT parameter.
If only the minor version number has changed, then
you probably do not need to modify your programs.
The API version number for the APIs included in the
current version of the CZ_modelOperations_pub package is:
1.0
The local constant that stores this version number
is:
l_api_version CONSTANT NUMBER
To detect incompatible calls, programs calling an
API must pass an API version number as one of the input parameters. The API can
then compare the passed version number to its current version number, and
detect any incompatible calls.
The Oracle standard parameter used by all
procedures in this package to pass in the API version number is:
p_api_version IN NUMBER
This parameter is required, and has no initial
values, thus forcing your program to pass this parameter when calling an API.
If your call to the API results in a version
incompatibility, then an error message is inserted in the table CZ_DB_LOGS.
Parameter Specifications
The specifications
for the public APIs provided by the Resource Manager define four categories of
parameters:
·
Standard IN
·
Standard OUT
·
Procedure specific IN
·
Procedure specific OUT
Standard IN and OUT parameters are specified by the
Oracle Applications business object API Coding Standards, and are discussed in
the following sections.
Procedure specific IN and OUT parameter are related
to the API being specified, and are discussed with that individual API.
Standard IN Parameters
1) p_api_version IN NUMBER
This must match the version number of the API. An unexpected error is returned if the calling program version number is incompatible with the current API version number
2) p_init_msg_list IN VARCHAR2
The valid values for this parameter are:
• True = FND_API.G_TRUE
• False = FND_API.G_FALSE
• Default = FND_API.G_FALSE
If set to true, then the API makes a call to fnd_msg_pub.initialize to initialize the message stack. To set to true, use the value, "T". If set to false then the calling program must initialize the message stack. This action is required to be performed only once, even in the case where more
than one API is called. To set to false, use the value, "F".
3) p_commit IN VARCHAR2
The valid values for this parameter are:
• True = FND_API.G_TRUE
• False = FND_API.G_FALSE
• Default = FND_API.G_FALSE
If set to true, then the API commits before returning to the calling program. To set to true, use the value, "T". If set to false, then it is the calling program’s responsibility to commit the transaction. To set to false, use the value, "F".
1) p_api_version IN NUMBER
This must match the version number of the API. An unexpected error is returned if the calling program version number is incompatible with the current API version number
2) p_init_msg_list IN VARCHAR2
The valid values for this parameter are:
• True = FND_API.G_TRUE
• False = FND_API.G_FALSE
• Default = FND_API.G_FALSE
If set to true, then the API makes a call to fnd_msg_pub.initialize to initialize the message stack. To set to true, use the value, "T". If set to false then the calling program must initialize the message stack. This action is required to be performed only once, even in the case where more
than one API is called. To set to false, use the value, "F".
3) p_commit IN VARCHAR2
The valid values for this parameter are:
• True = FND_API.G_TRUE
• False = FND_API.G_FALSE
• Default = FND_API.G_FALSE
If set to true, then the API commits before returning to the calling program. To set to true, use the value, "T". If set to false, then it is the calling program’s responsibility to commit the transaction. To set to false, use the value, "F".
The
following table describes standard IN parameters, which are common to all
public APIs provided by Resource Manager.
Standard IN
Parameters
|
|||
Parameter
|
Data Type
|
Required
|
Description
|
p_api_version
|
NUMBER
|
Yes
|
This must match the version number of the API. An
unexpected error is returned if the calling program version number is
incompatible with the current API version number (provided in the
documentation).
|
p_init_msg_list
|
VARCHAR2
|
Yes
|
The valid values for this parameter are:
·
True =
FND_API.G_TRUE
·
False =
FND_API.G_FALSE
·
Default =
FND_API.G_FALSE
If set to true, then the API makes a call
to fnd_msg_pub.initialize to initialize the message stack.
To set to true, use the value, "T".
If set to false then the calling program must
initialize the message stack. This action is required to be performed only
once, even in the case where more than one API is called. To set to false,
use the value, "F".
|
p_commit
|
VARCHAR2(1)
|
No
|
The valid values for this parameter are:
·
True =
FND_API.G_TRUE
·
False =
FND_API.G_FALSE
·
Default =
FND_API.G_FALSE
If set to true, then the API commits before
returning to the calling program. To set to true, use the value,
"T".
If set to false, then it is the calling program's
responsibility to commit the transaction. To set to false, use the value,
"F".
|
Standard OUT Parameters
1) x_return_status OUT NOCOPY VARCHAR2
Indicates the return status of the API. The values returned are one of the following:
• FND_API.G_RET_STS_SUCCESS
Success: Indicates the API call was successful
• FND_API.G_RET_STS_ERROR
Expected Error: There is a validation error, or missing data error.
• FND_API.G_RET_STS_UNEXP_ERROR
Unexpected Error: The calling program can not correct the error.
2) x_msg_count OUT NOCOPY NUMBER
Holds the number of messages in the message list.
3) x_msg_data OUT NOCOPY VARCHAR2
Holds the encoded message if x_msg_count is equal to one
1) x_return_status OUT NOCOPY VARCHAR2
Indicates the return status of the API. The values returned are one of the following:
• FND_API.G_RET_STS_SUCCESS
Success: Indicates the API call was successful
• FND_API.G_RET_STS_ERROR
Expected Error: There is a validation error, or missing data error.
• FND_API.G_RET_STS_UNEXP_ERROR
Unexpected Error: The calling program can not correct the error.
2) x_msg_count OUT NOCOPY NUMBER
Holds the number of messages in the message list.
3) x_msg_data OUT NOCOPY VARCHAR2
Holds the encoded message if x_msg_count is equal to one
The
following table describes standard OUT parameters, which are common to all
public APIs provided by Resource Manager.
Note: All standard OUT
parameters are required.
Standard OUT
Parameters
|
||
Parameter
|
Data Type
|
Description
|
x_return_status
|
VARCHAR2(1)
|
Indicates the return status of the API. The
values returned are one of the following:
·
FND_API.G_RET_STS_SUCCESS
Success: Indicates the API call was successful
·
FND_API.G_RET_STS_ERROR
Expected Error: There is a validation error, or
missing data error.
·
FND_API.G_RET_STS_UNEXP_ERROR
Unexpected Error: The calling program can not
correct the error.
|
x_msg_count
|
NUMBER
|
Holds the number of messages in the message list.
|
x_msg_data
|
VARCHAR2(2000)
|
Holds the encoded message if x_msg_count is
equal to one.
|
Verify the size of
the column from the base table for that column when passing a parameter of a
specific length. For example, if you pass a NUMBER value, first query to find
the exact value to pass. An incorrect value can cause the API call to fail.
The following
table describes optional IN parameters which are initialized to pre-defined
values representing missing constants. These constants are defined for the
common PL/SQL data types and should be used in the initialization of the API
formal parameters.
Initialized IN
Parameters
|
||
Parameter
|
Type
|
Initialized Value
|
g_miss_num
|
CONSTANT
|
NUMBER:= 9.99E125
|
g_miss_char
|
CONSTANT
|
VARCHAR2(1):= chr(0)
|
g_miss_date
|
CONSTANT
|
DATE:= TO_DATE('1','j');
|
These constants are defined in the package FND_API
in the file fndpapis.pls. All columns in a record definition are
set to the G_MISS_X constant as defined for the data type.
·
Standard IN
·
Standard OUT
·
Mandatory procedure specific IN
·
Procedure specific OUT
If the API
encounters any invalid parameters during the API call, then one of the
following actions will occur:
·
An exception is raised.
·
An error message identifying the invalid parameter
is generated.
·
All API actions are cancelled.
It is mandatory
that every API call pass a version number for that API as its first parameter (p_api_version).
This version number must match the internal version
number of that API. An unexpected error is returned if the calling program
version number is incompatible with the current API version number.
Warning: The currently
supported version at this time is 1.0. Use only this for the API version
number.
In addition, the object version number must be
input for all update and delete APIs.
·
If the object_version_number passed
by the API matches that of the object in the database, then the update is
completed.
·
If the object_version_number passed
by the API does not match that of the object in the database, then an error
condition is generated.
Every API must
return one of the following states as parameter x_return_status after
the API is called:
Note: It is not required
that all status notifications provide a number identifier along with the
message, although, in many cases, it is provided.
Each state can be associated with a status message.
The following table describes each state.
Status Message
and Description
|
|
Status
|
Description
|
S
|
Indicates that the API performed all the
operations requested by its caller.
·
A success return
status may or may not be accompanied by messages in the API message list.
·
Currently, the
Resource Manager APIs do not provide a message for a return status of
success.
|
E
|
Indicates that the API failed to perform one or
more of the operations requested by its caller.
An error return status is accompanied by one or more messages describing the error. |
U
|
Indicates that the API encountered an error
condition it did not expect, or could not handle, and that it is unable to
continue with its regular processing.
·
For example,
certain programming errors such as attempting to divide by zero causes this
error.
·
These types of
errors usually cannot be corrected by the user and requires a system
administrator or application developer to correct.
|
In addition to these three types of possible status
messages, you can also code the following additional message types:
·
Warnings
·
Information
To create a warning message, perform the following
steps:
1.
Create a global variable to be used to signal a
warning condition. For example, this could be similar to the following:
G_RET_STS_WARNING := 'W'
This global variable is not part of the FND_API
package.
2.
Return this value if the warning condition is
encountered. For example, using the same example as in step one, set up the
following code in the API to process the warning condition:
x_return_status := G_RET_STS_WARNING
This code replaces the more usual:
x_return_status := fnd_api.g_ret_sts_unexp_error
for "U"
3.
If desired, perform a similar procedure to create
Information messages.
How to get log messages by using
fnd_msg_pub.initialize API:
FND_MSG_PUB for error logging in Oracle
Applications.
Use the following snipped of code while calling any
seeded API in Oracle Applications, like CRM, or Financials.
Put this block exactly under the section where you
invoke the private or public API. The errors raised in the API are logged in
the following API. Use this code while error logging or while debugging.
Comment out the code before moving into production.
IF ( FND_MSG_PUB.Count_Msg > 0) THEN
FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
FND_MSG_PUB.Get(p_msg_index => i,
p_encoded => 'F',
p_data => out_message,
p_msg_index_OUT => l_msg_index_OUT );
dbms_output.put_line('l_msg_data :' ||out_message);
END LOOP;
END IF;
FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
FND_MSG_PUB.Get(p_msg_index => i,
p_encoded => 'F',
p_data => out_message,
p_msg_index_OUT => l_msg_index_OUT );
dbms_output.put_line('l_msg_data :' ||out_message);
END LOOP;
END IF;
FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Applications:
FND_GLOBAL.APPS_INITIALIZE is used for initializing
the session before calling any public or private API's in Oracle Ebusiness
suite. Its not required for all the API's but its recommended that you set this
profile before making any calls to either private or public API.
Listed below is a sample call to
FND_GLOBAL.APPS_INITIALIZE function
fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>l_resp_appl_id);
resp_id=>l_resp_id,
resp_appl_id=>l_resp_appl_id);
1.
l_user_id is the
fnd user ID which will be utilized during the call.
2.
l_resp_id is the
responsibility ID
3.
l_resp_appl_id is
the responsibility application ID.
You can use either sysadmin or use some user who
has all the above listed responsibilities.
For SYSADMIN, utilize the following query to get
the respective values
select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp
where fnd.user_name = 'SYSADMIN'
and fresp.responsibility_name = 'Order Management Super User';
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp
where fnd.user_name = 'SYSADMIN'
and fresp.responsibility_name = 'Order Management Super User';
Another option is Help > Diagnostics >
Examine and get the values from $profile session values.