Extend Oracle ERP to Preview Report Output and enable Email

Oracle ERP is the largest selling suite of applications across north America and it is well Integrated with other corporate applications. Often a times, there is a business requirement to Preview report outputs (in browser) and then email directly from Oracle to the end customers. In this Article, we will use RMA Label (developed using XML Publisher report) as an example to display embedded PDF in browser window on a button click in ERP. We will also, see that same label can be sent via email by adding another button.

This topic outlines the steps to extend Oracle ERP applications in OA framework to add Preview and Email capability for a return Label using Oracle provided standards, so you are still Oracle compliant and do not get impacted by any patches released by oracle. This topic can be used as a reference to implement other features within Oracle applications using OA framework as well.

Here are the high-level steps for implementation:

  1. Designing a Label in XML Publisher with client specific format
  2. Upload the XML publisher template in Oracle Repository and Register as a concurrent program
  3. Create new pages using OA framework and JDeveloper to trigger XML Publisher label for preview and email.
  4. Add buttons on existing Business function (such as RMA Label Screen) using Personalization to call newly Developed pages

Pre-requisites

I’m using the following for my setup:

  • Oracle EBS 12.2.8 release
  • Setup Oracle JDeveloper with OAF Extension (10.1.3.4) project and workspace
  • Putty and FileZilla FTP tool
  • Design Label using XML Publisher

I won’t be going through the setup of these pre-requisites – setting up JDeveloper and Designing Labels is reasonably straightforward. Please get in touch in case you need any help regarding Pre-requisites.

Designing Label and Register Concurrent Program

Labels are designed using XML Publisher report template and uploaded in XML Publisher Administrator repository as below:

  • Login to Oracle EBS and navigate to XML Publisher Administrator -> Home -> Templates. Create a new Template as below and upload the RTF template

  • Login to Application Developer and Create a new Concurrent Program as below.

Please note that concurrent Program Executable is “Java Concurrent Program”.

  • Login to System Administrator-> Responsibility-> Request to assign the concurrent program (created in previous step) to Application Developer Responsibility.

  • Test Concurrent Program by submitting manually and Review the output. Make sure, your output is displayed in PDF format as desired.

Develop Oracle Application Framework Page to Preview the PDF output embedded in screen.

  • Launch JDeveloper and create new page by going to OA Components-> Page

  • Give a name (something like ReturnLabelViewPG) and package name (xx.oracle.apps.csd.returns.webui) and click OK
  • Under the Page Layout Region, add a Single column Layout Region as below.

  • Extend this region to “/oracle/apps/xdo/oa/common/webui/DocumentViewerRn.MainRegion”
  • Right click “PageLayoutRN” and set a new Controller as below.
    • Package Name – xx.oracle.apps.csd.returns.webui
    • Class Name – ReturnLabelViewCO

  • Add a new Method “PrintReport” in your Controller as below. Pasting the code as well:

  • Update ProcessRequest Method in Controller as below to call “printReport” method coded in previous step.

  • Create a new Application Module by doing right click on project and click, new Application Module.
    • Package name – xx.oracle.apps.csd.returns.server
    • Name – ReturnLabelAM

  • One the Application Module is created, add a new method “submitReport” as below. I have pasted the contents as well. This method is called from Controller (previous step) to trigger the label report by passing orderID as input parameter.

  • Associate the Controller and Application Module to developed OAF page as below. When this page is rendered, it will use Controller and Application Module specified below for execution.

Develop Oracle Application Framework Page to Email the PDF output based on an Email Address captured in screen.

  • Launch JDeveloper and create new page by going to OA Components-> Page. Use the Application Module (ReturnLabelAM) created previously.
    • Package Name – xx.oracle.apps.csd.returns.webui
    • Class Name – ReturnLabelEmailCO
    • Page Name – ReturnLabelEmailPG

  • In Oracle Database, create a new Procedure to submit concurrent program (created in previous steps) with email address provided in delivery options. Here is the code:

  • Create a method in Application Module(as below) to call the PLSQL function created above and call this method from the EmailController file.

Next steps will be to deploy your locally developed Java components/ OAF page to Application server server.

  • Login to Unix box on the server using putty and go to $JAVA_TOP

  • Create new Directories (same as your package structure in jDeveloper)
    • mkdir -p xx/oracle/apps/csd/returns/server #directory to hold server components
    • mkdir -p xx/oracle/apps/csd/returns/webui #directory to hold webui components
    • chmod 777 xx/oracle/apps/csd/returns/server #set permission to read/write/exec
    • chmod 777 xx/oracle/apps/csd/returns/webui #set permission to read/write/exec
  • Login to FileZilla and move the files to locations above
  • Review the files as below for xx/oracle/apps/csd/returns/webui

  • Review the files as below for xx/oracle/apps/csd/returns/webui

  • Login to putty and go to $JAVA_TOP, run below command to generate customall.jar file. Take backup of existing customall.jar file present on this location
    • adgcnjar
    • it prompts for you to enter APPS (database user name) and password. Once entered, it will generate a customall.jar file on the server.
  • Import the OAF page into oracle Repository using below command from putty, while connecting to Middle tier

  • Bounce application services on middle tier of EBS, so Oracle can pick customall.jar file on run time execution.

Personalize Oracle Application Screen to add Preview and Email Button

  • Add a button to the screen in EBS where you want to see the preview output. For demonstration purposes, Depot Repair screen has been used. Use Personalization feature to add Preview button and set the properties as below. Note the Destination URL, which calls the custom page developed.

  • Add a button to the screen where you want to trigger the email. For demonstration purposes, Depot Repair screen has been used. Use Personalization feature to add Email button and set the properties as below. Note the Destination URL, which calls the custom page developed.

  • As you can see below, RMA Preview and RMA Email button are displayed now.

Test RMA Preview changes

  • Click RMA Preview button. Clicking this button calls, the XML publisher template and embeds the output in frame as below.

  • Click RMA Email button. Clicking this button calls, the XML publisher Concurrent Program (XX_DEPOT_RMA) and sends an email.

Conclusion:

In this Article, we learnt about extending Oracle Applications to add Preview and Email functionality using oracle standards. You can use the same approach and extend any Oracle provided screen to add more functionality. I hope you find this article useful for your business requirements.