In Depth Apps - Oracle eBusiness Suite

Wednesday, March 19, 2008

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

Update: As posted on Steven Chan's blog, the answers to my queries in this post are now well described in Metalink Note Note:726711.1

One of the well publicized and contentious considerations at the crossroads of Fusion relates to the "official" lack of support for the mod_plsql component in the Oracle eBusiness Suite Release 12.

I'm a keen battler on items of contention and I've been tracking this one for while. A high percentage of customers I've dealt with have invested in mod_plsql (mod PL/SQL) based solutions. With its reappearance on the forums recently and interest in R12 I thought it would be nice to take a step back to this.

First of all let's neglect the question a bit longer and look at a different statement, for reference, from here:

Although mod_plsql is no longer hosted as part of the standard Release 12 technology stack infrastructure, it's still possible to use it, in albeit a configuration that requires more diskspace. You can have a separate Oracle Application Server 10g installation, either in a separate ORACLE_HOME on an existing server, or on a physically separate machine. You can use mod_plsql and other mod_plsql-based tools (such as Application Express) on that instance to access the E-Business Suite Release 12.

Hmm, so we know that we can use mod_plsql with Release 12. Just need to spin those propellors a bit and hey presto - there you go, working again. Or even just plug that Apache module back in.

If thats the case, then how does this all relate to support? Surely if the product can be used in a supported configuration, then it should be supported!? Using Apache (Oracle HTTP Server) and mod_plsql is one supported configuration of Oracle Application Express so I don't see the problem being with mod_plsql itself. If it was an inherent problem with mod_plsql then where would that leave Metalink?

Also, the quoted statement above conveniently fails to mention that inevitable L word - licenses. I'm no expert in the L word, so I venture no further and leave that to your own due diligence!

My current understanding - and I may be wrong so correct me if I am - is that:

the use of mod_plsql in combination with the APPS schema inherently raises security problems
mod_plsql is not on the roadmap to Fusion

With respect to 1, security problems generally can be fixed, for example, as they were for those issues in Security Alert #28.

With respect to 2, this is a technology decision in direction made by Oracle so my feeling is that it shouldn't affect support.

So where does that leave us?

Well, I leave you to form your own opinion, but I'd love Oracle to clarify further their position on the support of mod_plsql with Release 12.

PS. For more details on using mod_plsql with Release 12 open a Support Request (SR) on Metalink and ask for Note 435544.1.

Monday, March 17, 2008

Tuesday, March 11, 2008

Time for an Oracle eBusiness Suite Release 12 Upgrade? Eat your own dog food!

A number of years ago Oracle trumpeted the global implementation of its eBusiness Suite internally. In my view, this was a very important event for all eBusiness Suite customers. Why? Oracle was not only eating its own dogfood, but also giving customers a number of signals:

Oracle believes and relies on its own products.
Gaps/shortcomings in its own products become highly visible internally.
The readiness of major releases is signalled by Oracle's internal adoption of the release.

Release 12 has been generally available for over a year - since 1 Feb 2007, with key enhancements such as BI Publisher (XML Publisher), Integration Repository, Subledger Accounting and Multiple Organization Access (MOAC) being in the spotlight. Four Release Update Packs are available at the time of writing (to version 12.0.4). My understanding is that Oracle has upgraded its internal production instance and had a month or two to iron out the bugs, so to my mind Release 12 is ready for the primetime!

Now I can hear the echoes "but what about Fusion ... we can go directly from 11i10 (11.5.10.x) to Fusion?" ...

Well, I'm a fan of "big bang" implementations, but I also respect the path of least resistance and with what I know about Fusion my thinking is the path from Release 12 (which is based on Fusion Middleware) to Fusion will be smoother. I guess we'll see later this year when (hopefully) a number of Fusion Applications are released. I'm not fond of holding my breath for anticipated software releases!

In any case here are a few pointers to handy Release 12 Upgrade resources.

Note: for the presentations you may need Username: cboracle - Password: oraclec6

Presentation: Upgrading to eBusiness Suite R12 - Best Practises
Presentation: Upgrading to Oracle Fusion Applications: Planning Ahead
Presentation: Upgrade with Oracle’s Maintenance Wizard
Whitepaper: Best Practices for Upgrading Oracle E-Business Suite
Metalink Note: Upgrading to Release 12
Blog - Steven Chan: Apps Release 12.0.4 Release Update Pack Now Available
Blog - Steven Chan: Top 5 Myths About Patching Apps Environments
Forum: Oracle eBusiness Suite R12 Install/Upgrade
Software: eDelivery
Documentation: Oracle Financials Concepts Guide, Release 12
Documentation: Oracle Financials and Oracle Procurement Functional Upgrade Guide: Release 11i to Release 12

With respect to technical considerations such as the desupport of mod_plsql, the impending demise of Oracle Reports and Workflow, if you'd like my view, leave me a comment or send an email!

Update: Added links to documents thanks to David (see comments)

Monday, March 10, 2008

Boost productivity, reduce user frustration: Speed startup of Oracle Applications concurrent requests

Apps Users: Do you recognize this key sequence?
alt-v r enter alt-r alt-r alt-r alt-r alt-r ...

That's me waiting for concurrent requests to complete ... or worse ... to start!

Are you, your Developers or Test Team constantly clicking the "Refresh" button on the View Concurrent Requests screen to no avail?

If this is you then read on ... or forward to your DBA ... it could be time to decrease the sleep time of your standard concurrent manager!

Does the following query return 30, 60 or higher for the "Standard" concurrent manager?

select fcq.concurrent_queue_id
,      fcq.concurrent_queue_name
,      fcqt.user_concurrent_queue_name
,      fcqs.sleep_seconds sleep_sec
,      fcqs.min_processes min_proc
from   fnd_concurrent_queues fcq
,      fnd_concurrent_queues_tl fcqt
,      fnd_concurrent_queue_size fcqs 
,      fnd_concurrent_processors fcpr
where  fcq.concurrent_queue_id = fcqs.concurrent_queue_id
and    fcq.concurrent_queue_id = fcqt.concurrent_queue_id
and    fcq.concurrent_processor_id = fcpr.concurrent_processor_id
and    fcqt.language = 'US'
and    fcpr.concurrent_processor_name = 'FNDLIBR'
order by decode(fcq.concurrent_queue_name, 'STANDARD',0,1)
,        fcqt.user_concurrent_queue_name;

Is your average wait (AVG_WAIT) time for concurrent requests starting above a second or two?

Caveat: the following query assumes the majority of requests are not scheduled, any wait over 2minutes is excluded, and request history is retained for at least 2 weeks.

PROMPT Concurrent Requests wait stats for the prior week
select trunc(sysdate-8) date_from
,      trunc(sysdate-1) date_to
,      count(1) num_requests
,      round(sum(actual_start_date - request_date),2) tot_wait
,      round(sum(actual_start_date - request_date) / count(1),2) avg_wait
from   fnd_concurrent_requests
where  actual_start_date is not null
and    actual_start_date - request_date < 120
and    request_date >= trunc(sysdate-8)
and    request_date <  trunc(sysdate-1);

Review your Workshift for your Standard (or similar) manager.

System Administrator, Concurrent, Manager, Define
Query up the Standard manager (or similar)
Click on Workshifts
Review what the Sleep Seconds and Processes are set to.

Set the sleep time low enough, say 10 seconds, and check that there are adequate processes.

Note of course that reducing sleep time or increasing processes will put more load on your hardware, but if you have the capacity try it and see how it goes!

Thursday, March 06, 2008

Running Totals in BI Publisher + Cannot convert to number

Over on the BI Publisher forums, I spotted a interesting one error when attempting to put page totals using a running total using updateable variables and templated footers:

Once I run the RTF am getting error "Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number"

As per the example in document XML Publisher Users Guide 11i the syntax seemed right, i.e. where INVAMT is the field to calculate running total and RTotalVar is the running total variable:

Initialize variable:

<?xdoxslt:set_variable($_XDOCTX, 'RTotalVar', 0)?>

Set variable:

<?xdoxslt:set_variable($_XDOCTX, 'RTotalVar', xdoxslt:get_variable($_XDOCTX,'RTotalVar') + INVAMT)?>

Show variable:

<?xdoxslt:get_variable($_XDOCTX, 'RTotalVar')?>

After a bit of tinkering with the RTF and XML, I realized the syntax was correct, so what was going on?

One extenuating factor was the location of the "set variable" form field inside a nested table. Time to move it out, no need for it to be there - unless the grouping mattered ... in this case it didn't. So moving the "set variable" form field out of the table fixed things. Moral of the story?

If somethin's not working and you think it should, and the error output ain't doin it for ya - simplify simplify - or should that be KISS KISS - until it works ;-)

Saturday, March 01, 2008

Would the REAL Excel please stand up?!

One of the considerations of using Microsoft Excel is the underlying format of the data you're looking at.

Lets say you're viewing output from an Oracle eBusiness Suite concurrent request, and Excel is automatically opened like here (SYLK) and here (BI Publisher). These solutions pose a couple of questions:

How do you know what the real underlying format is for the Excel file opened?
What are the implications of the underlying format?

A "true" Excel workbook format is a binary format. When you view concurrent request output in EXCEL format from BI Publisher then the output is actually XHTML. If a file with .xls suffix is in XHTML format and you do a blind "save" on the file, then you wouldn't know otherwise. Other formats (SYLK, CSV, etc) prompt you with "File.xls may contain features that are not compatible with XXX format. Do you want to keep the workbook in this format." Another alternative I use to identify the format and more often to change the contents without the subtleties of Excel mangling coming into play is to use a non mangling text editor with hex/binary file editing capabilities, my preferred tool is the fantastic free PSPad. As soon as I open the file the format is obvious.

Can the underlying XHTML format cause a problem?

At this stage I haven't identified any issues, but the question has already been asked. I generally don't like to take chances when an easy workaround is available so my advice is:

Use "File, Save As" functionality and save as type "Microsoft Office Excel Workbook *.xls" whenever you're not 100% sure of your underlying Excel file format.

When you do a file save as, the format that Excel thinks the file will be defaulted in the "save as type" field. So then you'll know!

PS. If anyone has any specific isses with the XHTML output from BI Publisher I'd be keen to hear.

PPS. "True" Excel templates are supposed to be coming to the eBusiness Suite BI Publisher sometime soon ... Tim any update?

PPPS. If anyone knows a CSV editor that doesn't mangle the contents (e.g. dates, number formats) like Excel but has functionality similar then I'd also love to hear!

Monday, February 25, 2008

Disappearing Parameters / Top Level Elements in Template after adding @section to BI / XML Publisher Report

A few people have run into this one so worthy of a post.

When you group with a section, you effectively "set" the base level of your XML to the level at which you group'ed. Hmm, better explained with an example.

Let's say your BIP'ing AR Statements and so you have something like this XML (fragment):

<ARXSGPO_CPG>
<LIST_G_SETUP>
  <G_SETUP>
  <COMPANY_NAME>Vision Operations (USA)</COMPANY_NAME>
  <COA_ID>101</COA_ID>
  <FUNCTIONAL_CURRENCY>USD</FUNCTIONAL_CURRENCY>
  <FUNCTIONAL_CURRENCY_PRECI>2</FUNCTIONAL_CURRENCY_PRECI>
  <LIST_G_STATEMENT>
   <G_STATEMENT>
    <SEND_CUSTOMER_NAME>My Valued Customer</SEND_CUSTOMER_NAME>
    <STATEMENT_DATE>25-JAN-02</STATEMENT_DATE>
    <BUCKET1_HEADING>Current</BUCKET1_HEADING>
    <BUCKET2_HEADING>1-30 Days</BUCKET2_HEADING>
    <BUCKET3_HEADING>31-60 Days</BUCKET3_HEADING>
    <BUCKET4_HEADING>61-90 Days</BUCKET4_HEADING>
    <BUCKET5_HEADING>Over 90 Days</BUCKET5_HEADING>
    <BUCKET1>379431.9</BUCKET1>
    <BUCKET2>267494.31</BUCKET2>
    <BUCKET3>0</BUCKET3>
    <BUCKET4>0</BUCKET4>
    <BUCKET5>130291.76</BUCKET5>
    ...

And in your template you "group" and "section" the statement:

<?for-each@section:G_STATEMENT?>
<?SEND_CUSTOMER_NAME?>
<?end for-each?>

And you want to refer to the element <?COMPANY_NAME?> in the Header section, but if you put <?COMPANY_NAME?> then it doesn't appear (is null).

Why? When you section on G_STATEMENT then that becomes the "base" level, so you need to go back up the XML tree to get to your Parameter / Company Name etc.

In this case you'd need to put

<?../../COMPANY_NAME?>

in your Header section. I.e. the parent (G_SETUP) of the parent (LIST_G_STATEMENT) of the current group (G_STATEMENT).

Update:Alternatively you can use xpath from the root node, in this case you could use

<?/ARXSGPO_CPG/LIST_G_SETUP/G_SETUP/COMPANY_NAME?>

in your Header section.

PS. Its been a while since I posted. Busy helping a number of people with BIP and other such sexy things. Means I have a good post pipeline though!

Thursday, January 24, 2008

Beautiful Statements in 1 Easy Step - Automatically submit XML Report Publisher request for Oracle Receivables Statements output

Update: This post is specifically for Release 11i. New functionality in Release 12 implements BI Publisher in many of the 3rd party facing documents.

This post is specific to Receivables Statements, but equally applies to Dunning Letters as well. It requires you having setup your Data Definition and Template, in the case of Statements codes ARXSGP/ARXSGPO, and changed your output to XML on the concurrent program setup for Statements/Statement Print.

As per Note:337740.1 and Note:429283.1, and as per Enhancement Request 4461071, there is currently a limitation with Statements/Dunning Letters that you can't automatically get BI Publisher/XML Publisher output from the concurrent request. A couple of attempts have been made to add layouts, submit requests to the after report trigger in ARXSGPO.rdf, but can end up with either:

A reports compilation error when you don't pass all 100 parameters to fnd_request.submit_request:

unsupport construct or internal error [2601]

or the XML Report Publisher request completing with a warning, when all 100 parameters passed to fnd_request.submit_request:

One or more post-processing actions failed. Consult the OPP service log for details.
Output Post Processor log shows [UNEXPECTED] [752224:RT2801518] java.lang.reflect.InvocationTargetException

with Output Post Processor log showin

 [UNEXPECTED] [752224:RT2801518] java.lang.reflect.InvocationTargetException

So what can we do? Here's one solution, and this applies equally for any concurrent request producing XML where you don't have control over the template/layout.

Create a package:

create or replace package XXV8_XMLP_PKG AUTHID CURRENT_USER AS
  function submit_request_xmlp
  ( p_code in varchar2
  , p_request_id in number
  ) return number;
end XXV8_XMLP_PKG;
/

create or replace package body XXV8_XMLP_PKG AS
function submit_request_xmlp
( p_code in varchar2
, p_request_id in number
) return number
is
  l_req_id number := 0;
begin
  if p_code = 'ARXSGP' then
    l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB',NULL,NULL,FALSE,
                                           p_request_id,
                                           222, -- Receivables
                                           'ARXSGP', -- Statement Generate
                                           'en-US', -- English
                                           'N','RTF','PDF');
  end if;
  return l_req_id;
end submit_request_xmlp;

end XXV8_XMLP_PKG;
/

Add the following to the after report trigger in ARXSGPO.rdf:

  declare
    v_req_id number := 0;
  begin
    v_req_id := xxv8_xmlp_pkg.submit_request_xmlp('ARXSGP',:p_conc_request_id);
    if v_req_id > 0 then
      srw.message(20002, 'Submitted request_id ' || v_req_id);
      commit;
    else
      srw.message(20002, 'Failed to submit request');
    end if;
  end;

Run Statements and fingers crossed you'll have beautiful output in one easy step!

Update: Fixed mismatch between code passed to package by after report trigger and the "if" statement code, they must match ARXSGP=ARXSGP!

Changing the default layout format from PDF to Excel using Profile Option and FNDRSRUN Form Modification - Submitting BI Publisher Report Request

Update 2: This solution is now obsolete:

Patch 5612820 and 7627832 for R11i have been released for this issue. Applying these patches will overwrite the customization. See this post for details.
Patch 5612820 for R12 has been released. See this post for details.

Well, a few people have been frustrated with the default output format of PDF when submitting BI Publisher based concurrent requests.

Oracle's better solution to provide a default output format on the template definition isn't here yet. For reference see Metalink Note 401328.1, or Bug 5612820 or Bug 5036916, or Forums here or here

I'm not going to hold my breath, so herein lies a solution to set the default based on a profile option value, using an unsupported form modification to FNDRSRUN.fmb (Submit Requests or Standard Request Submission). I don't usually recommend modifications, but in this case its one line of code and the impact is very minor, so if its blown away, we'll just have to get over it ... check the caveats at the bottom of the post too!

Note that it is possible to set the default format to Excel, RTF or whatever your preferred output format is via forms personalization if you always navigate to the Options, Layout block of the Submit Requests screen. But 99 times out of 100 I don't go there.

Onto the instructions.

1. Create profile option "XML Publisher Default Format".

Navigate to Application Developer, Profile

Create new profile option

Name = XXV8_XMLP_DEFAULT_FORMAT
Application = (Your modifications application or Application Object Library)
User Profile Name = XML Publisher Default Format
SQL Validation:

SQL="SELECT MEANING \"Default Output Format\"
  , LOOKUP_CODE
  INTO :VISIBLE_OPTION_VALUE
  , :PROFILE_OPTION_VALUE
  FROM FND_LOOKUP_VALUES_VL
  WHERE  LOOKUP_TYPE = 'XDO_OUTPUT_TYPE'"
COLUMN="\"Default Output Format\"(50)"

2. Set profile option value to Excel (or RTF etc) at the required levels

Navigate to System Administrator, Profile, System

Find you profile option XML Publisher Default Format and set values as required.

3. Modify Form FNDRSRUN.fmb

Copy and open form $AU_TOP/forms/US/FNDRSRUN.fmb

Open Program Unit WORK_ORDER

Find the line:

:templates.format := 'PDF';

Note this is line/char 349/41 in Release 11i FNDRSRUN.fmb 115.169 or 359/40i in Release 12 FNDRSRUN.fmb 120.29

Change to:

-- GR 24-JAN-08 Override default BI Publisher layout output format
--:templates.format := 'PDF';
:templates.format := nvl(fnd_profile.value('XXV8_XMLP_DEFAULT_FORMAT'),'PDF');

4. Compile Form FNDRSRUN.fmb to FNDRSRUN.fmx

Copy the new FNDRSRUN.fmb to your custom top forms/US directory

Compile the fmb to fmx.

FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US:$AU_TOP/plsql
f60gen Module=FNDRSRUN.fmb Userid=apps/apps > genform.log

5. Replace standard FNDRSRUN.fmx with modified version

Note: Replace XXV8_TOP with your custom top.

cd $FND_TOP/forms/US
mv -i FNDRSRUN.fmx FNDRSRUN.fmx.orig
ln -s $XXV8_TOP/forms/US/FNDRSRUN.fmx FNDRSRUN.fmx

6. Test it out.

Some caveats here:

This is an unsupported form modification that will be blown away when the FNDRSRUN form is upgraded (hopefully when the real solution appears).
The instructions above only replace the executable .fmx version of the FNDRSRUn file, so if FNDRSRUN is regenerated/recompiled via adadmin or similar then the modification will not be in place. Redo the "replace" step.
This modification assumes a template type of RTF. PDF can only produce PDF output, so you need to watch this when you set the profile option value.
If you always click on the Options button, then use a forms personalization instead of this forms modification.

Thursday, January 10, 2008

I'm IT - I've been OraBlog tagged.

Tim Dexter tagged me in the current round of OraBlog tag, so as per the rules here are the 8 things you might not know about me:

Pianist? nope, Computer Science. My career could have been as a musician. When it came to University I auditioned and got accepted into a piano performance spot, so my choice was Computer Science or Musician. Either way I'd be tapping a keyboard so my $$ motivated decision meant Comp Sci was it. Plus my brother is better on the music front ;-)
Mmmm, ~~Pork Chops~~ Chocolate. If you leave chocolate near me you won't see it again.
First Day Blues. As a graduate intake at Oracle New Zealand back in 1995, on my first day no one expected me there, so my first week was spent with a SQL*Plus and DBA training manual.
Pickled. I once worked in a pickle factory in Japan.
The Beach. I grew up near, or should that be on, the beach - nothing better than salt water and fresh warm air. It's a shame I'm not a windsurfer in Windy Wellington though.
Patchsets. I'm behind the R11i and R12 patchsets blogs that some Oracle Apps folks might find useful.
Games. Another influence when I was young was Rally-X. So much so a couple of years back I put together an arcade style MAME machine that included hacking a keyboard and wiring up a custom joystick port.
Adrenalin Rocks. Every once in a while I gotta have a bit of a thrill and there ain't much better than in New Zealand. If you're in the "posh part of down under" as Tim says, I'll be more than happy to help you out with the 134m Nevis Bungy, 7m waterfall Kaituna Rafting, Kayaking Aniwhenua Falls or Mountain Biking the Queen Charlotte Walkway.

Now to the tagging, bound to tag someone already tagged, but hey, will try anyway!

In Depth Apps - Oracle eBusiness Suite - Gareth Roberts