BI Publisher Report in Oracle Applications

BI Publisher is an emerging reporting tool in Oracle Applications that is now being used extensively in place of Reports. BI Publisher provides for extremely configurable reports that can be published as documents RTF/PDF/Various other formats. This article is aimed at providing a simple coding example for a BI Publisher report using a data template. Following are the steps:

Assumption:
Example: Let us assume that we want to create a program that tracks changes to all supplier bank accounts. For this purpose assume that we have created a table xxxx_po_vendors_audit which captures changes to all bank accounts related to suppliers and has the following structure:
CREATE TABLE xxxx.xxxx_po_vendors_audit
(
vendor_id NUMBER,
old_bank_account_num VARCHAR2(30),
new_bank_account_num VARCHAR2(30),
vendor_name VARCHAR2(240),
creation_date DATE,
last_update_date DATE,
created_by NUMBER,
last_udated_by NUMBER,
last_update_login NUMBER,
last_update_user VARCHAR2(100),
vendor_site_code VARCHAR2(30)
);
/
CREATE SYNONYM xxxx_po_vendors_audit for xxxx.xxxx_po_vendors_audit;
/

This data is capture in a trigger defined on :
ap_bank_account_uses_all. The code for this would be:
CREATE OR REPLACE TRIGGER apps.po_vendors_nrc_xxxx AFTER INSERT OR UPDATE ON ap_bank_account_uses_all FOR EACH ROW
BEGIN
if(:new.external_bank_account_id <> :old.external_bank_account_id)THEN
IF(:NEW.VENDOR_SITE_id IS NOT NULL)THEN
insert into xxxx_po_vendors_audit
(
vendor_id,
old_bank_account_num,
new_bank_account_num,
vendor_name,
creation_date,
last_update_date,
created_by,
last_udated_by,
last_update_login,
last_update_user ,
VENDOR_SITE_CODE
)
select
:NEW.vendor_id,
aba_old.bank_account_num,
aba_new.bank_account_num,
pov.vendor_name,
sysdate,
:NEW.last_update_date,
:NEW.created_by,
:NEW.last_updated_by,
:NEW.last_update_login,
FU.USER_NAME ,
povs.vendor_site_code
from fnd_user fu,
AP_BANK_ACCOUNTS_ALL aba_new,
ap_bank_accounts_all aba_old ,
po_vendors pov ,
po_vendor_sites_all povs
where fu.user_id=:NEW.last_updated_by
AND aba_new.bank_account_id=:NEW.EXTERNAL_BANK_ACCOUNT_ID
AND aba_old.bank_account_id=:OLD.EXTERNAL_BANK_ACCOUNT_ID
AND pov.vendor_id = :NEW.VENDOR_ID
AND povs.vendor_id = :NEW.VENDOR_id
AND povs.vendor_site_id =:NEW.VENDOR_SITE_ID;
ELSE
insert into xxxx_po_vendors_audit
(
vendor_id,
old_bank_account_num,
new_bank_account_num,
vendor_name,
creation_date,
last_update_date,
created_by,
last_udated_by,
last_update_login,
last_update_user
)
select
:NEW.vendor_id,
aba_old.bank_account_num,
aba_new.bank_account_num,
pov.vendor_name,
sysdate,
:NEW.last_update_date,
:NEW.created_by,
:NEW.last_updated_by,
:NEW.last_update_login,
FU.USER_NAME
from fnd_user fu,
AP_BANK_ACCOUNTS_ALL aba_new,
ap_bank_accounts_all aba_old ,
po_vendors pov
where fu.user_id=:NEW.last_updated_by
AND aba_new.bank_account_id=:NEW.EXTERNAL_BANK_ACCOUNT_ID
AND aba_old.bank_account_id=:OLD.EXTERNAL_BANK_ACCOUNT_ID
AND pov.vendor_id = :NEW.VENDOR_ID;
END IF;
end if;
END;
/

Once this is done you would have data in the table xxxx_po_vendors_audit whenever you modify the supplier data. Based on this data we would want to create the xml publisher report:
1. Create a sample xml file:

2. Create an RTF template
Before creating an RTF template it is imperative that you have the XML Publisher word plugin. Install the Oracle XML Publisher Desktop available via patch 5887917. Open the Microsoft word. You should be able to see the following menus and toolbars.

Once you install the XML Publisher Desktop you can upload the sample xml data using the menus:

Once you upload data you can then go about creating your RTF template.

First prepare your basic design for the reports by putting the columns/data cells in place.

Then associate data with the placeholders.
You can associate placeholders to elements from the xml file by using form fields. To add form fields enable the forms toolbar:

Add a new form field and add a default text for internal documentation

Now it is time to associate xml elements with placeholders so that data renders effectively:
1. We need to ensure that for parent node(row from the db table) in the xml file the data is rendered.
2. The right data is placed in the right placeholder.

Click on the help text field:
statement would loop throught all elements of type BANKACCOUNTVOROW
would then associate the VENDORNAME column with the placeholder.

Repeat the same set of steps for all other placeholder columns.
The end product should look something like this:

Once your data template is ready you can use the menu option Preview-> pdf/rtf to view it in the appropriate format.

3. Create a data definition template
Once the rtf template is ready we would need to create a data definition template that would act as the source for creating the xml data file that would be in the input source for RTF template.
This file is again defined in an xml format.

4. Create a data definition file using XML Publisher Admin

5. Create a template file using XML Publisher Admin and link it to the data definition

6. Create a new concurrent program with the same short name as the data definition file.
Once you get this done you can run the concurrent program and specify the template to see the output:

Comments