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:
- Designing a Label in XML Publisher with client specific format
- Upload the XML publisher template in Oracle Repository and Register as a concurrent program
- Create new pages using OA framework and JDeveloper to trigger XML Publisher label for preview and email.
- 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:
public void printReport(OAPageContext pageContext, OAWebBean webBean , String orderId)
{
OAApplicationModule oaAM = pageContext.getApplicationModule(webBean);
pageContext.putParameter("p_DataSource",DocumentHelper.DATA_SOURCE_TYPE_BLOB);
pageContext.putParameter("p_DataSourceCode","XX_DEPOT_RMA");// Data Definition Short Name
pageContext.putParameter("p_DataSourceAppsShortName","XX");// Data Definition Registered Application Short Name
pageContext.putParameter("p_TemplateCode","XX_DEPOT_RMA");//XML Report Template Short Code
pageContext.putParameter("p_TemplateAppsShortName","XX");//XML Report Template Application Short Name
pageContext.putParameter("p_Locale","English:United States");//XML Report Template Language and Territory
pageContext.putParameter("p_OutputType","PDF");//Desired XML Report Output
pageContext.putParameter("p_XDORegionHeight","200%");//Desired XML output frame size
Serializable[] oaParams = {orderId}; //Parameter passed to the report
BlobDomain result = (BlobDomain)oaAM.invokeMethod("submitReport",oaParams);
pageContext.putSessionValueDirect("XML_DATA_BLOB", result);
}
- Update ProcessRequest Method in Controller as below to call “printReport” method coded in previous step.
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
String strOrderId = pageContext.getParameter("OrderId");//Get this value from calling Page
printReport(pageContext, webBean, strOrderId);
}
- 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.
public BlobDomain submitReport (String strOrderId)
{
BlobDomain blobDomain = new BlobDomain();
try
{
DataTemplate datatemplate = new DataTemplate(((OADBTransactionImpl)getOADBTransaction()).getAppsContext(), "XX", "XX_DEPORT_RMA");
Hashtable parameters = new Hashtable();
parameters.put("P_ORDER_ID",strOrderId);
datatemplate.setParameters(parameters);
datatemplate.setOutput(blobDomain.getBinaryOutputStream());
datatemplate.processData();
}
catch(SQLException e)
{
throw new OAException("SQL Error=" + e.getMessage(),OAException.ERROR);
}
catch (OAException e)
{
throw new OAException("XDOException" + e.getMessage(),OAException.ERROR);
}
catch(Exception e)
{
throw new OAException("Exception" + e.getMessage(),OAException.ERROR);
}
return blobDomain;
}
}
- 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 OR REPLACE PROCEDURE xx_submit_rma_email(
p_order_id IN VARCHAR2,
p_email IN VARCHAR2,
x_request_id OUT NUMBER)
AS
l_request_id NUMBER;
l_add_delivery BOOLEAN;
l_add_layout BOOLEAN;
BEGIN
fnd_global.apps_initialize (user_id=> fnd_global.user_id
,resp_id=>fnd_global.RESP_ID
,resp_appl_id=>fnd_global.RESP_APPL_ID);
l_add_layout:= fnd_request.add_layout (template_appl_name => 'XX',
template_code => 'XX_DEPOT_RMA',
template_language => 'en', --Use language from template definition
template_territory => 'US', --Use territory from template definition
output_format => 'PDF' --Use output format from template definition
);
l_add_delivery := fnd_request.add_delivery_option (TYPE => 'E',
p_argument1 => 'Return Material Authorization (RMA) for order#'||p_order_id, -- Email Subject
p_argument2 => 'admin@red-gate.com', -- From Address
p_argument3 => p_email, -- To Address
p_argument4 => null -- CC email address
);
l_request_id := fnd_request.submit_request ( application => 'XX'
, program => 'XX_DEPOT_RMA'—Concurrent Program short Name
, description => XX Depot Repair RMA Email'
, start_time => SYSDATE
, sub_request => FALSE
, argument1 => p_order_id
);
COMMIT;
IF l_request_id = 0 THEN
dbms_output.put_line('Request error during submission '|| fnd_message.get);
ELSE
dbms_output.put_line('Request is submitted successfully with request id ' || l_request_id);
END IF;
x_request_id:= l_request_id;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unexpected error has occurred during program submission ' || SQLERRM);
END xx_submit_rma_email;
/
- Create a method in Application Module(as below) to call the PLSQL function created above and call this method from the EmailController file.
public String emailLabel(String orderNumber, String xxemail)
{
OracleCallableStatement callableStatement = null;
String requestIdStr ="";
try
{
String callProc = " BEGIN xx_submit_rma_email "+
"(p_order_id => :1, p_email => :2, x_request_id => :3);"+
" END; ";
callableStatement = (OracleCallableStatement)getOADBTransaction().createCallableStatement(callProc,1);
callableStatement.setString(1, orderNumber);
callableStatement.setString(2, xxemail);
callableStatement.registerOutParameter(3,OracleTypes.NUMBER,255);
callableStatement.execute();
NUMBER requestID = callableStatement.getNUMBER(3);
requestIdStr = requestID.stringValue();
}
catch(Exception e) {
e.printStackTrace();
throw new OAException(e.toString(),OAException.ERROR);
}
finally {
try
{
callableStatement.close();
}
catch(Exception exception2)
{
throw OAException.wrapperException(exception2);
}
} return requestIdStr+"";
}
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
$JAVA_TOP/xx/oracle/apps/csd/returns/webui/ReturnLabelViewPG.xml -username apps -password <apps_password> -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= <host_name> )(PORT=<host_port>))(CONNECT_DATA=(SID=<host_SID>)))" -rootdir $JAVA_TOP $JAVA_TOP/xx/oracle/apps/csd/returns/webui/ReturnLabelEmailPG.xml -username apps -password <apps_password> -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= <host_name> )(PORT=<host_port>))(CONNECT_DATA=(SID=<host_SID>)))" -rootdir $JAVA_TOP
- 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.
The post Extend Oracle ERP to Preview Report Output and enable Email appeared first on Simple Talk.
from Simple Talk https://ift.tt/2YKhC7H
via
No comments:
Post a Comment