APPS.POR_AME_APPROVAL_LIST Error Message

Today I had a support call where a user was raising a requisition on behalf of another member of staff in iProcurement (using R12.1.3).

When they tried to send the requisition for approval they saw this message:

Error Page
You have encountered an unexpected error. Please contact the System Administrator for assistance.

I enabled the FND: Diagnostics profile at User Level for the user, and they tried again.

This time a link appeared allowing them to click into the details.

This time they got a massive error message – starting with:

Exception Details.
oracle.apps.fnd.framework.OAException: java.sql.SQLException: ORA-20001: The approver group First Supervisor with Authority has dynamic query in wrong format or it has returned an invalid approver. ORA-06512: at "APPS.POR_AME_APPROVAL_LIST", line 3232 ORA-06512: at line 1 at...

The user was able to raise requisitions on behalf of other users. When I compared the HR record for one of those users, with the one that led to the error, I noticed that person had no Supervisor showing on their HR Assignment. Once that was added in, problem solved.

 

SQL – Concurrent Requests – Hierarchy

Often concurrent requests have a parent request. For example, you might run a big request set with a whole series of child jobs, and each of those might launch child jobs.

Single jobs rather than request sets can also launch a series of child jobs, such as Create Accounting in Release 12.

Sometimes it can be useful to extract the info about those jobs and the hierarchy / position they ran in.

This SQL can be useful to extract that information.

-- ##############################################################################
--      HIERARCHY
-- ############################################################################*/
 
SELECT   LPAD(' ', (LEVEL - 1) * 10, ' ') || fcr.request_id id
       , LEVEL
       , NVL(fcr.description, fcpt.user_concurrent_program_name) job_name
       , DECODE (fcr.phase_code , 'P', 'Pending' , 'R', 'Running' , 'C', 'Complete' , 'I', 'Inactive') phase
       , DECODE (fcr.status_code , 'A', 'Waiting' , 'B', 'Resuming' , 'C', 'Normal' , 'D', 'Cancelled' , 'E', 'Error' , 'F', 'Scheduled' , 'G', 'Warning' , 'H', 'On Hold' , 'I', 'Normal' , 'M', 'No Manager' , 'Q', 'Standby' , 'R', 'Normal' , 'S', 'Suspended' , 'T', 'Terminating' , 'U', 'Disabled' , 'W', 'Paused' , 'X', 'Terminated' , 'Z', 'Waiting') status
       , fcr.actual_start_date start_ 
       , fcr.actual_completion_date end_
       , fcr.argument_text
       , fcr.request_type
       , fu.user_name
       , (replace(replace(fcr.completion_text,chr(10),''),chr(13),' ')) completion_text
    FROM applsys.fnd_concurrent_requests fcr
    JOIN applsys.fnd_user fu ON fcr.requested_by = fu.user_id
    JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.program_application_id = fcpt.application_id
   WHERE fcr.phase_code != 'P'  
START WITH fcr.request_id = :parent
CONNECT BY PRIOR fcr.request_id = fcr.parent_request_id;

Example output:

Example output

SQL – Concurrent Requests – Extract Parameter Details

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

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

-- ##########################################################################################################
-- Concurrent Requests - accessing parameter data
-- https://community.oracle.com/message/13365904
-- ##### Usage Notes #####
-- Enter Request ID to return parameters and associated values.
-- If the parameter is linked to a list of values, you only get the ID and not the actual translated value
-- If the job has more than 25 parameters, this only returns the first 25
-- ##########################################################################################################

    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;

SQL – Concurrent Requests – Scheduled

While working in one job where we were doing a big upgrade, we needed to cancel all of the scheduled jobs before the upgrade started, and then start them up again after the upgrade.

I wanted to report on our scheduled concurrent requests. I had SQL to extract the names of requests, who requested them, and via which responsibility – all the usual stuff.

What I didn’t have was a way to extract when the jobs ran. The only option I had was to click into the details of every request, clicking on the “Schedule” button, and copy down the details for hundreds of jobs. It didn’t look hopeful!

On doing some digging, I found some useful SQL on Oracle’s forums. It turned out the info was held in the fnd_conc_release_classes table – with the nitty gritty schedule data mainly held in the release_class_id field.

Examples

The value of release_class_id varies depending on your schedule:

  • Mon, Tue, Wed, Thu and Fri: 000000000000000000000000000000000111110
  • Tue, Wed, Thu, Fri, Sat: 000000000000000000000000000000000011111
  • Saturday: 000000000000000000000000000000000000001
  • Sunday: 000000000000000000000000000000001000000
  • 6th, 13th, 20th, and 27th of every month: 000001000000100000010000001000000000000

To make sense of that required some decode statements. I didn’t write them myself – I found those on the web too.

The SQL

This SQL returns a list of any jobs on your EBS system which are scheduled.

The data which contains the schedule information is in the class_info column of the fnd_conc_release_classes table.

A variety of decodes can then be used to extract the schedule information.

-- ##############################################################################
-- #        SCHEDULED CONCURRENT REQUESTS
-- ############################################################################*/

        SELECT fcr.request_id
             , fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
             , fcr.description
             , fcr.request_date
             , fcr.requested_start_date requested_start
             , TRUNC(fcr.requested_start_date) requested_start_trim
             , TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
             , DECODE (fcr.phase_code, 'P', 'Pending', 'R', 'Running', 'C', 'Complete', 'I', 'Inactive') phase
             , DECODE (fcr.status_code , 'A', 'Waiting' , 'B', 'Resuming' , 'C', 'Normal' , 'D', 'Cancelled' , 'E', 'Error' , 'F', 'Scheduled' , 'G', 'Warning' , 'H', 'On Hold' , 'I', 'Normal' , 'M', 'No Manager' , 'Q', 'Standby' , 'R', 'Normal' , 'S', 'Suspended' , 'T', 'Terminating' , 'U', 'Disabled' , 'W', 'Paused' , 'X', 'Terminated' , 'Z', 'Waiting') status
             , fat.application_short_name application
             , fu.user_name requester
             , fu.description requested_by
             , frt.responsibility_name requested_by_resp
             , fcr.argument_text "PARAMETERS"
             , CASE WHEN TO_CHAR(fcr.requested_start_date, 'D') = 1 THEN 'Monday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 2 THEN 'Tuesday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 3 THEN 'Wednesday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 4 THEN 'Thursday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 5 THEN 'Friday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 6 THEN 'Saturday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 7 THEN 'Sunday' END requested_start_day
             , TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
             , '------>' holds
             , fcr.hold_flag
             , DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
             , CASE
                  WHEN fcr.hold_flag = 'Y'
                     THEN SUBSTR(
                            u2.description
                          , 0
                          , 40
                         )
               END last_update_by
             , CASE
                  WHEN fcr.hold_flag = 'Y'
                     THEN fcr.last_update_date
               END last_update_date
             , '------>' prints
             , fcr.number_of_copies print_count
             , fcr.printer
             , fcr.print_style
             , '------>' schedule
             , DECODE (fcrc.class_type,
                   'P', 'Periodic',
                   'S', 'On Specific Days',
                   'X', 'Advanced',
                   fcrc.class_type
               ) schedule_type
             , DECODE(fcr.increment_dates, 'N', '', 'Y', 'Yes') increment_dates
             , CASE WHEN fcrc.class_info IS NULL THEN
                TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
               END run_once
             , CASE WHEN fcrc.class_type = 'P' THEN
                SUBSTR(fcrc.class_info, 1, INSTR(fcrc.class_info, ':') - 1)
               END repeat_interval
             , CASE WHEN fcrc.class_type = 'P' THEN
                DECODE(SUBSTR(fcrc.class_info, INSTR(fcrc.class_info, ':', 1, 1) + 1, 1),
                       'N', 'minutes',
                       'M', 'months',
                       'H', 'hours',
                       'D', 'days')
               END repeat_interval_unit       
             , CASE WHEN fcrc.class_type = 'P' THEN
                DECODE(SUBSTR(fcrc.class_info, INSTR(fcrc.class_info, ':', 1, 2) + 1, 1),
                       'S', ' from the start of the prior run',
                       'C', ' from the completion of the prior run')
               END from_the     
               , CASE WHEN fcrc.class_type = 'S' AND INSTR(SUBSTR(fcrc.class_info, 33),'1',1) > 0 THEN
                       DECODE(SUBSTR(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 39, 1), '1', 'Sat, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 33, 1), '1', 'Sun ')
                 end  days_of_week 
               , CASE WHEN fcrc.class_type = 'S' AND INSTR(SUBSTR(fcrc.class_info, 1, 31),'1',1) > 0 THEN
                       DECODE(SUBSTR(fcrc.class_info, 1,  1), '1', '1st, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 2,  1), '1', '2nd, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 3,  1), '1', '3rd, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 4,  1), '1', '4th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 5,  1), '1', '5th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 6,  1), '1', '6th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 7,  1), '1', '7th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 8,  1), '1', '8th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 9,  1), '1', '9th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 10, 1), '1', '10th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 11, 1), '1', '11th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 12, 1), '1', '12th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 13, 1), '1', '13th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 14, 1), '1', '14th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 15, 1), '1', '15th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 16, 1), '1', '16th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 17, 1), '1', '17th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 18, 1), '1', '18th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 19, 1), '1', '19th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 20, 1), '1', '20th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 21, 1), '1', '21st, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 22, 1), '1', '22nd, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 23, 1), '1', '23rd,' ) ||
                       DECODE(SUBSTR(fcrc.class_info, 24, 1), '1', '24th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 25, 1), '1', '25th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 26, 1), '1', '26th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 27, 1), '1', '27th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 28, 1), '1', '28th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 29, 1), '1', '29th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 30, 1), '1', '30th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 31, 1), '1', '31st. ')
                 END days_of_month
               , CASE WHEN fcrc.class_type = 'S' AND SUBSTR(fcrc.class_info, 32, 1) = '1' THEN
                          'Yes'
                 END last_day_of_month_ticked
               , fcrc.class_info
          FROM applsys.fnd_concurrent_requests fcr
          JOIN applsys.fnd_user fu                     ON fcr.requested_by =           fu.user_id
          JOIN applsys.fnd_user u2                     ON fcr.last_updated_by =        u2.user_id
          JOIN applsys.fnd_concurrent_programs fcp     ON fcr.concurrent_program_id =  fcp.concurrent_program_id  AND fcr.program_application_id = fcp.application_id
          JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcp.concurrent_program_id =  fcpt.concurrent_program_id AND fcp.application_id =         fcpt.application_id
          JOIN applsys.fnd_printer_styles_tl fpst      ON fcr.print_style =            fpst.printer_style_name 
     LEFT JOIN applsys.fnd_conc_release_classes fcrc   ON fcr.release_class_id =       fcrc.release_class_id
          JOIN applsys.fnd_responsibility_tl frt       ON fcr.responsibility_id =      frt.responsibility_id 
          JOIN applsys.fnd_application fat             ON fcr.program_application_id = fat.application_id AND fat.application_id = fcpt.application_id AND fcp.application_id = fcpt.application_id 
         WHERE fcr.phase_code = 'P' -- scheduled / pending
           AND fcr.hold_flag = 'N' -- not on hold
--           AND NVL(fcr.description, fcpt.user_concurrent_program_name) LIKE 'Con%'
           AND 1 = 1;