FNDSQF Routine APIs

Introduction to FNDSQF Routine APIs

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

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.

FND_CURRENCY.GET_FORMAT_MASK (Client or Server)

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:

Although the profile for negative format allows three different bracket styles, this routines only uses angle brackets (< >).

Currency Examples

Client-side PL/SQL Example

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.

Server-side PL/SQL Example

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

FND_DATE: Date Conversion APIs

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.

FND_GLOBAL: WHO Column Maintenance and Database Initialization

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

FND_GLOBAL.USER_ID (Server)

Variable Description
Summary 
function  FND_GLOBAL.USER_ID 
                return number;
Description Returns the user ID.

FND_GLOBAL.APPS_INITIALIZE (Server)

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

FND_GLOBAL.LOGIN_ID (Server)

Variable Description
Summary 
function  FND_GLOBAL.LOGIN_ID
                return number;
Description Returns the login ID (unique per signon).

FND_GLOBAL.CONC_LOGIN_ID (Server)

Variable Description
Summary 
function  FND_GLOBAL.CONC_LOGIN_ID
        return number;
Description Returns the concurrent program login ID.

FND_GLOBAL.PROG_APPL_ID (Server)

Variable Description
Summary 
function  FND_GLOBAL.PROG_APPL_ID
        return number;
Description Returns the concurrent program application ID.

FND_GLOBAL.CONC_PROGRAM_ID (Server)

Variable Description
Summary 
function  FND_GLOBAL.CONC_PROGRAM_ID
        return number;
Description Returns the concurrent program ID.

FND_GLOBAL.CONC_REQUEST_ID (Server)

Variable Description
Summary 
function  FND_GLOBAL.CONC_REQUEST_ID
        return number;
Description Returns the concurrent request ID.

FND_ORG: Organization APIs

Use this package to set the correct Organization in forms that use organizations.

FND_ORG.CHANGE_LOCAL_ORG

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.

FND_ORG.CHANGE_GLOBAL_ORG

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.

FND_ORG.CHOOSE_ORG

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.

FND_STANDARD: Standard APIs

This section describes utilities you can use to achieve standard functionality in your forms.

FND_STANDARD.FORM_INFO

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

FND_STANDARD.SET_WHO

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.

FND_STANDARD.SYSTEM_DATE

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.

FND_STANDARD.USER

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.

FND_UTILITIES: Utility Routines

This section describes various utility routines.

FND_UTILITIES.OPEN_URL

Variable Description
Summary 
procedure  OPEN_URL(URL in varchar2);
Variable Description
Description Invokes the Web browser on the client computer with the supplied URL document address. If a browser is already running, the existing browser is directed to open the supplied URL. You can use this utility to point a Web browser to a specific document from your forms.
This utility is not appropriate for opening Oracle Self-Service Web Applications functions from forms, however, as it does not provide session context information required for such functions. Use FND_FUNCTION.EXECUTE for opening those functions.
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);

FND_UTILITIES.PARAM_EXISTS

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;