OIC - Update OIC lookups based on BI reports invoke and config file

Usecase: Here, We will see the logic steps and implementation how we can call BI reports based on lookups config file stored in S3 bucket and update the OIC lookups like supplier sites,Source Legal entites, GL Source, BU Ledgers etc.

Config file is a csv file where each line contains BI report paths to fetch the look up values.

Logic steps:

From Main Integration , named : OIC_Lookups_Update_D

1. Take a body scope and assign globals
2. Call child integration to get the config file from AWS S3 bucket. (https://soalicious.blogspot.com/2023/11/oic-get-files-from-aws-s3-rest-service.html)
3. Loop over the config files
1. Decode base64 to file reference
2. Read file reference
3. Loop over each lookup config record
1. Call BI report using SOAP adapter and pass the absolute report path name.(https://soalicious.blogspot.com/2021/05/oic-invoke-erp-bi-report.html)
2. Call child integration to store the BI lookup response to S3 bucket (https://soalicious.blogspot.com/2023/12/oic-create-resuable-integration-to.html)
4. Call child integration and get the files (https://soalicious.blogspot.com/2023/11/oic-get-files-from-aws-s3-rest-service.html)
5. Loop over the files
1. For each file, call child integration to update the OIC lookup. (https://soalicious.blogspot.com/2023/11/oic-import-replace-lookup-take-base64.html)

ERP - Create EText EFT template based BI report | Create etext fixed position based BI Report

Usecase: We will create EFT template based or fixed position based BI report:

EFT template or layout:

https://docs.google.com/document/d/1HzqeSCsFBc8BO9m3bmGsaBXJqPul-a9n/edit?usp=drivesdk&ouid=105651791254983245041&rtpof=true&sd=true

Implementation Steps:

Step1: Create a Data model based on a Sql query:

Navigation Menu >> Tools >> Reports and Analytics >> Browse Catalog >> Create >> Data Model >> Click New Data Set >> SQL Query >> provide the SQl query and Dataset name >> ok >> go to data tab and view the data >> save as sample data >> export the xml data to see the structure and content. >> save the data model.

Sql query used: 

Select INVOICE_ID, INVOICE_NUM,INVOICE_AMOUNT,TO_CHAR(Invoice_date,'DD-MON-RRRR') Invoice_date

FROM AP_INVOICES_ALL

WHERE rownum <101

Step2: Based on the Exported xml data structure, create EFT delimiter based rtf doc and upload it to create a report >> Save >> view report

Note: Use the attached EFT template google drive link and copy and modifiy as per your need. SAVE it as .rtf file

ERP - Create EDI template based BI report | Create Delimiter based report

Usecase: Here, we will create a BI report based on eText delimiter based rtf template or EDI template .

Delimiter template used:

https://docs.google.com/document/d/1Hvur9U0kmy9hCGw59OR_O8jePHNu2mkx/edit?usp=drivesdk&ouid=105651791254983245041&rtpof=true&sd=true

Implementation Steps:

Step1: Create a Data model based on a Sql query:

Navigation Menu >> Tools >> Reports and Analytics >> Browse Catalog >> Create >> Data Model >> Click New Data Set >> SQL Query >> provide the SQl query and Dataset name >> ok >> go to data tab and view the data >> save as sample data >> export the xml data to see the structure and content. >> save the data model.

Sql query used: 

Select INVOICE_ID, INVOICE_NUM,INVOICE_AMOUNT,TO_CHAR(Invoice_date,'DD-MON-RRRR') Invoice_date

FROM AP_INVOICES_ALL

WHERE rownum <101

Step2: Based on the Exported xml data structure, create EDI delimiter based rtf doc and upload it to create a report >> Save >> view report

Note: Use the attached EDI template google drive link and copy and modifiy as per your need. Save it as .rtf file.

Another example of Delimiter Based eText Report:

Step1: First create a Data model based on a SQL Query for instance, "Generate_invoice_id".

For steps follow - oic-how-to-create-bi-report-in-oracle

SQL Query used: select AP_INVOICES_S.nextval invoice_id from dual

type of SQL: standard

data source: ApplicationDB_FSCM

Name: apt name.

Step2: Create a RTF template file. To begin with, Copy trailing content and save it as Delimetr_eText.rtf.

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

Step3: Create a BIP report with the above Data model (ref point 1) , upload Delimetr_eText.rtf  file with template type as eText.

Execute the Report. You might expect outcome as below:

invoice_id

12345

OIC - Import (Replace) a Lookup | Update an existing OIC Lookup based on base64 encoded CSV file as rest feed

Usecase: We have a requiremwnt that we will create a reusable integration which will take input as lookup name and base64 encoded csv content and update or replace the OIC lookup based on that CSV file.

Logic steps:

1. Create 2 connections
1. Rest trigger connection
2. OIC Rest invoke connection for oic lookup update rest api call
2. Create an app driven orchestration style
3. Configure rest trigger with the following:
1.  Request: 
1. Base64 encoded csv content
2. Lookup name
2. Response:
1. jobStatus
2. Exception code, reasona and details
4. Assign globals 
1. Content: decodeBase64ToReference(base64StreamRef)
2. Exception code, reason, details and result variables
5. Take a scope and drag and drop created oic rest invoke connection and configure to update the lookup
1. Api: /ic/api/integration/v1/lookups/archive
2. Verb: Put
3. Request: Multipart/form-data
4. Response: binary , other media type: application/json; charset=utf-8
6. Map the following:
1. AttatchmentReference : content
2. contentType: applocation/octet-stream
3. partName: lookupname + ".csv"
4. fileInputHtmlFieldName : file
7. Go to default fault handler and assign the scope fault details and result as "UPDATE_ERROR"
8. Assign the final response.

Rest trigger Request json:

{

"base64StreamRef":"ref",

"lookupName":""

}

Rest trigger Response Json:

{

"jobStatus":"",

"jobMessage":"",

"exceptionCode":"",

"exceptionReason":"",

"exceptionDetails":""

}

Detailed steps with screenshots:

Create OIC Rest connection:

Create a rest trigger connection:

Create an app driven integration and configure the Rest trigger

Take a scope and configure OIC rest invoke adapter to update lookup

Assign global 

Content as decodebase64ToReference

Different exception details variables

Map the required details to update the lookup

Map the exception details in default handler

Do the final response mapping.

Add tracking and activate.

Create a lookup

Export the lookup file

Update the lookup file.

Test the file

We can see that the lookup has been updated.

Test with wrong file details and see the exception details in formatted manner.

Note: this is to update or replace existing lookup. To import new lookup file, use rest verb as post instead of put.