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:

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:

1. Go to Tools >> Reports and Analytics >> browse catalog >> Administration >> Publisher >> Manage Publisher >> Create a data source for Web service connection.
2. Add a data source  and provide following details:
1. Data source name
2. Server protocol : https or http
3. Server : host name
4. Port: 443
5. URL suffix: ic/ws/integration/v1/flows/soap/SD_EMPLOYEESYNCWSDL/1.0/?wsdl
6. Session timeout: 500
7. Complex type: checked
8. Ws-security: 2004
9. Authentication type: XML Soap
10. Username
11. Password
12. WSDL protected by HTTP basic auth : checked.
13. Add BI Author Role and BI Consumer Role.
3. Create data model based on web service option >> select created data source , method and parameter if any.
4. Go to data tab >> view >> save as sample data and save the data model
5. 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. 

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;

OIC - How to extract large data from Oracle Fusion using REST APIs | How to overcome REST APIs limitation which can return only 499 records in a single request

Usecase: Here, we will get Rest data in 500 records size chunks using limit and offset.

Limitation:

Oracle REST APIs can return only 499 (<500) records in a single request. But in Fusion, there may be more than 499+ records so we need to fetch all records

Solution:

We need to make use of Oracle REST APIs parameters like offset and limit and call REST API in the loop until hasMore attribute in the response is false.

Sample Request URL to test rest: 

https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/latest/invoices?fields=InvoiceNumber,InvoiceCurrency&onlyData=true&links=self&limit=1&offset=0

Operation: GET

Response

{
    "items": [
        {
            "InvoiceNumber": "test23",
            "InvoiceCurrency": "USD"
        }
    ],
    "count": 1,
    "hasMore": true,
    "limit": 1,
    "offset": 0,
    "links": [
        {
            "rel": "self",
            "href": "https://servername.fa.us2.oraclecloud.com:443/fscmRestApi/resources/11.13.18.05/invoices",
            "name": "invoices",
            "kind": "collection"
        }
    ]
}

Implementaion steps:

1. Create a new Scheduled integration and assign all the required variables offset=0, limit= 500, hasMore="true", onlyData="true", links="self", fields="InvoiceNumber,InvoiceCurrency"
2. Take a stage and write the headers
3. Take a while and loop till hasMore != "false"
4. Configure rest endpoint to get the invoice data with the qurey params
1. URI: fscmRestApi/resources/latest/invoices
2. Query params: fields,onlyData,links,limit, offset
3. Json response 
4. Map the fields
5. Take a stage and write invoices
6. Take a assign and update: 
1. hasMore= from the inboice response hasMore, 
2. offset = $offset + 500
7. Take a ftp adapter and configure to write final response.

Detailed steps with screenshots:

Integration flow:

Assign globals

Write headers

Loop 

Get rest call

Write to stage.

Update variable

Write to ftp directory

Generated file