XDO Tables - XML Publisher report queries

XDO_DS_DEFINITIONS_B
XDO_DS_DEFINITIONS_TL

XDO_TEMPLATES_B
XDO_TEMPLATES_TL
XDO_LOBS


XDO_FONT_MAPPINGS
XDO_FONT_MAPPING_SETS_B
XDO_FONT_MAPPING_SETS_TL

select * from XDO_DS_DEFINITIONS_B where data_source_code = 'Data source short code'
select * from XDO_DS_DEFINITIONS_TL where rownum < 3

select * from XDO_TEMPLATES_B where TEMPLATE_CODE = 'Template Short code'
select * from XDO_TEMPLATES_TL where TEMPLATE_CODE = 'Template Short code'
select * from XDO_LOBS where lob_code = 'Template Short code'

TABLE NAME	DESCRIPTION
XDO_CONFIG_PROPERTIES_B	Stores the XML Publisher Administration configuration properties that are accessible from the OA Framework interface.
XDO_CONFIG_PROPERTIES_TL	Translation table for XDO_CONFIG_PROPERTIES_B.
XDO_CONFIG_VALUES	Stores the values assigned to the property in Administration Configuration Data
XDO_CURRENCY_FORMATS	Stores the format masks for various currencies. A collection of these formats forms a currency format set.
XDO_CURRENCY_FORMAT_SETS_B	Stores the Currency Format Sets
XDO_CURRENCY_FORMAT_SETS_TL	Stores the Currency Format Sets
XDO_DS_DEFINITIONS_B	Stores data source definition represented by XML Schema Definition (XSD). Each data source has one or more elements, and these information are stored in XDO_DS_ELEMENTS_B
XDO_DS_DEFINITIONS_TL	Translation table for XDO_DS_DEFINITIONS_B
XDO_FONT_MAPPINGS	Stores the mappings from a base font to a target Truetype or Type 1 font. A collection of these mappings forms a font mapping set
XDO_FONT_MAPPING_SETS_B	Stores the header information for a font mapping set, which is a collection of font mappings
XDO_FONT_MAPPING_SETS_TL	Translation table for XDO_FONT_MAPPING_SETS_B
XDO_LOBS	Stores Template(RTF File), XML File, XML Schema File, locale(langauge and territory) sensitive binary and text files. It is mainly used for storing language layout templates.
XDO_TEMPLATES_B	Stores template information. Each template has a corresponding data source definition stored in the XDO_DS_DEFINITIONS_B. Each translation of a certain template, not each template, has a corresponding physical template file. The physical template file information are stored in the XDO_TEMPLATE_FILES.
XDO_TEMPLATES_TL	Translatable table for XDO_TEMPLATES_B
XDO_TEMPLATE_FIELDS	Stores information of the fields of template file. Each field belongs to one of physical template files
XDO_TRANS_UNITS	Stores the header information regarding each segment of translatable text in layout templates
XDO_TRANS_UNIT_PROPS	Stores any untranslatable values embedded within a segment of text. These values will be merged back into the text translations
XDO_TRANS_UNIT_VALUES	Stores any untranslatable values embedded within a segment of text. These values will be merged back into the text translations

Click here to know basic of How to develop XML report

XML Publisher Reports oracle Apps

Overview

XML Publisher, which is also called Oracle Business Intelligence Publisher (BI Publisher), is a template-based reporting tool that leverages standard technologies for data extraction and display. Business users can build the layout and rules for the report themselves by using common desktop tools. Development engineers can concentrate on extracting data in the most efficient manner. Deployment time is faster. Also, business users with minimal training can design templates and reports based on the data sources provided by engineers, thus greatly reducing the cost of ownership.

XML Publisher separates a reports data, layout and translation components into three manageable pieces at design time; at runtime all the three pieces are brought back together by XML Publisher to generate the final formatted, translated outputs like PDF, HTML, XLS and RTF. In future, if any there is any change in layout we just need to add/modify the Layout file.

Data Logic: Data extracted from database and converted into an XML string.

Layout: The layout templates to be used for the final output are stored and managed in the Template Manager.

Translation: The translation handler will manage the translation that is required at runtime

In brief the steps are as follows:-
a.    Create a procedure and register it as Concurrent Program so that we write   

       XML  tags  into output file.
b.    Build a Data Definition & XML Template using XML Publisher.
c.    Create a relation between XML Template & Concurrent Program and run the concurrent

       program

Requirements for XML Data Object Reports

1. Oracle XML Publisher
2. Template Builder

When you download the XML Publisher Desktop edition you get a Zip file containing setup for XML Publisher Desktop Install Shield, this installs some components into Microsoft Word. After installing, the Word Add-Ins is attached to the menu bar for the word document. This menu lets you attach an XML data source document, add the XML data to your template, set preferences and preview the output.

So let us take a look at how we can create xml report from xml data template.

The XML data template is an XML document that consists of four basic sections:

1. Parameters
2. Triggers
3. Data Query
4. Data Structure

This structure is shown in the following graphic: 

If you see the above XML Data Template, we have specified the parameters that are being used and also the SQL Query that will be fetching the data. The SQL Query is using the parameters that are being defined under the Parameters Section.  I have also included a jpeg image of the XML File Definition. Save the same as “.xml” file in your local system. After saving the file, you should be able to open it in the browser. Double click the xml file and the file should open.

Now, navigate to System Administrator > Concurrent > Program > Define.

Create a new Concurrent Program and give the details. The Executable should always be XDODTEXE and output format should XML. After creation of concurrent program assign the program to the request group. 

Now, Go to XML Publisher Administrator responsibility and create a new Data Definition. Give the following for the fields:

Code name = concurrent program short name

Application – Provisioning (or your custom application)

Start Date – Automatically Populated

Press “Add File” next to Data Template. Browse and upload the XML Data Template that we have created here. 

Go to Data Templates tab and create a new Data Template. Give the following details:

Code name = concurrent program short name

Select the Data Definition that we had created prior to this.

Select Language as US English and upload the RTF File that we have created (Template_file.rtf)

Now, all set to get your report output. Go to responsibility where you have assign the program and run the report by providing required parameter.

Hope this helps. Let me know your thoughts and feel free to ask any questions that you might have. Also, check out the next blog which has Triggers being called from the XML Template file.