OIC - Grouping Payment Data with XSLT: Using for-each-group to Transform Headers and Details in Oracle EBS

🔷 Use Case

In Oracle EBS payment integrations, incoming data files often contain repeating header-detail structures. A typical scenario includes File Headers (MH), Batch Headers (RH), and Detail Records (D1).

The requirement is to extract the POS ID value from each batch header (RH) and include it in each of the detail records (D1) for the target file.

Source file:

MHHEADER Record

RHHEADER Record posid1

D1DETAIL Record 1.1

D1DETAIL Record 1.2

D1DETAIL Record 1.3

RHHEADER Record posid2

D1DETAIL Record 2.1

D1DETAIL Record 2.2

...

The target file should follow the format:

Header  
Detail posid1 
Detail posid1
Detail posid1  
Header  
Detail posid2  
Detail posid2 
...

---

🔷 Solution Overview

To achieve this, we utilize XSLT to:
✅ Identify and group records by batch header (RH).
✅ Extract the POS ID from each RH record.
✅ Write one Header record to the target for each RH.
✅ Write corresponding Detail records, including the extracted POS ID, for each D1 under the RH.
✅ Repeat for all batches in the file.

---

🔷 Solution Steps

1️⃣ Identify the Grouping
Use <xsl:for-each-group> with group-starting-with="RH" to create logical batches.

2️⃣ Extract POS ID from Batch Header
Within each group, identify the RH record and extract the POS ID using XSLT’s substring and normalize-space functions.

3️⃣ Write Header Record
For each group, create a target Header record.

4️⃣ Iterate Over Detail Records
Loop through the Detail (D1) records within the group and create target Detail records. Insert the extracted POS ID value into each of these records.

5️⃣ Output to Target XML
The XSLT’s template writes the Header and Detail records in the specified order for the target file.

---

🔷 Source XSLT Snippet

<xsl:for-each-group select="..." group-starting-with="...">
  <!-- Extract RH's POS ID -->
  <xsl:variable name="POSID" select="substring(RH, ... , ... )"/>
  <!-- Write Header record -->
  <Header>
    <POSID><xsl:value-of select="$POSID"/></POSID>
  </Header>
  <!-- Write all Detail records under RH -->
  <xsl:for-each select="current-group()[...D1...]">
    <Detail>
      <POSID><xsl:value-of select="$POSID"/></POSID>
      ...
    </Detail>
  </xsl:for-each>
</xsl:for-each-group>

Actual code:

<xsl:template match="/">
  <ns0:empmpr:Write xmlns:ns0="http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.bpel.services.XSLTProcessor.BPEL2XSLT">
    <ns24:Payments xmlns:ns24="http://xmlns.oracle.com/apps/financials/payables/payments/shared/paymentsmodel/paymentsservice/">
      <ns24:Payment>
        <ns24:HDR_RECORD-RECORD>
          <ns24:VALUE-OF xml:id="id_248" select="'HDR'"/>
          <ns24:value-of xml:id="id_249" select="'HDR'"/>
        </ns24:HDR_RECORD-RECORD>
        <ns24:CUST_VENDOR_ID xml:id="id_277">
          <ns24:value-of xml:id="id_278" select="'PSB'"/>
        </ns24:CUST_VENDOR_ID>
        <ns24:CURR_CD_PAY_MODE xml:id="id_252">
          <ns24:value-of xml:id="id_253" select="'HKD'"/>
        </ns24:CURR_CD_PAY_MODE>
        <ns24:PIT_EFF_PAY_SOURCE xml:id="id_254">
          <ns24:value-of xml:id="id_255" select="'PPSB'"/>
        </ns24:PIT_EFF_PAY_SOURCE>
        <ns24:PAY_SOURCE_ACCT_NO xml:id="id_256">
          <ns24:value-of xml:id="id_257" select="'PPSB'"/>
        </ns24:PAY_SOURCE_ACCT_NO>
      </ns24:Payment>
    </ns24:Payments>

    <xsl:for-each-group select="$ReadRawFile/nsmp0:ReadResponse/ns23:FileRowSet/ns23:FileRow" 

group-starting-with="ns23:FileRow[starts-with(normalize-space(ns23:MyData), 'RH')]">
      <xsl:if test="starts-with(normalize-space(current-group()[1]/ns23:MyData), 'RH')">
        <xsl:variable name="hvalue" select="normalize-space(current-group()[1]/ns23:MyData)"/>
        <xsl:for-each select="current-group()[position() &gt; 1][starts-with(normalize-space(ns23:MyData), 'D1')]">
          <ns24:Payment xml:id="id_42">
            <ns24:HDR_RECORD-RECORD>
              <ns24:VALUE-OF xml:id="id_248" select="'DTR'"/>
              <ns24:value-of xml:id="id_249" select="'DTR'"/>
            </ns24:HDR_RECORD-RECORD>
            <ns24:CUST_VENDOR_ID xml:id="id_277">
              <ns24:value-of xml:id="id_278" select="normalize-space(substring(ns23:MyData, 28, 25))"/>
            </ns24:CUST_VENDOR_ID>
            <ns24:CURR_CD_PAY_MODE xml:id="id_252">
              <ns24:value-of xml:id="id_253" select="'CASH'"/>
            </ns24:CURR_CD_PAY_MODE>
            <ns24:PIT_EFF_PAY_SOURCE xml:id="id_254">
              <ns24:value-of xml:id="id_255" select="'PPSB'"/>
            </ns24:PIT_EFF_PAY_SOURCE>
            <ns24:PAY_SOURCE_ACCT_NO xml:id="id_256">
              <ns24:value-of xml:id="id_257" select="'PPSB'"/>
            </ns24:PAY_SOURCE_ACCT_NO>
            <ns24:PAY_AMT xml:id="id_258">
              <ns24:value-of xml:id="id_259" select="normalize-space(substring(ns23:MyData, 28, 25))"/>
            </ns24:PAY_AMT>
            <ns24:PAY_DATE xml:id="id_260">
              <ns24:value-of xml:id="id_261" select="normalize-space(substring(ns23:MyData, 20, 8))"/>
            </ns24:PAY_DATE>
            <ns24:POSID xml:id="id_262">
              <ns24:value-of xml:id="id_263" select="normalize-space(substring(ns23:MyData, 55, 8))"/>
            </ns24:POSID>
            <ns24:INPUT_TIME xml:id="id_264">
              <ns24:value-of xml:id="id_265" select="normalize-space(substring(ns23:MyData, 11, 15))"/>
            </ns24:INPUT_TIME>
          </ns24:Payment>
        </xsl:for-each>
      </xsl:if>
    </xsl:for-each-group>
  </ns0:empmpr:Write>
</xsl:template>

---

Screenshot:

🔷 Step-by-Step Breakdown

✅ Step 1: Start the Template

<xsl:template match="/">

This is the root template—processing begins here.

✅ Step 2: Write Static Header Payment Record

<ns24:Payments>

  <ns24:Payment>...</ns24:Payment>

</ns24:Payments>

This block writes a static Payment record containing hard-coded values. It seems to serve as a placeholder or “file-level header.”

✅ Step 3: Group Records by Batch Header (RH)

<xsl:for-each-group select="..." group-starting-with="...RH...">

This groups all input file rows into batches starting with a RH record (Batch Header).

✅ Step 4: Validate the Group

<xsl:if test="starts-with(normalize-space(current-group()[1]/ns23:MyData), 'RH')">

Ensures the first row in the group is indeed an RH record.

✅ Step 5: Extract Batch Header Data

<xsl:variable name="hvalue" select="normalize-space(current-group()[1]/ns23:MyData)"/>

This stores the RH record’s data for use in Detail record mapping.

✅ Step 6: Process Each Detail Record (D1) in the Group

<xsl:for-each select="current-group()[position() &gt; 1][starts-with(normalize-space(ns23:MyData), 'D1')]">

Loops through all D1 rows after the first row in the group (which is the RH record). Each D1 record triggers the creation of a new Payment record.

✅ Step 7: Write Detail Payment Record

Inside the for-each, it writes a Payment XML element for each D1 record, extracting and transforming parts of the data:

Extracts substrings from MyData to populate fields like:

CUST_VENDOR_ID

PAY_AMT

PAY_DATE

POSID

INPUT_TIME

Adds static values to fields like:

CURR_CD_PAY_MODE (CASH)

PIT_EFF_PAY_SOURCE (PPSB)

🔷 Conclusion

This XSLT approach automates the transformation from a mixed file structure to the target structured file by grouping, extracting POS IDs, and mapping the data efficiently. It ensures data consistency and proper file formatting for downstream processing.

OIC - Dynamic WSDL in OIC: Accept Any Payload with xsd:anyType

Dynamic WSDL in OIC: Accept Any Payload with xsd:anyType

Use Case

In OIC integrations, it's common to interact with multiple upstream or downstream systems where payload structures differ or aren't fixed. Instead of creating a unique integration for every structure, we can expose a flexible web service that accepts any XML structure. This is especially helpful in hub-and-spoke or generic logging/auditing integrations.

The goal here is to expose a SOAP endpoint in OIC that accepts any type of payload.

---

Problem Statement

You want to expose an integration in OIC that:

• Accepts varying or dynamic data structures from multiple sources.
• Doesn't require a fixed schema.
• Can act as a dynamic listener or dispatcher based on incoming payload content.

---

Solution: WSDL with xsd:anyType

Here is the exact WSDL used to expose a SOAP endpoint in OIC with flexible payload capability:

<?xml version="1.0" encoding="UTF-8"?>
<definitions xmlns="http://schemas.xmlsoap.org/wsdl/"
             xmlns:xsd="http://www.w3.org/2001/XMLSchema"
             xmlns:tns="http://iec.ch/TC57/2011/schema/message"
             targetNamespace="http://iec.ch/TC57/2011/schema/message"
             name="CIMAsyncService">

    <!-- TYPES -->
    <types>
        <xsd:schema targetNamespace="http://iec.ch/TC57/2011/schema/message"
                    elementFormDefault="qualified">
            <xsd:element name="ResponseMessage" type="tns:ResponseMessageType"/>
            <xsd:complexType name="ResponseMessageType">
                <xsd:sequence>
                    <xsd:element name="Data" type="xsd:anyType"/>
                </xsd:sequence>
            </xsd:complexType>
        </xsd:schema>
    </types>

    <!-- MESSAGE -->
    <message name="SendResponseMessageInput">
        <part name="parameters" element="tns:ResponseMessage"/>
    </message>

    <!-- PORT TYPE -->
    <portType name="CIMAsynType">
        <operation name="SendResponseMessage">
            <input message="tns:SendResponseMessageInput"/>
        </operation>
    </portType>
</definitions>

---

How to Use in OIC

Step 1: Create a SOAP Connection

• Go to Connections.
• Choose connection type: SOAP.
• Use the above WSDL file to configure the connection.
• Set the security policies (None / Basic Auth / WSS, based on your use case).

Step 2: Create an Integration (App Driven - SOAP Trigger)

• Choose the connection as the trigger.
• OIC will auto-generate a structure where the input message will include:

  <Data> ... </Data>
  

• Map this Data field directly or use oraext:getElement or xpath functions to extract values from it.

Step 3: Process the AnyType Content

• Inside the integration flow, use an Assign or Function Call to extract specific XML elements.
• Alternatively, just log or route the payload to another system without inspecting its structure.

Step 4: Activate and Test

• Deploy and activate the integration.
• Test with different XML structures using SOAP UI or Postman.

Example Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
                  xmlns:mes="http://iec.ch/TC57/2011/schema/message">
   <soapenv:Header/>
   <soapenv:Body>
      <mes:ResponseMessage>
         <mes:Data>
            <Customer>
               <ID>123</ID>
               <Name>John Doe</Name>
            </Customer>
         </mes:Data>
      </mes:ResponseMessage>
   </soapenv:Body>
</soapenv:Envelope>

---

Benefits

• No schema lock-in: Send anything from upstream systems.
• Reusable: Use this as a generic endpoint for multiple use cases.
• Powerful: Combine with Decision Models, JavaScript, or XSLT for routing or transformation.

Postman - How to Test a SOAP Service Using Postman

Use Case:

A QA engineer or developer needs to validate a legacy SOAP web service provided by an enterprise application. However, there is no UI available for direct interaction and using tools like SOAP UI feels heavy. They prefer a lightweight, quick alternative—Postman.

Solution Steps:

Step 1: Gather SOAP Endpoint and WSDL

Obtain the following from the service provider:

• SOAP endpoint URL
• WSDL (Web Services Description Language) URL or XML
• Sample SOAP Request XML (if available)

Step 2: Open Postman

Launch Postman and open a new tab for a new request.

Step 3: Configure Request

• Method: POST
• URL: Enter the SOAP endpoint (e.g., https://example.com/soap/Service) and not the wsdl url(except ?wsdl part)
• Headers:
  • Content-Type: text/xml;charset=UTF-8
  • SOAPAction: (Optional — required for some services. Use the action URL from WSDL.)

Step 4: Add the SOAP Envelope in the Body

• Switch to the Body tab
• Select raw
• Choose XML (text/xml) from the dropdown
• Paste your SOAP XML envelope. Example:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://webservice.example.com/">
   <soapenv:Header/>
   <soapenv:Body>
      <web:getUser>
         <web:userId>12345</web:userId>
      </web:getUser>
   </soapenv:Body>
</soapenv:Envelope>

Step 5: Send Request

Click Send. You’ll receive a SOAP XML response in the same format.

Step 6: Validate the Response

• Check for the correct tags in the SOAP Body.
• Look for fault tags (<soap:Fault>) to debug errors.

If authentication is required (Basic Auth, WS-Security), configure it under the Authorization tab or add headers manually.

How to Select a SOAP Operation in Postman

Step 1: Understand the WSDL File

Open the WSDL URL in a browser or download the XML. Look for the <wsdl:operation> tags. Each one represents an operation you can call.

Example:

<wsdl:operation name="getUser">
  ...
</wsdl:operation>

Step 2: Identify SOAPAction (Optional but often required)

Under each operation, the WSDL may define a soapAction attribute:

<operation name="getUser">
  <soap:operation soapAction="http://example.com/getUser"/>
</operation>

You’ll use this value in the SOAPAction header in Postman.

Step 3: Copy the Request XML for the Operation

Often, the WSDL (or service documentation) includes a sample request for each operation. If not, you can generate one based on the operation’s input schema.

Example SOAP request for getUser:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://example.com/webservice/">
   <soapenv:Header/>
   <soapenv:Body>
      <web:getUser>
         <web:userId>12345</web:userId>
      </web:getUser>
   </soapenv:Body>
</soapenv:Envelope>

Step 4: Paste in Postman Body

Paste the envelope into Postman's body (as described earlier), and update the SOAPAction header (if required) with the operation’s SOAPAction URL.

OIC Gen3 - New feature - File Server Action in OIC 25.x

🔧 File Server Action in OIC 25.x

The File Server Action enables you to interact with the embedded File Server in OIC, facilitating operations such as listing directories, reading files, writing files, moving files, and deleting files. 

Supported Operations:

1. List Directory

Retrieves a list of files and subdirectories within a specified directory.

Note: Supports listing up to 1,000 files per operation. 

2. Get File Reference

Obtains a reference to a specific file, which can be used for further processing within the integration. 

3. Write File

Writes content to a specified file on the File Server.

Useful for creating new files or updating existing ones. 

4. Move File

Moves a file from one directory to another within the File Server.

Helps in organizing files post-processing. 

5. Delete File

Deletes a specified file from the File Server.

Useful for cleanup operations after processing.

 

🛠️ Implementing File Server Actions in an Integration

To utilize the File Server Action:

1. Enable File Server:

Ensure that the File Server is enabled in your OIC instance. 

2. Create or Edit an Integration:

In the integration canvas, click on the "+" icon to add a new action.

Select "File Server Action" from the list of available actions. 

3. Configure the Action:

Choose the desired operation (e.g., List Directory, Write File).

Provide necessary parameters such as directory paths, file names, and content as required by the operation. 

4. Map Inputs and Outputs:

Use the mapper to define how data flows into and out of the File Server Action. 

5. Test and Deploy:

Test the integration to ensure the File Server Action performs as expected.

Deploy the integration for production use. 

📘 Example Scenario: Processing Supplier Invoices

Objective: Automate the retrieval and storage of supplier invoices. 

Workflow:

1. List Directory:

Use the File Server Action to list all invoice files in the /invoices/pending directory. 

2. For Each File:

Get File Reference:

Obtain a reference to the current file.

Process File:

Read and process the file content as needed (e.g., extract data, validate).

Move File:

After successful processing, move the file to the /invoices/processed directory.

Delete File:

Optionally, delete the original file from the /invoices/pending directory to prevent reprocessing.

By leveraging the File Server Action in OIC 25.x, you can streamline file-based operations within your integrations, reducing the need for external tools and simplifying your workflows. 

File Server vs Ftp/sftp connection:

File Server (OIC 25.x) is lighter on message pack, faster, more secure, and ideal for internal automation.

SFTP Adapter is useful only when external connectivity is required, but comes with higher message consumption, slower performance, and more monitoring overhead.

OIC - Transforming anyType XML to a Namespaced Fragment using XSLT in Integration Flows | Dynamic XML Transformation: Namespace Remapping with xsl:apply-templates in XSLT

Use Case:

In enterprise integrations using Oracle Integration Cloud (OIC) or similar middleware, you may encounter services that accept or return XML fragments typed as xsd:anyType. These fragments can contain any structure, and the lack of a fixed schema or namespace binding makes it difficult to directly map elements in transformations.

For example, a source service might wrap business logic (like GetMeterReadings) inside a generic RequestMessage node with dynamic content. The requirement is to extract the GetMeterReadings fragment and pass it along to the target system with a well-defined target namespace.

---

Solution:

To handle this dynamic anyType payload, we use XSLT (Extensible Stylesheet Language Transformations) to:

• Extract the desired fragment (GetMeterReadings) from the dynamic XML.
• Reconstruct it in a known target namespace (myns1 in this case).
• Maintain attribute and nested node integrity.

Here’s how it works:

1. Define a generic template to re-map all nodes into a target namespace:

<xsl:template match="*">
    <xsl:element name="myns1:{local-name()}">
        <xsl:apply-templates select="@* | node()"/>
    </xsl:element>
</xsl:template>

2. Apply the template only to the fragment of interest (GetMeterReadings) inside an anyType payload:

<xsl:apply-templates select="//inpl:RequestMessage/inpl:Request/*:GetMeterReadings"/>

3. Declare the required namespaces in the XSLT header:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:inpl="http://example.com/source"
                xmlns:myns1="http://example.com/target"
                exclude-result-prefixes="inpl"
                version="1.0">

---

Result:

The output of this transformation will be a GetMeterReadings element, moved into the myns1 namespace, ready to be used by the target system.

This approach ensures robust handling of anyType payloads in integrations and allows you to enforce consistent target schemas, even when the source is non-standard.

---

OIC - Handling any Type Field Mapping in Oracle Integration Cloud (OIC) | Use of Local-namespace() function and apply template

Usecase: 

While building an integration in OIC, you may come across a requirement where the target SOAP or XML structure contains a field of type <any> (or xs:anyType). These fields are placeholders meant to accept any valid XML content, but OIC’s Mapper UI doesn’t allow direct drag-and-drop mappings for such fields.

This can be confusing and frustrating, especially when you're trying to pass a dynamic XML or construct a custom payload. Here's how to deal with this in a clean and simple way.

---

Solution Steps

Step 1: Identify the <any> Field

In your target structure, locate the field labeled as <any> or marked with type xs:anyType.

Step 2: Switch to XSLT View

In the Mapper canvas:

• Click on the </> icon (usually found in the upper right of the mapping panel).
• This will open the raw XSLT mapping code.

Step 3: Manually Add a Custom XML Payload

Here, insert a valid XML snippet that the target system expects. Example:

<any>
  <ns1:CustomData xmlns:ns1="http://example.com/schema">
    <ns1:DeviceId>{/ns0:TriggerService/Request/GetMeterReadings/EndDevice}</ns1:DeviceId>
    <ns1:ReadingType>{/ns0:TriggerService/Request/GetMeterReadings/ReadingType}</ns1:ReadingType>
  </ns1:CustomData>
</any>

Replace /ns0:TriggerService/... with actual source path expressions from your input.

Step 4: Validate and Save

• Ensure the XML syntax is correct.
• Click Validate in the mapper toolbar.
• Save your changes and return to the visual canvas.

Optional: Use JavaScript or Stage File for Complex Data

If the XML is very dynamic:

• Use a JavaScript action to build the payload.
• Or read it from a stage file and assign it as a single string/XML object.

Used code: we have used local-namespace() function and apply template

<xsl:param name="tracking_var_3" xml:id="id_21"/>

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

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

    <nstrgmpr:Body>

      <ns2:RequestMessage>

        <ns2:Header>

          <ns2:Verb>

            <xsl:value-of select="//*[local-name()='RequestMessage']//*[local-name()='Header']/*[local-name()='Verb']/text()"/>

          </ns2:Verb>

          <ns2:Noun>

            <xsl:value-of select="//*[local-name()='RequestMessage']//*[local-name()='Header']/*[local-name()='Noun']/text()"/>

          </ns2:Noun>

          <ns2:Timestamp>

            <xsl:value-of select="//*[local-name()='RequestMessage']//*[local-name()='Header']/*[local-name()='Timestamp']/text()"/>

          </ns2:Timestamp>

          <ns2:Source>

            <xsl:value-of select="//*[local-name()='RequestMessage']//*[local-name()='Header']/*[local-name()='Source']/text()"/>

          </ns2:Source>

          <ns2:ReplyAddress>

            <xsl:value-of select="//*[local-name()='RequestMessage']//*[local-name()='Header']/*[local-name()='ReplyAddress']/text()"/>

          </ns2:ReplyAddress>

          <ns2:MessageID>

            <xsl:value-of select="//*[local-name()='RequestMessage']//*[local-name()='Header']/*[local-name()='MessageID']/text()"/>

          </ns2:MessageID>

          <ns2:CorrelationID>

            <xsl:value-of select="//*[local-name()='RequestMessage']//*[local-name()='Header']/*[local-name()='CorrelationID']/text()"/>

          </ns2:CorrelationID>

        </ns2:Header>

        <ns2:Request>

          <xsl:apply-templates select="//*[local-name()='RequestMessage']"/>

        </ns2:Request>

      </ns2:RequestMessage>

    </nstrgmpr:Body>

  </nstrgmpr:OutboundSOAPRequestDocument>

</xsl:template>

<xsl:template match="//*[local-name()='RequestMessage']">

  <nsd10:GetMeterReadings>

    <xsl:for-each select="*[local-name()='Request']/*[local-name()='GetMeterReadings']/*[local-name()='EndDeviceAsset']">

      <nsd10:EndDevice>

        <nsd10:Names>

          <nsd10:name>

            <xsl:value-of select="*[local-name()='mRID']/text()"/>

          </nsd10:name>

        </nsd10:Names>

      </nsd10:EndDevice>

    </xsl:for-each>

    <xsl:for-each select="*[local-name()='Request']/*[local-name()='GetMeterReadings']/*[local-name()='ReadingType']">

      <nsd10:ReadingType>

        <xsl:value-of select="*[local-name()='name']/text()"/>

      </nsd10:ReadingType>

    </xsl:for-each>

  </nsd10:GetMeterReadings>

</xsl:template>

</xsl:stylesheet>

Screenshot:

Conclusion

Although <any> fields are not directly mappable in OIC’s graphical interface, you can still pass data to them using XSLT view and inserting well-formed XML manually. This gives you flexibility while still adhering to schema requirements.

OIC - Simulating External SOAP Service Calls in OIC Without Agent Using a Stub Integration

Use Case:

In Oracle Integration Cloud (OIC), we had a requirement to integrate with an external SOAP service that mandates an agent setup. However, due to agent issues during development, we needed a workaround to simulate the SOAP service response and continue functional testing. The goal was to create a stub service that mimics the real SOAP service, allowing us to test our main integration flow without dependency on the actual external system or agent setup.

Solution Steps:

1. Create a Stub SOAP Connection:

   • Created a new SOAP connection in OIC without agent setup and basic authentication.
   • Uploaded the WSDL (with embedded XSD) of the external service to this connection.
   • Ensured that the connection is fully functional for internal calls.
   • 
     
     

2. Develop a Stub Integration:

   • Built a separate OIC integration that uses the above SOAP connection as a trigger.
   • Hardcoded a sample SOAP response that mimics the external service's expected response.
   • This stub acts as a mock service and can be updated later with more realistic data if needed.
   • 
     
     
     
     
     

3. Modify the Main Integration:

   • In the main integration (which originally called the external SOAP service), replaced the invoke to the external service with an invoke to the stub SOAP connection.
   • Used a dynamic approach to set Endpoint URL and SOAP Action in the invoke properties to point to the stub integration endpoint.
   • 
     
     

4. Use a Lookup to Manage Endpoint Properties:

   • Created an OIC Lookup table to store environment-specific endpoint URLs and SOAP Actions.
   • This makes switching between the stub and real service (during SIT/UAT) seamless by just updating the lookup values.

5. Mapping and Testing:

   • Mapped the request payload to match the expected structure of the original service.
   • Verified the response from the stub integration is structurally and functionally valid.
   • Ensured the integration behaves as if it's communicating with the actual external service.

Outcome:

This stub-based approach enabled functional testing of the main integration flow without waiting for the agent setup. It allowed developers and testers to validate business logic early, improving delivery timelines. Later in SIT/UAT, switching to the real service will only require updating the lookup to point to the actual endpoint.

OIC - Resolving "SOAP Header Security was not understood" invoking Web Services

Use Case:

In a utility integration scenario using MultiSpeak-compliant web services, a SOAP request fails with the error:
"CASDK-0033: Received a SOAP fault... Fault Code : soap:MustUnderstand – SOAP header Security was not understood."
This typically occurs when integrating Oracle Integration Cloud (OIC) with a third-party endpoint that requires WS-Security headers.

Root Cause:

The SOAP request included a <wsse:Security> block with mustUnderstand="1", but the target endpoint does not understand or support WS-Security headers in that format.
Alternatively, the expected security token or credentials were missing or not compliant with the service’s expected authentication scheme.

Solution Steps:

Step 1: Understand the Error Message

Error:

Fault Code : soap:MustUnderstand  
Fault String : SOAP header Security was not understood.

This indicates that the service could not process the WS-Security headers, often because it does not support them or requires a different security configuration.

Full error details:

CASDK-0033: Received a SOAP fault while invoking endpoint target: https://<host>/CC/WebAPI/MRCB.asmx.

This indicates a processing exception on the service endpoint side. Please check service side logs to further diagnose the problem

<![CDATA[

Fault Code : soap:MustUnderstand

Fault String : SOAP header Security was not understood.

]]>

Step 2: Analyze the SOAP Request

The failing payload included this header:

<wsse:Security env:mustUnderstand="1" ...>
  <wsu:Timestamp ...>
    <wsu:Created>...</wsu:Created>
    <wsu:Expires>...</wsu:Expires>
  </wsu:Timestamp>
</wsse:Security>

This was likely injected by a policy in Oracle Integration Cloud (OIC) or a SOAP client security configuration.

Step 3:  Suppress the insertion of timestamp in the request from the soap invoke connection. This will remove the Security Header from the request.

Reason: The target service does not support WS-Security, modify the request to remove the <wsse:Security> block completely.

Connection snap:

Failing payload:

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">

  <env:Header>

    <tns:MultiSpeakMsgHeader env:mustUnderstand="0" UserID="xxxx" Pwd="xxxx" xmlns:tns="http://www.multispeak.org/Version_5.0"/>

    <wsse:Security env:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">

      <wsu:Timestamp wsu:Id="TS-84" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">

        <wsu:Created>2025-05-09T09:07:58.512Z</wsu:Created>

        <wsu:Expires>2025-05-09T10:07:58.512Z</wsu:Expires>

      </wsu:Timestamp>

    </wsse:Security>

  </env:Header>

  <env:Body>

    <tns:MeterAddNotification xmlns:xml="http://www.w3.org/XML/1998/namespace" xmlns:tns="http://www.multispeak.org/Version_5.0">

      <tns:addedMeters>

        <tns:meter>

          <tns:meterNo>2345</tns:meterNo>

          <tns:utilityInfo>

            <tns:servLoc>SL#1435</tns:servLoc>

          </tns:utilityInfo>

        </tns:meter>

      </tns:addedMeters>

    </tns:MeterAddNotification>

  </env:Body>

</env:Envelope>

Updated working payload:

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">

  <env:Header>

    <tns:MultiSpeakMsgHeader env:mustUnderstand="0" UserID="xxxx" Pwd="xxxx" xmlns:tns="http://www.multispeak.org/Version_5.0"/>

  </env:Header>

  <env:Body>

    <tns:MeterAddNotification xmlns:xml="http://www.w3.org/XML/1998/namespace" xmlns:tns="http://www.multispeak.org/Version_5.0">

      <tns:addedMeters>

        <tns:meter>

          <tns:meterNo>2345</tns:meterNo>

          <tns:utilityInfo>

            <tns:servLoc>SL#1435</tns:servLoc>

          </tns:utilityInfo>

        </tns:meter>

      </tns:addedMeters>

    </tns:MeterAddNotification>

  </env:Body>

</env:Envelope>

Step 4: Test the Integration

Resend the modified payload from OIC or any SOAP client (e.g., SOAP UI or Postman with SOAP support). The endpoint should now process the request successfully.

OIC - Combining Source File Records into a Single Line in Oracle Integration Cloud (OIC)

Working...

Issue Statement:

In many real-time integrations, especially in ERP or data migration scenarios, source files are received in a structured format (e.g., one header record followed by multiple detail records). However, certain downstream systems or services may require this data to be flattened into a single string line (e.g., HeaderDetailDetailDetail...) before further processing. Oracle Integration Cloud (OIC) does not offer a direct activity to do this in a single step, so a custom approach is required.

Solution Steps:

Technique 1 : for small size source file

Read the file:

Read the source file as raw line by line using a sample csv file uaing ftp adapter.

Write File using stage and translate for each line new line :

translate(ns22:FileData, "&#xA;&#xD;", "")

This XSLT function removes newline (
) and carriage return (
) characters from each line, which is essential when combining multiple lines into a single line in OIC.

Technique 2: For bigger size file - 10k ~ 30k size.

TBD

OIC - How will you handle error in OIC integration? If you are processing multiple records, how will you ensure all the records are processed even if a single record errors out?

How will you handle error in OIC integration? If you are processing multiple records, how will you ensure all the records are processed even if a single record errors out?

Answer:

In Oracle Integration Cloud (OIC), when processing multiple records (for example, a JSON array or CSV data), handling errors gracefully is crucial to prevent the entire integration from failing due to a single bad record. Here's how to ensure all records are processed even if one fails:

1. Use a For-Each Action:
   Loop through the array or list of records using the For-Each action so each record is processed individually.

2. Wrap Logic Inside a Scope:
   Place your main processing logic for each record inside a Scope activity.

3. Add Fault Handler to Scope:
   Inside the Scope, add a Fault Handler to catch any errors specific to that record and log or handle them (e.g., write to a log file, invoke an error-handling integration, or call a notification).

4. Continue on Error:
   Since the fault is handled within the loop, the integration will not fail globally and will continue to the next record in the For-Each.

5. Track Success and Failures:
   Optionally, maintain a status array or write results to a lookup, object store, or DB table indicating success or failure for each record.

This way, each record is treated independently, and errors in one record do not block the processing of others.

OIC Lookup Import Error – "The Type, Name and Description Line is Invalid" [Resolved]

Issue Case:

While trying to re-import a modified Lookup CSV file back into Oracle Integration Cloud (OIC), an error occurred:

"Failed to import lookup file ‘XYZ.csv’. The lookup is formatted incorrectly. The Type, Name and Description line is invalid."

This happened despite exporting the Lookup directly from OIC and only updating its rows.

Root Cause:
Upon opening the exported CSV file in Notepad, it was observed that the header line:

Type,Name,Description,,,,,,,

...contained extra trailing commas. These additional commas beyond the expected three fields caused the file to be rejected by OIC during import, as it violates the expected format.

Solution Steps:

1. Open the exported Lookup CSV file using Notepad or any plain text editor.
2. Locate the first line that should read:

   Type,Name,Description
   

3. Remove all extra commas after "Description" so that only three fields are defined in the header row.
4. Save the file with the same CSV format (UTF-8 or ANSI).
5. Retry the import in OIC — it should now succeed without error.

OIC - Automating File-Based Integrations in OIC: A Scheduler-to-SFTP Flow Using Object Storage and Child Integrations

Use Case

In many enterprise scenarios, large volumes of files are generated daily and need to be processed, transformed, and delivered to target systems on a schedule. This blog outlines an OIC-based solution where a scheduled integration orchestrates the entire process: from validating input parameters, listing files from Oracle Object Storage, and invoking a child integration to download, transform, and upload the files to an SFTP server.

Solution Architecture

The solution consists of three integrations:

1. Scheduler Integration – Initiated with a fileProcessingDate parameter (format: yyyyMMdd) and validates its format. This is empty by default that means current date will be used to fetch the file. but if someone wants to reprocess the file, have to put the file processing date.
2. Main Integration – Lists files from a configured Object Storage path based on prefix and and date filter in the file name and loops over each file and invokes the child integration.
3. Child Integration – Downloads each file, performs necessary transformation, and uploads it to a specified target SFTP location.

Solution Steps

1. Scheduler Integration

• Trigger Type: Scheduled
• Input Parameter: fileProcessingDate (string, format: yyyyMMdd)
• Step 1: Validate fileProcessingDate using an inline expression or JavaScript function.
  • Throw a fault if the format is invalid.
• Step 2: Call the Main Integration, passing fileProcessingDate as a parameter.

2. Main Integration

• Trigger Type: App Driven (HTTP or SOAP)
• Input: fileProcessingDate
• Step 1: Use List Objects action (Object Storage adapter) to list files in the bucket/prefix corresponding to fileProcessingDate.
• Step 2: Loop over each file name from the response.
• Step 3: For each file, call the Child Integration and pass:
  • File name
  • File path (if required)
  • fileProcessingDate (if used in downstream processing)

3. Child Integration

• Trigger Type: App Driven
• Inputs:
  • File name
  • File path (optional)
• Step 1: Use Get Object action from Object Storage to download the file.
• Step 2: Apply transformation (e.g., map fields, convert formats).
• Step 3: Use FTP Adapter to upload the file to the target SFTP folder.

OIC - Understanding self::node() XSLT function in Oracle Integration cloud

In Oracle Integration Cloud (OIC) XSLT, the XPath function self::node() is used to reference the current node in a predicate or expression.

Purpose of self::node()

It ensures that the comparison or operation is being done on the current node itself, rather than its child or attribute.

Example Usage in OIC

In your expression:

fn:count(ns95:dtstart[(self::node() = $FileProcessingDate)])

Here:

• self::node() refers to the value of the current dtstart element.
• It compares that value directly to $FileProcessingDate.

Why use self::node()?

While . (dot) also refers to the current node, self::node() is more explicit and avoids ambiguity in complex expressions, especially when namespaces or deeper hierarchies are involved in OIC mappings.

Simplified Equivalent

This:

self::node() = $FileProcessingDate

Is functionally equivalent to:

. = $FileProcessingDate

But self::node() is sometimes preferred in tools like OIC for clarity or compatibility.

OIC - How to skip File Processing Excluding Hong Kong Public Holidays in Oracle Integration Cloud Using 1823 API

Use Case:

When processing data files through Oracle Integration Cloud (OIC), certain business operations must be skipped on public holidays. To automate this, we want to check whether a file's processing date falls on a Hong Kong public holiday using the 1823.gov.hk public API. If it is a holiday, the integration should stop gracefully.

API used: Https://www.1823.gov.hk/common/ical/en.json

Solution Overview:

We will implement the following in Oracle Integration Cloud (OIC):

1. Create a REST connection to consume the Hong Kong 1823 Public Holiday API.
2. Fetch and parse the public holiday list.
3. Compare the input fileProcessingDate with the holiday list.
4. If it is a holiday, stop the integration using a Switch action.

Step-by-Step Implementation:

1. Create REST Connection in OIC

• Go to Connections > Create > Choose REST Adapter.
• Set the name (e.g., HK_PublicHoliday_API).
• Set the connection URL:
  https://www.1823.gov.hk
• Security: Select “No Security Policy”.
• Test and save the connection.

2. Build the Integration

• Create a new Scheduled Integration.
• Add a Schedule Parameter: fileProcessingDate (type: date or string in yyyy-MM-dd format).
• Add an Invoke action to call the REST Connection created earlier.
  • Method: GET
  • URI: /common/ical/en.json
  • Configure the response JSON to capture the public holiday data.

3. Add a Switch Action

• Put Condition: fn:count($GetHKHolidayList/ns96:executeResponse/ns95:response-wrapper/ns95:vcalendar/ns95:vevent/ns95:dtstart[self::node()= /ns91:execute/ns88:request-wrapper/ns88:ProcessRequest/ns88:FileProcessingDate]) > 0

• If condition matches: add a stop action which will stop to proceed further.

5. Test the Integration

• Test with various fileProcessingDate values including a known public holiday (e.g., 2025-01-01) and a regular working day.
• Confirm that the integration stops only on public holidays.

OIC - Padding leading zeros to a number field using xslt format-number()

In many payment-related integrations, credit card numbers often arrive as plain numeric strings. For security and compliance—and to meet target schema requirements—we need to standardize their format (e.g., always output 16‑digit strings, padding with leading zeros when necessary). In this post, we’ll explore a simple XSLT/Expression Builder approach in Oracle Integration Cloud to achieve consistent credit card number formatting.

---

🎯 Use Case

• Source System: A front-end or legacy application that sends credit‑card numbers without leading zeros (e.g., “123456789012345”).
• Target System: A downstream ERP or card‑processing API requiring exactly 16 digits (e.g., “0123456789012345”).
• Challenge: Ensure every card number string is left‑padded with zeros up to 16 characters, without manually writing complex code.

---

🚀 Solution Steps

1. Locate the Target Element
   In your OIC mapping or XSLT, identify the node where the formatted card number should go (e.g., <ns24:ExternalCardNumber>).

2. Use the fn:format-number() Function
   Leverage the built‑in XPath function in OIC’s Expression Builder to pad the numeric string. The format mask for 16 digits is simply 16 zeros: "0000000000000000".

3. Write the Expression
   In the Expression Builder for ns24:ExternalCardNumber, enter:

   fn:format-number(ns22:CreditcardNumber, "0000000000000000")
   

   • Argument 1: ns22:CreditcardNumber – the incoming numeric string
   • Argument 2: "0000000000000000" – the format mask specifying 16-digit width, padded with leading zeros

4. Test with Sample Payloads

   • Input: <CreditcardNumber>123456789012345</CreditcardNumber> → Output: <ExternalCardNumber>0123456789012345</ExternalCardNumber>
   • Input: <CreditcardNumber>987654321</CreditcardNumber> → Output: <ExternalCardNumber>0000000987654321</ExternalCardNumber>

5. Deploy and Validate

• Activate your integration and send real messages.
• Verify target payloads always carry exactly 16 digits, meeting downstream requirements.

OIC - How to Add Blank Spaces When Creating Fixed-Length Files using javascript

Use Case:

When building integrations in Oracle Integration Cloud (OIC), you might need to generate fixed-length flat files. In such files, each field must occupy a specific number of characters—even if the data is shorter or sometimes missing.

Let’s say a field requires 10 characters, but the actual value is only 6 characters. In this case, you need to add 4 blank spaces to maintain the correct file structure.

To solve this, you can use a custom JavaScript function to generate blank spaces and pad the fields properly.

Solution: How to Add Blank Spaces (Padding) in Fixed-Length Output

You can write a small JavaScript function that returns a string of spaces based on a given length.

Here’s a sample function:

function clp_getSpaces(length) {
    var spaces = ' '.repeat(length);
    return spaces;
}

What This Does:

• Takes a number (length) as input.
• Uses the .repeat() method to return that many blank spaces.

Steps to Use in Integration:

Step 1: Create the JavaScript Function

Use the function above in your utility library or directly in your integration if scripting is allowed.

Step 2: Use It While Constructing the Flat File Line

For example:

var employeeName = 'John';
var paddedName = employeeName + clp_getSpaces(10 - string-length(employeeName));

This ensures that even if employeeName is short, the total field length is still 10 characters.

Step 3: Write to File Using Stage Activity

Use this padded string as input to your stage file write operation. This will ensure correct alignment of all fields in your fixed-length file.

Why This Is Important:

Fixed-length files are strict in formatting. If the alignment is off, downstream systems might reject the file or misread the data. Padding with spaces ensures compliance with the required structure.

[OIC Error Fix] Fixed length File Translation Failure While Writing File: 'Element CreationDate Not Expected'

Fixing Fixed-Length File Translation Error in OIC: "Element 'CreationDate' Not Expected"

Use Case:

You’re building an integration in Oracle Integration Cloud (OIC) that writes data to a fixed-length flat file using the Stage File action. The NXSD schema includes a field called CreationDate. However, during runtime, OIC throws this error:

Translation Failure: Element 'CreationDate' not expected. The data does not conform to the NXSD schema.

Root Cause (Simple Explanation):

In fixed-length files, every field must appear in the exact order and position as defined in the NXSD schema.

If any field before CreationDate is:

• Not mapped
• Mapped incorrectly
• Mapped with the wrong length

… then the data will shift in the output file. As a result, CreationDate shows up at the wrong spot — making OIC think it’s unexpected.

Solution Steps:

Step 1: Review NXSD Schema

• Open the NXSD file used in the Stage File activity.
• Check the order of the fields and their lengths.
• Confirm that CreationDate is present and has the right definition.

Step 2: Check Field Mappings

• Go to the mapping just before the Stage File action.
• Make sure all fields before CreationDate are mapped.
• If a field is optional and not present in the source:
  • Map it to a blank value with the correct length (e.g., ' ' for a 5-character field).

Step 3: Test the Output

• Use the Test button or enable tracing to check the actual output string.
• Verify if the data aligns with the fixed-length format.

Step 4: Regenerate NXSD (if needed)

• If you recently changed the schema, re-import or reconfigure the Stage File activity to ensure it uses the latest NXSD version.

OIC - Managing Integration Limits in Oracle Integration Cloud (OIC) Using Projects and Shared Connections

Use Case:

In Oracle Integration Cloud (OIC), you may encounter limits like:

• 100 Integrations per project
• 50 Connections per project

As your environment grows, hitting these limits can cause issues in maintenance, deployment, and governance.

So, how do you scale your OIC environment effectively?

Solution Overview:

To handle this, follow a modular design using multiple projects:

Best Practice Strategy:

1. Create multiple domain-specific projects (e.g., HR, Finance, Procurement) and assign the **serviceDeveloper** role to developers who need edit access.
2. Create a common project (e.g., CommonResources) that holds shared connections like HCM, ERP, REST, FTP.
3. Use shared connections from the common project in other integration projects.
4. We can also share integrtaions from COMMON project and then we can copy them into another project.
5. We can also invoke shared integrtaions as local integration call from other projects.

Step-by-Step Guide

1. Create a New Project in OIC:

• Go to OIC Home > Projects
• Click “+ Create Project”
• Give a name like FinanceProject, HRProject, or CommonResources
• Add description and tags (optional)
• Click Create
• Assign access edit, view and monitor role as expected.

2. Add Connections in Common Project:

• Open the CommonResources project
• Go to Connections
• Click “+ Create”, configure REST, SOAP, HCM, ERP, etc.
• Share with other projects.
• These will now be available as shared connections

3. Share Connection with Other Projects:

• Go to the destination project (e.g., HRProject)
• Click Connections > + Add Shared Connection
• Select from the list of connections created in CommonResources
• Click Add

Now, that connection is usable in your integrations in that project, without recreating it.

4. Assign ServiceDeveloper Role:

• Go to OIC Console > Identity & Access
• Click Users > Assign Roles
• Select user
• Assign OIC_ServiceDeveloper role
• This gives them full edit access to integrations, connections, and lookups in that project.

Benefits of This Approach:

• Avoid hitting integration/connection limits
• Easier access control and role assignment
• Better modularization of logic by domain
• Improved governance and maintainability

we can also copy one integration to another project as below:

Share the integration:

Copy to the project as required.