OIC - Use REST adapter with multiple resources or Multiple Verbs in Oracle Integration Cloud

Here, we will see how to use REST adapter with multiple resources and verbs support in Oracle Integration Cloud(OIC).

Usecase: We will create a calculator service with 2 resource operations addition and subtraction with rest adapter multi resources or verbs support. In this case, we have used same verb as post with 2 different resource operations.

Detailed steps:

Choose App driven Orchestration integration style.

Provide integration name, package >> Create.

We see empty canvas, Drop a rest adapter.

Provide endpoint name and select to configure multiple resources or verbs.(max11).

1st creating for addition operation.

Provide operation name, resouce uri: /addition, verb : post, and request and response payloads.

Provide json sample as request payload.

{

"Number1":"",

"Number2":""

}

Next

Provide json sample as response payload.

{

"Result":""

}

Next

Thus, we added 1st addition operation.

Select Add another operation box and go next

Add next operation : subtract.

Provide json sample for request payload.

Provide json sample for response payload.

Next

Thus we added 2 operations, we can add more (max 11) operations as required.

Next.

Done

We can see the operation flows added.

Do the Mappings for the addtion and subtraction operations .

Add the tracking from trigger >> more 

Save >> Activate >> Test

Thus, we can achieve the multiple resources or verbs on the rest adapters.

OIC - Subscribe to business events raised within the ERP Cloud

Here, we will show the subscription of the ERP Integration Inbound event which will be raised within ERP Cloud.

Detailed Steps:

OIC to ERP Cloud : what happens in the background ERP after import from OIC

Suppose we import AP invoice to ERP cloud from OIC using FBDI bulk import and ErpIntegrationService, then what happens do we really think?

If you dont know, just see the below processes which are running in the Scheduled process section in ERP:

Load Interface File for Import:

Interface loader will start and run InterfaceLoaderAsyncJob.

** Reading properties from scheduler environment Properties file.

/u01/APPLTOP/instance/ess/config/environmen.properties.

Transfer File: 

This above async job will submit loader job: InterfaceLoaderSqlldrImport to transfer the imported Header and Line files from UCM to respective Interface tables.

Load File to interface:

1st load header file : ApInvoicesInterface.csv to AP_INVOICES_INTERFACE table)

Load File to interface:

2nd load Line file : ApInvoiceLinesInterface.csv to AP_INVOICES_LINES_INTERFACE table.

Import Payables Invoices:

This will import the invoice data from inteface table to main import tables.

Import Payable Invoices Report:

This will generate report how the import goes, Is there any failure or what are the invoices processed or success details etc.

Product interface and error table data will be automatically be purged by the purge interfavw table scheduled procrss( default setting is within 30 days of the initial load). Prior to purge execution, the interface and error data will first be extracted and uploaded to the particular UCM server account for the individual product import process that is used. 

If you need to reference the purged interface and error data, then you will need to access the UCM file with the following naming convention:.

ImportBulkData_APXIIMPT_<LoadRequestId>.zip

ODI12C - Why the Set variable increment parameter is disabled in package?

This is a common issue we observed in ODI. To increment the value of a variable in package, variable's data type must be numeric otherwise the increment option will be disabled.

ODI 12C - Read multiple files and store them in database table

Usecase: Here, we have multiple csv files from Source team. This usecase is to process multiple files from file location and store into database table.

Lets see how we can achieve this:

Highlevel steps

1. First write all file names from the source file location to database table using Jython script procedure. Follow my below blog. https://soalicious.blogspot.com/2022/10/odi-12c-write-file-names-from-location.html
2. Import flat file to odi studio model using a variable for file name. Follow my below blog: https://soalicious.blogspot.com/2022/10/odi-12c-importing-flat-file-to-odi.html
3. Creat a package to read the file contents one by one and write to dadabase table. Which has been described in the details steps below.

Detailed steps:

Package flow: File Load to Emp Db Table

Step1: first create a parameter table to fetch all the parametes needed to poll the files like file location, mail from, mail to , archive location etc.

Create table poc_parameter_tbl(

Par_Name varchar2(200),

Par_val varchar2(500),

Insert_dt timestamp(6),

update_dt timestamp(6)

)

Step2: Create a table to store all the file contents:

Create table poc_emp(

EmpId number,

Name varchar2(200),

Dept varchar2(200),

Salary Number

)

Step3: create model

Step4: create refersh variables

Step5: create procedures

Raise error

Truncate table

Update processed flag

Step6: create mapping

Step6: create package with following sequence to process multiple files one by one:

• Drag and drop LV_EPM_OBJ_STORAGE_SRC_FOL_PATH
• Drag and drop LV_FILE_COUNT
• Drag and Drop LV_FILE_ITERATOR as set variable and assign to zero
• Drag and Drop LV_FILE_COUNT as evaluate variable and  >= 0
• If True, 
• Drag and drop PROC_Truncate_TargetTable
• Drag and drop LV_FILE_ITERATOR as set variable and increment with 1
• Drag and drop LV_FILE_ITERATOR as  evaluate variable and <= #POC.LV_FILE_COUNT
• If True,
• Drag and drop POC_FILE_NAME
• Take mapping FileToDBLoad
• Proc_Update_Processed_Flag
• Loop to LV_FILE_ITERATOR where it is crementaing with 1.
• If False,
• Take LV_ERROR_MSG as set variable and assign with "No File Found"
• Call Proc_Raise_Error

ODI 12C - Importing flat file to ODI studio

Usecase: Here, we will see how to import a flat .csv file in ODI studio.

High level steps:

• Under Topology, choose physical architecture >> File Technology and create new data server and new physical schema with the file location.
• Under Topology, choose logical architecture >> File Technology and create new logical schema.
• Under Design >> model >> create new model or use existing model >>select Technology and logical schema 
• Under model, create new datastore >> provide resource file name >> files format 
• Over data store >> view data

If you want to have variable file name, then create a variable in a project and use it as refresh variable and use it in the model data store resource name as #procjectcode.variablename

Detailed Steps with screenshots: