While working on interfaces we face some requirements like submitting a concurrent program sequentially one after the other. In some scenarios we need to submit 2nd concurrent program only when 1st current program is successfully completed. In such scenarios we need to wait for completion of the 1st program execution and then decide whether we need to submit the 2nd program or not. We can achieve this requirement using Oracle seeded API FND_CONCURRENT.WAIT_FOR_REQUEST.
What does API FND_CONCURRENT.WAIT_FOR_REQUEST do?
This API takes request ID as a parameter and waits for the request completion, returns phase/status and completion text to the caller. Calls SLEEP between db checks for waiting.
Below is the sample piece of code to achieve this requirement:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | DECLARE -- -- +========================================================================= -- | Purpose : How to make a Concurrent Program WAIT till completion another Concurrent Program execution? -- | Author : Shailender Thallam -- +========================================================================= -- lv_request_id NUMBER; lc_phase VARCHAR2(50); lc_status VARCHAR2(50); lc_dev_phase VARCHAR2(50); lc_dev_status VARCHAR2(50); lc_message VARCHAR2(50); l_req_return_status BOOLEAN; BEGIN -- --Setting Context -- fnd_global.apps_initialize ( user_id => fnd_profile.VALUE ('USER_ID'), resp_id => fnd_profile.VALUE ('RESP_ID'), resp_appl_id => fnd_profile.VALUE ('RESP_APPL_ID'), security_group_id => 0); -- -- Submitting XX_PROGRAM_1; -- lv_request_id := fnd_request.submit_request ( application => 'XXCUST', program => 'XX_PROGRAM_1', description => 'XX_PROGRAM_1', start_time => sysdate, sub_request => FALSE); COMMIT; IF lv_request_id = 0 THEN dbms_output.put_line( 'Request Not Submitted due to "' || fnd_message.get || '".'); ELSE dbms_output.put_line( 'The Program PROGRAM_1 submitted successfully – Request id :' || lv_request_id); END IF; IF lv_request_id > 0 THEN LOOP -- --To make process execution to wait for 1st program to complete -- l_req_return_status := fnd_concurrent.wait_for_request (request_id => lv_request_id ,INTERVAL => 5 --interval Number of seconds to wait between checks ,max_wait => 60 --Maximum number of seconds to wait for the request completion -- out arguments ,phase => lc_phase ,STATUS => lc_status ,dev_phase => lc_dev_phase ,dev_status => lc_dev_status ,message => lc_message ); EXIT WHEN UPPER (lc_phase) = 'COMPLETED' OR UPPER (lc_status) IN ('CANCELLED', 'ERROR', 'TERMINATED'); END LOOP; -- -- IF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'ERROR' THEN dbms_output.put_line('The XX_PROGRAM_1 completed in error. Oracle request id: '||lv_request_id ||' '||SQLERRM); ELSIF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'NORMAL' THEN dbms_output.put_line( 'The XX_PROGRAM_1 request successful for request id: ' || lv_request_id); -- --Submitting Second Concurrent Program XX_PROGRAM_2 -- BEGIN -- lv_request_id := fnd_request.submit_request ( application => 'XXCUST', program => 'XX_PROGRAM_2', description => 'XX_PROGRAM_2', start_time => sysdate, sub_request => FALSE); -- COMMIT; -- EXCEPTION WHEN OTHERS THEN dbms_output.put_line( 'OTHERS exception while submitting XX_PROGRAM_2: ' || SQLERRM); END; ELSE dbms_output.put_line( 'The XX_PROGRAM_1 request failed. Oracle request id: ' || lv_request_id ||' '||SQLERRM); END IF; END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line( 'OTHERS exception while submitting XX_PROGRAM_1: ' || sqlerrm); END; |