OIC XSLT - How to use loop inside loop in transformation |"Dynamic XML Data Transformation with XSLT: A Case Study on Person and Skill Records"

Usecase: Here, we will demonstrate how we can use multiple nested loops one level to fetch skill id based on qualification code for each HCM person.

Bascially person data has qualication codes and we have skill ids, qualifications codes in skill data, and we need to populate a target payload incorporating person details plus skill id based on person qualification code. Thats why need two loops - one outer represents person and one inner represents skill and for each person , we are matching qualification codes for both and fetching skill id and more over, each person can have multiple skills for a qualification codes.

Xslt code used:

<xsl:template match="/" xmlns:id="id_11">

  <nstrgmpr:Write xmlns:id="id_12">

    <ns36:Recordset xmlns:id="id_54">

      <xsl:for-each select="$Stage_ReadPersonRecords/nsmpr9:ReadResponse/nsmpr9:PersonRecord">

        <xsl:variable name="QualificationCode" select="nsmpr9:QUALIFICATION_CODE"/>

        <xsl:variable name="PersonNumber" select="nsmpr9:PERSON_NUMBER"/>

        <xsl:variable name="ValidTo" select="nsmpr9:VALID_TO"/>

        <xsl:variable name="TrainingCompletionDate" select="nsmpr9:TRAINING_COMPLETION_DATE"/>

        <xsl:for-each select="$Stage_ReadSkillIdData/nsmpr4:ReadResponse/ns29:Record[ns29:Code=$QualificationCode]">

          <ns36:Record xmlns:id="id_55">

            <ns36:Responsible xmlns:id="id_58">

              <xsl:value-of xmlns:id="id_59" select="$PersonNumber"/>

            </ns36:Responsible>

            <ns36:Skill xmlns:id="id_56">

              <xsl:value-of xmlns:id="id_254" select="ns29:Id"/>

            </ns36:Skill>

            <ns36:Valid until xmlns:id="id_60">

              <xsl:value-of xmlns:id="id_61" select="$ValidTo"/>

            </ns36:Valid until>

            <ns36:Validity_period xmlns:id="id_62">

              <xsl:choose>

                <xsl:when test="$ValidTo != &quot;&quot;">

                  <xsl:value-of xmlns:id="id_63" select="&quot;Yes&quot;"/>

                </xsl:when>

                <xsl:otherwise>

                  <xsl:value-of xmlns:id="id_63" select="&quot;No&quot;"/>

                </xsl:otherwise>

              </xsl:choose>

            </ns36:Validity_period>

            <ns36:Achievement_Date xmlns:id="id_64">

              <xsl:value-of xmlns:id="id_65" select="$TrainingCompletionDate"/>

            </ns36:Achievement_Date>

            <ns36:Score xmlns:id="id_66">

              <xsl:value-of xmlns:id="id_67" select="&quot;Pass&quot;"/>

            </ns36:Score>

          </ns36:Record>

        </xsl:for-each>

      </xsl:for-each>

    </ns36:Recordset>

  </nstrgmpr:Write>

</xsl:template>

OIC - ERP - Manual File Import to UCM from Fusion console for OIC Development in the Absence of HCM Extract Configuration

Use Case: Manual File Import to UCM for OIC Development in the Absence of HCM Extract Configuration

We were tasked with developing an HCM extract to generate a file for downstream processing. The requirement was to extract the file to Oracle UCM, download it, transform it, and then process it for downstream applications. However, the configuration for the HCM extract was not ready at the time, which could have delayed the integration work.

To continue with the Oracle Integration Cloud (OIC) development without waiting for the HCM extract setup, we manually imported the required file into the HCM UCM repository. This allowed us to proceed with testing and development of the OIC process, including file transformation and downstream processing.

This manual approach ensured that the integration work could continue in parallel with the configuration of the HCM extract, thus preventing delays in project timelines.

Comprehensive walkthrough with images:

Login to ERP/HCM console >> home navigation >> tools >> file import and export.

Click (+) button >> choose file >> choose account as hcm/dataloader/import. >> save

Login to Content server >> search default >> there we see the file uploaded successfully which we can download from OIC integration and bulid codes and test.

OIC - Converting one XML Field Values to distinct Comma-Separated Entries" | How do I generate a comma-separated list with XSLT/XPath?

Usecase:

Here, we will demonstrate how we can fetch unique comma separeated values for a xml field values.

For instance , here

Input file:

<Root>

  <qualification_code>A</qualification_code>

  <qualification_code>B</qualification_code>

  <qualification_code>A</qualification_code>

  <qualification_code>C</qualification_code>

  <qualification_code>B</qualification_code>

</Root>

Expected Output:

  "A", "B", "C"

Solution:

Implementation steps:

1. Use distinct-values() function to get the distinct node values.
2. For each record, take the values using select ="."
3. Using text field to hard codes like  quotes or comma and space
4. Use position() != last() condition to avoid the last comma.

xslt code:

<xsl:template match="/" xml:id="id_11">

    <nstrgmpr:Write xml:id="id_12">

        <ns22:RecordSet xml:id="id_31">

            <ns22:Record xml:id="id_32">

                <ns22:Test xml:id="id_36">

                    <xsl:for-each xml:id="id_35" select="distinct-values($readFile/nsmpr2:ReadResponse/ns25:RecordSet/ns25:Record/ns25:QUALIFICATION_CODE)">

                        <xsl:text>"</xsl:text>

                        <xsl:value-of xml:id="id_37" select="."/>

                       <xsl:text>"</xsl:text>

                        <xsl:text>,</xsl:text>

                        <xsl:if test="position() != last()">

                            <xsl:text>, </xsl:text>

                        </xsl:if>

                    </xsl:for-each>

                </ns22:Test>

            </ns22:Record>

        </ns22:RecordSet>

    </nstrgmpr:Write>

</xsl:template>

Screenshot:

OIC Gen3 - About OAuth setup to call integration and Factory APIs

Before we dive deep into the concept of OAuth2.0, lets first understand with below two terminologies:

• Authentication.
• Authorization.

Authentication(who you are):

• Authentication is the process of proving your own identity to third party service. It verifies whether a person’s identity is the same as he or she had declared himself or herself to be.

Example: So when we are trying to log in to Facebook or Google, we are required to first enter the email and password to verify our identity. This is what Authentication is.

Authorization(What access of resources you have):

• Authorization is the process of giving someone permission to do something or have something. In this process, a person grants another person to have some of its resources or use some of its resources.
• It is done after successful Authentication. Here an Authorization server is providing access to third-party services to use some of its resources.

Example:

the Authorization server is Google who is providing access to Tableau Desktop(Third-party service)  to use some of its resources.

So OAuth or OAuth 2.0 is an industry standard protocol for authorization, not for authentication. It is meant for service to authorize another service.

OAuth Flow

The requesting, granting, and life management of this tokens are often referred to as a “flow”. The OAuth specification allows for several ways of obtaining and validating tokens, and not all flows are meant for all types of clients.

There are more than one flows available for OAuth. 

Here, we will mainly discuss for below two flows:

1. Client Credential Flow
2. Authorization Code Flow

Client Credential Flow:

1. First the client application register itself with the OCI IAM authorization server. As a part of the registratration, the client chooses the flow or grant_type as the client credential(client id and secrect) and chooses the app role or roles(service developer or service invoker) that it needs to access the APIs
2. Client (an application or machine) requests access token using its credentials
3. OCI IAM validates and generates a token.
4. Access token is sent back to the client.
5. Client use access token as authentication to call the APIs
6.  Return the APIs data to the client.

Authorization Code Flow:

1. First the client application register itself with the OCI IAM authorization server. As a part of the registratration, the client chooses the flow or grant_type as the client credential(client id and secrect) and chooses the app role or roles(service developer or service invoker) that it needs to access the APIs
2. The Client (A user over mobile or web application) submits an authorization request through a browser to the authorization server's authorization endpoint. 
3. The authorization server authenticates the user and approve or deny consent for the client to access the user's resource.
4. In return, the client gets an authorization code that it exchanges for an access token at the token endpoint. 
5. The client uses the access token to invoke the API and gets the protected data in return.

Key Parameters required by Client Application

Grant Type: Client Credentials/Authorization Code

Access Token URL: https://<idcs url>/oauth2/v1/token

Client ID: xxxxxx

Client Secret: xxxxxx

Scope: https://xxxxxx:opc:resource:consumer::all

Auth URL*: https://<idcs url>/oauth2/v1/authorize

Redirect URL*: https://<oic url>/icsapis/agent/oauth/callback

Note: * for Autorizarion code only

What is IDCS url?

In oci domain where you have provisioned the oracle integration cloud service, its the domain url.

Navigation

Identity & security >> domains >> default domains >> domain url.

What is OIC URL?

Service console url.

Integration instances >> click the oracle integration instance >> service console url

How to create confidential application for Client Credential flow?

High level Steps :

1. Add application details(application name)
2. Configure OAuth
1. Config as client flow
2. Select client credential and refresh token authrization
3. Client ip address anywhere
4. Add resources >> add scope for the oic integration instance.
3. Configure policy if needed
4. Add app roles

Navigattion:

Identity & Security >> domain >> integrated application >> add application >>confidential application 

Testing from postman:

How to create confidential application for Authorization Code flow?

High level Steps :

1. Add application details(application name)
2. Configure OAuth
1. Config as client flow
2. Select client credential and refresh token authrization
3. Client ip address anywhere
4. Add resources >> add scope for the oic integration instance.
3. Configure policy if needed

Navigattion:

Identity & Security >> domain >> integrated application >> add application >>confidential application 

Test from postman:

OIC Factory APIs:

• Oauth is mandatory in OIC 3.
• Recommnended to use Design time url with parameter integrationinstance

The Oracle Integration Cloud (OIC) Factory APIs are APIs provided by Oracle for automating and managing integrations and other tasks within Oracle Integration Cloud. These APIs offer capabilities for developers and administrators to programmatically control OIC environments, allowing for tasks such as creating, deploying, managing, and monitoring integrations.

Key Use Cases for OIC Factory APIs

• Managing Integrations: List, activate, deactivate, or delete integrations.
• Monitoring: Check the status of integrations, view errors, and retrieve logs.
• Instance Management: Manage and monitor specific integration instances.
• Connections and Adapters: Manage connections and adapters, including creation, update, and deletion.
• Error Handling: Fetch and manage error details for integration runs.

Example Use Cases and API Requests

1. List Integrations

This API lists all the integrations available in OIC.

Request

GET /ic/api/integration/v1/integrations

Headers:

- Authorization: Bearer <access_token>

Example Response

[

   {

      "id": "INT-123",

      "name": "Example_Integration",

      "status": "ACTIVE",

      "version": "01.00.0000",

      "lastUpdated": "2023-08-20T15:45:00Z"

   },

   {

      "id": "INT-124",

      "name": "Another_Integration",

      "status": "INACTIVE",

      "version": "01.00.0001",

      "lastUpdated": "2023-07-18T10:10:00Z"

   }

]

2. Activate an Integration

Activating an integration makes it available to process instances.

Request

POST /ic/api/integration/v1/integrations/{integrationId}/activate

Headers:

- Authorization: Bearer <access_token>

Example Request

POST /ic/api/integration/v1/integrations/INT-123/activate

Response

{

   "status": "SUCCESS",

   "message": "Integration activated successfully."

}

Rest connection example created in OIC:

Example1: