ERP - BI report XML output using XSL template

Usecase: 

Here, we will create a custom fields XML Output BI report using XSL template.

Check my previous blog how to create BI report using xsl template

https://soalicious.blogspot.com/2022/03/create-bi-report-using-xsl-template.html

.xsl template used:

Syntax1:

<?xml version="1.0" encoding="utf-8"?><xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/">

<document>

<xsl:element name="SupplierData">

<xsl:for-each select="DATA_DS/G_1">

<xsl:element name="Supplier">

<xsl:element name="SUPPLIER_NAME">

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

</xsl:element>

<xsl:element name="SUPPLIER_NUMBER">

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

</xsl:element>

<xsl:element name="SUPPLIER_ID">

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

</xsl:element>

<xsl:element name="COUNTRY">

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

</xsl:element>

<xsl:element name="CITY">

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

</xsl:element>

<xsl:element name="STATUS">

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

</xsl:element>

</xsl:element>

</xsl:for-each>

</xsl:element>

</document>

</xsl:template>

</xsl:stylesheet>

Syntax2:

<?xml version="1.0" encoding="utf-8"?><xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/">

<document>

<SupplierData>

<xsl:for-each select="DATA_DS/G_1">

<Supplier>

<SUPPLIER_NAME>

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

</SUPPLIER_NAME>

<SUPPLIER_NUMBER>

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

</SUPPLIER_NUMBER>

<SUPPLIER_ID>

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

</SUPPLIER_ID>

<COUNTRY>

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

</COUNTRY>

<CITY>

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

</CITY>

<STATUS>

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

</STATUS>

</Supplier>

</xsl:for-each>

</SupplierData>

</document>

</xsl:template>

</xsl:stylesheet>

Dont forget to change the default format from html to xml.

Output report:

For multi group sample:

ERP - Create a BI report text output using XSL template

Usecase: 

Here, we will create a text Output BI report using XSL template. 

Why we should use xsl template?

• XSL Stylesheet is generally used to generate text, xml, html and FO.
• Rich in xslt function like format-number(), count(), sum() etc.
• user friendly syntax in xml format.

.xsl template:

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="2.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:exslt="http://exslt.org/common"

xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.xdo.template.rtf.XSLTFunctions"

xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xsl:output method="text" omit-xml-declaration="yes" indent="no"/>

<xsl:template match="/">

<xsl:text>supplier_name</xsl:text>

<xsl:text>,</xsl:text>

<xsl:text>supplier_number</xsl:text>

<xsl:text>,</xsl:text>

<xsl:text>supplier_id</xsl:text>

<xsl:text>,</xsl:text>

<xsl:text>country</xsl:text>

<xsl:text>,</xsl:text>

<xsl:text>city</xsl:text>

<xsl:text>,</xsl:text>

<xsl:text>status</xsl:text>

<xsl:text>&#xa;</xsl:text>

<xsl:for-each select="DATA_DS/G_1">

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

<xsl:text>,</xsl:text>

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

<xsl:text>,</xsl:text>

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

<xsl:text>,</xsl:text>

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

<xsl:text>,</xsl:text>

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

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

<xsl:text>&#xa;</xsl:text>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>

Data model query used:

Select hp.party_name supplier_name,

poz.segment1 supplier_number,

poz.vendor_id supplier_id,

hp.email_address,

hp.country,

hp.city,

hp.status

from POZ_SUPPLIERS poz, HZ_PARTIES hp where 1=1 

And poz.party_id = hp.party_id

And rownum<=10

Detailed steps with Screenshots:

WSDL - Generic interface for anytype xml

 Generic interface for parent flow. The wsdl should support any type of payload:

<definitions xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:wsp="http://www.w3.org/ns/ws-policy" xmlns:wsp1_2="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wsam="http://www.w3.org/2007/05/addressing/metadata" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:tns="http://mock.service.com/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.xmlsoap.org/wsdl/" targetNamespace="http://mock.service.com/" name="EchoMultiOpsImplService">
  <types>
    <xs:schema xmlns:tns="http://mock.service.com/" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0" targetNamespace="http://mock.service.com/">
      <xs:element name="echoAny" type="tns:echoAnyType"/>
      <xs:element name="echoAnyResponse" type="tns:echoAnyType"/>
      <xs:complexType name="echoAnyType">
        <xs:sequence>
          <xs:element name="xml" type="xs:anyType"/>
        </xs:sequence>
      </xs:complexType>
    </xs:schema>
  </types>
  <message name="echoSingle">
    <part name="parameters" element="tns:echoAny"/>
  </message>
  <message name="echoSingleResponse">
    <part name="parameters" element="tns:echoAnyResponse"/>
  </message>
  <portType name="EchoAny">
    <operation name="echoAny">
        <input wsam:Action="http://mock.service.com/echoSingle" message="tns:echoSingle"/>
        <output wsam:Action="http://mock.service.com/echoSingleResponse" message="tns:echoSingleResponse"/>
    </operation>
  </portType>
</definitions>

OIC - Managing Multiple Operations in OIC SOAP Adapter

Introduction:

• OIC SOAP Adapter is an extensively used adapter to expose a soap-based external interface on cloud for many other applications to access. 
• Today, any integration within OIC uses exposes a single operation in it's interface. 
• If your implementation needs to support multiple operations, each operation requires a separate integration to be developed with an interface exposed causing the linear complexity in O(n), with n being the number of operations to be exposed.

Problem Statement:
Expose multiple operations using single soap integration. Is this possible?

Analysis and Solution:
Yes, it is certainly possible with a workaround and some limitations.

Workaround steps:

1. Create one integration per an operation that needs to be implemented (which will be called as child flows from here on).
2. Create a soap connection that uses the above exposed OIC wsdl and runtime credentials with corresponding security policy (which will be called as child connections from here on).
3. Repeat 1 & 2 for all required number of operations.
4. Create a generic wsdl interface and a soap trigger connection to use this. This wsdl interface uses anyType payload with a generic operation name.
5. Create an integration using above connection as trigger (which will be called as parent flow from here on) that will call the child integrations.
6. Once the  trigger is configured, parse the payload to identify the operation and identify an unique condition to determine which child to be invoked.
7. Use switch action with conditions to uniquely identify the child invocations.
8. Use invoke activity with child connections in each condition

Note: This is not a recommended implementation pattern and just a workaround until OOTB support is provided.

Generic interface for parent flow. The wsdl should support any type of payload:

<definitions xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:wsp="http://www.w3.org/ns/ws-policy" xmlns:wsp1_2="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wsam="http://www.w3.org/2007/05/addressing/metadata" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:tns="http://mock.service.com/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.xmlsoap.org/wsdl/" targetNamespace="http://mock.service.com/" name="EchoMultiOpsImplService">
  <types>
    <xs:schema xmlns:tns="http://mock.service.com/" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0" targetNamespace="http://mock.service.com/">
      <xs:element name="echoAny" type="tns:echoAnyType"/>
      <xs:element name="echoAnyResponse" type="tns:echoAnyType"/>
      <xs:complexType name="echoAnyType">
        <xs:sequence>
          <xs:element name="xml" type="xs:anyType"/>
        </xs:sequence>
      </xs:complexType>
    </xs:schema>
  </types>
  <message name="echoSingle">
    <part name="parameters" element="tns:echoAny"/>
  </message>
  <message name="echoSingleResponse">
    <part name="parameters" element="tns:echoAnyResponse"/>
  </message>
  <portType name="EchoAny">
    <operation name="echoAny">
        <input wsam:Action="http://mock.service.com/echoSingle" message="tns:echoSingle"/>
        <output wsam:Action="http://mock.service.com/echoSingleResponse" message="tns:echoSingleResponse"/>
    </operation>
  </portType>
</definitions>

Converting the entire child node of xml element to a string:

xmlDataAsString = ora:getContentAsString(/nsmpr8:echoAny/xml/*)

Identify the root-element from the child-element of xml tag.

xmlDataRoot = substring-before(substring-after($xmlDataAsString, '<'),'>') 

There can be case where the root element can be one of below types :

<rootElement>
<rootElement xmlns="someTargetNamespace">
<nsprefix:rootElement xmlns:nsprefix="someTargetNamespace">

The value of $xmlDataRoot, we expect here should have only 'rootElement' so we can route it appropriately. So we will add a switch action to handle this and update the variable using assign actions (pseudo-code shown below)

if $xmlDataRoot contains ' '(space) --> substring-before($xmlDataRoot,' ')

if $xmlDataRoot contains : --> substring-after($xmlDataRoot,':')

The final value of $xmlDataRoot at this point will return the value - 'rootElement'.

Now based on the rootelement we will create a switch and call each operartion.

Used xml payload for login operation:

<login>

<AccountNumber>111</AccountNumber>

<UserNane>abc</UserName>

<Password>pwd</Password>

</login>

For Base64 opaque schema, follow my blog:

https://soalicious.blogspot.com/2022/02/xsd-opaque-schema.html

Poc Screenshots:

Create a generic soap connection.

Create a parent integration and use generic wsdl connection as trigger.

Convert the xml child data into string

Fetch the xml root element(which is the opetration name).

Take a switch and add cases for each operation like here $xmlRootData ="login"

In each switch case, we need to convert the xmldata as string to xml data using write file with base64 and read file with xml payload.

Now call the child integration and map the read file content to child elements.

Convert the response to string.

 

Map the string response back to the caller.

Flow diagram with 1 operarion in switch. You can add multiple switch cases for other operations.

Reference:

https://blogs.oracle.com/integration/post/managing-multiple-operations-in-oic-soap-a