Query To Find Concurrent Program status and other Details

 SELECT fu.user_name

             requested_by,fu.user_id,

         fcr.request_id,--fcr.concurrent_program_id,

         CASE

             WHEN fcr.phase_code = 'R' AND fcr.status_code = 'R'

             THEN

                 'RUNNING'

             ELSE

                 CASE

                     WHEN fcr.phase_code = 'C' AND fcr.status_code = 'C'

                     THEN

                         'COMPLETED NORMAL'

                     ELSE

                         CASE

                             WHEN     fcr.phase_code = 'C'

                                  AND fcr.status_code = 'G'

                             THEN

                                 'WARNING'

                             ELSE

                                 CASE

                                     WHEN     fcr.phase_code = 'C'

                                          AND fcr.status_code = 'E'

                                     THEN

                                         'ERROR'

                                     ELSE

                                         CASE

                                             WHEN     fcr.phase_code = 'P'

                                                  AND fcr.status_code = 'Q'

                                             THEN

                                                 'PENDING QUEUE INACTIVE MANAGER'

                                             ELSE

                                                 CASE

                                                     WHEN     fcr.phase_code =

                                                              'C'

                                                          AND fcr.status_code =

                                                              'D'

                                                     THEN

                                                         'COMPLETED CANCELLED'

                                                     ELSE

                                                         CASE

                                                             WHEN     fcr.phase_code =

                                                                      'C'

                                                                  AND fcr.status_code =

                                                                      'X'

                                                             THEN

                                                                 'TERMINATED'

                                                             ELSE

                                                                 'CHECK STATUS'

                                                         END

                                                 END

                                         END

                                 END

                         END

                 END

         END

             status,

         parent_request_id,

         fcpv.user_concurrent_program_name,fcpv.concurrent_program_id,

         fcr.argument_text,

         sysdate,

         fcr.requested_start_date,

         TO_CHAR (fcr.actual_start_date, 'DD-MM-RRRR HH:MI:SS PM')

             actual_start_date,

         fcr.actual_completion_date,

         fcr.responsibility_id,

         frv.responsibility_name,

         fcr.phase_code,

         fcr.status_code,

         fcr.completion_text,

         logfile_node_name,

         outfile_node_name,

         printer,

         print_style,

         EMAIL_ADDRESS,

         argument1,

         argument2, argument3, argument4, argument5,argument6,argument7,argument8, argument9, argument10,argument11,argument12

         , fcr.outfile_name

    FROM fnd_concurrent_requests   fcr,

         fnd_responsibility_vl     frv,

         fnd_concurrent_programs_vl fcpv,

         fnd_user                  fu

   WHERE     fcr.concurrent_program_id = fcpv.concurrent_program_id

         AND fcr.requested_by = fu.user_id

         AND (UPPER (fcpv.user_concurrent_program_name) LIKE

                  UPPER ('%<Request_set  OR Concurrent_program Name>%'))

         AND fcr.responsibility_id = frv.responsibility_id

ORDER BY fcr.request_id DESC;

Query to Find Request set Name

SELECT distinct rs.user_request_set_name         "Request Set" 

     , rss.display_sequence             Seq

     , cp.user_concurrent_program_name  "Concurrent Program" 

     , e.execution_file_name 

     , lv.meaning                       file_type, rs.*

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

     , apps.fnd_lookup_values           lv

 WHERE 1 = 1 

   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 cp.executable_id              = e.executable_id

   AND cp.executable_application_id  = e.application_id

   AND lv.lookup_type                = 'CP_EXECUTION_METHOD_CODE'

   AND lv.lookup_code                = e.execution_method_code

   AND  rs.user_request_set_name = '<request set name>' 

--AND cp.user_concurrent_program_name = <concurrent_program_name>

   AND rs.end_date_active IS NULL

   AND language = 'US'

ORDER BY rss.display_sequence

Usage Of Ref Cursor

DECLARE
    TYPE lcur_order_details IS REF CURSOR;
    lcur_order_details_rc lcur_order_details;
   
    l_order_number VARCHAR2(50);
BEGIN
    OPEN lcur_order_details_rc FOR SELECT order_number FROM oe_order_headers_all WHERE order_number = 52424905;
        LOOP
            FETCH lcur_order_details_rc INTO l_order_number;
            EXIT WHEN lcur_order_details_rc%NOTFOUND;
        END LOOP;
    CLOSE lcur_order_details_rc;
    dbms_output.put_line('l_order_number :- ' || l_order_number);
END;

-------------------------------------------------------
DECLARE
TYPE lcur_order_details IS REF CURSOR;
lcur_order_details_rc lcur_order_details;

TYPE lr_order_detail_rec IS RECORD
        ( l_order_number oe_order_headers_all.order_number%TYPE,
          l_line_number  VARCHAR2(20),
          l_ordered_item oe_order_lines_all.ordered_item%Type,
          l_line_status  oe_order_lines_all.flow_status_code%TYPE,
          l_order_status oe_order_headers_all.flow_status_code%TYPE);
   
    TYPE lt_order_detail_tab IS TABLE OF lr_order_detail_rec
        INDEX BY BINARY_INTEGER;
   
    lt_order_detail_tab_o     lt_order_detail_tab;

l_order_number VARCHAR2(50);
BEGIN
OPEN lcur_order_details_rc FOR SELECT order_number, oe_flex_util.get_concat_value (oola.line_number,
 oola.shipment_number,
 oola.option_number,
 oola.component_number,
 oola.service_number)
line_num,
oola.ordered_item,
oola.flow_status_code,
ooha.flow_status_code
FROM oe_order_headers_all ooha, oe_order_lines_all oola
WHERE ooha.header_id = oola.header_id
AND ooha.order_number = '52424905';
LOOP
FETCH lcur_order_details_rc BULK COLLECT INTO lt_order_detail_tab_o;
EXIT WHEN lcur_order_details_rc%NOTFOUND;
END LOOP;
CLOSE lcur_order_details_rc;
dbms_output.put_line('lt_order_detail_tab_o.COUNT :- ' || lt_order_detail_tab_o.COUNT);
END;

Query to find executable file details of concurrent program by passing the concurrent prorgam name as input

As part of supporting Oracle daily, sometimes we have to write certain queries multiple times in order to find the details. One such query is to find the concurrent program details like its executable file name. In order to reduce this tedious task, please use below query and keep it handy.

SELECT user_concurrent_program_name, description, concurrent_program_name,EXECUTION_FILE_NAME , 
fe.EXECUTION_METHOD_CODE, fcp.*
  FROM fnd_concurrent_programs_vl fcp, fnd_executables fe
 WHERE fcp.executable_id = fe.executable_id
    AND fcp.enabled_flag = 'Y'
--and UPPER(execution_file_name) LIKE  'XX%'  -- pass the file name here
and  UPPER(user_concurrent_program_name) LIKE 
    UPPER( '<Pass the concurrent program here>')
--and concurrent_program_id = 32766
--and concurrent_program_name = '<pass the concurrent prorgam short name here>'
;

Kindly comment if you like it :)