Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Category Archives: API

List of JDR_UTILS APIs

JDR_UTILS is a PL/SQL package which can be used to query and inspect OA Framework metadata in the MDS repository. The complete list of JDR_UTILS APIs are given below.

Sample usage:


SQL> exec jdr_utils.listcustomizations('/oracle/apps/icx/icatalog/shopping/webui/NonCatalogRequestPG');

/oracle/apps/icx/icatalog/shopping/webui/customizations/site/0/NonCatalogRequestPG

PL/SQL procedure successfully completed

SQL>

PROCEDURE deleteEmptyCustomizations;
Deletes all empty customization documents from the repository. An empty customization document is a customization document that does not specify any modifications to the base metadata.

PROCEDURE deleteDocument(p_document VARCHAR2);
Deletes the document from the repository.
p_document – the fully qualified document name, which can represent either a document or package file.

PROCEDURE deletePackage(p_package VARCHAR2);
Deletes the package from the repository if the package is empty. If the package is not empty (i.e. it contains either documents or packages), then an error will be issued indicated that non-empty packages can not be deleted.
p_package – the fully qualified package name

FUNCTION exportDocument(
p_document VARCHAR2,
p_exportFinished OUT BOOLEAN,
p_formatted BOOLEAN DEFAULT TRUE) RETURN VARCHAR2;
Export the XML for a document and pass it back in 32k chunks. This function will return XML chunks, with a maximum size of 32k. Returns the exported XML in 32k chunks
p_document – the fully qualified name of the document.
p_exportFinished – OUT parameter which indicates whether or not the export is finished
p_formatted – TRUE indicates that the XML is formatted nicely

FUNCTION getComponentName(
p_docid jdr_paths.path_docid%TYPE,
p_compid jdr_components.comp_id%TYPE) RETURN VARCHAR2;
Gets the fully qualified name of the component.
p_docid – the ID of the document which contains the component
p_compid – the ID of the component (from comp_id in the jdr_components table)

FUNCTION getDocumentName(
p_docid jdr_paths.path_docid%TYPE) RETURN VARCHAR2;
Gets the fully qualified name of the document.
p_docid – the ID of the document

FUNCTION getTranslations(
p_document VARCHAR2) RETURN translationList;
Gets all of the translations of the specified document.
p_document – the fully qualified document name

PROCEDURE listContents(p_path VARCHAR2, p_recursive BOOLEAN DEFAULT FALSE);
Prints the contents of a package. For the non-recursive case, this will list the documents, package files and package directories. For the recursive case, this will list the document, package files and empty package directories (i.e. packages which contain no documents or child packages). In order to diferentiate documents from package directories, package directories will end with a ‘/’.
p_path – The path in which to list the documents. To specify the root directory, use ‘/’.
p_recursive – If TRUE, recursively lists the contents of sub-directories. Defaults to FALSE.

PROCEDURE listCustomizations(p_document VARCHAR2);
List the customizations for the specified document.
p_document – the fully qualified document name, which can represent either a document or package file.

PROCEDURE listDocuments(p_path VARCHAR2, p_recursive BOOLEAN DEFAULT FALSE);
Internally calls listContents.

PROCEDURE listLanguages(p_document VARCHAR2);
Lists the supported languages for the specified document.
p_document – the fully qualified document name, which can represent either a document or package file.

PROCEDURE printDocument(p_document VARCHAR2,
p_maxLineSize NUMBER DEFAULT MAX_LINE_SIZE);
Prints the contents of a JRAD document to the console.
p_document – the fully qualified document name, which can represent either a document or package file.
p_maxLineSize – the maximum size of line. This defaults to 255 which is the maximim allowable size of a line (the 255 limit is a limitation of the DBMS_OUPUT package).

PROCEDURE printTranslations(p_document VARCHAR2,
p_language VARCHAR2,
p_maxLineSize NUMBER DEFAULT MAX_LINE_SIZE);
Prints the translations for the document in XLIFF format.
p_document – the fully qualified document name, which can represent either a document or package file.
p_language – the language to use for the translations
p_maxLineSize – the maximum size of line. This defaults to 255 which is the maximim allowable size of a line (the 255 limit is a limitation of the DBMS_OUPUT package).

PROCEDURE saveTranslations(
p_document VARCHAR2,
p_translations translationList,
p_commit BOOLEAN := TRUE);
Saves the specified translations for the specified document.This procedure will do the following:
1. Lock the document so as to prevent multiple users attempting to modify translations at the same time
2. Delete all of the translations for the specified document
3. Insert the new translations
4. Commit the data unless p_commit set to FALSE
p_document – the fully qualified document name
p_translations – the list of translations to insert
p_commit – if TRUE, the data is committed. Default is TRUE

All about Host Concurrent Programs

One of the things to remember while registering a host concurrent program is that the first five parameters are reserved by Oracle E-Business Suite for its own use. The sixth parameter onwards is used for user-defined concurrent program parameters. The first five parameters refer to the following:

$0: The shell script to be executed
$1: Oracle user/password
$2: Applications user_id
$3: Application user_name
$4: Concurrent program request_id

In addition to these, the environment variable FCP_LOGIN is also used to store the Oracle user/password. The steps required to register a shell script as a concurrent program are:

1. Create the shell script in the $APPLBIN directory of the specific application top. In this example, I have created the script under $CS_TOP/bin and named it myscr.prog(Oracle documentation mentions that the extension should be .prog). Its contents are listed below:


#!/bin/sh
echo 'Printing parameters....'
echo '0:'$0
echo '1:'$1
echo '2:'$2
echo '3:'$3
echo '4:'$4
echo '5:'$5

echo 'FCPLOGIN:'$FCP_LOGIN
echo 'Finished printing parameters.'

2. Define the concurrent executable with Execution Method ‘Host’. In the Execution File Name field, specify the name of the shell script without extension(‘myscr’ for this example)

3. Define the concurrent program with parameters as required. For this example, I have defined one parameter with default value ‘ABCDEF’

4. Add the concurrent program to a request group

5. Create a symbolic link to fndcpesr (which is located in the $FND_TOP/$APPLBIN directory) and give it the same name as the Execution File Name.  The symbolic link is created in the same directory as the shell script.


[oracle@myapps bin]$ ln -s $FND_TOP/bin/fndcpesr  myscr

Ensure that all files have execute permissions to prevent any ‘Permission denied’ errors. You should now be able to execute the concurrent program which in turn will run the shell script. Executing the concurrent program for this example results in a log file containing the following output. Note that the values of FCP_LOGIN, the reserved parameters and the user-defined parameter are all printed.

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/APPS
2:1001530
3:EBUSINESS
4:2721361
5:ABCDEF
FCPLOGIN:APPS/APPS
Finished printing parameters.

Return codes for host concurrent program

If the script traps an error, use the UNIX exit command ‘exit 1’ to return failure (status code 1) to the Concurrent Manager running the program. Of course, code sections after the exit command will not be executed.


#!/bin/sh
echo 'Printing parameters....'
echo '0:'$0
echo '1:'$1
echo '2:'$2
echo '3:'$3
echo '4:'$4
echo '5:'$5

exit 1

echo 'FCPLOGIN:'$FCP_LOGIN
echo 'Finished printing parameters.'

The concurrent program will complete with status ‘Error’ and the log file will contain the following:

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/APPS
2:1001530
3:EBUSINESS
4:2721405
5:ABCDEF
/u01/oracle/visappl/cs/11.5.0/bin/myscr
Program exited with status 1

There are no defined exit commands to return a warning status. However, it can be done by using the FND_CONCURRENT.SET_COMPLETION_STATUS API  to set the completion status of the request to ‘Warning’. Gareth Roberts deserves a big ‘Thank You’ for posting this on Oracle Forums.


#!/bin/sh
echo 'Printing parameters....'
echo '0:'$0
echo '1:'$1
echo '2:'$2
echo '3:'$3
echo '4:'$4
echo '5:'$5

MYSTATUS=`sqlplus -s $1 <<!
SET HEADING FEEDBACK OFF PAGESIZE 0
declare
l_result boolean;
l_session_id number;
begin
fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, null, $4, null,null,null,null,null,null,-1);
l_result := fnd_concurrent.set_completion_status('WARNING','Review log file for details.');
commit;
end;
/
exit;
!`

echo 'FCPLOGIN:'$FCP_LOGIN
echo 'Finished printing parameters.'

This solution makes use of a SQL script to initialize a session with the request_id of the concurrent program using FND_GLOBAL.INITIALIZE and then sets the completion status. Upon execution, the concurrent program ends with a ‘Warning’ status and generates the following output:

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/APPS
2:1001530
3:EBUSINESS
4:2721408
5:ABCDEF
FCPLOGIN:APPS/APPS
Finished printing parameters.

One important thing to notice is that echoing the parameters $1 and $FCP_LOGIN leads to the Oracle user/password being written to the log file. This can be prevented by using the options ENCRYPT and SECURE while defining the concurrent program. ENCRYPT signals the Concurrent Manager to pass the Oracle password in the environment variable FCP_LOGIN. The Concurrent Manager leaves the password in the argument $1 blank. To prevent the password from being passed, enter SECURE in the Execution Options field. With this change, Concurrent Manager does not pass the password to the program.

For this example specifying SECURE in the concurrent program options:

and then running the concurrent program does not set the completion status to ‘Warning’ since the Oracle user/password is not passed and the SQL script cannot run. This can be observed from the contents of the log file.

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/
2:1001530
3:EBUSINESS
4:2721412
5:ABCDEF
FCPLOGIN:
Finished printing parameters.

If we set the options field in the concurrent program to ENCRYPT

then the Oracle user/password will be passed only to $FCP_LOGIN and not to $1. We can change the SQL  script to use $FCP_LOGIN instead of $1 and execute the concurrent program. It will now complete with a ‘Warning’ status since the Oracle user/password was passed to the script through $FCP_LOGIN. This can be verified from the contents of the log file:

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/
2:1001530
3:EBUSINESS
4:2721409
5:ABCDEF
FCPLOGIN:APPS/APPS
Finished printing parameters.

Note:

  1. Use ${10}, ${11} instead of $10,$11 to refer to double-digit parameters
  2. fndcpesr parses the arguments passed to the shell script. So if you don’t create the symbolic link to fndcpesr while registering the program, parameter values will not be parsed and passed automatically to the variables $0, $1, $2 etc. You will have to parse the individual parameters yourself (I think they are tab-separated).
  3. The FND_GLOBAL.INITIALIZE procedure is used to set new values for security globals during login or when the responsibility is changed, it accepts the following parameters:
 session_id in out number,
 user_id in number,
 resp_id in number,
 resp_appl_id in number,
 security_group_id in number,
 site_id in number,
 login_id in number,
 conc_login_id in number,
 prog_appl_id in number,
 conc_program_id in number,
 conc_request_id in number,
 conc_priority_request in number,
 form_id in number default null,
 form_appl_id in number default null,
 conc_process_id in number default null,
 conc_queue_id in number default null,
 queue_appl_id in number default null,
 server_id in number default -1

Using Web ADI to create Service Request

Web Applications Desktop Integrator is a tool that leverages the standard desktop applications to perform some of the Oracle E-Business Suite task. The Web ADI brings Oracle E-Business suite to the desktop where familiar desktop tool like Excel can be used to create spreadsheet, enter and modify data in the spreadsheet and finally upload the data into the Oracle Applications. Web ADI can be very useful for the users who are experienced with Excel and will like to use Excel to enter the data related to Oracle Applications. These users can use the special features of excel i.e. copy, paste, drag cells to increment and various arithmetic functions to optimize their business task and increase their productivity. Data that is entered into the spreadsheet can then be finally be uploaded in to the Oracle Applications with or without validation.

From the Oracle white paper, Web ADI: Extending E-Business Suite with Desktop Applications

In this post we will see an example of using Web ADI to create Service Requests in Oracle E-Business Suite 11i based on data entered in Microsoft Excel. Though creating a service request isn’t what Web ADI is commonly used for but for the purpose of demonstrating how to create and use custom integrator in Web ADI, it is quite sufficient. Also note that this example is for EBS 11i, there are a few differences in case of R12.

Prerequisite steps to ensure that Web ADI works for you:

  1. Set profile option, ‘BNE Allow No Security Rule’ to ‘Yes’
  2. Assign ‘Oracle Web ADI’ responsibility to your user. This responsibility has the menu ‘WEBADI MENU’ associated with it
  3. Add the submenu, ‘HR ADI Seeded Integrator Form Functions’ to the ‘WEBADI MENU’
  4. Enable macros for Microsoft Excel

Once the prerequisite steps are completed, we can start with creating the custom integrator which will upload the service request data from Excel sheet to Oracle E-Business suite and use this data for creating the service request.

The basic steps for creating a custom integrator for Web ADI are:

  • Define the custom integrator – This is done by using the Web ADI functionality itself to create a document, populating it with the necessary information and then uploading the document to create the integrator.
  • Create a layout for the custom integrator
  • Create mappings for the integrator if there is a need to load content from a text file (we will not be performing this step in our example)

1. Create a wrapper package which will use the data uploaded through the Excel sheet to create the service request. I will be using the Create Service Request API in my code. The package specifications for the package which I am using are given below. Note that the procedure xxx_create_sr which creates the Service Requests has five input parameters out of which two have default values. These input parameters will take their values from the Excel sheet.

create or replace package xxx_my_webadi is
 procedure xxx_create_sr(p_consumer_id in varchar2,
 p_complaint_type in varchar2,
 p_problem_summary in varchar2,
 p_optional in varchar2 default null,
 p_comments in varchar2 default null);
end xxx_my_webadi;

2. Define the custom integrator by navigating to Oracle Web ADI>Create Document. Select Excel 2003 as the viewer(do not select the reporting checkbox) and HR Integrator Setup(pre-defined integrator provided by Oracle for creating custom integrators) as the Integrator. Keep the default values for the other options. Review and click on ‘Create Document’

3. A confirmation message will be displayed and an Excel sheet will open. Following details are filled in the Excel sheet:

  • Metadata Type- CREATE
  • Application Short Name- CS(since I am using the integrator for creating Service Request)
  • Integrator User Name- XXX_SR_Test_Intg
  • Form Name- GENERAL
  • API Package Name- xxx_my_webadi(from Step#1)
  • API Procedure Name- xxx_create_sr(from Step#1)
  • Interface User Name- XXX_SR_Test_Intf
  • Interface Parameter List Name- XXX_SR_Test_Param
  • API Type- PROCEDURE.

Note that you can use the List of Values(Add-Ins>Oracle>List of Values) to fill in some of the fields such as Metadata Type, Application Short Name, Form Name and API Type. Double-clicking on these fields will also invoke the List of Values window.

4. Upload the document through the Add-Ins tab(Add-Ins>Oracle>Upload). A confirmation message will be displayed

5. Next the layout needs to be defined by following the navigation Oracle Web ADI>Define Layout. Select the integrator XXX_SR_Test_Intg defined in Step#3 and define the layout. Specify a Layout Name and a Layout Key.

6. The layout has some required fields and some optional fields. The fields are derived from the input parameters of the procedure in Step#1. Note that the input parameters without default values are indicated as required fields here. Default values are specified for the required fields though this is not a mandatory step. Also, I have used one of the optional fields for comments/instructions to the user and I have specified this as a header field so that it is displayed only once in the Excel sheet.

7. The basic steps for creating and using the custom Web ADI integrator is complete and the user can access it by navigating to Oracle Web ADI>Create Document, selecting the integrator and other relevant options. However in order to make this integrator available to other responsibilities and to automatically default other options so that the user does not have to select them every time, we will define a custom function for the integrator by navigating to Application Developer>Application>Function. Details to be provided are:

  • Type- SSWA servlet function
  • HTML Call- oracle.apps.bne.webui.BneApplicationService
  • Parameters- bne:page= BneCreateDoc&bne:viewer= BNE:EXCEL2003&bne:reporting= N&bne:integrator= USER_NAME:XXX_SR_Test_Intg&bne:noreview= Yes (I have inserted spaces after the equals symbol for formatting purposes, please remember to remove them)

8. Assign the function to the appropriate menu

9. Invoke the integrator by accessing the function defined in Step#7 and 8.

10. The following Excel sheet will open. Note the comment in the header section and the default values for the required fields. Double click on the ‘Upl’ column to flag the rows which you want to upload and make necessary changes. Upload the data through Add-Ins>Oracle>Upload. You can select the options as required in the Upload Parameters window.

11. A confirmation message and green smileys will be displayed denoting successful upload.

Resources:

  1. Metalink Note#603169.1 -WebADI and Report Manager Webcasts for EBS 11i/R12 — Replay is Now Available
  2. Metalink Note#360105.1 -Understanding and Using Web ADI in Oracle HRMS.