Introduction

This approach stuck with me when my partners and I started JMJ Cloud. Since the weekend we started our business (when we built our time sheet application in APEX) through to our most recent push to replace Jira Service Desk with an APEX App, we have been using APEX to solve our own business problems as well as our customer's. In this post I will review the building blocks of the integrated APEX portal we built to help us run our business and interact with our customers.
The Platform
Authentication (Social Sign On)
Tip: If all you do is create an authentication scheme for Microsoft Social Sign On, then you have just given everyone with a Microsoft account access to your applications. You must follow it up with a default Authorization scheme that verifies the authenticated user has access to your portal.
Authorization (Who has Access to What)
Of course, your authorization scheme must be based on some data. We decided on a simple role-based model with the following entities:
- Users
- Applications
- Application Roles (User, Admin, Project Manager etc.)
- User > Application > Roles
For most applications, a role-based model will work just fine. The main drawback of this approach, however, is that it does not allow you to be very granular. Sometimes you need to explode this model and include privileges that give users access to specific actions.
Tip: Whenever possible set the ‘Evaluation Point’ of your Authorization Schemes to ‘Once per session’. This ensures that APEX only checks the Authorization Scheme at the time of login as opposed to every time a page (or component) is rendered. This is especially important if your authorization scheme contains a lot of code (or even calls to web services). This may seem like a small thing but poor performance in APEX comes about incrementally. Two tenths of a second for two authorization scheme calls, a tenth of a second for a sentry function call and two tenths for a before page process and your user has just waited half a second and the page hasn’t even rendered yet.
Portal (Multiple Applications)
External Integrations
- SMS Messages Via Twillio (time entry reminders, important notifications)
- File Object store via Amazon S3
- SMTP email via AWS Simple Email Service (SES)
Tip: When integrating with external web services you need to become familiar with installing SSL certificates in your database wallet. I suggest creating a single wallet (with auto login) that can be referenced in your APEX environment settings (via the INTERNAL Workspace) so that native APEX components (e.g. Social Sign on, APEX_WEB_SERVICE etc.) can utilize this wallet when making calls to external services. If you are using a completely managed service like Oracle Autonomous ATP then this has been done for you.
The Applications
Time Entry Application
Time entry is the lifeblood of a consulting company. Even a small consulting company will get mired in spreadsheets without a system for their consultants to enter their time. As I mentioned, we built our current time-entry APEX application the weekend before we officially started our business. Fifteen thousand time entries later and the application is still going strong. Not only does it make time entry more accurate it helps us to generate our monthly invoices in minutes instead of hours.
e-Business Suite (EBS) Code Deployment Application
I have been building EBS extensions for nearly 20 years. A constant challenge has been to consistently and accurately deploy code between EBS instances. In days gone by, we achieved this by hand crafting shell scripts to deploy the code and handing these off with the source code to a DBA as attachments to an email. This approach is fraught with errors and inconsistencies.
In view of this challenge, we built an APEX application where developers can upload their source scripts for a deployment and the application automatically generates a shell script to install them. The application is aware of all the types of objects you can deploy in an EBS environment and builds the shell script appropriately. These object types include standard database objects (APEX applications, tables, views, PL/SQL packages etc.) as well as EBS specific objects (Menus, Responsibilities, Lookups, Forms, Reports BI Publisher Templates etc.). Once the developer has submitted their deployment, it goes through an approval and eventually a notification is sent to the DBA. The DBA can then download a single zip file and run the deployment on the EBS server. Once a deployment is complete in a TEST environment, it can then be copied to create a PROD deployment (with no manual intervention). This approach improves consistency, accuracy and audit traceability which are all key in an any ERP environment.
Service Request Tracking Application
When we started out, we used Jira Service Desk (for managing tickets with customers), Jira Software for managing projects and Jira Confluence for documentation. We loved these products but felt that Jira Service Desk was a little much (functionality wise and cost wise) for our needs. Three weeks ago, we introduced our replacement for Jira Service desk developed in APEX. The key to ticket management for us is to be able to have our customers create and update tickets as well as our consultants.
As we already had the portal framework in place (with Microsoft login, authorization management, email and object store capabilities) we were able to build the SR application in matter of weeks.
Tip: Our SR system allows users to add attachments. I believe file attachments do not belong in the database. They take up a lot of space on relatively expensive storage. We keep attachments out of the database using AWS S3. You can store a hundred GB worth of files in services like S3 or Oracle’s object store for less than $5 per month.