Forms Data Loader Automation Script with Playback option New

Description:

Oracle Forms supports record and playback feature. This feature is available in every version of Oracle Forms. When we run Oracle Forms in record mode, it will record every keystroke and mouse clicks and writes everything in a file specified by us. When we want to repeat the recorded activity in another system, run the Oracle Forms in playback mode specifying the file, and the forms will do whatever is specified in the file.

The PL/SQL script can be developed for the specific module with the help of the source code of .fdl file, which has been generated while recording the data with the help of Forms Data Loader (FDL) as explained below. This script can be used to generate a .fdl file and to place that file in the server path automatically.

The Form Data Loader (.fdl) file can be generated with the help of the UTL_FILE package in the script, which we have developed.

Step 1:

Create two user accounts in Oracle Apps and grant the desired responsibilities. Both the users should have the same responsibilities.

REC_USER: For Recording

PLAY_USER: For Playback

To create two user accounts login as sysadmin and navigate to System Administrator / Security / User / Define

Step 2:

Edit the ICX: Forms Launcher profile setting. To do this, log in as Sysadmin and navigate to / Profiles / System menu and then select the profile ICX: Forms Launcher and username REC_USER and click Find as shown in the screenshot below.

Then we will get a window showing this profile setting at Site Level, Responsibility Level, Application Level, and User Level. The Site Level Profile option will already contain a value which is used to launch forms. Now to turn on recording at user level, copy this value from Site Level Profile option to User Level profile option and append the “record=” option at the end of the value as shown below.

Before Edit:

http://host.domainname:port/forms/frmservlet

After Edit:

http://host.domainname:port/forms/frmservlet?record=myrec.fdl%20play=dummy.fdl

This will make forms record all the keystrokes in the file “myrec.fdl” in the default location {$INST_TOP/logs/ora/[FORMS Version Number]/forms} directory.

Note: In R12, we have to specify the play=dummy.FDL option; otherwise, Oracle Forms will not record any data.

Next, edit the ICX: Forms Launcher profile setting for PLAY_USER. To do this, log in as Sysadmin and navigate to / Profiles / System menu and then select the profile ICX: Forms Launcher and username PLAY_USER and click OK as shown in the screenshot below.

Then we will get a window showing this profile setting at Site Level, Responsibility Level, Application Level, and User Level. The Site Level Profile option will already contain a value which is used to launch forms. Now to turn on playback at user level, copy this value from Site Level Profile option to User Level profile option and append the “record=playrec.fdl%20play=play.FDL” option at the end of the value, as shown below.

Before Edit:

http://host.domainname:port/forms/frmservlet

After Edit:

http://host.domainname:port/forms/frmservlet?record=playrec.fdl%20play=play.fdl

In the above, Oracle will play the file “play.FDL” and record the log of playback in file “playrec.fdl.” This “playrec.FDL” file can be viewed to view the outcome of playback load later on. Now whenever user REC_USER logons to oracle apps, oracle forms will record all keystrokes and mouse clicks in file myrec.fdl

Step 3:

Create dummy.FDL file (required in R12 only, not required in 11.5.10 or 11.5.9).

Go to {$INST_TOP/logs/ora/[FORMS Version Number]/forms} directory and write the following line.

WINDOW FNDSCSGN SIGNON_WINDOW ACTIVATE 1

For example:

$cd /d01/oracle/EBS/inst/apps/EBS_ebs/logs/ora/10.1.2/forms

$vi dummy.fdl

Save the file.

Step 4:

In a windows client and log in as REC_USER and open the required form and enter at least 2 sample records, please follow the given guidelines while recording.

Guidelines for Recording Loads

Please enter at least 2 similar records. i.e., enter the data in the same number of fields in the same order in both the records.

ü If you have entered data in 10 fields in the first sample record, then enter data for 10 fields in second sample record also. Do not skip fields and do not use extra fields while entering the second record.

ü Do not use different ways to select checkboxes or radio boxes. If you have used the mouse to choose a value for a checkbox or radio box field, then use the mouse to select the same checkbox or radio box field in the second record. If you have used the keyboard, then use the keyboard to select checkbox or radio box fields in both the sample records.

Use Keyboard shortcuts as much as possible instead of mouse clicks while recording.
Enter data directly in LOV fields as much as possible instead of selecting from LOVs
Do not switch between forms while recording
Exit Oracle Application as soon as you finished entering sample records.
For Dropdown lists, enter as many sample records as different values in the drop-down list you want to use.
It is better to rehearse before actually recording by logging as different user with the same responsibility
First attempt to load the data into a simple before loading the data in a complex form.

Use the developed code, which I have mentioned below, to create a .fdl file with the data from the custom table.

DECLARE

file1 UTL_FILE.file_type;

CURSOR conc_prog

IS Custom Table

SELECT ROWNUM, s.*

FROM xx_fnd_conc_prog s

WHERE interface_status = ‘R’;

CURSOR conc_prog_param (ucpn IN VARCHAR2)

SELECT ROWNUM, s1.* Custom Table

FROM xx_fnd_ds_flx_col_usages s1

WHERE interface_status = ‘R’ AND user_concurrent_program_name = ucpn;

CURSOR conc_param_count (ucpn1 IN VARCHAR2)

SELECT COUNT (*)

FROM xx_fnd_ds_flx_col_usages Custom Table

WHERE interface_status = ‘R’ AND user_concurrent_program_name = ucpn1;

cp1 conc_prog%ROWTYPE;

cp2 conc_prog_param%ROWTYPE;

stmt1 VARCHAR2 (3000);

stmt2 VARCHAR2 (3000);

stmt3 VARCHAR2 (3000);

head VARCHAR2 (3000);

footer VARCHAR2 (3000);

l_count NUMBER;

l_count1 NUMBER;

i NUMBER DEFAULT 0;

BEGIN

SELECT COUNT (*) Custom Table

INTO l_count1

FROM xx_fnd_conc_prog

WHERE interface_status = ‘R’;

file1 := UTL_FILE.fopen (‘/usr/tmp’, ‘fnd_conc_prog.fdl’, ‘w’);

head :=

‘# Forms Event File

# Created: ‘

|| TO_CHAR (SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’)

|| ‘

WINDOW FNDCPMCP CONC_PROG ACTIVATE 3

WINDOW FNDCPMCP CONC_PROG DEACTIVATE 3

WINDOW FNDCPMCP CONC_PROG ACTIVATE 3′;

UTL_FILE.putf (file1, head);

UTL_FILE.new_line (file1);

FOR cp1 IN conc_prog

LOOP

stmt1: =

‘KEY Create_record

VALUE FNDCPMCP CONC_PROG USER_CONCURRENT_PROGRAM_NAME ‘

|| ‘ ‘

|| cp1.ROWNUM

|| ‘ “‘

|| cp1.user_concurrent_program_name

|| ‘”‘

|| ‘

KEY Next_item

VALUE FNDCPMCP CONC_PROG CONCURRENT_PROGRAM_NAME ‘

|| ‘ ‘

|| cp1.ROWNUM

|| ‘ “‘

|| cp1.concurrent_program_name

|| ‘”‘

|| ‘

KEY Next_item

VALUE FNDCPMCP CONC_PROG APPLICATION_NAME ‘

|| ‘ ‘

|| cp1.ROWNUM

|| ‘ “‘

|| cp1.application_name

|| ‘”‘

|| ‘

KEY Next_item

VALUE FNDCPMCP CONC_PROG DESCRIPTION ‘

|| ‘ ‘

|| cp1.ROWNUM

|| ‘ “‘

|| cp1.description

|| ‘”‘

|| ‘

KEY Next_item

VALUE FNDCPMCP CONC_PROG EXECUTABLE_NAME ‘

|| ‘ ‘

|| cp1.ROWNUM

|| ‘ “‘

|| cp1.executable_name

|| ‘”‘

|| ‘

KEY Next_item

KEY Next_item’

|| ‘

CLICK FNDCPMCP CONC_PROG OUTPUT_FILE_TYPE ‘

|| ‘ ‘

|| cp1.ROWNUM

|| ‘ “‘

|| cp1.output_file_type

|| ‘”‘

|| ‘

KEY Next_item

KEY Next_item’

|| ‘

VALUE FNDCPMCP CONC_PROG PRINT_STYLE_NAME ‘

|| ‘ ‘

|| cp1.ROWNUM

|| ‘ “‘

|| cp1.output_print_style

|| ‘”‘

|| ‘

KEY Commit_form

CLICK FNDCPMCP CONC_PROG PARAMETERS ‘

|| ‘ ‘

|| cp1.ROWNUM

|| ‘ ‘

|| ‘ MOUSE

WINDOW FNDCPMCP CONC_PROG DEACTIVATE 3′;

UTL_FILE.putf (file1, stmt1);

UTL_FILE.new_line (file1);

FOR cp2 IN conc_prog_param (cp1.user_concurrent_program_name)

LOOP

i := i + 1;

OPEN conc_param_count (cp1.user_concurrent_program_name);

FETCH conc_param_count

INTO l_count;

CLOSE conc_param_count;

IF i < l_count

THEN

stmt2: =

‘WINDOW FNDCPMCP ARGUMENTS ACTIVATE 3

VALUE FNDCPMCP ARGUMENTS COLUMN_SEQ_NUM ‘

|| ‘ ‘

|| cp2.ROWNUM

|| ‘ ‘

|| cp2.column_seq_num

|| ‘

KEY Next_item

VALUE FNDCPMCP ARGUMENTS END_USER_COLUMN_NAME ‘

|| ‘ ‘

|| cp2.ROWNUM

|| ‘ “‘

|| cp2.end_user_column_name

|| ‘ “‘

|| ‘

KEY Next_item

VALUE FNDCPMCP ARGUMENTS DESCRIPTION ‘

|| ‘ ‘

|| cp2.ROWNUM

|| ‘ “‘

|| cp2.description

|| ‘”‘

|| ‘

KEY Next_item

VALUE FNDCPMCP ARGUMENTS VALUE_SET_NAME ‘

|| ‘ ‘

|| cp2.ROWNUM

|| ‘ “‘

|| cp2.flex_value_set_name

|| ‘”‘

|| ‘

KEY Next_item

VALUE FNDCPMCP ARGUMENTS SRW_PARAM ‘

|| ‘ ‘

|| cp2.ROWNUM

|| ‘ “‘

|| cp2.srw_param

|| ‘”‘

|| ‘

KEY Commit_form

KEY Next_item ‘;

UTL_FILE.putf (file1, stmt2);

UTL_FILE.new_line (file1);

END IF;

IF i = l_count

THEN

stmt3: =

‘WINDOW FNDCPMCP ARGUMENTS ACTIVATE 3

VALUE FNDCPMCP ARGUMENTS COLUMN_SEQ_NUM ‘

|| ‘ ‘

|| cp2.ROWNUM

|| ‘ ‘

|| cp2.column_seq_num

|| ‘

KEY Next_item

VALUE FNDCPMCP ARGUMENTS END_USER_COLUMN_NAME ‘

|| ‘ ‘

|| cp2.ROWNUM

|| ‘ “‘

|| cp2.end_user_column_name

|| ‘ “‘

|| ‘

KEY Next_item

VALUE FNDCPMCP ARGUMENTS DESCRIPTION ‘

|| ‘ ‘

|| cp2.ROWNUM

|| ‘ “‘

|| cp2.description

|| ‘”‘

|| ‘

KEY Next_item

VALUE FNDCPMCP ARGUMENTS VALUE_SET_NAME ‘

|| ‘ ‘

|| cp2.ROWNUM

|| ‘ “‘

|| cp2.flex_value_set_name

|| ‘”‘

|| ‘

KEY Next_item

VALUE FNDCPMCP ARGUMENTS SRW_PARAM ‘

|| ‘ ‘

|| cp2.ROWNUM

|| ‘ “‘

|| cp2.srw_param

|| ‘”‘

|| ‘

KEY Commit_form

WINDOW FNDCPMCP ARGUMENTS CLOSE 3

WINDOW FNDCPMCP ARGUMENTS DEACTIVATE 3

WINDOW FNDCPMCP CONC_PROG ACTIVATE 3′;

UTL_FILE.putf (file1, stmt3);

UTL_FILE.new_line (file1);

END IF;

END LOOP;

i := 0;

END LOOP;

footer :=

‘WINDOW FNDSCSGN NAVIGATOR ACTIVATE 1

KEY Exit

USER_EXIT CHOICE OK’;

UTL_FILE.putf (file1, footer);

UTL_FILE.new_line (file1);

UTL_FILE.fclose (file1);

END;

Step 5:

After we have successfully entered at least 2 sample records and exit the Oracle Applications. Oracle will record all activity in the file myrec.fdl. Now we have to transfer this file to the Windows machine where we are running Forms Data Loader. We can use FTP or any similar tools to transfer this file from Oracle Apps Server {$INST_TOP/logs/ora/[FORMS Version Number]/forms} directory to the Windows machine.

Step 6:

Open the recorded file “myrec.fdl” in Forms Data Loader, FDL will ask us to identify the First Field and Last Field of repeating section as shown below. Here, select the First and Last Repeating Fields of the First Sample record you have entered in the above step while recording the file.

Accurately selecting the First and Last field of the repeating section is crucial for correct execution of load.

Step 7:

Once we have identified the first and last field of repeating section, Forms Data Loader will show a spreadsheet showing the records we have entered in forms, as shown below.

Now we can add records to the spreadsheet by directly typing or importing from Excel CSV files or any delimited file. We can also copy and paste the data from any spreadsheet.

Once we have populated the spreadsheet, save the file. When we save the file, Forms Data Loader will generate the code necessary for playing the file in Oracle Forms. We can click the button to view the code.

Step 8 :

After we have populated the sheet, we have to now again transfer this file myrec.fdl by renaming it to “play.fdl” to the Oracle Apps Server {$INST_TOP/logs/ora/[FORMS Version Number]/forms} directory for playback. We can use FTP or similar tool to achieve this.

Remember one thing; the file should be renamed to “play.fdl” when we transfer it, because we have mentioned this filename in Forms launch settings for PLAY_USER.

Step 9:

Now login as PLAY_USER from any windows client and open the specific form i.e., the form in which we want to load the data. As soon as we open the specific form, Oracle will start playing the file. Sometimes we will not see the actual fields getting populated, but we can see the progress on the status bar of Oracle Forms. This is perfectly normal as Oracle is doing the load in the background.

Summary:

Forms Data Loader (FDL) is a tool to load data from Excel or CSV files into Oracle Apps 11i / R12 through front end forms. It provides an easy to use and cost-effective solution for loading test or legacy data into Oracle E-Business Suite. Although it is specially built for loading data into Oracle to load data in any application Forms running under Windows like SAP and others. Requires no specialized technical knowledge and can be used by any nontechnical end users as data is loaded through the front end.

queries?

Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.

Venkatesh Balasubramanian