Concurrent Program Parameters - include ValueSets Query

           SELECT DISTINCT fcpl.user_concurrent_program_name "Concurrent Program Name",

                fcp.concurrent_program_name "Short Name"                                 ,

                fat.application_name                                                     ,

                fl.meaning execution_method                                              ,

                fe.execution_file_name                                                   ,

                fcp.output_file_type                                                     ,

                fdfcuv.column_seq_num "Column Seq Number"                                ,

                fdfcuv.end_user_column_name "Parameter Name"                             ,

                fdfcuv.form_left_prompt "Prompt"                                         ,

                fdfcuv.enabled_flag " Enabled Flag"                                      ,

                fdfcuv.required_flag "Required Flag"                                     ,

                fdfcuv.display_flag "Display Flag"                                       ,

                fdfcuv.flex_value_set_id "Value Set Id"                                  ,

                ffvs.flex_value_set_name "Value Set Name"                                ,

                flv.meaning "Default Type"                                               ,

                fdfcuv.default_value "Default Value"

                 FROM fnd_concurrent_programs fcp ,

                fnd_concurrent_programs_tl fcpl   ,

                fnd_descr_flex_col_usage_vl fdfcuv,

                fnd_flex_value_sets ffvs          ,

                fnd_lookup_values flv             ,

                fnd_lookups fl                    ,

                fnd_executables fe                ,

                fnd_executables_tl fet            ,

                fnd_application_tl fat

                WHERE 1                     = 1

              AND fcp.concurrent_program_id = fcpl.concurrent_program_id

              AND fcp.enabled_flag          = 'Y'

              AND fdfcuv.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name

              AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

              AND flv.lookup_type(+)     = 'FLEX_DEFAULT_TYPE'

              AND flv.lookup_code(+)     = fdfcuv.default_type

              AND fcpl.LANGUAGE          = 'US'

              AND flv.LANGUAGE(+)        = 'US'

              AND fl.lookup_type         ='CP_EXECUTION_METHOD_CODE'

              AND fl.lookup_code         =fcp.execution_method_code

              AND fe.executable_id       = fcp.executable_id

              AND fe.executable_id       =fet.executable_id

              AND fet.LANGUAGE           = 'US'

              AND fat.application_id     =fcp.application_id

              AND fat.LANGUAGE           = 'US'

              and upper(fcpl.user_concurrent_program_name) like upper('Vertex Recon - AR to O Series Tax Reconciliation')

              --and upper(fe.execution_file_name) like 'PO_CHNG_PKG%'

              --and fcp.concurrent_program_name=''

              ORDER BY fdfcuv.column_seq_num;

Check Multiple Values in WHERE Clause SQL Query REGEXP_LIKE

-- Created on 9/29/2017 by C_CKANDA

DECLARE

  -- Local variables here

  LN                  INTEGER;

  LV_TRANS_TYPES      VARCHAR2(4000);

  LV_TRANS_TYPE_IDS   VARCHAR2(4000);

  LV_TRANS_TYPES_IDS  VARCHAR2(4000);

  LV_TRANS_TYPES_IDS1 VARCHAR2(4000);

BEGIN

  -- Test statements here

  SELECT LISTAGG(TRANSACTION_TYPE_ID, '|') WITHIN GROUP(ORDER BY 1)

    INTO LV_TRANS_TYPES_IDS

    FROM MTL_TRANSACTION_TYPES

   WHERE TRANSACTION_TYPE_NAME IN

         (SELECT MEANING

            FROM FND_LOOKUP_VALUES_VL

           WHERE LOOKUP_TYPE = 'LOOKUPNAME');

-- it will return multiple values like 2|38|45|60 

  LV_TRANS_TYPES_IDS := '^(' || LV_TRANS_TYPES_IDS || ')$';

 -- assignment  ^(2|38|45|60)$ passing this values to the below query

  SELECT COUNT(*)

    INTO LN

    FROM MTL_TRANSACTION_TYPES

   WHERE 1 = 1

     AND REGEXP_LIKE(TRANSACTION_TYPE_ID, LV_TRANS_TYPES_IDS);

  DBMS_OUTPUT.PUT_LINE(LN);

END;

Long Running Program and SQL Query

LONG RUNNING PROGRAM

SELECT a.request_id

,a.oracle_process_id "SPID"

,frt.responsibility_name

,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name

,a.description

,a.ARGUMENT_TEXT

,b.node_name

,b.db_instance

,a.logfile_name

,a.logfile_node_name

,a.outfile_name

,q.concurrent_queue_name

,a.phase_code,a.status_code, a.completion_text

, actual_start_date

, actual_completion_date

, fu.user_name

,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins

,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time

FROM APPLSYS.fnd_Concurrent_requests a2,

APPLSYS.fnd_concurrent_programs c2

WHERE c2.concurrent_program_id = c.concurrent_program_id

AND a2.concurrent_program_id = c2.concurrent_program_id

AND a2.program_application_id = c2.application_id

AND a2.phase_code || '' = 'C') avg_mins

,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours

FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b

,applsys.fnd_concurrent_queues q

,APPLSYS.fnd_concurrent_programs c

,APPLSYS.fnd_concurrent_programs_tl ctl

,apps.fnd_user fu

,apps.FND_RESPONSIBILITY_TL frt

WHERE a.controlling_manager = b.concurrent_process_id

AND a.concurrent_program_id = c.concurrent_program_id

AND a.program_application_id = c.application_id

AND a.phase_code = 'R'

AND a.status_code = 'R'

AND b.queue_application_id = q.application_id

AND b.concurrent_queue_id = q.concurrent_queue_id

AND ctl.concurrent_program_id = c.concurrent_program_id

AND a.requested_by = fu.user_id

AND a.responsibility_id = frt.responsibility_id

ORDER BY a.actual_start_date DESC

GET THE SESSION ID FROM PREVIOUS QUERY

SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,

b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time, ROUND((SYSDATE - b.logon_time) * 1440, 2)   "Runtime (in Minutes)"  

FROM v$process a, v$session b, v$sqltext c

WHERE a.addr=b.paddr

AND b.sql_hash_value = c.hash_value

AND b.STATUS = 'ACTIVE'

AND a.spid = 'SESSION ID PUT'

ORDER BY a.spid, c.piece

PROGRAM CONSUMING TIME

SELECT /*+ rule */

       rq.parent_request_id                   "Parent Req. ID",

       rq.request_id                          "Req. ID",

       tl.user_concurrent_program_name        "Program Name",

       rq.actual_start_date                   "Start Date",

       rq.actual_completion_date              "Completion Date",

       ROUND((rq.actual_completion_date - rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"   ,  

       ROUND((sysdate - rq.actual_start_date) * 1440, 2)   "Runing (in Minutes)" ,

       ROUND((sysdate - rq.actual_start_date) * 1440/60, 2)   "Runing (in Hours)"

  FROM applsys.fnd_concurrent_programs_tl  tl,

       applsys.fnd_concurrent_requests     rq

 WHERE tl.application_id        = rq.program_application_id

   AND tl.concurrent_program_id = rq.concurrent_program_id

   AND tl.LANGUAGE              = USERENV('LANG')

--   AND rq.actual_start_date IS NOT NULL

--   AND rq.actual_completion_date IS NOT NULL

  and  request_id=202319707