In Depth Apps - Oracle eBusiness Suite

Tuesday, September 21, 2010

Enhancement Requests and Bugs for Oracle eBusiness Suite

From time to time I create enhancement requests or bugs, commonly on Oracle eBusiness Suite. Additionally I come across enhancement requests that I think deserve more visibility. This page is dedicated to that cause. If you have an Oracle Enhancement Request (ER) or Bug you would like to raise the visibility on, please feel free to comment or contact me.
If you like the looks of one of the Enhancement Requests noted below, log a Service Request (SR) on My Oracle Support (MOS) and ask for your MOS Customer record to be attached to the ER / Bug.

Created	Logged By	Bug / ER	Status	Product	Description
2015/02/22	See ER	9130428	Open	AR	Need a Standard Way to Export the Aging 7 Buckets By Account Report Output to Excel
2013/10/07	See ER	17069048	Open	BIP	Enable use of recursive with clause
2013/06/05	See ER	12872320	Open	BIP	Request to add autofilter functionality to native Excel template (binary)
2012/01/22	Gareth	13616572	Open	BIP	BI Publisher RTF unable to show a fixed number of lines for a word wrapped cell
2011/05/13	See ER	12423249	Open	BIP	Adding support of SMTP username/password to "XAPI:EMAIL" bursting for E-Business Suite (EBS) 12.1.X
2010/11/09	See ER	8208646	Open	AP/IBY	R12 Unable to send Separate Remittance Advice as email attachment in PDF
2010/08/11	Gareth	10019593	Rejected	AP/IBY	ARXRWMAI - In Receipts Summary form, cannot query by payment server id - PSON (Payment Server Order Number)
2010/07/14	See ER	9908713	Open	BIP	Need the DeliveryManager API to have a trackback variable for bounced emails
2010/07/18	See ER	9918364	Open	BIP	Need email body to be set to UTF-8 charset when using East Asian language emails
2010/07/08	Gareth	9891120	Open	eAM	Need hook to extend EAM Maintenance Work Order Detail Report
2010/06/19	Gareth	9834226	Open	AP	Need to embed email images Payables/Payments "Send Separate Remittance Advices"
2009/01/07	See ER	7694052	Open	AP	Does the Supplier Open Interface API Support Updates to Supplier Information?
2005/03/25	See ER	4262148	Open	AP	Need Public API to manage custom AP Invoice Holds

PS. If you would like to monitor Enhancement Requests, and be notified by Oracle of status updates, create a Favorite in My Oracle Support and ensure Hot Topics (Favorites) emails are on as follows.

Create a Favorite:

Login to My Oracle Support
Search on the ER / Bug number in the Search box e.g. 4262148
Click on the orange star next to the Title in the list view, or click on the article and click on the orange star. You should see "Favorite added" status message.

Switch on Hot Topics Emails:

Go to the Settings tab
Click on Hot-Topics Email under Personal on the left side menu list.
Switch On the Hot Topics email, every 1 days (or suitable), and ensure you have checked "Product Bugs Marked as Favorites"
Save

To see your Favorites, click on the Knowledge tab, then click Favorite Articles in the Recent Activity tab.

Table of Enhancement Request Statuses

Status Code	Definition
11	Code/Hardware Bug (Response/Resolution)
12	To External (User Group) Review
15	Enhancement Req. Internal(Oracle) Review
19	Approved for User Group Voting
20	To Requestor, Need More Info
22	Approved for Future Release
23	Scheduled for Future Release
50	ER Waiting for Base ER Fix
82	QA/PM to Eval ER, Chg Delivered Internal
97	Closed, Enhancement Rejected
98	Closed, Enhancement Implemented

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

Revisited: Changing the default layout format between RTF/PDF/Excel for BI / XML Publisher 11i Concurrent Requests: Patches 5612820, 7627832 here now!

Thursday, August 12, 2010

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>

Catch ya!
Gareth

This is a post from Gareth's blog at http://garethroberts.blogspot.com

References

Oracle Forums Thread

Tuesday, April 06, 2010

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.0

Or 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

NB: These queries assume your concurrent managers are running!

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

References

Oracle Forums Thread

Update - Added example output and $APPLTMP output.

Monday, March 22, 2010

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!

So what we should do is pickup the settings from the Workflow Notification Mailer service component for the Outbound SMTP Server and Override Address and use them appropriately.

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.

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

Clearing Email Notifications and manually setting the Workflow/Java Notification Mailer Override/Test Address via SQL for the Oracle eBusiness Suite

Thursday, March 04, 2010

NZOUG Conference 2010 Agenda online now!

I'm pleased to advise that the agenda is now online for the NZOUG 2010 Conference in Rotorua, March 15th / 16th.

The lineup of speakers is excellent, including a stack of Oracle Ace Directors:

Tim Hall
Chris Muir
Daniel Morgan
Franciso Munoz Alvarez
Robert Freeman

Only a week or so left so register for the conference now, and meet the Aces!

Find out more at the NZOUG Conference 2010 web page .

Disclaimer: I'm on the NZOUG Committee
Note: The speaker lineup/abstract list are subject to change.

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

References

NZOUG

In Depth Apps - Oracle eBusiness Suite - Gareth Roberts

Tuesday, September 21, 2010

Enhancement Requests and Bugs for Oracle eBusiness Suite

Create a Favorite:

Switch on Hot Topics Emails:

Table of Enhancement Request Statuses

Related Posts

Thursday, August 12, 2010

EBS Bursting: Filter on XML Elements using XPATH in Bursting Control File

Oracle EBusiness Suite XML / BI Publisher Bursting Control File Example - Multiple Email Filter

References

Related Posts

Tuesday, April 06, 2010

Environment Variables from database table - Oracle E-Business Suite

References

Monday, March 22, 2010

SQL: Override Address for Workflow Notification Mailer in the Oracle E-Business Suite

Workflow Notification Mailer Outbound SMTP Server

Workflow Notification Mailer Override Address or Test Address

Related Posts

Thursday, March 04, 2010

NZOUG Conference 2010 Agenda online now!

References

Recent Comments

Labels

Blog Archive

About Me

Links

Notes