Wednesday, April 6, 2022

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 



Tuesday, April 5, 2022

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


Monday, April 4, 2022

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;






Tuesday, March 29, 2022

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:










ERP BI - Case Study 2 - Create BI Report using ETEXT template

Here, we will create a ETEXT template using globally provided format and create a BI report.

Highlevel steps:

  • Create a Data model
  • Create a ETEXT template
  • Create a BI report

BI report Query:

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;

Global Template for ETEXT(modify as per requirement): save the format in ms word with .rtf extension.

Delimiter Format Setup:

Format Setup:

Hint: Define formatting options...

<TEMPLATE TYPE>

DELIMITER_BASED

<OUTPUT CHARACTER SET>

iso-8859-1

<CASE CONVERSION>

UPPER

<NEW RECORD CHARACTER>

Carriage Return


Hint: Format Data Records Table for DELIMITER_BASED


<LEVEL>

DATA_DS

<MAXIMUMLENGTH>

<FORMAT>

<DATA>

<COMMENTS>

<NEW RECORD>

InvoiceHeader

250

Alpha

'Invoice_id'

 




<LEVEL>

G_1

<MAXIMUMLENGTH>

<FORMAT>

<DATA>

<COMMENTS>

<NEW RECORD>

CLRDAta

250

Alpha

INVOICE_ID

 


<END LEVEL>

G_1


<END LEVEL>

DATA_DS



Steps in details(Screenshots):





















Monday, March 28, 2022

ERP - BI publisher desktop tool installation and Enable add Ins the word | RTF Template

Here we will download BI publisher desktop tool, install and add Ins to MS word.

Steps to follow:

Step1: Go to the following Oracle link and download 32bit or 64 bit:

https://www.oracle.com/middleware/technologies/analytics-publisher/downloads.html



Step2: click on .exe and install it.
Once installation done, ms word bi publisher will open automatically and Bi publisher tab will show.


You can also go to ERP BI home and go to download desktop tool >> Template Builder For word >> downloads >> download BI Publisher desktop



Step3: sometimes BI publisher tab is not showing in MS word, then we have to add it from add Ins.
Navigation path: File >> Options >> Add-ins >> Manage:(COM Add_ins) Go >> BI Publisher Template Builder for word.




Sometime after creating the rtf part while we click pdf to preview it will throw below error:

javaw -Xmx256M -jar "C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib\RTF2PDFv2.jar" "C:\Users\HP\AppData\Local\Oracle\BIPublisher\TemplateBuilderforWord\tmp\tb_param.xml"

Solution: Java jre is not installed in the system.

Go to java path and download and install.


after installation, configure the java path as below in the ms word >> bi publisher tab >> options >> options >> preview >> set java home :




Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...