Concurrent Program Creation in Oracle Apps

      There are 2 steps need to be followed to create concurrent programs. 1)Creation of concurrent Executables 2) Creation of concurrent programs. Lets discuss the steps to create concurrent program using simple database procedure to display output in text format.

1) Create a simple procedure which displays order information.

To copy select+ctrl+C
CREATE OR REPLACE PROCEDURE XXCONCURRENT_PROGRAM_TEST_PRC(errbuff OUT VARCHAR2, retcode OUT VARCHAR2) AS CURSOR order_detail_cur IS SELECT ooha.order_number, TO_CHAR(ooha.creation_date,'dd-mon-yyyy') creation_date, oola.ordered_item, oola.line_number||'.'||oola.shipment_number line_number FROM oe_order_headers_all ooha, oe_order_lines_all oola WHERE ooha.header_id=oola.header_id AND ooha.creation_date BETWEEN '1-jan-2014' AND '20-May-2015' ORDER BY 1; BEGIN FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting Loop to read from the cursor'); FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Order Number Creation Date Ordered Item Line Number'); FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'**************************************************************************'); FOR order_detail IN order_detail_cur LOOP FND_FILE.PUT_LINE(FND_FILE.OUTPUT,order_detail.order_number||' '||order_detail.creation_date||' '||order_detail.ordered_item||' '||order_detail.line_number); END LOOP; FND_FILE.PUT_LINE(FND_FILE.LOG,'Cursor read ended'); EXCEPTION WHEN OTHERS THEN FND_FILE.PUT_LINE(FND_FILE.LOG,'Error Occured while processing the record:'||SQLERRM); retcode:=2; END XXCONCURRENT_PROGRAM_TEST_PRC;

2) Go to Application Developer/System Administrator Responsibility =>Concurrent=>Executable

Concurrent program executable window will be opened.

3) Enter the Executable Name.
4) Enter the Executable Short Name.
5) Select the Application from LOV.
6) Enter the description of the Concurrent executable.
7) Select the Execution Method. As it is a Procedure need to select PL/SQL Stored Procedure.
8) Enter the Execution File Name i.e the procedure name.
9) Click on Save.
10) Go to Concurrent=> Program

Concurrent Program window will be opened.

11) Enter the Program Name. This is the user concurrent Program name.
12) Enter the Short name. This name should be same as the Executable Short name.
13) Select the Application from LOV.
14) Enter the description of concurrent program. It is optional.
15) Select the Executable name from the LOV.
16) Select the Output format. There are many formats available as html, XML, Text. In this case it is Test.
17) Click on Save.
18) Find out the request group attached to the responsibility where you would like to attach the concurrent Program.
19) Go to System Administrator Responsibility=>Security=>Responsibility=>Request

20) Query with the Request Group Name.
21) Go to Detail Block and add the Concurrent Program name to the Request Group.

22) Go to the Responsibility where the concurrent Program is available.

23) GO to View Menu=>Request. Select Submit new Request

24) Submit Request Window will be appeared. Select the Concurrent Program from the LOV and Click on Submit.

25) Once it is Completed click on view log to view the log details

26) Click on the output button to view the output. Below screen shot is the expected output.

27) As explained above is the program without any parameter (the date is hardcoded in the procedure). You can also pass the parameter from the concurrent program.
28) Click on the parameter button on the concurrent program window.

29) Create the parameter name, Select the value set name and click on save.
30) Modify the database procedure with the parameter as from_date and to_date. Use this in the query.
31) Once you submit the concurrent Program It will ask for the Parameter to enter.

32) Use the below query to get the data about concurrent program from the backend.

1) Query to Find the concurrent execution file name
SELECT fe.executable_name ,fe.execution_file_name ,flu.meaning execution_method ,fcp.concurrent_program_name ,fcpt.user_concurrent_program_name FROM fnd_executables fe ,fnd_concurrent_programs fcp ,fnd_concurrent_programs_tl fcpt ,apps.fnd_lookup_values flu WHERE fe.executable_id = fcp.executable_id AND fcp.concurrent_program_id = fcpt.concurrent_program_id AND flu.lookup_type = 'CP_EXECUTION_METHOD_CODE' AND fe.execution_method_code = flu.lookup_code AND fcpt.LANGUAGE = 'US' AND (fcp.concurrent_program_id =:concurrent_program_id OR fcp.concurrent_program_name =:concurent_program_name OR UPPER (fcpt.user_concurrent_program_name) =UPPER(:user_concurrent_program_name));

2) Query to find concurrent request details. (Once you submit the request you can get request_id . Using that you can trace further)
SELECT DISTINCT fcp.user_concurrent_program_name "User Concurrent Program” ,fcp.concurrent_program_name "Concurrent Program Name" ,fcr.request_id "Request ID" ,fcr.request_date "Request Date" ,flv.meaning status ,fcr.status_code ,fcr.completion_text ,fcr.logfile_name ,fcr.outfile_name ,fcr.argument_text FROM apps.fnd_concurrent_programs_vl fcp ,apps.fnd_concurrent_requests fcr ,apps.fnd_lookup_values flv WHERE fcr.concurrent_program_id = fcp.concurrent_program_id AND TRUNC (fcr.last_update_date) = TRUNC (SYSDATE) AND flv.lookup_code = fcr.status_code AND flv.lookup_type = 'CP_STATUS_CODE' AND flv.LANGUAGE = 'US' AND fcr.request_id = :request_id -- AND fcr.concurrent_program_id=:Concurrent_program_id ORDER BY fcr.request_date ,fcr.request_id DESC;

Hope you like this...!!! If you have any concern/doubt please comment/contact us.