Regulatory Management APIs

This appendix covers the following topics:

• Regulatory Management APIs
• Introducing the Regulatory Management APIs
• Regulatory Management API Support Policy
• Technical Requirements
• Technical Overview
• Basic Business Needs
• Input Data Sources
• Stored Procedure
• Standard Parameters
• Value-ID Conversion
• Dispatch History API

Regulatory Management APIs

This topic describes the parameters needed for the API, as well as the table structure and the associated details. The topics discussed are:

• Introducing the Regulatory Management APIs

• Basic Business Needs

• Standard Parameters

• Dispatch History API

Introducing the Regulatory Management APIs

The Regulatory Management APIs let you import information from another system into the OPM Regulatory Management tables. When you import this information you can include all pertinent information using a tool that does not have cryptic IDs and system specific information. The interface ensures that your imported data contain the same detail as those you enter manually on the OPM Regulatory Management windows.

Regulatory Management APIs are written in PL/SQL that can be called by your own programs. To make use of these APIs, code your wrapper function that passes the appropriate parameters to the APIs. Your program is responsible for connecting to a database before calling an API function, and disconnecting from the database upon return. You can choose to write log files before calling and after returning from a function. If there is a problem during execution of a call, then the APIs return one of the following status codes:

• S for success

• E for error

• U for unknown or unexpected status

• N for item requiring a location

• V for inventory shortage exists

• I for incomplete manual transactions exist

• G for over allocation exists

Regulatory Management API Support Policy

Regulatory Management APIs are supported by Oracle. This means:

• Oracle provides objects and libraries needed to use the APIs and the documentation for their use.

• Oracle ensures that the APIs function as designed.

• Oracle does not support customer generated programs that use the APIs.

Technical Requirements

Regulatory Management APIs make use of the following standard Oracle Applications packages:

• FND_API - the standard Oracle Applications API version checking function. This is used by the stored procedure to check for a valid API version number and also contains constant variables such as TRUE and FALSE.

• FND_MESSAGE - the standard Oracle Applications messaging function. This is used by the stored procedure to report status and error handling.

• FND_PUB_MSG - the standard Oracle Applications message retrieval function used to search the procedure messages.

These packages are installed as part of the current release. Refer to the Oracle Applications Coding Standards manual for additional details.

Technical Overview

Regulatory Document Management APIs are designed to operate in an OPM 11i environment only. They offer the following basic API functionality:

• Creating, updating, and deleting information.

• Proper encapsulation.

• Synchronous processing following the business hierarchy.

• Detailed and translatable error messages.

Basic Business Needs

These APIs let you feed information from a third party vendor into OPM Regulatory Management.

Following are some of the important characteristics for these APIs:

Code Reuse

You can maximize code reuse from all application development tools, including PL/SQL, Oracle Forms, and Oracle Reports.

Ease of Integration

You can integrate APIs into other applications and enabling technology, such as Oracle Workflow Server, Oracle Internet Commerce & Oracle WebSystem, and Oracle EDI Gateway.

Insulation from Changes

You can encapsulate the structure of schema to prevent changing schema structures from affecting other applications.

Consistent Behavior

You can hide Object logic specific to an application from other applications, and to ensure that this logic is correctly invoked by other applications and customers.

Robust Validation

You can validate all incoming information into Oracle Applications.

Input Data Sources

Flat File

Input data to the user wrapper function comes from a flat file source. This is processed by the wrapper and header information, passed as parameters, to the stored procedure in a synchronous mode. However, along with the standard parameters, the header information is passed as a PL/SQL table. In this mode, the calling function monitors the success or failure (return code) from the called procedure. It also provides an option to COMMIT work done by the procedure.

Batch File

Input data to the user wrapper function comes from a batch file. This is processed by the wrapper and header information passed, as parameters, to the stored procedure in an asynchronous mode. In this mode, the calling function does not monitor the success or failure of each individual record. The Oracle Message FND_PUB_MSG functionality is used to audit the calls.

Online User Interface (UI)

Input data to the user wrapper function comes from a window or another user interface. This is processed by the UI and the details passed, as parameters, to the stored procedure in a synchronous mode. In this mode, the UI calling function monitors the success or failure (return code) from the called procedure. It also provides an option to COMMIT work done by the procedure.

Stored Procedure

The stored procedure is called with the appropriate parameters forwarded in a PL/SQL table format. The procedure validates each record from this table and then processes the appropriate functional logic as required. The procedure writes appropriate messages to the Oracle Messages table. These are informational as determined by the logic. These can be interrogated by the calling wrapper function through the GET MESSAGES functionality.

The stored procedure calls other validation procedures in the course of its execution; a modular approach has been adopted. Functions called by these procedures do not use IN/OUT parameters as these have been removed from the Oracle 8 coding standards.

On successful completion of the procedure, a success unit is in place that can be optionally COMMITTED. The decision as to whether a COMMIT is issued on successful completion is under the control of the calling code and deliberately outside the scope of the API procedures.

Standard Parameters

API standard parameters are a collection of parameters that are common to most APIs. The following paragraphs explain the standard parameters that are used in APIs and their interpretation.

Some of the standard parameters apply to all APIs regardless of the nature of the business function they perform. For example, p_api_version and x_return_status are included in all APIs.

However, some parameters are applicable for certain types of APIs and not applicable for other types. For example, p_commit is applicable for APIs that change the database state, and not applicable for read APIs.

Standard parameters are included in all APIs whenever applicable.

Standard IN parameters:

• p_api_version

• p_init_msg_list

• p_commit

• p_validation_level

Standard OUT parameters:

• x_return_status

• x_msg_count

• x_msg_data

  Parameter	Type	IN/OUT	Required	Validation
  p_api_version	varchar2	IN	Y	Validates version compatibility. The version sent by the calling function is compared to the internal version of the API and an unexpected error (U) is generated if these do not match.
  p_init_msg_list	varchar2	IN	N	Used to specify whether the message list can be initialized on entry to the API. It is an optional parameter, and if not supplied, then it defaults to FND_API.G_FALSE, and the API does not initialize the message list.
  p_commit	varchar2	IN	N	Used to specify whether the API can commit its work before returning to the calling function. If not supplied, then it defaults to FND_API.G_FALSE.
  x_return_status	varchar2	OUT	-	Specifies whether the API was successful or failed. Valid values are S - successful, E - failed due to expected error, U - failed due to unexpected error.
  x_msg_count	number	OUT	-	Specifies number of messages added to message list.
  x_msg_data	varchar2	OUT	-	Returns the messages in an encoded format. These messages can then be processed by the standard message functions as defined in Business Object API Coding Standards document.

Value-ID Conversion

IDs are usually used to represent primary and foreign entity keys, and for internal processing of attributes. They are not meaningful and are hidden. Besides IDs, attributes have values that represent them. Those values are meaningful and are used for display purposes. In general, APIs operate only on IDs.

For example, an item is represented by an ID, the number column item_id. This ID is its primary key and is used for all internal processing of the item. Besides this ID, an item is represented by a value, the varchar2 column item_no. This value is displayed when you choose an item. Therefore, an item can be identified by either its ID or value, in this case item_no.

The following set of rules are for the conversion process:

• Either ID or value, or both can be passed to an API. But, when both values are passed, ID based parameters take precedence over value based parameters. For example, if both parameters are passed, the value based parameter is ignored and the ID based parameter is used.

• When both the value and ID of an attribute are passed to an API, a message informs the API caller that some of the input has been ignored.

• This message is not an error message. The API continues with its regular processing.

• Each value has to resolve into one ID. Failure to resolve a value into an ID is an error and is associated with an error message. The API aborts processing and returns with a return status of error.

Dispatch History API

The Dispatch History API stores details about the distribution of Regulatory documents. If the document has not already been stored in the fnd_documents table, then the eSignatures file upload API does. The API is called from within the Oracle E-Business Suite or by other sources, such as third party applications.

Following is the definition of the Dispatch History API:

Parameter	Required	In/Out	Description
p_item	N	IN	The item or product that the document is related to.
p_cas_number	N	IN	CAS number of the item or product that the document is related to.
p_recipient_id	Y	IN	Document recipient ID.
p_recipient_site_id	Y	IN	Document recipient site ID.
p_date_sent	Y	IN	Date document was sent to recipient.
p_dispatch_method_code	Y	IN	Method used to send the document to recipient.
p_document_id	N	IN	If the document is already in the system, then this is the ID of the document. Otherwise, the Files Upload API is called to upload the file.
p_document_location	N	IN	Physical location of the document. Required if the document is not currently in the system.
p_document_name	N	IN	Actual name of the file. Required if the document is not currently in the system.
p_document_version	N	IN	Document version. Required if the document is not currently in the system.
p_document_category	N	IN	Category to assign document to. Required if the document is not currently in the system.
p_file_format	N	IN	Format of the file. For example, XML, PDF. Required if the document is not currently in the system.
p_file_description	N	IN	Description of document. Required if the document is not currently in the system.
p_document_code	Y	IN	Type of regulatory document. For example, US16, CA16. Required if the document is not currently in the system.
p_disclosure_code	N	IN	Disclosure code used to generate the document. Required if the document is not currently in the system.
p_language	N	IN	Language that document was generated in. Required if the document is not currently in the system.
p_organization_code	N	IN	Organization the document was created for.
p_user_id	Y	IN	User ID used in Standard Who columns.
p_creation_source	N	IN	Specifies the application calling this API. Valid values are 0 - External application, 1- Internal application, 2 - Form. Required if the document is not currently in the system.