Oracle XML/ BI Publisher – Convert Custom Reports to Oracle Business Intelligence Publisher

Convert Custom Reports to Oracle Business Intelligence Publisher – A very useful presentation by Mr. Mohan Dutt & Parth Agnihotri

1. Step 1: Convert the RDF report into XML format
   • Convert the RDF Report into XML format using rwconverter utility
   • This utility is available with Report 9i or later
   rwconverter batch=yes source=c:XX_TRX_PRINT.rdf dest=c:XX_TRX_PRINT.xml dtype=xmlfile overwrite=yes
2. Step 2: Create RTF Template
   • Option 1 – RDF Layout Migration
   • • Use RTFTemplateGenerator utility to generate layout template from RDF Layout
   • • java.exe oracle.apps.xdo.rdfparser.RTFTemplateGenerator XXXX_TRX_PRINT.xml
   • Option 2 – Create RTF file using Template Builder
3. Create RTF Template – Template Builder
   • New Cross Tab Wizard
   • • Multiple Levels
   • • Totals
   • Enhanced Chart Dialog
   • • Multiple Line Charts
   • • 3D Charts with Gradients
   • • Color Schemas
   • • Real Preview
   • New Table Wizard
   • Translation Support
   • • Generate XLIFF file
   • • Test Translations
   • Template Validation
4. Create RTF Template
   • Generate Sample XML Data
   • • Set Output Format to XML for current RDF Concurrent Program.
   • • Execute the concurrent program and save XML Data Output File
5. Create RTF Template
   • Insert XML field in the template – Insert Menu -> Field option
   • All the headers and summary level details can be inserted using this feature
6. Create RTF Template
   • Insert Table –
   • • Insert Menu -> Table / Form -> Select Wizard option.
   • • All the line level details can be inserted using this feature
7. Create RTF Template
   • Loop –
   • • <?for-each:G_LINE?> loop through all lines
   • Conditional Formatting
   • • display lines with LINE_TYPE=‘LINE’
   • • RDF Format Trigger logic can be implemented like this
   • Last Page Only Tag
   • • <?start@last-page:body?><?end body?>
   • • Anything after the last page only tag is displayed at the end of the document
8. Create RTF Template
   • Preview output
9. Step 3: Create Data Template
   • Use DataTemplateGenerator Utility to generate Data Template
   • java.exe oracle.apps.xdo.rdfparser.DataTemplateGenerator XX_TRX_PRINT.xml
   • DataTemplateGenerator creates:
   • • Data Template (XX_TRX_PRINT_template.xml)
   • • PLS files for Report PLSQL code:
   • • • PL/SQL package specification (XX_TRX_PRINTS.pls)
   • • • PL/SQL package body (XX_TRX_PRINTB.pls)
10. Create Data Template
    • Data Extraction Engine
    • • Fast, scalable extraction engine
    • • Integrated into concurrent manager
    • • Offers all that Oracle Reports offers plus 30-40% faster
    • • Multiple queries + joins
    • • Event triggers
    • • Flexfield support
    • • Data Structure
11. Create Data Template
    • Data Template consists of
    • • <parameters>section in which parameters are declared in child <parameter> elements;
    • • <dataQuery> section in which the SQL queries are defined in child <sqlStatement> elements;
    • • <dataTrigger> section in which PLSQL function are defined which are fired when the event occurs
    • • <dataStructure> section in which the output XML structure is defined
12. Step 4: Execute PLSQL code generated
    • Execute the .pls files in the database
    • Review the code before executing the .pls files
    • • One may need to change the code
    • • RDF Format triggers are not supported by the utility. Format trigger logic must be implemented separately.
13. Step 5: Register new Concurrent Program
    • Set the concurrent program output format to XML.
    • Attach executable XDODTEXE to the concurrent program
14. Step 6: Register Data Definition
15. Register Data Definition
    • Upload Data Template generated in Step 3 to Data Definition
16. Step 7: Register Layout Template
17. Step 8: Create Bursting Control File
    • This is required to e-mail files or delivering reports directly to the printer
    • Bursting Engine Features
    • • Burst individual documents from a batch
    • • Dynamic generation based on data
    • • Deliver in desired layout and output format
    • • Deliver down desired delivery channel
    • • Integrated into concurrent processing
    Batch Data Control File XML Publisher Templates XML EDI HTML PDF PDF
18. Create Bursting Control File
    • Accepts a data stream and splits it based on multiple criteria
    • Generates output based on a template
    • Delivers the individual documents through the delivery channel of choice (Email, Fax, Printer, File system, FTP)
19. Step 9: Upload Bursting Control File to Data Definition
20. Step 10: Program Call to Bursting Engine
    • Add code in After Report trigger to call Bursting Engine for delivery of reports
    • This submits ‘XML Publisher Report Bursting Program’ program which splits current program’s XML output, generates documents and delivers them to destination mentioned in Bursting Control File