#852 OIC May 21 Release New Features - New Recipes - FTP to Netsuite File Cabinet

The May 21 release includes many new recipes - let's look at some of them - today - FTP to Netsuite File Cabinet. 

The use case here is very simple - I have files on an ftp server that need to be copied to a folder in the Netsuite File Cabinet. Many customers need to upload files, attach them to records and then organize them in the Netsuite File Cabinet. The example here, again, is very simple - but does provide the basis for more complex implementations.

The recipe is contained in the following package -   

The only pre-requisites for this recipe are -

• FTP connection in OIC
• Netsuite connection in OIC
• Target folder created in the Netsuite File Cabinet

I have all the pre-reqs - my connections are called AA-FTP and AA-Netsuite.

My Netsuite connection uses the following wsdl url - it is important to use the v2019_1 version of the wsdl.

Once I import the recipe, I can start configuring - 

Step 1 - update the ftp and netsuite connections

This is very easy using the OIC Configuration editor - 

all I need to do is click the Replace icon.

So now let's look at the integration itself - 

Rudimentary error handling has also been implemented - 

Simple enough - gets the file from an ftp directory via the OIC ftp adapter.

Here is my ftp directory - 

The name of the directory/file is set as follows -

So let's look at the Integration Properties - 

I set the ftp directory value accordingly -

Now to the internalId value - this is the internal id of my Netsuite File Cabinet.

I created a new folder - id = 801

I update the relevant integration property with this value - 

That was it - I now copy a couple of files to the ftp directory -

 

I then activate and test the integration and see the following error -

Ok, so the role is specified in the Netsuite connection definition also needs this permission - so back to Netsuite - 

 

I re-test and then check the file cabinet in Netsuite - 

Now let's look at some other interesting fields, when creating the file -

attachFrom - valid values are _computer (for local files) or _web 

isPrivate - If you want to be the only person 

isOnline - If you want the file to be accessible externally - file will be accessible via an url.

owner - The owner of the file is a RecordRef , i.e. points to a Netsuite business object - 

 

I augment the recipe, by adding an explicit call to Netsuite in order to attach the file to my customer -

Here is the target payload for the Attach Invoke -

I will use the following customer - id - 16987

The File internal id source field - 

I delete the 2 files from the File Cabinet and re-test, then validate in Netsuite -
File have been attached to the customer.

 

#851 OIC May21 Release - New Features Overview from Lilly

Lilly has done it again - the May release is upon us - a full list of what's coming is available here

Well done Lilly - woof, woof!

#850 OIC May 21 Release New Features - ATP Bulk Data Import

To begin, a BIG thank you to my colleague Prakash M. here.

This scenario covers the bulk load of data into ATP from an ftp server. I'v got a file full of orders and need to get them into my DB ORDERS table asap. With the May 21 release, the OIC ATP has been powered to do just this. 

The diagram below shows the flow -

OIC reads the file via the ftp adapter. Then the ATP adapter does the magic - stores the file in my OCI Object Storage Bucket and then loads the data from there into ATP via the DBMS_FILE.COPY_DATA procedure. 

 

So here are all the parts - I use the bucket - bucketNC.

OCI Object storage.

Here is my DB table - 

Here is the orders data - this is a .csv file 

So let's get those orders from the csv file into the ORDERS table.

Firstly to OCI...

The ATP adapter has been augmented to allow connectivity over OCI - there is a new security option available - JBDC With OCI Signature. The configuring of this option will require some information from your OCI setup - 

Here are the keys I will use - please refer to my previous post on OCI for details on how to generate keys.

apart from the private key, I will also require the following -

• Tenancy OCID
• Compartment OCID
• User OCID
• API Fingerprint

Now to OIC... 

ATP adapter configuration -

I save and test - 

I now leverage this connection in an integration  -

Here is the configuration of the ATP Invoke - BulkLoadOrders - 

Now to define the input file format - 

I leave the Record Delimiter blank - please see this post from Prakash for a detailed description of these options as well as the Advanced -

Now to the mapping - here I simply pass the file reference from the ftp read - 

Note the format field in the target - 

This allows me to make formatting changes on the fly.

Now essentially that's it - 

I activate and test - 

Note the OPERATION_ID returned - more about that later.

I check out the ATP Table - 

Just to prove to those sceptics out there that OCI Storage is being used...

I activate and test again - deleting the DB rows beforehand.

I check out the Bucket - 

So file is in the bucket, and the orders in the DB - 

check out the DB Schema - a couple of new tables appear - 

Check out the following in the DB - 

select type, status, table_name, rows_loaded, file_uri_list 

from user_load_operations 

where id = 5;

I also did a load test with a file containing 3 million orders.

The 3 million orders were loaded within 2 minutes.