RICEFW
Index
1. RICEFW
2. RTF Template (.rtf)
3. Reports Builder (.rdf)
4. Image/Logo Path
5. Removing files from Data Definition
6. Workflow
7. Staging Tables
8. SQL*Loader
9. UTIL_FILE
10. External Table
11. Formatting Month using XDOXSLT
12. Difference between Lexical Parameter and Bind Variable in Oracle Reports
13. System Parameters used in Reports Builder
14. Discoverer Reports
1. RICEFW
2. RTF Template (.rtf)
To eradicate exponential format or to increase line spacing to fit the column in excel output use below syntax in data definition (RTF – word/excel):
<fo:bidi-override direction=”ltr” unicode-bidi=”bidi-override”><?GIVE_ELEMENT_NAME?></fo:bidi-override>
3. Reports Builder (.rdf)
srw.MESSAGE (1002, ‘SQLERRM=’ || SQLERRM);
In rdf the fnd_file.put_line(fnd_file.LOG, ‘Message’) doesn’t work…we have to use
srw.MESSAGE (1002, ‘SQLERRM=’ || SQLERRM); where 1002 is some random number for identification, text message || SQL Error Message
4. Image/Logo Path
The images/ logos are placed in media directory and the name of the directory is OA_MEDIA.
In rtf right click on logo >> Format Picture >> Under Format Picture, select Layout & properties tab >> Go to ALT TEXT >> Under description enter value as given below:
url:{concat(‘$[OA_MEDIA]/’,.//Enter element name)}
In few instances we have to mention complete OM_MEDIA path. Get the correct path from DBA’s as this varies from instance to instance.
Ex: url:{concat(‘/abcapp/apps/apps_st/comn/java/classes/oracle/apps/media/’,.//Enter element name)}
5. Removing files from Data Definition
We wont be able to delete files from data definition from XML Publisher responsibility so have to go use below syntax.
DELETE FROM xdo_lobs
WHERE lob_code = ‘Give Data definition code’
AND lob_type = ‘BURSTING_FILE’ or ‘DATA_TEMPLATE’
Also we can download templates from backend using table xdo_lobs where files get saved in file_data column.
6. Workflow
Workflow Architecture 
Activities in Workflow:
1. Attributes
2. Processes
3. Notifications
4. Functions
5. Events
6. Messages
7. Lookup Types
7. Staging Tables
The legacy system data can be loaded into R12 staging table using either of three concepts.
1. SQL *Loader
2. UTIL_FILE
3. External Table
4. Web ADI 
8. SQL*Loader
An utility provided by Oracle to load data from external files to database. 
Here the data file and control file are input files and log file, bad file and discard file are outputs to SQL Loader.
Data File: Which contains actual data (.csv or .dat) and usually placed in path like /usr/tmp (select * from dba_directories)
Control File: Gives the format in which the data file has to be loaded and gets placed in applications top (like $APP_TOP/bin)
Log File: Contains log information like successful/ error records. This is default file.
Bad File: Invalid format records
Discard File: Rejected records
SQLLDR to be executed in command prompt.
SQLLDR SCOTT/TIGER LOAD.ctl LOAD.log LOAD.bad LOAD.dat — Syntax to execute in command prompt and should be in same order. In case we use named parameters then no need to follow same order like SQLLDR USERID=SCOTT/TIGER CONTROL=LOAD.ctl DATA=LOAD.dat
Once the data is loaded into table then COMMIT gets auto executed internally.
--Creation of Staging table
DROP TABLE CUST_STG_TAB CASCADE CONSTRAINTS;
CREATE TABLE CUST_STG_TAB
(
CUSTOMER_ID NUMBER,
CUSTOMER_NAME VARCHAR2(400 BYTE),
ACCOUNT_NUMBER VARCHAR2(100 BYTE)
);--Creation of control file:
***************************************************************************
-- File Name : CUSTOMER_LOAD.ctl
-- Type : CONTROL FILE
-- Purpose : To load Customer Data into R12 staging table
***************************************************************************
OPTIONS (SKIP=1) --This skips first row (column names) in data file and is optional
LOAD DATA
CHARACTERSET 'UTF8'
INFILE 'CUSTOMER_DATA.csv' --Data file name and use INFILE only when data file and control file are placed in same folder
DISCARDFILE 'sample_discard_file.txt'
TRUNCATE(or)APPEND INTO TABLE CUST_STG_TAB WHEN account_number = "1234"--Staging table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
CUSTOMER_ID "TRIM(:CUSTOMER_ID)"
, CUSTOMER_NAME "TRIM(:CUSTOMER_NAME)"
, ACCOUNT_NUMBER "TRIM(:ACCOUNT_NUMBER)"
)Save the file as .ctl extension
Data File:
Save the excel file as .csv extension
A sample data file (.csv) with two records is shown below.
| CUSTOMER_ID | CUSTOMER_NAME | ACCOUNT_NUMBER |
| 1001 | ABC Corp Limited | 1234 |
| 1002 | XYZ Corp Limited | 5678 |
Prior to sending the data file or control file to DBA’s to place at /usr/tmp path or $APPS_TOP/bin path we can test the files in Toad by placing the two files under one folder.
Open Toad >> Database >> Import >> SQL *Loader Wizard >> Check Use control file >> Next >> Browse control file under control file name >> Next >> Check Execute Now >> Click Finish >> Check the successful message in log tab.
Now check SELECT * FROM CUST_STG_TAB, two records will be shown.
→ For fixed length of data we give positions as POSITION(1:5). It consider from first character to fifth character.
→ In SQLLDR we have INTEGER datatype not NUMBER datatype.
→ For certain fields we get delimiter comma (,) but for fields we do not. Then we have to declare on that column independently. Ex: column_name datatype TERMINATED BY ‘:’
→ Declare date datatype column and mention format. column_name DATE “DD-MONTH-YYYY”
→ Auto generate sequence number. column_name SEQUENCE(MAX, 1)
→ If specific token is found at first place then use CONTINUEIF (1) = ‘*’. Here * considers as start of row.
→ If specific token is found at end then use CONTINUEIF LAST <> ‘|’. Here first row considers till end of |.
→ CONTINUEIF THIS (1:2) = ‘**’
→ Say we have emp 1234 Baker working on three depts (101, 102, 103) then we have to convert de-normalize data into normalize data.
→ NULLIF — We cannot use NVL in SQLLDR rather we have to use NULLIF. In fixed length we use NULLIF in NUMBER datatype columns and for CHAR columns its not required.
→ If we use INDEXES then its direct path loading, else it will be conventional path
→ We can manipulate data using SQLLDR
→ Multiple files data into multiple tables: File structure should be same.
9. UTIL_FILE
UTL_FILE.FILE_TYPE; — Pointer to find the file location
UTL_FILE.FOPEN; — Open the existing file or create a new file
UTL_FILE.PUT_LINE; — Transfer data from DB to flat file
UTL_FILE.GET_LINE; — Transfer data from flat file to DB
UTL_FILE.FCLOSE; — Closes the file
Below are UTIL_FILE Exceptions:
UTL_FILE.invalid_operation
UTL_FILE.invalid_path
UTL_FILE.invalid_mode
UTL_FILE.invalid_filehandle
UTL_FILE.read_error
UTL_FILE.internal_error
UTL_FILE.write_error
UTL_FILE.access_denied
--Note
--SELECT * FROM all_directories;
--The directory name should be considered one of the value from value column in below query
--SELECT * FROM v$parameter WHERE name like '%utl_file%';
--The privilege should be execute in below query
--SELECT * FROM all_tab_privs where table_name = 'UTL_FILE';
--Check this directory has read and write privileges in below query
--SELECT * FROM all_tab_privs where table_name = 'ODPDIR';
DECLARE
lv_write_file_handle utl_file.file_type;
lv_fileoutput VARCHAR2(1000) := 'Outbound_Interface';
BEGIN
lv_write_file_handle := utl_file.fopen('ODPDIR','write_file.txt','W');
IF
utl_file.is_open(lv_write_file_handle)
THEN
utl_file.put_line(lv_write_file_handle, lv_fileoutput);
END IF;
IF
utl_file.is_open(lv_write_file_handle)
THEN
utl_file.fclose(lv_write_file_handle);
END IF;
IF
NOT utl_file.is_open(lv_write_file_handle)
THEN
dbms_output.put_line('File is closed successfully..!!');
dbms_output.put_line('Data '||lv_fileoutput|| ' is written successfully');
END IF;
COMMIT;
EXCEPTION
WHEN utl_file.invalid_operation THEN
dbms_output.put_line('invalid operation ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
dbms_output.put_line('invalid path ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
dbms_output.put_line('invalid mode ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
dbms_output.put_line('invalid filehandle ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.read_error THEN
dbms_output.put_line('read error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
dbms_output.put_line('internal error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.write_error THEN
dbms_output.put_line('write error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.access_denied THEN
dbms_output.put_line('access denied ' || sqlerrm);
utl_file.fclose_all;
END;
--Note
--SELECT * FROM all_directories;
--The directory name should be considered one of the value from value column in below query
--SELECT * FROM v$parameter WHERE name like '%utl_file%';
--The privilege should be execute in below query
--SELECT * FROM all_tab_privs where table_name = 'UTL_FILE';
--Check this directory has read and write privileges in below query
--SELECT * FROM all_tab_privs where table_name = 'ODPDIR';
--Being inbound, place a file read_file.txt with message as 'Inbound_Interface'
--Give 0777 permissions to file
DECLARE
lv_read_file_handle utl_file.file_type;
lv_fileinput VARCHAR2(1000);
BEGIN
lv_read_file_handle := utl_file.fopen('ODPDIR','read_file.txt','R');
IF
utl_file.is_open(lv_read_file_handle)
THEN
utl_file.get_line(lv_read_file_handle, lv_fileinput);
END IF;
IF
utl_file.is_open(lv_read_file_handle)
THEN
utl_file.fclose(lv_read_file_handle);
END IF;
IF
NOT utl_file.is_open(lv_read_file_handle)
THEN
dbms_output.put_line('File is closed successfully..!!');
dbms_output.put_line('Data '||lv_fileinput|| ' read successfully');
END IF;
COMMIT;
EXCEPTION
WHEN utl_file.invalid_operation THEN
dbms_output.put_line('invalid operation ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
dbms_output.put_line('invalid path ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
dbms_output.put_line('invalid mode ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
dbms_output.put_line('invalid filehandle ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.read_error THEN
dbms_output.put_line('read error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
dbms_output.put_line('internal error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.write_error THEN
dbms_output.put_line('write error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.access_denied THEN
dbms_output.put_line('access denied ' || sqlerrm);
utl_file.fclose_all;
END;
--Note
--SELECT * FROM all_directories;
--The directory name should be considered one of the value from value column in below query
--SELECT * FROM v$parameter WHERE name like '%utl_file%';
--The privilege should be execute in below query
--SELECT * FROM all_tab_privs where table_name = 'UTL_FILE';
--Check this directory has read and write privileges in below query
--SELECT * FROM all_tab_privs where table_name = 'ODPDIR';
DECLARE
lv_write_file_handle utl_file.file_type;
TYPE writerecords IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
rec_writerecords writerecords;
BEGIN
rec_writerecords(1) := 'Record one : This is first line';
rec_writerecords(2) := 'Record two : This is second line';
rec_writerecords(3) := 'Record three : This is third line';
rec_writerecords(4) := 'Record four : This is fourth line';
rec_writerecords(5) := 'Record five : This is fifth line';
lv_write_file_handle := utl_file.fopen('ODPDIR','write_file.txt','W');
IF
utl_file.is_open(lv_write_file_handle)
THEN
FOR my_table_index IN 1..rec_writerecords.COUNT
LOOP
utl_file.put_line(lv_write_file_handle, rec_writerecords(my_table_index));
dbms_output.put_line('Data with index '||my_table_index|| ' is '||rec_writerecords(my_table_index)||' written successfully');
END LOOP;
END IF;
IF
utl_file.is_open(lv_write_file_handle)
THEN
utl_file.fclose(lv_write_file_handle);
END IF;
IF
NOT utl_file.is_open(lv_write_file_handle)
THEN
dbms_output.put_line('File is closed successfully..!!');
END IF;
COMMIT;
EXCEPTION
WHEN utl_file.invalid_operation THEN
dbms_output.put_line('invalid operation ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
dbms_output.put_line('invalid path ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
dbms_output.put_line('invalid mode ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
dbms_output.put_line('invalid filehandle ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.read_error THEN
dbms_output.put_line('read error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
dbms_output.put_line('internal error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.write_error THEN
dbms_output.put_line('write error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.access_denied THEN
dbms_output.put_line('access denied ' || sqlerrm);
utl_file.fclose_all;
END;
--Note : This code has issue
--SELECT * FROM all_directories;
--The directory name should be considered one of the value from value column in below query
--SELECT * FROM v$parameter WHERE name like '%utl_file%';
--The privilege should be execute in below query
--SELECT * FROM all_tab_privs where table_name = 'UTL_FILE';
--Check this directory has read and write privileges in below query
--SELECT * FROM all_tab_privs where table_name = 'ODPDIR';
DECLARE
lv_read_file_handle utl_file.file_type;
TYPE readrecords IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
rec_readrecords readrecords;
lv_read_from_file VARCHAR2(1000);
ln_my_index NUMBER(2) := 0;
BEGIN
lv_read_file_handle := utl_file.fopen('ODPDIR','read_file.txt','R');
IF
utl_file.is_open(lv_read_file_handle)
THEN
LOOP
BEGIN
utl_file.get_line(lv_read_file_handle, rec_readrecords(ln_my_index + 1));
ln_my_index := ln_my_index + 1;
EXCEPTION WHEN NO_DATA_FOUND
THEN dbms_output.put_line('Did not found the record number '||(ln_my_index+1));
END;
END LOOP;
END IF;
IF
utl_file.is_open(lv_read_file_handle)
THEN
utl_file.fclose(lv_read_file_handle);
END IF;
IF
NOT utl_file.is_open(lv_read_file_handle)
THEN
dbms_output.put_line('File is closed successfully..!!');
dbms_output.put_line('Data '||rec_readrecords(ln_my_index + 1)|| ' read successfully');
END IF;
COMMIT;
EXCEPTION
WHEN utl_file.invalid_operation THEN
dbms_output.put_line('invalid operation ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
dbms_output.put_line('invalid path ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
dbms_output.put_line('invalid mode ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
dbms_output.put_line('invalid filehandle ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.read_error THEN
dbms_output.put_line('read error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
dbms_output.put_line('internal error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.write_error THEN
dbms_output.put_line('write error ' || sqlerrm);
utl_file.fclose_all;
WHEN utl_file.access_denied THEN
dbms_output.put_line('access denied ' || sqlerrm);
utl_file.fclose_all;
END;
lv_file_handle UTL_FILE.file_type;
lv_dir_name all_directories.directory_name%TYPE := 'Specify Directory Name';
lv_file_name VARCHAR2 (100) := 'CUSTOMER_DATA.dat';
lv_file_output VARCHAR2 (1000);
BEGIN
lv_file_handle := UTL_FILE.FOPEN (lv_dir_name, lv_file_name,'R',32767);
IF UTL_FILE.IS_OPEN (lv_file_handle)
THEN
DBMS_OUTPUT.PUT_LINE ('File Opened Successfully');
END IF;
UTL_FILE.GET_LINE (lv_file_handle, lv_file_output);
DBMS_OUTPUT.PUT_LINE ('Loading data to DB has started');
INSERT INTO CUST_STG_TAB VALUES lv_file_output;
DBMS_OUTPUT.PUT_LINE ('Loading data to DB has completed');
IF SQL%FOUND
THEN
DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || 'rows loaded successfully');
COMMIT;
END IF;
UTL_FILE.FCLOSE (lv_file_handle);
EXCEPTION
WHEN UTL_FILE.invalid_operation
THEN
DBMS_OUTPUT.PUT_LINE ('invalid operation ' || SQLERRM);
UTL_FILE.fclose_all;
WHEN UTL_FILE.invalid_path
THEN
DBMS_OUTPUT.PUT_LINE ('invalid path ' || SQLERRM);
UTL_FILE.fclose_all;
WHEN UTL_FILE.invalid_mode
THEN
DBMS_OUTPUT.PUT_LINE ('invalid mode ' || SQLERRM);
UTL_FILE.fclose_all;
WHEN UTL_FILE.invalid_filehandle
THEN
DBMS_OUTPUT.PUT_LINE ('invalid filehandle ' || SQLERRM);
UTL_FILE.fclose_all;
WHEN UTL_FILE.read_error
THEN
DBMS_OUTPUT.PUT_LINE ('read error ' || SQLERRM);
UTL_FILE.fclose_all;
WHEN UTL_FILE.internal_error
THEN
DBMS_OUTPUT.PUT_LINE ('internal error ' || SQLERRM);
UTL_FILE.fclose_all;
WHEN UTL_FILE.write_error
THEN
DBMS_OUTPUT.PUT_LINE ('write error ' || SQLERRM);
UTL_FILE.fclose_all;
WHEN UTL_FILE.access_denied
THEN
DBMS_OUTPUT.PUT_LINE ('access denied ' || SQLERRM);
UTL_FILE.fclose_all;
END;10. External Table
Loading data from excel (any legacy system) to DB.
Prior to creation of external table we need to place the data file at specified location (/usr/tmp) and please ensure the file gets placed in DB tier rather than application tier. Also make sure you remove all the comments (–) from below syntax, else results in error while using SELECT query.
Syntax:
CREATE TABLE CUST_STG_TAB(
CUSTOMER_ID NUMBER,
CUSTOMER_NAME VARCHAR2(100),
ACCOUNT_NUMBER VARCHAR2(100)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY ECX_UTL_XSLT_DIR_OBJ --Directory name from DBA directories table (dba_directories)
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
CHARACTERSET 'WE8MSWIN1252'
LOGFILE 'CUST_STG_TAB.log' -- log file name
BADFILE 'CUST_STG_TAB.bad' -- bad file name
SKIP 1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
LOCATION (ECX_UTL_XSLT_DIR_OBJ:'CUSTOMER_DATA.csv')--Data file placed at the Specified Path
)
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;Now check SELECT * FROM CUST_STG_TAB, two records will be shown.
Subsequently the data file need to be replaced in the directory to load new data.
Difference between SQL *Loader and External Table
| SQL * Loader | External Table |
| This is a command line utility to load data from flat files to staging tables. | The flat file data can be accessed directly as if it were in database table. |
| Can be loaded from any system having Oracle client installed | The files has to be accessed from system where DB is installed via pre-defined directory |
11. Formatting Month using XDOXSLT
Set month which has date format like 20-NOV-2015
<?xdoxslt:set_variable($_XDOCTX, ‘P_START_DATE’,xdoxslt:format_date(START_DATE,’mmyyyy’,’yyyy/mmm/dd’,$_XDOLOCALE, $_XDOTIMEZONE))?>
<?xdoxslt:set_variable($_XDOCTX, ‘P_END_DATE’,xdoxslt:format_date(START_DATE,’mmyyyy’,’yyyy/mmm/dd’,$_XDOLOCALE, $_XDOTIMEZONE))?>
Comparing with month
<?if:xdoxslt:format_date(LEAVE_START_DATE,’MMyyyy’,’dd-MMM-yyyy’,$_XDOLOCALE, $_XDOTIMEZONE)= xdoxslt:get_variable($_XDOCTX, ‘P_START_DATE’) or
xdoxslt:format_date(LEAVE_END_DATE,’MMyyyy’,’dd-MMM-yyyy’,$_XDOLOCALE, $_XDOTIMEZONE)= xdoxslt:get_variable($_XDOCTX, ‘P_START_DATE’)?>
12. Difference between Lexical Parameter and Bind Variable in Oracle Reports
13. System Parameters used in Reports Builder
BACKGROUND, COPIES, CURRENCY, DECIMAL, DESFORMAT, DESNAME, DESTYPE, MODE, ORIENTATION, PRINTJOB, THOUSANDS
Difference between Compile and Incremental Compile
In Compile all the PL/SQL within the reports will be compiled whereas in Incremental Compile only the modified the PL/SQL code gets compiled. When compiling for the first time, we should always go with Compile rather than Incremental Compile.
14. Discoverer Reports
We have four categories in Discoverer.
1. Discoverer Administration Edition– Application based
2. Discoverer Desktop Edition — Application based
3. Discoverer Plus — Browser based
4. Discoverer Viewer — Browser based
Discoverer Administration Edition — We create Business area >> Folders >> SQL Queries.
Administrator and desktop editions both use same application.