DataLoad Commands

DataLoad Command	Application Action(s)
TAB	Press the Tab key
ENT	Press the Enter key
*UP	Press the up arrow key
*DN	Press the down arrow key
*LT	Press the left arrow key
*RT	Press the right arrow key
*SAVE	Save the current record
*SP	Save & proceed
*FE	Open the field editor
*PB	Navigate to the previous block
*NB	Navigate to the next block
*PF	Navigate to the previous field
*NF	Navigate to the next field
*PR	Navigate to the previous record
*NR	Navigate to the next field
*IR	Insert a new record
*CL	Clears the form field but does not delete any data
*ER	Clears a record from the form fields but does not delete it
*DR	Delete the current record
*FR	Navigate to the first record
*LR	Navigate to the last record
*SB	Press the Spacebar. This is useful for toggling the setting of radio buttons and check boxes instead of using the mouse.
*ST	Select the entire field
*FI	Open the find window
*FA	Find all records
*QE	Put the form in to the query enter mode
*QR	Run a query when the form is in query enter mode
*BM	Open the block menu. This is used in Oracle E-Business to select the block or tab to which to navigate.
*AX	Sends the Alt + X keystroke where X is a single letter (A-Z). See Note 1

Note 1 In most applications the menus, buttons and other controls can usually be activated by using the Alt key combined with one other key. DataLoad provides *AX so that all possible Alt key combinations are available as a DataLoad command.

Built-in Commands

The following commands are built-in to DataLoad to provide additional functionality beyond sending data and keystrokes to applications. These commands cannot be edited or replaced in the commands window. The built-in commands are listed in the following table. Where available, please click the link on the command name for more information about using each command.

Command	Application Action(s)
*SLN or *SL(N)	Pause the load for N seconds
*CW(window)	Change to window window
*ML(coordinates)	Position the mouse at coordinates and press the left button
*MR(coordinates)	Position the mouse at coordinates and press the right button
*DL(coordinates)	Position the mouse at coordinates and double click the left button
PROMPT(message)	Prompt the user with message and wait for a response
PHP(function)	Calls a PHP function when Scripting used with a Macro load

User Exits

We can integrate Oracle reports with Oracle Application Object Library, and run them as concurrent programs from your forms or through standard request submission.

User Exits : The Program which makes USER to ExiT from ongoing environment to perform certain action is call USER EXIT.

1. We use the User Exits in the Report triggers like before report, after report, before parameter form, after parameter form.
2. We have to create a mandatory parameter called P_CONC_REQUEST_ID when we work with FND SRWINIT, FND SRWEXIT.
3. The user exits are case sensitive we have to write them in capital letters only otherwise they will raise an error.

SRW Package : SQL Report Writer, SRW is package used while creating a report using .rdf  it helps to perform action at runtime


These are the user exits available in Oracle Reports that makes AOL integration.

Five Types of User Exits:

FND SRWINIT
FND SRWEXIT
FND FORMAT_CURRENCY
FND FLEXIDVAL
FND FLEXSQL

FND SRWINIT

This is a User Exit which sets your profile option values and allows Oracle AOL user exits to detect that they have been called by oracle repots.
It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing).

FND SRWINIT also allows your report to use the correct organization automatically.
Can be used in BEFORE-REPORT Trigger.
Syntax is : SRW.USER_EXIT(‘FND SRWINIT’);

FND SRWEXIT
This user exit ensures that all the memory allocated for AOL user exits has been freed up properly.
Can be used in AFTER- REPORT Trigger.
Syntax is : SRW.USER_EXIT(‘FND SRWEXIT’);

FND FORMAT_CURRENCY
To format the currency amount dynamically depending upon the precision of the actual currency value, standard precision, users profile values and location (country) of the site.
You obtain the currency value from the database into an Oracle Reports column.
Define another Oracle Reports column, a formula column of type CHAR, which executes the FORMAT_CURRENCY user exit to format the currency value.
A displayed field has this formula column as its source so that the formatted value is automatically copied into the field for display.

Syntax:
FND FORMAT_CURRENCY
CODE=:column containing currency
DISPLAY_WIDTH=field width for display
AMOUNT=:source column name
DISPLAY=:display column name
[MINIMUM_PRECISION=:P_MIN_PRECISION]
[PRECISION={STANDARD|EXTENDED}]
[DISPLAY_SCALING_FACTOR=:P_SCALING_FACTOR]

FND FLEXSQL
Call this user exit to create a SQL fragment usable by your report to tailor your SELECT statement that retrieves flexfield values. This fragment allows you to SELECT flexfield values or to create a WHERE, ORDER BY, GROUP BY, or HAVING clause to limit or sort the flexfield values returned by your SELECT statement. You call this user exit once for each fragment you need for your select statement. You define all flexfield columns in your report as type CHARACTER even though your table may use NUMBER or DATE or some other datatype.
Syntax:

FND FLEXSQL
CODE=”flexfield code”
APPL_SHORT_NAME=”application short name”
OUTPUT=”:output lexical parameter name”
MODE=”{ SELECT | WHERE | HAVING | ORDER BY}”
[DISPLAY=”{ALL | flexfield qualifier | segment number}”]
[SHOWDEPSEG=”{Y | N}”]
[NUM=”:structure defining lexical” | MULTINUM=”{Y | N}”]
[TABLEALIAS=”code combination table alias”]
[OPERATOR=”{ = | < | > | <= | >= | != | “||” | BETWEEN | QBE}”]
[OPERAND1=”:input parameter or value”]
[OPERAND2=”:input parameter or value”]
FND FLEXIDVAL

Call this user exit to populate fields for display. You pass the key flexfields data retrieved by the query into this exit from the formula column. With this exit you display values, descriptions and prompts by passing appropriate token (any one of VALUE, DESCRIPTION, APROMPT or LPROMPT).

Syntax:

FND FLEXIDVAL
CODE=”flexfield code”
APPL_SHORT_NAME=”application short name”
DATA=”:source column name”
[NUM=”:structure defining source column/lexical”]
[DISPLAY=”{ALL|flexfield qualifier|segment number}”]
[IDISPLAY=”{ALL|flexfield qualifier|segment
number}”]
[SHOWDEPSEG=”{Y | N}”]
[VALUE=”:output column name”]
[DESCRIPTION=”:output column name”]
[APROMPT=”:output column name”]
[LPROMPT=”:output column name”]
[PADDED_VALUE=”:output column name”]
[SECURITY=”:column name”]

Handling multiple layouts in XML Publisher

Steps for handling multiple layouts in XML.

1. After developing the Report definition file (.rdf) we have to add one more parameter like follows.
   
2. This parameter value should be assigned to Place holder column(CP ) like follows
   We can assign Parameter value either after parameter form or before report Triggers.
   In this we assigned in Before report Trigger like Below..
   
   
   

Note: – place holder column should be placed at Report level.

Then we can create multiple layouts in (.rtf).

Like below we have to add condition(If) Field for handling the multi layouts.

Double click on if condition which was added by ourselves. Then the following screen

Will display.

Click On Add help text Button

Then the following screen will display.

Different Types of Value Sets

Independent Value Set:

Navigation: Application Developer > Application > Validation > Sets

Enter information as below

Save it.

Create Values for the value set we defined above

Navigation: Application Developer > Application > Validation > Values

Enter information as below

Save it.

Dependent Value Set

To define a Dependent value set we also need one Independent value set.

Example: To List down States in a Country we need to have list of Countries for which states need to be defined.

First Define list of countries in a independent value set

Navigation: Application Developer > Application > Validation > Sets

Enter Values for the above value set

Navigation: Application Developer > Application > Validation > Values

Enter Values in the below screen

Now define another Value set

Navigation: Application Developer > Application > Validation > Sets

Click Edit Information Button to link this Dependent Value set with Independent Value set defined in previous step

Enter Values for each country

Navigation: Application Developer > Application > Validation > Values

Ensure to Select Country name in “Independent Value” field before click Find.

Click Find and enter states for Country India

Save it

Now repeat the above two steps to enter states of country USA

Ensure to select country as USA before clicking Find button

Save it.

Table Type Value Set

Navigation: Application Developer > Application > Validation > Sets

Select Validation type as “Table” and click Edit Information button

Enter information as below

Click Test button validate the information that is entered

Navigate to Concurrent Program Parameters screen and add fourth parameter

API Parameters

Overview

1) The Application Programming Interface or API is a PL/SQL packaged procedure which can be used as an alternative entry point into the system to the traditional online forms

2) The advantage being that the same logic used by the seeded online forms can also be used by other interfaces into the system, thus ensuring that the integrity of the data remains intact

Calling API

1) Ensure it is appropriate to allow that particular business operation

2) Validate the data passed to the API

3) Each API has a number of parameters, most of them mapped with DB column. Every parameter name starts with p_. If the parameter maps onto a database column, the remaining part of the name is usually the same as the column name

4) When calling the APIs, it is strongly recommended that you use Named Notation, instead of Positional Notation

Standard IN Parameters

1) p_api_version IN NUMBER 

This must match the version number of the API. An unexpected error is returned if the calling program version number is incompatible with the current API version number

2) p_init_msg_list IN VARCHAR2
The valid values for this parameter are:
• True = FND_API.G_TRUE
• False = FND_API.G_FALSE
• Default = FND_API.G_FALSE
If set to true, then the API makes a call to fnd_msg_pub.initialize to initialize the message stack. To set to true, use the value, "T". If set to false then the calling program must initialize the message stack. This action is required to be performed only once, even in the case where more
than one API is called. To set to false, use the value, "F".

3) p_commit IN VARCHAR2 

The valid values for this parameter are:
• True = FND_API.G_TRUE
• False = FND_API.G_FALSE
• Default = FND_API.G_FALSE
If set to true, then the API commits before returning to the calling program. To set to true, use the value, "T". If set to false, then it is the calling program’s responsibility to commit the transaction. To set to false, use the value, "F".

Standard OUT Parameters

1) x_return_status OUT NOCOPY VARCHAR2

Indicates the return status of the API. The values returned are one of the following:
• FND_API.G_RET_STS_SUCCESS
Success: Indicates the API call was successful
• FND_API.G_RET_STS_ERROR
Expected Error: There is a validation error, or missing data error.
• FND_API.G_RET_STS_UNEXP_ERROR
Unexpected Error: The calling program can not correct the error.

2) x_msg_count OUT NOCOPY NUMBER
Holds the number of messages in the message list.

3) x_msg_data OUT NOCOPY VARCHAR2
Holds the encoded message if x_msg_count is equal to one

Outbound Interface

XML Bursting with Example

1. Create RDF Report
2. Register Executable
3. Register Concurrent Program
4. Create XML bursting control file
5. Register data definition
6. Create RTF template File
7. Upload RTF file to data template
8. Place RTF File on Server
9. Add concurrent program in request group
10. Set the Temporary Working Directory
11. Submit concurrent Program and wait for email

XML Bursting Concept

Oracle Defines it as following

Oracle Reports enables you to deliver a single report to multiple destinations simultaneously. Using the new, enhanced distribution feature, you can set up your report to be distributed to an e-mail destination, a portal, a printer, or anywhere else when the report is run. This feature also enables you to improve performance, since you only fetch the data once for many different formats and destinations. Using distribution also reduces your maintenance overhead because you only need one job request to publish the report to multiple destinations. You can refine this further by sending the Header section to some recipients, the main to others, and the entire report to an entirely different recipient list.

Business Requirement

We have a concurrent program for generating payslip and we want a concurrent program through which payslip should be generated and output should be emailed to all persons. Every person should receive only his related payslip.

Solution concept

There are different ways of achieving this task. But in this post i am demonstrating the feature of XML Bursting to accomplish this task. In this example we shall create the concurrent program and will see how to burst it through XML Bursting.

Step By Step Solution

We have to do following steps to achieve the required result

1. Create RDF Report
2. Register Executable
3. Register Concurrent Program
4. Create XML bursting control file
5. Register data definition
6. Create RTF template File
7. Upload RTF file to data template
8. Place RTF File on Server
9. Add concurrent program in request group
10. Set the Temporary Working Directory
11. Submit concurrent Program and wait for email

Note: In case of pure xml report, this report has to be called through pl/sql and xml bursting program should be called sub sequentially passing the request id of report concurrent program.

Back to top

Step 1 – Create a report in RDF 

Create a report in Oracle Report Builder and make sure that report should contain following
1. Parameter p_conc_request_id
2. User Exit in Before and After Report Triggers
3. User Exit and XML Publisher bursting program call on After Report Trigger

After Report Trigger should look like this

Back to top

Step 2 – Register Executable

Once report is ready, register its executable program through Application Developer responsibility

Back to top

Step 3 – Register Concurrent Program

Register Concurrent Program against the executable created in step above. Make sure the Output should be of XML type.

Add Variable to Concurrent program. These parameters will be as per your custom requirement. In this example for simplicity purpose, i have taken only one parameter of business group id. Make sure that token of parameter should match with parameters in RDF Report.

XML output of this program looks like this

Back to top

Step 4 – Create XML bursting control file

Next and very important step is to create the XML Bursting control file. Oracle has suggested instructions to create the bursting control file, Following which we can control the behavior of this feature as per our need. Find the example of sample control file.

Detailed explanation of above file is given below

Back to top

Step 5 – Register data definition

Register data definition through XML Publisher responsibility. Make Sure that Short Code of concurrent program and Code of data definition should be same.

Back to top

Step 6 – Create RTF template File

Using the XML file that we had generated in Step 3 above, create RTF template. Creating RTF template using XML publisher is out of scope of this post. Once template is ready, it looks like this.

Back to top

Step 7 – Upload RTF file to data template

Create template against the data definition created above and upload the RTF template file as shown below

Back to top

Step 8 – Upload RTF Template to Data Template

This additional step is must required. You need to upload RTF template at any location on your server. Bursting program considers this template to generate the output. You can place this file using WinScp software if you have access otherwise ask your DBA to place file on any location of your application server. Preferably, you can put this template file on your temporary folder that you will do in Step 10ahead.

Step 9 – Add concurrent program in request group

At this step you need to add your custom program XX Employee Data – XML Bursting in request group of your responsibility. You should also add the XML Publisher Report Bursting Program program in addition to it. Short name of XML Bursting concurrent program is XDOBURSTREP. 

You had called this program in the After-Report parameter of you RDF report created above. If you will not add this concurrent program in your request group,  your concurrent program will execute smoothly but xml publisher concurrent program will error out.

Step 10 – Set the Temporary Working DirectoryYou also need to set the temporary working directory. Through XMP Publisher responsibility, Go to Administration Tab

and set the value of temporary working directory.

It could be any directory where Apps user create the temporary files to be used for bursting.

Back to top

Final Step for XML Bursting – Submit concurrent Program and wait for email

At this point, all your steps are completed and its time to submit the custom concurrent program XX Employee Data – XML Bursting.

Once you submit the program, as soon as it gets completed, it submits bursting program that we had called in After-Report trigger of rdf report of this concurrent time. If this program gets completed successfully and your SMTP server is configured properly, you should received an email as per your control file.

In my case case, following  is the email i received through this concurrent program.

Following is further detail, when i open the attachment in the email i received.