RTF to Excel Sheet (Separate) O/P

Different Sheet Xls O/P


Form Bi Publisher Report RTF Template if you need to generate output in different excel sheets need to perform the exercise as below.


1. Ensure the grouping of block items.

Different Grouped Data Blocks

2. After Each Grouped Data Block click 'CTRL+ENTER' and place the block you need to be on next sheet.

3.To name the sheets - use the XML tag before the data block:

The output will be generated as below in different sheets.

Happy Learning !!!

Consult the OPP service log for details.

For XML reports with layout or Cross tab type template errors will encounter with Complete/warning with Error Msg Consult the OPP service log for details.

Need to Check log file for error:

The path is

/u01/DZWD(service SID)/inst/apps/DZWD_jvezdap1/logs/appl/conc/log/filename.txt

To find the file Path  use the query

SELECT 

fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name 

FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp 

WHERE fcpp.processor_id = fcp.concurrent_process_id

 AND fcpp.action_type = 6

 AND fcpp.concurrent_request_id = 20918150(&concurrent program request_id)

;

Mostly the error will come when the template code given differs.
Note:Concurrent program short name and data definition short name should be the same and template code can differ from concurrent program short name.

Conditional Selection

1.Displaying set if records:
e.g:10 records per page
Declare Global Variable called ‘no_of_Count_per_page’ -- In this case I have fixed 10 lines per page.
xsl:variable name="no_of_lines_per_page" select="number(10)"
2.Calling a inner group:
?for-each:$inner_group?
Can specify any if loop before calling the inner group
--if:position()>=$first_rec and position()<$first_rec+$no_of_Count_per_page?> --
3.Can call another template using xsl:call-template

With Xml DD

Xml Data Definitions can be used instead of RDF's

Inside the xml tag u need ot specify the parameters,groups and elements used.amd can uplaod in XML format in Data Definition.

Each set is sql statement are grouped and called.

Using Choose

.In regular XSL programming, if a condition is met in the choose command then further XSL code is executed.

-Used for multiple conditions.

Use the following syntax for these elements:

expression?>

CREATING XML REPORT USING PROCEDURES

we are not using RDF file to generate XML file. We can use the Procedure for this Purpose.

1. First Declare the utlfile.
2. Next give the path to save the .xml file name. This can be stored in the selected directories only. This can be found out using the query

SELECT *

  FROM all_directories

Procedure:

CODING

CREATE OR REPLACE PROCEDURE xmlproc (errbuf VARCHAR2, retcode NUMBER)

IS

   myutlfile   UTL_FILE.file_type;

BEGIN

   myutlfile := UTL_FILE.fopen ('c:\temp', 'xinv.xml', 'W');

   UTL_FILE.put_line (myutlfile, '');

   UTL_FILE.put_line (myutlfile, '');

CREATE OR REPLACE PROCEDURE xmloutproc (errbuf VARCHAR2, retcode NUMBER)

IS

BEGIN

   fnd_file.put_line (fnd_file.output, '');

   fnd_file.put_line (fnd_file.output, '');

   FOR i IN (SELECT inventory_item_id, segment1, description, creation_date

               FROM mtl_system_items_b

              WHERE creation_date > TO_DATE ('01-JAN-2008')

                AND organization_id = 204)

   LOOP

      fnd_file.put_line (fnd_file.output, '');

      fnd_file.put_line (fnd_file.output,

                            ''

                         || i.inventory_item_id

                         || ''

                        );

      fnd_file.put_line (fnd_file.output,

                         '' || i.segment1 || ''

                        );

      fnd_file.put_line (fnd_file.output,

                         '' || i.description || ''

                        );

      fnd_file.put_line (fnd_file.output,

                            ''

                         || i.creation_date

                         || ''

                        );

      fnd_file.put_line (fnd_file.output, '');

   END LOOP;

   fnd_file.put_line (fnd_file.output, '');

END;

Compile the Procedure

BEGIN

   xmloutproc ('a', 1);

END;

Now create a executable ans program as the same u do for all other reports.

Conditional Formating

XML Publisher supports the usage of simple "if" statements, as well as more complex "choose" expressions.

                       Insert the following syntax to designate the beginning of the conditional area.

condition?>

                       Insert the following syntax at the end of the conditional area: .

Use the following syntax to construct an if-then-else statement in your RTF template:

element_condition then result1 else result2 end if?>

Defining Groups n Page breaks

that for each occurrence of an element, you want the included fields displayed

XML group element tag name?>

Insert the following tag after the final element:

Page Breaks

                       To create a page break after the occurrence of a specific element

                       To insert a page break between each occurrence of a group, insert the "split-by-page-break" form field within the group immediately before the tag that closes the group

XML Publisher

Oracle XML Publisher is a template-based publishing solution delivered with the Oracle E- Business Suite. XML Publisher is a tool which enables the users to produce


Flexible

High Quality

Template-Based Reports

The main feature of XML Publisher is that it separates the Data Source from the presentation/Layout

Steps To Create:
 
1. CREATING AN ORDINARY XML REPORT
Create a Report using a Query in .rdf format.


STEP1: Open the Report Builder and Select the option to build the report manually.

The Query used is to bring the items created after 01-Jan-2008



SELECT inventory_item_id, segment1, description, creation_date

FROM mtl_system_items_b

WHERE creation_date > TO_DATE ('01-JAN-2008') AND organization_id = 204



Connect as apps/apps@prod

Create the Layout. [Actually, there is no need to create layout for XML report. Here, we have created it for demo purpose.]

The Output will be like this.

Save this Report in the Inventory Top.

oracle\prodappl\inv\11.5.0\reports\US\xinv.RDF

STEP2: Generate XML File

Save it.

This is the XML coding for the RDF File.

STEP 3: CREATE XML TEMPLATE


Now, we have to create a template for Output. For this open Microsoft word.



If XML was installed in Your system, You can see this

Now Load the XML data

Select the XML saved Data

Create the Template.

Here, we are inserting all those fields. So, Select All Fields.

You will get the layout as Follows

This is the Template, we have created. Save this as rtf file [Rich Text Format].

STEP4: REGISTER THE REPORT IN APPS




Open the Application.

Goto System Administrator Responsibility. Create the Executable

Save it. Create the Concurrent Program

Save it.
Add it to request group and submit the request
Out put wil be in XML form that it is not that we need.

STEP5 : ATTACHING THE TEMPLATE


Now GO to XML Publisher Administrator responsibility

Creating the Data Denition

When you create the data definition, you register the source of the data that will be merged with your template layout to create your published report.



We have to create the Data Definition Now.

By Default, it will be in the query mode. So Click CREATE DDF

One Important thing here is, Give the Code name same as the Conc Prgm Short Name in caps. Else it will not give the result.

If you are using the Oracle Applications concurrent manager to generate your report, the data definition Code must match the concurrent program short name of the report program (for example, RAXCUS for the Customer Listing Summary). This enables the concurrent manager to locate the templates available for the report concurrent program when you submit the request.

After the data definition is created, all the fields are updateable except Application and Code





Creating the Template

When you create a template, you assign it a data definition and upload your template layout files. Assigning the data definition makes the template available to the corresponding data source at runtime.



Now Attach the Template,

After the template definition is created, the following fields are not updateable: Application, Code, and Type




Now, you Run the Report in the Inventory Responsibility

If You see the output, it will come in the Format we have give. Here we have given as PDF