A web browser extension to automate invoice data entry

Monthly invoices come in as (text based) pdf files, and the hours worked (quantity) by hourly rate (price per unit) have to be entered manually in a 3th party web application by day.

In case such invoice contains hours worked over many days with multiple rates, the manual data entry becomes a very tedious and time consuming job.

An option for automation ..

Starting point is a pdf file, from which the tabular data needs to be extracted. On premise & cloud solutions exist that try to extract that tabular data (date, price, quantity) from a pdf file. One option is the open source tabular-java tool, which can be wrapped within a REST API-call and returns the tabular data in a JSON format. PDF in, JSON out. No promise, but this deserves a post on its own. 

But how to inject that tabular data then in the html of the 3th party web page? There comes a local Chrome web extension into play. Such an extension offers the following functionality:

1. reads a pdf file
2. calls the tabular web service
3. shows the returning data, and totals, for validation
4. has a button to push the tabular data to the 3th party web page

The latest step is done after reverse engineering the 3th party web page, so we identify the cells where the hours worked need to be entered for a given date and rate. Some JavaScript maps then the tabular data from the pdf invoice to these identifiers, and copies the data in these html-elements of the 3th party page, which effectively automates the job.

I had expected that developing a Chrome extension from scratch would be a pretty hard task, but progress went rather smooth, leveraging previous experience with HTML, CSS and more important JavaScript, together with abundant documentation from Google and available information on problems encountered by others.

One stone - 2 birds

We are upgrading Oracle Apex behind our hr/payroll popay.net solution, and in the mean time we are looking into minimizing network bandwidth.

 The last years we were rolling out many modules within popay.net, like learning, performance management, time management, etc. That focus left us on an older version of Apex. Now time to go to Apex 18..

Our solution uses a few Apex plugins. One is a syntax highlighter for the payroll smart formulas. But  the related files of the plugin were not arriving in the browser anymore with Apex 18.

The solution was rather simple. We just set the url prefix in the plugin, we copied the files from the database to the application tier, and the files were served as static files.

One stone. One bird.

Independent of this, we had still a few http-304 calls in our Apex pages, instead of http-200 calls, which are completely handled by browser cache.

A 304 is not that bad, the content is handled locally, but the browser does a ping too much, to be sure he can serve the content locally. Which can be felt for users far away from the server on a slower connection.

These requests were in fact the files of the plugins being served dynamically via the database.

So once again, setting the url prefix of the plugin, copying over the files to the application tier, transformed the call in a static file call, and the browser cache was serving these files locally.

As a result, only the apex page call ../f?p=10000:... was consuming network time, since it need at least to go to the database. All other supporting files come out of the cache, when present.

One stone. Second bird.

net pie - a payslip visualisation

I wanted to spice up the payslip self service part of popay.net, so adding a visual touch could do.

We finally landed on a sunburst, or a pie diagram with two levels, in this case. It shows the earnings, the deductions and finally the remaining net. Employer charges are left out.

This example (image) comes from the Kenyan legislation.

Given that the net, earnings and deductions make up a whole circle, and given that the net amount equals earnings minus deductions, gives that the earnings span half a circle.

To be honest, I only checked that relationship after realizing that each test was showing the earnings as half a circle.

The second level of the chart shows the individual payroll components.

I used highcharts to integrate the chart in popay.net's apex pages. json is generated in the database, and sent as an ajax request to the html page, where some further javascript tweaking feeds the chart.

This example (javascript chart, interactive tooltips, ...) comes from Gabon.

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. ✔

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.

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.

R12.2

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.

hr visualizations

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

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.

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.


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

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.

Some challenges

Designing a payroll system for a few million employees. Mapping cash management reconciliation, costing of payments, continuous calculations and voiding of payments on the core process.

eBS 12.2 : summer sleep

We went throught the updated RCD docs today on metalink and very little has been added for HCM. Succession planning got some extra features.

For Payroll, they only announced that Enhanced Retropay will be the only supported flavor of Retropay. So when you upgrade to 12.2, and your localisation is not yet on Enhanced Retropay, you have to pay attention.

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".