9 Replies Latest reply: Aug 6, 2014 7:13 AM by 934101 RSS

    Issue with completion of Main concurrent program

    934101

      Dear All,

       

      I am submitting two concurrent programs from a Wrapper program using below code. Programs are submitted correctly in parallel successfully.

      However main Wrapper program get complete status every time any one of the child program completed, its not waiting for both child programs submitted to complete.

       

      Kindly requesting you to suggest the way I can modify me code so that my Main program wait till both of the child programs completed successfully.

      Please note I need to submit child programs in parallel not sequentially.

       

       

      CREATE OR REPLACE PROCEDURE aexpgbt_id_mgm_cube_load_pkg_m(errbufOUT nocopy VARCHAR2,
      retcodeOUT nocopy VARCHAR2,
      p_scenario IN VARCHAR2,
      p_dummy1   IN VARCHAR2 DEFAULT NULL,
      p_period   IN VARCHAR2 DEFAULT NULL,
      p_dummy2   IN VARCHAR2 DEFAULT NULL,
      p_yearIN VARCHAR2 DEFAULT NULL)

        AUTHID CURRENT_USER AS

       

        l_scenario      VARCHAR2(150) := p_scenario;
        l_period        VARCHAR2(150) := p_period;
        l_year          VARCHAR2(150) := p_year;
        l_user_id       FND_USER.USER_ID%TYPE DEFAULT FND_GLOBAL.USER_ID;
        l_user_name     FND_USER.USER_NAME%TYPE DEFAULT FND_PROFILE.VALUE('USERNAME');
        l_login_id      number DEFAULT FND_GLOBAL.LOGIN_ID;
        l_resp_id       number DEFAULT FND_PROFILE.VALUE('resp_id');
        l_resp_appl_id  number DEFAULT FND_PROFILE.VALUE('resp_appl_id');
        l_conc_request_idnumber DEFAULT FND_GLOBAL.CONC_REQUEST_ID;
        l_request_id    number;

        l_get_request_status BOOLEAN;

        l_phase         VARCHAR2(100);
        l_status        VARCHAR2(100);
        l_dev_phase     VARCHAR2(100);
        l_dev_status    VARCHAR2(100);
        l_message       VARCHAR2(100);

      BEGIN

        BEGIN

      IF (l_user_id IS NULL OR l_resp_id IS NULL OR l_resp_appl_id IS NULL) THEN
      RAISE_APPLICATION_ERROR(-20001,
      'Apps Initialization Information NOT FOUND');
      ELSE
      FND_GLOBAL.apps_initialize(user_id => l_user_id,
      resp_id => l_resp_id,
      resp_appl_id => l_resp_appl_id);
      END IF;

       

        END;

        retcode := 0;

        FND_FILE.PUT_LINE(FND_FILE.LOG,

      'Main request id: ' || l_conc_request_id || CHR(10) ||
      'For Scenario: ' || l_scenario || CHR(10) ||
      'Parameters:' || CHR(10) || 'Scenario- ' || l_scenario ||
      CHR(10) || 'Period- ' || l_period || CHR(10) ||
      'Year- ' || l_year);

       

       

      IF (p_scenario = 'FULL') THEN

        

      l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'AEXP',
      program=> 'AEXPGBTIDMGMBALLDSRHM',
      description => 'History',
      start_time  => NULL,
      sub_request => FALSE);

        

      FND_FILE.PUT_LINE(FND_FILE.LOG,
      'Request submitted for History data with request id :' ||
      l_request_id);
      COMMIT;

        

      l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'AEXP',
      program=> 'AEXPGBTIDMGMBALLD',
      description => '',
      start_time  => NULL,
      sub_request => FALSE,
      argument1   => 'FULL');

        

      FND_FILE.PUT_LINE(FND_FILE.LOG,
      'Request submitted for GBT data with request id :' ||
      l_request_id);

        

      COMMIT;
      END IF;

       

        END IF;

        IF l_request_id = 0 THEN

      FND_FILE.PUT_LINE(FND_FILE.LOG,
      'Request id: ' || l_request_id ||
      ' is not submitted due to :' || FND_MESSAGE.GET || '"');
      retcode := 1;

        ELSE

       

      FND_FILE.PUT_LINE(FND_FILE.log,
      'Monitoring request ' || to_char(l_request_id));

       

        END IF;

       

        IF l_request_id > 0 THEN

      LOOP
      l_get_request_status := FND_CONCURRENT.WAIT_FOR_REQUEST(request_id => l_request_id,
      interval   => 5,
      max_wait   => 0,
      phase => l_phase,
      status=> l_status,
      dev_phase  => l_dev_phase,
      dev_status => l_dev_status,
      message=> l_message);

        

      EXIT WHEN UPPER(l_phase) = 'COMPLETED' OR UPPER(l_status) IN('CANCELLED',
      'ERROR',
      'TERMINATED');
      END LOOP;
      IF UPPER(l_phase) = 'COMPLETED' AND UPPER(l_status) = 'ERROR' THEN
      FND_FILE.PUT_LINE(FND_FILE.LOG,
      'Program completed in error because :' || l_message ||
      l_message);
      retcode := 2;
      ELSIF UPPER(l_phase) = 'COMPLETED' AND UPPER(l_status) = 'NORMAL' THEN
      FND_FILE.PUT_LINE(FND_FILE.LOG,
      'Request id: ' || l_request_id ||
      ' completed successfully');
      ELSE
      FND_FILE.PUT_LINE(FND_FILE.LOG,
      'Request failed check log for details' || SQLERRM);
      END IF;

        END IF;

       

      EXCEPTION

        WHEN OTHERS THEN

      retcode := 3;
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'ABORTED RUN.   Retcode = ' || retcode);

       

      END aexpgbt_id_mgm_cube_load_pkg_m;

      /

       

       

       

      Regards,

      Amit

        • 1. Re: Issue with completion of Main concurrent program
          Hoek

          Why are you doing this:

           

          EXCEPTION

            WHEN OTHERS THEN

          retcode := 3;
          FND_FILE.PUT_LINE(FND_FILE.LOG, 'ABORTED RUN.   Retcode = ' || retcode);

           

          ?

          • 2. Re: Issue with completion of Main concurrent program
            934101

            Hi Hoek,

             

            I am using this for handling any exception.

            Kindly suggest what condition I have to choose.

             

            Regards,

            Amit

            • 3. Re: Issue with completion of Main concurrent program
              Hoek

              I suggest NOT to handle ANY exception. Do you really want to handle exceptions that you do NOT expect?

              And if so, returning 'ABORTED RUN. Retcode=3' is not really meaningful or helpful regarding information what went wrong, don't you think.

              You've lost:

              - the original error code and message

              - the line number where the UNEXPECTED error occurred

              Also:

              -  you're not RE-RAISING the error.

              In short: you turned your unexpected error into NOT an error a.k.a. 'swallowing errors'.

              Only catch errors you EXPECT, like NO_DATA_FOUND, TOO_MANY_ROWS and so on.

               

              If, for whatever reason, you insist on using the dreaded WHEN OTHERS then at least use a RAISE in it and re-raise the unexpected error.

              For a more profound explanation see:

              https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1509245700346768268#tom1535781400346575552

              https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1270688200346183091

              • 4. Re: Issue with completion of Main concurrent program
                934101

                Hi Hoek,

                 

                I got your point and I'll check on this right now, however my original issue is to make Main program to wait till both submitted child program completed.

                Please suggest on that point as well.

                 

                Regards,

                Amit

                • 5. Re: Issue with completion of Main concurrent program
                  Hoek

                  If you submit 2 requests, then use 2 request variables instead of using one for both.

                  • 6. Re: Issue with completion of Main concurrent program
                    Srini Chavali-Oracle

                    Pl post EBS questions in the EBS forums. One option you have is to create a request set that consists of your two concurrent programs. The request set will complete only after the concurrent program complete - pl see EBS Docs for request set.

                    • 7. Re: Issue with completion of Main concurrent program
                      934101

                      Dear All,

                       

                      Please suggest If two variables for two child requests parallel pass status to main program or I have to use some logic here.

                      I would appreciate if any one of you please suggest me some sample for this logic.

                       

                      Regards,

                      Amit

                      • 8. Re: Issue with completion of Main concurrent program
                        934101

                        Dear All,

                         

                        Could you please suggest how I can upload my modified code in this post, so that you can advise me on my problem.

                         

                        Thanks

                        • 9. Re: Issue with completion of Main concurrent program
                          934101

                          Dear All,

                           

                          Could anyone please suggest how to wait Main Concurrent Program till its both child programs submitted parallel are completed successfully.

                           

                          I have modified my code now as below, any suggestions are appreciated.

                          Now issue is if any one child  program completed main program complete and not waiting for other child to complete.

                           

                           

                          CREATE OR REPLACE PROCEDURE aexpgbt_id_mgm_cube_load_pkg_m(errbuf OUT nocopy VARCHAR2,
                                                                                 retcodeOUT nocopy VARCHAR2,
                                                                                 p_scenario IN VARCHAR2,
                                                                                 p_dummy1   IN VARCHAR2 DEFAULT NULL,
                                                                                 p_period   IN VARCHAR2 DEFAULT NULL,
                                                                                 p_dummy2   IN VARCHAR2 DEFAULT NULL,
                                                                                 p_year IN VARCHAR2 DEFAULT NULL)

                            AUTHID CURRENT_USER AS

                           

                            l_scenario       VARCHAR2(150) := p_scenario;
                            l_period         VARCHAR2(150) := p_period;
                            l_year           VARCHAR2(150) := p_year;
                            l_user_id        FND_USER.USER_ID%TYPE DEFAULT FND_GLOBAL.USER_ID;
                            l_user_name      FND_USER.USER_NAME%TYPE DEFAULT FND_PROFILE.VALUE('USERNAME');
                            l_login_id       number DEFAULT FND_GLOBAL.LOGIN_ID;
                            l_resp_id        number DEFAULT FND_PROFILE.VALUE('resp_id');
                            l_resp_appl_id   number DEFAULT FND_PROFILE.VALUE('resp_appl_id');
                            l_conc_request_idnumber DEFAULT FND_GLOBAL.CONC_REQUEST_ID;
                            l_request_id_1   number;
                            l_request_id_2   number;

                            l_get_request_status BOOLEAN;

                            l_phase          VARCHAR2(100);
                            l_status         VARCHAR2(100);
                            l_dev_phase      VARCHAR2(100);
                            l_dev_status     VARCHAR2(100);
                            l_message        VARCHAR2(100);

                          BEGIN

                            BEGIN

                          IF (l_user_id IS NULL OR l_resp_id IS NULL OR l_resp_appl_id IS NULL) THEN
                            RAISE_APPLICATION_ERROR(-20001,
                                                    'Apps Initialization Information NOT FOUND');
                          ELSE
                            FND_GLOBAL.apps_initialize(user_id  => l_user_id,
                                                       resp_id  => l_resp_id,
                                                       resp_appl_id => l_resp_appl_id);
                          END IF;

                           

                            END;

                            retcode := 0;

                            FND_FILE.PUT_LINE(FND_FILE.LOG,

                                          'Main request id: ' || l_conc_request_id || CHR(10) ||
                                          'For Scenario: ' || l_scenario || CHR(10) ||
                                          'Parameters:' || CHR(10) || 'Scenario- ' || l_scenario ||
                                          CHR(10) || 'Period- ' || l_period || CHR(10) ||
                                          'Year- ' || l_year);

                            BEGIN

                          IF (p_scenario = 'FULL') THEN

                             

                            l_request_id_1 := FND_REQUEST.SUBMIT_REQUEST(application => 'AEXP',
                                                                         program => 'AEXPGBTIDMGMBALLDSRHM',
                                                                         description => 'History',
                                                                         start_time  => NULL,
                                                                         sub_request => FALSE);

                             

                            FND_FILE.PUT_LINE(FND_FILE.LOG,
                                              'Request submitted for History data with request id :' ||
                                              l_request_id);
                            COMMIT;

                             

                            l_request_id_2 := FND_REQUEST.SUBMIT_REQUEST(application => 'AEXP',
                                                                         program => 'AEXPGBTIDMGMBALLD',
                                                                         description => '',
                                                                         start_time  => NULL,
                                                                         sub_request => FALSE,
                                                                         argument1   => 'FULL');

                             

                            FND_FILE.PUT_LINE(FND_FILE.LOG,
                                              'Request submitted for GBT data with request id :' ||
                                              l_request_id);

                             

                            COMMIT;
                          END IF;

                            END;

                           

                            IF l_request_id_1 = 0 AND l_request_id_2 = 0

                             FND_FILE.PUT_LINE(FND_FILE.LOG,

                                             'Request id: ' || l_request_id_1 || ' AND ' ||
                                             l_request_id_2 || ' not submitted due to :' ||
                                             FND_MESSAGE.GET || '"') ; retcode := 1 ;

                           

                             ELSE

                           

                             FND_FILE.PUT_LINE(FND_FILE.log,

                                             'Monitoring request ' ||
                                             to_char(l_request_id_1 AND l_request_id_2)) ;

                           

                             END IF ;

                           

                             IF l_request_id_1 > 0 THEN

                          LOOP
                            l_get_request_status := FND_CONCURRENT.WAIT_FOR_REQUEST(request_id => l_request_id_1,
                                                                                    interval   => 5,
                                                                                    max_wait   => 0,
                                                                                    phase  => l_phase,
                                                                                    status => l_status,
                                                                                    dev_phase  => l_dev_phase,
                                                                                    dev_status => l_dev_status,
                                                                                    message=> l_message);

                             

                            EXIT WHEN UPPER(l_phase) = 'COMPLETED' OR UPPER(l_status) IN('CANCELLED',
                                                                                         'ERROR',
                                                                                         'TERMINATED');
                          END LOOP;
                          IF UPPER(l_phase) = 'COMPLETED' AND UPPER(l_status) = 'ERROR' THEN
                            FND_FILE.PUT_LINE(FND_FILE.LOG,
                                              'Program completed in error because :' || l_message ||
                                              l_message);
                            retcode := 2;
                          ELSIF UPPER(l_phase) = 'COMPLETED' AND UPPER(l_status) = 'NORMAL' THEN
                            FND_FILE.PUT_LINE(FND_FILE.LOG,
                                              'Request id: ' || l_request_id_1 ||
                                              ' completed successfully');
                          ELSE
                            FND_FILE.PUT_LINE(FND_FILE.LOG,
                                              'Request failed check log for details' || SQLERRM);
                          END IF;

                            END IF;

                           

                            IF l_request_id_2 > 0 THEN

                          LOOP
                            l_get_request_status := FND_CONCURRENT.WAIT_FOR_REQUEST(request_id => l_request_id_2,
                                                                                    interval   => 5,
                                                                                    max_wait   => 0,
                                                                                    phase  => l_phase,
                                                                                    status => l_status,
                                                                                    dev_phase  => l_dev_phase,
                                                                                    dev_status => l_dev_status,
                                                                                    message=> l_message);

                             

                            EXIT WHEN UPPER(l_phase) = 'COMPLETED' OR UPPER(l_status) IN('CANCELLED',
                                                                                         'ERROR',
                                                                                         'TERMINATED');
                          END LOOP;
                          IF UPPER(l_phase) = 'COMPLETED' AND UPPER(l_status) = 'ERROR' THEN
                            FND_FILE.PUT_LINE(FND_FILE.LOG,
                                              'Program completed in error because :' || l_message ||
                                              l_message);
                            retcode := 2;
                          ELSIF UPPER(l_phase) = 'COMPLETED' AND UPPER(l_status) = 'NORMAL' THEN
                            FND_FILE.PUT_LINE(FND_FILE.LOG,
                                              'Request id: ' || l_request_id_2 ||
                                              ' completed successfully');
                          ELSE
                            FND_FILE.PUT_LINE(FND_FILE.LOG,
                                              'Request failed check log for details' || SQLERRM);
                          END IF;

                            END IF;

                           

                           

                          END aexpgbt_id_mgm_cube_load_pkg_m;

                           

                          Regards,

                          Amit