soalicious

- Oracle integrations presently does not support modelling an asynchronous request response service. However, all scheduled orchestration patterns implement an asynchronous request response internally, As a result, asyn integration through scheduled orchestration is an anti pattern

16. Why we should use OIC instead SOACS?

https://soalicious.blogspot.com/2024/05/oic-why-should-we-choose-oic-over-soacs.html

17. Version controlling in OIC?

18. How to do monitoring in OIC?

19. How will you implement multiple operation based calculator service in OIC? How will you implement calculator service in OIC?

For Soap:

https://soalicious.blogspot.com/2022/03/oic-managing-multiple-operations-in-oic.html

For rest with multiple verbs:

https://soalicious.blogspot.com/2022/11/oic-use-rest-adapter-with-multiple.html

20. Have you used enrichment service in OIC?

21. What are the OIC integrations you have implemented in your project?

22. How to embed barcode or image in import to erp? How to embed image or barcode in email?

23. We have a Project requirement that we have to schedule a service to load data and also need to do a real time data feed as needed. How you will implement this?

Answer: first create an app driven integration and expose as rest / soap so that client can send data in real time. Second, create a schedule integration to call that app driven integration to run in a scheduled time manner.

24. Rest api vs Erp cloud adapter in terms of FBDI import.

25. Which OIC adaptets you have worked on?

Answer: Ftp, file, DB, ATP, Rest, SOAP, ERP, HCM

26. BIP vs OTBI report

https://soalicious.blogspot.com/2024/05/erp-hcm-bip-vs-otbi.html

27. FAH vs GL when to use for import?

https://soalicious.blogspot.com/2023/12/oic-when-to-use-fah-vs-gl-import.html

28. Can we read excel file in OIC? If no, how we can achieve it?

Answer: OIC does not support excel read. If we really need to read it then follow the below steps:

Create a serverless OCI function with python/Java logic to convert excel to csv >> create a bucket in oci object storage >> create a rest connection to put excel file from oic to Object storage >> do another call to function to convert and save to OS >> do another OS call to get the converted CSV file and use.

29. How to change the email notification from part?

https://soalicious.blogspot.com/2023/06/oic-how-to-change-from-address-for.html

30. What are the diffrent stage action functions?

Answers: we can do the following:

Read entire file
Read file in segments
write file
zip files
Unzip file
Encrypt file
decrypt file
List files

31. BIP report huge size, How to handle this situation?

32. Suppose you are invoking a BIP report and received null or empty file how to handle this so that it should not flow to Downstream applications?

33. There are large number of schedule orchestrated integrations run at the same time. What to do to avoid backlogging??

https://soalicious.blogspot.com/2024/06/oic-there-are-huge-number-of-scheduled.html

34. Have you used PLSQL in xslt or in complex logic processing?

35. Can we call BIP report from database?

36. What is your current role in the project?

37. Suppose we are getting data from ERP and then storing them in a database table. How to avoid the duplicate data to be stored in the database?

38. Apex reports, Can we use plsql in apex?

39. We have a requitement to digitally sign the email while sending the email notification from OIC. Is there any way we can embed an image in the email notification in OIC?

40. How to get the 2nd or 3rd max salary from an employee table using sql query?

Answer:

Using limit and offset

2nd Maximum Salary

SELECT salary

FROM employee

ORDER BY salary DESC

LIMIT 1 OFFSET 1;

3rd Maximum Salary

SELECT salary

FROM employee

ORDER BY salary DESC

LIMIT 1 OFFSET 2;

Method 1: Using Subqueries

2nd Maximum Salary

SELECT MAX(salary) AS SecondHighestSalary

FROM employee

WHERE salary < (SELECT MAX(salary) FROM employee);

3rd Maximum Salary

SELECT MAX(salary) AS ThirdHighestSalary

FROM employee

WHERE salary < (SELECT MAX(salary)

FROM employee

WHERE salary < (SELECT MAX(salary) FROM employee));

41. Can we convert App driven orchestration to Scheduled orchestration integration?

Answer: directly there is no provision to convert from App driven to Scheduled integrations. But we can convert from scheduled to app driven integrations.

As a workaround , we can create a separate scheduled orchestration integration and can call then the app driven the integration from the scheduled one.

42. How to fetch incremental or delta or daily data using BI report?

43. We need to execute a schedule job in oic that will run only on weekdays excluding public holidays?

Answer

https://soalicious.blogspot.com/2024/09/oic-we-need-to-execute-schedule-job-in.html

44. how you will write BIP extension report?

45. Max number of scopes can be created in an integration?

Answer: Unlimited.

46. Max number of instances of an integration can be run in parallel?

47. Can we use data from one scope to another scope ? If yes, how?

Answer: yes, using global variable and data stitch action.

48. While we call HCM extract flow, whih fields we are passing ?

Answer:

Extract name or flow name
Integration name
Parameters like effective date
Legislative data group

49. What are the different xslt functions you have worked on OIC?

50. How to generate Sequence number and insert to a database table without using Db sequnce?

51. What are the different fault handling actions avaiable in OIC? And their functions?

52. Suppose you are calling an API for invoice bulk import, how you will track any failure for each invoices?

53. Suppose you have a requirement to approve or disapprove some budget from a VBCS page how you will achieve this?

54. What are different OCI storage types available and their fucntions?

55. Suppose we have 2 duplicate in web portal, from OIC, you need to call the service and check duplicacy. How do you do it?

56. How you will connect hybrid model from OIC?

57. What is max size notification action - attachment support?

Answer: 2 MB

58. How to recover a failed intances in oic?

59. How you handle complex mapping in oic?

OIC Tracing | How to enable and use OIC Tracing | Oracle Integration Cloud

Why we need tracing:

When Tracing is enabled, OIC integration prints detailed information before and after each action / activity that is executed. It also prints the message / payload of needed.
We should make sure that it is enabled only for debugging purposes and turned off before going into production.
When tracing is enabled, integration activity can be viewed in Activity Stream.

Types of Tracing:

Integration Level : If your requirement is to enable the tracing for one or more integrations and disable tracing for the rest of the integrations, you can ise Integration level tracing.
Global Tracing On : It will turn tracing on for all activated flows and we can't enbale/disable it at individual level on the integration page.
Global Tracing Off : It will turn tracing off for all activated flows and we can't further enbale/disable it at individual level on the integration page.

How to change the level of Tracing:

Oracle home >> Settings >> Integrations >> Tracing

If we choose the Integration level tracing, we can enbale or disable the tracing at individual integration level following below steps:

Go To Integrations Page >> select actions >> Tracing >> Enable Tracing & Include Payload >> Save

Tuesday, August 16, 2022

OIC to ERP : Move custom report | download custom report based on ess job id and then move to desired folder

Usecase: Here, we will create a sub integration which will take a request id and check if ESS job status, if its succceeded, it will download the report and move the file to desired mail dl or sftp directory.

HighLevel steps:

Main flow (where import and child process runs) call to callback integration
Archive the success file
Execute the execution accounting report based on child request id for support help.
Call ESS job which internally calls the BI report to execute the SQL query to get the callback report
Call a Sub Integration like MoveCTRLReport and feed the ESS job process id

Here, check the ESS job status using getESSJobStatus(Interesting fact is that we are looping max 55 times to check if the ESS process completes )
If ESS process gets completed,

Download the control report using downloadESSJobExecutionDetails operation
Write the control report in stage
Unzip the report
Read the text report
Move the report to FTP location

Request payload:

{

"RequestId":"",

"directoryAdapter":"",

"sourceFileName":"",

"targetDirectory":""

}

Implementation logic steps:

Configure rest request with post verb. Request json payload spacified above.
Assign looping variables like

LoopCounter = -45.0
ESS_Status = ""
MaxLoopCount = 9

Take a while loop with condition $Loopcounter < $MaxLoopCount and $ESS_Status != "SUCCEEDED"

Call Erp cloud adapter - operation: getESSJobStatus and service : erpIntegrationService and map request Id.
Wait 59 sec
Assign status like

LoopCounter = Loopcounter +1
ESS_Status = result of the getESSJobStatus

Take a switch and check if ESS_Status = SUCCEEDED

If yes,

Call erp cloud adapter and download control report using service: erpIntegrationService and operation: downloadESSJobExecutionDetails. And map request id
Write control reports to stage location using sourceFileName, temp stage location and using opaque schema and map the content to opaque element.
Unzip the zipped contents using stage
Read text control report using stage and xsd opaque file. Specifying file name as unzipcontrolreport/properties[filetype='text']/filename
Call another sub integration like OIC_File_Handler to move the control report to desired sftp or s3 folder. Or send a mail attaching the report.

If no, throw new fault with code, reason, details.

Sceenshots:

TBD

OIC To ERP - Design steps for Import and Custom callback | Oracle Integration Cloud

Usecase: Here we will see a design how we can import to Cloud ERP and get a Custom callback.

Implementation Steps:

Follow the below design steps:

First source file is there in a SFTP location. So we will list files of the location using SFTP connection
We can have multiple files, so we will iterate over the file references and take a body scope

Download each file to stage location
Write header field names to a header file.
Write LineHeader names to a Line file
Read file reference from the download response using stage File action and CSV file sample.
For each file record,

Put a switch activity and check of 1st column of the field(Transaction_Identifier) = Header ,

Then appends the header details to the header file

If Transaction_Identifier = Line,

You can map the line records to the Line file
Take a debit and credit line counts, total debit and total credit line counts etc

If your file has Trailer part , check if Transaction_Identifier = Trailer,

Take the all the trailer record counts like total debit line count, total credit line counts, total debit amount, total credit amount , total lines etc

We can also map the line records outside of the for each record loop using a Stage Read in Segments so that the performance can improve, In the XSLT , we have to check if the record identifer =Line, then go for the line record mapping to line file.
Write Metadata file like Metadata version number and Application short name etc if needed
Zip all these 3 files - header, Line and Metadata using stage file action
We can keep a back up of the zip file by calling a sub Integration (File_Handler) to archive the file to a FTP location
Now we can validate the Trailer information with the record level all the counts like total debit line , total credit line, total debit amt, total credit amt , total lines etc. Once this switch checks success

We can create OIC level control file
Now we call call sub integration (Encrypt_Import) to encrypt and import the zip file to Cloud ERP. We can do following 3 way of import.

Old method - Multi step : https://soalicious.blogspot.com/2021/05/oic-erp-import-technique-1.html
ImportBulkData operation of ERPIntegrationService: https://soalicious.blogspot.com/2021/05/oic-import-to-erp-technique-2-bulk-data.html
New 1 step method - import Bulk Data : https://soalicious.blogspot.com/2022/02/oic-erp-supplier-bulk-import-and.html

Check import process status using getESSJobStatus

Follow: https://soalicious.blogspot.com/2021/07/oic-get-ess-job-status.html

Capture Child Process id of the import process if any

Follow: https://soalicious.blogspot.com/2022/01/oic-erp-how-to-get-child-process-id.html

Check the child process status again using getESSJobStatus

Follow: https://soalicious.blogspot.com/2022/08/oic-get-ess-job-status-of-child-process.html

Call CallBack sub Integration to get the custom call back

Friday, August 12, 2022

SQL query WITH clause | Database

The SQL WITH clause is used to provide a sub-query block which can be referenced in several places within the main SQL query.
The usage of WITH clause is very similar to creating tables. When you create a table, you give it a name. Similarly, when you use the WITH caluse, you also give it a name and this name essentially acts like a table name in the main SQL statement.
WITH clasue dose not create a table or view, the object associated with the WITH statement disappears after the main SQL statement is executed, and there is nothing to clean up.

Syntax for single sub-query alias:

WITH <alias_name> AS (sql_sub-query_statement)

Sysntax for multiple sub query :

WITH <alias_name_1> AS (sql_sub-query_statement_1), <alias_name_2> AS (sql_sub-query_statement_2),.....

Example:

Suppose we want to list all the stores that have more sales than the average.

With t1 as

(Select avg(sales) avg_sales from store_sales)

Select a1.* from store_sales a1, t1

Where a1.sales > t1.avg_sales;

Input:

Store name sales

A 15000

B 2500

C 3000

D 7000

Output:

Store_Name Sales

A 15000

D 7000

Thursday, August 11, 2022

OIC - get ESS job status of the child process of the import job

Here, I will show you how to get child process id of the Import process and then to get the the ESS job status of the child process once the import job completed and you have the ess import request id.

Logic:

we will create a loop and check the ess job status using the import request id whether the ess import job is successful or not(at each loop it will wait 59 sec).

Follow the below steps:

Step1: Get the Child Process id calling BI report.

https://soalicious.blogspot.com/2022/01/oic-erp-how-to-get-child-process-id.html

Step2: Take an assign with the following variables:

I_inProgress : "true"

I_childRequestStatus : ""

I_childRequestID: decodebase64(reportBytes) << from BI report response

Step3: take a while activity and add the following condition.

$I_inProgress = "true"

Step4: Take a wait activity and assign some time to complete the child process lika value 59sec.

Step5: Drag and drop Erp cloud adapter and configure following:

Provide a name: essJobStatus

Select Query, Create, Update or delete information.

Select service: ErpIntegrationService

Operation: getESSJobStatus

Step6: Map the child request id to request id of the getEssJobstatus.

Step7: Take a assign named like updateProgress and do the following:

I_inProgress : returnStatus(result of getESSJobStatusResponse)

I_childRequestStatus: result of getESSJobStatusResponse.

Click here how to convert the status to boolean value:

oic-js-return-boolean-status

Tuesday, August 9, 2022

OIC - Create a header file based on sequential unique lines Transaction number | Oracle Integration cloud

Usecase: Here, the requirement is, we will have a source file which contains sequential duplicate transaction based lines or records. Based on sequential unique transaction numbers we have to fetch unique lines and write header file.

For instance,

Source file:

Transaction_Identifier,Transaction_number,Event_Type_Code,Ledger_Name,Date

Line,10,Payment,XX,08/09/2022

Line,11,Reverse,XX,08/09/2022

Line,12,Payment,XX,08/09/2022

Line,13,Payment,XX,08/09/2022

Line,14,Payment,XX,08/09/2022

Expected output header file:

Transaction_number,Event_Type_Code,Ledger_Name,Date

10,Payment,XX,08/09/2022

11,Reverse,XX,08/09/2022

12,Reverse,XX,08/09/2022

13,Payment,XX,08/09/2022

14,Payment,XX,08/09/2022

Logic steps:

Using FTP adapter connection, download the file to stage location and then read the file reference using sample csv filw format.
Assign preTransactionNumber as empty string("").
Write the header field names
For each ledger line or record,