Introduction

What is APEX Office Print?
Why is it Different?
Other AOP differentiators include:
- Tight integration with APEX.
- JSON data payloads (as opposed to the bulkier XML that BI Publisher uses).
- Powerful and easy to follow templating syntax.
- Comprehensive demo APEX application. This was a great help when I learned AOP.
- Reasonably priced (especially when compared to BI Publisher).
Use Cases
Inspection Report
One of our clients’ needs to produce documents (in pdf and Word) detailing the inspection of product before it is shipped to the customer. The document shows order details, material condition information and up to a hundred readings taken from a digital quality gauge. The document is printed at the plant and then emailed to the customer. With so much information coming from many different sources, we decided to use the following approach to generate the document:
- In APEX, the user selects the job to print, checks the output format (pdf or Word) and clicks a custom ‘Print’ button.
- Behind the ‘Print’ button, we call a custom PL/SQL procedure does the following:
- Calls a function to generate the JSON payload for the report. This function calls a number of functions and procedures to gather the data necessary for the report.
- Fetches the appropriate AOP template from a database table for the user’s logged in language.
- Calls the AOP procedure ‘aop_plsql_pkg’ with the JSON and the template. The ‘aop_plsql_pkg’ gathers the template and JSON and calls the AOP web service which returns a BLOB with the formatted document.
- Downloads the output to the users’ computer (optionally emails the document to the user).
Note: In the above example we have not used the AOP APEX Plugin at all. This illustrates you can use the ‘aop_plsql_pkg’ package to call AOP directly from PL/SQL.
Generic Interactive Report Excel Downloads
The example illustrates AOPs tight integration with APEX via their Dynamic Action Plugin. One of our clients has an APEX application with over 150 pages. Amongst these pages are about 50 interactive reports and interactive grids. If we wanted to develop an ‘.xlsx’ download for all of these reports using BI Publisher, we would have had to:
- Develop 50 different BIP templates and upload them to the ‘Report Layouts’ section in ‘Shared Components’ in our APEX App.
- Create a copy of the 50 underlying SQL statements for each of the reports to the ‘Report Queries’ section in ‘Shared Components’ in our APEX App.
- Add code to each of the 50 pages to call these BI Publisher reports.
AOP to the Rescue
Using AOP, we can create a single Excel template with the company logo, a nicely formatted title block and a single token ‘{&interactive}’. This template can be used for all 50 of our interactive report Excel downloads.
Code from the Init PL/SQL Block:
- Sets the download filename using a global variable.
- Sets a global variable with APEX Items so they can be referenced in the AOP template.
- Sets the formatting for the table headings in the output and even sets alternating row colors.
Sample Output:
Employee Document Receipt Confirmation
My final example shows how you can use AOP in the Cloud to generate highly formatted documents from on-premise or Cloud based applications. In this instance, the customer requested that their employees be able to confirm receipt of key documents (e.g. NDA, Employee Handbook) in the Cloud and also generate a pdf copy of their acceptance for the employee to download. In addition to this, they also wanted to generate a copy of the pdf to attach to the employees HR record in EBS on-premise.
APEX running on Exadata Express was used to show the employee the document and to call AOP to generate a pdf of the document acceptance for the employee to download.
One accepted, the on-premise system pulled in the acceptance to the local database, and then used the same AOP template to generate another pdf copy of the document acceptance and attached it to the employees official HR record in EBS.
Architecture
Other Use Cases
- Using AOP from a button in APEX to generate a report and email it instead of downloading it.
- Calling the AOP web service from a native iOS App in order to generate a pdf to display to the user on an iPad.
- Calling the AOP web service from a customer portal (built in a non-Oracle technology) to generate a pdf copy of an invoice for a customer.
Room for Improvement?
- Ability to store templates in the Cloud e.g. AWS S3 and have AOP go directly to the Cloud to get the template.
- You can store templates in S3 today and reference them via a URL. The problem is AOP makes a call from your local database to get the template before the AOP web service is called.
- Ideally, we would pass a template URL and a JSON document to AOP. AOP would then fetch the template directly from S3, without the extra round trip.
- Free development mode credits.
- Developing a template-based report is an iterative process. You often go through many iterations of changing the template, running the report, changing the template and running the report again. A complex report could take more than 100 iterations. This can quickly eat into the monthly AOP plan quota.
- I would like to see a development mode that did not count against your quota. I would even be OK if the result was watermarked in development mode.
Conclusion
Author
Jon Dixon, Co-Founder JMJ Cloud