X

Best Practices from Oracle Development's A‑Team

  • March 1, 2021

A Function for Processing FBDI Job Log File

Introduction

This blog provides a set of sample code in Node.js to demonstrate an Oracle function for extracting information such as the status and failed records from an FBDI job log file.

FBDI Job Log

File Based Data Import (FBDI) scheduled jobs have been widely used by Oracle SaaS customers for bulk importing into Oracle SaaS. This is because this bulk operation is much more efficient than processing each record individually. In many cases, FBDI is the only viable way to satisfy the volume import requirement. One downside of this bulk approach compared to single record-based processing is that we do not have the immediate status response of each record import. But this information is not all lost. Records failed to import are logged in an FBDI job process log file. We can download a job log file, view and manually extract the status information we need.

Oracle SaaS also provides SOAP-based APIs for downloading the log file based on a process(job) ID. The goal of this blog is to provide a working sample code in Node.js to show that we can automate the information extraction from the log files.

Oracle Function

As we can see in the source code shown below, this application is small, self-contained, and narrowly focused on performing one specific task. Oracle Function Cloud Service is an ideal deployment platform for applications like this. Oracle Function takes care of all the necessary infrastructure work and security (with the API Gateway) and allows customers to focus on functional development.

Before we can deploy functions to Oracle Function Service, we must configure the environment. Details can be found in Preparing for Oracle Functions.

For details on creating a basic Oracle function, please refer to Creating, Deploying, and Invoking a Helloworld Function.

Create an Oracle Function File Structure

To get started, we will need to have a set of basic function file structure created by running the command:

fn init --runtime node fbdi-log

We should have a folder named fbdi-log created, which looks like the following:

All files mentioned below should be created under this folder. All dependant modules should be installed in the same folder as well.

Why Node.js

Oracle Function supports all major programming languages. The main reason why Node.js is chosen is that the same code can be easily modified and turned into a custom component for Oracle Digital Assistant (ODA). Such a custom component can enable a Chatbot application to answer a user question such as "what is the status of my FBDI job?". There will be a separate blog on this topic.

Dependent Modules

This code sample requires the following Node.js modules which can be installed via "npm install" if not already included.

  • @fnproject/fdk
  • fs
  • unzipper
  • readline
  • urllib-sync
  • crypto

Code Overview

Due to Oracle company policy, only the most critical code snippets are shown in each code module. A fully functional sample code is in the process of being published to Github. A Github URL will be posted here once available. I am reachable at siming.mu@oracle.com for any questions.

At a high level, the implementation includes the following steps:

  • Extract required information from the input. Information such as SaaS host, process ID, username, and password.
  • Call SaaSHost / fscmService/ErpIntegrationService with type downloadESSJobExecutionDetails
  • Extract the zip file attached to the response message
  • Unzip the zip file to get the log file
  • Read the log file line by line to extract overall result and failed records by using RegEx

Web Service Response

To download the FBDI log file, we send a SOAP request to the endpoint at /fscmService/ErpIntegrationService with a type of downloadESSJobExecutionDetails. The request payload is straightforward and shown in the main code block section. The response is a multipart/related message. A sample response is shown in the image below.

The first part of the message contains a generic XML message that we are not interested in. The second part contains the zip file in binary format. This is the part we want to extract.

Process Multipart Related Response Message

The code file MultipartRelated.js was originally created by Cristian Salazar named parse-multipart. The original source was created for parsing Multipart/form-data. It has been modified here to process multipart/related messages. The Parse function defined in the file takes an HTTP request raw data and a multipart boundary string and returns a JSON object. For example, the sample response message mentioned above is parsed into the following format. Each object in the outermost array represents a message part in the response.

 [{ "Content-Type":"application/xop+xml;charset=UTF-8;type=\"text/xml\",
    "Content-Transfer-Encoding":"8bit",
    "Content-ID":"<03bcc0b4-ca21-4d5b-aaf0-ca41f5f6aa94>",
    "data": [the XML message in an array of bytes] },
  { "Content-Transfer-Encoding":"binary",
    "Content-ID":"<035b0e9c-2057-4b77-a529-9892b399634e>",
    "data":[the zip file binary in an array of bytes] }
 ]

MultipartRelated.js

Due to Oracle company policy, this module is in the process of re-development. The snippets of the new module will be posted soon.

Code snippets coming soon.

HttpClientUrllib.js

This is a synchronous HTTP client module that checks for multiple/related response and parses it accordingly.

'use strict';

/*
    props = {
      requestURL:  {required: true, type: 'string'},
      method: {required: true, type: 'string'},
      requestBody: {required: false, type: 'string'},
      username: {required: false, type: 'string'},
      userPassword: {required: false, type: 'string'},
      accessToken: {required: false, type: 'string'},
      contentType: {required: false, type: 'string'},
      headers: {required: false, type: 'string'}, // must be a map object or a stringified map object
    };
*/

function invokeHttpClient(props) {
      ...

      var options = {
        method: method.toUpperCase(),
        headers: {
            'Content-Type': (contentType)? contentType : 'application/json',
            'Accept': '*',
            'Accept-Charset': 'utf-8',
            'User-Agent': 'other'
        }
      };

      if (requestBody) {
        options.data = requestBody;
      }

      var res = urlLib.request(reqUrl, options);

      // check to see if the response is multipart
      var resContentType = res.headers['content-type'];
      if (!resContentType && !resContentType.startsWith('multipart')) {
        response['data'] = res.data;
        return response;
      }

      // Process multipart response. 
      // The response object is in the form of
      // [{header1:value1,header2:value2,...,data:buffer}, {},...]
      var boundaryMark = 'boundary="';
      var startIndex = resContentType.indexOf(boundaryMark);
      var endIndex = resContentType.indexOf('"', startIndex + boundaryMark.length);
      if (startIndex > 0 && endIndex > 0) {
        var boundary = resContentType.substring(startIndex + boundaryMark.length, endIndex);
        var parts = parseMultipart.Parse(res.data, boundary);
        response['data'] = parts;
        return response;
      }
};
 

FbdiLog.js

This is the main code module that implements the download and extraction logic.

const { constants } = require('crypto');
const defaultFilePath = '/tmp/';

async function getSalesOrderImportStatus(props) {
  // set up properties for calling the HttpClient CC
  props = {}
  props['requestURL'] = host + '/fscmService/ErpIntegrationService';
  props['username'] = username;
  props['userPassword'] = userPassword;
  props['accessToken'] = accessToken;
  props['method'] = 'POST';
  props['contentType'] = 'text/xml';
  props['requestBody'] = '' +
                          '' +
                          '' + processID + '';

  // Calling the web service and get response json object back in the form of
  // [{header1:value1,header2:value2,...,data:buffer}, {},...]

  var response = httpClientUrllib.invoke(props);
  retRes['callStatus'] = 'successful';

    // convert buffer to zip file
    var buffer = Buffer.from(response['data'][1]['data']);

    // save the zip file to storage
    var buffer = Buffer.from(response['data'][1]['data']);
    fs.writeFileSync(zipFileName, buffer, 'binary');

    // unzip the file in th storage
    await unzipLogFile(zipFileName);

    // read the log file
    fs.accessSync(logFileName, fs.constants.F_OK);
    var jobStatus = await processLineByLine(logFileName);
    retRes['jobStatus'] = jobStatus;

    return retRes;
}

function unzipLogFile(zipFileName) {
    // unzip the file in th storage
      var promise = new Promise((resolve, reject) => {
        fs.createReadStream(zipFileName).pipe(
          unzipper.Parse()
        ).on('entry', function (entry) {
            const fileName = defaultFilePath + entry.path;
            const type = entry.type; // 'Directory' or 'File'
            const size = entry.vars.uncompressedSize; // There is also compressedSize;
            entry.pipe(fs.createWriteStream(fileName));
        }).on('close', () => {
          resolve();
        });
      });
      return promise;
}

async function processLineByLine(logFileName) {
  var jobStatus = {};
  var orderStatusArray = [];
  jobStatus['failedRecords'] = orderStatusArray;

  var promise = new Promise((resolve, reject) => {
      var readStream = fs.createReadStream(logFileName);
      var rl = readLine.createInterface({
        input: readStream,
        crlfDelay: Infinity
      });
    
      rl.on('line', (line) => {
        var orderStatus = failedOrderRegExp.exec(line);
        if (orderStatus) {
          var tempOrderStatus = {};
          tempOrderStatus['orderID'] = orderStatus[1];
          tempOrderStatus['errorMessage'] = orderStatus[2];
          orderStatusArray.push(tempOrderStatus);
        }
        else {
          var overallStatus = overallStatusRegExp.exec(line);
          if (overallStatus) {
            jobStatus['overallStatus'] = overallStatus[1];
          }
        }
      }).on('close', () => {
            resolve();
      });
  });

  await promise;

  return jobStatus;
}

Sample Output JSON Object

Use the following log file as an example,

Batch Name: smu2
Source System: null
Order Number: null
Sold To Customer Name: null
Sold To Customer Number: null
createdWithinLastNDays: null
Allow Auto Purge: Y
subBatchName: 2557275-SUB_BATCH-1
Order management did not import source order smu_sample_source_transaction_id_011 because of the following error: Source system LEG13 does not exist. Define fulfillment or order capture as a source system to interact with Oracle Fusion Distributed Order Orchestration..
Order management did not import source order smu_sample_source_transaction_id_021 because of the following error: An order was not created because the values 100001,300000047367162,100000000395811 provided for the combination of attributes CUSTOMER_ITEM_ID,INVENTORY_ITEM_ID,SOLD_TO_PARTY_ID are invalid for the source order with the following details: source order smu_sample_source_transaction_id_021, source order line 1, source order schedule 20001. Check the attribute value, and resubmit the order..
Order management did not import source order smu_sample_source_transaction_id_031 because of the following error: An order was not created because the values 100001,300000047367162,100000000395811 provided for the combination of attributes CUSTOMER_ITEM_ID,INVENTORY_ITEM_ID,SOLD_TO_PARTY_ID are invalid for the source order with the following details: source order smu_sample_source_transaction_id_031, source order line 1, source order schedule 30001. Check the attribute value, and resubmit the order..
Attribute Party Name for the SHIP_TO address use type was populated for address type BILL_TO in order smu_sample_source_transaction_id_041, line 401, with schedule 4001.
Order management did not import source order smu_sample_source_transaction_id_051 because of the following error: Source system LEG14 does not exist. Define fulfillment or order capture as a source system to interact with Oracle Fusion Distributed Order Orchestration..
Order management did not import source order smu_sample_source_transaction_id_06 because of the following error: Source system LEG14 does not exist. Define fulfillment or order capture as a source system to interact with Oracle Fusion Distributed Order Orchestration..
Order management did not import source order smu_sample_source_transaction_id_07 because of the following error: Source system LEG14 does not exist. Define fulfillment or order capture as a source system to interact with Oracle Fusion Distributed Order Orchestration..
Number of orders that were imported: 0. Check the run time user interface for processing status.
Number of orders that did not pass validation: 7. Some of these orders may be available in the order entry user interface.
Overall Result : ERROR

A successful execution results in the following JSON object:

{
   "callStatus":"successful",
   "jobStatus":{
      "failedRecords":[
         {
            "orderID":"smu_sample_source_transaction_id_011",
            "errorMessage":"Source system LEG13 does not exist. Define fulfillment or order capture as a source system to interact with Oracle Fusion Distributed Order Orchestration.."
         },
         {
            "orderID":"smu_sample_source_transaction_id_031",
            "errorMessage":"An order was not created because the values 100001,300000047367162,100000000395811 provided for the combination of attributes CUSTOMER_ITEM_ID,INVENTORY_ITEM_ID,SOLD_TO_PARTY_ID are invalid for the source order with the following details: source order smu_sample_source_transaction_id_031, source order line 1, source order schedule 30001. Check the attribute value, and resubmit the order.."
         },
         {
            "orderID":"smu_sample_source_transaction_id_051",
            "errorMessage":"Source system LEG14 does not exist. Define fulfillment or order capture as a source system to interact with Oracle Fusion Distributed Order Orchestration.."
         },
         {
            "orderID":"smu_sample_source_transaction_id_07",
            "errorMessage":"Source system LEG14 does not exist. Define fulfillment or order capture as a source system to interact with Oracle Fusion Distributed Order Orchestration.."
         }
      ],
      "overallStatus":"ERROR"
   }
}

If something goes wrong, the resulting JSON object should look like this:

  {
    "callStatus": "failed",
    "message": "something wrong"
  }

func.js

In this module, we will connect our main implementation module, FbdiLog.js, to the function deployment.

const fdk=require('@fnproject/fdk');
var fbdiLog = require('./FbdiLog.js');

fdk.handle(fbdiLog.getSalesOrderImportStatus);

Function Service Storage

Oracle Function Service comes with default file storage. The writable folder is at /tmp, which is used in this sample code as a temporary storage for the zip and log files. Please be aware that there is a limitation on the storage capacity. You can find details in Accessing File Systems from Running Functions.

Invoking the Function Service

Documentation on invoking Oracle functions can be found at Invoking Functions. The easiest way to programmatically call an Oracle function service directly is to use a language-specific OCI SDK. The SDK takes care of the signature and encryption required by Oracle Function. Below is a sample Python client for invoking the fbdi-log function.

auth = Signer(
tenancy=config['tenancy'],
user=config['user'],
fingerprint=config['fingerprint'],
private_key_file_location=config['key_file'],
pass_phrase=config['pass_phrase']
)

endpoint = 'https://xxxxxxxx.us-ashburn-1.functions.oci.oraclecloud.com/20181201/functions/ocid1.fnfunc.oc1.iad.aaaaxxxxxxxxxxxxxxxxxxxxxxxxxxxrwa/actions/invoke'
body = {}
body['host'] = 'https://saas-cloud-host'
body['processID'] = '1234567'
body['username'] = 'username'
body['userPassword'] = 'userPassword'

response = requests.post(endpoint, json=body, auth=auth, headers={'Content-Type':'application/json'})
#response.raise_for_status()

pp.pprint(response.json())

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha