Concurrent Requests – Parameters

Often when you’re analysing a concurrent request via SQL, you might want to see the parameters which were used when that request was submitted. You can see basic information via the argument_text on the fnd_concurrent_requests table – for example, for a run of Gather Schema Statistics you might see something like ALL, 10, 3, NOBACKUP, , LASTRUN, GATHER, , Y.

You can see the same values in the argument1, argument2, argument3 fields on the same fnd_concurrent_requests table:

Screenshot showing argument fields

What this doesn’t tell you is the name of the parameter which the argument text relates to.

Half way there

You can extract the parameters linked to a concurrent request definition via SQL – e.g. taking Gather Schema Statistics as our example job, whose concurrent_program_name is FNDGSCST:

	SELECT cp.concurrent_program_name CP_Name           -- The Concurrent Program name
		 , dfcu.end_user_column_name Column_name        -- The real argument name 
		 , lv.meaning data_type                         -- The data type of argument
		 , ffv.maximum_size                             -- The length of the argument
		 , dfcu.required_flag                           -- The argument required or not
		 , dfcu.display_flag                            -- The argument displayed or not on Oracle Form 
		 , dfcu.default_value                           -- The default value of the argument
		 , dfcu.column_seq_num                          -- The argument sequence number  
	FROM apps.fnd_concurrent_programs_vl cp 
	JOIN apps.fnd_descr_flex_col_usage_vl dfcu ON dfcu.descriptive_flexfield_name ='$SRS$.'||cp.concurrent_program_name
	JOIN apps.fnd_flex_value_sets ffv ON ffv.flex_value_set_id = dfcu.flex_value_set_id
	JOIN apps.fnd_lookup_values_vl lv ON lv.lookup_code = ffv.format_type AND lv.lookup_type = 'FIELD_TYPE' AND lv.enabled_flag = 'Y' AND lv.security_group_id = 0 AND lv.view_application_id = 0
  WHERE  cp.concurrent_program_name = 'FNDGSCST'
ORDER BY cp.concurrent_program_name
	   , dfcu.column_seq_num;

That returns this:

Screenshot of parameters SQL output

That’s useful in that we can see the parameters linked to the job definition.

The Answer

Ideally we want to be able to see the list of parameters on a job, and the values entered against them for any given request_id. The SQL below allows you to do that.

This is sample output:

Screenshot of parameters and values against a concurrent job

There are drawbacks:

  • The SQL only gives the first 25 parameter values because the fnd_concurrent_requests table only holds 25 argument fields. If your request has more than 25 parameters, you might need to do something fancy by hacking up the data in the fnd_concurrent_requests.argument_text field, which contains a comma separated list of arguments, instead of getting the parameter values from the argumentn fields.
  • Where the parameter is linked to a record from another table, e.g. AP Invoice Number, PA Project Number, the parameter value will return the Invoice ID / Project ID etc, and not the actual Invoice Number / Project Number – unless you specifically build in joins to the related tables.

Therefore it’s a bit rough and ready, but can still be useful nonetheless.

-- ##########################################################################################################
-- Concurrent Requests - accessing parameter data
-- https://community.oracle.com/message/13365904
-- ##########################################################################################################

    WITH tbl_job_data AS (SELECT fcr.argument1
                               , fcr.argument2
                               , fcr.argument3
                               , fcr.argument4
                               , fcr.argument5
                               , fcr.argument6
                               , fcr.argument7
                               , fcr.argument8
                               , fcr.argument9
                               , fcr.argument10
                               , fcr.argument11
                               , fcr.argument12
                               , fcr.argument13
                               , fcr.argument14
                               , fcr.argument15
                               , fcr.argument16
                               , fcr.argument17
                               , fcr.argument18
                               , fcr.argument19
                               , fcr.argument20
                               , fcr.argument21
                               , fcr.argument22
                               , fcr.argument23
                               , fcr.argument24
                               , fcr.argument25
                            FROM applsys.fnd_concurrent_requests fcr 
                           WHERE fcr.request_id = :reqid)
       , tbl_params   AS (SELECT dfcu.column_seq_num col_seq
                               , dfcu.end_user_column_name col_prompt
                               , fcr.request_id
                               , fcr.completion_text
                               , cp.user_concurrent_program_name job_name
                               , dfcu.display_flag
                               , '' col_data
                               , '' col_attrib  
                            FROM apps.fnd_concurrent_programs_vl cp 
                            JOIN apps.fnd_descr_flex_col_usage_vl dfcu ON dfcu.descriptive_flexfield_name ='$SRS$.' || cp.concurrent_program_name
                            JOIN apps.fnd_flex_value_sets ffv          ON ffv.flex_value_set_id           = dfcu.flex_value_set_id
                            JOIN apps.fnd_lookup_values_vl lv          ON lv.lookup_code                  = ffv.format_type
                            JOIN applsys.fnd_concurrent_requests fcr   ON fcr.concurrent_program_id       = cp.concurrent_program_id
                             AND lv.lookup_type = 'FIELD_TYPE' 
                             AND lv.enabled_flag = 'Y'
                             AND lv.security_group_id = 0 
                             AND lv.view_application_id = 0
                           WHERE fcr.request_id = :reqid
                        ORDER BY dfcu.column_seq_num)
    SELECT y.request_id
         , y.job_name
         , y.col_seq seq
         , y.col_prompt param
         , x.col_attrib job_value
         , y.completion_text
              FROM (SELECT row_number() OVER (ORDER BY LPAD (REGEXP_SUBSTR (d.col_data, '\d+'), 3, '0')) AS r_num 
                         , NULL AS col_prompt  
                         , d.col_data  
                         , d.col_attrib  
                      FROM tbl_job_data  
                          UNPIVOT INCLUDE NULLS  
                                (col_attrib
                             FOR col_data IN (argument1
                                            , argument2
                                            , argument3
                                            , argument4
                                            , argument5
                                            , argument6
                                            , argument7
                                            , argument8
                                            , argument9
                                            , argument10
                                            , argument11
                                            , argument12
                                            , argument13
                                            , argument14
                                            , argument15
                                            , argument16
                                            , argument17
                                            , argument18
                                            , argument19
                                            , argument20
                                            , argument21
                                            , argument22
                                            , argument23
                                            , argument24
                                            , argument25)
                                 ) d
                    ) x
                , (SELECT   request_id
                          , job_name
                          , completion_text
                          , col_seq  
                          , col_prompt
                          , ROW_NUMBER () OVER (ORDER BY col_seq)  AS r_num
                      FROM tbl_params
                  ) y
            WHERE x.r_num  = y.r_num
         ORDER BY x.r_num;

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*