Friday, May 30, 2025

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.


at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Featured Post

OIC - OIC Utility to Reprocess Failed Real-Time Integration JSON Payloads

📌 Use Case In real-time OIC integrations, JSON payloads are exchanged with external systems via REST APIs. When such integrations fail (du...