Jan 23, 2017

LOV with unicode symbols in Apex

I discovered the unistr sql function, which shows the unicode symbol of an hexadecimal input.

eg> select unistr('\2713 ') tick from dual;

As such I constructed a dynamic (sql) LOV in apex, converting the Y/N codes into a unicode symbol. See the tick symbol query above.

Linking the LOV to a reporting column (text shown as LOV) changed the Y/N values of the column in a nice symbol.

✓   You also have unicode symbols for airplanes, balloons, stars, ... Please use them wisely. ✔

Nov 14, 2016

Netsuite integration

We are working on a native plsql integration between popay.net and Netsuite. We need such for some new customers in Kenya and South Africa. The solution will be based on raw SOAP messages, all handled with plsql, including the token based authentication (TBA).

By pure coincidence, Larry opted to acquire Netsuite during the same period.

The documentation is sometimes a little though to get through it, so maybe not such a coincidence after all.

Sep 12, 2016

issue with security profiles while upgrading

TL;DR: Do not use secured view in the definition of a security profile

During a migration from 11i to R12.2 we noticed that whatever chance in the assignment screen had as side effect that the employee was not visible anymore under secured responsibilities.

So Oracle deleted all records for such employee from the per_person_lists table, but the call to renew them did nothing.

The security profiles in question contain a subquery on per_assignments_f. That was a table, long time ago, in 11i, but is now a (secured) view in R12. So replacing the secured view by the base table resolved the issue.

Feb 5, 2016


A long time I was not really active on eBS, a long time I was not blogging anymore.

I just want to document my R12.2 adventures.

One was that the fast formula text is now stored in a clob column, gone is the long column. Finally. Our Documentool for Payroll supports this little change already for a while.

Value set security. I was not able anymore to enter values for a value set. Reading several notes, praising the Security by Default approach, so that something that worked for decades involves now an enterprise level of confusion and complexity. Bitter sweet.

This article describes how to enforce backwards compatibility, by linking your user to the "Flexfield Value Set Security: All privileges" role, done under SYSADMIN / User management.

But overall impression of R12.2 is that is it very stable and solid. So a sign of an end of live technology, viva Fusion.

May 12, 2014

hr visualizations

Having great fun with HR visualizations. 9 boxes for talent management, org charts, workforce comparison charts, .. you name it.

Jun 17, 2012

Pay Value trick

When Pay Value is filled, no fast formula attached to the element type will trigger. Basic stuff.

But I learned recently that when a prorated fast formula is attached to the element type, pro ratio will kick in, and will eventually modify the Pay Value...

Quick Retropay

Oracle Payroll came with a new feature on top of 12.1.3: Quick Retropay. We had build a similar functionality ourselves before, but it is good to see we have it now out of the box.

Quick Retropay comes as a concurrent program. You specify the assignment and a date where you want the retro entries to be created. The start date is optional. The program is smart enough to find out from where to start the recalculations.

Next on my to do list is some performance profiling of the concurrent program, so that Quick means Fast.

Jan 15, 2012

Making Quickpay faster

For years we know that quickpay is not the fastest payroll process, and we pointed to the wait time of the concurrent manager.

But we had a closer look, and saw that even an empty Quickpay took always at least 5 seconds, excluding the time doing nothing in a concurrent queue. A sql trace file was generated at concurrent program level.


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      151      0.00       0.00          0          0          0           0
Execute    760      0.06       0.07          2        110        115         112
Fetch      663      0.08       0.07          0       7926          0        1317
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1574      0.16       0.16          2       8036        115        1429

Misses in library cache during parse: 1
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     837        0.00          0.00
  SQL*Net message from client                   837        5.00          5.08
  asynch descriptor resize                       13        0.00          0.00
  Disk file operations I/O                        2        0.00          0.00
  SQL*Net more data from client                   8        0.00          0.00
  SQL*Net more data to client                    17        0.00          0.00
  log file sync                                  12        0.02          0.04
  utl_file I/O                                   22        0.00          0.00
  db file sequential read                         2        0.00          0.00

The totals show clearly that the sql part needed only 0.16 seconds, while the wait event “Sql*net message from client” took in total 5.08 seconds, where there was a max wait of 5 seconds. We see this line in the raw tracefile that corresponds with that maximum wait:

WAIT #0: nam='SQL*Net message from client' ela= 5001949 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1304197240453556

That wait event indicates that the database is waiting (doing nothing) for a next sql statement to process. During that time, the client application must be busy performing other non-database, non-sql related activities. The client over here is the pyugen pro*C program, that executes the concurrent program of the Quickpay. We do not have access to the source code of that pro*C program. So it was time to call support, and a few weeks later patch 12565924 was released.