Thursday, November 26, 2020

#815 OIC --> Netsuite to Object Storage

 simple use case here - customer wants to extract data from Netsuite and store it in Object Storage -



Step 1 is to create a bucket in object storage. This will be where the data will be stored.

My colleague Daniel T. has a great post describing how to do that here 
A big thanks to my colleague Ravi P. for the Object Storage setup.

Step 2- Create the REST connection to Object Storage Service in OIC.














Tenancy OCID can be found here -







User OCID can be found here - 








Private Key is what you uploaded to Object Storage Service

Fingerprint can be found here - 











Step 3 - Now to the Integration itself - 

I use the Netsuite adapter to get customers whose name include "Dublin" -

I have a lot of such.

I use the Search option in the wizard and configure the mapping as follows -























operator is set to "contains"

I loop over each customer returned and use the STAGE FILE action to write customer data (company name, date created) to a "nn.txt" file in the OIC VFS (Virtual File System)










Note I also assign the file reference to a variable. I can use this later when writing to object storage.






Once all customers have been processed - 






I use the Object Storage REST connection (StoreData) - configured as follows - 



 











Note the resource uri - 





Check out the full REST api doc for Object Storage Service here

Mapping is simple - I just pass the file reference from the variable - 





Step 4 - Test 














5 Dublin customers processed -

I validate the result in Object Storage -



Tuesday, November 24, 2020

#814 OIC - DB Adapter -> Master/Detail Insert

 Simple example here, sure aren't all of my examples simple?

As Schiller says - 

Man only plays when he is in the fullest sense of the word a human being, and he is only fully a human being when he plays.

So without further ado - 

here are my DB tables - nc_master and nc_detail







Here is the Integration - 














I have a simple REST Trigger based integration with the following payload -











DB Adapter Invoke - configured as follows - 















Mapping - 







Testing - 



Check in SQLPLUS - 
 

Thursday, November 19, 2020

#813 - OIC - Complex SQL processing

How to decide if an SQL statement is complex?

Easy for me - if it goes beyond the basic SELECT x FROM y WHERE ... then it's complex for me.

Here I have the following statement that retrieve data from my CUSTOMERS table - 

SELECT CUST_NAME, CREATED_DATE, MODIFIED_DATE, DEFAULT_ADDRESS, COMPANY_SIZE FROM CUSTOMERS WHERE CREATED_DATE > #lastDate ORDER BY CREATED_DATE DESC OFFSET #offset ROWS FETCH FIRST 1000 ROWS ONLY









I execute the SQL in SQLPLUS - 




Looks good - now to trying this in OIC -














Essentially a REST trigger.

Then the DB adapter (GetCustomers) - configured as follows -












The Mapping - as you can see, I simply hard code test for test purposes -








I then loop over each customer and simply LOG the customer name -


















I finally return all customer names.

Now to testing -



Monday, November 16, 2020

#812 OIC Nov 20 Release - Documentation etc.

 Starting point, as always, the What's New doc, available here












Next, the official ORCL integration blog, available here











Enjoy the November Release. Looking forward to your feedback.



Sunday, November 15, 2020

#811 - OIC Nov 20 Release - Netsuite adapter New Features

 Searching on Item

Item and Transaction are super records which can have many constituent types.
For example -
Inventory Items - physical products that can be bought or sold.
Assembly Items - physical components that need to be assembled into a finished product.
There are also non-inventory items e.g. for digital assets etc.

















Before November - 














Now - 














In this simple example I will select an Inventory item, based on its internalId.  














The mapping is as follows - Note I am filtering on mapped fields -








 


operator is set to anyOf as this is a MultiSelectFieldOperator

I activate and test - btw. another new November 20 Release feature is that the body you use when testing is saved between tests -












The only response field I map is the display name.






Now a quick look at the structure returned by Netsuite -















Note the different item types, listed under recordList.

Now I test for this Assembly Item with the internalId 724.












I don't need to change the request mapping -









The Response mapping has been changed -





I activate and test -