Edi Yanto (何 萬 新)

BI Publisher Report Using PL/SQL as Data Source

Posted on: September 19, 2014


Basically we can generate the data source from Oracle Report Builder or using XML Data Template, but in this time I show you another option (using PL/SQL) to generate data source when we just have a single group for our report generally.

Steps to create XML/BI Publisher report using PL/SQL package.

1. Write a PL/SQL package procedure to generate XML data based on your business requirement (simple sample package EY_GENERATE_SOURCE_PKG attached. Note: remove the extension .odt and extract the zip file)
–> Script to Generate XML Data

     — create a new context with the sql query
      l_ctx := DBMS_XMLGEN.newcontext (p_ref_cur);

      — add tag names for rows and row sets
      DBMS_XMLGEN.setrowsettag (l_ctx, p_row_tag);
      DBMS_XMLGEN.setrowtag (l_ctx, p_row_set_tag);

      — generate xml data
      x_xml_data := DBMS_XMLGEN.getxml (l_ctx);
      DBMS_XMLGEN.closecontext (l_ctx);

2. Register package procedure to Oracle Apps (Executable and Define Program) and add to Request Group

BIPPLSQL1

BIPPLSQL2

BIPPLSQL3

3. Run the report and save the output as EYARCMINV.xml
BIPPLSQL4

4. Create a RTF template using BI Publisher Desktop tool
BIPPLSQL5

5. Register this template to Oracle Apps (Data Definition and Data Template under XML Publisher Administrator responsibility)

BIPPLSQL6

BIPPLSQL7

6. Submit and View Output the request

–> BI Publisher layout attached on this request

BIPPLSQL8

–> Output type in PDF

BIPPLSQL9

 

10 Responses to "BI Publisher Report Using PL/SQL as Data Source"

I want to use Excel template instead of rtf template. How is it possible. Can you explain on this

Like

Hi Sanjana,

Yes, it’s possible to do it. you can use the syantax “XDO_?name?”, “XDO_GROUP_?nama?” and you can use the code is written to the XDO_METADATA sheet.
For more information you can try this link “http://docs.oracle.com/cd/E28280_01/bi.1111/e22254/create_excel_tmpl.htm#BIPRD3400”

Thanks,
Edi Yanto

Like

It was working for xml publisher reports with XDODTEXE as executable but i was trying to do from plsql stored procedure and it was not working with “XDO_?name?”
Let me know if you have worked from pl/sql stored procedure and keeping excel template with excel output

Thanks in advance

Like

Hi Sanjana,

It can work also with pl/sql stored procedure.. your excel template have XDO_METADATA sheet or not? I’ve try it and it work. My template for this blog attached, please click this link “https://ediyanto83.files.wordpress.com/2015/10/eyarcminv.xls”

Thanks,
Edi Yanto

Liked by 1 person

Thanks a lot it worked🙂

Like

You’re welcome..

Thanks,
Edi Yanto

Like

Hi Ed.. Could you create a master detail xml output sample for my case?

Like

Hi Chris,

Why u need the master detail case using this method? You also can do it by using Report Builder or XML Data Template, my buddy… haha..
Ok for your request, you can do it following this below scripts (based on your own case):
1. Create object TYPE for MASTER and DETAIL
–Detail
CREATE TYPE ap_inv_detail AS OBJECT (
line_number NUMBER,
quantity_invoiced NUMBER,
unit_price NUMBER,
amount NUMBER
);

CREATE TYPE ap_inv_details AS TABLE OF ap_inv_detail;

–Master
CREATE TYPE ap_inv_hdr AS OBJECT (
invoice_num VARCHAR2 (50),
inv_curr VARCHAR2 (3),
invoice_date DATE,
detail ap_inv_details
);

2. Script to generate XML Output
SELECT SYS_XMLGEN (ap_inv_hdr (aia.invoice_num,
aia.invoice_currency_code,
aia.invoice_date,
CAST (MULTISET (SELECT aila.line_number, aila.quantity_invoiced, aila.unit_price, aila.amount
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id) AS ap_inv_details)
),
xmlformat.createformat (‘MASTER’)
).getclobval () AS xml_query
FROM ap_invoices_all aia
WHERE aia.invoice_id = 61101;

3. Result
[?xml version=”1.0″?]
[MASTER]
[INVOICE_NUM]ERS-4419-36577[/INVOICE_NUM]
[INV_CURR]GBP[/INV_CURR]
[INVOICE_DATE]20-NOV-03[/INVOICE_DATE]
[DETAIL]
[AP_INV_DETAIL]
[LINE_NUMBER]1[/LINE_NUMBER]
[QUANTITY_INVOICED]35[/QUANTITY_INVOICED]
[UNIT_PRICE]790.6[/UNIT_PRICE]
[AMOUNT]27671[/AMOUNT]
[/AP_INV_DETAIL]
[AP_INV_DETAIL]
[LINE_NUMBER]2[/LINE_NUMBER]
[QUANTITY_INVOICED]15[/QUANTITY_INVOICED]
[UNIT_PRICE]12.83[/UNIT_PRICE]
[AMOUNT]192.45[/AMOUNT]
[/AP_INV_DETAIL]
[AP_INV_DETAIL]
[LINE_NUMBER]3[/LINE_NUMBER]
[AMOUNT]4842.43[/AMOUNT]
[/AP_INV_DETAIL]
[AP_INV_DETAIL]
[LINE_NUMBER]4[/LINE_NUMBER]
[AMOUNT]33.68[/AMOUNT]
[/AP_INV_DETAIL]
[/DETAIL]
[/MASTER]

Hopefully can help you…

Thanks,
Edi Yanto

Like

Hi edd..
Thanks for quick reply. It’s works like a charm!
But as you say, I prefer using report builder, cause bulk query like that doesn’t bring additional advantage.
I think with this PL/SQL method SQL query can be split through out cursor loop. or how is it possible?

Like

Hi Chris,

I think it’s more complicated if doing in PL/SQL, you maybe can do it with Oracle PL/SQL DOM (Document Object Model) API (DBMS_XMLDOM) for XMLType.
You’d basically be querying up the data and looping over result sets to create XML nodes, nodelists, elements, attributes and putting it all together yourself.

Thanks,
Edi Yanto

Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

September 2014
M T W T F S S
« May   Dec »
1234567
891011121314
15161718192021
22232425262728
2930  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: