Fusion HCM

The radio silence of Oracle around Fusion is over. Last week a webcast explained the Next Generation of Hr software in the cloud.

If the Easter tea leaves are right, Fusion HCM will be available within a few weeks. Fusion Talent management will be Saas-ed by Oracle.

mission accomplished : apex on apps

Years ago we developed a solution to integrate Apex with Oracle's eBusiness suite.

It worked fine on 11i, and we came with a jsp solution for some of our initial R12 customers.

We had sometimes the "Apex is not supported by Oracle for the EBusiness Suite" discussion.

On the Apex site you can now read the "official" white paper how to integrate Apex on Apps. You should be on a recent 12.1.3, and it requires some patching.

The paper is introduced on David Peake's blog, Apex product manager.

I will blog how it works out, the next time we gonna use the official integrator.

Upgrade stories : Web Adi again

We had a nasty web ADI issue on a few of the upgraded instances. It worked before, it works on some other R12 instances, but we are not able to make it working again.

It throws all the time "Mail Merge Aborted due to error in creating the Mail Merge Data Source". Nothing in the log files.

Escalated the P2, but no reactions from support side. After some background fighting, a senior support analyst jumped in, and the next day it worked.

The $BNE_TOP/sql/BNENLINS.sql script fixed some NLS data issues in the custom integrator, et voila.

Upgrade stories : Apex integration

The security model in 12.1.3 is a little bit more picky, so we had to add a few more configuration steps to made the secured HR views working again. Without these 2 steps, the secured views returned 0 rows.

First, we registered the APEX_PUBLIC_USER in eBS via the regular Forms, and then a little update was needed to make it working.

update fnd_oracle_userid a
set a.read_only_flag = 'U'
where a.oracle_username = 'APEX_PUBLIC_USER';

No electricity, but the application is up

Dakar is having more troubles then normal with electricity. Never thought that the SaaS model would be that useful when the light goes out. And the extra battery in the office dies, and the router goes down. But you still can access the application over your smartphone and finish your payroll run.

And hope you can charge your phone's battery some time in the evening ...

Payroll 13th month

A monthly payroll has 12 periods, but a customer requested a 13th run, so that all corrections made over 2010 would still be processed in 2010.

By using the standard processes a little bit smarter, we achieved the goal.

Retropay was launched so that the differences were created in December again.

The Payroll ran again for December with an element set, that contained only retro element types. So only the differences found by Retropay were processed by the second December run.

And just a tweak of the (custom) payslip, so that only run results of that latest run were shown, completed the picture.

And the post-payroll processes pick up all results of both December runs.

Upgrade stories : Bursting

The parameters of the concurrent "XML Publisher Report Bursting Program" have changed between 11i and R12. Oracle added a new parameter, before the previously defined 2 parameters.

The new and hidden parameter is called "Dummy for Data Security", and is defaulted by "select xdo_cp_data_security_pkg.get_concurrent_request_ids from dual".

Upgrade stories : Web Adi error

Launching Web ADI from HR forms throws a http 404 error under Internet Explorer. Firefox shows a more meaningful "Resource /OA_HTML/oracle.apps.bne.webui.BneApplicationService not found on this server".

After some digging, it was found that the web html call of seeded and custom Web ADI forms functions had 'oracle.apps.bne.webui.' before 'BneApplicationService'.

It looks like the upgrade scripts do not clean that up correctly.

Upgrade stories : 12.1.2 or 12.1.3

So which version to choose from?

12.1.2 is out for a while, so a nice list of fixes exists already.

12.1.3 is new on the block from August 2010 on. Going for this version brings you to the latest and finest verion of the eBusiness Suite. And will fix some previous bugs. And will introduce some new bugs.

To make things a little more complicated, Metalink states:

"R12.HR_PF.B.Delta.3 (9114911) - Oracle HRMS RUP 3: This is the lowest level of HRMS Release Update Pack (RUP) on which Netherlands Payroll customers are supported for 2010/11 Year End processing and statutory updates effective from 01-Jan-2011."

And this Rup 3 is already included in the 12.1.3 version, but is an extra Rup to install on top of 12.1.2.

I gonna propose 12.1.3. Either way we will have issues to fix, so it seems better to fix them in the latest version. As such, the customer is one relaese point higher, and support can not ask you to upgrade to 12.1.3.

Upgrade stories : Extended support for 11.5.10

Plan is to blog some articles about ongoing R12 upgrades, within the HCM domain.

If you are on 11.5.10 right now :
- Premier support ends by November 2010
- you are forced into Extended support from December 2010 on
- Oracle does not bill extra money for the extended support for the first year
- So you pay extra money for Extended support from December 2011 on

You can avoid the extra money, if
- you talk to your account manager
- Oracle revises their extra money support policy, or extends the grace period
- you upgrade to R12 (which will cost you also money)

Count your options.

Retropay by Employee

I'm posting the last time a lot about Apex. While in reality I'm mainly busy with Oracle Payroll.

If your legislation uses advanced or enhanced retropay, there is the RetroNotifications process to run, to identify which employees have retrospective changes.

And in a next step, you run the RetroPay process. The enhanced version does not have any assignment set parameter anymore. In other words, the people selected by RetroNotifications will be processed by Retropay.

We have now a customer that wants to see the effect of changes in the past as soon as possible on a payslip.

So we engineered a RetroPay by Employee process, on top of the standard Retropay process. It forces an assignment into retropay, runs retropay for all assignments of an employee (yes, multi-assignments), and runs the payroll for all these assignments. All in one process.

It is now part of our Popay Payroll Toolkit.

Apex 4.0

Oracle will never confirm this, but I guess Apex 4.0 will see the light before the end of the worldcup.

This could be understood in between the lines of what was told at the OBUG Apex SIG day in Belgium yesterday.

The feature list looks impressive, and with all demonstrations seen yesterday, I have a way better understanding of the way forward.

But I'm still wondering how to use Websheets in an eBusiness suite context.

And by the way: how to integrate Apex with a version control system is still an open question for me.

Apex on R12 - patch 7377023

This patch brings back the good old SSWA plsql menu functions on R12. And in more recent versions of R12, they are part of the base install.

That does not mean that the eBS team supports mod_plsql. No. But it gives more possibilities to integrate Apex on Apps.

Again:
- Oracle supports Apex
- Oracle supports mod plsql
- Oracle does not support customizations
- Oracle eBS does not support customizations
- Oracle eBS does not support mod plsql anymore
- Oracle eBS does not support Apex applications

So bottomline: Apex on Apps in R12 is supported within Oracle, but not by the eBS team.

The readme of the patch says :

Mod_plsql technology is supported by Oracle Server Technologies Support teams.
Oracle E-Business Suite Support and Development is unable to provide
instructions or diagnostic assistance on any issues that arise from custom
development using mod_plsql.

Fusion on the iPad?

Too many posts these days about the iPad, so one more will not make the difference.

The last (and only) demo I saw from Fusion looked a lot like Flash. But there is not a lot of love lost between Apple and Flash. Meaning that Oracle has still to release their next gen platform, but will not be able to use the next gen client device.

Apex 4.0 Release date

Patrick Wolf shows again a good reason why we should look forward for the 4.0 release of Apex. Cascading LOVs for dummies.

But one little phrase makes me think that the 4.0 production release is not yet for tomorrow. "(Note: Popup LOV and List Manager will be added as well in the final product)". So not yet code complete ...

Busy busy busy

3 customers are live this month with payroll in the Netherlands. So i hope you understand that there was not that much time to maintain this blog.

So 14000 people more receive an Oracle payslip this month.

The errors we correct next month with Retropay.

NL Daily tax tables

Two NL Payroll customers ask in the same week if one can activate the NL daily taxation tables. Coincidence.

While i answered the first time NO, except when an employee is hired or fired in the middle of the month, the second time i dived a little deeper in the seeded fast formulas and plsql functions, to discover a hidden fast formula to configure.

Define the fast formula NL_OVERRIDE_SOCIAL_INSURANCE_DAYS with the business logic required, and you have full control over the proration (daily tax) happening.

Apps - Apex on R12

We just got the Apps-Apex integration working on R12.

Yes you need a separate Apache server, but the rest is almost the same. Nothing has to be changed within the Apex application, you still set the VPD call to initialize the apps session.

Only the menu function to launch Apex from is changed from a plsql based one, into a jsp menu function. So yes, an extra jsp page needs to be compiled on the Apps j2ee server.

Exit 2.0 & SOA

The cloud is now the buzzword. SOA and Web 2.0 are losing popularity.

I tried also to translate Web 2.0 concepts into HR processes, but no luck. It looked good on the powerpoint, but not on the workfloor.

Oracle Fusion development started within the SOA and 2.0 hype period. Curious how marketing will convert that into the buzzword popular at the time Fusion will pop up.

Good things is that marketing has still a few months to come up with a twist.

One more customer on R12

One of our HR customers in the Netherlands upgraded to R12 last weekend. It was mainly a technical DBA exercise. Operating system became linux, the database became a full 10 and the upgrade scripts pumped the eBusiness Suite up to the 12.0.6 level.

Release 12.1: Oracle Payroll for India

I never saw a functional business driver to migrate towards the 12 Release. R12.1 should overcome that. I hope. I hoped. For payroll, only some stuff for our India colleagues is present.

For some more details in the other HR modules, check the RCD doc.

back to basics

We wanted to extend the Dutch localization so that the mandatory assignment extra info fields where defaulted automatically when entering a new employee.

First step was to cook up some plsql, based on api's, that creates the assignment extra infos. Easy.

Now we only need to fire that code when they create a new employee.

So a user hook would be nice, but since the assignment screen is still not using an api, that was not an option.

Ok, so let's go dynamic triggers, which in this case corresponds to a real database trigger.

Oops, mutating table errors. Oops, cannot perform rollback/commit in a trigger (hidden in the api calls).

Ok, so let's transform the original piece of plsql into an autonomous transaction.

Oops, no data found. Sure, since it operates as a separate transaction, it does not yet sees the assignment that fired the trigger.

Finally we changed the dynamic trigger into an Update trigger, and pieces felt together.

Apps doesn't like the Apex datepicker

The default DatePicker of Apex was throwing a security error when we were testing our Apex application. We had to pass a user/password for a call on the wwv_flow_utilities package.

Just registering that package as secure plsql as System Administrator in Apps solved the issue (table: fnd_enabled_plsql).

But when we changed the month in the DatePicker, the same error popped up again.

Seems Apex adds the schema name to the package call, and as such the secured plsql scan can not handle the call.

So we were forced to go for a custom DatePicker.

A pity we can not wait for Apex 4.0 were jQuery will be included by default. Demostuff.

Use the Organization on the User's Assignment(s) as the Top Oganization

We ran in a small HR security issue, where it seems that future hires are not visible for a manager, when using a security profile that lists all employees in and under the manager's organization.

When digging into the Oracle code, we understood why.

Please enjoy the comments and the trunc( sysdate) piece of magic, found in the hr_security_internal package.

FUNCTION get_effective_date RETURN DATE
IS
BEGIN
--
-- Default the effective date to sysdate. This could potentially
-- be used in the future to set the effective date to the
-- date-track effective date, but as it stands now, security
-- is also determined as of sysdate.
--

RETURN trunc(sysdate);

END get_effective_date;

Apex on Apps

The Apex statement of direction mentions for the 4.0 version a j2ee Apex listener, as an alternative for mod/plsql, and additional Authentication schemes.

Knowing that R12 doesn't support anymore mod plsql, i read in the tea leaves that the 4.0 release will add an Apps EBS Authentication scheme connecting via the new listener.

In the mean time, some more posts will follow that describe how we integrated Apex with Oracle's eBusiness suite.

Documentool for Forms personalizations

Tired of creating your setup documentation by copy/paste? Or via print screens? Me too. 

So we added a new module to the Documentool family. Payroll, Hr, Flexfields and Alerts welcome Forms Personalisations. 

Documentool for Forms Personalisations is now in beta, and you can participate in the beta program by simply sending a mail request to documentool@popay.be.

Some people asked also for a solution to generate documentation for Framework Personalizations. Thanks for the suggestion.

Lasagna with french fries, welcome in the Netherlands

Oracle NL invited me to conduct a 2 days Fast Formula training, so far so good.

Found a nice family run hotel nearby, so far so good.

Went to a restaurant nearby, by far no good. Ordered tuna, got Lasagna. With French Fries. Skipped the menu dessert, had to pay the whole bill.

It's really not the first time I'm in the Netherlands, but this was an Olympic top, by far.

Documentool - JavaScript - Chrome

The html output of Documentool renders a tree via Java Script. A little slow sometimes for huge trees, really slow on Internet Explorer 6.

So i was curious to see how Chrome would do, and was simply blown away. The new JS implementation does make a difference. And this is only a start, knowing that Firefox and Safari also have new stuff on their way. Microsoft has to follow. Browser War 2.0.

But why i see on the workfloor only IE6?

One click slip, part II

The payslip solution we developed over here is a concurrent program based on BI Publisher. So adding an optional PROFORMA watermark in the rtf template was not that hard with the latest Word in place. Also, we show the Pay Advice Message from the Quickpay form on the One Click Slip.

Data security in the streets of Dakar

I spent 2 weeks in Senegal. One week of holidays in the South, Casamance, and one week work on an internal Popay project.

We ordered a take away sandwich, and great was my surprise that the sandwich was wrapped within printed paper. My first reflex was about health.

Then i had a closer look into the paper, and i saw a listing, dating from September 2002.

An old style print, listing bank account numbers, employee names, employee numbers, net amount.

Doudou Sonko, 114816 cfa net à payer, based in Ziguinchor. The relaxed, quiet town we were one week ago. How twisted can a souvenir be for a payroll consultant.

Why Apex is there to stay

Some people asked me recently how sure it is Oracle will continue support and developement of Apex. Never say never, but the fact that it comes pre-installed with XE and the 11g database tells already something.

But the most striking fact for me is that Oracle development uses Apex themselves as part of their database solutions. Oracle Vault, the build-in auditing tool, uses Apex as reporting layer. Just have a look into the online documentation. Interactive reports, someone?

One click slip

We just implemented a solution, based on Forms Personalizations, so you can launch a payslip for a given employee and given period, by one single click.

The Forms Personalization adds an entry to the menu, and starts two more actions. The first action launches a plsql program that starts the actual concurrent program of the payslip, stealing the parameters from the Quickpay form or the Assignment Actions form.

The second action launches an url that opens the pdf payslip in the browser. The url is generated via plsql, and i blogged about that some time ago.

ohug blog silence

OHUG is on the final day, and i was not attending this year. I expected to see a lot of blogs talking about it, but silence as far as I can see euh hear. This tells a lot about the 2.0-ness of the people attending and expecting in the feature set of their HCM solution.

And what about Fusion and ready to market. Did I used the word silence already?

By the way, holistic talent management was the talk of the town.

Launch an Apex page from your forms

This article describes the steps to launch an html page from an Apps form, using Forms Personalizations. In this example, the html is an Apex (Application Express) page.

The first step is to navigate into the form from which you want to launch an html page. Then you navigate further via the menu ::Help::Diagnostics::Custom::Forms Personalizations.

Create an additional menu entry, as shown:



and specify for example Special16 as the menu entry.



Then define the real action, linked to this menu entry:



and define the html call



The call here is dynamic, since it starts with the equal (=) sign.

= 'http://apps12.popay.be:7777/pls/apex/f?'
|| 'p=103:8:::NO::P8_PERSON_ID:'
|| :PERSON.PERSON_ID

The :PERSON reference is filled in from the forms variables and block, which you can examine.

If you want to try first with an easier, static html call example, then simply put there

http://www.popay.be

An other example of an url (via mod plsql) is:

fnd_profile.value( 'APPS_WEB_AGENT')
|| '/popay_htp_hr_org?p_org_id='
|| :HOU1.ORGANIZATION_ID

Quit the original form, enter the form again, and you should see the new meny entry:



You are now one click away from the html page.



This article is not exploring how the create the Apex page, how to setup the Apex Apps security integration, and so forth, and will be nice topics for the future.

calling balances via plsql in fast formula

We all know you can call plsql functions within fast formula. The way to go when it becomes really complicated.

When you need the last 9 months of a particular balance (a running total), you can fix that by calling the seeded pay_balance_pkg.get_value plsql function.

You can even use it to retrieve balance values from the actual period. But in that case you sometimes run into the issue that the data is still in memory of the payroll engine, and not yet flushed into the Oracle tables. So the balance call returns zero, while you know very well it is not.

A trick to force the engine to flush the data (and to guarantee that he balance function call comes with the right values) is to call a YTD balance just before your function call. Since the YTD spans multiple periods, the engine realizes he needs database access, and flushes his memory structures.

RUP 5 for Hrms

2300 fixes for Hrms available right now on Release 12.

Since the Hrms functionalities of 11i and R12 do not differ that much, i wonder when these fixes will be downgraded.

Will they come back as the July pack we were getting used too, or will oracle skip 11i on this, to push all of us gently towards the 12 version? Apps unlimited or limited Apps?

Metalink down, planned

I had to read that twice, and checked if it was not planned on the first of April. But no, Metalink will go dark for a few hours. Oracle the high availability database. And anyone knows when Google plans the upgrade their search site?

Fusion = Apps 13

This article by Tim Dexter of BI Publisher name and fame just tells in between the lines that all eBusiness Suite reports will be ported to Fusion. What is in fact the same as saying that Fusion is Apps 13, an evolution from R12, not a new game.

So the good old per_all_assignments_f will still exists. Maybe with a good looking UI on top of it, which passed for the first time an usability lab. Skip the last part, I'm asking too much.

SOA only matters at the back end side, not at the front end, despite the Web 2.0 vague.

My Virtual Apps

Since a few days I run APPS via a VMWARE virtual machine on my little laptop. Vision 11.5.10. And all data on an external disk.

With 1G of memory it is slow, but not too slow. Anyway, I ordered already an extra Giga RAM.

Summary till now: i love it, but it needs to be seen how handy this setup will be when i have to restore the virtual machine, a heafty 40G of unzipping.

Next step is to run Documentool on it. And publish the output. But we discovered some data corruptions in the Vision database, which needs to be worked around in Documentool.

I expected these corruptions. You see them on any project, and the Vision database is a very old, ongoing project.

Oracle Fusion Capable

Documentool is Oracle Fusion Capable.

Do you need an additional save harbor statement?

A license for one LOV?

The very excited people contact form has two fields to store the begin and end reason of a contact relationship.

These lists of values are standard empty, but when you create some Personal life events, they show up nicely in the relationship reason fields.

Too bad that both List of Values show identical reasons. A divorce is not the most straightforward reason to start a relationship. Or maybe it is, but then in the other way.

Even worse is that you need a license for OAB (Oracle Advanced Benefits) to create these personal life events. So far the integrated solution.

Forms personalizations can eventually overcome both issues.

Distributed costing, again

First of all, the input value over which you want to distribute must be Pay Value. Any other name will not do, even if the uom is Money.

And it seems that distribution over distribution seems to work (by accident, or by purpose?). When setting up the element link for distributed costing, it will tell you the element is not eligible for distributed costing since it is part of a distributed element set.

Remove it from ther element set, configure the element link, and add it back to the element set.

Desupport of 11.5.9

As you can read on Schan's excellent blog , 11.5.9 Premier Support ends by June 2008. This is almost tomorrow. There is no Extended Support offered, but Sustaining Support is there to stay.

By the way, Sustaining Support is not giving you any new updates.

So if you are on 11.5.9 with a localized payroll, you simply need these patches and fixes, due to statutory legislative changes. So Sustaining Support is not an option. So you have to upgrade to 11.5.10. By tomorrow.

my fast formula editor

I used several years the free ConText editor to edit fast formulas. Recently Paolo suggested me Notepad++, which is open source, and does a great job in syntax highlighting and brace matching.

I used it today for the whole day, and even when i have to get used to shortcuts and menu options, i would not be surprised to stick with it.

By the way, i used the sql language as syntax style.

Oracle acquires BEA

The first thought is that this is a pure middleware play.

Buy installed base and generate support revenue.

Or has it to do something with Fusion Apps. I do not think so. The architecture is defined right now, and BEA will only play a role later on in the life cycle of Fusion Apps. If any.

But the hidden gem could be Tuxedo. Oracle wants to position itself in the primary processes. Think banking processes for banks. And these transactions are handled by Tuxedo, already for years.

And there is the big money. Compared to SAP that hunts for the little cents with Business By Design.

Inovation. Nope. Smart. Yes.

Any link with HR. Nope.

HR 2.0

Every HR implementation tries to structure all kinds of employee related data within the venders shoebox. We just make it fit, but a lot of data and processes are not structured at all.

Why not load all employees within a social network, tag them with competences, positions, domains of interest, ... from the corporate HR system? And then let the employees connect to their peers, discuss issues, create communities within the enterprise, learn from collegues the other side of the world. Free flow versus structure.

From a technical point of view, it is easy. But which organisation will be ready to give this a try?

gross up rounding error

One of the outputs generated by the gross up formula is the remainder. We noticed that we missed a few eurocents in costing, due to these remainders.

First we set the "to within" input value to 1 eurocent, but gross up still was coming up with a few remainders of one cent.

And we could not decrease the value of "to within", since the unit of measure was money.

So we changed the unit of measure to Number, filled 0.009 as the "to within" tollerance, and grossup did a perfect job, leaving 0 as remainder. We still have to see if this perfect job has no performance consequences.

We opted not to create an additional element to store the remainder, and cost it.

Gross up, out of the box

This article describes the configuration steps for the grossup or gross to net functionality. The seeded fast formulas are used, but you could implement your own variations on them.

1. define an element type.

• In the Advance tab, set Gross up (which activates the iterative flag and the process seperate)
• define input values, based on the inputs of the seeded fast formula default_grossup.

• Pay Value
• Amount
• Additional amount
• Low gross
• High gross
• Remainder
• To within
• Method (defaulted to interpolation)
  
• Grossup balance

• map the input values to the inputs of the fast formula (which is in this case a 1-1) via the iterative rules button.
  


• (optional) exclude grossup balances, via the exclude balances button. Only those balances that are defined as grossup show up in the list to be excluded.

2. Link the seeded fast formula calc_grossup_pay_value to the element type created above. The output of that fast formula (payment_amount) must be linked directly to the pay value.

3. Define an element link, and happy testing.

Yes you can change the names of the input values, since a mapping is foreseen by the payroll engine, and also the fast formulas can be used as a template for your custom solution, although i would not advise that.

Grossup only works from an element entry, so you can not activate it from an indirect run result.

result name	type	input value	severity
ADDITIONAL_AMOUNT	Adjust	Additional amount	.
CHANGER	Adjust	Changer	.
GROSSUP_BALANCE	Adjust	Grossup balance	.
HIGH_GROSS	Adjust	High gross	.
LOW_GROSS	Adjust	Low gross	.
MESG	Message	_	Information
REMAINDER	Adjust	Remainder	.
STOPPER	Stop	_	.

BEE Spreadsheet Interface

We wanted to load BEE batch element lines via Excel. And we remembered somehow it was described as new standard functionality a few family packs ago.

So just search metalink and there we go. At least, that was the plan. Metalink came with a lot of results, pointing to web ADI, error messages and how to work around them, but a simple doc that describes how to activate the functionality was not found.

I guess Google set the search standards too high.

But ok, a new try the next day on some slightly different keywords, and all of a sudden "BEE Spreadsheet Interface" docs showed up within the top 40 list. So that was the magic search string.

And from that moment on we found some docs, and one hour later a first xls (sorry spreadsheet) was loaded.

Before you ask it: note 341427.1 did the trick.

apps password

today the customer asked to work out a fairly easy forms personalisation. Trouble was that we were not getting the apps password on the development instance. Some pressure, some extra decibles, and we were delighted to receive the very secret apps password.

By the way, on production the password is simply apps. Go figure out.

Documentool for Alerts

In the previous post i mentionned the Belgian summer. Sorry about that.

We just created a Documentool for Alerts. So if you are in need to document all your alerts, leave a comment over here, and i'll send you the code.

Discussions are ongoing, but we think to make it freeware, as Documentool for Flexfields.

HRMS Release 11i Family Pack K Rollup 2

The summer is almost there (here in Belgium), and a new family pack is coming. Sorry, roll up.

One of the nice new payroll core features is the possibility to handle payments to foreign bank accounts. Dev made it now possible to select organizational payment methods from an other legislation then the one of your business group.

And i have the impression this is only implemented in framework, so i wonder how the form will behave.

Oracle in the blogging community: they don't get it yet

In the eBusiness suite corner, i'm not aware of good blogs from Oracle, except the tech stack blog of shan.

With a good blog, i mean a blog where personal opinions are expressed in an open, transparent way, by key players. For that to happen, Oracle is way too closed and hierarchical. Marketing and legal are close followers of all expressions.

First thing to happen is an internal cultural shift to openess. Inviting the blog community to their events can help that transformation to happen. I'm sure that the top bloggers will not all follow the official marketing strategy and message, and will post critical questions. If Oracle internals will then reply, an open communication and exchange can start.

But this will take time, and i'm not sure if Oracle wants to open the windows when the Fusion story needs to be told. A single marketing message is then so important, and the fusion message will not be easy to communicate anyway.

applications unlimited

From a marketing point of view, again, genious. Oracle will support your solution all over the time, if you pay for it.

But the pieces below will be desupported anyway by other suppliers, so you are forced by others to upgrade.

I'm afraid to raise a SR

Disclaimer: i work sometimes directly for Oracle support, so receive money from them.

But on almost every SR i have to pleasure to work on, they drive me nuts. To bring the status of an SR asap back to the customer, they paste in the most fanstatic texts. Or ask for whatever diagnostic information. And ask first for the traces that have nothing to do with the problem itself, but take ages to generate. Or ask for versions, even when you pasted it in already 3 times.

But ok, that is the first layer of analysts you should try to go through. It takes time, but once you now their top 10 of preferred useless info, you can prepare yourself pretty well. Unless you do not have the time (P1).

It becomes more dangereous when they suggest out of context recommendations, that can make the problem worse, or can have impact on other modules. But if you explain why you think it is not a good idea to perform a step, and thus refuse to implement their recommendation, they will not proceed in handling the SR. Catch 22, or better, deadlock.

It would help if all of these analysts had some on-site experience. For all the money customers pay for support from Oracle, this is not asking too much.

Before i end this flow of thoughts, let me stress too that some analysts simply do their job very well, and just play their role as intermediate between customer and development.

After a while, you start knowing them by name. One hint, if your SR is randomly assigned to one of these that think that collecting 10 Giga of data is necessary before contacting dev, try to escalate untill a knowledgable analyst takes over.

retropay, i hate it, i love it

I scanned my blog for retropay. I found back a post of september 2003. At that time we worked out in the Netherlands a solution around reversals. Standard retropay was not an option at that time, since the specifications of the customer were too specific, volumes were huge, and the NL localisation and core solution were not yet 100% finished.

Q: Would i do it again, more then 3 years later?
A: Yes.

Main reason is performance. Retropay is designed to recalculate exceptions. When massive retropay is needed, you can start it very early in the payroll cycle. And run it an other time to process the last minute exceptional cases.

key flex fields

It is not the first time they ask me to "decode" a flex structure. Till now, i havent found a generic way of doing that, but the supported package fnd_flex_ext does the trick nicely for key flexfields. The function get_segs returns the concatenated display value off all segments.

Please drop me a line when you can point me to a similar function for descriptive flexfields.

a passionate user has gone

There will always be a few psychos around. But one can be enough to ruin your life.

We do not use custom.pll

Why do people still enhance the Apps forms via custom.pll coding?

Are forms personalizations not enough? Maybe, but apply the 80/20 principle, and don't code custom anymore.

Are we not shifting towards self service? So every business logic you add in custom.pll, you can do again in for those lovely web pages.

But my first try anyway is always user hooks.

Salary updates via Web ADI

The standard integrator 'HR Update Salary Proposals' is showing only seeded US salary components, the columns to change the amount or percentage by component are missing, and the headers are not meaningfull.

Showing other components then the seeded once was solved by changing the view hru_salary_proposal. This is a documented change. Searching online help or metalink by the name of the view of peupl01.v.sql will explain you. How to find these docs without knowing these details already is an other story. But Oracle is marketing now a search solution.

Adding additional columns was done by enabling hidden columns in the Web ADI layout of the 'HR Update Salary Proposals' integrator.

Note 389624.1 on CWB was helpfull in explaining how the change column names. Once the 'Web ADI - Interface Columns Integrator' was enabled and unsecured, we could download the interface and do the modifications.

But the end user was not happy with the order of the columns. With an update statement (on bne_layout_cols) we could work around that, but we have still not found a documented way to realize that. Feel free.

Release 12

I guess R12 will be the most stable release ever, since
1. there are no new functionalities in it for HCM
2. the technology stack is simplified

(1) It seems all new functionalites have been released already on previous 11i family packs and RUPs. I only found some changes on the new financial legal entities, operating units and business groups (based on hr security profiles!) and checklist Self Service functionality.

(2) No 9 database anymore. Only a few Oracle Homes. Fusion middleware.

apologies

I was not realizing it was so long ago i was not blogging. How do you find time to do it anyway?

I was very busy the last months, so sorry, but i'm afraid the next months will be even worse.

Some things i was working on, since the last post, so far i remember:
- serving a payroll customer in the Netherlands having issue after issue. There is light at the end of the tunnel.
- Implementing a payroll upgrade from 11.0.3 to 11.5.10. Nasty NLS issues, and the volume test was challenging.
- Self service implementation (with AME).
- upgrading Documentool
- creating an extention on the Dutch payroll for foreign payments (bic/iban/btl91)
- Compensation Workbench. Oracle using their own OAB framawork.

element sets within Payroll

Is it possible to prevent users from updating particular element entries? Well, today i learned that a profile option can be set to specify an element set which defines the 'do not touch me' element types.

An overview of the different types of element sets:

Customization set, for element entries

• Limit the elements that can be seen on a configured version of the Element Entries window
• List the elements to be entered for assignments using BEE
• Prevent users updating entry values in the Element Entries form for a set of elements. (set the element set in the profile option HR:Non-Updateable Element Set)

Run set, for payroll processing

• Use a run set to specify the elements to be processed in a payroll run.

Distribution set, for costing

• Use a distribution set to define the elements over which the costs of other elements are to be distributed.

Bye bye family packs, welcome Rollups

Oracle released the first rollup patch for Hrms.

One of the new features is the FastFormula Assistant. She let's you

• choose multiple FastFormulas to compile
• show line numbers for formula text to locate easily errors
• upload/download a formula for integration with your favorite text editor
• generate FastFormula text templates

fusion = Ebusiness Suite 12

I read a blarticle about fusion that is not just repeating the messages oracle spreads, but is trying to read in between the lines. And funny.

Calculate absence duration

When you create the fast formula BG_ABSENCE_DURATION (probably a copy from TEMPLATE_ABSENCE_DURATION), this formula is used to calculate the duration of the absences entered in your business group (BG). What's in a name.

Good to known is that this formula can have as extra parameters

• assignment_id (context)
• element_type_id (context)
• absence_attendance_type_id

The last one allows you to implement calculation rules per absence type.

Online Sql Trace

When you have long running processes (like payroll PUYGEN), you do not need a sql trace file to find the top sql consumers.

Some sql statements can give you the same result.

select module, sql_hash_value, s.*
from v$session s
where module = 'PUYGEN'
order by 1, 2

select sql_text, s.executions, buffer_gets, s.*
from v$sql s
where hash_value =

select *
from v$sql_plan
where hash_value =

order by id

But this set of statements will not give you the waits.

Htmldb gives pls-00103

A customer wanted to have a decentralized application, based on the positions of Oracle Hrms.

Since self-service is non-existing for positions, we had to explore other options. At the end we opted for HtmlDb.

Installation went smooth, and the workspace was created in a snap. But when creating a first test application, we had several errors, but without error messages.

At the end we were able to see the error message, together witha piece of code. pls-00103 was popping up, together with a select in a select, dynamically generated by htmldb.

We brought the problem down to:
declare
n number;
begin
select nvl( 1, (select 2 from dual))
into n
from dual;
end;

This snippet executed fine on my laptop database (9.2.0.1) ,but not on the apps database (9.2.0.5).

We were sure we had discovered a new bug in the database software, but a lucky metalink hit brought us further.

Event 10933 mimics old plsql-sql behavior, and this event was set in our upgraded oracle apps instance.

We unset the event, and voila.

Summary: oracle apps ; htmldb ; pls-00103 ; event 103999

List all request launched via a request set

This query shows all request related to a master request with timing and dependecies.

select
level,
status_code,
(
select nvl( description, concurrent_program_name)
from fnd_concurrent_programs p
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
) prog,
trunc( ( actual_completion_date - actual_start_date) * 24 * 60, 2) minuten,
request_id,
argument_text,
priority
from fnd_concurrent_requests r
where 1 = 1
start with r.request_id = :p_top_request_id
connect by prior request_id = parent_request_id
order siblings by request_id;

See more concurrent output.

It is possible to see someone other's concurrent output, when you are linked to the same responsibility the user submitted the concurrent.

You do that by setting the profile option Concurrent:Report Access Level to Responsibility instead of the default or implicit value User.

This feature can become very handy for operational people to share each others output.

the organizational payment methods api

The update api for organizational payment methods (pay_org_payment_method_api / update_org_payment_method) shows some unpredictable effects.

In order to overcome that, i had

• to pass a null value for the p_sets_of_book_id, instead of the default value.
• to pass a value for a random pmeth_information column, instead of defaulting them all (and leave them out of the api call).

How to access concurrent output via a browser, outside apps?

The example included returns an url, which will give you the log file of request 194790. Oracle apps manages this via the fndwrr executable. The temp_id is an encrypted number, generated by the apps software.


declare
l_request_id number := 194790;
l_two_task varchar2(256);
l_gwyuid varchar2(256);
l_url varchar2(1024);
begin
select profile_option_value
into l_gwyuid
from fnd_profile_options o, fnd_profile_option_values ov
where profile_option_name = 'GWYUID'
and o.application_id = ov.application_id
and o.profile_option_id = ov.profile_option_id;
--
select profile_option_value
into l_two_task
from fnd_profile_options o, fnd_profile_option_values ov
where profile_option_name = 'TWO_TASK'
and o.application_id = ov.application_id
and o.profile_option_id = ov.profile_option_id;
--
l_url := fnd_webfile.get_url
(
file_type => fnd_webfile.request_log,
id => l_request_id,
gwyuid => l_gwyuid,
two_task => l_two_task,
expire_time => 100 -- minutes, security!.
);
dbms_output.put_line( l_url);
end;

Fast Formula Wrappers

It was only when we ran into the APP-FF-33186 error (kindly inviting us to regenerate the wrapper package) that I realized that the compiler technology behind fast formulas was changed slightly.

In order to minimize dynamic sql calls, oracle generates for each fast formula package (FFP_) now also a wrapper package, starting with FFW.

This can be seen into the code behind the ff_exec package, from familypack H on.

I cannot come up with a good reason why this wrapper packages saw the light. Performance comes in my mind, or the ability to run fast formulas directly through sql?

Run the ffgenwrap.sql script to overcome the error messages, located in $FF_TOP/patch/115/sql.

Change the employee name format in Self Service

By default, the employee's name is shown as last name, first name. You can overrule that to the full name format by setting the profile option 'HR:Display Person Name' .

Saving the output of a concurrent request on your desktop.

Often Apps is configured that way that the output of a concurrent request is opened within your browser. You can save that file from the browser, but it is possible the browser changes the content of your file.

Some steps to change that behaviour.

1. Navigate via System Administrator, Install to the Viewer Options screen.
2. Add an additional entry for TEXT, and associate a non existing mime type to it.
3. Change the profile option for 'Viewer Application for Text' for your user or responibility to the newly created option. From now on, all the concurrent programs with TEXT as output format for you or all users of the responsibility, will see the impact of your changes.
4. Open the output of a request of a TEXT concurrent program.
5. Since no mime type is associated with this extention, Windows asks you to save the file, or to open it with a specific desktop application.

The employee API fills in the background also the CRM parties table, and as a consequence generates also a lot of workflow business events.

When you do NOT use CRM, you can stop these side-actions of the employee API by setting the profile option "HZ: Execute API Callouts" to No (defaulted on Yes)

this query gives you all the Tier I loclaizations/legislations supported by Oracle core dev team:

select
nvl( territory_short_name, 'ZZ International') legislation,
(
Select 'Y'
from hr_legislation_installations li2
where li2.application_short_name = 'PAY'
and li2.legislation_code = li.legislation_code
) payroll
from hr_legislation_installations li, fnd_territories_tl t
where application_short_name = 'PER'
and legislation_code = territory_code (+)
and language (+)= 'US'
order by 2, 1

To enable full scale concurrency for inserts and updates on tables, one has to change the settings of initrans. In previous versions of Oracle this was only possible by recreating the table. In 9iR2 this can be done dynamically. Included an example:

alter table hr.hr_comments initrans 12
 
 

To find all components and version of the pro*C payroll executable (PYUGEN), run the following command on your unix flavor from the $PAY_TOP/bin directory:

strings -a PYUGEN | grep '$Header'

The seeded Retronotification Report (both normal and advanced) end up with a REP-1212 report error. The seeded print style is not wide enough.

Changing the style to US Landwide overcomes this. Note that you cannot update that field through forms, even if you copy that concurrent program.

By the way, the concurrent executes the Pro*C payroll PYUGEN, which starts the Oracle Report PAYRPRNP.rdf.

What are the database items used by a particular fast formula?

Query the ff_fdi_usages_f table to answer that question.

To start a concurrent program via the Self Service interface, add the function 'Schedule Requests' (FNDCPSRSSSWA) to your menu.

This function can be added in a pure Self Service menu, but also in a forms menu.

This function can be used as a template to start specific concurrent programs, by adding the parameters 'programApplName' and 'programName' to the html call.

Run balances are a new implemention of balance value retrieval, optimized for performance. The old style implementation is also known as run result balances.

To check if your legislation uses the run balances architecture, check the pay_legislation_rules table for the keys 'SAVE_ASG_RUN_BAL' and 'SAVE_RUN_BAL'.

Update recurring and reversal are not compatible. The reversal process inverses only run results, and does not modify the element entries (values, and the column updating_action_id).

A very old enhancement request exists on metalink...

Appraisals within the manager self service model work fine. But now I had to give the HR department read-only access to all published appraisals, excluding the appraisals of HR people.

This can be done through the creation of a security profile, that shows all employees, except the HR emps.

Next, create a new responsibility, based on the manager self service, that is linked to the security profile above. Set the profile option HR_DISPLAY_PERSON_SEARCH to Yes for the responsibility.

As such, Hr people can enter the application with the responsibility above, and they have now the possibility to search for people (and to create an individual people list) within their security profile. Once they found the employee needed, they can view the published appraisals.

The concurrent manager is working fine now. I had to recreate the network 8.0.6 environment (make -f ins_network.mk install), since the lsnrctl executable (amongst others) did not exist.

This recreation of executables gave a terrible list of link errors (undefined reference to `__ctype_b'). After a long search, I found out (www.puschitz.com) that I had to downgrade the glibc libraries to glibc-2.3.2-5.i686.rpm. Once this was done, the make finished well, and up was the concurrent manager.

I really need to control the execution order of concurrent requests. Incompatibility rules work fine, but I need further control. This could be realized through the priority of the request.

This priority can be set through the profile option "Concurrent: Request Priority" (system name: CONC_PRIORITY).

Programmatically, you can set this profile option value via a fnd_profile.put call.

Self Service Version 4 has still the old style appraisals (html generated by plsql). The good thing about it is that it is still very straightforward to tweek the output a little. A few lines of plsql code visualized also the people group in the appraisal html page.

Next problem is that the apps listener is not up. adalsctl exits with status 127. The lsnrctl executable does not exists in the 8.0.6 home.

I have to make them again. I guess something will be wrong with the gnu C compiler version.

The motif libraries were missing. This became a blocking problem when I launched the forms at client side. Frm-92050 was the very message we've got when launching a form.

Look for the missing libraries for the forms server:
ldd f60webmx | grep 'not found'

=> libXm.so.2 not found

Installing the motif libraries from the RH9 distributie. Since this version was too high, I created a symbolic link from the higer libXm to the lower one.

Installing 11.5.9 on Red Hat 9. When starting the Apache server, the libdb.so.3 library could not be found. And he is really not on the system. Extracting compat-db-3.3.11-4.i386.rpm into a tmp directory, and copying that very library into /lib overcame the issue.

For more details: http://www.linuxquestions.org/questions/showthread.php?threadid=129670

This snippet to rollback multiple payroll actions. Handy in a test environment.

for r in (
Select payroll_action_id from pay_payroll_actions
where
order by 1 desc
)
loop
py_rollback_pkg.rollback_payroll_action
(
p_payroll_action_id => r.payroll_action_id,
p_all_or_nothing => FALSE,
p_dml_mode => 'FULL'
);
end loop;

The API pay_balance_adjustment_api.create_adjustment is functionally the same as the Adjust Balance form. An element entry is created (creator type and entry type = B), non visible in the element entry screen. The creator id points to an assignment action. A payroll action (B) is created, with one assignment action, and one single run result.

Hrms FamilyPack G has been applied. From that moment on hrglobal is reporting problems on database items. Seems that there is now a new who-trigger on the ff_database_items table, but the code to decide whether to insert or to update the database item from the shadow tables uses therefore still the last_update_date. As such, every database_item is seen as a new one, so every time hrglobal tries to insert an extra row in ff_database_items, violating some constraints.

A quick fix in in the body of ff_data_dict resolved it. Now the tar.

The Formula functions (link between PlSql functions and Fast Formula) do now support output parameters. No idea since what release, but in 11 it was not working. This enhances the PlSql-FF integration significantly.

The output from hr_utility.set_location can be redirected to dbms_output. This has an upper limit of 1 Mb. Since the assignment api's generate tons of tracing ,the 1 Mb can not be enough.

In that case, you can redirect your tracing to Oracle trace files.

Hr_Utility.set_trace_options(UPPER( 'trace_dest:trace_file'));
Hr_Utility.trace_on;

We encountered some problems with the pre-payment process for third party payment methods.

By adding some trace code into the package HR_PRE_PAY, we could knock down the issue.

fnd_file.put_line( 1, 'your message');

The line above adds some lines to the log file of the concurrent request.

When you change or create a balance route, you have to recompile all fast formulas, since the route text is part of the fast formula package code.

Bulk compile of fast formulas: $FF_TOP/bin/FFXBCP apps/apps 0 Y %% %%

Oops, deleted data, and 2 hours later my report was not working anymore. And the dba was not in to restore the table.

Flashback queries saved me.

SELECT * FROM xxrs_pay_balance_redefinitions
AS OF TIMESTAMP TO_TIMESTAMP ('07012004 00:00:01','DDMMRRRR HH24: MI: SS')

The API for assignment extra information types requires that there is a line for your session in the Fnd_sessions table, since some of the queries rely on that datetrack information.