Sending OIC Instance ID Tracking page link in Error emails

In case of any Fault in Integrations, one of the most common configured step is to send email along with some important details such as Instance ID and other fault details.

After reading email, we often want more information about error and for that we must look at the flow trace to understand what caused this fault. For this usually the step is to login to OIC console then go to Monitoring -> Tracking and then filter based on Instance ID which usually consumes quite amount of time. However, we can totally save this time by embedding a hyperlink of Instance ID Tracking page in the email itself at design time.

Steps to achieve–

1. In Notification Template create a reusable HTML body which contains Instance Id and before that add <a href='{InstanceBaseUrl}{InstanceId}'>
   If you are sending instance details in table row, then it would look something like this
   <tr>
   <td width="200" valign="top"><b>Instance ID</b></td>
   <td valign="top"><a href='{InstanceBaseUrl}{InstanceId}'>{InstanceId}</a></td>
   </tr>
2. Set Parameter name and its value as
   InstanceBaseUrl – concat($self/nsmpr0:metadata/nsmpr0:environment/nsmpr0:baseURL,'/integration/home/faces/link?page=tracking&tracking_id=')
   InstanceId - $self/nsmpr0:metadata/nsmpr0:runtime/nsmpr0:instanceId
   
3. Activate and test. Your email should look like below screenshot and once you click on Hyperlink it will open that Instance ID.
   

Bonus tip –

For Integrations developed using Oracle SOA Suite - BPEL you can send Flow ID links in emails. To generate generic Flow ID url and use it-

1. Navigate to SOA Infra -> Flow Instances and click on Search Options.
2. Change Instance created Time to 7 days
3. Click on Add/Remove Filter and remove all selection and check Flow Instance
4. Click on generate Bookmarkable Link and copy the URL. It would look like this -
   http://{hostname}:{port}/em/faces/ai/soa/infra?flowQuery=fId%3A{flow_id}%3Bct_twv%3A7%3Bct_twu%3Adays%3Bct_md%3Anormal%3B&type=oracle_soainfra&selectedTab=flowInstancesTab&target=/Domain_DefaultDomain/DefaultDomain/DefaultServer/soa-infra
   
5. There are some values which are env specific so, create two BPEL properties to store URL values, everything before flow_id as part1 and everything after that as part2. Now concat these in assign and add this as href in email which triggers from SOA, similarly to how we did in OIC. We can update config plans to with env specific properties values and then migrate.

Let’s look at the result

Integration between Oracle Integration Cloud Rest API and Process Forms

In this blog we will see how we can leverage Integrations in Process form to generate Dynamic LOV’s on Form fields, perform Refresh connector Actions on an event, filter list response based on criteria and Skip Data Upon Load.

For this, we will develop a simple form which will capture User’s International movement details request and submit for approval -

• In the form we will have user select his Current and Destination Country/State.
• Source Country LOV will be populated on form Load
• Once user selects Source Country, Source State and Destination Country LOV’s will be populated.
• User should not be able to see Source Country name in Destination Country LOV’s
• Once user selects Destination Country, Destination State LOV will be populated.
• Considering this as POC, Source and Destination options available are only India and USA.

Watch this video to understand how the form will behave-

Let’s begin –

1. For this use case I have developed a OIC service which returns list of Country one Rest resource and list of State having Param as Country Name on 2nd resource. It looks like this.
2. Navigate to Process. Click Create to create New Process Application -> Create an Application to start from scratch
3. Name the application and click Create.
4. Click on Create a Structured Process -> Start with a Form -> Name the process –> Click Create
5. Click on Start and Edit properties.
6. Give it a title and click on Form to create a new Form -> Name it -> Check Open form Immediately -> Click Create
7. Once the form opens, Drag and drop Select field 4 times, rename, and position them.
   
   Now we will configure Integration and then come back to form.
   
   
8. Click on Integrations -> Link to an Integration. Here we will select existing OIC integrations, for non OIC Rest and SOAP fulfillment services we can create connectors using options ‘Connect to Rest Connector’ and ‘Connect to SOAP connector’
9. Select OIC integration and click on Create. Upon finishing you will be able to see this service configuration card under Integrations. In my case I have selected CountryStateLookup which I have developed prior to coming to Process
   
   Let’s get back to form.
   
   
10. Click on Source Country -> Change Option Source from Static to Connector
• Select the required connector, resource, operation
• Similarly, we will configure State, Destination Country, and Destination State.
• Provide element name coming from Integration in Response.
  
11. Although we have now setup all fields to populate using Integrations, however we wouldn't want to call an Integration to fetch Source State, Destination Country and Destination State unless Source Country is selected. For this we will use Form features.
12. In Source State, Destination Country and State click on Enable skip upon Load.
13. On Source Country create an event so that we can load Source State once user has selected Country.
14. Create Event and select On Change
15. Edit and Add Action -> Select Control -> SourceState ->Refresh Connector
16. We can also refresh Connector for Destination based on this.
17. Now once user selects Destination Country, we also need to populate Destination State so let’s create an event on Destination Country also.
18. Only thing left is to configure Destination Country to not show Source Country name. For this we will use Filter. Edit Filter. Add Filter Criteria -> Select Operator “Not equals to”. In type select Control and in Control select SourceCountry. Click on ok and that’s it done.  
19. Save and Activate and Test. 

Thank you 😊

Unable to generate schema for reading XML file in Oracle Integration Cloud

Problem Statement – Unable to generate Schema for Reading XML file in Oracle Integration Cloud

Error: Please select a valid XML file. Caused by oracle.tip.tools.adapters.cloud.api.CloudAdapterException. Start of root element expected.

Explanation – For an Integration requirement with JDE we received this Source sample xml file from one of our European customers. When we open this xml file and validate data and structure, we could not find anything wrong with it still OIC is throwing an error. 

After spending some more time with this file on Notepad++ we were able to figure out the reason, it was its encoding. This file’s encoding is UTF-8-BOM whereas most of the time we receive UTF-8 encoded file.



Solution –

1. Make a copy of this file and open in Notepad++ or any other editor which allows you to change file’s encoding.
2. Change it’s encoding to UTF-8 and now let’s provide this as sample file in OIC to generate schema
   
3. Using modified xml file, OIC generates schema successfully.
   
   At this stage if we complete the integration, activate and test, integration will fail for actual file but can read modified file (UTF-8 encoded file)
   
   
   Modified File read is successful
   
   
   Original file read fails.
   
   Which means that schema still cannot handle BOM encoded files. Let's move ahead and modify schema.
   
   
4. Edit Adapter endpoint configuration and modify schema manually. To do this Edit Adapter endpoint config, Click on Summary and then click on Schema. You will be now able to see NXSD schema generated for XML file.
   
   
   
   
5. Copy this schema into Notepad++ and add nxsd:parseBom="true" property in xsd:schema element. Save this file. Finally, it will look like this –
   
6. Now, navigate back to Schema tab and choose XML Schema (XSD) Document option for defining schema. Choose schema file saved in previous steps. Complete adapter configuration.
   
7. That’s it. Activate and test integration for both files. Both files will be processed successfully with this schema.
   
   
   Modified file read is successful
   
   
   Also, able to read Original file

To summarize, If you are facing an error while generating schema for a xml file and you see that there is no issue with structure or content of the file then investigate other areas and if you end up with BOM encoded file then use schema attribute nxsd:parseBom="true" to read this type of files.

Bonus tip - This property is applicable for Oracle SOA suite too and can be used where you have a situation to process BOM encoded files through SOA. 

Thank you.

Send WhatsApp notifications from Oracle Integration Cloud

In the recent times I have noticed that many businesses have implemented/integrated with IT solutions to Send and Receive orders/complaints/shipping updates on WhatsApp. So, I thought to explore on finding a simple way to achieve this from Oracle Integration Cloud. For this I found that we can leverage Twilio platform and Twilio adapter which comes as OOTB adapter with OIC. 

About Twilio - Twilio is an IT platform which provides flexible and easy ways to send communications (APIs) to multiple channels i.e., Voice/SMS/WhatsApp etc. They provide user trial accounts so that one can explore before purchasing subscriptions. More details about them is available on their website - https://www.twilio.com/

Let’s see how we can achieve this –

1. First, let’s create Free User trial account in Twilio.
• Open https://www.twilio.com/ in browser and Click ‘Signup’. In next page Click on Sign up for free.
• Provide necessary info and click on ‘Start your free Trial’ and complete setup.
• On the left side of home screen, we can see Phone Numbers/Messaging options listed down under Develop section.
  Expand Messaging -> try it out and click on Send WhatsApp Message.
• Agree and activate Sandbox.
  
  
  
2. Send WhatsApp Message from your phone to connect your WhatsApp account with Sandbox env and within few seconds you will receive confirmation. Next, you can also Try sending One-way Messaging or Two-way Messaging from Twilio platform to see how it works.
   
   
   
3. Copy Account SID and Auth token. We will need this to create connection in OIC.
   
   
   
   

Now let’s create Integration in OIC -

1. Create new Connection and Select Twilio Adapter.
2. Provide name for this connection and under security policy provide Account SID and Auth token captured in earlier stage.
3. Test and Save connection.
   You will notice that we can only select Invoke Role, which means currently we cannot trigger OIC API when an event is generated in Twilio. However, we can create rest-based API in OIC and provide this API as webhook in Twilio Sandbox Configuration page to fulfill request.
4. Now, Create new schedule type integration
5. Add Twilio adapter connection created in earlier step and configure
• Choose Send SMS or MMS in operation
• Next and Done
  
  
  
  
6. Perform Map and for the time being let’s hardcode To, Body, From fields so that we don’t have to define a source. Validate and Close map.
   
   
   Sample Values -
   To – “whatsapp:+918095831600”. As we are using sandbox, we can only provide number which is connected to it configured in previous step.
   From – “whatsapp:+14155238886” .For sandbox account only this is a valid value, 
   Body – “Whatsoever you want to send”
7. Add Business identifier, Save and Activate Integration. Finally, your Integration should look like this.
   
8. Now let’s Test integration
   
   
   
   
9. Check WhatsApp.
   
   
   
   

That’s it. Based on requirement -

• For One way communication we can develop Rest based OIC service which can be triggered from Oracle ERP, Oracle SaaS, VBCS, Oracle Apex or from other OIC service to send WhatsApp/SMS/Voice notifications
• Or, for Two way communication we can also develop Rest based API in OIC and provide it as webhook in Twilio to fulfill requests triggered by Users. 

Thank you. 😊

Update

Just a day after writing this post, I visited Decathlon to book Squash Racket service and guess what, I received repair updates and job completion update through WhatsApp.

Looping over Unique Records in Oracle Integration Cloud

Frequently we get this requirement where we have to retrieve hundreds of rows from database or by reading a file and have to send this data to downstream application. 

Catch is we can’t send all records together as downstream application will not accept it, nor we can send one record at a time as we will end up sending too many requests to downstream application, however we can group unique records and send them at once to downstream. 

In OIC, by default for-each runs for each node and we have no way to configure it to run only for unique records. Thankfully, this can be achieved by using Global Variables, Data stitch and Xslt for-each group function in Map. Let’s see how.

For this demo –
1. I am using existing OIC service [acting as downstream app] which receives and process Employee information.
2. New Service [which we will develop] will read file from FTP which contains multiple employee’s data and process each employee’s record.
3. Input File Structure used -

   

Steps -
1. Create FTP connection, Test and Save

 
2. Create New Integration of Schedule type.
3. Invoke FTP connection created in step 1 to read file. Provide sample file for OIC to create schema.

    [Assuming that input file size will be < 10mb. If the file size is more than 10 mb then we

     should use download file option and then use Read file option under Stage file action]

4. Add action ‘for each’ after File Read and provide values to configure it.

5. Add action and choose call OIC, configure and select EmployeeService

6. Map from Source to target then Validate and Save.
7. Map business Identifiers and save Integration. Finally, your integration should look like this –

 
8. Activate and Test

After testing you will notice that for-each ran 8 times whereas as per our requirement it should run only 3 times.

 

This is not what we wanted 😟. So, let’s make changes in integration to achieve our requirement.

9. Deactivate integration
10. Create Global Variable of type EmployeeService say distEmpCollection

11. Before loop add a Map action, if required choose target as EmployeeService variable, will have to write for each group-by xslt function and store only unique ID. Validate and Test.

We can see that for 3 Employee records we were able to generate 2 Employee nodes as output since Source contains only 2 unique records.

 

12. Now we will overwrite this data into global variable which we created earlier using Data stitch action.

13. Open ‘for each’ action and change root element to distEmpCollection. [See now your loop will run on this xml data which contains only distEmpCollection]

 
14. Open Map and update Mapping and Finally your Integration should look like this.

 
15. Save, activate and Test Integration.

Hope this helps. 😃