Interfacing Data from APEX to Oracle Fusion Cloud ERP over REST Aug 29, 2024 · 8 min read 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. 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. Read on web Add a comment © 2024 Cloud Nueva Blog (Oracle, APEX & ORDS) 8 The Green, Ste 300, Dover, Delaware, 19901 Unsubscribe Write on Hashnode