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:
StubRecordPaymentRecord
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)
