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 when execute 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

SET SERVEROUTPUT ON
CLEAR SCREEN
/*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;

SET SERVEROUTPUT ON
CLEAR SCREEN
/*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;

SET SERVEROUTPUT ON
CLEAR SCREEN
/*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;

SET SERVEROUTPUT ON
CLEAR SCREEN
/*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;

SET SERVEROUTPUT ON
CLEAR SCREEN
/*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_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:

SET SERVEROUTPUT ON
CLEAR SCREEN
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.

---