This chapter describes the features, parameters, attributes and messages that are part of the Oracle Trading Community Architecture granular APIs.
This chapter covers the following topics:
You must set the Applications Context in a database session, before calling any Trading Community Architecture application programming interfaces (API). The APIs rely on global variables and profiles that are part of this Applications Context. For details on setting Applications Context, please refer to Note 209185.1 on OracleMetaLink (http://metalink.oracle.com).
The TCA API has been designed in a highly modular fashion, giving you code that is easy to understand, easy to maintain and easy to extend.
The modular approach defaults and validates user-entered information, defaults information not provided by the user, and calls the appropriate entity handler to perform the business related tasks.
The APIs provide an extensive set of error-handling and error-reporting mechanisms so that errors encountered in the different phases of API execution are reported and put on the message stack. The calling program has the option of looking up all the error messages or the first error message on the stack. If there is only one error in the message stack, the error is displayed as one of the output parameters of the API routine. You do not have to fetch that message from the stack.
There are three types of information that the TCA APIs provide to their calling programs:
Overall status
Messages describing the operations performed or errors encountered by the API
Output values that the program making the call to the API might need to use
The return status (x_return_status) of the API informs the caller about the result of the operation or operations performed by the API. The possible return status values and their meanings are:
FND_API. G_RET_STS_SUCCESS (PL/SQL API) HzConstant.getGRetStsSuccess() (Java API)
A success return status indicates that the API performed all of the operations requested by its caller. A success return status can be accompanied by informative messages in the API message list.
FND_API. G_RET_STS_ERROR (PL/SQL API) HzConstant.getGRetStsError() (Java API)
An error return status indicates that the API failed to perform some or all of the operations requested by its caller. An error return status is usually accompanied by messages describing any errors and how to resolve them.
In most cases, you should be able to correct normal, expected errors such as missing attributes or invalid date ranges.
FND_API. G_RET_STS_UNEXP_ERROR (PL/SQL API) HzConstant.getGRetStsUnexpError() (Java API)
An unexpected error status indicates that the API encountered an unexpected error condition that it could not handle. In this case, the API cannot continue its regular processing. Examples of such errors are irrecoverable data inconsistency errors, memory errors, and programming errors such as attempting to divide by zero.
In most cases, an end user will not be able to correct unexpected errors. These errors usually require resolution by a system administrator or an application developer.
The APIs put result messages into a message list. Programs calling these APIs can then get the messages from the list and process those messages by issuing them, loading them into a database table, or writing them to a log file.
The APIs store messages in an encoded format so that the API callers can use standard functions provided by the message dictionary to find message names. With the message dictionary you can also store these messages in database tables and generate reports from these tables in different languages.
The API message list must be initialized every time a program calls an API. API callers can either call the message list utility function, FND_MSG_PUB.Initialize, or request that the API do the initialization by setting the p_init_msg_list parameter to T (TRUE).
The program calling the API can retrieve messages from the message stack using the existing FND API functions FND_MSG_PUB.Count_Msg (or standard output parameter x_msg_count) and FND_MSG_PUB.Get. You can see examples of these in the sample code in the appendix.
The TCA APIs perform robust validations. The APIs collect all the validation errors encountered and put them on the message stack. The relevant entity handler is called only if no errors are reported during the validation phases.
The TCA public APIs provide a new locking mechanism for update procedures, based on the new OBJECT_VERSION_NUMBER column, which has been included in all HZ tables. For this reason, OBJECT_VERSION_NUMBER is a mandatory attribute for all update APIs.
The locking mechanism works as follows:
Whenever a new record is created, the value in the OBJECT_VERSION_NUMBER column is set to 1.
Whenever a record is updated, the value in the OBJECT_VERSION_NUMBER column is reset to OBJECT_VERSION_NUMBER + 1.
For records that existed in the HZ tables prior to introduction of this locking mechanism, the API sets the value in the column OBJECT_VERSION_NUMBER to null.
The TCA APIs have been developed to conform to the Oracle Applications API standards. The parameters below are common to all of the APIs. Brief descriptions are provided for some of the important features of the API.
p_<entity>_object_version_number IN/OUT NUMBER Required
This parameter is either called p_object_version_number or p_<entity>_object_version_number. For example, for the hz_party_v2pub.update_organization API, this parameter is called p_party_object_version_number because organization is a type of party. The parameter value must match the version number in the database of the record being updated. An error will be returned if the calling program passes an object version number that is not identical to the one in the database for the existing record.
p_init_msg_list IN VARCHAR2 Optional
The default is FND_API.G_FALSE. If set to true, the API calls fnd_msg_pub.initialize to initialize the message stack. If it set to false, then the calling program must initialize the message stack. The initialization must only be done once in the case where more than one API is being called.
Caution: Do not use the rec.parameter for the OUT parameter. For example, do not use "p_organization _rec.party_rec.party_id", instead use "x_party_id".
The names of all output parameters begin with "x_"
x_return_status OUT VARCHAR2(1)
The Out parameter returns the status of the API. The returned value is one of the following :
FND_API.G_RET_STS_SUCCESS - Success
FND_API.G_RET_STS_ERROR - Expected error, validation or missing data
FND_API.G_RET_STS_UNEXP_ERROR - Unexpected error, cannot be corrected by the calling program
x_msg_count OUT NUMBER
x_msg_data OUT VARCHAR2
The x_msg_count column contains the number of messages in the message list. If the count is one, then the x_msg_data column holds the encoded message.
PL/SQL record types are used in all of the create and update APIs. In some cases, nested record types have been used as well.
For example, in the Create_Person API, the p_person_rec input parameter is of the person_rec_type record type. The person_rec_type has party_rec as one of its elements, which itself is of party_rec_type record type.
The reason for doing this is that the attributes of the Person party type include attributes of a party, because Person is a subtype of Party.
The Create APIs handle both sequence-generated and manually-passed primary keys, which can be generated on a device from an Oracle Mobile application. In order to prevent duplicate primary keys, the Create APIs handle exceptions to the unique key violation.
Optional IN parameters do not have default values. An attribute value that is not passed in is assumed to have a default of null for a Create API. For an Update API, if a particular attribute value is not passed into the API, then the database retains the existing value. In order to set a database value to null, the calling program must explicitly set the attribute value to one of the following constants, based on the data type of the attribute.
FND_API.G_MISS_NUM for NUMBER type.
FND_API.G_MISS_CHAR for VARCHAR2 type.
FND_API.G_MISS_DATE for DATE type.
These are pre-defined values in the FND_API Package (fndapis.pls)
G_MISS_NUM CONSTANT NUMBER:= 9.99E125
G_MISS_CHAR CONSTANT VARCHAR2(1):= chr(0)
G_MISS_DATE CONSTANT DATE:= TO_DATE('1','j') ;
Use the extensive debug messages to trouble shoot in case of unexpected problems. These debugging messages are extremely useful because an API would be difficult to debug otherwise. You can turn on debug messages by the use of a certain profile option. These messages can be written to a log file as well.
The profiles for controlling the debug mechanism are:
Name - HZ_API_FILE_DEBUG_ON
User Profile Name - HZ: Turn On File Debug
Name - HZ_API_DEBUG_FILE_NAME
User Profile Name - HZ: API Debug File Name
Name - HZ_API_DEBUG_FILE_PATH
User Profile Name - HZ: API Debug File Directory
If the HZ_API_FILE_DEBUG_ON profile is set to Y when any APIs are called, then debug messages are written to the file specified in HZ_API_DEBUG_FILE_PATH and HZ_API_DEBUG_FILE_NAME. If the HZ_API_FILE_DEBUG_ON profile is set to N, no debug messages are generated. The value of the HZ_API_DEBUG_FILE_PATH profile specifies a directory file path that the database has write access to, as provided in init.ora. You can find path information by querying: select value from v$parameter where name equals 'utl_file_dir'. If you turn the file debug mode on, but did not set a proper value for the HZ_API_DEBUG_FILE_PATH profile or the HZ_API_DEBUG_FILE_NAME profile is null, the API errors out.
Debug messages accumulate in the debug file. After collecting any debug messages, you must reset the HZ_API_FILE_DEBUG_ON profile back to N. If you do not, you might cause an exceeded file size error.
There are two ways to run APIs in the debug mode:
From Oracle Applications:
You can enable or disable the debug mode by setting HZ_API_FILE_DEBUG_ON to Y or N, respectively. The default value is N. When the profile is set Y, you must set the proper values for the HZ_API_DEBUG_FILE_NAME and HZ_API_DEBUG_FILE_PATH profiles.
With SQLPLUS or server side PL/SQL custom code:
You can enable or disable the debug mode by calling FND_PROFILE API.
This example assumes that the directory, /sqlcom/out/tca115, has write access that is specified by the utl_file_dir parameter in the init.ora for the relevant database.
exec fnd_profile.put('HZ_API_DEBUG_FILE_PATH', '/sqlcom/out/tca115/');
exec fnd_profile.put('HZ_API_DEBUG_FILE_NAME', 'api_debug');
exec fnd_profile.put('HZ_API_FILE_DEBUG_ON', 'Y');
fnd_profile.put('HZ_API_DEBUG_FILE_PATH', '/sqlcom/out/tca115/');
fnd_profile.put('HZ_API_DEBUG_FILE_NAME', 'api_debug');
fnd_profile.put('HZ_API_FILE_DEBUG_ON', 'Y');
This debug strategy is provided as a public utility procedure that you can include in your custom code.
Please refer to the HZ_UTILITY_V2PUB package for further details.
The TCA APIs have been developed to conform to the Oracle Applications API standards. The parameters below are common to all of the APIs. Brief descriptions are provided of some of the important features of the API.
Comments that are the same for PL/SQL API are repeated in this section.
Declare all parameters or member variables as BigDecimal if it is PL/SQL NUMBER type, String if it is PL/SQL VARCHAR2 type, or Timestamp if it is PL/SQL DATE type. To access these parameters or variables you must import
java.math.BigDecimal
java.sql.Timestamp
In PL/SQL the caller's value can be modified by the called procedure, if a parameter is declared OUT or IN OUT. For example, PL/SQL routines calling this procedure:
procedure p1(n1 in out number, n2 number);
might find that the first variable passed to p1 was modified by the time the program's call to p1 is completed, because the parameter is declared IN OUT. The value the programs pass to n2 cannot possibly be modified.
A PL/SQL procedure can declare any parameter to be OUT or IN OUT. The caller must be prepared in case the PL/SQL procedure has modified any data, and that it is therefore part of the value returned from the procedure.
Java has no clear analogue to the concept of declaring parameters as OUT or IN OUT. Instead, in Java, there are certain types of objects that are immutable (changes by the called procedure which cannot be seen by the caller) and other types which are mutable.
For this reason, if there are any arguments to PL/SQL which are OUT or IN OUT, the Java API for it must be of a mutable type. This is why arrays are mutable APIs. For example, the Java API for procedure p1 above might look like:
public static void p1 (BigDecimal [ ] n1, BigDecimal n2) {...}
The first parameter is an array and the second is not, even through, in PL/SQL, both parameters are simply NUMBER. In the case where p1 modifies n1, you can identify the new value. You can get the value by referencing, for instance, n1[0].
OracleConnection _connection Required
This parameter is for passing Oracle JDBC connection to APIs.
BigDecimal [ ] p_object_version_number Required
This parameter is either called p_object_version_number or p_<entity>_object_version_number. For example, for the HzPartyV2Pub.updateOrganization API , this parameter is called p_party_object_version_number because organization is a type of party. The value of p_object_version_number[0] must match the version number in the database of the record being updated. An error will be returned if the calling program passes an object version number that is not identical to the one in the database for the existing record.
String p_init_msg_list Optional
If HzConstant.getGTrue() is set to true, the API makes a call to fnd_msg_pub.initialize to initialize the message stack. If HzConstant.getGFalse() is set to false, then the calling program must initialize the message stack. This initialization is required only once when more than one API is called.
The names of all output parameters begin with "x_"
String [ ] x_return_status
This returns the status of the API. The values returned in x_return_status[0] are one of the following:
HzConstant.getGRetStsSuccess() - Success
HzConstant.getGRetStsError() - Expected Error - validation or missing data.
HzConstant.getGRetStsUnexpError() - Unexpected Error, not fixable by calling program.
BigDecimal [ ] x_msg_count
String [ ] x_msg_data
x_msg_count[0] holds the number of messages in the message list. If the count is one, then x_msg_data[0] holds the decoded message.
PL/SQL record types are used in all Create and Update APIs. In some cases nested record types are used as well. Correspondingly, the Java Inner Class (for example, Record Class) is defined for each PL/SQL record type.
For example, in the HzPartyV2Pub.createPerson() method, the p_person_rec input parameter is of type HzPartyV2Pub.PersonRec. The HzPartyV2Pub.PersonRec parameter has party_rec as one of its elements which itself is of type HzPartyV2Pub.PartyRec.
The reason for doing this is that the attributes of the Person party type include attributes of a party, because Person is a subtype of Party.
The Create APIs handle both sequence-generated and manually-passed primary keys, which can be generated on a device from an Oracle Mobile application. In order to prevent duplicate primary keys, the Create APIs handle exceptions to the unique key violation.
Each record class has two constructors:
Public <entity>Rec();
This constructor initializes all BigDecimal, String and Timestamp attributes to HzConstant.getGMissNum(), HzConstant.getGMissChar(), HzConstant.getGMissDate(). If the record class is nested, then the included record classes will also be initialized as GMissXXX.
HzConstant.getGMissNum() for BigDecimal type.
The application returns 9.99E125.
HzConstant.getGMissChar() for String type.
The application returns "\0".
HzConstant.getGMissDate() for java.sql.Timestamp type.
The application returns "4713-01-01 00:00:00.0"
Public <entity>Rec(boolean __RosettaUseGMISSValues);
This constructor provides two choices. Calling this constructor with a 'true' value corresponds to the first constructor. If we call the constructor by passing 'false', all BigDecimal, String and Timestamp attributes will be initialized to null values. Similarly, this boolean parameter will be passed into included record classes if any is present.
There is a performance difference between passing 'true' or 'false'. If passing 'true', we only transmit to the database the GMiss values for attributes that a user explicitly sets in creating and updating. In client or middle tier, more memory is needed to hold GMiss values, but it causes less network traffic. If passing 'false', we internally convert null value to GMiss in order to call the public APIs and transmit all attributes to the database. This approach can result in less memory consumption in the client, but more network traffic between the middle tier and the sever. You should consider the physical configuration of your application structure as well as how many attributes you display in the UI when you choose a constructor. If you have less than 20% of the attributes exposed in UI, for instance, the first approach, passing 'true' or using default constructor, is more efficient.
Unlike the PL/SQL APIs, Java APIs take whatever values you pass in to create or update records.
Use the extensive debug messages to trouble shoot in case of unexpected problems. These debugging messages are extremely useful because an API would be difficult to debug otherwise. You can turn on debug messages by the use of a certain profile option. These messages can be written to a log file as well.
The profiles for controlling the debug mechanism are:
Name - HZ_API_FILE_DEBUG_ON
User Profile Name - HZ:Turn On File Debug
Name - HZ_API_DEBUG_FILE_NAME
User Profile Name - HZ: API Debug File Name
Name - HZ_API_DEBUG_FILE_PATH
User Profile Name - HZ: API Debug File Directory
If the HZ_API_FILE_DEBUG_ON profile is set to Y when any APIs are called, then debug messages are written to the file specified in HZ_API_DEBUG_FILE_PATH and HZ_API_DEBUG_FILE_NAME. If the HZ_API_FILE_DEBUG_ON profile is set to N, no debug messages are generated.
The value of the HZ_API_DEBUG_FILE_PATH profile specifies a directory file path that the database has write access to, as provided in init.ora. You can find this path information by querying: select value from v$parameter where the name equals 'utl_file_dir'.
If you turn the file debug mode on, but did not set a proper value for the HZ_API_DEBUG_FILE_PATH profile or the HZ_API_DEBUG_FILE_NAME profile is null, the API errors out.
Debug messages accumulate in the debug file. After collecting any debug messages, you must reset the HZ_API_FILE_DEBUG_ON profile back to N. If you do not you might cause an exceeded file size error.
You can call FND_PROFILE.Put to set profile value.
This example assumes that the directory, /sqlcom/out/tca115, has write access, that is specified by the utl_file_dir parameter in the init.ora for the relevant database.
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall(
"begin fnd_profile.put('HZ_API_DEBUG_FILE_PATH', '/sqlcom/out/tca115/'); end;");
ocs.execute();
ocs = (OracleCallableStatement)conn.prepareCall(
"begin fnd_profile.put('HZ_API_DEBUG_FILE_NAME', 'api_debug'); end;");
ocs.execute();
ocs = (OracleCallableStatement)conn.prepareCall(
"begin fnd_profile.put(fnd_profile.put('HZ_API_FILE_DEBUG_ON', 'Y'); end;");
ocs.execute();
You can use the TCA APIs to access data about people after the data is entered and stored using Oracle Human Resources (HR) applications. The security, integrity, and validity of HR data must be maintained due to the sensitivity of personal information.
TCA's Security of HR Person Data feature enables Oracle applications to share data entered and stored with Oracle HR applications. With this feature other Oracle applications can access information about a person without compromising the security, integrity, or validity of the HR information. Although other Oracle applications can access information in the HR tables, only properly authorized users of HR applications can modify data about any person entered by using an Oracle HR application.
The following PL/SQL and Java APIs support third-party data integration:
Party API
Create Organization API
Update Organization API
Create Person API
Update Person API
Location API
Party Site API
Contact Point API
Relationship API
The integration of third-party data, from providers such as D&B, enables users to acquire data from external sources and then use that data to populate the HZ tables. The data in these HZ tables can then be viewed and used across the Oracle e-Business suite.
From an API, you can populate the actual_content_source column to identify the third party data source. The content_source_type attribute is obsolete. You should use the actual_content_source attribute for third- party data integration applications. If necessary, you can use the content_source_type attribute to maintain backward compatibility.
Default values for the actual_content_source and content_source_type attributes are taken from the actual_content_source column, unless you have overwritten the default value in either column.
In the Organization and Person record types; the values in the actual_content_source column default to SST and the values in the content_source_type column default to user_entered.
These APIs first determine the data source based on the values in the actual_content_source and content_source_type columns. If the data source is either SST or user_entered, the API errors out if the same party ID exists in the HZ_PARTIES table. Otherwise, the API will create new party profiles and parties.
If the data source is a third-party data source and if we create profiles for existing parties by passing a party ID, the API errors out if the parties have profiles from the same data source. Otherwise, the API creates the third-party profiles for the parties. If you set up Advanced Third Party Data Integration and select the third party data source as one of the ranked data sources, the API creates or updates the third-party profiles, SST or single source of truth, and denormalizes the SST profiles to HZ_PARTIES. Users can only see SST profiles.
These APIs first determine the data source based on the values in the actual_content_source and content_source_type columns. If the data came from a third-party source, but the user does not have permission to overwrite third party data, then the API triggers an error message; "You do not have permission to overwrite the information in the &COLUMN column, which comes from a third party data source. Please contact your system administrator."
The values in both the actual_content_source and content_source_type columns default to user_entered. Unless the privilege is provided through the user create and update rule for Other entities, users cannot update third-party locations, except for data populated in descriptive flexfields.
The values in both the actual_content_source and content_source_type columns default to user_entered. You can control whether third party data for a site is active or inactive.
The values in both the actual_content_source and content_source_type columns default to user_entered. You can control whether third party data for a contact point site is active or inactive
Phone component information from data sources other than USER_ENTERED cannot be updated if the user does not have update privilege specified through rules. D&B data is always nonupdateable, as update rules cannot be set for PURCHASED systems.
The values in both the actual_content_source and content_source_type columns default to user_entered.
You can control whether third-party data for a contact point site is active or inactive. For example, the value of the actual_content_source attribute is other than user_entered.