This chapter provides you with specifications for calling several Oracle E-Business Suite APIs from your PL/SQL procedures. Most routines in the FNDSQF library are described in this section. Some FNDSQF routines are described in other chapters (for example, the FND_KEY_FLEX routines are described in the chapter called ”Flexfields”). The routines described in this chapter include:
FND_CURRENCY: Dynamic Currency APIs
FND_GLOBAL: WHO Column Maintenance
FND_ORG: Organization APIs
FND_STANDARD: Standard APIs
FND_UTILITIES: Utility Routines
This section describes Dynamic Currency APIs that you can use in your client- and server-side PL/SQL procedures. The Dynamic Currency feature allows different values in arbitrary currencies to be displayed in the same report or form, each shown with appropriate formatting.
Variable | Description |
---|---|
Summary |
function FND_CURRENCY.GET_FORMAT_MASK( currency_code IN varchar2 field_length IN number) return varchar2; |
Description | This function uses the normal default values to create a format mask. |
Variable | Description |
---|---|
currency_code | The currency code for which you wish to retrieve a default format mask |
field_length | The maximum number of characters available to hold the formatted value
Important: The varchar2 field that receives the format mask should be ten characters longer than the field_length. |
This routine uses the following profiles to create the format mask:
CURRENCY:THOUSANDS_SEPARATOR
CURRENCY:NEGATIVE_FORMAT
CURRENCY:POSITIVE_FORMAT
Although the profile for negative format allows three different bracket styles, this routines only uses angle brackets (< >).
The ORDER_LINES.AMOUNT field in a form is to be displayed using Dynamic Currency formatting. The format mask is created and passed into the APP_ITEM_PROPERTY.SET_PROPERTY call:
APP_ITEM_PROPERTY.SET_PROPERTY('ORDER_LINE.AMOUNT', FORMAT_MASK, FND_CURRENCY.GET_FORMAT_MASK( :ORDER_CURRENCY_CODE, GET_ITEM_PROPERTY( 'ORDER_LINE.AMOUNT', MAX_LENGTH)));
The use of the display group separator, and positive and negative formatting are typically user preferences. Thus these settings are allowed to default from the User Profile system. The precision comes from the stored information for that order's currency code.
Dynamic currency support is also accessible from server-side PL/SQL. The package interfaces are identical. An example implementation has the following calls:
DISPLAYABLE_VALUE := TO_CHAR(AMOUNT, FND_CURRENCY.GET_FORMAT_MASK( DC_FORMAT_MASK, 30));
The routines in the FND_DATE package are documented with the APP_DATE package.
See: APP_DATE: Date Conversion APIs
For a discussion of handling dates in your applications, see the chapter on dates. See: Handling Dates.
This section describes Global APIs you can use in your server-side PL/SQL procedures. The server-side package FND_GLOBAL returns the values of system globals, such as the login/signon or “session" type of values. You need to set Who columns for inserts and updates from stored procedures. Although you can use the FND_GLOBAL package for various purposes, setting Who columns is the package's primary use.
You should not use FND_GLOBAL routines in your forms (that is on the client side), as FND_GLOBAL routines are stored procedures in the database and would cause extra roundtrips to the database. On the client side, most of the procedures in the FND_GLOBAL package are replaced by a user profile option with the same (or a similar) name. You should use FND_PROFILE routines in your forms instead.
See: Tracking Data Changes with record History (WHO)
FND_PROFILE: User Profile APIs
Variable | Description |
---|---|
Summary |
function FND_GLOBAL.USER_ID return number; |
Description | Returns the user ID. |
Variable | Description |
---|---|
Summary |
procedure APPS_INITIALIZE(user_id in number, resp_id in number, resp_appl_id in number); |
Description | This procedure sets up global variables and profile values in a database session. Call this procedure to initialize the global security context for a database session. You can use it for routines such as Java, PL/SQL, or other programs that are not integrated with either the Oracle E-Business Suite concurrent processing facility or Oracle Forms (both of which already do a similar initialization for a database session). The typical use for this routine would be as part of the logic for launching a separate non-Forms session (such as a Java program) from an established Oracle E-Business Suite form session. You can also use this procedure to set up a database session for manually testing application code using SQL*Plus. This routine should only be used when the session must be established outside of a normal form or concurrent program connection You can obtain valid values to use with this procedure by using profile option routines to retrieve these values in an existing Oracle E-Business Suite form session. For manual testing purposes, you can use Examine during an Oracle E-Business Suite form session to retrieve the profile option values. |
Variable | Description |
---|---|
USER_ID | The USER_ID number |
RESP_ID | The ID number of the responsibility |
RESP_APPL_ID | The ID number of the application to which the responsibility belongs |
Example
fnd_global.APPS_INITIALIZE (1010, 20417, 201);
Variable | Description |
---|---|
Summary |
function FND_GLOBAL.LOGIN_ID return number; |
Description | Returns the login ID (unique per signon). |
Variable | Description |
---|---|
Summary |
function FND_GLOBAL.CONC_LOGIN_ID return number; |
Description | Returns the concurrent program login ID. |
Variable | Description |
---|---|
Summary |
function FND_GLOBAL.PROG_APPL_ID return number; |
Description | Returns the concurrent program application ID. |
Variable | Description |
---|---|
Summary |
function FND_GLOBAL.CONC_PROGRAM_ID return number; |
Description | Returns the concurrent program ID. |
Variable | Description |
---|---|
Summary |
function FND_GLOBAL.CONC_REQUEST_ID return number; |
Description | Returns the concurrent request ID. |
Use this package to set the correct Organization in forms that use organizations.
Variable | Description |
---|---|
Summary |
function FND_ORG.CHANGE_LOCAL_ORG return boolean; |
Description | Use this function to change the organization of the current form. It returns FALSE if the change is cancelled or fails. |
Variable | Description |
---|---|
Summary |
function FND_ORG.CHANGE_GLOBAL_ORG return boolean; |
Description | Use this function to change the global organization defaults when opening a new form. It returns FALSE if the change is cancelled or fails. |
Variable | Description |
---|---|
Summary |
procedure FND_ORG.CHOOSE_ORG( allow_cancel IN boolean default FALSE); |
Description | Call this procedure in PRE-FORM to ensure the organization parameters are set. If the local form has no organization parameters passed, the global defaults are used. If the global organization defaults are not set, the procedure opens the organization LOV to force an organization selection. |
Variable | Description |
---|---|
allow_cancel | Allow cancelation of the LOV without forcing a choice. The default is FALSE. |
This section describes utilities you can use to achieve standard functionality in your forms.
Variable | Description |
---|---|
Summary |
procedure FND_STANDARD.FORM_INFO( version IN varchar2, title IN varchar2, application_short_name IN varchar2, date_last_modified IN varchar2, last_modified_by IN varchar2); |
Variable | Description |
---|---|
Description | FND_STANDARD.FORM_INFO provides information about the form. Call it as the first step in your WHEN-NEW-FORM-INSTANCE trigger. The TEMPLATE form provides you with a skeleton call that you must modify. |
See: Special Triggers in the TEMPLATE form
Variable | Description |
---|---|
Summary |
procedure FND_STANDARD.SET_WHO; |
Description | SET_WHO loads WHO fields with proper user information. Call in PRE-UPDATE, PRE-INSERT for each block with WHO fields. You do not need to call FND_GLOBAL if you use SET_WHO to populate your WHO fields. |
See: Tracking Data Changes With Record History (WHO) and FND_GLOBAL:WHO Column Maintenance.
Variable | Description |
---|---|
Summary |
function FND_STANDARD.SYSTEM_DATE return date; |
Description | This function behaves exactly like the built-in SYSDATE, only cached for efficiency. You should use it in your Oracle Forms PL/SQL code in place of the built-in SYSDATE. |
Variable | Description |
---|---|
Summary |
function FND_STANDARD.USER return varchar2; |
Description | This function behaves exactly like the built-in USER, only cached for efficiency. You should use it in your Oracle Forms PL/SQL code in place of the built-in USER. |
This section describes various utility routines.
Variable | Description |
---|---|
Summary |
procedure OPEN_URL(URL in varchar2); |
Variable | Description |
---|---|
URL | You can pass either an actual URL string or a :block.field reference of a field that contains a URL string. |
Example 1
FND_UTILITIES.OPEN_URL('http://www.oracle.com/index.html');
Example 2
FND_UTILITIES.OPEN_URL(:blockname.fieldname);
Variable | Description |
---|---|
Summary |
function PARAM_EXISTS(name varchar2) return boolean; |
Description | Returns true if the parameter exists in the current form. |
Variable | Description |
---|---|
name | The name of the parameter to search for. |
Example
if fnd_utilities.param_exists('APP_TRACE_TRIGGER') then execute_trigger(name_in('PARAMETER.APP_TRACE_TRIGGER')); end if;