Oracle Reports Generation with Docufy

In a previous blog post we explained already why we decided to build our own report generation tool. In this post we will explain the main principles and components the tool consists of.

First of all, Docufy is a tool for developers: for APEX developers … surely, but in fact for anyone who is developing against an Oracle database and who needs a printable output. In the current version, the printable document is a .DOCX file.

How does it work?
Docufy consists of two major components:
1) a front-end application (UI), used for defining the metadata that describes the documents. The actual UI is written in APEX and is available in all 4.x versions. We are also working on a Eclipse-RCP version of the UI.
2) a PL/SQL engine, which contains all the intelligence and generates the desired output

1) The APEX UI
The main objective of APEX UI is to give the developer an easy interface to configure his document. There are different screens where you can enter the metadata of your ‘printable’ report. This metadata consists of:
- one or more SQL statements
- minimal 1 Word-template (DOCX)
- and optionally input parameters

Following screenshot gives you a general idea of how the UI looks like:

APEX-UI

2) The PL/SQL Engine
The PL/SQL Engine retrieves the metadata which is defined via the UI as input for a given document. It executes the SQL statement(s), binds them with the parameter(s) and gets the specified DOCX-template.
Thereafter, this template is unzipped. As you may know, a DOCX-document is an amalgam of different XML-files. The engine replace all data tags in the document.xml file by the corresponding business data.
At the end all XML-files are packed again in a DOCX-output-document, which is returned as a BLOB variable to the calling program.

Following picture gives a schematic overview of the concept:

So, summarizing:
Docufy is a RAD-tool for generating operational reports. With its intuitive APEX UI, you easily configure your documents by adding DOCX-templates and SQL-queries. The engine is written in PL/SQL, which makes installation, integration and maintenance a walk in the park. The tool comes as a packaged application for APEX 4.x.

Follow @docufy on twitter
www.docufy.be

Docufy: Document Generation for APEX made easy!

APEX is well known for his RAD solution and the no-extra cost factor: APEX makes it possible to build on time and within budget your database application for the web. But an application is more then building pages for CRUD operations. Very often you also need to generate well looking documents. And this is standard not possible with APEX. A pity!  Within the Oracle world, Bi Publisher is by far the best solution. But from the pricing point of view, it does not really fit in the ‘cheap’ philosophy of APEX. Further, you can also look for Open Source solutions like Jasper Reports and Birt. Both have a community version that you may use for free.

Nevertheless, we decided to build our own tool. During the presentation at APEX World of last week, organized by OgH, we explained how we came to this decision. On different projects at the Flemish Government we always had the business requirement to generate output for MS-Word (.docx). We first tried to solve this requirement with Jasper Reports. This was fine for simple letters. But the end-user also wanted to change some of those documents after generation … and that was not so easy to do with the output created by Jasper.

So the idea for an own “document generator for Oracle” was born. The main objective was trying to bring some of the better features of other tools together in one solution:

  • Separation of data retrieval and document layout.  The layout is template driven: your template is a MS Word document (.docx). This leaves the possibility open to involve business users in the design and build of the desired output.
  • Simple, easy and light: we want the solution to be as much as possible in the database, in PL/SQL, with no extra software on a middle-tier
  • Metadata driven and nice user interface to manage those data
  • Integration in your application via a PL/SQL API. This application may be developed in the technology of your choice. We use it initially in APEX projects, but it can be integrated in any tool that can do a PL/SQL call (Oracle Forms, any java solution, Formspider, …)

Our custom solution evolved well: we already used it successfully in different projects. Therefor, we decided to make it available for the world and packaged the solution in a tool with the name Docufy. You can find more information on the Docufy-site or you may always send a mail to info@docufy.be.

With Docufy,
we proceed on the APEX philosophy,
there where APEX it self stops:
easy and quick generation of operational documents.

APEX and eBusiness (EBS or APPs)

Traditionally EBS-extensions are built in tools like Oracle Forms, Reports and BI Publisher. Most E-Business developers master these traditional skills (e.g. PL/SQL, workflow).
Oracle now propagates a shift towards Java (Oracle Applications Framework (OAF) and Oracle Application Development Framework (ADF)), however this requires to refocus of the current E-Business developers toward java development (object orientated).  If the developer’s background are the basic Oracle skills (SQL and PL/SQL and even Oracle Forms), then APEX is the perfect tool to web-enable your skills. No Java knowledge needed.

At the moment alternatives like APEX are not widely used in the E-Business area. Maybe due to the following reasons:

  • E-Business  customers are not yet aware of the possibilities of APEX within E-Business;
  • E-Business customers are not yet aware of the lower costs of APEX development in e-business;
  • E-Business  customers are not yet aware that the tool is free as it comes pre-installed with Oracle XE and 11g;
  • APEX consultancy companies do not have the E-Business knowledge.

Since the very beginning we, at iAdvise, believed in the possibilities of APEX as a valid “alternative” platform for building cost-effective, open, reliable E-Business customizations and extensions.
We think of different use-cases where APEX can be used for custom development:

  • Custom data collection, for which today Excel is often used
  • Building New applications, in support of business processes not covered by standard APPS
  • Reporting and data analysis, when necessary with real time access to EBS data
  • Modernization of custom Oracle Forms applications

Already in 2008 we did a modernization exercise for existing APPs functionality. The customer wanted a more efficient way for manually introducing invoices in the system. We developed a small Quick Entry application, in APEX 3.0. Via a small wizard,  the user can enter the necessary data. Following screenshot shows Step 2 of the wizard where one or more order lines could be defined.

Add invoice lines

Quick Entry AR Invoices: Step 2

When pressing Finish at the last step, the newly created invoice is added in the APPs tables via the standard APIs. We have also foreseen the possibility to switch to the ‘Open Interface (OI)’ mode: instead of using the APIs, the data is in inserted in the available Open Interface tables.

Since last year, Oracle also officially declared APEX as a valid tool to extend Oracle APPs by publishing the already famous white paper that gives a very good explanation how you can integrate both environments.

In the BeNeLux the interest is growing for this combination. Therefor we are happy with the OBUG initiative to organize on the 15th of February an APEX-EBS combi-SIG. You can subscribe for this event via the OBUG site.

We will be there !

Pictogram: APEX Developer – Plug-In Developer

Not every APEX developer should be a Plug-In developer.
The APEX developer uses the Plug-In a Plug In developer has created.

Following photo shows the pictograms we used during our presentation @ OBUG APEX SIG :

Pictogram Plug-In developer APEX developer

Pictogram Plug-In developer APEX developer

During the demo we showed our little green man while we created and elaborated the plug-in via the shared components.

When we implemented the plug in in a page, we showed the pictogram of the little red man.

As you can see the red guy brings different elements of the puzzle together: standard components (red) and a plug-in (the green piece).

OBUG APEX SIG

Today iAdvise has given a presentation during the OBUG APEX SIG at Oracle Belgium. The title of the presentation was “APEX Plug Ins: How to use them, How to build them.“.

The goal of the session was to give a general introduction on

  1. What are plug-ins ?
  2. How can you use them as an APEX Developer.
  3. How can you write them as an PLUG In Developer.

Especially part 3 of the presentation was a real challenge. We did a live demonstration of how you can gradually build up a new plug in from scratch. In 5 steps Stijn Van Raes demonstrated how to construct a “Clearable Text Item” plug-in. It is not the most complex plug-in, but it allowed us to describe every single step, pointing out the existing Plug-In API’s that you may use when writing your code. At every step, the necessary PL/SQL code was explained,  the resulting options for the APEX Developer demo’ed and  the effect at run time for the end user. Where relevant the resulting HTML code was inspected by using Firebug.

Because there is not yet that much documentation on those Plug-In API’s and their usage –  unless in the internal PL/SQL Packages of the APEX engine itself or in existing plug-ins–, we decided to make a kind of APEX4.0 Plugin Cheatsheet which lists the most important API procedures and functions and the different record types they use.

For the people who were interested, it was also possible in the afternoon to attend a hands-on session and try out everything we explained in the morning. We have elaborated two tutorials that repeated every single step with some extra explanation and the necessary code snippet. Everybody who attended the hands-on finished both tutorials without any problem.

First APEX SIG in Belgium (via OBUG) focused on APEX 4.0

Last Thursday (17/06) Oracle Benelux User Group, aka OBUG, organised his first APEX SIG, an initiative of Dimitri Gielis and Roel Hartman. Even though the new version of the product is not yet “in production”, the theme of the day was APEX 4.0.

iAdvise sponsors firs APEX SIG in BeNeLuxWe, at iAdvise, are one of the early promoters of Oracle Application Express in Belgium.   Since 2006, we implemented already different solutions with APEX. Therefore, we could not  be absent at this event and decided to participate and sponsor this first APEX SIG day.

Olivier Dupont and Jan Huyzentruyt gave a presentation with the title: “APEX 4.0: Feature 135, 184, 301, 315, 348 … How it was before, how it is now !”  The idea was to demonstrate some of the less spectacular new features of Apex 4.0 that (1) makes the life of the APEX developer easier and more productive and (2) allows to create more good looking, to the point web-application. Most of those features we explained by showing how it was in the “old” days, and how (easy) it is with APEX 4.0.

For everybody who could not attend the OBUG session, we published our presentation at slideshare.net:

A report of the day can also be found here.

Google Translate integrated within xTra4o

xTra4o stands for “XLIFF Translator for Oracle”, and more specific for APEX.

xTra4o is an application that iAdvise has built, already a few years ago, that helps in the translation process of an APEX application. This tool is publicly available as referenced by the APEX OTN site.
Read our blogs  referring to this tool, posted in February 2007.

There is a one specific step within the process of tanslating an application that  APEX itself does not so well support; it is step 3 where you must translate all translatable elements within the so-called XLIFF file.

Our xTra4o allows to upload this XLIFF file and translate the individual elements via a specific editor.

A first surplus of this tool is the possibility  to write common terms in a Dictionary  and use this Dictionary for translating similar terms.

Today we have added an additional useful feature to the tool. For each source term you can get the translation via the Google Translate API. Following screenshot shows the new icon, you can click on to let Google make the translation. In the example English terms must be translated into French:

As a user, you can always adjust the proposed translation yourself and decide to add the word in the Dictionary.

We have discovered that Google does not always treat ‘special characters’ in a proper way. For some of those signs (like the ‘#’ sign), we block the API call. It is possible that we do not block all special characters yet, but this can easily be added. If you detect such cases, let us know via the feedback option in the tool.

To use this Google Translate API we started from the PL/SQL code that you can find on this blog.