Steps to create a XML Publisher Report
1] Add the “Xml Publisher Administrator” Responsibility to the user through the front end.
2] Create the Report(Data Model or we can say the .Rdf file) using Oracle Report Builder.
3] Set the user parameter as p_conc_request_id.
4] Add the default values to the Before Report and After Report triggers(not mandatory)
5] Ftp the Report to the Cust_Top/Report/Us.
6] Open the Oracle E-Business Suite then go to Sysadmin>Concurrent>Program>Executables, Here we have to create one executable file for that Rdf.
7] Then go to Sysadmin>Concurrent>Program>Define, Here we have to make a Concurrent Program for that Executable. Make sure that the output format must be XML.
8] Goto the Sysadmin>Security>Responsibility>Define. Query for the Xml Publisher Administrator. See the Request Group attached to this. Attach the Concurrent Program to this Request Group.
9] Design the template in Ms Word(Using the .Rtf file).
10] Goto responsibility XML PUBLISHER ADMINISTRATOR. Then Goto HOME>DATA DEFINITION>CREATE DATA DEFINITION and create a new data definition. Make sure that your Data Definition’s Code should be same as Concurrent Program’s Short Name used by you to create the Rdf file.
11] Now go to Xml publisher administrator>Home>Template. Create a new template with template type=’Rtf’. Then upload the RTF File by browsing the path.
12] Now go to the Responsibility and run the request.
Setting the Password for PDF File sent through XML Publisher
Open the rtf for which you want to set password and do the following things
1) Open the .rtf
2) Go to File – > Properties
Create a new custom property
a) Name : xdo-pdf-open-password
Type : text
Value : Either Hard Code the value / or get the value for xml data
b) Name : xdo-pdf-security
Type : text
Value : true
Note : property name should always start with xdo- .
Query to get details of XML Report
SELECT fndcptl.USER_CONCURRENT_PROGRAM_NAME UserConcurrentProgram,
fndcp.CONCURRENT_PROGRAM_NAME ConcurrentProgramShortName,
fndex.EXECUTION_FILE_NAME ExecutionFileName,
datadefinitiontl.DATA_SOURCE_NAME DataDefinitionName,
datadefinition.DATA_SOURCE_CODE DataDefinitionCode,
templatestl.TEMPLATE_NAME TemplateName,
templates.TEMPLATE_CODE TemplateCode,
lobs.FILE_NAME FileName
FROM fnd_executables fndex,
fnd_concurrent_programs fndcp,
fnd_concurrent_programs_tl fndcptl,
xdo_ds_definitions_b datadefinition,
xdo_ds_definitions_tl datadefinitiontl,
xdo_templates_b templates,
xdo_templates_tl templatestl,
xdo_lobs lobs
WHERE fndex.EXECUTABLE_ID = fndcp.EXECUTABLE_ID
AND fndcp.CONCURRENT_PROGRAM_ID = fndcptl.CONCURRENT_PROGRAM_ID
AND fndcp.CONCURRENT_PROGRAM_NAME = datadefinition.DATA_SOURCE_CODE
AND datadefinition.DATA_SOURCE_CODE =
datadefinitiontl.DATA_SOURCE_CODE
AND datadefinitiontl.DATA_SOURCE_CODE = templates.DATA_SOURCE_CODE
AND templates.TEMPLATE_CODE = templatestl.TEMPLATE_CODE
AND templatestl.TEMPLATE_CODE = lobs.LOB_CODE
AND lobs.LOB_TYPE = ‘TEMPLATE_SOURCE’
AND fndex.EXECUTABLE_NAME = ‘XXAJ_SALES_ORDER_REPORT’
Benefits of Oracle XML Publisher
» Document Management Solution well integrated with Oracle Applications.
» A solution which authors, manages and delivers the Business documents.
» Meets the Business Requirements like corporate identity.
» Removes the Complexity
» Reduces Maintenance Cost
» Reduces Total Cost
» One Template -> Multiple Outputs -> PDF,HTML,EXCEL,RTF
» Business Consultants can use familiar desktop tools like Word, PDF, etc.
» Extract one time ->-> Publish multiple times
Developing XML Publisher Report – using Data Source as PL/SQL Stored Procedure
Background:
Developing sample XML Publisher Report with Executable Method as ‘PL/SQL Stored Procedure’
Prerequisite for the below Example:
1. Create a table
CREATE TABLE demo_products
( product_code NUMBER,
product_name VARCHAR2 (100));
2. Insert Values
INSERT INTO demo_products
VALUES (569, ‘Oracle Cost Management’);
3. Issue Commit
1. Create a Package Spec & Body with a single Procedure
Spec:
CREATE OR REPLACE PACKAGE APPS.MY_PACKAGE
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER);
END MY_PACKAGE;
/
Body:
CREATE OR REPLACE PACKAGE BODY APPS.MY_PACKAGE
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER)
IS
l_qryCtx DBMS_XMLGEN.ctxHandle;
l_query VARCHAR2 (32000);
l_length NUMBER (10);
l_xmlstr VARCHAR2 (32000);
l_offset NUMBER (10) := 32000;
l_retrieved NUMBER (10) := 0;
l_result CLOB;
l_no_rows NUMBER;
BEGIN
l_query := ‘SELECT product_code, product_name
FROM demo_products
WHERE product_code = ‘ || p_product_id;
l_qryCtx := DBMS_XMLGEN.newContext (l_query);
— set rowset tag to PRODUCTS and row tag to PRO_DETAILS
DBMS_XMLGEN.setRowSetTag (l_qryCtx, ‘PRODUCTS’);
DBMS_XMLGEN.setRowTag (l_qryCtx, ‘PRO_DETAILS’);
— now get the result
l_result := DBMS_XMLGEN.getXML (l_qryCtx);
l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
FND_FILE.put_line (FND_FILE.LOG, ‘No of rows processed= ‘ || l_no_rows);
l_length := NVL (DBMS_LOB.getlength (l_result), 0);
FND_FILE.put_line (FND_FILE.LOG, ‘Length= ‘ || l_length);
LOOP
EXIT WHEN l_length = l_retrieved;
IF (l_length – l_retrieved) < 32000
THEN
SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;
l_retrieved := l_length;
fnd_file.put_line (fnd_file.output, l_xmlstr);
ELSE
SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
INTO l_xmlstr
FROM DUAL;
l_retrieved := l_retrieved + l_offset;
fnd_file.put_line (fnd_file.output, l_xmlstr);
END IF;
END LOOP;
DBMS_XMLGEN.closeContext (l_qryCtx);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
raise_application_error (-20001, ‘Error in procedure MY_PACKAGE.report’);
END REPORT;
END MY_PACKAGE;
/
2. Define Executable
Navigation: Application Developer > Concurrent > Executable
Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: MY_PACKAGE.REPORT
3. Define Concurrent Program
Navigation: Application Developer > Concurrent > Program
Provide Program, Short name, Application, Description & also
– Executable Name as defined in the above step
– Output Format should be XML
– Define a Parameter p_product_id
– Associate Concurrent Program to the Request Group.
4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.
5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template
6. Run the Concurrent Program to see the output
How to create Data Template in xml publisher
The Data Template Definition:
The data template is an XML document that consists of four basic sections:
1. Define Parameters
2. Define triggers
3. Define Data Query
4. Define Data Structure
This structure is shown in the following graphic:
To build XML report the following steps are involved.
Step 1: Designthe Data Template XML File.
Step 2:Create the Data Definition in XML Publisher and associate it with Data Template
Step 3:Define a Concurrent Program to generate the Data XML File.
Step 4: Define the RTF Template using the Generated Data XML.
Step 5: Registering the Template with XML Publisher
Step 6: Execute the concurrent program to generate the PDF report.
Step 2: Create the Data Definition in XML Publisher and associate it with Data Template.
Step 3: Define a Concurrent Program to generate the Data XML File.
The short name in the above concurrent program is the code what you give while defining data definition.
Then run the above created concurrent program and get the xml output.
Save the output.
Step 4: Define the RTF Template using the Generated Data XML.
Go to word document and design your rtf Template.
Step 5: Registering the Template with XML Publisher.
Step 6: Execute the concurrent program to generate the PDF report.
Deleting a Report Template From Back End
DECLARE
l_templateCode varchar2 (100) := ‘XX_AR_CUST_AUDIT_REPORT’; — Template Code
BEGIN
FOR r IN (SELECT t1.application_short_name template_app_name,
t1.data_source_code,
t1.application_short_name def_app_name
FROM xdo_templates_b t1
WHERE t1.template_code = l_templateCode)
LOOP
xdo_templates_pkg.delete_row (r.template_app_name, l_templateCode);
DELETE FROM xdo_lobs
WHERE lob_code = l_templateCode
AND application_short_name = r.template_app_name
AND lob_type IN (‘TEMPLATE_SOURCE’, ‘TEMPLATE’);
DELETE FROM xdo_config_values
WHERE application_short_name = r.template_app_name
AND template_code = l_templateCode
AND data_source_code = r.data_source_code
AND config_level = 50;
END LOOP;
END;
Caused by: java.io.UTFDataFormatException: Invalid UTF8 encoding in Oracle Reports
Usually we are facing the issue, while generating XML Tags, the issue is Caused by: java.io.UTFDataFormatException: Invalid UTF8 encoding. This is Caused because of the version of Oracle Reports or Language we need to set in NLS_LANG.
For a Time Being we can go for a below step, we need to change the XML Prolog Value in Oracle Reports Builder.
XML Prolog Value: xml version=”1.0″ encoding=”iso-8859-1″
This will help in the short term but in my experience it will come back and bite you. you might get lucky but remember what you have done if you hit another problem with your XML in the future.
You are now treating a single report – Oracle support can help you address all of them at once :).
Base Tables of BI Publisher Data Definitions and Templates in Oracle Apps R12
XDO_DS_DEFINITIONS_B
XDO_DS_DEFINITIONS_TL
XDO_TEMPLATES_B
XDO_TEMPLATES_TL
XDO_LOBS
XDO_CONFIG_KEYS
XDO_CONFIG_VALUES
XDO_CONFIG_PROPERTIES_B
XDO_CONFIG_PREPORTIES_TL
XDO_FONT_MAPPINGS
XDO_FONT_MAPPING_SETS_B
XDO_FINT_MAPPING_SETS_TL
How to generate XML report through PL/SQL Code
First Create procedure or package in Database.
See below sample code
Create or replace procedure xx_test_pro_rep(errfbuff out varchar2,retcode out varchar2)
IS
CURSOR data_cur
IS
SELECT empno, ename, job, hiredate, sal
FROM emp;
output_row data_cur%ROWTYPE;
BEGIN
DBMS_OUTPUT.put_line
(”);
fnd_file.put_line
(fnd_file.output,
”
);
DBMS_OUTPUT.put_line (”);
fnd_file.put_line (fnd_file.output, ”);
—
OPEN data_cur;
LOOP
—
FETCH data_cur
INTO output_row;
EXIT WHEN data_cur%NOTFOUND;
—
DBMS_OUTPUT.put_line (”);
fnd_file.put_line (fnd_file.output, ”);
—
DBMS_OUTPUT.put_line ( ”
|| DBMS_XMLGEN.CONVERT (output_row.empno)
|| ”
);
fnd_file.put_line (fnd_file.output,
”
|| DBMS_XMLGEN.CONVERT (output_row.empno)
|| ”
);
—
DBMS_OUTPUT.put_line ( ”
|| DBMS_XMLGEN.CONVERT (output_row.ename)
|| ”
);
fnd_file.put_line (fnd_file.output,
”
|| DBMS_XMLGEN.CONVERT (output_row.ename)
|| ”
);
—
DBMS_OUTPUT.put_line ( ”
|| DBMS_XMLGEN.CONVERT (output_row.job)
|| ”
);
fnd_file.put_line (fnd_file.output,
”
|| DBMS_XMLGEN.CONVERT (output_row.job)
|| ”
);
—
DBMS_OUTPUT.put_line ( ”
|| DBMS_XMLGEN.CONVERT (output_row.hiredate)
|| ”
);
fnd_file.put_line (fnd_file.output,
”
|| DBMS_XMLGEN.CONVERT (output_row.hiredate)
|| ”
);
—
DBMS_OUTPUT.put_line ( ”
|| DBMS_XMLGEN.CONVERT (output_row.sal)
|| ”
);
fnd_file.put_line (fnd_file.output,
”
|| DBMS_XMLGEN.CONVERT (output_row.sal)
|| ”
);
—
DBMS_OUTPUT.put_line (”);
fnd_file.put_line (fnd_file.output, ”);
—
END LOOP;
CLOSE data_cur;
—
DBMS_OUTPUT.put_line (”);
fnd_file.put_line (fnd_file.output, ”);
—
END xx_test_pro_rep;
After that create executable and Concurrent program.
In concurrent program output format is “XML”.
Add CP to responsibility and run program from SRS window.
See output file of that program.
Thanks
Sajal Agarwal
XML Publisher Report Tag
— Bold
bold
— colour
green
— Create sub template
… sub template design
— Choose
design layout 1
design layout 2
design layout 3
.
.
.
.
— find String Length
Ex- SAJALAGARWAL
— Sort Date
Ex-
Ex-
—- Handel Null value
Custom Message
Ex- No Data Found
— print Sysdate
— Print No Data Found
NO DATA FOUND
— Looping
No comments:
Post a Comment