6 Replies Latest reply: Sep 1, 2014 3:15 AM by 2657837 RSS

    Setting Parent Request Status based on Child Request Completion Status FND_REQUEST.SUBMIT_REQUEST,sub_request TRUE

    2657837

      Hi Gurus,

       

      We have a requirement where we need to have parent request id populated to the child request and ability to set the parent request status based on child request completion status.

       

      I am using FND_REQUEST.SUBMIT_REQUEST with sub_request as TRUE for submitting a child request from parent request.

      I have followed the code in "Sample Code for a Concurrent Program that Submits a Child Request (Doc ID 1616185.1)"

       

      After using the code, child request's parent is getting populated perfectly. But am not able to set the status of the parent program based on child program's completion status.

       

      I have tried using wait_for_request, while loop and even goto for for achieving this. nothing is working and if i use these child program is going to Inactive, No Manager.

       

      Any help is greatly appreciated.

       

      Best Regards

      gt1942

        • 1. Re: Setting Parent Request Status based on Child Request Completion Status FND_REQUEST.SUBMIT_REQUEST,sub_request TRUE
          Hussein Sawwan-Oracle

          Not sure how do you use FND_CONCURRENT.WAIT_FOR_REQUEST as you haven't shared the code with us but you may take a alook at the following docs.

           

          Submitting Concurrent Request via FND_CONCURRENT.WAIT_FOR_REQUEST (Doc ID 1021956.6)

          How to Launch Planning Data Pull MSCPDP using FND_REQUEST.SUBMIT_REQUEST (Doc ID 235359.1)

           

          Thanks,

          Hussein

          • 2. Re: Setting Parent Request Status based on Child Request Completion Status FND_REQUEST.SUBMIT_REQUEST,sub_request TRUE
            2657837

            Hi Hussein,

             

            Thanks for the response, i have tried using FND_CONCURRENT.WAIT_FOR_REQUEST. If i use it when i pass sub_request TRUE for FND_REQUEST.SUBMIT_REQUEST, the child program is going into inactive, no manager.

             

            Following is the code used by me. I have added proper comments to the approaches used by me.

             

            *****

            *****Please provide information on, when we use Sub_Request TRUE, how can we capture the Completion Status of Sub Request (Child Concurrent Program) and accordingly set Parent Program Completion Status

            *****

             

            CREATE OR REPLACE PACKAGE BODY APPS.TEST_GT_PACK2

            AS

            G_REQ_DATA              VARCHAR2(240) := NULL;

            PROCEDURE TEST_GT_PROC2(p_out_errbuf      OUT VARCHAR2,

                                    p_out_retcode     OUT NUMBER)

            AS

            ----

            l_request_id       NUMBER;

            l_phase           VARCHAR2(100);

            l_status       VARCHAR2(100);

            l_dev_phase       VARCHAR2(100);

            l_dev_status       VARCHAR2(100);

            l_message       VARCHAR2(1000);

            l_phase_code       VARCHAR2(100);

            l_success        BOOLEAN;

            i number;

            req_data varchar2(10);

            l_req_phase_code    VARCHAR2(100);

            l_req_status_code    VARCHAR2(100);

            ----

            BEGIN

            ----

            req_data := fnd_conc_global.request_data;

            ----

            if (req_data is not null) then

                i := to_number(req_data);

                i := i + 1;

                if (i < 11  ) then

                  p_out_errbuf := 'Done!';

                  p_out_retcode := 0 ;

                  return;

                end if;

              else

                i := 1;

              end if;

              ----

            l_request_id := NULL;

            l_request_id := FND_REQUEST.SUBMIT_REQUEST('AR',

                                                       'TEST_GT_PROG1',

                                                       NULL,

                                                       NULL,

                                                       TRUE);

            COMMIT;

            IF l_request_id = 0 THEN

                FND_FILE.PUT_LINE (FND_FILE.log, 'Failed to Submit TEST_GT_PROG1');

            ELSE

                fnd_conc_global.set_req_globals(conc_status => 'PAUSED',

                                           request_data => to_char(i));

                FND_FILE.PUT_LINE (FND_FILE.log, 'Submitted TEST_GT_PROG1 ' || TO_CHAR(l_request_id));

                FND_FILE.PUT_LINE (FND_FILE.log, 'i Value ' || TO_CHAR(l_request_id));

            END IF;

            --DBMS_LOCK.SLEEP (20);--Not Working,sending Child program into Inactive, No Manager

            l_phase      := NULL;

            l_phase_code := NULL;

              SELECT lookup_code

                     INTO

                 l_phase_code

                     FROM

                 fnd_lookup_values_vl

                     WHERE 1=1

                 AND lookup_type='CP_PHASE_CODE'

                     AND lookup_code = 'C'

                     AND view_application_id = 0;

             

            SELECT PHASE_CODE,STATUS_CODE INTO l_req_phase_code,l_req_status_code

            FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID=l_request_id; 

            --Not working,sending Child program into Inactive, No Manager      

            /*

            <<loopstart>>

            SELECT PHASE_CODE,STATUS_CODE INTO l_req_phase_code,l_req_status_code

            FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID=l_request_id; 

             

            IF l_req_phase_code!='C' THEN

                GOTO loopstart;

            END IF; 

            */

            --While loop is sending Child program into Inactive, No Manager

            /*

            WHILE l_req_phase_code!='C' LOOP

            SELECT PHASE_CODE,STATUS_CODE INTO l_req_phase_code,l_req_status_code

            FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID=l_request_id;

            END LOOP;

            */

             

            FND_FILE.PUT_LINE (FND_FILE.log, 'l_req_phase_code ' || l_req_phase_code);

             

            --This is not working as l_req_phase_code is always returning Pending 'P'

            IF     l_req_phase_code=l_phase_code THEN

                IF l_req_status_code IN ('G','D') THEN--Warning

                    p_out_retcode:=1;

                ELSIF l_req_status_code='E' THEN--Error

                    p_out_retcode:=2;

                END IF;

            END IF;

             

             

            END test_gt_proc2;

            END TEST_GT_PACK2;

             

            Best Regards

            gt1942

            • 3. Re: Setting Parent Request Status based on Child Request Completion Status FND_REQUEST.SUBMIT_REQUEST,sub_request TRUE
              2657837

              hello Gurus...

               

              could you please have a look at this and let me know if this is possible or not...

               

              Best Regards

              gt1942

              • 4. Re: Setting Parent Request Status based on Child Request Completion Status FND_REQUEST.SUBMIT_REQUEST,sub_request TRUE
                2657837

                It is sad that i could not even get reply from community..the api fnd_request.submit_request is very commonly used one..i have already posted the code that i have tried...

                there is no documentation on the issue that have raised...that is the reason for coming to forums...

                 

                hoping that someone will have a look and help me on this..

                 

                Best Regards

                gt1942

                • 6. Re: Setting Parent Request Status based on Child Request Completion Status FND_REQUEST.SUBMIT_REQUEST,sub_request TRUE
                  2657837

                  Hi All,

                   

                  I have raised a Bug with development team and they confirmed that we can not get the child request completion status when using fnd_request.submit_request with sub_request as TRUE.

                   

                  So, it is not possible to set the completion status of parent request based on child request completion status.

                   

                  Hope this helps others..

                   

                  Best Regards

                  gt1942