In an Oracle Integration Cloud (OIC) flow, we receive Unifier UDR report rows where the creation date is in MM/DD/YYYY HH:MM AM/PM format.
To fetch only the latest project record, we must:
- Convert the UDR date/time to a sortable format →
YYYYMMDDHHMM00 - Sort all rows in descending order
- Pick the latest row’s details
This allows us to correctly extract the most recent department/project data for further processing.
1. Extract Date Components Using XSLT
From input (example):
05/21/2025 07:15 PM
We extract:
- Month →
05 - Day →
21 - Year →
2025
2. Extract Time Components
- Hour →
07 - Minute →
15 - AM/PM →
PM
3. Convert 12-Hour Time to 24-Hour Format
Example conversion logic:
| Input | Output |
|---|---|
| 07:15 PM | 19:15 |
| 12:30 AM | 00:30 |
| 11:45 AM | 11:45 |
In XSLT:
- If PM and hour≠12 →
hour + 12 - If AM and hour=12 →
00
4. Combine Into Sortable Format
Final sortable value:
YYYYMMDDHHMM00
Example:
Input:
05/21/2025 07:15 PM
Converted:
20250521191500
5. Sort and Pick Latest Record
Use XSLT <xsl:sort order="descending"> to ensure the newest timestamp appears first.
6. Build Output Structure
After sorting, the latest record is passed to the next system inside:
<NewProjectDetails>
<departmentId>...</departmentId>
<projectId>...</projectId>
<creationTime>20250521191500</creationTime>
</NewProjectDetails>
By converting the date/time into a sortable format and applying descending sort, OIC always fetches the most recent project details, even if multiple rows exist for different departments.
Following xslt to convert from 05/21/2025 07:15 PM to sortable format: YYYYMMDDHHMM00
<xsl:template match="/" xml:id="id_11">
<nstrgmpr:Write xml:id="id_12">
<ns40:request-wrapper>
<xsl:for-each select="$GetlatestDepartmentId1/nsmpr1:executeResponse/ns33:response-wrapper/ns33:data/ns33:report_row">
<!-- Extract Date parts (MM/DD/YYYY) -->
<xsl:variable name="month" select="substring(ns33:c3, 1, 2)"/>
<xsl:variable name="day" select="substring(ns33:c3, 4, 2)"/>
<xsl:variable name="year" select="substring(ns33:c3, 7, 4)"/>
<!-- Extract Time parts (HH:MM AM/PM) -->
<xsl:variable name="timePart" select="substring-after(ns33:c3, ' ')"/>
<xsl:variable name="hourStr" select="substring($timePart, 1, 2)"/>
<xsl:variable name="minute" select="substring($timePart, 4, 2)"/>
<xsl:variable name="amPm" select="substring($timePart, 7, 2)"/>
<!-- Convert 12-hour hour to 24-hour -->
<xsl:variable name="hour24">
<xsl:choose>
<!-- If PM and hour is not 12, add 12 -->
<xsl:when test="$amPm = 'PM' and number($hourStr) != '12'">
<xsl:value-of select="number($hourStr) + 12"/>
</xsl:when>
<!-- If AM and hour is 12 (midnight), set to 00 -->
<xsl:when test="$amPm = 'AM' and number($hourStr) = '12'">
<xsl:value-of select="'00'"/>
</xsl:when>
<!-- Otherwise, use the hour as is -->
<xsl:otherwise>
<xsl:value-of select="$hourStr"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<!-- Pad single digit hours with a leading zero if necessary (e.g., 9 becomes 09) -->
<xsl:variable name="paddedHour">
<xsl:choose>
<xsl:when test="string-length($hour24) = 1">
<xsl:value-of select="concat('0', $hour24)"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$hour24"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<ns40:NewProjectDetails>
<ns40:departmentId>
<xsl:value-of select="ns33:c1"/>
</ns40:departmentId>
<ns40:projectId>
<xsl:value-of select="ns33:c2"/>
</ns40:projectId>
<ns40:creationTime>
<xsl:value-of select="concat($year, '-', $month, '-', $day, ' ', $paddedHour, ':', $minute, ':00')"/>
</ns40:creationTime>
</ns40:NewProjectDetails>
</xsl:for-each>
</ns40:request-wrapper>
</nstrgmpr:Write>
</xsl:template>
Following xslt to sort data based on sortable creation date and map latest data:
<xsl:template match="/" xml:id="id_11">
<nstrgmpr:execute xml:id="id_12">
<nstrgmpr:HTTPHeaders>
<ns54:StandardHTTPHeaders>
<ns54:Authorization>
<xsl:value-of select="concat ("Bearer ", $GetUnifierToken/nsmpr1:executeResponse/ns36:response-wrapper/ns36:data/ns36:access_token )"/>
</ns54:Authorization>
</ns54:StandardHTTPHeaders>
</nstrgmpr:HTTPHeaders>
<ns52:request-wrapper xml:id="id_41">
<ns52:reportname xml:id="id_45">
<xsl:value-of xml:id="id_46" select="dvm:lookupValue ("ABC_Common_CORS_Static_Data_Lookup", "Key", "UNIFIER_USERGROUPBYDEPARTMENT_UDR_REPORTNAME", "Value", "NA" )"/>
</ns52:reportname>
<ns52:query xml:id="id_42">
<ns52:label xml:id="id_100">
<xsl:value-of xml:id="id_101" select="'PROJECTNUMBER'"/>
</ns52:label>
<xsl:for-each select="$WriteDepartmentId REQUEST/nsmpr3:Write/ns38:request-wrapper/ns38:NewProjectDetails">
<xsl:sort select="ns38:creationTime" data-type="xsd:string" order="descending"/>
<xsl:if test="position() = 1">
<ns52:value1>
<xsl:value-of select="ns38:departmentId"/>
</ns52:value1>
</xsl:if>
</xsl:for-each>
</ns52:query>
</ns52:request-wrapper>
</nstrgmpr:execute>
</xsl:template>
No comments:
Post a Comment