BI Publisher Report in Oracle Applications
0digg
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: ![]() 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. ![]() ![]() First prepare your basic design for the reports by putting the columns/data cells in place. ![]() 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. ![]() 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. 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. ![]() ![]() ![]() ![]() 5. Create a template file using XML Publisher Admin and link it to the data definition ![]() Once you get this done you can run the concurrent program and specify the template to see the output: |
Technical > BI Publisher >