Creating Report (with Excel output) using XML BI Publisher and RDF (Oracle Report Builder) in Oracle APPS r12

Purpose: The purpose of this post is to create a report in Oracle APPS using RDF (Oracle Report Builder) and XML BI Publisher. RDF will be used to generate XML dataset and BI Publisher for RTF template.

This case will help you to directly generate the report output in excel format from simple RDF report.

Steps:

·         First prepare the query to fetch the data or records as per the requirement. Here I will be creating a simple MRN Register (Receiving report).

Query:

      SELECT   PHA.SEGMENT1 ORDER_NUM,

               RSH.RECEIPT_NUM,

               PV.VENDOR_NAME,

               RSH.SHIPMENT_NUM INVOICE_NUM,

               TRUNC (RSH.SHIPPED_DATE) INVOICE_DATE,

               RSL.ITEM_ID,

                  MSI.SEGMENT1

               || '.'

               || MSI.SEGMENT2

               || '.'

               || MSI.SEGMENT3 ITEM_CODE,

               MSI.DESCRIPTION,

               RSH.CREATION_DATE RECEIPT_DATE,

               SUM (PLLA.QUANTITY) ORDER_QTY,

               SUM(RSL.QUANTITY_RECEIVED) RECEIPT_QTY,

               SUM(RSL.QUANTITY_RECEIVED * PLA.UNIT_PRICE) RECEIPT_AMT

          FROM PO_HEADERS_ALL PHA,

               RCV_SHIPMENT_HEADERS RSH,

               PO_VENDORS PV,

               RCV_SHIPMENT_LINES RSL,

               PO_LINES_ALL PLA,

               MTL_SYSTEM_ITEMS_B MSI,

               PO_LINE_LOCATIONS_ALL PLLA

         WHERE 1 = 1

           AND PHA.PO_HEADER_ID = RSL.PO_HEADER_ID

           AND PV.VENDOR_ID = RSH.VENDOR_ID

           AND RSL.PO_LINE_ID = PLA.PO_LINE_ID

           AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID

           AND MSI.INVENTORY_ITEM_ID = RSL.ITEM_ID

           AND RSH.SHIP_TO_ORG_ID = MSI.ORGANIZATION_ID

           AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID

           AND RSH.SHIP_TO_ORG_ID =

                                   NVL (:P_ORGANIZATION_ID, RSH.SHIP_TO_ORG_ID)

           AND PLA.ORG_ID = NVL (:P_ORG_ID, PLA.ORG_ID)

           AND RSH.VENDOR_ID = NVL (:P_VENDOR_ID, RSH.VENDOR_ID)

      GROUP BY PHA.SEGMENT1,

               MSI.SEGMENT1,

               MSI.SEGMENT2,

               MSI.SEGMENT3,

               MSI.DESCRIPTION,

               RSH.RECEIPT_NUM,

               RSH.CREATION_DATE,

               RSL.ITEM_ID,

               PV.VENDOR_NAME,

               RSH.SHIPMENT_NUM,

               TRUNC (RSH.SHIPPED_DATE)

      ORDER BY PHA.SEGMENT1, RSH.RECEIPT_NUM;

·         Now open the Oracle Report Builder.

·         Select “Build a new report manually” and then click “Ok” button.

·         Select “SQL” icon from the toolbar and then click on the empty space to open a window.

·         Paste the query in the new window and click on Ok button.

·         Save the file as .rdf extension.

·         Click on File -> Generate to File -> XML.

·         Give the output file a name XXTest.xml.

·         Enter the value for the parameters.

·         Go and check the xml file has been generated at the specified path

·         Move the RDF file to server using FTP (SSH, Winscp, Toad)

·         Next step is to register the report in the Oracle application

·         Navigate to Application Developer -> Concurrent -> Executable

·         Fields to keep in mind:

o   Application: Application top at which the RDF file has been moved on server

o   Executable method: Oracle Reports

o   Execution File Name: XXFIL_MRN_REGISTER_P (Name of the RDF file)

·         Navigate to Application Developer -> Concurrent -> Program

·         Enter the details as per below mentioned image

·         Fields to keep in mind

o   Executable Name: Name of the executable file created above

o   Options Format: XML

·         Click on “Parameters” button to define parameters.

·         Save the record.

·         Create the rtf template (How to create the rtf template? Click here) and load the xml file and preview the output.

·         Register the rtf template in the application (How to register rtf template? Click here).

·         Run the program and view the output.