Confessions of an Oracle Apps Consultant

InSync10

2010-07-14T08:09:00.003+10:00

Hi again everyone
So for InSync10 in August (Melbourne, Australia) I submitted two presentations that are completely different from one another so that the organisers could pick whichever fit in best with the rest of the agenda. They both got in - LOL! So for the record, this year I'm presenting about:

the effect of Subledger Accounting when you upgrade to R12; and
how you could use a risk-based approach to UAT for the R12 upgrade

I've done the draft outline so far - time to knuckle down this week as I think that both topics require support materials rather than just a presentation. Does anyone other than me read white papers from conferences - I always like to walk away with some reference material (preferably in PDF rather than hardcopy) - is it just me? Does anyone else keep them forever? (Yes, I still refer back to Cathy Cakebread's whitepaper on reconciling AR to GL which was originally written for R9 I believe).
Take care
Jo

Favourite Profile Options - what's yours?

2008-03-14T16:02:00.003+11:00

Okay, first up I have to vote for:

FND Corporate Branding Image - yep, that's the one that replaces that Oracle logo with yours - nice! Only normally see this one used on the big sites, but what a slick profile option :)

Self Service Personal Home Page Mode - prior to 11.5.9 I used to use this to change the front page back to the blue screen so I could launch the forms in one click. Now I just set up a favourite and leave this one alone.

Concurrent:Active Request Limit - isn't it tempting to set this? I mean, really.... I'm always tempted but never done it to anyone other than myself LOL
Concurrent: Hold Requests - yeah, this too... see above ;)

Concurrent:Report Access Level - if you're the system administrator or support look up this profile option, everyone else don't worry about it - you don't need to know.

Flexfields: AutoSkip - means when you are in a pop-up flexfield and you've finished typing the two digits in segment 1 for example the cursor will automatically go to the next segment without you pressing TAB.

Flexfields:Open Descr Window and it's brother Flexfields:Open Key Window - controls whether the flexfield automatically pops the window open when your cursor goes into the field. Annoying if you want to enter concatenated values.

Utilities: Diagnostics - set this to Yes for the user to use the Diagnostic tools without having the APPS password. Useful for support staff and consultants :)

and of course everybody's favourite:

Java Color Scheme - the one that lets you change the colour of your screen - I make production red whenever I have access just so my subconcious knows I don't belong in there. It's hideous but it works. :)

Over to you guys - got a favourite?

Jo

Why TE.040 Test Scripts for UAT are a waste of time and money

2008-02-29T10:39:00.003+11:00

I’m all for testing… I love the stuff.. but seriously, spending weeks of effort putting together a test script in the format of:

Enter "Jo's super Journal 290208" as the journal entry header
Enter the journal source "Manual"
Enter the journal category "Adjustment"
.....
????

It seems I've spent weeks of my life over the last 11 years writing these things. Weeks I will never get back. (There are some nice ones uploaded by other nice humans over at http://www.oracleappsblog.com/index.php/weblog/comments/oracle-11i-test-scripts/ if you’re looking for a standard test script repository by the way).

Why I don’t like them, in a nutshell:

too easy to concentrate effort on the test script steps instead of taking a step back and looking at the types of testing you’re doing and what sort of coverage you’ve got across the applications, particularly with regard to negative testing and scenario-based integration testing.
users hate writing them and try to outsource it to the implementation partner, which is never going to work because seriously the only person who can effectively test whether you’ll still be able to do your job on Monday morning is you, not some hired gun.
they contribute to the intimidating thud factor. Let’s face it – would a user would like to see a 50 page test script he/she has to get through or a one page one? Keep in mind that he/she’s also juggling their actual job (which is piling up on their desk while they’re here testing this thing because no one actually back-fills the finance department)… talk about stress.
they get out of date very quickly and are never maintained, so when you go to do the next project you find the steps listed for ‘Enter an invoice’ are not the way Payables actually perform the task. It must be kept in line with the user procedure but there's no method to do so.

So - back to basics – what is the purpose of User Acceptance Testing? To ascertain whether the business can continue to function with the system as delivered. Whether it be a fresh implementation, an upgrade or a really big patch, the purpose of UAT is the same.
We need to spend time, effort and money on documenting the user procedures and the training. Put the effort in where it will really be used ongoing, not just used for 2-4 weeks of UAT every six months or so.

The test scripts can then just document the actual test case or scenerio and refer out to the procedures/training guide on how to actually perform the task. If you really want a detailed, step-by-step test plan for UAT use a product to document your procedures which will produce the test script steps automatically, e.g. Oracle’s User Productivity Kit (UPK) or (my favourite) RWD’s uPerform. If you get really exited go have a look at Turnkey Solutions and see how you can have software document the testing for you apparently (haven't tried it myself yet, but the demo looks promising). Then you can concentrate your effort on what you should be testing, rather than how you should be testing it.

Is 10.7 Character extinct? Hardly...

2008-02-22T15:41:00.002+11:00

With all the talk about R12 and Lifetime support, consultants can be forgiven for thinking that the old character interface is actually extinct... the truth is (for those of you out there still on it) that no, it isn't. There are actually Oracle E-Business Suite sites still on the 10.7 Character release (despite the fact that it's desupported) and wondering to themselves where they go from here? Have they totally missed the ship? So for anyone in this position, here's my two cents worth....

Upgrading to Release 11.5.10 is not impossible.... it's actually quite straightforward.... the secret (and it's not much of a secret) is to use a DBA team who've done it before... and seriously it's now 2008, we've been upgrading from 10.7 Character to 11i for eight years now, the DBAs are getting pretty good at it :) I've always said that an actual upgrade (not a point release, which is a really BIG patch, an actual upgrade which involves laying down a new $APPL_TOP and running AutoUpgrade) is not the time to break in your baby DBA. Trial by Fire may have it's uses as a training technique (although I'm not a huge fan of that either), but a massive upgrade is not a candidate.

The only thing which can bite you in the backside is going to be those customisations, so it's time to gather every single piece of documentation you can find with the word 'Oracle' in it then see what you've got. If we're lucky we can kill 90% of them while we're at it (don't get me wrong - I like developers, I just don't like massive customisations which bugger up the standard stuff).

So, as usual, I'm still recommending Upgrade over ReImplementation provided you're basically happy with the Accounting Flexfield structure and calendar. (And seriously - if you want to change your AFF, don't re-implement, just use Flexfield Express - it's cheaper and you get to keep your history).

Okay, end of babbling for today... catch you next time..

Jo

Openworld Session Room Change

2007-11-14T12:30:00.000+11:00

For those who don't check the morning OpenWorld newspaper my session tomorrow has changed rooms, it is now on in....

Saloon 12 & 13 at the Marriot

Apparently Diana & I needed an upgrade as we had too many registrations for the other room? How cool is that? And for those who care - yes, I will be talking about actual case studies in the presentation not just the theory :)

Have a great day
Jo

Openworld San Francisco

2007-11-06T13:57:00.000+11:00

Yes! The rumour is true! I will be at San Francisco, co-presenting about Internet Expenses! Come to the session.... or catch me for a coffee ... definately the place to be next week!

For those of you who are preregistering here is the info:
Session ID: S291212
Date: Wednesday 14th November
Time: 3pm - 4pm (What are the chances everyone will fall asleep?)
Where: Marriott Salon 3

And for those attendees who feel the urge to contact me (can't think why - perhaps that coffee?):

The Easiest way to get Chart of Accounts Segment Descriptions

2007-05-10T12:44:00.000+10:00

Such as account descriptions or cost centre descriptions as well as the value:

SELECT
gcc.code_combination_id
,gcc.segment1
,gcc.segment2
,gcc.segment3
,gcc.segment4
,gcc.segment5
,gcc.segment6
,gcc.segment7
,gcc.segment8
,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1),1,40) segment1_desc
,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2),1,40) segment2_desc
,DECODE(gcc.segment3,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3),1,40)) segment3_desc
,DECODE(gcc.segment4,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4),1,40)) segment4_desc
,DECODE(gcc.segment5,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5),1,40)) segment5_desc
,DECODE(gcc.segment6,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,6,gcc.segment6),1,40)) segment6_desc
,DECODE(gcc.SEGMENT7,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,7,gcc.segment7),1,40)) segment7_desc
,DECODE(gcc.SEGMENT9,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,8,gcc.segment8),1,40)) segment8_desc
,gcc.chart_of_accounts_id chart_of_accounts_id
,gcc.account_type
FROM
gl_code_combinations gcc

Otherwise you have to do a hideous number of joins back to FND_FLEX_VALUES_TL and generally hard-code in value set ids :)

Have a fun day
Jo

Auditing on FND_FLEX_VALUES: How to see Audit History

2007-01-10T14:23:00.000+11:00

To keep an audit trail be kept for mapping of segment values to external values (such as for extracts to other systems) and changes to it

1) Define DFF on FND_FLEX_VALUES to contain the mapping values

2) enabled auditing on FND_FLEX_VALUES,

3) if they wish to query the audit data start with this:

select *
from FND_FLEX_VALUES_AC1
where attribute1 is not null

FND_FLEX_VALUES_AC1 is a view of the audit table

How to Migrate Personalizations from TEST to PROD on Release 11.5.10.2

2007-01-10T13:35:00.000+11:00

This applies to iProcurement (11.5.10 only), iExpenses 2nd Generation (i.e. above 11.5.7 and the white screens, not the blue ones) and anything else that uses the new self-service architecture (i.e. NOT iTime or anything else with those blue screens and no bouncing balls across the top.... you know what I mean)

It's all covered in Metalink Note 370734.1 but suffice is to say

- it can be done, you don't need to redo all the personalizations

- you can only use this on release 11.5.10.2

- you will need the appltest and applprod passwords and a passing familiarity with Unix (or a tame developer or DBA to help you)

Have a great day

Jo

I think I'm in love with UPK

2006-11-04T06:18:00.000+11:00

Yeah, I know - I've been lax lately - no posts! So on Thursday of course I spent the day at the Red Rock Leadership Forum and one of my collegues gave a demo of the User Productivity Kit which Peoplesoft had (and has now been ported across to Oracle Apps and JD Edwards too). All I can say is - WOW! Everything I dreamed Tutor would do for my life.... but this actually seems to do it :)

Documentation, training and testing - three of my favourite things in the world. And the UPK makes training material (instructor led and online) and gives you those detail level, field-by-field test scripts that everyone seems to be totally keen on printing and storing somewhere (despite my preference for concentrating your efforts on effective scenario-based testing). And more importantly it does it faster than anything else I've seen!

Needless to say, our Peoplesoft guys at work got a pretty good laugh at the Oracle Apps consultants over this one - we're all madly in awe, they've been using the thing for years of course! So the UPK developer is now on my laptop and my adventures in the land of UPK begin today (well technically last night, which is when I started downloading it as the zip file is a massive 405309KB according to my computer - it's still extracting as I type).

So will my love-at-first-sight affair with this software come crashing into an evil wall of reality? Possibly.... I'll let you know

Employees with No Usernames or entry in WF_ROLES

2006-06-01T14:39:00.000+10:00

And to list out all the active employees, their usernames and whether they have an entry in WF_ROLES:

select ppf.employee_number, ppf.full_name, fu.user_name, wr.status
from per_people_f ppf,
fnd_user fu,
wf_roles wr
where ppf.person_id =fu.employee_id (+)
and ppf.person_id =wr.orig_system_id (+)
and ppf.current_employee_flag='Y'

Run this to check everyone in the approval hierarchy is set up correctly if you are having any problems with testing transactions in Approval Manager and get the dreaded...
ORA-20001: The approver identified by the following parameters is invalid: originating system PER originating-system ID PER_ID. Please delete or replace this approver wherever they occur in AME data, including approval groups, list-modification conditions, and substitution actions. This approver does not have an entry in wf_roles. (ORIG_SYSTEM_ID=)

Pre-Production Diagnostics Tests

2006-05-23T15:19:00.000+10:00

Metalink have rather helfully published a summary of the diagnostics tests which are useful in a pre-production situation to check config. I usually run these things just before UAT and again in Prod just before we turn it all on ;) Really useful for applications like iExpenses and iProcurement where you're implementing into an already live applications instance - no matter how good your change control is, prod will still not be the same as test ;)

Loading employees from Spreasheet

2006-05-09T11:09:00.001+10:00

Another trick I keep forgetting the name of so I'm storing it here....

Express setup tools in iProcurement is the thing that lets you upload employees from a spreadsheet. As of 11.5.10 it got moved to a concurrent program, as per Metalink Note 303087.1. Today the doco is on page 5-19 of the iProc user guide, but tomorrow it could be anywhere.

Have a great day!

Excel Tricks - How to split the AFF (or anything else) into different cells

2006-05-02T14:39:00.000+10:00

Once upon a time a management accountant (thankyou Amanda) showed me how to do this and I keep forgetting what it's called so I figure I should blog it so I don't forget and everyone else can add it to their bag of tricks ...

1) Select the range of cells that contains the text values. The range can be any number of rows tall, but no more than one column wide.
Note: There must be one or more blank columns to the right of the selected column or the data to the right of the selected column will be overwritten.

2) On the Data menu, click Text to Columns.
Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

And that's how you split the accounting flexfield by it's delimiter when you're in a hurry and can't write a custom report :)

Have a great day!

Manual Depreciation Override in Assets

2006-04-13T09:34:00.000+10:00

Latest sexy thing I came across:

Oracle Assets mini-pack 11i.FA.K (2398923) contains the Manual Depreciation Override functionality.

In a nutshell:
- set the profile option FA: Enable Depreciation Override to Yes
- write pretty sql to populate fa_deprn_override with a record for each asset per period per book where the depreciation calculation should be overriden with your magically calculated number

and you're done.. no more frigging around with unplanned depreciation after the event to try and get the accumulated depreciation and depreciation expense to the desired value. How simple is that? I'll let you know if it actually works ;)

Eternal Mysteries of iProcurement

2006-04-10T15:49:00.000+10:00

Okay - I confess - I forgot to update this blog thingie ... again.... :)

My latest craze - iProcurement on 11.5.10, haven't done this one since 11.5.8 so it's a bit sexier and yet again the screens have changed - woohoo!

New things I like:
- Non-Catalog templates
- Using AME for Requisition Approval

New things I dislike:
- The new homepage isn't as easy as the 11.5.8 one
- Personalization just gets more complicated every release

Old things they still haven't fixed:
- Trying to populate the AFF is like trying to pull teeth, that saved list of top combinations is just rubbish, you still have to customise the Account Generator to get the right combination first time or the users are stuffed - it doen's remember the segments that it was able to generate so they start again from scratch. Marvellous if they happen to work for the finance department and know their full accounting flexfield, pain in the neck if they're a normal human who has no clue! (End of rant)

iExpenses wishlist

2005-11-10T11:50:00.000+11:00

Okay, here is my wishlist for today:
- When the CCID generated by the expense claim line is invalid it should do something useful at the time of entry, not wait for the expense claim to be submitted then notify the system administrator;
- The wording of the notifications should be easier to modify :) I hate workflow some days.

and the big one....

- Employees need to be able to pick the travel advance (prepayment) to apply against their expense claim!!!

And yep, I'm implementing iExpenses again. Off to sunny Melbourne next week to talk to someone about... you guessed it.... iExpenses. Plus off down the road to help another client with yet more iExpenses. The best part - all on different patch levels ;)

iExpenses... things I learnt this week

2005-07-26T15:34:00.000+10:00

On 11.5.10 you can...
- display any or all of the AFF segments
- elect a percentage or otherwise of expense reports to be audited for receipts by Payables staff
- credit card charges from hotels can be itemised automatically in the upload
- policies (whilst great functionality) are a bit of a pain when trying to demo
- we can finally dispute credit card transactions on credit cards instead of just on P-Cards
- the credit card audit screen is a pain - can I go back to using the payables screen please? :)

Have a great day!

The Post Acquisition World.....

2005-07-21T14:31:00.000+10:00

Read an interesting article in the Australian CIO which raises a few points on the brave new world (post PeopleSoft acquisition) of ERP. Interesting. It might just be that the next few years are the time for everyone to sit back and have a bit of a strategic think about the world of ERP and effective strategies for managing it....

Personalisation makes it to the forms-based apps

2005-06-26T21:35:00.000+10:00

Okay, so anyone who's been subjected to my ravings at OAUG conferences knows what a big fan I am of not customising..... hold onto your hats because the personalisation functionality from the self-service web apps has a new cousin in 11.5.10 - personalisation of the forms based screens! Move over folder tools and custom.pll, now we're really cooking with gas..... Ramakrishna Goud has put together a nice white paper on it. It's definately not in 11.5.9 - maybe I can find a patch on metalink to back-port it? They're just making 11.5.10 more and more worth the hassle every day aren't they... I think it's a plot :)

Took an insane quiz and apparently I'm a caramel - who knew?

2005-06-13T18:35:00.000+10:00

This blog just wouldn't be complete unless I took some insane quiz and bored you all with the results....

You're a Caramel!! You are known for your
sweetness. You are comfortable with yourself,
and help others feel the same way about
themselves. You are generally friendly to
everyone, and believe in second chances.

Which kind of candy are you?

And the nutty part... that's actually quite true, and not such a bad thing I reckon, since I happen to be a consultant for a living ;)

Name 5 integration Points between the Oracle eBusiness Suite and the Desktop.... go!

2005-06-12T13:04:00.000+10:00

Here's my ten:
1) j-Initiator launch
2) workflow and mail client (as in notifications)
3) export to excel (as in (N) File > Export)
4) attachments - loading and accessing
5) web ADI - create a spreadsheet
6) upload a journal from a web ADI spreadsheet
7) export to printer (as in (N) File > Print)
8) define and upload rates in the new currency rates manager (aka another clown suit for Web ADI)
9) every single fun thing in Client-Server ADI
10) ditto for Discoverer ;)

Don't you love sociability testing with multiple desktop SOEs?

Fixed Blog Comments Settings

2005-06-01T06:43:00.000+10:00

Have fixed the comments settings to allow anonymous comments (thanks Warwick) - all right, I admit it, I didn't know how to do that.... I love learning to do these things on an as-needs basis ;) I wonder if there's anything else I should do... hmmm... (other than get on with my day... it's almost 7am!).

Dynamic Inserts - always, sometimes or never?

2005-05-30T20:17:00.000+10:00

Okay - so I used to be in the camp of "Always dynamic insert, no questions asked". I've mellowed in my old age... okay - I admit it - the clients have convinced me that sometimes the cross-validation rules just can't be defined well enough to stop rubbish combinations occuring (especially when their chart of accounts hasn't even got a passing acquaintence with ranging values) and that for some organisations it's just a better business fit to turn dynamic inserts off after the conversion load and just wear that 'bedding down' pain.... anyone with me here? Or am I alone in the wilderness? :)

Public Access Release 11i Environments!?!

2005-05-27T15:29:00.000+10:00

Am I the only one who didn't know that Solution Beacon provide public access release 11i environments?? 11.5.10, 11.5.9 and 11.5.8 - all you have to do apparently is fill in your email addy and name on the form and they send you a username and password - how cool is that?? (And no I don't work for those people - never met them actually, only know of them because they co-authored a book with Barbara Matthews who is a god-like-being in the land of Apps DBAs - I have her first book about the administering the apps aka How to Herd Cats :) ).

AusOUG National Conference Series

2005-05-27T12:49:00.000+10:00

Well the paper submissions for the AusOUG National Conference Series close on the 31st of May and it looks like I won't be making it again this year - last year I was too ill when the lovely AusOUG people asked me to speak (don't you hate that) - this year apparently I can't be spared from client work ... bugger! And you know how fond I am of my own voice! Maybe next year - the Gods only know if the OAUG will be running an Asia-Pacific conference this year - the one in Melbourne last year was a rather large dud so I won't be holding my breath!

So here we are then!

2005-05-25T13:07:00.000+10:00

Okay - I'm submitting to the calls of narcissism and writing a blog. For those who are actually interested in the Oracle EBusiness Suite it may prove mildly entertaining, for all normal people I apologise in advance and suggest you set your alarm to wake you in about half an hour.

This week's grand adventure - had to work out what on earth is in the latest release of Report Manager and get a straight answer on "is this really available or just vapourware"? - the answer, for those who care is.... yes, it's there, it's a patch on 11.5.10, prereqs include a patch to the framework so not for the fainthearted but it does deliver actually the long promised Request Centre functionality from desktop ADI in the web-enabled format.... now if they can just get around to doing a GL Budget Upload in Web ADI we can throw the client-server ADI install CDs on a ceremonial bonfire! :)

Have a great day!