Steps for your first pl/sql Concurrent Program in Oracle Apps
| I think this topic is already covered partially in one of the previous training lesson[ for concurrent programs], but I would like to touch base on this again.|
Lets revisit some basics first
Question: What are the ingredients for a concurrent program?
Answer: A concurrent executable and a program attached to that executable.
Question: Will executable for a pl/sql concurrent program be a database stored procedure?
Answer: Yes, but in addition to above you can also register a procedure within a package as an executable. However, you can't make a Function to become the executable for a stored procedure.
Question: Does this stored procedure need to have some specific parameters, in order to become an executable of a concurrent program?
Answer: Yes, such procedure must have at least two parameters
( errbuff out VARCHAR2, retcode out NUMBER)
Question: Can we add additional parameters to such pl/sql procedures that happen to be Conc Prog Executables?
Answer: Sure you can, but those parameters must be defined after the first two parameters. Effectively I mean first two parameters must always be errbuff and retcode. The sequence of the remaining parameters must match with the sequence in which parameters are registered in define concurrent program-parameters window.
Question: Can those parameters be validated or will these parameters be free text?
Answer: These parameters can be attached to a value set, hence this will avoid users passing free text values.
Question: What are the possible things that a concurrent pl/sql program can do?
Answer: Firstly your stored procedure would have been created in apps. This concurrent program will connect to "apps schema" from where access to all the tabes across every module will be available.
You can do the following:-
1. Insert records in tables(usually interface or temp tables)
2. Update and delete records
3. Initiate workflows
4. Display messages in the output file or the log file of the concurrent program.
5. Make this concurrent program complete with status Error or Warning or Normal.
Question: Please give me an example of a pl/sql concurrent program in Oracle apps in real life?
Answer: Lets say you have an external application which is integrated with apps. Assume that it is Siebel application where the new customer records are created. Siebel is assumingly hosted on a Oracle database from where it has database links to your Oracle apps database.
In this case, siebel inserts sales order records into your custom staging tables.
You can then develop a concurrent process which will do the following:--------
Loop through the records in that staging table
Check if the customer already exists in Oracle AR TCA
If customer already exists, thencall the api to update existing customer
If this is a new customer, then update existing TCA Customer/Party record
Question: Ok, how do I do the above?
Answer: Find the steps below for doing so
Create table xx_stage_siebel_customers ( customer_Id integer, customer name varchar2(400));
Grant all on xx_stage_siebel_customers to apps ;
Create or replace synonym xx_stage_siebel_customers for xxschema.xx_stage_siebel_customers ;
Step 3 ( again in apps schema)
Create or replace procedure xx_synch_siebel_cust ( errbuff out varchar2, retcode out varchar2 ) is
n_ctr INTEGER := 0 ;
for p_rec in ( select * from xx_synch_siebel_cust ) LOOP
Select count(*) into n_ctr from hz_parties where party_number = p_rec.customer_number;
If n_ctr=0 then
Hz_party_create(pass appropriate parameters here).
Hz_party_update(pass appropriate parameters here);
END LOOP ;
delete from xx_synch_siebel_cust ;
Create concurrent program executable ( for example of screenshot, visit link )
Create concurrent program for that executable
Add this concurrent program to request group
Now your program is ready to roll....