NZOUG Conference 2008: Early Bird Closing extended! Win an iPod Touch

An announcement for those readers on the conference trail to New Zealand in October:

Join me at the NZOUG Conference 2008 October 20/21 in Rotorua and go in the draw for an iPod Touch! Early Bird registrations have been extended and will close on Monday 8 September, so register now and be in the draw to win!

Details at: http://nzoug.org/ipod08

PS. I'm on the NZOUG Committee and will be at the conference hence the shameless plug for this event ;-)

PPS. If you have any sessions/questions/design issues or ideas bring them along or let me know and I can tee up a special slot for you!

XML / BI Publisher concurrent request fails with no error message? Check Output Post Processor (OPP) logfile for errors like: invalid char in text

So your Oracle Reports based concurrent request completed with warning and your concurrent request log file looks like this:

+------------- 1) PUBLISH -------------+
Beginning post-processing of request 1294502 on node XXXX01 at 02-SEP-2008 12:53:28.
Post-processing of request 1294502 failed at 02-SEP-2008 12:53:33 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
+--------------------------------------+

And if you go to System Administrator, Concurrent > Manager > Administer, Query up "Output Post Processor", click on Processes, View Manager Log, you see something like:

[9/2/08 10:44:34 AM] [198325:RT1294354]
Template code: XXGLXXXX
Template app:  XXV8
Language:      en
Territory:     US
Output type:   EXCEL
[9/2/08 10:44:49 AM] [UNEXPECTED] [198325:RT1294354] java.lang.reflect.InvocationTargetException
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at oracle.apps.xdo.common.xml.XSLT10gR1.invokeParse(XSLT10gR1.java:517)
 at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:224)
 at oracle.apps.xdo.common.xml.XSLTWrapper.transform(XSLTWrapper.java:177)
 at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:1044)
 at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:997)
 at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:212)
 at oracle.apps.xdo.template.FOProcessor.createFO(FOProcessor.java:1657)
 at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:967)
 at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5888)
 at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3438)
 at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3527)
 at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:247)
 at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:157)
Caused by: oracle.xdo.parser.v2.XMLParseException: Invalid char in text.
 at oracle.xdo.parser.v2.XMLError.flushErrors1(XMLError.java:324)
 at oracle.xdo.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:290)
 at oracle.xdo.parser.v2.XMLParser.parse(XMLParser.java:266)
 ... 17 more
[9/2/08 10:44:49 AM] [198325:RT1294354] Completed post-processing actions for request 1294354.

You look at your XML file via View Concurrent Requests, Diagnostics, View XML. Then how do you find the invalid character?

Try loading the XML Data in BI Publisher Desktop Word Plugin (Data > Load XML Data), and hopefully you'll see something like the following that gives the details of where the error is occurring (of course without the smudges ;-):

In this case the "invalid char" is indicated by the square box.

Throwing that into a hex editor (part of the highly recommended PSPad) we see character hex 05 followed by the two periods (hex 2E):

Looking at our ASCII table we see 05 is Enquiry - but this isn't a printing character and so will be causing the problem.

Okay, so now we know what's causing it, we can just update the data to remove the special character, and run the report again without hassles!

Or we could write a wrapper PL/SQL procedure to remove the control characters from the affected fields and add to the Report definition SQL.

Or those of you with more problems that just one occurrence of the control character could log an SR and see if something can be done other than that referred to in Note:155078.1!

BI / XML Publisher Bursting fails with InvalidPropertyValueException for IPP_HTTP_RESPONSE_CHECK_INTERVAL

If you are attempting bursting and hit an error that looks something like the following:

[082608_022234429][oracle.apps.xdo.batch.DeliveryHelper][EXCEPTION]
oracle.apps.xdo.delivery.InvalidPropertyValueException: Invalid String property  value for Integer. 
Property name :[IPP_HTTP_RESPONSE_CHECK_INTERVAL], 
Property  value :[O]

Check out Metalink Note 603424.1!

Solution:

A patch containing a later version of the DeliveryHelper.java must be installed in order to resolve the issue:

• Oracle E-Business Suite 11i The issue is encountered only when Patch 6470598 has been installed. This one-off patch has already been superseded by a new patch which included the fixed version of the DeliveryHelper.java. Patch 6470598 XML Bursting Reply-To Using From Instead resolves the issue.
• Oracle E-Business Suite Release 12 A one-off patch on top of the 12.0.4 code level is required to resolve the issue: Patch 6482024 Bursting Engine Does Not Resolve OA_MEDIA. This patch is released under the terms of 'By Support' which implies that the patch is queryable on Metalink, but downloads of this patch requires a password. Please contact Global Customer Support to receive a password to download the patch.

Discoverer Plus window not appearing or Jinitiator JVM conflicts on the Oracle eBusiness Suite: Java JRE EnableSecureStaticVersioning

If you find that Discoverer Plus won't start (or the Discoverer Plus windows doesn't appear) after downloading the Java Applet, or if you're having issues with JInitiator with Oracle Applications, then it could be that you have multiple versions of Java required. For your Oracle Discoverer Plus, Oracle JInitiator Java Plugin (JVM), or other Java based software multiple versions of Java can cause conflicts, so try the following steps. Actually I'd recommend any rollout of the Disco Plus or EBS client include the below steps!

1. Allow multiple concurrent versions of Java to run by enabling Secure Static Versioning.

Update or Create the following Windows registry entry on your desktop client as follows;

1.1 If the 'Java Deployment' and 'Policy' folders do not exist under your HKEY LOCAL MACHINE -> SOFTWARE -> JavaSoft entry in your registry you will first need to create these folders as follows;
1.2 Right mouse click on the JavaSoft folder in your registry and from the menu select 'New -> Key'. Name the new folder 'Java Deployment'. Open this folder and create another folder named 'Policy' underneath it, then follow the rest of the steps below.
1.3 Go to 'HKEY LOCAL MACHINE -> SOFTWARE -> JavaSoft -> Java Deployment -> Policy' in your Windows registry
1.4 Right mouse click on the Policy folder and from the menu select 'New -> DWORD Value'
1.5 Right click on the newly created value and rename it to - 'EnableSecureStaticVersioning'
1.6 The Data value should remain at the default of '0x00000000 (0)'

2. Switch off automatic updates of Java.

It is advisable to turn off the automatic upgrade facility for the desktop client JRE. This will ensure that newer versions of the JRE plug-in are not installed, and used with Oracle E-Business Suite / Oracle Discoverer unknowingly. This feature can be turned off as follows;

2.1 From the 'Start Menu', select 'ControlPanel'
2.2 Double click the 'Java' icon
2.3 Select the 'Update' tab
2.4 Uncheck the 'Check for Updates Automatically' box
2.5 Click the 'Never Check' button on the 'Java Update -Warning' pop up window
2.6 Click 'OK' to close the 'Java Control Panel'

If you are getting crashes that refer to jvm.dll see my other popular post on JVM crashes.

References:

• Sun Forum 1
• Sun Forum 2

NZOUG Conference 20-21 Oct 2008: Registration Open and Call for Papers Closing

The NZOUG Conference 2008 is open for registrations. The event is shaping up well, with some excellent sponsors, high profile speakers, and interesting papers. As well as that the entertainment in Rotorua is not to be missed!

The Call for Papers is closing 25 July, so if you are a potential speaker make the most of the last minute and get those abstracts in!

Watch the NZOUG website for updates on the conference.

PS. If you're sending a group, watch out for the buy 3 get 1 free offer.

Revisited: Why is mod_plsql not supported with the Oracle eBusiness Suite Release 12? Fusion Crossroads #1

In a prior post I raised the question of why mod_plsql was not supported with the eBusiness Suite R12. Excellent to see that Oracle was listening to the community and as Steven Chan's post highlights, Metalink Note:726711.1 has been created to address this question.

In summary you can still run mod_plsql connecting to the eBusiness Suite as long as the Apache/mod_plsql are in an isolated install and you address security and associated considerations. And of course as a customization it is not supported under the guise of the eBusiness Suite ... but hey your customizations never are!

Guess that means the same for the other sweet tools you'd connect/integrate to your eBusiness Suite ... like Application Express (APEX), PHP/Zend, Ruby on Rails, etc.

Where do I get the XML file or request output file for my BI Publisher Report / Concurrent Request?

A quick post ... it's been a frenetic few months, so blog posts a bit thin lately but plenty of good stuff in the pipeline!

Generally when I'm designing a BI Publisher/XML Publisher report I'll create a concurrent program that generates XML, I'll follow the usual steps:

1. Setup the concurrent program to output Text
2. Run it to ensure its working
3. Switch the concurrent program to output XML
4. Run the concurrent program to output XML
5. SFTP/copy the o*.out XML file from $APPLCSF/$APPLOUT
6. Load it into Word via the BI Publisher Desktop plugin and design the layout "on the run".
7. Setup the BI Publisher Data Definition/Template
8. Perform system/unit tests

Is there an easier way to get the XML file from a concurrent request? Sure is - nice and easy through the front end.

Get the XML File from a BI Publisher based concurrent request/report:

1. View->Requests
2. Find the request you're interested in
3. Click on Diagnostics button
4. Click the View XML
   
5. Save the file to your PC by doing File->Save As, *.xml

But where does the XML file live?

Well, by default it is $APPLCSF/$APPLOUT/o{REQUEST_ID}.out.

But if that's the XML file, then where does the actual output file reside as the location of the XML file is where normal request output resides?

Get the Request Output File for a BI Publisher based concurrent request/report:

The output is $APPLCSF/$APPLOUT/{REPORTNAME}_{REQUEST_ID}_{COUNT}.PDF|RTF|EXCEL|HTML

Where

• REPORTNAME is the concurrent program short name,
• REQUEST_ID is the concurrent request ID
• COUNT is a counter based on the number of times a request has been re-published.
• One of PDF, RTF, EXCEL, HTML is the file extension/type dependent on the output formats chosen.

An example is: XXXX_FNDSCURS_2803880_1.EXCEL

Output file location courtesy of Tim's post here.

Cool, file locations identified!

 

Find JDeveloper with OAF / OA Extension patch for Oracle eBusiness Suite Release 11i / Release 12

I've been working with Java more recently, and thought this might be a handy reference.

In order to find the right JDeveloper match up for extending the Oracle eBusiness Suite / Applications, point your web browser to http://host:port/OA_HTML/OAInfo.jsp, then choose the matched JDeveloper patch.

Release 11i

OA Framework 5.10 patch	JDeveloper 9i Patch
ATG.PF.H (patch 3438354 or Oracle Applications 11.5.10) or 11.5.10K	Patch 4045639 9IJDEVELOPER WITH OA EXTENSION ARU FOR FWK.H
ATG PF CU1 (patch 4017300) or 11.5.101CU	Patch 4141787 9IJDEVELOPER WITH OA EXTENSION ARU FOR CU1
ATG PF CU2 (patch 4125550) or 11.5.102CU	Patch 4573517 Oracle9i JDeveloper with OA Extension for 11.5.10 CU2
11i.ATG_PF.H RUP3 (patch 4334965) or 11.5.103CU	Patch 4725670 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP3
11i.ATG_PF.H RUP4 (patch 4676589)	Patch 5455514 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP4
11i.ATG_PF.H RUP5 (patch 5473858)	Patch 6012619 9IJDeveloper With OA Extension ARU FOR 11i10 RUP5
11i.ATG_PF.H.RUP6 (patch 5903765)	Patch 6739235 9IJDeveloper With OA Extension ARU FOR 11i10 RUP6

Release 12

ATG Release 12 Version	JDeveloper 10g Patch
12.0.0	Patch 5856648 10g Jdev with OA Extension
12.0.1 (patch 5907545)	Patch 5856648 10g Jdev with OA Extension
12.0.2 (patch 5484000 or 5917344)	Patch 6491398 10g Jdev with OA Extension ARU for R12 RUP2 (replaces 6197418)
12.0.3 (patch 6141000 or 6077669)	Patch 6509325 10g Jdev with OA Extension ARU for R12 RUP3
12.0.4 (patch 6435000 or 6272680)	Patch 6908968 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12 RUP4
12.0.5 - no full 12.0.5 release planned only individual product suites like HRMS and Financials 12.0.5	n/a
12.0.6 (patch 6728000 or patch 7237006)	Patch 7523554 10G Jdeveloper With OA Extension ARU for R12 RUP6
12.1 (Controlled Release - only included for completeness)	Patch 7315332 10G Jdev with OA Extension ARU for R12.1 (Controlled Release)
12.1.1 (rapidInstall or patch 7303030)	Patch 8431482 10G Jdeveloper with OA Extension ARU for R12.1.1
12.1.2 (patch 7303033 or patch 7651091)	Patch 9172975 10G Jdeveloper with OA Extension ARU for R12.1.2
12.1.3 (patch 9239090 or patch 8919491)	Patch 9879989 10G Jdeveloper with OA Extension ARU for R12.1.3

For details latest details see Metalink Note:416708.1 or Note:330236.1

NZOUG Call for Papers - Conference 20-21 October 2008

Keen to head down-under? Check out this message from the New Zealand Oracle Users Group.

The New Zealand Oracle Users Group is pleased to announce… Call for Papers and Training for the 2008 Conference We only require your ABSTRACT of not more than 150 words at this stage. We invite all users and suppliers of Oracle technology and applications and related products to submit presentations, including Oracle Users with an interesting story, case study or any useful tips Oracle Partners playing a key role in the Oracle world Third party suppliers of products and services that will assist Oracle technology and application users Presentation topics may include but are not limited to Oracle Aplications such as eBS, JDE, Peoplesoft and Hyperion, and Oracle Development, Middleware and Database technologies. Everything is welcome for submission but delegates are especially interested in hearing case studies of real life scenarios and situations, lessons learnt and best practices. Both Technical and Business streams will run throughout the conference programme with presentations covering the implementation, use and support of the whole range of Oracle and associated third party products and services. If your paper is selected for presentation: You’ll be admitted to the conference free of charge upon submission of your full written paper, and Your paper will be published in the conference proceedings.
SUBMITTING AN ABSTRACT
How do I submit an abstract? Visit our submission website and complete the Call for Papers submission, by 20 June 2008 Also anyone can submit topics you are interested in, and we’ll try to source a presenter for the topic. We’ll post further information about this and for presenters on our website in due course. Remember to visit our website for all up to date information on regional events, the conference, and other news. We look forward to receiving your completed submissions by 20 June 2008
HOW DO I REGISTER FOR THE CONFERENCE?
We are not quite ready to open registration but watch this space (or keep an eye on our website) in the next few weeks for an announcement.
WHAT DISCOUNTS CAN WE GET?
There will be an early bird discount for all registrations before the 1st September 2008. Watch this space for these great rates.
VENUE AND DATES
Rotorua 20th and 21st October 2008 Novotel Rotorua Lakeside – Rotorua
NEED MORE INFORMATION?
Any questions please contact our conference management company, The Conference Company. By phone on +64 9 360 1240 or by email at nzoug@tcc.co.nz

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

Update: 14-Mar-2012 Added supported back-end method to update Workflow Notification Mailer attributes. Warning: This post also contains unsupported SQL updates, use at your own risk!

I think the Workflow Notification Mailer is excellent, but one of my pet hates is the setup process for a mailer. I never seem to be able to get it setup cleanly. One thing that really bugs me is that the Service Component Manager (Workflow Mailer Service) needs to be running before you can set the "Override Address". So when setting up a mailer you have to be very careful that the "Manual" startup option is set or your Mailer will start after configuration or when you startup the Workflow Mailer Service. Similarly caution is needed when cloning non-Prod environments from Prod when the Prod Override Address is not set, but Test/Dev etc need to be set.

Additionally, there's no button anywhere with a label like "Click me to update all Mail_Status pending Notifications as Sent" which is what the following accomplishes:

update WF_NOTIFICATIONS
set mail_status = 'SENT'
where mail_status = 'MAIL';

# Note: Change the apps_password in the following command
sqlplus apps @$FND_TOP/patch/115/sql/wfntfqup.sql APPS apps_password APPLSYS

Okay, so here's how you get the current Override Address via SQL on the backend:

select fscpv.parameter_value
from   fnd_svc_comp_params_tl fscpt
,      fnd_svc_comp_param_vals fscpv
where  fscpt.display_name = 'Test Address'
and    fscpt.parameter_id = fscpv.parameter_id; 

And now here's a very unsupported way to update the Override Address - please try this on a Test environment first to make sure it works for you! Note this will update all Mailers you have setup. Recommended to stop and start the mailers after making this change. Also make sure you change the email address to your Test Address and change the NONE to your current value (from previous SQL) if necessary:

update fnd_svc_comp_param_vals
set    parameter_value = 'MyTestAddress@MyCompany.com'
where  parameter_value = 'NONE'
and    parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address'
);

commit;

Update: 14-Mar-2012 - There is a supported way to make back-end updates to Workflow Notification Mailer (and other service components). This is documented in Support Note 1399464.1. In short run the following and follow the prompts:

sqlplus apps @$FND_TOP/sql/afsvcpup.sql

PS. See Metalink Note 467229.1 if you get the dreaded "ORA-20000: Oracle Error = -20000 - ORA-20000: 330 Alert messages exist in WF_NOTIFICATION_OUT. Ensure these messages are processed before running wfntfqup.sql"

PPS. Anil has a nice post clear post where the Override Address (Test Address) lives these days here.