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.