Thursday, October 8, 2020

#803 OIC 4 Netsuite - Polling for New / Updated Customers


A simple use case here - 

poll for new/updated Netsuite customers and update a custom on-premise CRM app.

Here I will write directly to the custom app DB, CUSTOMER table.

The DB table is basic - 

First step is to work out the fields to filter.

I created a new customer in Netsuite and looked at the xml representation.

I found the following -


But net, net - when I create a new Customer in Netsuite both of the above fields are set to the same dateTime value.

Ergo, lastmodifieddate will be the only value on which I need to filter.

Here is an example - 

I look at the xml representation - 

So here's my business logic -

Have a scheduled job in OIC - with a schedule date parameter - dateLastRun

Do a search in Netsuite for customers where the lastmodifieddate is after dateLastRun.

For each of these customers, do an upsert into the Oracle DB CUSTOMERS table.

Update the dateLastRun parameter with the current dateTime.

The Anatomy of the Integration

Now, step by step - 

Schedule parameter definition -

Get Current Timestamp invokes the following JS function -

ts is set to the schedule -> start time -

z is set as follows - 

Here is the Lookup - 

Here is the Search - 

I can set the number of records to be fetched here -

The only field I need to map is lastmodifieddate -

operator is set to "after"

I then Log the number of new/Updated customers found -

I do an UPSERT in the loop -

and that's it.

Now to Testing this - 

DB table is empty -

I start the schedule off from Oct 1st.

I get 5 entries in the DB -

Now I go and edit Dublin Philosophy and change the company size

I re-run the integration - 

great stuff agus araile!

Wednesday, September 30, 2020

#802 - Creating Journal Entries in Netsuite via OIC

 My first real foray into the magical world of bookkeeping.

What is a journal entry?

Apparently journal entries are used to record business transactions. And remember the books must always balance - so here's an example for a sales entry -

I sell my Hare of the Dog T-Shirt on credit, so I need to debit accounts receivable and credit sales.

Ask your accountant, if you need to know more.

Net, net, I can create a Journal Entry in Netsuite, via the OIC Netsuite adapter.

Again, I am not a Netsuite or bookkeeping expert, so my first step is to create a Journal Entry in Netsuite itself and then look at the xml version.

Now I have an idea of the structure. 
I create a new app driven integration in OIC with a REST Trigger - request payload as follows -

This seems to me the minimum required.
Credit account 2 and debit account 4. 
I got these account numbers form the xml representation of the Netsuite Journal Entry.

I map as follows - 

I put an if before mapping debit/credit.

Now to testing - 

looks good, except for the Permissions Error -

I give myself this permission in Netsuite 

I re-run the integration -

Validate in Netsuite -

Tuesday, September 29, 2020

#801 Oracle Integration (OIC) Recipes - HCM Directory Synchronisation


The 2 previous posts dealt with Technical Accelerators, now to the recipes.
Recipes are best practice implementations of common use cases. They provide a very quickstart to implementing these.

Let's look at the above recipe; as you can see, I have already installed it.


It installs as a package - 

This is what it contains - 

Here is the Integration - I can either clone and edit or edit directly -
I clone it -

Note the Keywords here - you can have a max of 10, so delete that do not apply to you.

If you do not do this, then you will see the following error when trying to save the integration -

Thanks for pointing this out Harris!

Ok, so I delete the superfluous keywords -

Open the integration - 

Let's look at the scope - 

Easy enough to follow - this integration is a scheduled job that will run according to your schedule. It invokes the ATOM Feed from HCM to retrieve new employees, entered after a particular date - dateAtomLastRun.

It uses the REST adapter to invoke an HCM REST api to retrieve new employee details.
Then the ftp adapter is invoked to write the new employees to an file.

This final step can, of course, be replaced by your specific functionality.

So what do I need to do for this to work?

Firstly - complete the connections that came with the recipe -


Now to the ftp connection -
I first create a directory on my ftp server - I am using DriveHQ here

Ok, all connections are tested and saved - let's look at the Lookup and the Library -

Change emailalias to suit your needs.

The Library function is used when setting the value of dateLastRun for the HCM Atom Feed.

Now to the final step, before amending the recipe integration -

Now we can return to the integration -

Note the Schedule Parameter and it's default value - adjust as necessary -


The GetNewHireATomFeed action leverages the HCM connection -

Again, you may want to do some changes here e.g. amending values such as Maximum entries set to be processed etc.

Now to the newEmployee processing loop -

As already mentioned, the HCM REST api is used here to retrieve employee details, remember, the AtomFeed invoke has been configured just to return header data; again something you could change. This action could also be replaced by invoking the REST api via the HCM adapter. 

What I do have to change is the FTP invoke - to specify my output directory.


Note that the file format is based on a csv - again, this is something you can change to suit your requirements.

That's it, now I can activate and test