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?

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.

OIC - Post XML payload with Rest API | Rest API with XML Request Response

Here, we will expose rest Trigger API and post XML request payload to Rest api and get back a response as XML by the rest API.

Request schema used:

<?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>

Response schema used:

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

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

            elementFormDefault="qualified">

  <xsd:element name="EmployeesRes">   

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="EmployeeRes" 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="DEPARTMENT_ID" type="xsd:string"/>

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

      </xsd:sequence>

    </xsd:complexType>

</xsd:schema>

Detailed steps:

Integration view:

Take a Rest trigger and configure

Choose request payload as XML_Schema and media type of the request body as XML.

Choose response payload as XML_Schema and media type of the response body as XML.

Map the request to response

Test 

DB - Count(*) vs count(1) vs count(column_name)

In general, count function accepts only one argument and counts the number of rows.

Example table:

Office with 5 employees:

Id name

1 ram

2 sham

3 jadu

4 madhu

5 NULL

>>>>>>>>>>count(*)<<<<<<<<<<

1. Count(*): when * is used as an argument, it simply counts the total number of rows including the NULLs.

Query: Select count(*) as total from office

Output: Total: 5

>>>>>>>>>>count(1)<<<<<<<<<<

2. Count(1): it counts all the rows including NULLs. What count(1) really does is that it replaces all the records we get from the query result with the value 1 and then counts the rows.

Query: Select 1 from office

Output:

1

1

1

1

1

Query: Select count(1) as total from office

Output: total: 5

>>>>>>>>>>count(column_name)<<<<<<<<<<

3. Count(column_name): it counts all the rows but not NULLs.

Query: Select count(id) as total from office

Output: 5

Query: Select count(name) as total from office

Output: 4

ERP BI SQL query for custom import response or callback

Here, we are using a long sql query to fetch all the parameters respect to import callback which we will use to validate later.

Tables used:

1. xla_transaction_lines
2. xla_events
3. xla_accounting_errors

How the tables look like:

Xla_events:

Xla_transaction_lines:

Xla_accounting_errors:

How the import callback output looks:

BI report SQL Query:

Select 'Source: '|| LISTAGG(a.event_type_code, ':') within group ( order by a.event_type_code) as column1

From (Select distinct xe.event_type_code from xla_transaction_lines xtl, xla_events xe

Where xtl.event_id = xe.event_id

And xe.data_set_name = 'XLA_LOAD_' || :p_import_request_id

And xe.event_type_code is not null) a

//this code is used to get the event type code for the matched import request id. The event type code can have multiple values so used LISTAGG to make a row of the even types.

Union all

Select 'Total number of transactions reveived' || NVL((select received_rec.received_count

From (select count(1) as received_count from xla_transaction_lines xtl where 1=1 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) received_rec

Where received_rec.received_count <> 0),0)

As count_rcv from dual

Union all

Select 'Total number of debit transactions reveived' || NVL((select debit_rec.debit_count

From (select count(1) as debit_count from xla_transaction_lines xtl where 1=1 and xtl.number1 >=0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_rec

Where debit_rec.debit_count <> 0),0)

As debit_count_rcv from dual

Union all

Select 'Total number of credit transactions reveived' || NVL((select credit_rec.credit_count

From (select count(1) as credit_count from xla_transaction_lines xtl where 1=1 and xtl.number1 <0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_rec

Where credit_rec.credit_count <> 0),0)

As credit_count_rcv from dual

Union all

Select 'Total amount of debit transactions reveived' || NVL((select round(debit_rec.debit_rev_amount,2)

From (select NVL(sum(xtl.number1),0)as debit_rcv_amount from xla_transaction_lines xtl where 1=1 and xtl.number1 > 0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_rec

Where debit_rec.debit_rcv_amount <> 0),0)

As debit_amount_rcv from dual

Union all

Select 'Total amount of credit transactions reveived' || NVL((select round(credit_rec.credit_rev_amount,2)

From (select NVL(sum(xtl.number1),0)as credit_rcv_amount from xla_transaction_lines xtl where 1=1 and xtl.number1 < 0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_rec

Where credit_rec.credit_rcv_amount <> 0),0)

As credit_amount_rcv from dual

Union all

Select 'Total No of Transactions Processed' || 

NVL((select process_rec.process_count process_trans

From (select count(1) as process_count 

From xla_transaction_lines xtl where 1=1 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id 

And xe.event_status_code='P'

And xe.process_status_code='P'

 xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) process_rec

Where process_rec.process_count <> 0),0)

As tot_process_count from dual

Union all

Select 'Total No of Debit Transactions Processed' || 

NVL((select debit_rec.debit_count 

From (select count(1) as debit_count 

From xla_transaction_lines xtl where 1=1 and xtl.number1>=0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id 

And xe.event_status_code='P'

And xe.process_status_code='P'

 xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_rec

Where debit_rec.debit_count <> 0),0)

As tot_debit_count from dual

Union all

Select 'Total Amount of Debit Transactions Processed' || 

NVL((select round( debit_amt_rec.debit_line_amount,2) debit_line_amount

From (select NVL(SUM(xtl.number1),0) as debit_line_amount

From xla_transaction_lines xtl where 1=1 and xtl.number1>0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id 

And xe.event_status_code='P'

And xe.process_status_code='P'

 xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_amt_rec

Where debit_amt_rec.debit_line_amount<> 0),0)

As tot_debit_amount from dual

Union all

Select 'Total No of credit Transactions Processed' || 

NVL((select credit_rec.credit_count 

From (select count(1) as credit_count 

From xla_transaction_lines xtl where 1=1 and xtl.number1<0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id 

And xe.event_status_code='P'

And xe.process_status_code='P'

 xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_rec

Where credit_rec.credit_count <> 0),0)

As tot_credit_count from dual

Union all

Select 'Total Amount of Credit Transactions Processed' || 

NVL((select round( credit_amt_rec.credit_line_amount,2) credit_line_amount

From (select NVL(SUM(xtl.number1),0) as credit_line_amount

From xla_transaction_lines xtl where 1=1 and xtl.number1<0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id 

And xe.event_status_code='P'

And xe.process_status_code='P'

 xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_amt_rec

Where credit_amt_rec.credit_line_amount<> 0),0)

As tot_credit_amount from dual

Union all

Select 'Total No of Debit Transactions Not Processed' || 

NVL((select debit_rec.debit_count 

From (select count(1) as debit_count 

From xla_transaction_lines xtl where 1=1 and xtl.number1>=0 and exists (select 1 from xla_events xe 

where xtl.event_id = xe.event_id 

And xe.event_status_code<>'P'

And xe.process_status_code<>'P'

 xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_rec

Where debit_rec.debit_count <> 0),0)

As tot_debit_count from dual

Union all

Select 'Total Amount of Debit Transactions Not Processed' || 

NVL((select round( debit_amt_rec.debit_line_amount,2) debit_line_amount

From (select NVL(SUM(xtl.number1),0) as debit_line_amount

From xla_transaction_lines xtl where 1=1 and xtl.number1>0 and exists (select 1 from xla_events xe 

where xtl.event_id = xe.event_id 

And xe.event_status_code<>'P'

And xe.process_status_code<>'P'

 xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_amt_rec

Where debit_amt_rec.debit_line_amount<> 0),0)

As tot_debit_amount from dual

Union all

Select 'Total No of credit Transactions Not Processed' || 

NVL((select credit_rec.credit_count 

From (select count(1) as credit_count 

From xla_transaction_lines xtl where 1=1 and xtl.number1<0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id 

And xe.event_status_code<>'P'

And xe.process_status_code<>'P'

 xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_rec

Where credit_rec.credit_count <> 0),0)

As tot_credit_count from dual

Union all

Select 'Total Amount of Credit Transactions Not Processed' || 

NVL((select round( credit_amt_rec.credit_line_amount,2) credit_line_amount

From (select NVL(SUM(xtl.number1),0) as credit_line_amount

From xla_transaction_lines xtl where 1=1 and xtl.number1<0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id 

And xe.event_status_code<>'P'

And xe.process_status_code<>'P'

 xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_amt_rec

Where credit_amt_rec.credit_line_amount<> 0),0)

As tot_credit_amount from dual

Union all

Select 'Total number of Errored transactions:' || NVL((select error_rec.error_count

From (select count(1) as error_count from xla_transaction_lines xtl where 1=1 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id) and exists (select 1 from xla_accounting_errors xae where xae.event_id=xtl.event_id) ) error_rec

Where error_rec.error_count <> 0),0)

As error_count from dual

DB - LISTAGG() function | Aggregate fuction

Sometimes we need to aggregate data from a number of rows into a single row and associate the result row with a specific value.

For example, following query returns a comma separated list of employees for each job title.

Table:

Employees:

Employee_Id

First_name

Last_name

Email

Phone

Hire_date

Manager_id

Job_title

Query:

Select 

Job_title,

LISTAGG(

First_name, ',',) within group( order by first_name) as employees

From employees

Group by job_title

Order by

Job_title;