How to Using Forms Based Applications | Oracle General Ledger
By Jag - October 27, 2014
When building customizations for the Oracle E-Business Suite it might be needed to load data from external sources into specific tables. Tables might be seeded tables, i.e. Open Interface tables, or custom tables which you've added to a special customizations database schema.
To load data from external sources into Oracle eBS you might consider using SQL*Loader to accomplish this. SQL*Loader is using comma seperated (csv) files with data and a so called control file (ctl). The control file tells the system how to import the csv file with data. The control file describes the table and columns to be loaded, what the seperator is of the incoming file etc. Next the SQL*Loader program exports a log file to give an overview of the process, a bad file of records that caused errors in the process, and a discard file for records that were not selected during the load.
To load data from external sources into Oracle eBS you might consider using SQL*Loader to accomplish this. SQL*Loader is using comma seperated (csv) files with data and a so called control file (ctl). The control file tells the system how to import the csv file with data. The control file describes the table and columns to be loaded, what the seperator is of the incoming file etc. Next the SQL*Loader program exports a log file to give an overview of the process, a bad file of records that caused errors in the process, and a discard file for records that were not selected during the load.
Business Process Architecture (BP)BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure
Business Requirements Definition (RD)RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure
Business Requirements Definition (RD)RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements
How to create a simple report using BI Publisher in Oracle E Business Suite R12
By Jag - May 06, 2014
There are several ways to implement a BI Publisher report in Oracle E Business Suite R12, but I’ll explain a simple way where your data is being retrieved in the XML data template file.
The XML data template file consists of 3 parts:
The XML data template file consists of 3 parts:
- Parameters
- DataQuery
- DataStructure
First of all you need a SQL which retrieves the necessary data for your report.
As mentioned earlier the SQL is part of your XML Data Template file.
You’ll probably have to use parameters in your SQL.
Your SQL might be something like this (I have deleted big parts from the ‘from’ and ‘where’ clause, because this is a report used at one of our clients)
As mentioned earlier the SQL is part of your XML Data Template file.
You’ll probably have to use parameters in your SQL.
Your SQL might be something like this (I have deleted big parts from the ‘from’ and ‘where’ clause, because this is a report used at one of our clients)
It is important that you use the exact same parameter names in your SQL, “” and Oracle E Business Suite Concurrent Program and yes: case sensitive.
The data structure has to be defined in your XML file as well.
In this case the data exists of 3 groups: book, property and assets
In this case the data exists of 3 groups: book, property and assets
So in the end your XML Data Template file might look like this:
After you have created your XML Data Template file you need to upload it into Oracle E Business Suite.
You can do this via the following path:
First you need to create a new data definition
Choose a name, the application where the report needs to be added to, the code (concurrent program) and upload the XML Data Template file.
Process XML data and generate a PDF and Store PDF file in same input XML data
By Jag - May 04, 2014
Use Case:
Objective is to process XML data and generate a PDF for the same using XML Publisher API. After the PDF is generated the PDF documented is to be converted into a Base64 encoded string and appended back to the input XML.
Solution:
Following are the steps involved to achieve the above use case
1) Create a RTF template file. This file is used for generating the PDF using Oracle BI Publisher APIs.
2) Generate Java classes for the input XML schema using Apache XML Beans. This is done in order to facilitate accessing the XML file using Java. Any other technology can be used for the same
3) Retrieve the List of files to be processed from a folder. Alternatively the input XML can be picked up from a database, JMS / MQ queue or any alternative data source.
4) Parse the input XML file using the generated XML Bean classes.
5) Extract the application id from the input xml. This is used as the filename for the generated XML file.
6) Create an RTFProcessor to generate a XSL file, which will be used by the FOProcessor to convert the input XML data into a PDF document. Both of these classes are part of Oracle BI Publisher API’s. The FOProcessor class is the front¬end class to use the FO Processing engine. The RTFProcessor class is the front¬end class to use RTF Processing engine. Both of these classes are used to generate the PDF using a RTF template file.
7) Retrieve the bytes for the generated PDF file using a FileInputStream object, this is used to convert the PDF file into a Base64 encoded string.
8) Set the Base64 encoded string, along with other information to the in memory XML bean.
9) Finally convert the XML Bean into an xml string and sore it onto a file system object. Alternatively the XML can be stored in a database or sent to a JMS / MQ queue or any alternative data source.
Sample Code:
Objective is to process XML data and generate a PDF for the same using XML Publisher API. After the PDF is generated the PDF documented is to be converted into a Base64 encoded string and appended back to the input XML.
Solution:
Following are the steps involved to achieve the above use case
1) Create a RTF template file. This file is used for generating the PDF using Oracle BI Publisher APIs.
2) Generate Java classes for the input XML schema using Apache XML Beans. This is done in order to facilitate accessing the XML file using Java. Any other technology can be used for the same
3) Retrieve the List of files to be processed from a folder. Alternatively the input XML can be picked up from a database, JMS / MQ queue or any alternative data source.
4) Parse the input XML file using the generated XML Bean classes.
5) Extract the application id from the input xml. This is used as the filename for the generated XML file.
6) Create an RTFProcessor to generate a XSL file, which will be used by the FOProcessor to convert the input XML data into a PDF document. Both of these classes are part of Oracle BI Publisher API’s. The FOProcessor class is the front¬end class to use the FO Processing engine. The RTFProcessor class is the front¬end class to use RTF Processing engine. Both of these classes are used to generate the PDF using a RTF template file.
7) Retrieve the bytes for the generated PDF file using a FileInputStream object, this is used to convert the PDF file into a Base64 encoded string.
8) Set the Base64 encoded string, along with other information to the in memory XML bean.
9) Finally convert the XML Bean into an xml string and sore it onto a file system object. Alternatively the XML can be stored in a database or sent to a JMS / MQ queue or any alternative data source.
Sample Code:
Method 1 (Standard way):
First we need to create directory on the database server to store files on it.
command:
Create or replace directory as TEMP_DIR '<database server path>/temp'
Now copy all the files to that directory.
Sql>create table reader(reader_id varchar2(10),signature BLOB);
Sql>Insert into reader (reader_id) values ('01');
Create Procedure to update table with signature file.
CREATE OR REPLACE procedure user_signature(v_id IN number)
IS
BEGIN
DECLARE
v_bfile bfile := BFILENAME('TEMP_DIR', 'sign.JPG'); --first parameter in all caps, second is case sensitive
v_lob blob;
BEGIN
select signature into v_lob from reader where reader_id = v_id for update;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.CLOSE(v_bfile);
commit;
END;
END;
Now run it with sql plus
SQL> exec user_signature('01')
or in TOAD (sql editor)
begin
user_signature('01');
end;
Method 2 (Easiest way): Need TOAD !!!
Select table from schema browser and click on data tab.
First we need to create directory on the database server to store files on it.
command:
Create or replace directory as TEMP_DIR '<database server path>/temp'
Now copy all the files to that directory.
Sql>create table reader(reader_id varchar2(10),signature BLOB);
Sql>Insert into reader (reader_id) values ('01');
Create Procedure to update table with signature file.
CREATE OR REPLACE procedure user_signature(v_id IN number)
IS
BEGIN
DECLARE
v_bfile bfile := BFILENAME('TEMP_DIR', 'sign.JPG'); --first parameter in all caps, second is case sensitive
v_lob blob;
BEGIN
select signature into v_lob from reader where reader_id = v_id for update;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.CLOSE(v_bfile);
commit;
END;
END;
Now run it with sql plus
SQL> exec user_signature('01')
or in TOAD (sql editor)
begin
user_signature('01');
end;
Method 2 (Easiest way): Need TOAD !!!
Select table from schema browser and click on data tab.
Creating journal import table to use instead of GL_INTERFACE table. This gave me the option of running Journal Import on multiple tables without the fear of table lock. I have given the code below.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
| DECLAREbulk_errors EXCEPTION;PRAGMA EXCEPTION_INIT (bulk_errors, -17594);TYPE t_int_record_type IS TABLE OF gl_interface%ROWTYPE INDEX BY BINARY_INTEGER;P_INT_RECORDS t_int_record_type;v_sob_id NUMBER;v_run_id NUMBER;v_group_id NUMBER;v_request_id NUMBER;v_request_status BOOLEAN;v_phase VARCHAR2 (100);v_status VARCHAR2 (100);v_dev_phase VARCHAR2 (10);v_dev_status VARCHAR2 (100);v_message VARCHAR2 (100);BEGIN -- Create the custom gl_interface table gl_journal_import_pkg.create_table (table_name => 'GL_CUSTOM_INTERFACE', TABLESPACE => NULL, physical_attributes => NULL, create_n1_index => TRUE, n1_tablespace => NULL, n1_physical_attributes => NULL, create_n2_index => TRUE, n2_tablespace => NULL, n2_physical_attributes => NULL ); |