Monday, January 8, 2024

OIC ERP - Get Source Name for invoice callback | Fixed asset callback

Usecase: Here, we will see how to get source name for which the callback event we received once the FBDI import completed to ERP. Here, we will show couple of examples like soure names for invoice or fixed asset callbacks etc.

High level steps:
  1. Create a BI report data model based on the below sql query.
  2. Create an EDI delimiter based eText BI template and create a BI Report.
  3. Create a BI report invoke soap connection in OIC
  4. In callback integration - invoke the BI report soap connection and pass the load request id parameter name value and the report path name etc.
  5. Decode the base 64 response and remove carriage return or next line using custom Javascript and store it into a variable for further use.

BI report SQL queries:

Select distinct source from ap_invoices_interface where 1 = 1 and load_request_id = :p_load_request_id
Union all
Select distinct aii1.feeder_system_name from fa_mass_additions aii1 where aii1.load_request_id = :p_load_request_id
Union all
Select distinct bank_name from ce_statement_headers_int csh where 1=1 and csh.load_request_id = :p_load_request_id
Union all
Select distinct 'ServiceNowUpdates' from fa_descriptive_details_int where 1=1 and 
load_request_id = :p_load_request_id
Union all
Select distinct 'ServiceNowTransfer' from fa_transfers_t where 1=1 and 
load_request_id = :p_load_request_id
Union all
Select distinct 'ServiceNowRetirements' from fa_retirements_t where 1=1 and 
load_request_id = :p_load_request_id

Detailed with screenshots:

Step1: Create Data model.

Home > Tools > Reports & Analytics >Browse Catalog > create > data model > click + sign > SQL Query > provide the SQL query

Checked the box to create the parameter.

Data model created.

Provide a default value and make it mandatory

See the structure.

Click on data and provide valid load request id and view

Save the sample data

Export the data to see the xml structure.


Step2: Create a BI report. Create a edi (.rtf) template and upload.


View Report.

Sample edi (comma separated template) etext template.

To know how to create eText Delimiter based rtf template , follow my blog:

Step3: Create a SOAP connection to call the BI report

Step4: Configure BI report invoke and decode base64 and store it into a variable.

Sometime while we decode the base64 BI report response, it can have a next line or carriage return with the response, in that case , we need to create a custom javascript and remove them. Follow my below blog for the js code:

No comments:

Post a Comment

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