EBS Bursting: Filter on XML Elements using XPATH in Bursting Control File
Just a quick post to give an example of a bursting control file that has multiple emails, with a filter based on XML Element in the data to select which email to send.Oracle EBusiness Suite XML / BI Publisher Bursting Control File Example - Multiple Email Filter
Here it is:<?xml version="1.0" encoding="UTF-8"?> <xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi"> <xapi:globalData location="stream"/> <xapi:request select="/ARXSGPO_CPG/LIST_G_SETUP/G_SETUP/LIST_G_STATEMENT/G_STATEMENT"> <xapi:delivery> <xapi:email server="${XXX_SMTP}" port="25" from="${XXX_SEND_FROM}" reply-to ="${XXX_REPLY_TO}"> <xapi:message id="email1" to="${XXX_CUST_EMAIL}" cc="${XXX_ARCHIVE_EMAIL}" attachment="true" content-type="html/text" subject="Statement from ${ORG_NAME} - ${STATEMENT_DATE}">Hello, Please find attached the Statement for period to ${STATEMENT_DATE}. ${ORG_NAME} Internal Ref: Customer Email </xapi:message> </xapi:email> <xapi:email server="${XXX_SMTP}" port="25" from="${XXX_SEND_FROM}" reply-to ="${XXX_REPLY_TO}"> <xapi:message id="email2" to="${XXX_AGENT_EMAIL}" cc="${XXX_ARCHIVE_EMAIL}" attachment="true" content-type="html/text" subject="Statement from ${ORG_NAME} - ${STATEMENT_DATE}">Hello, Please find attached the Statement for period to ${STATEMENT_DATE}. Regards, ${ORG_NAME} Internal Ref: Agent Email </xapi:message> </xapi:email> </xapi:delivery> <xapi:document key="${CUSTOMER_ID}_1" output="${XXX_SHORTNAME}_Statement_${STATEMENT_DATE}" output-type="pdf" delivery="email1"> <xapi:template type="rtf" location="xdo://AR.XXX_STATEMENT_PRINT.en.00/?getSource=true" filter="/ARXSGPO_CPG/LIST_G_SETUP/G_SETUP/LIST_G_STATEMENT/G_STATEMENT[XXX_CUST_MODE='Email']"/> </xapi:document> <xapi:document key="${CUSTOMER_ID}_2" output="${XXX_SHORTNAME}_Statement_${STATEMENT_DATE}_Agent" output-type="pdf" delivery="email2"> <xapi:template type="rtf" location="xdo://AR.XXX_STATEMENT_PRINT.en.00/?getSource=true" filter="/ARXSGPO_CPG/LIST_G_SETUP/G_SETUP/LIST_G_STATEMENT/G_STATEMENT[XXX_AGENT_MODE='Email']"/> </xapi:document> </xapi:request> </xapi:requestset>=======================================
Environment Variables from database table - Oracle E-Business Suite
Are you running Oracle E-Business Suite (EBS) / Applications and want to get an operating system level environment variable value from a database table, for example for use in PL/SQL? Or perhaps to default a concurrent program parameter? Didn't think environment variables were stored in the database?Try out out this query that shows you $FND_TOP:
select value from fnd_env_context where variable_name = 'FND_TOP' and concurrent_process_id = ( select max(concurrent_process_id) from fnd_env_context ); VALUE -------------------------------------------------------------------------------- /d01/oracle/VIS/apps/apps_st/appl/fnd/12.0.0Or did you want to find out the Product "TOP" directories e.g the full directory path values from fnd_appl_tops under APPL_TOP?
col variable_name format a15 col value format a64 select variable_name, value from fnd_env_context where variable_name like '%\_TOP' escape '\' and concurrent_process_id = ( select max(concurrent_process_id) from fnd_env_context ) order by 1; VARIABLE_NAME VALUE --------------- ---------------------------------------------------------------- AD_TOP /d01/oracle/VIS/apps/apps_st/appl/ad/12.0.0 AF_JRE_TOP /d01/oracle/VIS/apps/tech_st/10.1.3/appsutil/jdk/jre AHL_TOP /d01/oracle/VIS/apps/apps_st/appl/ahl/12.0.0 AK_TOP /d01/oracle/VIS/apps/apps_st/appl/ak/12.0.0 ALR_TOP /d01/oracle/VIS/apps/apps_st/appl/alr/12.0.0 AME_TOP /d01/oracle/VIS/apps/apps_st/appl/ame/12.0.0 AMS_TOP /d01/oracle/VIS/apps/apps_st/appl/ams/12.0.0 AMV_TOP /d01/oracle/VIS/apps/apps_st/appl/amv/12.0.0 AMW_TOP /d01/oracle/VIS/apps/apps_st/appl/amw/12.0.0 APPL_TOP /d01/oracle/VIS/apps/apps_st/appl AP_TOP /d01/oracle/VIS/apps/apps_st/appl/ap/12.0.0 AR_TOP /d01/oracle/VIS/apps/apps_st/appl/ar/12.0.0 ...Or perhaps the full directory path to $APPLTMP?
select value from fnd_env_context where variable_name = 'APPLTMP' and concurrent_process_id = ( select max(concurrent_process_id) from fnd_env_context ); VALUE -------------------------------------------------------------------------------- /d01/oracle/VIS/inst/apps/VIS_demo/appltmp==================================
SQL: Override Address for Workflow Notification Mailer in the Oracle E-Business Suite
In the Oracle E-Business Suite I often need to develop code that sends emails. Easy enough to jump in and code it using a variety of options like workflow, utl_smtp etc. but what sort of things do we need to consider here?- Don't hardcode Outbound SMTP Server details in your code
- Source the SMTP Server details from a single location
- For test/development environments we don't want to send out email to real email addresses, so use an email address for test purposes and source it from a single location
- If you select an email address from a record in the database, then put in an IF or CASE or DECODE statement to check if we should be using a test address and use that instead of the real email address!
That way emails won't go to recipients from Test / Development environments where the Override / Test Email Address is set.
Workflow Notification Mailer Outbound SMTP Server
We can get the Outbound SMTP Server Name using the following SQL:select fscpv.parameter_value smtp_server_name from fnd_svc_comp_params_tl fscpt , fnd_svc_comp_param_vals fscpv , fnd_svc_components fsc where fscpt.parameter_id = fscpv.parameter_id and fscpv.component_id = fsc.component_id and fscpt.display_name = 'Outbound Server Name' and fsc.component_name = 'Workflow Notification Mailer';
Workflow Notification Mailer Override Address or Test Address
And the Override Address (or Test Address in older terminology) from the following SQL:select fscpv.parameter_value test_address from fnd_svc_comp_params_tl fscpt , fnd_svc_comp_param_vals fscpv , fnd_svc_components fsc where fscpt.parameter_id = fscpv.parameter_id and fscpv.component_id = fsc.component_id and fscpt.display_name = 'Test Address' and fsc.component_name = 'Workflow Notification Mailer';And then we can use them consistently... perhaps following coding practises and wrapping the above in a PL/SQL function would be good!
Note that the above SQL assumes you are using the default seeded Workflow Notification Mailer queue and haven't set up your own. If you have then replace the name Workflow Notification Mailer in the SQL with the name you used.
===================================
Hi,
ReplyDeleteCan some body please let me know how to over come this issue.
I need to authenticate mail server, which I heard it from our DBA saying in needs to be embed in XML Bursting control file. How is that possible?
Rgds, Adnan