OIC - Handling Complex Stub and Payment Mapping in Oracle Integration Cloud (OIC)

Introduction

In real-time file-based integrations using Oracle Integration Cloud (OIC), handling relationships between Stub Records and Payment Records can become tricky.

A common scenario:

• One Stub → Multiple Payments
• Multiple Stubs → One Payment

This blog explains how to handle such complex mappings using XSLT logic inside OIC.

---

Problem Statement

From the source file:

• We receive a structure containing:
  • StubRecord
  • PaymentRecord

But the relationship is not always 1:1:

• Sometimes 1 Stub → Multiple Payments
• Sometimes Multiple Stubs → 1 Payment

We need to: ✔ Match records using Transaction Number
✔ Ensure correct mapping between Stub and Payment
✔ Avoid duplication or data mismatch

---

Approach

Step 1: Iterate Over Payment Records

We start by looping through each Payment Record:

<xsl:for-each select="Payments/PaymentRecord">

---

Step 2: Check Matching Stub Count

We check how many Stub Records match the current Payment using Transaction Number:

count(StubRecord[TransactionNumber = current()/TransactionNumber])

---

Step 3: Conditional Logic (1:1 vs Multiple)

Case 1: 1 Stub ↔ 1 Payment

If count = 1:

• Direct mapping is done
• Simple and straightforward

<xsl:when test="count(...) = 1">

✔ Map fields directly
✔ No extra handling required

---

Case 2: Multiple Mapping Scenario

If count > 1:

• Either:
  • One Payment → Multiple Stubs
  • Multiple Payments → One Stub

👉 Here we handle carefully using variables

---

Step 4: Store Payment Info in Variable

We store payment-related data in variables so it can be reused:

<xsl:variable name="paymentAmount" select="PaymentAmount"/>
<xsl:variable name="transactionNumber" select="TransactionNumber"/>

This helps in: ✔ Avoiding repeated calculations
✔ Reusing values across multiple stub mappings

---

Step 5: Loop Through Stub Records

Now, iterate through matching Stub Records:

<xsl:for-each select="StubRecord[TransactionNumber = $transactionNumber]">

Inside this loop:

• Map stub-specific fields
• Use stored payment variables

---

Step 6: Final Mapping Strategy

Scenario	Logic
1 Stub → 1 Payment	Direct mapping
1 Stub → Multiple Payments	Loop Payments
Multiple Stubs → 1 Payment	Store Payment in variable, loop Stubs
Multiple ↔ Multiple	Combine filtering + variables

---

Key Highlights

✔ Use current() for correct context reference
✔ Use count() to identify mapping scenarios
✔ Use xsl:variable to store reusable values
✔ Always filter using Transaction Number

---

Sample Logic Summary

<xsl:template match="/">

  <Target>

    <!-- Loop Payment Records -->
    <xsl:for-each select="ReadResponse/Payments/PaymentRecord">

      <xsl:variable name="txn" select="TransactionNumber"/>

      <Record>

        <!-- Payment Info -->
        <PaymentTxn>
          <xsl:value-of select="$txn"/>
        </PaymentTxn>

        <PaymentAmount>
          <xsl:value-of select="PaymentAmount"/>
        </PaymentAmount>

        <!-- Loop matching Stub Records -->
        <xsl:for-each select="/ReadResponse/Payments/StubRecord[TransactionNumber = $txn]">

          <Stub>

            <StubAccount>
              <xsl:value-of select="StubAccountNumber"/>
            </StubAccount>

            <StubAmount>
              <xsl:value-of select="StubAmount"/>
            </StubAmount>

          </Stub>

        </xsl:for-each>

      </Record>

    </xsl:for-each>

  </Target>

</xsl:template>

---

Conclusion

Handling complex relationships between Stub and Payment records requires:

• Smart use of XSLT looping
• Conditional checks using count()
• Efficient reuse using variables

By implementing this approach, you can: ✔ Ensure accurate 1:1 mapping
✔ Handle multiple scenarios seamlessly
✔ Avoid duplication and mismatches

---

Pro Tip 💡

Always validate your mapping with:

• Single record case
• Multiple record case
• Edge cases (missing or unmatched Transaction Numbers)