OIC - How to change From address for email Notification

If we use a "from" address in the email address in the email notification action, it will not be reflected. We can configure a from address from settings. If no email address is configured, a default from address will be used.

To change a from address we have to perform following 2 steps:

1. Register the from address in Settings >> integrations >> Notification
2. Configure SPF and DKIM on the sender domain. For example, if you want from address as no-reply@oraclecloud.com, then you have to configure the sender domain i.e, oraclecloud.com

Go to Settings >> integrations >> Notification >> you will see default from address.

For more details, follow the reference:

https://blogs.oracle.com/integration/post/an-advanced-guide-to-oic-notification-via-emails

OIC - Why Scheduled Integrations are not executing on Time.

Scheduled integrations may be queued and not execute on time as scheduled for the following reasons:

• There is a running instance of the same scheduled integration. No new instance of the same integration is executed until the running instance completes.
• The throtting limit for scheduled integrations has been reached. No new scheduled integrations ate executed until the number of scheduled integrstions being executed falls below the throtting limit.

More, a scheduled integration is considered stuck when the total execution time exceeds six hours.

OIC - Maximum duration for integration flows | time out time for OIC services | Service limit for Scheduled orchestration or Async or Sync service

For Async integrations: Six hours (the instance is marked as aborted due to deadline timeout)

For Scheduled integrations: Six hours (the instance is marked as aborted due to deadline timeout)

For Synchronous integrarions: Five minutes (An HTTP 502 occurs).

OIC - Read Multiple records are of different types CSV file in Oracle Integration where 1st column does not have default values

UseCase: Read multiple records are of different types CSV file in Oracle Integration where 1st column does not have default values.

If we have requirement to read a "multiple records are of different types" CSV file in Oracle Integration where 1st column have default values. Then follow my below blog:

https://soalicious.blogspot.com/2023/06/oic-read-multi-records-of-different.html

In this blog, we have the following file to read:

Source file to be read:

Header1, Header2

Line1,Line2,Line3,Line4

Line11,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Header11, Header2

Line1,Line2,Line3,Line4

Line11,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Header111, Header2

Line1,Line2,Line3,Line4

Line11,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Important notes:

If you notice the file format, you will see the following:

One set is repeating mutilple times where

Header record 1 time

Line records 4 times fixed count

Implementation steps:

First create an XSD based on the CSV file using Jdeveloper File adpater configuration.

For the steps, Follow my previous blog :

https://soalicious.blogspot.com/2023/06/oic-read-multi-records-of-different.html

Then modify the xsd a little so that it can read this file properly.

Changes done:

1. Replace the choice element with sequence and make maxOccurs as unbounded.
2. Added a SET element as a pair element for repeating header and lines.
3. Remove the conditionValues element as we dont have default values as 1st column.

Modified XSD:

<?xml version="1.0" encoding="UTF-8" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" xmlns:tns="http://TargetNamespace.com/fileReference" targetNamespace="http://TargetNamespace.com/fileRefeference" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars" nxsd:encoding="US-ASCII">

<xsd:element name="Root-elememt">

<xsd:complexType>

<xsd:sequence maxOccurs="unbounded" >

<xsd:element name="SET">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="HEADER">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="header1" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="header2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="LINE" minOccurs="4" maxOccurs="4">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="line1" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="line2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="line3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="line4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

References:

https://technology.amis.nl/amis/oracle-11g-soa-suite-read-multi-record-data-from-csv-file-with-the-file-adapter-master-detail/

OIC - Read Multi records of different types CSV file | How to create a multi records of different types XSD to read CSV file

Use Case: We have multi Records are of Different types CSV file. How we can create a schema for the same and read via oracle integrarion.

File to be read: InputSourceData.txt

Invoice,header1, header2,header3,header4

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice,header1, header2,header3,header4

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Trailer, Trailer1

Notes: This case, we have following  3 types of records and 1st column values are fixed which would be our conditionValue while generating the xsd. >> 

1. Invoice (Header)
2. Invoice Line(Line)
3. Invoice Trailer(Trailer)

Implementation steps:

Step1: Create XSD from Jdeveloper tool

Create a SOA project and open composite.xml canvas and drag and drop a File adapter:

XSD is generated but while you tested the C1 or column 1 value for each record is missing due to have conditionValue attribute. Here we modify the code little to add the column 1 fixed or default values:

For each record add the below line and it works:

<xsd:element name="C1" type="xsd:string" default="Invoice"/>

The Modified XSD:

<?xml version="1.0" encoding="UTF-8" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" xmlns:tns="http://TargetNamespace.com/fileReference" targetNamespace="http://TargetNamespace.com/fileRefeference" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars" nxsd:encoding="US-ASCII">

<xsd:element name="Root-elememt">

<xsd:complexType>

<xsd:choice minOccurs="1" maxOccurs="unbounded" nxsd:choiceCondition="terminated" nxsd:terminatedBy=",">

<xsd:element name="RECORD1" nxsd:conditionValue="Invoice">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice"/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="RECORD2" nxsd:conditionValue="Invoice Line">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice Line"/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C6" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C7" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C8" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C9" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="RECORD3" nxsd:conditionValue="Invoice Trailer">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice Trailer "/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:choice>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Step2: Create a scheduled orchestration integration and drag and drop a FTP adpater connection and read the file using this created schema.

Activate and test.

OIC - ERP Bulk Import - Do we always need to provide manifest or .properties file for a import job

Rules to follow:

Basically, if an import job has associated with parameters, then we need to send a manifest or .properties file with the source file as a zip. But if the import job does not have any parameters, then we don't send any manifest file at all. 

For example, we have GL daily rates import job which does  not have any parameters. So simple a source file is enough to do bulk import to ERP.

Otherside,  AP invoice, Supplier or Journal are associated with parameters, thus for them, we need to send .properties file with the source files as zip to do bulk import.

OIC - How to continue FOR EACH loop for any failure case | Oracle Integration

Problem Description: Suppose, we have a scenario where we are taking the employees data as a rest trigger and writting for each employee one by one to a database table. Here, if any employee data gets errored out then what will happen, the process will be stopped from the moment and other employees will not be inserted to Database. 

What we can do so that all valid data employees to be inserted into database table irrespective of any failure for an employee invalid data. The invalid employee data can be discarded and stored to a csv file and do a mail to business.

Solution:

We can follow the below 2 steps to achieve this requitement:

Step1: Take a scope inside the FOR EACH loop and insert the data into db.

Step2: Go to the scope default fault handler >> handle the error, like this case, we are writing the failed employee details as a CSV file to a FTP directory

Testing:

Scenario: Tested with 3 employees where 1st employee has invalid department data which exceeds the actual size.

You can see 1st employee has not been inserted to database table. Other 2 employees has been successfully inserted to database even the 1st employee got errored out.