OIC - how can I use XSLT functions to remove leading zeros from numeric and alphanumeric fields?

To remove leading zeros from an numeric field in Oracle Integration Cloud (OIC) using XSLT, you can Use number() Function

The number() function automatically converts a string with leading zeros into a numeric value, effectively removing the leading zeros.

<xsl:value-of select="number(input_element)" />

Example:

Input: "000123"

Output: 123

To remove leading zeros from an alphanumeric string in Oracle Integration Cloud (OIC) using XSLT, you can use the replace() function in combination with regular expressions. Here's how you can achieve it:

Explanation:

1. Input Element: Replace your_input_element with the XPath to the input value.

2. Regular Expression:

^0+ matches one or more zeros (0) at the start (^) of the string.

3. Replace Function: The replace() function removes the matched zeros by replacing them with an empty string ('').

Input Example:

If the input is 00123ABC, the result will be 123ABC.

Xslt code:

<xsl:template match="/">

    <result>

        <xsl:value-of select="replace(your_input_element, '^0+', '')"/>

    </result>

</xsl:template>

OIC - Splitting Fixed-Length File Based on batch header Terminal Numbers into 2 separate files using xslt mapping.

Use Case: 

OIC - Splitting Fixed-Length File Based on batch header Terminal Numbers into 2 separate files using xslt mapping.

In integration workflows, processing fixed-length files is a common requirement. A typical fixed-length file might contain hierarchical data structured as:

• 1. File Header: Represents metadata for the file.
• 2. Batch Header: Denotes the start of a batch, including terminal-specific identifiers (e.g., 001 or 002).
• 3. Detail Records: Contains individual transaction or data entries for each batch.
• 4. Batch Trailer: Marks the end of a batch.
• 5. File Trailer: Marks the end of the entire file.

Problem Statement:

Given a fixed-length file structured as above, the requirement is:

Identify Batch Headers containing specific terminal numbers (e.g., 001, 002).

Split the file into separate outputs based on these terminal numbers.

Transform each split batch into a target file format for further processing.

Example Input File:

File Header  

Batch Header (001 Terminal)  

Detail  

Detail  

Batch Trailer  

Batch Header (002 Terminal)  

Detail  

Detail  

Batch Trailer  

File Trailer

Expected Output:

File 1: Contains data related to 001 terminal.

Batch Header (001 Terminal)  

Detail  

Detail  

Batch Trailer  

File 2: Contains data related to 002 terminal.

Batch Header (002 Terminal)  

Detail  

Detail  

Batch Trailer  

Solution Overview:

1. File Parsing: Read the the fixed-length file as csv sample file. 

2. Get batch header position: identify the positions of Batch Headers with terminal numbers 001 and 002.

3. Splitting Logic: Extract data between Batch Header and Batch Trailer for each terminal number 001 and 002 respectively using the positions fetched in step2.

4. Read splited fixed length files: using nxsd, read the files.

3. Transformation: Convert the split content into the desired target file format (e.g., XML or JSON).

4. Output Generation: Write the transformed content into separate output files.

This solution ensures modular processing of hierarchical data, enabling seamless integration into downstream systems.

Xslt code Used for getting the batch header position for 001 and 002:

<xsl:template match="/" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xml:id="id_11">

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

        <ns28:RecordSet>

            <xsl:variable name="CircleKPosition">

                <xsl:for-each select="$ReadSourceFile/nsmpr2:ReadResponse/ns26:RecordSet/ns26:Record" xml:id="id_48">

                    <xsl:choose>

                        <xsl:when test="contains(ns26:Data, &quot;RH&quot;) and (substring(ns26:Data, 23, 3) = &quot;001&quot;)">

                            <xsl:value-of select="position()" />

                        </xsl:when>

                    </xsl:choose>

                </xsl:for-each>

            </xsl:variable>

            <xsl:variable name="VangoPosition">

                <xsl:for-each select="$ReadSourceFile/nsmpr2:ReadResponse/ns26:RecordSet/ns26:Record" xml:id="id_48">

                    <xsl:choose>

                        <xsl:when test="contains(ns26:Data, &quot;RH&quot;) and (substring(ns26:Data, 23, 3) = &quot;002&quot;)">

                            <xsl:value-of select="position()" />

                        </xsl:when>

                    </xsl:choose>

                </xsl:for-each>

            </xsl:variable>

            <ns28:Record>

                <ns28:CircleK>

                    <xsl:value-of select="$CircleKPosition" />

                </ns28:CircleK>

                <ns28:Vango>

                    <xsl:value-of select="$VangoPosition" />

                </ns28:Vango>

            </ns28:Record>

        </ns28:RecordSet>

    </nstrgmpr:Write>

</xsl:template>

Xslt code for spliting for 001 file : same way we have to do for 002.

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

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

        <ns31:RecordSet xml:id="id_56">

            <xsl:choose xml:id="id_59">

                <xsl:when test="number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:CircleK) &lt; number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:Vango)" xml:id="id_60">

                    <xsl:for-each select="$ReadSourceFile/nsmpr2:ReadResponse/ns28:RecordSet/ns28:Record[position() &lt; number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:Vango)]" xml:id="id_61">

                        <ns31:Data>

                            <xsl:value-of select="ns28:Data" />

                        </ns31:Data>

                    </xsl:for-each>

                </xsl:when>

                <xsl:when test="number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:CircleK) &gt; number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:Vango)" xml:id="id_62">

                    <xsl:for-each select="$ReadSourceFile/nsmpr2:ReadResponse/ns28:RecordSet/ns28:Record[position() &gt;= number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:CircleK)]" xml:id="id_63">

                        <ns31:Data>

                            <xsl:value-of select="ns28:Data" />

                        </ns31:Data>

                    </xsl:for-each>

                </xsl:when>

            </xsl:choose>

        </ns31:RecordSet>

    </nstrgmpr:Write>

</xsl:template>

Screenhots:

For getting batch header positions

For splitting the content.

Eee

OIC - difference between SOAP and REST

Difference between SOAP and REST:

SOAP (Simple Object Access Protocol)

Protocol: A strict protocol for message exchange with built-in standards for security, transactions, and error handling.

Message Format: Always uses XML, requiring a structured SOAP envelope with headers and body.

Transport: Can use multiple transport protocols like HTTP, SMTP, TCP, etc.

Features:

Supports WS-Security for secure message exchange.

Better suited for stateful operations (e.g., sessions).

Heavy and slower due to XML parsing and strict standards.

Use Cases: Enterprise applications requiring high security, reliability, and formal communication (e.g., financial transactions, payment gateways).

REST (Representational State Transfer)

Architecture: A lightweight architectural style, not a protocol, that uses standard HTTP methods (GET, POST, PUT, DELETE).

Data Formats: Supports multiple formats like JSON, XML, HTML, or plain text (JSON being the most common).

Transport: Relies exclusively on HTTP.

Features:

Stateless, meaning each request is independent of others.

Faster and more efficient due to lightweight communication and flexible data formats.

Easy to implement and widely used for modern APIs.

Use Cases: Web and mobile applications, microservices, and scenarios where performance and scalability are priorities.

In summary:

SOAP is rigid, heavy, and ideal for complex, secure operations.

REST is simple, flexible, and optimized for web-based services.

OIC - Can we send XML input in rest adapter?

Yes, the REST adapter in OIC can handle XML input.

1. Set the media type to application/xml.
2. Define an XML schema (XSD) for payload mapping.
3. Include Content-Type: application/xml in the request header.
4. Test with XML payloads using Postman or curl.

Ensure proper configuration and schema matching for seamless processing.

Screenshots:

TBD

OIC - Use of nested DVMs or lookups to implement conditional logic | Enhancing Error Handling in OIC with Conditional DVM Lookups

Here, we will demonstrate how nested DVMs or lookups can be utilized to implement conditional logic effectively.

Use Case:

Error handling is a critical component in integrations. In Oracle Integration Cloud (OIC), different error codes might require specific severity levels for better classification. For example:

Specific error codes from external systems (e.g., ERROR_CODE) map to severity levels like CRITICAL, HIGH, or LOW.

If an unknown or generic error occurs, it defaults to a predefined severity level (e.g., NA).

To achieve this, we use nested DVM lookups to dynamically retrieve severity levels based on the error context.

Code Explanation

The provided code handles dynamic severity mapping based on two levels of logic:

1. Primary Lookup: Retrieves the SEVERITY based on the error code received (ERROR_CODE) from the payload.

2. Fallback Logic: If the error code is missing or invalid, a second lookup retrieves a default severity (GENERICOICTECHNICALERROR) to ensure robustness.

Expression Used:

dvm:lookupValue("Common_Error_Details_Lookup","ERROR_CODE",$GlobalFaultObject/ns0:fault/ns0:errorCode,"SEVERITY",dvm:lookupValue("Common_Error_Details_Lookup","KEY","GENERICOICTECHNICALERROR","SEVERITY","NA"))

How It Works:

1. Outer Lookup:

File: Common_Error_Details_Lookup.

Uses ERROR_CODE from the payload ($GlobalFaultObject/ns0:fault/ns0:errorCode).

Retrieves the corresponding SEVERITY.

2. Inner Lookup (Fallback):

File: Common_Error_Details_Lookup.

Key: GENERICOICTECHNICALERROR.

Retrieves a default severity (SEVERITY) if the primary lookup fails or returns null.

Key Benefits

1. Dynamic Mapping: Avoids hardcoding severity levels by using reusable DVM files.

2. Robust Error Handling: Implements a fallback mechanism for unknown or generic errors.

3. Centralized Control: Simplifies maintenance by managing mappings in one place (DVM).

4. Seamless Integration: Ensures consistent error handling across systems and reduces potential failures.

Practical Example

DVM File Structure

Conclusion

This approach highlights how OIC's DVM lookup capability can be extended to include conditional logic, ensuring robust and scalable error handling mechanisms in integration flows. By leveraging nested DVMs, developers can streamline error classification while minimizing manual intervention.

OIC - XSLT - "Using format-number() to Add Leading Zeros to Single-Digit Numbers"

Use Case:

In data processing or reporting scenarios, leading zeros are crucial for ensuring consistency and accuracy in how numbers are displayed, especially for dates. 

For example, when formatting dates such as "Day 1" or "Day 9," it’s essential to present them as "01" and "09" to maintain uniformity, especially when aligning values in tables or spreadsheets. 

Using the format-number function, such as fn: format-number(ns28: Input Day, "00"), ensures that single-digit day values are always presented with a leading zero, improving both readability and data integrity in systems requiring consistent date formatting.

Code:

fn: format-number (ns28: Input Day, "00") 

OIC - XSLT - "Dynamic Payment Date Adjustment with XSLT: Handling Conditional Date Formatting"

Usecase:

This XSLT code dynamically adjusts the payment date (H_I_PAY_DATE) based on the comparison of the InputDay and the day part of the ValueDate from the source file. It formats the date by concatenating the year-month from ValueDate and the day from InputDay, or adjusts the date if necessary, ensuring proper formatting for payment processing.

Code used:

<xsl:choose>

<xsl:when test="number (ns28:InputDay) &lt; number (substring ($ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 7, 2))">

<ns23:H_I_PAY_DATE xml:id="id_76">

<xsl:value-of select="concat (substring ($ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 1, 6), fn:format-number (ns28:InputDay, '00'))"/>

</ns23:H_I_PAY_DATE>

</xsl:when>

<xsl:when test="(( number (ns28:InputDay)) &gt; ((number (substring ($ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 7, 8)))  and ((number (substring ($ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 5, 2)) -1) =0))">

<ns23:H_I_PAY_DATE xml:id="id_209">

<xsl:value-of select="concat ((number(substring ($ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 1, 4)) -1), '12', fn:format-number (number (substring (ns28:InputDay, 1, 2)), '00'))"/>

</ns23:H_I_PAY_DATE>

</xsl:when>

<xsl:when test="((number (ns28:InputDay ) &gt; number (substring ($ReadSourceFile/nsmpre:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 7, 8 ) )) and ((number (substring ($ReadSourceFile/nsmpre: ReadResponse/ns28: Payments/ns28: FileHeader/ns28: ValueDate, 5, 2)) - 1) != 0))">

<ns23:H_T_PAY_DATE xml:id="id_76">

<xsl:value-of xml:id="id_215" select="concat (substring ($ReadSourceFile/nsmpro: ReadResponse/ns28: Payments/ns28: FileHeader/ns28:ValueDate, 1, 4), fn: format-number ((number (substring ($ReadSourceFile/nsmpre: ReadResponse/ns28: Payments/ns28: FileHeader/ns28:ValueDate, 5, 2)) - 1), &quot;00&quot;), fn: format-number (ns28: Input Day, &quot;00&quot;) )"/>

</ns23:H_T_PAY_DATE>

</xsl:when>

<xsl:otherwise>

<ns23:H_I_PAY_DATE xml:id="id_210">

<xsl:value-of select="$ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate"/>

</ns23:H_I_PAY_DATE>

</xsl:otherwise>

</xsl:choose>

Screenshot: