Query to get request set parameter in oracle apps

Hi Friends, we are going to discuss about the query to get request set parameter in oracle apps. We will share the detail sql query which will help to extract the complete request set details in oracle apps. In this request set query, we can able to extract the request set parameters and the parameters values submitted in the request set. This query will help to extract the complete request set details including parameter in oracle apps. This is one of the useful sql query related to concurrent program and the request set in oracle apps. Using this query, we can develop the custom report which will help to extract the request set details including request set parameter details in oracle apps. We will also share the important table too which will help to store the request set details and the request set parameter in oracle apps. Please find below the complete detail about the query to get request set parameter in oracle apps.

Query to get request set parameter in oracle apps

7 Important Tables used in the query to get request set parameter in oracle apps

1.fnd_request_sets_vl

2.fnd_req_set_stages_form_v

3.fnd_request_set_programs

4.fnd_concurrent_programs_vl

 5.fnd_descr_flex_col_usage_vl

 6.fnd_concurrent_requests

 7.fnd_concurrent_programs

 

Complete query to get request set parameter in oracle apps

 Here below is the detail Query to get request set parameter in oracle apps

SELECT "Request Set Name"

     , Request_id " Concurrent Request ID"

     , "Set Stage Seq"

     , "Set Program Seq"

     , "Concurrent Program Name"

     , "Request Set Parameter Name"

     , DECODE(num, 1, (SELECT argument1  FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                   2, (SELECT argument2  FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                   3, (SELECT argument3  FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                   4, (SELECT argument4  FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                   5, (SELECT argument5  FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                   6, (SELECT argument6  FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                   7, (SELECT argument7  FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                   8, (SELECT argument8  FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                   9, (SELECT argument9  FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  10, (SELECT argument10 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  11, (SELECT argument12 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  12, (SELECT argument12 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  13, (SELECT argument13 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  14, (SELECT argument14 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  15, (SELECT argument15 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  16, (SELECT argument16 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  17, (SELECT argument17 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  18, (SELECT argument18 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  19, (SELECT argument19 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  20, (SELECT argument20 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                                                  21, (SELECT argument21 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                                          22, (SELECT argument22 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                  23, (SELECT argument23 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                                                  24, (SELECT argument24 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                                                  25, (SELECT argument25 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id),

                 'Add Argument for further parameter value'

             ) " Request Set Parameter Value"

  FROM (SELECT rs.user_request_set_name        "Request Set Name"

             , req.request_id                   Request_ID

             , rss.display_sequence            "Stage Seq"           

             , rsp.sequence                    "Program Seq"

             , cp.user_concurrent_program_name "Concurrent Program Name"           

             , cp_param.column_seq_num         "Param Seq"       

             , form_left_prompt                "Parameter Name"

             , rs.REQUEST_SET_ID

             , cp.concurrent_program_id

             , row_number () over (partition by req.request_id, CP.CONCURRENT_PROGRAM_ID order by cp_param.column_seq_num )num

          FROM apps.fnd_request_sets_vl           rs

             , apps.fnd_req_set_stages_form_v     rss

             , applsys.fnd_request_set_programs   rsp

             , apps.fnd_concurrent_programs_vl    cp

             , apps.fnd_descr_flex_col_usage_vl   cp_param

             , fnd_concurrent_requests            req

             , fnd_concurrent_programs            rset_prog

         WHERE rs.user_request_set_name  =:p_request_set_name

           AND rs.application_id                       = rss.set_application_id

           AND rs.request_set_id                       = rss.request_set_id

           AND rss.set_application_id                  = rsp.set_application_id

           AND rss.request_set_id                      = rsp.request_set_id

           AND rss.request_set_stage_id                = rsp.request_set_stage_id

           AND rsp.program_application_id              = cp.application_id

           AND rsp.concurrent_program_id               = cp.concurrent_program_id

           AND rs.end_date_active                     IS NULL

           AND cp_param.descriptive_flexfield_name (+) = '$SRS$.' || cp.concurrent_program_name

           AND cp_param.application_id             (+) = cp.application_id

           AND cp_param.enabled_flag               (+) = 'Y'

           AND req.concurrent_program_id               = rset_prog.concurrent_program_id

           AND req.program_application_id              = rset_prog.application_id

           AND req.argument1                           = to_char(rs.application_id)

           AND req.argument2                           = to_char(rs.request_set_id)

         ORDER BY req.request_id

                , rss.display_sequence

                , rsp.sequence

                , cp_param.column_seq_num

       ) a

 

Query to get request set parameter in oracle apps