Introduction
Requirement Overview
The customer has a diverse set of employees including salaried as well as hourly workers. All employees have access to Oracle EBS but not all of them have a corporate (or even private) email address and even fewer have access to VPN. The initial notification asking an employee to acknowledge a new document had to be made via an Oracle Work list (through the Oracle Workflow module) notification as well as email (if available). All these factors made it important that we deliver an easy to access, easy to use and mobile friendly experience that is available outside of the firewall.
Approach
- Identify new employees that have not acknowledged certain documents of record.
- Generate a document of record for the employee and document type (using Oracle Document of Record API).
- Generate a unique token for each employee and document of record.
- Call an ORDS REST service (hosted on Exadata Express) to POST the document of record to a table in Exadata Express.
- Launch a workflow to notify the employee that there is a document he or she need to review and acknowledge. The notification contains a link to an APEX application in Exadata Express. The link includes the unique token created in step 3.
When the employee clicks the link, they are taken to the APEX App on Exadata Express, which validates the token and shows the employee the document that needs to be reviewed. As well as the document, the user is also presented with an Acknowledge button. Once clicked, the document is marked as acknowledged in a table in Exadata Express.
Some documents are displayed in-line in HTML format, while others are shown as an attached document, housed in an Amazon S3 bucket. This is configurable using an APEX administration App.
In addition to the End User Application, an Admin APEX App was created to maintain the text of each document type as well as links to the PDF documents on S3. This application allows an administrator to configure what the user sees for a particular document type. This includes the ability to have a variable number of sections for each document type and allow the document to be displayed in three different languages.
A second concurrent program was developed which performed the following steps:
- Calls a 2nd ORDS REST service running on Exadata Express. This service fetches a list of documents that have been acknowledged since the last time the process ran.
- For each record returned, update the related EBS document of record to indicate the date the document was accepted.
Regarding reporting, the HR department can report on which employees had accepted which document, directly in EBS. This is possible because we utilized standard Document of Record functionality in EBS.
Security Considerations
- All web service calls are made outbound from the on-premise EBS database to Exadata Express. No inbound traffic is allowed.
- The table of document of record information stored in the Cloud is a cut down version of the table in EBS excluding any sensitive employee data.
- The token used to identify the user is invalidated as soon as the employee acknowledges the document. In addition, each token is specific to a combination of employee and document of record.
- We secured the Exadata Express ORDS REST services using OAUTH2 client credentials flow. This uses a known user name and password (stored in the on-premise database) to obtain a short-lived token. That token is then used to call the web services.
Architecture Diagram
Benefits of the Approach
- The load of thousands of employees viewing and acknowledging various different documents and PDF files is off-loaded from the main transactional ERP database to the Cloud.
- The APEX application is easily accessible outside the firewall without exposing sensitive HR data sitting in the EBS database.
- APEX provides for a much more user (and mobile) friendly platform for delivering documents to employees, many of whom use computers very infrequently.
- Using APEX, we can capture more details about the user’s interaction with the document. For example, we can determine which users viewed the document but did not acknowledge it.
Conclusion
Author
Jon Dixon, Co-Founder of JMJ Cloud