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;

VBCS - Overview

Overview:

• Oracle Visual Builder is a cloud based software development Platform as a Service (PaaS) and a hosted environement for our development infrastructure.
• Oracle Visual is a visual and declarative cloud environment for developing and hosting web, mobile and progress web application in minutes from your browser.
• Oracle VBCS allows to access data from any REST, SOAP based API and allows to create custom reusable business objects to store and manage data.
• Without the need of any software, VBCS allows to develop and test the responsive web application and native mobile applications.
• The visual designer allows us to quickly create a web and mobile application by dragging and dropping the rich UI components. - all based on the open source Oracle Javascript Extension Toolkit(JET).
• Oracle Visual builder comes with a built in database that is used to stote data for your business objects( and your apps meta data too). That db is limited to 5GB in size and also does not enable to access the data in the objects using regular SQL. Luckily we can point VB to use another oracle cloud db including the new Oracle Autonomous Transaction Processing(ATP) database which will give you more space and direct SQL access to thr objects vb creates. With ATP, We will get 20GB of storage free.

Business Object:

• A business object is just a resource for example, en employee or address. It has fields to hold the data for your application. A business object is like database table. In fact, business objects are stored in a database.
• The web application accessess the business objects through their rest endpoints.

Data Manager:

• Data Manager allows us to manage the data stored in our application during dev, staging and live phases.
• Tool to help you migrate data between our databases and to import and export data.

Other tools:

• Publishing tools for staging and publishing the applications.
• Advanced tools for configuring role bases security and user access management.
• Collaboration tools for sharing application resources between team members.
• Robust tools gor describing requests and responses to Oracle SaaS rest services in the integrated catalog.

Visual Builder benefits:

• Zero software, browser based.
• Drag and drop Rich in components development.
• Custom coding for complex functionalities
• Fast to developement and Market
• Seamless integration with Oracle  integrarions and process cloud.
• Access anywhere.
• Development and hosting platform.
• Easy access of data

Navigations: