soalicious

Tuesday, July 2, 2024

OIC ERP - How to extract Incremental data from ERP using BIP report in Oracle integration

Method 1: Use a custom sql query in Data model to include last update date which will be passed as parameter from OIC. In OIC, we will use schedule parameter to persist the last update date or last run date to get the latest incremental data.

For example, the following custom SQL will create a custom data model for Suppliers. Add the parameter LAST_RUN_DATE when is prompted and set the Date Format String to MM-dd-yyyy HH:mm:ss.

SELECT
    Supplier.VENDOR_ID AS Supplier_Id,
    Party.PARTY_NAME AS Supplier_Name,
    Party.PARTY_NUMBER AS Supplier_Number,
    Party.DUNS_NUMBER_C AS Duns_Number,
    Supplier.VENDOR_TYPE_LOOKUP_CODE AS Supplier_Vendor_Type,
    Party.PARTY_TYPE AS Supplier_Type,
    Supplier.LAST_UPDATE_DATE AS Last_Update_Date
FROM 
    POZ_SUPPLIERS Supplier LEFT OUTER JOIN 
    HZ_PARTIES Party ON Supplier.PARTY_ID = Party.PARTY_ID
WHERE 
    Supplier.LAST_UPDATE_DATE >= NVL(:LAST_RUN_DATE, Supplier.LAST_UPDATE_DATE)

From OIC:

We can also include start and end date to pull the incremental data as below query:

SELECT
    Supplier.VENDOR_ID AS Supplier_Id,
    Party.PARTY_NAME AS Supplier_Name,
    Party.PARTY_NUMBER AS Supplier_Number,
    Party.DUNS_NUMBER_C AS Duns_Number,
    Supplier.VENDOR_TYPE_LOOKUP_CODE AS Supplier_Vendor_Type,
    Party.PARTY_TYPE AS Supplier_Type,
    Supplier.LAST_UPDATE_DATE AS Last_Update_Date
FROM 
    POZ_SUPPLIERS Supplier LEFT OUTER JOIN 
    HZ_PARTIES Party ON Supplier.PARTY_ID = Party.PARTY_ID
WHERE 
    Supplier.CREATION_DATE >= NVL(:START_DATE, Supplier.CREATION_DATE) AND Supplier.CREATION_DATE < NVL(:END_DATE, Supplier.CREATION_DATE)

Method 2 : using Modified sql query and using ESS job respective ess_request_history table "processstart" time as last run time.

Please follow my below blog.

https://soalicious.blogspot.com/2021/05/oic-ess-job-run-for-delta-calculation.html

Reference:

https://blogs.perficient.com/2017/02/28/using-custom-sql-bip-to-extract-erp-cloud-data-from-otbi/

Monday, July 1, 2024

OIC - How to embed image in email body | How to send image in email notification body in oracle integration

Usecase:

We get a requirement to embed or show an image to email body. Here, we will do the same using html code.

Solution:

We will use following html code in notification body and "img" tag to embed or show image base64 data to email body.

Html code used:

<html>

<head>

</head>

<body>

<img src="data:image/jpg;base64,{P_Image}" alt=" Cat image" width="100" height="100"/>

</p>

</body>

</html>

Implementation steps:

Create a rest trigger connection.
Take app driven orchestration and configure rest trigger to get image/jpeg file as binary
Take a assign and decode the stream ref to base64 file. ( encodeReferenceToBase64(streamReference)
Take a notification action and create a parameter P_Image to assign the $decodedStreamToBase64 data and use the html code to show image.
Activate and test

Detailed steps with screenshots:

Integration flow:

Configure rest trigger

Assign image stream ref to base64

Put the html code in notification body

Test:

Reference:

https://stackoverflow.com/questions/6706891/embedding-inage-in-html-email

Friday, June 28, 2024

OIC ERP - GL Daily rates or exchange rate FBDI import

Usecase:

Here , we will see how to import FBDI for GL daily rates and also received callback events and perform post processing actions like archive the file, logs the event refrence logs details and send an email notification.

FBDI file & Template:

FBDI template:

https://docs.oracle.com/en/cloud/saas/financials/24b/oefbf/importandcalculatedailyrates-3137.html

Download XLSM file and generate samaple file: :DailyRatesImportTemplate.xlsm

GlDailyRatesInterface.csv

Detailed screenshots:

Daily gl rates import:

Daily rates callback:

Testing:

Tuesday, June 25, 2024

OIC BI - Call Webservice from Fusion BI Report

Usecase: Here, we have a requirement to get PAAS DB data which will be using in ERP BI REPORT generation. So in simple, we will call an OIC exposed SOAP sync web service( this service will fetch DB data) from FUSION BI report.

Implementation steps:

Go to Tools >> Reports and Analytics >> browse catalog >> Administration >> Publisher >> Manage Publisher >> Create a data source for Web service connection.
Add a data source and provide following details:

Data source name
Server protocol : https or http
Server : host name
Port: 443
URL suffix: ic/ws/integration/v1/flows/soap/SD_EMPLOYEESYNCWSDL/1.0/?wsdl
Session timeout: 500
Complex type: checked
Ws-security: 2004
Authentication type: XML Soap
Username
Password
WSDL protected by HTTP basic auth : checked.
Add BI Author Role and BI Consumer Role.

Create data model based on web service option >> select created data source , method and parameter if any.
Go to data tab >> view >> save as sample data and save the data model
Create report based on data model and test.

Detailed screenshots:

Create data source for web service connection.

Create data model.

Create Report:

Test:

Notes:

1. If you use basic authentication as security while creating soap connection , then we need to select " WSDL protected by HTTP BASIC auth", else the data model will.give below error:

Parseerror

H3(This page contains the following errors)

Div(error on line 3 at column 1: Start tag expected , '<' not found)

H3 (Below is a rendering of thr page up to the first letter)

2. We can create data source for following types:

JDBC

JNDI

File

LDAP

OLAP

Web Services

HTTP

Conten Server

3. Disadvantages:

It only supports sync soap service call. It does not support one way or async call.

Tuesday, June 18, 2024

Db - insufficient privileges issue while using dbms_scheduler create job

While we are executing a package /procedure which contains a dbms_scheduler.create job. This is giving insufficient priviledge issue.

Error:

ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at line 2

Solution: Run the below command:

grant create job to user_name;