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:

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

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

Output report:

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

ERP BI sql query to fetch output based on ESS REQUEST HISTORY Table last run date | Fetch Processstart from ESS_Request_History table

Usecase: Here using ess job tables - ESS_request_history and ESS_request_property, we will fetch the latest succeeded processstart data as last_run_date and then compare this last_run_date with the last_update_date from the xla_trsansaction_headers.

Same BI sql query to fetch the last run date latest data from ERP db tables:

Example1:

WITH ess_requests

AS (select NVL(MAX(erh.processstart), NULL) last_run_date FROM fusion.ess_request_history erh, fusion.ess_request_property erp1

Where erh.executable_status = 'SUCCEEDED'

AND erp1.requestid = erh.requestid

AND erp1.name ='reportID'

AND erp1.VALUE = '/Custom/Integrations/Poc/SampleREP.xdo'

AND erh.submitter = 'SVC_OIC_ERP')

Select '1' super_group

,xth.transaction_number

,xal.sr8 policy number

,gcc.segement1

,gcc.segment2

...........

From xla_ae_lines xal

,xla_ae_headers xah

,xla_events xe

,xla_transaction_entities xte

,xla_transaction_headers xth

 ,gl_code_combinations gcc

,xla_subledgers

,xla_event_types_b xet

,ess_requests er

Where 1=1

And xte.entity_id =xe.entity_id

And xte.application_id = xe_application_id

And xe.event_id =xah.event_id

And xah.ledger_id = xal.ledger_id

...........

And xth.last_update_date > NVL(er.last_run_date, SYSDATE - 2)

Example2:

With jobrun as 

(Select (

Select nvl(MAX(erh.processstart), sysdate-1)

From fusion.ess_request_history erh

Where 1=1

And upper(erh.definition) like '%ESS_Report_Name%'

And erh.executable_status = 'SUCCEEDED'

AND erh.submitter = 'SVC_OIC_ERP') last_run

From dual)

Select distinct cc.segment1

,cc.segment2

,jeh.description

,jeh.status

........

From gl_period_statuses ps

,gl_je_headers jeh

,gl_je_lines jel

,gl_je_sources src

,gl_code_combinations cc

,gl_je_batches jeb

,xla_ae_lines xal

,xcla_ae_headers xah

,jobrun

........

Where 1=1

And ps.application_id =101

,ps.ledger_id = jeh.ledger_id

And jeh_period_name =ps.period_name

.....

And ((jeh.last_update_date) >=jobrun.last_run

Or (jel.last_update_date) >=jobrun.last_run

Or (jeb.last_update_date) >=jobrun.last_run

Or (src.last_update_date) >=jobrun.last_run

Or (xah.last_update_date) >=jobrun.last_run

Or (xal.last_update_date) >=jobrun.last_run)

Oracle Fusion ess job definition tables | ERP ESS job Schedule processes tables

Following are the oracle fusion job schedule processes tables which helps to extract the submitted ESS job requests:

• ESS_Request_History
• ESS_Request_Property
• Request_History
• Request_History_view

Here below is the Detail SQL query for the ESS job Schedule processes:

Select r.requestid, 

r.name "ESS Job Name",

r.EXECUTABLE_STATUS "JOB  STATUS",

R.USERNAME,

R.APPLICATION,

R.PRODUCT,

R.REQUESTEDSTART,

R.COMPLETEDTIME 

from fusion.ess_request_history r,fusion.ess_request_property v

where 1=1  and r.requestid = v.requestid
order by r.requestid, v.name

BI SQL Query sample to read data based on the last run date from the ess request history table:

https://soalicious.blogspot.com/2022/03/erp-bi-sql-query-to-fetch-output-based.html

OIC ERP BI Bursting using FTP delivery channel

UseCase : We will create a Bursting BI report and create a ESS job over the bursting BI and call the ESS job from OIC and save the report in the FTP delivery channel. HERE, we will create one document/single file.

Please follow my previous blog for BI Bursting about and details:

https://soalicious.blogspot.com/2022/03/oic-erp-bi-bursting.html

Bursting Query:

SELECT 'XX' AS "KEY",

'XXSupplierRecord' AS "TEMPLATE",

'en-US' AS "LOCALE",

'TEXT' AS "OUTPUT_FORMAT",

'FTP' AS "DEL_CHANNEL",

'AmazonS3' AS "PARAMETER1",

:p_destination AS "PARAMETER4",

'XXSUPPLIERREPORT_' || TO_CHAR(SYSDATE, 'YYYY-MM-DD_HHMISS') || '.csv'

AS "PARAMETER5",

'true' AS "PARAMETER6"

FROM DUAL

BI report Query:

Select 'XX' AS "KEY",

hp.party_name supplier_name,

poz.segment1 supplier_name,

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;

Create BI Bursting Report:

Navigator >> Tools >> Reports and Analytics >> Browse catalog >> traverse Shared Folders -- Custom -- Integrarions -- Poc >> 

Create ESS job:

Navigator >> My Enterprise >> Setup and Maintenance >> search with Manage Enterprise in search tasks

Provided following details:

Name: XXSupplierREP_ESS

Path: /delta/

Application: Application Toolkit

Description: POC bursting report

Job Application name: FscmEss

JOb type: BIPJobType and bursting report

Report id: /Custom/Integratikns/Poc/XXSpplierREP1.xdo

Allow multiple pending submissions: True

Create a Paramter: p_destination.

Call From OIC:

JobPackageName: oracle/apps/ess/custom/delta

JobDefinitionName: XXSupplierREP_ESS

Parameter: Sftp path to save the file.

ERP delivery configuration:

Navigation # Tools >> Reports and Analytics >> Browse Catalog >> Administration 

Bursting for multiple files:

Sample bursting query for sending email:

Select a.Vendor_id KEY,

'Test_supp' TEMPLATE,

'en-US' LOCALE,

'PDF' OUTPUT_FORMAT,

'Supplier Details' OUTPUT_NAME,

'EMAIL' DEL_CHANNEL,

'test@gmail.com' PARAMETER1,

'test@gmail.com' PARAMETER2,

'bipublisher-report@oracle.com' PARAMETER3,

'Supplier Details' PARAMETER4,

'Please find the attached file' PARAMETER5,

'true' PARAMETER6,

'donotreply@oracle.com' PARAMETER7,

From poz_suppliers a

For more details follow oracle docs:

https://docs.oracle.com/cd/E21764_01/bi.1111/e18862/T527073T555155.htm#:~:text=About%20Bursting,-Bursting%20is%20a&text=For%20each%20block%20of%20the,element%20in%20the%20data%20model.