ERP - BI report using Excel template

Here, we will create a BI report based on a Excel template.

Highlevel steps:

• Create a Data model based on SQL query.
• Export the sample xml data from the created Data Model.
• Create a .xls template and upload.
• For element, use : XDO_?elementname?
• For group, use: XDO_GROUP_?elementname?
• Run the report to generate the output.

SQL 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<=5

Steps in detail:

Step1: Create a Data model:

Select SQL Query

Provide SQL Query

From Data tab, view the data, export and save as sample.

Save the Data model.

Step2: Create a .xls file

Based on the exported xml file, choose the field names and mention them as below with the element syntax:

XDO_?ElementName?

Select one element and go to Formulas >>Name Manager

Click New

Select Scope as Sheet1 >> ok

Similary do the same steps for all the element or column name.

Now select all the element and Formulas >> Name Manager 

Click New

Add the Group name as below syntax

XDO_GROUP_?groupname? 

Select scope as Sheet 1.

Now in sheet2, rename it as XDO_METADATA and copy the following in the sheet.

Create the header section by entering the following variable names in column A, one per row, starting with row 1:

• Version

• ARU-dbdrv

• Extractor Version

• Template Code

• Template Type

• Preprocess XSLT File

• Last Modified Date

• Last Modified By

• Skip a row and enter “Data Constraints” in column A of row 10.

• In the header region, for the variable “Template Type” enter the value: TYPE_EXCEL_TEMPLATE

Hide the XDO_METADATA sheet.

Save it with .xls extension

Step3: Upload the .xls template and run the report to get the output.

Note: if you have multiple groups in the data model, like G_2 under G_1, then we have to perform the following:

For example group 1 have PO headers and group 1 also have Group 2 which contains PO lines,

• For group1  elements use: XDO_?ElementName?
• For group2 elements use: XDO_?ElementNane?
• Select all the headers and elements and create group element with XDO_GROUP_?group1Name?

Note:

We can also write the element name in the name box and enter.

OIC - Convert String XML to XML using parseXML() | Use of ParseXML() function

Usecase: we will receive string xml data from source and then convert it back to XML content using parseXML() function and Rest API.

Highlevel steps: 

• Using rest connection, configure app driven orchestration integration request and response.
• In request & response, we will use XML schema as  payload format.
• In the map, we will take parseXML() function which takes string XML as argument and map to execute response.
• In the map code, change the value-of element name to copy-of. If you use only value-of then we will get only the parsed xml values ,not the element names. TO get values with element names, we have to use copy-of element.

Request XML for Rest API:

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

<Input xmlns="http://org.poc.com/v1.0">

<XMLString>abcd</XMLString>

</Input>

Response schema for Rest API:

<?xml version="1.0" encoding="windows-1252" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.poc.org" xmlns:ns0="http://www.poc.org" targetNamespace="http://www.poc.org"

            elementFormDefault="qualified">

  <xsd:element name="Employees">   

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="Employee" type="EmployeeType" minOccurs="0" maxOccurs="unbounded"/>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

    <xsd:complexType name="EmployeeType">

      <xsd:sequence>

        <xsd:element name="EMPLOYEE_ID" type="xsd:string"/>

        <xsd:element name="FIRST_NAME" type="xsd:string"/>

<xsd:element name="LAST_NAME" type="xsd:string"/>

<xsd:element name="AGE" type="xsd:string"/>

<xsd:element name="DEPARTMENT_NAME" type="xsd:string"/>

<xsd:element name="DEPARTMENT_ID" type="xsd:string"/>

<xsd:element name="COUNTRY" type="xsd:string"/>

<xsd:element name="SALARY" type="xsd:string"/>

      </xsd:sequence>

    </xsd:complexType>

</xsd:schema>

XML data:

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

<Employees>

  <Employee>

    <EMPLOYEE_ID>str1234</EMPLOYEE_ID>

    <FIRST_NAME>str1234</FIRST_NAME>

    <LAST_NAME>str1234</LAST_NAME>

    <AGE>str1234</AGE>

    <DEPARTMENT_NAME>str1234</DEPARTMENT_NAME>

    <DEPARTMENT_ID>str1234</DEPARTMENT_ID>

    <COUNTRY>str1234</COUNTRY>

    <SALARY>str1234</SALARY>

  </Employee>

</Employees>

String XML data to be tested:

&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;

&lt;Employees&gt;

  &lt;Employee&gt;

    &lt;EMPLOYEE_ID&gt;str1234&lt;/EMPLOYEE_ID&gt;

    &lt;FIRST_NAME&gt;str1234&lt;/FIRST_NAME&gt;

    &lt;LAST_NAME&gt;str1234&lt;/LAST_NAME&gt;

    &lt;AGE&gt;str1234&lt;/AGE&gt;

    &lt;DEPARTMENT_NAME&gt;str1234&lt;/DEPARTMENT_NAME&gt;

    &lt;DEPARTMENT_ID&gt;str1234&lt;/DEPARTMENT_ID&gt;

    &lt;COUNTRY&gt;str1234&lt;/COUNTRY&gt;

    &lt;SALARY&gt;str1234&lt;/SALARY&gt;

  &lt;/Employee&gt;

&lt;/Employees&gt;

Detailed steps:

Integration flow:

Take rest trigger and configure post with request and response.

Map the requeest to response using parseXML().

In the map code, change value-of with copy-of elelment name.

Add tracking, save, activate and test.

OIC - Invoke a rest service whose request and response payloads as XML | Invoke Rest API with XML payloads

Here, we will invoke an OIC internal integration which supports Rest with XML request & Response.

Please follow my previous blog for how to create an rest expose service which support XML payloads request & response.

https://soalicious.blogspot.com/2022/04/oic-post-xml-payload-with-rest-api-rest.html

Highlevel steps:

1. Create a Invoke Rest connection with OIC instance.
2. Create an app driven integration with rest trigger.
3. Invoke the created rest adaptet to call the oic internal rest Integration which supports XML request & Response.
4. Map the Trigger request to invoke XML request.
5. Add tracking and activate and test.

Detailed steps:

Create an invoke connection with OIC Instance host and port and security as basic authentication and provide instance userid and password.

Integration Flow 

Rest trigger to accept a employee data as json.

Drag and drop created invoke rest adapter and configure as follow with the resource URI.

Map 

Test

Note: we can't call this rest with XML service using integration call in the flow, we will get the following error.