Develop Your Functions

To process the data, you will need three independent code units: a transform function, a load function, and a callback function.

These functions have been implemented using Oracle Functions and written in Python. Oracle Functions are perfectly suited to this job because data loading could be something which happens with limited frequency (perhaps once or twice per hour or per day). Using Oracle Functions is advantageous because the function is only called when there is something to do and once it has finished processing, then it shuts down. Additionally there is no operating system, routing, or other server for you to maintain; this is a serverless architecture. For this example implementation we chose Python over other language options because it is easily understood and exendable, and there is not a strict performance requirement for this data loading job.

Our three functions are :

• A transform function for translating the data file from a simplified JSON format to the Oracle Cloud ERP Specific Zip file
• A load function for loading the file into Oracle Cloud ERP
• A callback function for handling the response from Oracle Fusion

Each of these functions will source its data from the OCI Storage bucket, process it and then put it in another bucket.

Use OCI Storage Buckets

You first need to get the data into the OCI Cloud so you can process it efficiently. Oracle OCI has an ideal option for this called Oracle Cloud Infrastructure Object Storage Buckets. Buckets provide plentiful storage capacity and several options for uploading files, including a CLI, a REST API, and the admin console.

The transformed data to be loaded is similar to this JSON file:

"invoices": [ 
{ 
    "invoiceId": "222290", 
    "businessUnit": "US1 Business Unit", 
    "source": "External", 
    "invoiceNumber": "111190", 
    "invoiceAmount": "4242.00", 
    "invoiceDate" : "2019/02/01", 
    "supplierName": "Staffing Services", 
    "supplierNumber" : 1253, 
    "supplierSite" : "Staffing US1", 
    "invoiceCurrency": "USD", 
    "paymentCurrency": "USD", 
    "description" : "New Invoice from global Angels", 
    "importSet": "AP_Cloud_Demo", 
    "invoiceType": "STANDARD", 
    "paymentTerms": "Immediate", 
    "termsDate": "2019/02/01", 
    "accountingDate": "2019/02/01", 
    "paymentMethod": "CHECK", 
    "invoiceLines": [ 
                    { 
                        "amount": "200", 
                         "description" : "Invoice Line Description" 
                    }, 
                    { 
                        "amount": "300", 
                        "description" : "Invoice Line Description2", 
                        "invoiceQuantity": "10", 
                        "unitPrice": "5" 
                    }] 
}]

This is much simpler than the native Oracle Cloud ERP format FBDI import zip which comprises two CSV files, similar to these CSV excerpts:

222284,US1 Business Unit,External,111184,4242.00,2019/02/01,Staffing
              Services,1253,Staffing US1,USD,USD,New Invoice from global
              Angels,AP_Cloud_Demo,STANDARD,,,,,,Immediate,2019/02/01,,,2019/02/01,CHECK,Standard,#NULL,,,,,,,,,,,#NULL,,,,,#NULL,#NULL,,,,21,#NULL,,,#NULL,#NULL,,,,#NULL,,,,,,,,,,,,,,,N,N,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,END 

222284,1,ITEM,200,,,,Invoice Line
              Description,,,,,,,,,,,,,N,,#NULL,2019/02/01,,,,,#NULL,,,,,,,,,,,,,,,,,#NULL,,,N,1,,,N,,,,,,,N,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,END

The rest of this solution will also use additional buckets which to store the file as it is being processed. As the process moves along, the file moves along to the next bucket. As soon as the file is loaded into Oracle Cloud ERP the file is renamed to include the ERP data loading JOBID.

Description of the illustration load-data-serverless-overview.png

1. Create storage buckets in your OCI instance.
2. Upload the resulting JSON file to a storage bucket by using the following CLI command:

   oci os object put -ns mynamespace -bn JSONIncoming --name mysimpliedJSONInvoice.json --file mysimpliedJSONInvoice.json 

Create a Transform Function

When loading data into Oracle Fusion a mandatory step is to transform the input data into the correct CSV format and then zip up the files into a single ZIP file. In this example we demonstrate this transform step by accepting a simplified JSON data structure which we then transform into the CSV format required by Oracle Cloud ERP. After this we then wrap the files into a single ZIP file ready for uploading.

When doing this manually, you normally need to download Excel Macro files, which they can populate and generate the zip files. (See Explore More for a link.) Instead, you can perform the transform using a Function that executes some Python code.

Description of the illustration load-data-serverless-transform.png

The transform function gets the JSON data from the Incoming JSON bucket, transforms it using code into a CSV, zips it up, and then stores it in an Incoming ZIP bucket. The transform function uses a template approach to generate the CSV file. Interacting with OCI files is straightforward: you can put and delete objects, and if you need to copy large files you can run this asynchronously. This example uses small files so asynchronous copying is not used here.

put_object_response = object_storage_client.put_object(namespace, param_processing_bucket_name, data_file_name + "_ERPJOBID_" + erp_job_id, data_file.data.content)

Create a Load Function

Loading data into Oracle Cloud ERP is straightforward. For this example, we use the standard importBulkData REST API.

Here is a snippet of code showing how you can load data using the open source requests REST API:

erp_payload = {
        "OperationName": "importBulkData",
        "DocumentContent": base64_encoded_file,
        "ContentType": "zip",
        "FileName": data_file_name,
        "JobName": jobname,
        "ParameterList": paramlist,
        "CallbackURL": param_fa_callback_url,
        "NotificationCode": "10"
    }
    logging.info(f'Sending file to erp with payload {erp_payload}')
    result = requests.post(
        url=param_erp_url,
        auth=param_erp_auth,
        headers={"Content-Type": JSON_CONTENT_TYPE},
        json=erp_payload
    )
 
    if result.status_code != 201:
        message = "Error " + str(result.status_code) + " occurred during upload. Message=" + str(result.content)
        raise FA_REST_Exception("Error " + message)

About Linking Objects Together Using Events

When a file is uploaded into Oracle Cloud Infrastructure Object Storage Buckets it is possible to configure the Bucket to emit an event when CRUD operations are performed. This event can be captured by the Oracle Cloud Infrastructure Events service.

The creation of rules based on object events is declarative. For example, you can create a rule that implements logic such as "if a new file is created in bucket called INCOMING_JSON then call the erp-transform serverless function." You can configure a bucket to Emit Object Events as an option in the Features section of the Bucket Information tab.

Description of the illustration bucket-information.png

Here's an example of a rule based on an Object Storage event emitted by the inbound JSON Bucket:

Description of the illustration event-rule.png

Events allow you to tie operations which happen to a storage Bucket (or other objects) to Function calls for processing. Event-based functionality allows you to create a string of operations which are triggered by events in a truly decoupled manner.

This image shows the new operational flow with events implemented:

Description of the illustration load-data-serverless-events.png

Each Bucket has been marked to emit an event and the event service captures this event and invokes the appropriate Oracle Function. When invoking the function the event service passes an event payload which in turn invokes the function. Within the event payload is the type of event emitted, and in this case also the Bucket and file name.

Secure Passwords Using Oracle Vault

The LoadToSaaS Function needs to be able to authenticate with Oracle Cloud ERP. Authentication requires the username and password for the integration user. The Oracle Cloud Infrastructure Vault service provides a secure place to store encrypted passwords.

You can store the user name as a Functions Configuration variable, but its not secure practice to store the password there. Oracle OCI provides Oracle Cloud Infrastructure Vault as an ideal solution. Within code you can query the vault, and extract the secret. Once retrieved you can use this secret to execute an authenticated REST call to Oracle Cloud ERP. Note that the OCID of this secret key does not change if you update it, so you can update passwords safely without breaking the solution.

Description of the illustration vault-information.png

From the Vault Details screen, under Resources, select Secrets to create new secrets.

The following example Python code can be used to extract the secret:

signer = oci.auth.signers.get_resource_principals_signer()
secret_client = oci.secrets.SecretsClient(config={}, signer=signer)
secret_response = secret_client.get_secret_bundle("ocid1.vaultsecret.oc1.phx.xxxxxxxx")
base64_secret_bytes = secret_response.data.secret_bundle_content.content.encode('ascii')
base64_message_bytes = base64.b64decode(base64_secret_bytes)
print("secret value is " + base64_message_bytes.decode('ascii'))

Create a Callback Function

When data is loaded into Oracle Fusion Applications, a callback to the client is sent with a status payload. You can create a callback function to receive this information.

When data is loaded into Oracle Cloud ERP, the service performs the following (simplified) steps:

1. Data is loaded into the Fusion UCM Repository
2. An ESS job transfers the file contents into ERP integration tables
3. An ESS job imports data into transactional tables
4. A report is generated, showing which rows were inserted where in the UCM repository
5. A callback to the client is sent with a status payload

The final function which gets called in this example solution is the one which implements the client side, or the receiving side of this callback from Oracle Cloud ERP. The callback is an HTTP call with XML data. Oracle Functions are not REST endpoints, so to be able to receive the GET HTTP call from Oracle Cloud ERP you need to front end the Function with API Gateway.

As before, this is a declarative operation that involves mapping the endpoint and resource URL to a Function. In the Create (or Edit) deployment wizard, enter route information in the Routes step. This image shows an example:

Description of the illustration callback-route.png

This erp-callback function is triggered when Oracle Cloud ERP issues a call back. The function decodes the XML payload and extracts out the JOBID and Status. With the JOBID you can determine which file in the Processing bucket the event was for and then move the file from the Processing bucket to either the Success or Error bucket. Importantly, in ERP callbacks, a succeeded job does not necessarily mean that the data was loaded into Oracle Cloud ERP: it may have been duplicate data, an unknown business organisation, etc. One enhancement you could implement to the pattern demonstrated here would be for this callback Function to download the report from UCM and introspect it to determine whether all rows were inserted successfully.

Extend the Solution by Subscribing to Notifications

The Oracle Cloud Infrastructure Notifications service allows you to create topics where you can post messages. These topics can have subscribers which listen to a message and then send it out somewhere.

With the flow complete you can leverage the benefits of integrating with OCI. Because the flow was designed as micro services, and you are using native services like events, you can leverage additional features and services such as Oracle Cloud Infrastructure Notifications. In this sample code the notifications are emails but the subscriber could instead be another Function, a PagerDuty channel, or some other mechanism. The loosely-coupled architecture lends itself to be extended in many ways. For example you could add a function which inserts data into a Grafana dashboard, and the notification could call this function with some data you want to display in Grafana.

For example, here is a code snippet of a helper function which demonstrates how to send a notification to a topic (by using its OCID):

def publish_ons_notification(topic_id, msg_title, msg_body):
    try:
        signer = oci.auth.signers.get_resource_principals_signer()
        logging.info("Publish notification, topic id" + topic_id)
        client = oci.ons.NotificationDataPlaneClient({}, signer=signer)
        msg = oci.ons.models.MessageDetails(title=msg_title, body=msg_body)
        client.publish_message(topic_id, msg)
    except oci.exceptions.ServiceError as serr:
        logging.critical(f'Exception sending notification {0} to OCI, is the OCID of the notification correct? {serr}')
    except Exception as err:
        logging.critical(f'Unknown exception occurred when sending notification, please see log {err}')