Calling a Concurrent Program - Web ADI

Calling a concurrent program can be achieved by defining an ‘importer’ during Integrator definition (Please follow other Web ADI posts for details).

There are three options to define an importer

 

Asynchronous concurrent request:
Here Integrator submits the specified concurrent program after completing the upload and returns the request ID to the user, but does not wait for the import program to complete. This importer type reduces the wait time by deferring the import process. However, you need to verify the concurrent program by your own. it is recommended that you define pre-import rules with this type to validate the data at the time of upload. If the pre-validation fails, then integrator returns an error message.

 Synchronous concurrent request:
Integrator submits the specified concurrent program after completing the upload, waits for the program to complete, and then returns the results to the user. This importer type gives users immediate feedback about the success of the import, but can increase the wait time.

 PL/SQL AP:
It is same as the Synchronous concurrent request, only difference is that it calls an API instead on concurrent request and partly reduces the time by bypassing the concurrent processing queue.

Here, I am using an Asynchronous Concurrent Request importer type.

“Asynchronous concurrent request” importer type allows:
-          Pre-Import Rules    - Optional                          
-          Group Definitions  - Optional
-          Document Row : Interface Attribute Mapping - Optional
-          Concurrent Program Request Submission  - Mandatory
-          Success Message Definition  - Optional
-          Cleanup - Optional

Pre-Import Rules:  These can be used to run a select statement or to run some PL/SQL.  We can define a pre-import rule in order to use a value from the spreadsheet as a parameter value in the concurrent program submission.
 
Rule can be based on :
SQL Query
PL/SQL API Function
PL/SQL API Procedure
Sequence

I am using SQL Query and taking benefit_name from the staging table.

* Please note that it always fetches the first row of the result.

Test your query and ‘Apply’

Provide the reference name so that you can refer this rule.

Group Definition:  It is needed when you want to process the records in a particular group. I am skipping it here.

Document Row : Interface Attribute Mapping: This rule type is required for asynchronous concurrent request importer and optional for other importer types. This is how we identify each row that may have a unique result. I am skipping it here.

Concurrent Program Request Submission - Specifies the concurrent program to perform the import. This rule type is required only for an asynchronous or synchronous importer type.
Select the specified concurrent program name and ‘Apply’

Please provide ‘Reference Name’ and any values you want. Here I am providing default value ‘NEW’ to p_status and the importer rule value to the benefit name.

Value source can be:
Environment Variables
Import
Import Table
Upload Parameters

‘Apply’

Success Message Definition: You can provide a message to display when the import process is submitted successfully.

Program 'XXAK: Content Parameter Test ADI Program' Submitted Successfully With Request ID: $import$.requestid
Please verify the status from concurrent request window.

‘Apply’

Cleanup - Specifies cleanup processing to perform if errors occurred during any of the previous importer rules. I am skipping it here.

‘Submit’

> Now It is time to test,  run the ADI
Insert a new row and ‘upload’

Note the request id returned

Check the request

*Parameter tag and value for import is bne:import=Yes or No
Yes: Program submitted automatically after the upload



Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues