Introduction

Oracle APEX and Visual Builder are the go-to development platforms for extending and integrating with Oracle Fusion Cloud ERP.

😉

Obviously, I believe APEX is the best tool for the job.

This post will show you how to integrate data from APEX to Oracle Fusion Cloud ERP using the erpintegrations REST API. In addition to showing you how to do the integration, the goal of this post is to illustrate why it makes sense to use APEX.

Scenario

I built an APEX application for a customer to allow users to create and upload manual journals requiring special approvals and business-specific defaulting rules, which was impossible using an out-of-the-box Oracle Fusion Cloud ERP functionality. Once completed, these journals must interface with Oracle Fusion Cloud ERP General Ledger. The journal details are converted into a special File-Based Data Import (FBDI) file format and sent to Oracle Fusion Cloud ERP using the erpintegrations REST API.

👉

This solution was used to build a real integration for an actual client.

FBDI Process

FBDI isn't a secret government agency. It stands for File-Based Data Import, a standard for importing large volumes of data into Fusion using CSV files. FBDI is not the most elegant interface I have used, but it gets the job done. The FBDI process is pretty much the same for all of the different integrations:

Generate one or more CSV files in a specific format.
ZIP the CSV File.
Base64 Encode the Zipped CSV file.
Post the Base64 encoded string to the erpintegrations REST API, which is asynchronous.
The REST API performs several tasks in Fusion:
- Unencode and unzip the file.
- Load the relevant Fusion interface table.
- Run the relevant Fusion interface process (ESS Scheduler Job).
- Post the results to the callback URL.
Wait for a callback to let you know the import is complete.
Receive the Callback from Fusion, inspect the payload to determine if the import was successful, and take appropriate action.

The following sections will describe steps 1-4 and 7 in more detail.

1. Generate CSV File

Because we are using APEX, we must rely on PL/SQL to generate a CSV file in the appropriate FBDI format. While some open-source PL/SQL tools are out there, the most well-known being the alexandria-plsql-utils from Morten Braten, I typically write my own code to generate CSV data.

The code snippet below generates a CSV and stores it in a CLOB. I have omitted most columns from the General Ledger FBDI format for brevity.

DECLARE
  CURSOR cr_fbdi_line IS
    SELECT  GC_JI_FBDI_STATUS_NEW                                ||','||
            hdr.ledger_id                                        ||','|| 
            TO_CHAR(hdr.accounting_date, GC_JI_FBDI_DATE_FORMAT) ||','||
            hdr.je_source                                        ||','|| -- user_je_source_name
            ...
            ''                                                   ||','|| -- LINE DFF ATT15
            '' gl_row
    FROM    ...
    WHERE   ...;
  l_fbdi_csv CLOB;
BEGIN
  -- Create CSV in FBDI Format.
  FOR r_fbdi_line IN cr_fbdi_line LOOP
    l_fbdi_csv   := l_fbdi_csv || TO_CLOB(r_fbdi_line.gl_row || CHR(10));
  END LOOP;
END;

💡

With the introduction of database version 23ai and the MLE engine, it will be interesting to see if we can utilize open-source Javascript-based utilities to generate CSV files in the future.

2. ZIP the CSV File

This one is easy: APEX_ZIP to the rescue.

DECLARE
  l_fbdi_csv           CLOB;  -- Generated in the Previous Step.
  l_fbdi_csv_file_name VARCHAR2(100) := 'File_Name_After_Unzipping.csv';
  l_fbdi_zip_file      BLOB;  -- Zipped BLOB of the CSV File.
BEGIN
  apex_zip.add_file 
    (p_zipped_blob => l_fbdi_zip_file, 
     p_file_name   => l_fbdi_csv_file_name,
     p_content     => apex_util.clob_to_blob 
                       (p_clob    => l_fbdi_csv,
                        p_charset => 'AL32UTF8'));
  apex_zip.finish(p_zipped_blob => l_fbdi_zip_file);
END;

💡

We used another helpful APEX PL/SQL API apex_util.clob_to_blob to convert our CSV CLOB to a BLOB.

3. Base64 Encode the Zipped File

This is another easy one for APEX. We can use the PL/SQL API apex_web_service.blob2clobbase64 to do this for us.

DECLARE
  l_fbdi_zip_file   BLOB;   -- Zipped BLOB from the previous step.
  l_fbdi_zip_base64 CLOB; 
BEGIN
  dbms_lob.createtemporary(l_fbdi_zip_base64, TRUE);
  l_fbdi_zip_base64 := apex_web_service.blob2clobbase64 
                        (p_newlines => l_fbdi_zip_file);
  dbms_lob.freetemporary(l_fbdi_zip_base64);
END;

4. POST to the erpintegration REST API

The next step is to post the Base64 encoded string to the erpintegration REST API.

API Details

REST API URL

The URL for the erpintegration REST API will look something like the below:

https://<POD>.fa.<data center ID>.oraclecloud.com/fscmRestApi/resources/11.13.18.05/erpintegrations

e.g.
https://abccorp-dev.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/erpintegrations

Sample Payload

Use the POST method to post the payload to the REST API.

{
   "OperationName":"importBulkData",
   "DocumentContent":"<BASE64 Encoded Content Goes Here>",
   "ContentType":"zip",
   "FileName":"GL_TEST.zip",
   "DocumentAccount":"fin$/generalLedger$/import$",
   "JobName":"/oracle/apps/ess/financials/generalLedger/programs/common,JournalImportLauncher",
   "ParameterList":"<DATA_ACCESS_SET_ID>,<JE_SOURCE_NAME>,<LEDGER_ID>,<INTERFACE_GROUP_ID>,N,N,O",
   "CallbackURL":"<ORDS_CALLBACK_URL>",
   "NotificationCode":"10",
   "JobOptions":"ImportOption= Y ,PurgeOption = N ,ExtractFileType=ALL,InterfaceDetails=15"
}

You can get the ParameterList values from the following sources:

DATA_ACCESS_SET_ID (gl_access_sets.access_set_id)
JE_SOURCE_NAME (gl_je_sources.je_source_name)
LEDGER_ID (gl_ledgers.ledger_id)
INTERFACE_GROUP_ID (Integer used to identify this specific set of records in the GL Interface table. You could use a DB Sequence in the APEX DB to generate this).

Other variables:

Base 64 Encoded Content Goes Here (this is the Base64 encoded and zipped CSV file we created in the previous section)
ORDS_CALLBACK_URL (this is the URL of the ORDS REST API you want Fusion to call once the import process is complete. More on this later.

HTTP Headers

You should pass the following HTTP Header:

Content-Type > application/vnd.oracle.adf.resourceitem+json

Building the Payload

DECLARE
  l_request_obj      json_object_t := json_object_t();
  l_fbdi_zip_base64  CLOB;  -- Created in the previous section.
  l_gli_param_list   VARCHAR2(1000); -- Described in the Sample Payload.
  l_payload_clob     CLOB;
BEGIN
  l_request_obj.put('OperationName', 'importBulkData');
  l_request_obj.put('DocumentContent', l_fbdi_zip_base64);
  l_request_obj.put('ContentType', 'zip');
  l_request_obj.put('FileName', 'FileName.zip');
  l_request_obj.put('DocumentAccount', 'fin$/generalLedger$/import$');
  l_request_obj.put('JobName', '/oracle/apps/ess/financials/generalLedger/programs/common,JournalImportLauncher');
  l_request_obj.put('ParameterList', l_gli_param_list);
  l_request_obj.put('CallbackURL', 'https://apex.oracle.com/...');
  l_request_obj.put('NotificationCode', '10');
  l_request_obj.put('JobOptions', 'ImportOption= Y ,PurgeOption = N ,ExtractFileType=ALL,InterfaceDetails=15');
  l_payload_clob := l_request_obj.to_clob;
END;

Calling the REST API

DECLARE
  l_erp_rest_base_url  VARCHAR2(500);
  l_payload_clob       CLOB;  -- Built in the previous step.
  l_response_clob      CLOB;
  l_erp_ess_job_id     NUMBER;
BEGIN
  -- Build the URL.
  l_erp_rest_base_url := 'https://<POD>.fa.<data center ID>.oraclecloud.com/fscmRestApi/resources/11.13.18.05/erpintegrations';
  -- Set the HTTP Headers.
  apex_web_service.set_request_headers 
   (p_name_01  => 'Content-Type', 
    p_value_01 => 'application/vnd.oracle.adf.resourceitem+json',
    p_reset    => TRUE);

  -- Call the API.
  l_response_clob := apex_web_service.make_rest_request 
   (p_url                  => l_erp_rest_base_url, 
    p_http_method          => 'POST', 
    p_transfer_timeout     => 60, 
    p_body                 => l_payload_clob,
    p_credential_static_id => '<APEX_WEB_CREDENTIAL_STATIC_ID>',
    p_scheme               => 'Basic');

  -- Verify if the call was successful.
  IF apex_web_service.g_status_code = 201 THEN
    -- Parse the response to get the ESS Job ID.
    SELECT jt.erp_ess_job_id INTO l_erp_ess_job_id
    FROM   JSON_TABLE(l_response_clob, '$'
             COLUMNS (erp_ess_job_id  NUMBER  PATH '$.ReqstId')) jt;
  ELSE
    -- Something went wrong. Handle Errors appropriately.
  END IF;


END;

A few comments on the code block above:

APEX_WEB_SERVICE is the ultimate tool for calling web services from APEX. Read my post APEX_WEB_SERVICE The Ultimate Guide for more.
APEX Credentials provide a convenient method for securely storing credentials for web services. Read my post Secure your Secrets with APEX Web Credentials for more.

Sample Response from the erpintegrations REST API

{
  "OperationName" : "importBulkData",
  "DocumentId" : null,
  "DocumentContent" : "UEsDBBQAAAgIAO==",
  "FileName" : "AJE_1_20240422.zip",
  "ContentType" : "zip",
  "FileType" : null,
  "DocumentAccount" : "fin$/generalLedger$/import$",
  "Comments" : null,
  "ProcessName" : null,
  "LoadRequestId" : null,
  "JobPackageName" : null,
  "JobDefName" : null,
  "ReqstId" : "44230553",
  "RequestStatus" : null,
  "JobName" : "/oracle/apps/ess/financials/generalLedger/programs/common,JournalImportLauncher",
  "ParameterList" : "300000101545821,300001694807375,300000671504407,5000011,N,N,O",
  "NotificationCode" : "10",
  "CallbackURL" : "https://apex.oracle.com/...",
  "JobOptions" : "ImportOption= Y ,PurgeOption = N ,ExtractFileType=ALL,InterfaceDetails=15",
  "StatusCode" : null,
  "ESSParameters" : null,
  "links" : [ {
    "rel" : "self",
    "href" : "https://abc.fa.us3.oraclecloud.com:443/fscmRestApi/resources/11.13.18.05/erpintegrations/importBulkData",
    "name" : "erpintegrations",
    "kind" : "item"
  }, {
    "rel" : "canonical",
    "href" : "https://abc.fa.us3.oraclecloud.com:443/fscmRestApi/resources/11.13.18.05/erpintegrations/importBulkData",
    "name" : "erpintegrations",
    "kind" : "item"
  } ]
}

⏰

After submitting the request, we must wait for Oracle Fusion Cloud ERP to import our FBDI file. Once the import is complete, Oracle Fusion Cloud ERP POST a payload to the callback URL specified in the request.

7. Receive the Callback from Fusion

Callback ORDS REST API Definition

We can define a simple ORDS REST POST Handler to receive the callback. The API must be accessible from the internet.

Oracle ORDS REST API to Handle Fusion Callback

Handle Callback

When the callback occurs, Fusion will provide a payload like this:

<env:Envelope
    xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:wsa="http://www.w3.org/2005/08/addressing">
    <env:Header>
        <fmw-context
            xmlns="http://xmlns.oracle.com/fmw/context/1.0"/>
            <wsa:To>https://n143wfwm4d0nssh-mvhprj1db.adb.us-phoenix-1.oraclecloudapps.com/ords/VHGAPPSDB/FUSION_ERP_CALLBACK/fusion_gl_journals</wsa:To>
            <wsa:Action>http://xmlns.oracle.com/scheduler/ESSWebService/getCompletionStatus/Response</wsa:Action>
            <wsa:MessageID>urn:uuid:155c2578-29d8-431d-b3f2-be36ea3e8886</wsa:MessageID>
            <wsa:RelatesTo>urn:uuid:0d1c2e2e-f0df-4ef0-8934-6cc142b92f48</wsa:RelatesTo>
            <wsa:ReplyTo>
                <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
            </wsa:ReplyTo>
            <wsse:Security env:mustUnderstand="1"
                xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
                <saml:Assertion MajorVersion="1" MinorVersion="1"
                    xmlns:saml="urn:oasis:names:tc:SAML:1.0:assertion" AssertionID="SAML-oax4FbdYrTFKaFP04SMqHg22" IssueInstant="2024-06-21T18:07:03Z" Issuer="www.oracle.com">
                    <saml:Conditions NotBefore="2024-06-21T18:07:03Z" NotOnOrAfter="2024-06-21T18:12:03Z"/>
                    <saml:AuthenticationStatement AuthenticationInstant="2024-06-21T18:07:03Z" AuthenticationMethod="urn:oasis:names:tc:SAML:1.0:am:password">
                        <saml:Subject>
                            <saml:NameIdentifier Format="urn:oasis:names:tc:SAML:1.1:nameid-format:unspecified">VHAPEXFININT</saml:NameIdentifier>
                            <saml:SubjectConfirmation>
                                <saml:ConfirmationMethod>urn:oasis:names:tc:SAML:1.0:cm:bearer</saml:ConfirmationMethod>
                            </saml:SubjectConfirmation>
                        </saml:Subject>
                    </saml:AuthenticationStatement>
                    <dsig:Signature
                        xmlns:dsig="http://www.w3.org/2000/09/xmldsig#">
                        <dsig:SignedInfo>
                            <dsig:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
                            <dsig:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/>
                            <dsig:Reference URI="#SAML-oax4FbdYrTFKaFP04SMqHg22">
                                <dsig:Transforms>
                                    <dsig:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/>
                                    <dsig:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
                                </dsig:Transforms>
                                <dsig:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/>
                                <dsig:DigestValue>ra33ezAbzD9G1Pozytpth+/frcI=</dsig:DigestValue>
                            </dsig:Reference>
                        </dsig:SignedInfo>
                        <dsig:SignatureValue>WGP69J/fqr/FwKqmZLP7V9wi48BXSIlznbH/z4HZLbZPvsSP29w29oN4sqtY0z98</dsig:SignatureValue>
                        <dsig:KeyInfo
                            xmlns:dsig="http://www.w3.org/2000/09/xmldsig#">
                            <dsig:X509Data>
                                <dsig:X509Certificate>==</dsig:X509Certificate>
                                <dsig:X509IssuerSerial>
                                    <dsig:X509IssuerName>CN=Cloud9CA-2, DC=cloud, DC=oracle, DC=com</dsig:X509IssuerName>
                                    <dsig:X509SerialNumber>979551222832461690</dsig:X509SerialNumber>
                                </dsig:X509IssuerSerial>
                                <dsig:X509SubjectName>CN=abc-def_fasvc, DC=cloud, DC=oracle, DC=com</dsig:X509SubjectName>
                                <dsig:X509SKI>m4UR62jQPFp+GnsCR9sIDUXANfs=</dsig:X509SKI>
                            </dsig:X509Data>
                        </dsig:KeyInfo>
                    </dsig:Signature>
                </saml:Assertion>
            </wsse:Security>
        </env:Header>
        <env:Body>
            <ns0:onJobCompletion
                xmlns:ns0="http://xmlns.oracle.com/scheduler">
                <requestId
                    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="xsd:long">44359812
                </requestId>
                <state>SUCCEEDED</state>
                <resultMessage>{"JOBS":[{"JOBNAME":"Load Interface File for Import","JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader","DOCUMENTNAME":"AJE_5000195_20240621.zip","REQUESTID":"44359812","STATUS":"SUCCEEDED","CHILD":[{"JOBNAME":"Transfer File","JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader","REQUESTID":"44359813","STATUS":"SUCCEEDED"},{"JOBNAME":"Load File to Interface","JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader","REQUESTID":"44359814","STATUS":"SUCCEEDED"}]},{"JOBNAME":"Import Journals","JOBPATH":"/oracle/apps/ess/financials/generalLedger/programs/common","REQUESTID":"44359815","STATUS":"WARNING","CHILD":[{"JOBNAME":"Import Journals: Child","JOBPATH":"/oracle/apps/ess/financials/generalLedger/programs/common","REQUESTID":"44359816","STATUS":"WARNING"}]},{"JOBNAME":"Upload Interface Error and Job Output File to Universal Content Management","JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader","REQUESTID":"44359817","STATUS":"SUCCEEDED"}],"SUMMARYSTATUS":"ERROR","DOCUMENTID":"69220945"}</resultMessage>
            </ns0:onJobCompletion>
        </env:Body>
    </env:Envelope>

😣

What an ugly response. Even though we made a REST call, we got a SOAP response. If you dig through the response, you can see the resultMessage tag contains some JSON that looks useful.

With the power of APEX and the Oracle database, we can easily parse the JSON out of the XML:

SELECT x.request_id
,      x.state
,      x.jobs_json 
FROM   XMLTABLE(
            XMLNAMESPACES(
                'http://schemas.xmlsoap.org/soap/envelope/' AS "env",
                'http://xmlns.oracle.com/scheduler' AS "ns0"),
            '/env:Envelope/env:Body/ns0:onJobCompletion'
            PASSING XMLTYPE(l_xml_payload)
            COLUMNS
            jobs_json     CLOB          PATH 'resultMessage',
            state         VARCHAR2(25)  PATH 'state',
            request_id    NUMBER        PATH 'requestId'
        ) x;

The resultMessage tag contains JSON like this:

{
  "JOBS": [
    {
      "JOBNAME": "Load Interface File for Import",
      "JOBPATH": "/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",
      "DOCUMENTNAME": "AJE_5000195_20240621.zip",
      "REQUESTID": "44359812",
      "STATUS": "SUCCEEDED",
      "CHILD": [
        {
          "JOBNAME": "Transfer File",
          "JOBPATH": "/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",
          "REQUESTID": "44359813",
          "STATUS": "SUCCEEDED"
        },
        {
          "JOBNAME": "Load File to Interface",
          "JOBPATH": "/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",
          "REQUESTID": "44359814",
          "STATUS": "SUCCEEDED"
        }
      ]
    },
    {
      "JOBNAME": "Import Journals",
      "JOBPATH": "/oracle/apps/ess/financials/generalLedger/programs/common",
      "REQUESTID": "44359815",
      "STATUS": "WARNING",
      "CHILD": [
        {
          "JOBNAME": "Import Journals: Child",
          "JOBPATH": "/oracle/apps/ess/financials/generalLedger/programs/common",
          "REQUESTID": "44359816",
          "STATUS": "WARNING"
        }
      ]
    },
    {
      "JOBNAME": "Upload Interface Error and Job Output File to Universal Content Management",
      "JOBPATH": "/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",
      "REQUESTID": "44359817",
      "STATUS": "SUCCEEDED"
    }
  ],
  "SUMMARYSTATUS": "ERROR",
  "DOCUMENTID": "69220945"
}

We can now parse this JSON to determine if any jobs Oracle Fusion Cloud ERP ran during the import process failed and take the appropriate action.

Conclusion

The code snippets in this post illustrate how Oracle APEX, Oracle REST Data Services (ORDS), and the Oracle Database can be used to build enterprise integrations. Again, It is worth noting that this is a real integration built for a real client.

⚠

In addition to what I have shown you, you must include robust error handling and logging capabilities to track errors in a production solution.

Interfacing Data from APEX to Oracle Fusion Cloud ERP over REST