Friday, May 28, 2021

Python - Dictionary and Sets

 Dictionary is a collection of key-value pairs.


''' a = {“key”: “value”, “marks” : “100”, “list”: [1,2,9]}

a[“key”] # Prints value

a[“list”] # Prints [1,2,9] '''

Properties of Python Dictionaries

  • It is unordered
  • It is mutable
  • It is indexed
  • Cannot contain duplicate keys

Dictionary Methods:

Consider the following dictionary,

a = {“name”: “Sri”,

“from”: “India”,

“marks”: [92,98,96]}

items() : returns a list of (key,value) tuple.

keys() : returns a list containing dictionary’s keys.

update({“friend”: “Sam”}) : updates the dictionary with supplied key-value pairs.

get(“name”) : returns the value of the specified keys (and value is returned e.g. “Sri” is returned here)

More methods are available on

Sets in Python:

Set is a collection of non-repetitive elements.

S= Set()          # No repetition allowed!



# or Set = {1,2}

Properties of Sets:

  • Sets are unordered # Elements order doesn’t matter
  • Sets are unindexed # Cannot access elements by index
  • There is no way to change items in sets
  • Sets cannot contain duplicate values

Operations on Sets:

Consider the following set:

S = {1,8,2,3}

Len(s) : Returns 4, the length of the set

remove(8) : Updates the set S and removes 8 from S

pop() : Removes an arbitrary element from the set and returns the element removed.

clear() : Empties the set S

union({8, 11}) : Returns a new set with all items from both sets. #{1,8,2,3,11}

intersection({8, 11}) : Returns a set which contains only items in both sets. #{8}


Write a program to create a dictionary of Bengali words with values as their English translation. Provide the user with an option to look it up!

print("Optoons are sundar, nongra, chini:")
userInput=input("Enter a bengali word to the dictionary:\n")
print("english word is:",myDict[userInput])

Write a program to input eight numbers from the user and display all the unique numbers (once).

Answer: Use set

Can we have a set with 18(int) and “18”(str) as a value in it?

Answer: yes, because both have different types and value.

What will be the length of the following set S:

S = Set()




What will be the length of S after the above operations?

Answer: 2

S = {}, what is the type of S?

Answer: Empty Dictionary


Can you change the values inside a list which is contained in set S

S = {8, 7, 12, “Sri”, [1, 2]}

Answer: No,

Python - Lists and Tuples

Python Lists:

Lists are containers to store a set of values of any data type.

Example, friends = [‘Apple’, ‘Sri’, ‘Sanddy’, 7, False]

The list can contain different types of elements such as int, float, string, Boolean, etc. Above list is a collection of different types of elements.

List Indexing:

A list can be indexed just like a string.

L1 = [7, 9, ‘sri’]

L1[0] – 7

L1[1] – 9

L1[70] – Error

L1[0:2] – [7,9]         (This is known as List Slicing)

List Methods:

Consider the following list:

L1 = [1, 8, 7, 2, 21, 15]

sort() – updates the list to [1,2,7,8,15,21]

reverse() – updates the list to [15,21,2,7,8,1]

append(8) – adds 8 at the end of the list

insert(3,8) – This will add 8 at 3 index

pop(2) – It will delete element at index 2 and return its value

remove(21) – It will remove 21 from the last

Tuples in Python:

A tuple is an immutable (can’t change or modified) data type in Python.

a = ()              #It is an example of empty tuple

a = (1,)           #Tuple with only one element needs a comma

a = (1, 7, 2)   #Tuple with more than one element

Once defined, tuple elements can’t be manipulated or altered.

Accessing Values in Tuples:

To access values in tuple, use the square brackets for slicing along with the index or indices to obtain value available at that index. For example −

tup1 = ('physics', 'chemistry', 1997, 2000);

tup2 = (1, 2, 3, 4, 5, 6, 7 );





[2, 3, 4, 5]

Tuple methods:

Consider the following tuple,

a = (1, 7, 2)

count(1) – It will return number of times 1 occurs in a.

index(1) – It will return the index of first occurrence of 1 in a.



Write a program to store seven fruits in a list entered by the user.

n1 = input("Enter fruit name 1:")
n2 = input("Enter fruit name 2:")
n3 = input("Enter fruit name 3:")
n4 = input("Enter fruit name 4:")
n5 = input("Enter fruit name 5:")
n6 = input("Enter fruit name 6:")
n7 = input("Enter fruit name 7:")

fruits = [n1,n2,n3,n4,n5,n6,n7]

Write a program to accept marks of 6 students and display them in a sorted manner.

m1 = input("Enter score 1:")
m2 = input("Enter score 2:")
m3 = input("Enter score 3:")
m4 = input("Enter score 4:")
m5 = input("Enter score 5:")
m6 = input("Enter score 6:")
m7 = input("Enter score 7:")

marks = [m1,m2,m3,m4,m5,m6,m7]

Check that a tuple cannot be changed in Python.

myTuple =(1,4,7)
TypeError: 'tuple' object does not support item assignment

Write a program to sum a list with 4 numbers.

num=int(input("how many numbers you want to sum:\n"))
while i <=num:
    value=int(input("Enter number:\n"))
print("your list is:",myList)
for x in myList:
print("sum of" ,num , "numbers:" , sum)

Write a program to count the number of zeros in the following tuple:

a = (7, 0, 8, 0, 0, 9)

a = (708009)

OIC - How to handle error for outbound flow respective to ERP system.

Here i will discuss how we can handle the errors for outbound flow respect to Oracle ERP system.

Use case:

For example, supplier address or cost center or COA details or any reconciliation report are sending from ERP to any other system. Generally in this case, we are crating OTBI report on the ERP db layers and then call the BI report from OIC using BI wsdl service. In this flow, if any error occurs, then we can handle it with creating logs, creating incident or email notifications.

Implementation steps: outboubd flow(ERP to any other system):

Step1: In the scope body

Add "if and otherwise" block as needed after each invoke to sub integration or endpoints or invoking APIs, soap service etc and when if condition fails, sending a throw new fault" with code. Reason and Details.(justified hardcoded details).

For example, suppose , you are calling BI report soap service  and in this case you add a if and otherwise block and logic is if the response from ERP is empty then you are throwing a new fault which will be handled in higer level.

Note: This is best practice to use scope for actions or activites and then handle the fault. You can use one scope or multiple scopes for each business invokes.

Step2: In the body deault scope:

When there is a fault in the body scope or throwing a new fault from body scope, the fault  moves to the default scope level and here we can handle error depending upon our business logic. Like send mail notification, logs the error to a db or logs to S3 or create an incident to Service now etc. So that support guys can get notified and the error can be resolved with ease.

In our project, we did the following:

  • Create and update notify parameters
  • Send all the log and incident details to another sub Integration OIC-Notification.
  • Rethorw fault to Global fault.

Step3: sub integration OIC_Notification sends email, log the details and create incident to servicr now.

For outbound flow we only perform following:

  1. Log the error details to S3.
  2. Create incident to ask now application.

For outbound flow, we have kept global fault as nothing.

Note: you can also use global fault scope and design more things as per your business requirements.

Thursday, May 27, 2021

OIC - ESS job run for delta calculation | Fetch daily Incremental data using ESS job and ess history and property tables


Here, we will discuss the steps how we can use ESS job for delta data or incremental data calculation for outbound data send respective to Oracle ERP.

High level steps:

  1. Create a BI report with bursting query.
  2. In the BI report model, use last_run_date or processstart date (using ess tables fusion.ess_request_history, fusion.ess_request_property) to fetch incremental data.
  3. Create a ESS job and invoke the BI report as Reporting id.
  4. Call that ess job from OIC.

Navigation to check/create/clone your ESS job:

Settings & Actions -- setup and maintenance -- search tasks -- search with "JOB" --select Manage enterprise scheduler job definition and job sets for financials, supplier chain, management and related application-- search with you ess name like '%FIN%'--select your ess job and edit to see or clone to create new ess job.

Following details are needed to create a dummy ess job for delta calculation:

Path: /delta/

Name: ess job name

Report id: /Custom/Integrations/Outbound/AP/FIN<report_name>_BI.xdo

application: application tool kit

Job application: FscmEss

Job type: BIP job type

Allow multiple pending submission: yes

Enable submission from scheduled process : yes


While the dummy ess job will be ran from the oic, we can see the job and submission time from erp process monitor.

This submission time will be saved in Fusion.ess_request_history table. This last runtime of ess job we use with last update date  to calculate the delta data wih comparing with the respectibe gl_je_lines or other table creation date and also based on event type like for which interface.

Query to fetch Last run date from ess history table:

Select NVL(MAX(erh.processstart), NULL) last_run_date 

FROM ess_request_history erh, ess_request_property erp1 


erh.executable_status ='SUCCEEDED' 

AND erp1.requestid = erh.requestid 

and ='submit.argument1' 

and erp1.VALUE =:p_event 

and erh.submitter ='SVC_INTEGRATION_ERP_ACT'

And (erh.definition ='JobDefinition://oracle/apps/ess/custom/delta/FIN_GL_RECON_ERP_BLK_ESS' or erh.definition' = 'JobDefinition://oracle/apps/ess/custom/delta/FIN_GL_RECON_CSV_ERP_BLK_ESS')

Select requestid,processstart,executable_status,submitter,definition from ess_request_history where definition =JobDefinition://oracle/apps/ess/custom/delta/FIN_GL_RECON_ERP_BLK_ESS' or erh.definition'

Select * from ess_request_property where value = :p_event

Main data model BI query example: 

first we populate the last_run_date, then comparing the gl_je_lins creating datw with last_run_date, we can fetch tbe delta daily imcremental data.







'1' super_group



to_char(xal.accounting_date, 'MM/DD/YYYY') as effective_date,

glcc.segment1 as segment1,

Glcc.segment2 as segment2,



Glb.posted_date creation_date


gl_code_combination glcc,

gl_je_lines glje,

gl_je_headers gljeh,

gl_je_batches glb

gl_je_categories gljec

Xla_event xe,

xla_ae_headers xah,

xla_ae_lines xal,

gl_import_references gir,


Select NVL(MAX(erh.processstart), NULL) last_run_date 

FROM ess_request_history erh, ess_request_property erp1 


erh.executable_status ='SUCCEEDED' 

AND erp1.requestid = erh.requestid 

and ='submit.argument1' 

and erp1.VALUE =:p_event 

and erh.submitter ='SVC_INTEGRATION_ERP_ACT'

And (erh.definition ='JobDefinition://oracle/apps/ess/custom/delta/FIN_GL_RECON_ERP_BLK_ESS' or erh.definition' = 'JobDefinition://oracle/apps/ess/custom/delta/FIN_GL_RECON_CSV_ERP_BLK_ESS')



glje.code_combination_id = glcc.code_combination_id

And glje.je_header_id = gljeh.je_header_id

And gljeh.je_category = gljec.je_category_name

And glje.creation_date > NVL(:p_test_date,NVL(er.last_run_date, SYSDATE - 1))

And xe.event_id = xah.event_id

And xe.entity_id = xah.entity_id

And xah.ae_header_id = xal.ae_header_id

And xah.ledger_id = xal.ledger_id




Group by 

(xal.accounting_date, glcc.segment1,glcc.segment2,xe.event_type_code,er.last_run_date,glb.posted_date))



'Default_Row' segement1,

' ' segement2,

:p_event event_type_code,

Sysdate last_run_date,

Sysdate creation_date




'1' Super_group

From dual

OIC - Fetch name and value params from a input string


I have a input string called "report path" having multiple paramters appended with semicolon(;). So we have to fetch all name value parameters from the input string and save in a csv formated file for further use.


Input: abcreport;a=b;c=d


Csv file

Name= a Value=b

Name=c Value:d

Code steps:

Step1: Assign variable:

  • loopCount= 1.0
  • maxCount=returnCountOfString($reportPath,";")
  • loopCountString= 1.0


Step2: whileCount: loopCount <= maxCount

  • parameterName= returnParamter($reportPath, ";", "=,"Name", $loopCountString)
  • parameterValue = returnParamter($reportPath, ";", "=,"Value", $loopCountString)

Note: use this oic-javascript-find-name-and-value.

Step3: take a stage and Write file:

C1: parameterName

C2: parameterValue

Apend to existing file : yes

Step4: loop increment

If loopCountString = maxCount then

loopCount = loopCount + maxCount


loopCountString = loopCountString +1


OIC - Javascript - find name and value parameters from a string

 function returnParameter(value,splitString,indexString,type,count){

var output=value.split(splitString)

var index=output[count].indexOf(indexString);


var parameter=output[count].substring(0,index);



var parameter=output[count].substring(index+1);



return result








here loopCountOfString = the count of ";" the string contains.

OIC- Javascript - return count of string using Regular expression

 Js code: returnCountOfString.js

function returnCountOfString(value,string){

var rgxp = new RegExp(string,"g")

var output = (value.match(rgxp)||[]).length;

return output



function returnCountOfString(value,string){

var rgxp = new RegExp(string,"g")

var output = value.match(rgxp).length;

return output


Monday, May 24, 2021

python - Strings


The string is a data type in Python. A string is a sequence of characters enclosed in quotes.

We can primarily, write a string in three ways:

Single quoted strings : a = ‘sri’

Double quoted strings : b = “sri”

Triple quoted strings : c = ‘’’ sri‘’’

String Slicing/Accessing Values in Strings:

To access substrings, use the square brackets for slicing along with the index or indices to obtain your substring. For example −

Index starts with 0 and goes till string (length - 1).

var1 = 'Hello World!'
var2 = "Python Programming"
print(var1[0]) # 1st character
print(var2[1:5]) # 2nd character to 4th one




Negative Indices: Negative indices can also be used .-1 corresponds to the (length-1) index, -2 to (length-2).

var1 = 'Hello World!'



o World!

Slicing with skip value:

We can provide a skip value as a part of our slice like this:

word = “amazing”

print(word[1:6:2] )         # It will return ’mzn’

Other advanced slicing techniques:

word = ‘amazing’

word[:7] or word[0:7]      #It will return ‘amazing’

word[0:] or word[0:7]      #It will return ‘amazing’

String Functions:

Some of the mostly used functions to perform operations on or manipulate strings are:

len() function : It returns the length of the string.

len(‘harry’)               #Returns 5

endswith(“rry”) : This function tells whether the variable string ends with the string “rry” or not. If string is “harry”, it returns for “rry” since harry ends with rry.

count(“c”) : It counts the total number of occurrences of any character.

capitalize() : This function capitalizes the first character of a given string.

find(word) : This function finds a word and returns the index of first occurrence of that word in the string.

replace(oldword, newword) : This function replaces the old word with the new word in the entire string.

Escape Sequence Characters:

Sequence of characters after backslash ‘\’ [Escape Sequence Characters]

Escape Sequence Characters comprises of more than one character but represents one character when used within the string.

Examples: \n (new line), \t (tab), \’ (single quote), \\ (backslash), etc.

String Special Operators:

Assume string variable a holds 'Hello' and variable b holds 'Python', then −

Operator Description Example

+ Concatenation -

 Adds values on either side of the operator a + b will give HelloPython

* Repetition - 

Creates new strings, concatenating multiple copies of the same string a*2 will give -HelloHello

[] Slice - 

Gives the character from the given index a[1] will give e

[ : ] Range Slice - 

Gives the characters from the given range a[1:4] will give ell

in Membership - 

Returns true if a character exists in the given string H in a will give 1

not in Membership - 

Returns true if a character does not exist in the given string M not in a will give 1

r/R Raw String - 

Suppresses actual meaning of Escape characters. The syntax for raw strings is exactly the same as for normal strings with the exception of the raw string operator, the letter "r," which precedes the quotation marks. The "r" can be lowercase (r) or uppercase (R) and must be placed immediately preceding the first quote mark. print r'\n' prints \n and print R'\n' prints \n

% Format - 

Performs String formatting.

String Formatting Operator:

One of Python's coolest features is the string format operator %. Following is a simple example −

print("My name is %s and my age is %d"%('srinanda',33))


My name is srinanda and my age is 33

List of formatters:

Format Symbol Conversion

%c character

%s string conversion via str() prior to formatting

%i signed decimal integer

%d signed decimal integer

%u unsigned decimal integer

%o octal integer

%x hexadecimal integer (lowercase letters)

%X hexadecimal integer (UPPERcase letters)

%e exponential notation (with lowercase 'e')

%E exponential notation (with UPPERcase 'E')

%f floating point real number

%g the shorter of %f and %e

%G the shorter of %f and %E


Write a program to fill in a letter template given below with name and date.

letter = ‘’’ Dear <|NAME|>,

                        You are selected!


from datetime import date
letter ='''Dear <|NAME|>,\n\tYoy are selected!\n<|DATE|>'''
name=input("Enter your Name:")


Enter your Name:srinanda

Dear srinanda,

        Yoy are selected!


Write a program to detect double spaces in a string.

st="My name is  srinanda das"
resultst.find("  ")
temp = False
if(result != '-1'):
    print("It contains double spaces: ",temp)
    print("It does not contain double spaces: ",temp)
if(temp == True):
    print("at position:",result).

python - Variables and Data Types


A variable is a name given to a memory location in a program. For example




Variable – Container to store a value

Keywords – Reserved words in Python

Identifiers – class/function/variable name

Data Types:

Python is a fantastic language that automatically identifies the type of data for us. Data types identifies which type of data a variable can store.

Primarily there are the following data types in Python:


Floating point numbers





a = 71                                    #Identifies a as class<int>

b = 88.44                              #Identifies b as class<float>

name = “Sri”                  #Identifies name as class<Str>

Rules for defining a variable name: (Also applicable to other identifiers)

  • A variable name can contain alphabets, digits, and underscore.
  • A variable name can only start with an alphabet and underscore.
  • A variable can’t start with a digit.
  • No white space is allowed to be used inside a variable name.
  • Variable names are case sensitive.
  • We cant use reserved words as variable name.

Examples of few valid variable names,

Harry, harry, one8, _akki, aakash, harry_bro, etc.

Operators in Python

The following are some common operators in Python:

Arithmetic Operators (+, -, *, /, etc.)

Assignment Operators (=, +=, -=, etc.)

Comparison Operators (==, >=, <=, >, <, !=, etc.)

Logical Operators (and, or, not)

type() function and Typecasting

type function is used to find the data type of a given variable in Python.

a = 31

type(a)                      #class<int>

b = “31”

type(b)                      #class<str>

A number can be converted into a string and vice versa (if possible)

There are many functions to convert one data type into another.

Str(31)           # ”31” Integer to string conversion

int(“32”)       # 32 String to int conversion

float(32)       #32.0 Integer to float conversion

… and so on

Here “31” is a string literal and 31 is a numeric literal.

input() function

This function allows the user to take input from the keyboard as a string.

a = input(“Enter name”)               #if a is “harry”, the user entered harry

Note: The output of the input function is always a string even if the number is entered by the user.

Suppose if a user enters 34 then this 34 will automatically convert to “34” string literal.

To add quick single line comment press ctrl + forward slash


Write a Python program to add two numbers.

print("the addition of a and b is:"a+b)

Write a Python program to find the remainder when a number is divided by Z(Integer).

print("the reminder is when a is divided by b :"a%b)

Check the type of the variable assigned using the input() function.

a=input("Please enter a number:")
print("the input type is:"type(a))

Please enter a number:23 the input type is: <class 'str'>

Use a comparison operator to find out whether a given variable a is greater than b or not. (Take a=34 and b=80)

print("checking if a is greater than b:"a>b)

checking if a is greater than b: False

Write a Python program to find the average of two numbers entered by the user.

a=input("Enter 1st number:")
b=input("Enter 2nd number:")
print("avearge of",a,"and ",b,"is :",avg)

Write a Python program to calculate the square of a number entered by the user.

a=int(input("Enter the number:"))
print("the square of the number",a,"is :",sqr)

Sunday, May 23, 2021

Python - Modules, Comments & Pip

 Open the vs code and first install python extension

Let’s write our very first python program.

Create a file called and paste the below code in it

print(“Hello World”)         => print is a function (more later)

Execute this file (.py file) by typing python and you will see Hello World printed on the screen.


A module is a file containing code written by somebody else (usually) which can be imported and used in our programs.


Pip is a package manager for python. You can use pip to install a module on your system.

E.g. pip install flask (It will install flask module in your system)

Types of modules

There are two types of modules in Python:

Built-in modules – Pre-installed in Python

External modules – Need to install using pip

Some examples of built-in modules are os, abc, etc.

Some examples of external modules are TensorFlow, flask, etc.

Using Python as a Calculator

We can use python as a calculator by typing “python” + TO DO on the terminal. [It opens REPL or read evaluation print loop]


Comments are used to write something which the programmer does not want to execute.

Comments can be used to mark author name, date, etc.

Types of Comments:

There are two types of comments in python,

Single line comments – Written using # (pound/hash symbol)

Multi-line comments – Written using ‘’’ Comment ‘’’ or “”” Comment “””.


1. To print multiple lines in print function, you have to keep the multiple lines within the tripe quote  ''' or """.

print("""Twinkle, twinkle, little star,
How I wonder what you are.
Up above the world so high,
Like a diamond in the sky.
Twinkle, twinkle, little star,
How I wonder what you are!""")

2. How to import one external module and run on vs code:

run pip install playsound

from playsound import playsound
playsound('C:\\Users\\Srinanda\\Desktop\\python\\1. Chapter 1\\play.mp3')

3. Always Use labels in comments

4. How to print the contents of a directory using os module:

import os



import os


python - pip install

Initially after the installation of python 3.9.5, I have checked that the pip is not installed by default in the python39\Scripts folder.


Need to do following steps to install the pip:

Step 1: Check if Pip is Already Installed

Pip is installed by default on many newer Python builds. To check and see if it is already installed on our system, open a command prompt and type the following command.

pip help

If Pip is installed, you will receive a message explaining how to use the program. If Pip is not installed, you will get an error message stating that the program is not found.

Step 2: Confirm that Python is installed.

The simplest way to test for a Python installation on your Windows server is to open a command prompt. Once a command prompt window opens, type python and press Enter. If Python is installed correctly, you should see output similar to what is shown below.

Python 3.9.5 (tags/v3.9.5:0a7dcbd, May  3 2021, 17:27:52) [MSC v.1928 64 bit (AMD64)] on win32

Type "help", "copyright", "credits" or "license" for more information.


If you receive a message like:

Python is not recognized as an internal or external command, operable program or batch file.

Python is either not installed or the system variable path has not been set. You will need to either launch Python from the folder in which it is installed or adjust your system variables to allow Python to be launched from any location.

Step 3: Installing Pip on Windows

Once you have confirmed that Python is installed correctly, we can proceed with installing Pip.

Download file and store it in the same directory as python is installed.

Change the current path of the directory in the command line to the path of the directory where the above file exists.
cd C:\Users\Srinanda\AppData\Local\Programs\Python\Python39

Run the following command:


pip files will be store here 


Step4: Add this path to system environment path if not added.

This PC - right click on it - Properties - advance system settings - Environment variables - path - edit and append  with ";C:\Users\Srinanda\AppData\Local\Programs\Python\Python39\Scripts"

Step 4: Verify Installation and Check the Pip Version

We can now verify that Pip was installed correctly by opening a command prompt and entering the following command.

pip -V

You should see output similar to the following:

C:\Users\Srinanda\AppData\Local\Programs\Python\Python39>pip -V

pip 21.1.2 from c:\users\srinanda\appdata\local\programs\python\python39\lib\site-packages\pip (python 3.9)

Reference: pip-install & Download-pip

python - Install required software and validation

Step1:  Search "python install" in Browser and download python 3.9.5(

during installation, 

  • checked the option - add python 3.9 to path
  • install now 
  • yes
  • close

Step2: Search "VS Code install" in browser. (IDE cum source code editor)

For my case, its windows 64 bit. (my pc --> right click properties-->check 32/64 bit)

During installation,

  • I accept the agreement
  • check all the 4 options in delect additional sections
  • next 
  • install
  • uncheck launch vs code
  • finish

Step3: open windows power shell/CMD and validate as following:



>python --version

python 3.9.5


windows - dll file - api-ms-win-crt-runtime-l1-1-0.dll is missing

 For my case, While I have installed the python 3.9.5 and VS Code 1.56.2 software and trying to run python from windows power shell, I got this prompt "api-ms-win-crt-runtime-l1-1-0.dll is missing" issue.

Steps to resolve this error:


  • Visit Microsoft Website’s Software Download Page.
  • Select the software language and Click on the ‘Download‘ button
  • Now, tick the system type x64 or x86 (As per your PC’s compatibility) and Click on ‘Next‘.The file should now start downloading. Once it is downloaded, double click on the .EXE file to run the program.
  • Now, follow the instructions that are being displayed on the screen. Once done, hit finish.

This method should fix api-ms-win-crt-runtime-l1-1-0.dll is missing error for sure.


Thursday, May 20, 2021

OIC - ERP - xsl template to create control file BI report

Usecase: This xsl template is used to create a text file, comma separated csv file where we can use some xslt aggregator functions.

.xsl template:

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

<xsl:stylesheet version="2.0"





<xsl:output method="text" omit-xml-declaration="yes" indent="no"/>

<xsl:template match="/">







<xsl:text>AP: BI_REPORT_NAME</xsl:text>


<xsl:value-of select="count(DATA_DS/G_1)"/>


<xsl:value-of select="sum(DATA_DS/G_1/PAID_AMOUNT)"/>



We can also use some format number functions as below:




Wednesday, May 19, 2021

SOA/OSB/OIC - Interview Questions

OIC part: Follow my below blog page:

SOA Part:

Can we poll files from multiple directories using one file/ftp adapter?


singletone property


SOA errorhandling

DB Distributed polling 

FTP sync read

logical delete in db

OSB Part:

Common Questions:

XA vs non XA datastore

Difference between XA and Non-XA Datasource

Difference between SOA DB and OIC DB

SOA - Can we use single FIle/FTP adapter to poll multiple directory

Yes, we can poll from  multiple different directories using single file/ftp adapter. 

Following steps need to do:

Step1: In the File/FTP adapter configuration, Provide the physical or logical path using comma separator as below:

physical path:

directory for incoming files.


Step2: Keep the following property in your .jca file 

<property name="DirectorySeparator" value="," />

Now the file will be picked up from the multiple locations you gave....

Note: for each directory poll, it will create a separate instance in EM console.

Monday, May 17, 2021

OSB - Cross Origin Resource Sharing (CORS) in Oracle Service Bus

  • Cross-origin resource sharing (CORS) is a mechanism that allows restricted resources to be requested from another domain outside from which the first resource was served.
  • It restricts browser applications to access resources that have the origin different from the origin of the application itself. In order for API responses to be processed successfully, the API has to respond with its consent to be called Cross-Origin by these applications.

Note: A request to the same host but on a different port is considered a cross-origin request.The "origin" in the term "cross-origin" is defined as the scheme, host, and port of a URL.

To enable CORS, we just have to send the Access-Control-Allow-Origin header in the service response. To do this simply add a 'Transport Header' Component from the component pallet in the response Pipeline branch of your routing. Click the 'Transport Header' and then in the 'Transport Header - Properties' window click the '+'  button to add a new header named 'Access-Control-Allow-Origin' and give its value as '*' as shown below :

Note : Access-Control-Allow-Origin: * means that the resource can be accessed by any domain in a cross-site manner. If you wished to restrict the access to the resource to requests only from a particular domain or domains, you should set the value as that domain(or a list of those domains). With this configured, now you will be able to access your REST service from a web page running on another domain.

OIC - Process large file (above 10MB)

let’s discuss key design options based on your file size, content format and delivery (outbound) particularly files above 10MB.

The integration cloud offers several designs and modeling options to process large files. 

  • The Download file option allows the loading of files up to 1GB in Integration Cloud local file system.The file can be received in OIC from File Transfer Protocol (sFTP) server or through REST/SOAP APIs. This file then read in segments using the stage activity.

If your File size is less than 1 MB, you can use Any Adapters for Inbound Interfaces with no limitation for Runtime Considerations.

File greater than 1MB but less than 10MB, you can use Any Adapters for Inbound Interfaces with some limitations at Runtime. File Processing Limitations: Only XML or CSV format supported, Read Entire File in one go is not supported so you need to use Read File in Segments in Stage File Action.

For Outbound, you can use any Adapters that support both BASE64 encoding and file attachment.

For Files greater than 10MB, we have to use FTP, REST and SOAP adapters, with some limitations at Runtime. File Processing Limitations: Only XML or CSV format supported, Read Entire File in one go is not supported so you need to use Read File in Segments in Stage File Action.

For Outbound, you can use only use adapter that supports file attachment like FTP, REST and SOAP.

Integration Cloud does not allow loading files larger than 10MB for performance reasons.

If you need to read larger files, there is an option to “Download file”. This option handles files up to the size of 1 GB and allows files to be directly downloaded into OIC local file system without reading into memory.

The Downloaded files are accessed with the help of Stage Activity. It provides simple access to the Integration Cloud local file system.

Stage Activity provides options to list, read, write, zip and unzip files from logical directory paths used to download the file in the underlying file system.

OIC - File adapter vs FTP adapter

 File Adapter

  • The File adapter allows integrating with remote file servers for reading and writing files. You can use the File adapter with connectivity agent in scenarios where it is not possible to open up the FTP server to the Integration cloud.
  • The remote file is read using the File adapter trigger. Writing to remote files can be configured using the file adapter as invoke.

FTP Adapter

  • The FTP Adapter enables the integration of FTP servers (FTP/sFTP protocols) to integration cloud. It does not require a connectivity agent and the remote FTP server should be made accessible from the Integration cloud.
  • FTP Adapter cannot be used as a trigger but you can use Scheduled orchestration and add FTP as invoke for modeling trigger type integrations.

Tuesday, May 11, 2021

OIC - ERP - How to get the Job parameters for bulk import job

There are two ways to see the job parameters.Once we have job package and name, the next step is to determine the list of parameters for "Import Journals" job. Please follow these instructions:

Way 1:


  • From Fusion Apps main page, Click the Navigator, and others then click Setup and Maintenance.
  • On the Setup page, select your setup offering such as Financials
  • Click on right icon as shown above to search for "Manage Enterprise Scheduler Job Definition and Job Sets for Financial, Supply Chain Management and Related Applications":
  • Select and Click "Manage Enterprise Scheduler Job Definition and Job Sets for Financial, Supply Chain Management and Related Applications
  • and search for "%Invoice%" in display name.
  • Select "Import Payables Invoices" row and click edit (please do not change anything as our goal is to get job package and name):
  • From user properties tab, we can see the number of arguments to send. And from paramters tab we can see the parameters. For example imvoice, it has 14 arguments so send 1st 14 params as mentioned in the parameters tab.
Steps with screenshots:

Way 2:


  • From Fusion Apps main page, Click the Navigator, and then click Scheduled Processes in Tools.
  • Click on "Schedule New Process" button and search (and select) for "Import Journals". It will display the list of parameters as shown below:

Steps with Screenshots:

Few Instances of content  of .properties file

Journal Import Launcher:

/oracle/apps/ess/financials/generalLedger/programs/common/,JournalImportLauncher,GL_BU_ABC,<Data Access Set>,<source>,<Ledger>,<Group ID>,<Post Account Errors to Suspense>,<Create Summary Journals>,<Import Decriptive Flexfields>

Example: /oracle/apps/ess/financials/generalLedger/programs/common/,JournalImportLauncher,GL_BU_ABC,1061,Balance Transfer,1,ALL,Y,Y,N

Import Payables Invoices:

<ATTIBUTE1><ATTRIBUTE2><ATTRIBUTE3><AccountingDate><Hold><Hold Reason><ATTRIBUTE7><Source><Import Set><Purge><SUmmarize Report><ATTIBUTE12><Invoice Group><Number of parallel processes>

Default value:


#NULL,$BusinesUnitId,N,#NULL,#NULL,#NULL,1000, Source,#NULL,N,N,LedgerId,#NULL,1

Note: Check User properties tab to know the number of parameters to be sent via bulk import. for Import Payables Invoices it is showing 14 parameters.

OIC - Create eText template based BI report

What is ETEXT template:

An eText template is an RTF-based template used in generating text output intended for electronic communication. At runtime, BI Publisher applies this template to an input XML data file to create an output text file that can be transmitted to a bank or other customer.

Types of Etext Templates:

eText templates are categorized as 
  • Electronic Funds Transfer (EFT) 
  • Electronic Data Interchange (EDI).

An EFT(Fixed Position) is an electronic transmission of financial data and payments to banks in a specific fixed-position format flat file (text).


EDI(Delimiter Based) is similar to EFT except it is not only limited to the transmission of payment information to banks. It is often used as a method of exchanging business documents, such as purchase orders and invoices, between companies. EDI data is delimiter-based, and also transmitted as a flat file (text).

Follow my below blogs for implementation steps:

OIC - How to create BI report in Oracle ERP

Following 2 steps to perform:

  • Create custom data models
  • Create the Report based on the data model.

Create custom data models:

Log in to Oracle Fusion Application.

At the very first time, creating a “Data model” and “Reports” follow the below steps.

Step1: Go to “Reports and Analysis”. -> Navigation: Navigator -> Reports and Analysis. And then Click the Browser Catalog Icon to Open BI Catalog to Get the BI Publisher.

To Create

  1. To Create a new BI Publisher Data Model, Let’s Select New from the toolbar. >> And then the Data Model. 
  2. From new Data Set dropdown List, Select SQL Query Name the New Data set as Invoice_id, and Select the Appropriate Data source. Let’s build the query based on the Subject area tables and Not on the Physical Tables. Paste the Applications SQL Query to Defines application names, each mapped to a unique auto-generated ID, for users and roles. Then Click OK After Selecting OK, Columns from the Query are Displayed.
  3. Navigate to Data Tab. Click to View, A sample set of Data is Shown. We can Able to save this dataset as sample data.
  4. We can able to Save the Data Model in a custom folder so that our custom data models and reports will be preserved during upgrades. Click to Save Icon. Saved in POC folder as "Generate_invoice_id"

Create BI Publish Reports:

Now Let’s Create the BI Publish Report, Based on the newly created Data Model. The Data Model is Already Selected. Click to Next.

  1. Choose the table layout and include all data columns for the report.
  2. Drag Fields from the Data Source to Create the Table.
  3. We can able to Customize the Report. Now Select the View Report and Click to Finish. After Finishing, Select the Path to Save the Report. After Saving, the Created Report will show.

We can also able to Export the Report to Corresponding formats.

Detailed screenshots:

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