APEX: Oracle SaaS - Extending Aconex Construction Management

The last few weeks I was engaged with a customer project requiring integration with Oracle Aconex. Oracle acquired Aconex in 2017, adding it to Oracle’s Construction and Engineering Cloud portfolio along with other solutions such as Primavera.

Aconex project collaboration solution digitally connects owners, builders and other teams, providing complete visibility and management of data, documents and costs across all stages of a construction project lifecycle.

The project required SSO and API integration which are provided by two sets of services in an Aconex environment, the Lobby and the Aconex module.

About this Post

In this post, I’ll cover the Aconex services, client setup, authentication scheme and next steps.

The Aconex environment consist of multiple instances that can be accessed by a central Lobby. Each instance has a set of distinct APIs for the different product areas but the Early Access (EA1) Lobby and Aconex instance for projects was used.

Setting up Projects, uploading and configuring documentation or other Aconex objects are out of scope for this article. I’ll stick to the techno-babble, getting user’s authenticated and making the first API invocation.

Getting Access to Aconex

In order to support the customer, I registered on the Aconex Early Access instance (EA1). The Early Access (EA) environment for the Smart Construction Platform, consisting of an Early Access (EA) Lobby and an Early Access Aconex instance (EA1), for the purpose of testing integrations in a non-production environment.

The Early Access Lobby is located at https://constructionandengineering-ea.oraclecloud.com

The Aconex EA1 instance is located at https://ea1.aconex.com

After registration is accepted, you will need to create a Lobby Account which will be linked to your Aconex account. I think of the Lobby account as the Construction Platforms own federated identities allowing SSO access across the different modules and service instances.

Client Registration

An OAuth Client is required to access the Aconex and Lobby APIs. As a Admin you create an OAuth Client in the Lobby.

In the menu, select OAuth Clients to Create and manage OAuth Clients for your API integrations. Select Add and complete the details

The important properties are:

• Resource Application: Defines the APIs the client will use (Aconex)

  The Lobby has a limited set of APIs and are not related the Aconex modules and data or its users

• Type: Web Server Application: For applications that use a web browser interface

• Redirect URL: This is the APEX callback URL

  This needs to be only the callback URL without any query parameters. The page will accept any value but will error that the redirect URL is invalid during the sign-on process

• Select the Organization: Each organization can be associated with an identity store, which will used to identify and authenticate the user during sign-on.

Acknowledge and accept the Terms of Use to complete this step

The Client ID and Secret will be needed for the integration from APEX.

Aconex OAuth Integration Flow

The article Implement OAuth covers the different OAuth client flows in order to access an API.

The Lobby instance acts as the Authorization Server and there is a single Lobby for all commercial Aconex instances (ASIA1, ASIA2, AU1, CA1, EU1, KSA1, MEA, UK1, US1).

The Lobby URL is https://constructionandengineering.oraclecloud.com.

There is a separate Early Access (EA) Lobby for the Aconex Early Access (EA1) instance, which is used for testing Smart Construction Platform integrations in a non-production environment.

The EA Lobby URL is https://constructionandengineering-ea.oraclecloud.com.

The high-level flow to call an API is:

Using the OAuth Client ID & Secrect

1. Request an Authorization Code

2. Use the Authorization Code to request an Access Token and Refresh Token

3. Use the Access Token in API requests

4. Use the Refresh Token to get a new Access Token when it expires

The Lobby URL will be the base endpoint for the auth/authorize (to get the Authorization Code) and auth/token (to get the Access Token and Refresh Token)

In the EA1 instance

Authorization Endpoint URL: https://constructionandengineering-ea.oraclecloud.com/auth/authorize

Token Endpoint URL: https://constructionandengineering-ea.oraclecloud.com/auth/token

APEX Integration

Let’s configure the APEX integrations

I took a gamble setting up this OAuth web credential and authentication scheme after encountering some exceptions during sing-on. One such exception was that open_id was a required scope and so I combined a standard open id configuration url, the fact that the client eventually is registered against the OCI Lobby Tenant and OCI integrated applications support Social Sign-in using Open ID.

So here we go …

Web Credentials

Add the OAuth Client as a Web Credential in APEX using the OAuth2 Client Credential type.

Following the examples in the Aconex OAuth which reference the authorization_code grant type, I set the OAuth Scope to be: grant_type=authorization_code

Enter the Client ID and Client Secret and the Valid for URLs, if you wish to restrict the use of the credential. Be sure to add both the Lobby and Aconex base Endpoint URLs if you do so.

Authentication Scheme

The authentication scheme will preform all the redirects, request the Authorization Code & use it to request an Access Token and Refresh Token. The Tokens are the ones that will be counted in the Web Credentials screen.

Add a new Authentication Scheme based on Social Sign-In:

Once created the Authentication Scheme can be set to the default and I tested by setting the Home page Authentication to Page Requires Authentication.

This should be enough the have SSO running between APEX and the Lobby. Run the application and allow consent to access the requested scopes.

Enter the user details

For tenancies with the Oracle Authenticator, allow the login request

If you need additional attributes they will be accessible in the APEX_JSON if returned by the identity provider. Otherwise, as is the case for Aconex, use the web credential, access token to invoke APIs for the additional details.

For instance in Aconex:

View Own User Information in the Directory API Developer Guide

HTTP GET: https://{hostname}/api/user

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<User>
    <Division></Division>
    <Fax></Fax>
    <Mobile>+41 ** *******</Mobile>
    <OrgAdmin>true</OrgAdmin>
    <OrganizationId>1879048905</OrganizationId>
    <OrganizationName>Oracle Software</OrganizationName>
    <OrganizationPostalAddressLine>The Circle 32</OrganizationPostalAddressLine>
    <OrganizationPostalCity>Zürich</OrganizationPostalCity>
    <OrganizationPostalCountry>Switzerland</OrganizationPostalCountry>
    <OrganizationPostalPostCode>8058</OrganizationPostalPostCode>
    <OrganizationPostalState>Zürich</OrganizationPostalState>
    <Phone>+41 ** *******</Phone>
    <JobTitle>EMEA Data Development Specialist</JobTitle>
    <UserFirstName>Sydney</UserFirstName>
    <UserId>1879051192</UserId>
    <UserLastName>Nurse</UserLastName>
    <UserName>snurse</UserName>
    <UserPostalAddressLine>The Circle 32</UserPostalAddressLine>
    <UserPostalCity>Zürich</UserPostalCity>
    <UserPostalCountry>Switzerland</UserPostalCountry>
    <UserPostalPostCode>8058</UserPostalPostCode>
    <UserPostalState>Zürich</UserPostalState>
</User>

or the User Entitlements in the User Roles API Developer Guide

HTTP GET: https://{hostname}/api/roles/user?queryparams

Parameters

user_id string - Provides list of user entitlements assigned to a user provided user id

eg: user_id=123456

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<UserRoles>
<User>
<Email>sydney.nurse@oracle.com</Email>
<Mobile>+41 ** *******</Mobile>
<FirstName>Sydney</FirstName>
<UserId>1879051192</UserId>
<LastName>Nurse</LastName>
<MiddleName></MiddleName>
<Roles>
<Role>
<DefaultRole>false</DefaultRole>
<NewOrgRole>false</NewOrgRole>
<OrganizationAdminRole>true</OrganizationAdminRole>
<OwningOrganizationId>1879048905</OwningOrganizationId>
<ProjectId>0</ProjectId>
<RoleId>1879051091</RoleId>
<RoleName>5 - Org Admin</RoleName>
</Role>
</Roles>
<UserTitle>Mr</UserTitle>
<UserName>snurse</UserName>
</User>
</UserRoles>

No Worries, the REST Data Source can easily parse XML, not only JSON.

Set the Row Selector accordingly

For User Own Information

For User Entitlements

I’ve added these to the Home Page

This works out well as the API calls acquired during the sing-in process are now used by the REST Data Source calls as well.

Access & Refresh Tokens

Access Tokens have a limited lifespan and will expire by default in 3600 seconds. The Refresh Token is used to refresh the Access Token, acquiring a new Token which will expire and so on…

The Tokens, Access & Refresh and the expiration time, can be saved post authentication in the Authentication Scheme. These can be stored as Application Items or in a Collection and referenced in PL/SQL packages invoking the APIs.

The application should provide a package function that checks the expire time against the current time and if it is within a threshold request a refresh of the token and save the new values to the credential with the SET_SESSION_TOKEN Procedure.

Adding the refresh code to a Dynamic Content Region, that is hidden, along with a some javascript to refresh the Region, say ever 5 minutes.

declare
    l_token_endpoint    varchar2(32767) := 'https://constructionandengineering.oraclecloud.com/auth/token';
    l_parm_name         apex_application_global.vc_arr2;
    l_parm_value        apex_application_global.vc_arr2;
    l_req_resp          clob;
    l_expiry         timestamp with time zone;
    l_vals           apex_json.t_values;
begin
    -- Code to Refresh Token
    -- Should be in a package and check if expiration time is near
    -- Call auth/token API to refresh using refresh token
    apex_web_service.set_request_headers(
        'Accept',        'application/json',
        'Cache-Control', 'no-cache',
        'Content-Type',  'application/x-www-form-urlencoded;charset=UTF-8'
    ); 

    l_parm_name(1) := 'grant_type';    l_parm_value(1) := 'refresh_token';
    l_parm_name(2) := 'refresh_token'; l_parm_value(2) := :G_AUTH_REFRESH_TOKEN;

    l_req_resp := apex_web_service.make_rest_request(
        p_http_method          => 'POST',
        p_url                  => l_token_endpoint,
        -- Need to use Basic auth, with same client id/secret, otherwise it'll try Bearer auth with the tokens
        p_credential_static_id => 'aconex_oauth_client' || '_BASIC',
        p_parm_name            => l_parm_name,
        p_parm_value           => l_parm_value
    );
    if apex_web_service.g_status_code = sys.utl_http.HTTP_OK then
        apex_json.parse( p_source => l_req_resp, p_values => l_vals );

        l_expiry := systimestamp + numtodsinterval( to_number( apex_json.get_number( p_path => 'expires_in', p_values => l_vals )),'SECOND');

        :G_AUTH_ACCESS_TOKEN    := apex_json.get_varchar2( p_path => 'access_token',  p_values => l_vals );
        :G_AUTH_REFRESH_TOKEN   := apex_json.get_varchar2( p_path => 'refresh_token', p_values => l_vals );

        -- Update the Web Credential that's used for REST
        if :G_AUTH_ACCESS_TOKEN is not null then
            apex_credential.set_session_token (
                p_credential_static_id  => 'aconex_oauth_client',
                p_token_type            => apex_credential.c_token_access,
                p_token_value           => :G_AUTH_ACCESS_TOKEN,
                p_token_expires         => l_expiry      
            );
        end if;

    end if;
    return to_clob( '<!-- Refresh Token -->' );
end;

if( window.TOKEN_REFRESH_TIMEOUT ){
    clearTimeout( window.TOKEN_REFRESH_TIMEOUT );
}
window.TOKEN_REFRESH_TIMEOUT = setTimeout( () => {
    console.log( 'Refreshing FA session.' );
    apex.regions.TOKEN_REFRESHER.refresh();
}, 1000 * 60 * 5 ); // Every five minutes

How to Debug?

Once the application is launched you are taken directly to the Sign-on page and the developer tools menu is no longer visible.

If something goes wrong, how can you enable debugging?

During this process I used two primary methods to debug, one was the browsers Developer Tools, Network Tab, to review the URLs, headers, cookies, and payloads sent from APEX to Aconex.

To capture the APEX calls for authentication, start the application and leave it on the Sign-On page.

From the APEX App Builder, navigate the Monitor Activity

Select Active Sessions

In the list of sessions, select the session for username ‘nobody’

Set the Debug Level appropriately, I did not want to miss anything so I set it to Full APEX Trace

Apply the changes and return to the Sign-On screen to complete the sign in attempt then return to the session activity, refresh the report results and the Authentication Callback will be listed with a Debug ID

Select the Debug log to review.

Conclusion

Integration with Oracle Aconex, Construction Management, is similar to Fusion in general but it does not fall under the APEX Fusion SaaS or OCI Cloud Applications OOTB integration.

Fusion Integrations, Integrated Applications, required for SSO and REST API calls, are created in the OCI Fusion SaaS Tenant, now automated via the DB Tools Integrate APEX with Fusion Applications wizard. If you’ve not read the Enhancing Oracle Fusion Applications with Oracle APEX: Getting Started blog post, check it out.

For more insight into the entire integration and access to the support note on MOS covering the refresh token process, review the Extending Oracle Fusion Cloud Applications Suite Using Oracle APEX technical paper. Aconex shares the same challenge with expiring tokens and the paper and MOS note cover the topic in depth.

The Early Access environment is great for testing the basics but as an end user, no privileges to perform or verify the associated objects in the OCI Tenancy.

Aconex OAuth Clients are managed in the Lobby which provides a simple creation but has limited options, such as the lack of Post logout redirect URL. The Lobby can be thought as the central identity manager for the different Aconex instances, and it is used to request Authorization Codes & Access Tokens.

There are two independent sets of APIs for the Lobby and Aconex modules, with different endpoints, so care should be taken when adding OAuth Clients.

Lastly, Aconex module APIs have XML response payloads which are supported by the REST Data Source component. Oracle does provide XML support natively in the database

I hope you have found this series useful and find a use case in your APEX integrations as well.