4 Replies Latest reply on May 17, 2016 3:10 PM by John_K

    Wait for child conc program to finish

    2832249

      Hi,

       

      I am submitting a conc program from another conc program using fnd_request.submit_request.

      But the parent program completes once it triggers the child program.

      Is there any way to put the parent program on hold until the child program completes?

       

      I tried using fnd_concurrent.wait_for_request and it didn't help.

       

      Here goes the below code snippet.

       

      PROCEDURE P_MAIN_LOAD_PROCEDURE (x_ERRBUF            OUT     VARCHAR2,

                                       X_RETCODE           OUT     NUMBER,

                                       P_RESP_APPL_ID      IN      NUMBER,

                                       RESP_ID             IN      NUMBER,

                                       P_ORG_ID            IN      NUMBER) is

       

       

      LN_REQUEST_ID     FND_CONCURRENT_REQUESTS.REQUEST_ID%TYPE;

      LV_USER_NAME      VARCHAR2(100);

      LB_MESSAGE        BOOLEAN;

      LV_DEV_PHASE    VARCHAR2(1000);

      LV_DEV_STATUS   VARCHAR2(1000);

      LB_RETURN_OP    BOOLEAN;

      LN_PHASE_CODE FND_CONCURRENT_REQUESTS.PHASE_CODE%TYPE;

      LN_STATUS_CODE FND_CONCURRENT_REQUESTS.STATUS_CODE%TYPE;

      --P_RESP_APPL_ID    NUMBER;

      --P_RESP_ID         NUMBER;

      --P_ORG_ID          NUMBER := fnd_profile.VALUE('ORG_ID');

                                     

      begin

       

       

      if P_ORG_ID = 1234 then

       

       

      --P_LOAD_BAAC_DATA (P_FILE_PATH => P_FILE_PATH,

      --                            P_FILE_NAME => P_FILE_NAME,

      --                            p_bank_name => P_BANK_NAME);

       

       

       

       

          begin

            ln_request_id := fnd_request.submit_request('XXXX',

                                                        'XXXX_ORG_1',

                                                        NULL,

                                                        TO_CHAR(SYSDATE,

                                                                'YYYY/MM/DD HH24:MI:SS'),

                                                        FALSE,

                                                        P_RESP_APPL_ID,

                                                        RESP_ID,

                                                        P_ORG_ID);

        

       

       

        

          exception

            when others then

              LN_REQUEST_ID := '';

              FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unable to submit request  :  ' || SQLERRM);

            

            

         end;

       

       

       

       

      if P_ORG_ID = 12345 then

       

       

          begin

            ln_request_id := fnd_request.submit_request('XXXX',

                                                        'XXXX_ORG_2',

                                                        NULL,

                                                        TO_CHAR(SYSDATE,

                                                                'YYYY/MM/DD HH24:MI:SS'),

                                                        FALSE,

                                                        P_RESP_APPL_ID,

                                                        RESP_ID,

                                                        P_ORG_ID);

        

       

       

        

          exception

            when others then

              LN_REQUEST_ID := '';

              FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unable to submit request  :  ' || SQLERRM);

            

            

         end;

       

       

      END IF;

      END IF;

       

       

       

       

       

       

       

       

      --   lb_return_op := fn_wait_for_req(p_req_id     => ln_request_id,

      --                                    X_DEV_PHASE  => LV_DEV_PHASE,

      --                                    x_dev_status => lv_dev_status);

      --

      -- IF LV_DEV_PHASE = 'COMPLETE' THEN

      -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'The program completed successfully');

      -- end if;

       

       

      EXCEPTION

      WHEN OTHERS THEN

      X_RETCODE := 1;

      X_ERRBUF := SQLERRM;

      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in calling the program :  and the error is : ' || X_ERRBUF);

      end;

       

      Regards,

      Bharat.

        • 1. Re: Wait for child conc program to finish
          Bashar.

          Hi,

           

          It seems that you did not use a loop when waiting for the child request to finish.

          Have a look at the example in the following link:

           

          How to make a Concurrent Program WAIT till completion another Concurrent Program execution? | OracleAppsDNA

           

          Regards,

          Bashar

          • 2. Re: Wait for child conc program to finish
            2832249

            Hi Bashar,

             

            I am using a conc program to submit the conc program using above mentioned procedure which internally calls the other 2 programs. I did modify the programs and it didn't help either.

             

            below is the modified code

             

             

            PROCEDURE P_MAIN_LOAD_PROCEDURE (x_ERRBUF            OUT     VARCHAR2,

                                             X_RETCODE           OUT     NUMBER,

                                             P_RESP_APPL_ID      IN      NUMBER,

                                             RESP_ID             IN      NUMBER,

                                             P_ORG_ID            IN      NUMBER) is

             

             

            LN_REQUEST_ID     FND_CONCURRENT_REQUESTS.REQUEST_ID%TYPE;

            LV_USER_NAME      VARCHAR2(100);

            LB_MESSAGE        BOOLEAN;

            LV_DEV_PHASE    VARCHAR2(1000);

            LV_DEV_STATUS   VARCHAR2(1000);

            LB_RETURN_OP    BOOLEAN;

            LN_PHASE_CODE FND_CONCURRENT_REQUESTS.PHASE_CODE%TYPE;

            LN_STATUS_CODE FND_CONCURRENT_REQUESTS.STATUS_CODE%TYPE;

            --P_RESP_APPL_ID    NUMBER;

            --P_RESP_ID         NUMBER;

            --P_ORG_ID          NUMBER := fnd_profile.VALUE('ORG_ID');

             

             

              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

             

             

            if P_ORG_ID = 1234 then

             

             

            --P_LOAD_BAAC_DATA (P_FILE_PATH => P_FILE_PATH,

            --                            P_FILE_NAME => P_FILE_NAME,

            --                            p_bank_name => P_BANK_NAME);

             

             

             

             

                begin

                  ln_request_id := fnd_request.submit_request('XXXX',

                                                              'XXXX_ORG_1',

                                                              NULL,

                                                              TO_CHAR(SYSDATE,

                                                                      'YYYY/MM/DD HH24:MI:SS'),

                                                              FALSE,

                                                              P_RESP_APPL_ID,

                                                              RESP_ID,

                                                              P_ORG_ID);

             

             

             

             

                exception

                  when others then

                    LN_REQUEST_ID := '';

                    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unable to submit request  :  ' || SQLERRM);

                 

                 

               end;

             

             

             

             

            if P_ORG_ID = 12345 then

             

             

                begin

                  ln_request_id := fnd_request.submit_request('XXXX',

                                                              'XXXX_ORG_2',

                                                              NULL,

                                                              TO_CHAR(SYSDATE,

                                                                      'YYYY/MM/DD HH24:MI:SS'),

                                                              FALSE,

                                                              P_RESP_APPL_ID,

                                                              RESP_ID,

                                                              P_ORG_ID);

             

             

             

             

                exception

                  when others then

                    LN_REQUEST_ID := '';

                    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unable to submit request  :  ' || SQLERRM);

                 

                 

               end;

             

             

            END IF;

            END IF;

             

             

             

            IF ln_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      => ln_request_id

                                                        ,INTERVAL        => 2

                                                        ,max_wait        => 60

                                                         -- 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

                  fnd_file.put_line(fnd_file.log,'The XX_PROGRAM_1 completed in error. Oracle request id: '||ln_request_id ||' '||SQLERRM);

                ELSIF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'NORMAL' THEN

                  fnd_file.put_line(fnd_file.log, 'The XX_PROGRAM_1 request successful for request id: ' || ln_request_id);

                

                ELSE

                  fnd_file.put_line(fnd_file.log, 'The XX_PROGRAM_1 request failed. Oracle request id: ' || ln_request_id ||' '||SQLERRM);

                END IF;

              END IF;

             

             

             

             

            --   lb_return_op := fn_wait_for_req(p_req_id     => ln_request_id,

            --                                    X_DEV_PHASE  => LV_DEV_PHASE,

            --                                    x_dev_status => lv_dev_status);

            --

            -- IF LV_DEV_PHASE = 'COMPLETE' THEN

            -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'The program completed successfully');

            -- end if;

             

             

            EXCEPTION

            WHEN OTHERS THEN

            X_RETCODE := 1;

            X_ERRBUF := SQLERRM;

            FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in calling the program :  and the error is : ' || X_ERRBUF);

            end;

             

            Regards,

            Bharat.

            • 3. Re: Wait for child conc program to finish
              2832249

              The parent program (conc program calls the procedure mentioned above) didn't trigger the child program as it was running for atleast 10 mins and i had to cancel it.

               

              Regards,

              Bharat.

              • 4. Re: Wait for child conc program to finish
                John_K

                Remember  - you have to commit your request submission prior to waiting on it. If it's not committed then the concurrent managers obviously will be unable to see it to pick it up in the first place.