How to Create a BI Publisher Report in Oracle EBS
how to create a bi publisher report in oracle ebs
Also searched as
- oracle ebs xml publisher report from scratch
- how to register bi publisher template in ebs
- create data definition xml publisher ebs
- ebs bi publisher rtf template upload
Short answer
An EBS BI Publisher report has three parts: a data model that produces XML data (usually a SQL-based data template), a Data Definition registered in the XML Publisher Administrator responsibility, and an RTF or Excel template built with the BI Publisher desktop tools and uploaded against that definition. A concurrent program then ties them together and runs the same way any standard EBS report does.
Applies to: EBS 12.1.3 and 12.2.x with XML Publisher / BI Publisher embedded
Building an EBS BI Publisher report
- 1Write the data model first: either a SQL-based data template (an XML file wrapping one or more <dataQuery> SQL statements and a <dataStructure> defining the output XML tags), or reuse the XML output of an existing concurrent program if you are only adding a new layout.
- 2Test the data template independently by running it through the XML Publisher Administrator responsibility's Data Definitions preview, or by running the underlying SQL directly, before building any layout against it.
- 3In the XML Publisher Administrator responsibility, go to Data Definitions > Create, give it a code and application, and upload the data template XML file.
- 4Design the layout in Microsoft Word using the BI Publisher Desktop add-in (or the newer web-based Template Builder for later releases): insert fields from the sample XML data, add tables, grouping, and conditional formatting as needed.
- 5Save the layout as RTF, then in the Data Definition's Templates region add a new template, select the RTF file, choose the output type (PDF, Excel, RTF), and set the language and territory.
- 6Register a Concurrent Program Executable if this is a brand-new report (execution method PL/SQL Stored Procedure or SQL*Plus for the data extraction, or Data Template execution method if you skip a separate executable), then register the Concurrent Program itself, pointing its Output Format to XML Publisher and linking the Data Definition under the program's Data Template field.
- 7Add the concurrent program to the appropriate Request Group so it appears under Submit Request for the intended responsibility, then run it end to end and confirm the output renders correctly.
Data template versus reusing an existing concurrent program
The fastest path for a brand-new report from a custom SQL query is a stand-alone data template: an XML file with dataQuery elements holding your SQL and a dataStructure section describing how to nest the output. This keeps the data extraction independent of any existing concurrent program logic, which is cleaner to maintain and test in isolation.
If you only need a different look for data an existing standard or custom report already produces in XML, skip writing a new data model entirely - register a new template against the existing Data Definition instead. This is the common case for creating a second layout (for example, a summary version alongside a detailed one) of a report that already runs correctly.
<dataTemplate name="XX_INVOICE_SUMMARY" defaultPackage="" version="1.0">
<parameters>
<parameter name="P_ORG_ID" dataType="number"/>
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[SELECT invoice_num, invoice_amount FROM ap_invoices_all WHERE org_id = :P_ORG_ID]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_INVOICE" source="Q1">
<element name="INVOICE_NUM" value="invoice_num"/>
<element name="INVOICE_AMOUNT" value="invoice_amount"/>
</group>
</dataStructure>
</dataTemplate>Wiring the concurrent program correctly
The step people most often get wrong is the link between the concurrent program and the Data Definition - Output Format must be set to XML Publisher on the program definition, and the Data Template field (on the Concurrent Program's Templates tab in newer releases, or via a profile option in older ones) must reference the exact Data Definition code, not just the report's display name.
If the program's own executable produces the XML directly (a PL/SQL procedure calling FND SRW or similar), you do not need a separate data template - only the Data Definition and template registration. If instead you built a stand-alone data template as its own object, the concurrent program's executable is typically the data template itself, invoked with execution method Data Template rather than a custom PL/SQL package.
Common template design pitfalls in Word
Grouping in the RTF template must exactly match the nested group structure declared in the data template's dataStructure - a mismatched or missing <?for-each?> tag around a repeating group is the single most common cause of a report that runs cleanly but returns a blank or truncated page. Preview against sample XML data pulled from an actual test run, not hand-typed sample data, to catch this before deployment.
Keep conditional formatting and calculations in the template itself minimal where possible - pushing derived values (totals, running balances, formatted dates) into the SQL data model instead of Word formulas makes the report far easier to debug when output looks wrong, since you can isolate whether the problem is in the data or the layout.
Common pitfalls
- !Registering the template under the wrong Data Definition code, so the layout never appears as an option when the program runs.
- !Leaving Output Format on the concurrent program set to Text instead of XML Publisher, producing raw XML in the output instead of the formatted document.
- !A dataStructure group name that does not match the RTF template's for-each tag exactly (case-sensitive), producing a blank report with no error.
- !Forgetting to add the new concurrent program to a Request Group, so users cannot find it under Submit Request despite it being fully registered.
- !Testing only with the developer's own responsibility, missing that end users under a different responsibility lack access to the underlying data due to MOAC or security profile differences.
How an ERP-grounded AI assistant handles this
For teams building a steady stream of custom EBS reports, ERPray grounded on the instance's own Data Definitions and concurrent program registrations can generate a first-draft data template SQL from a plain-language request, flag when a similar report already exists to extend instead of duplicate, and check that Output Format and Data Definition linkage are set correctly before the first test run wastes a cycle.
Frequently asked questions
Do I need BI Publisher Desktop installed to build the RTF template?
It is the standard way to insert data fields, tables, and grouping tags without hand-typing XML Publisher syntax into Word, and strongly recommended for anything beyond a trivial layout. Later EBS and Fusion Middleware combinations also support the web-based BI Publisher Template Builder for teams that cannot install desktop add-ins.
Can I preview the output before registering the concurrent program?
Yes - the Data Definition's Preview option in XML Publisher Administrator lets you run a template against sample or live XML data and view the rendered PDF or Excel output directly, without touching a concurrent program at all. Use this to catch layout issues early since it is much faster than a full Submit Request cycle.
Why does my report run but produce a completely blank PDF?
This is almost always a mismatch between the data structure's XML tag names or group nesting and what the RTF template expects, so the template finds no matching data to render. Open the actual XML output from the concurrent request's log and compare its tag names character for character against the template's field references.
Can one Data Definition have multiple templates?
Yes, and this is the standard way to offer multiple layouts (detail, summary, a specific language translation) for the same underlying data without duplicating the SQL. Register each as a separate template under the same Data Definition and let the user pick a layout when they submit the request, if the program is set up to prompt for it.
Related
How to Use Forms Personalization in Oracle EBS (No CUSTOM.pll Needed)
Forms Personalization lets you change field properties, set defaults, run built-ins and show messages on any Oracle Forms screen through Help > Diagnostics > Custom Code > Personalize, with no custom library or forms compile required. Rules are stored per form and responsibility in FND_FORM_CUSTOM_RULES and apply at runtime for every user who opens that form.
How-toHow to Register a Custom Concurrent Program in Oracle EBS
Registering a custom concurrent program in EBS takes three linked steps in the System Administrator responsibility: register the Executable pointing at your actual code object, register the Program that references that executable and defines its parameters, then add the program to a Request Group so it is visible under the target responsibility's Submit Request window.
AdvancedHow to Extend an OAF Page in Oracle EBS Without Touching Seeded Code
Use OAF Personalization, through the Personalize Self-Service Defn responsibility or the Personalize Page link, for layout, prompts and simple rules, and Controller Extension, a custom Java class that extends the seeded controller, for logic changes such as new validation or events. Both approaches survive patching because the seeded XML and Java classes are never edited directly.
How-toSQL to Find Responsibilities Assigned to a User in Oracle EBS
Join FND_USER to FND_USER_RESP_GROUPS on USER_ID, then to FND_RESPONSIBILITY_TL on RESPONSIBILITY_ID and APPLICATION_ID to get the responsibility name, filtering the END_DATE columns to show only currently active assignments. This is the standard query DBAs and support teams use instead of clicking through System Administrator > Security > User > Define for every user.
Error fixFix FRM-40735: WHEN-VALIDATE-RECORD Trigger Raised Unhandled Exception ORA-06508
FRM-40735 with ORA-06508 almost always means a PL/SQL package used by the form was recompiled while your session still held the old cached state, or the trigger's own exception handling does not trap a real data error. Exit the form completely and re-enter it; if the error repeats for all users, recompile invalid objects on the database and check for a bad custom trigger.
Error fixOracle EBS Concurrent Manager Will Not Start: How to Fix It
A concurrent manager that will not start in Oracle EBS is almost always one of three things: the node registered in FND_NODES no longer matches the actual hostname (common after cloning), stale OS processes are blocking a fresh start, or the database and listener the manager connects to are unreachable. Check the internal manager log, verify the node name, clear stale processes, and restart with adcmctl.sh.
AI for ERPAI for Oracle E-Business Suite, Without Leaving On-Prem
Add AI to Oracle E-Business Suite 12.2 without moving off-prem. Query concurrent programs, interface tables, and AP/PO data with a private LLM. See how.
AI for ERPOracle ERP AI Consulting: What a Partner Should Deliver
What to demand from an Oracle ERP AI consulting partner across EBS, JD Edwards, NetSuite, and Fusion Cloud: interface tables, APIs, and buyer questions.
Stuck on Oracle E-Business Suite (EBS)?
Talk to engineers who work inside Oracle E-Business Suite (EBS) every week, and who build private AI that answers these questions from your own ERP data.