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;

Concurrent Requests – Hierarchy

Often if you look in the fnd_concurrent_requests table you’ll see that for some requests, the parent_request_id column is populated.

This can be the case if a concurrent request is part of a Request Set, where a series of jobs run, one after the other.

At other times, one request can launch / or spawn child requests. A common example in Release 12 of such a request is the Create Accounting job.

You can use the Connect By Prior condition of a Hierarchical Query to extract the hierarchy of a set of requests.

For example – this is sample output for the Create Accounting job:

Here are the 2 versions of the SQL, one using Oracle syntax, the other using ANI syntax.

Oracle Syntax SQL

-- ##############################################################################
--      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
       , fu.user_name
       , fcr.completion_text
    FROM applsys.fnd_concurrent_requests fcr
       , applsys.fnd_user fu
       , applsys.fnd_concurrent_programs_tl fcpt
   WHERE fcr.requested_by = fu.user_id
     AND fcr.concurrent_program_id = fcpt.concurrent_program_id
     AND fcr.program_application_id = fcpt.application_id
     AND fcr.phase_code != 'P'   
START WITH fcr.request_id = 20491922
CONNECT BY PRIOR fcr.request_id = fcr.parent_request_id;

ANSI Syntax SQL

-- ##############################################################################
--       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 = 20491922
CONNECT BY PRIOR fcr.request_id = fcr.parent_request_id;

Concurrent Requests – Usage

If you spend much time doing Oracle EBS support, you probably spend quite a while looking at concurrent requests. While users with access to the System Administrator responsibility have the option to review which concurrent requests have run, I find the interface is pretty cumbersome, since you can’t easily access all of the bits of info you need in one place, such as the username of the person who submitted the request, the responsibility they used, the date the request was submitted, when it started, how long it ran for etc.

The following are some queries I’ve come to rely on when I want to query the database to check on concurrent requests, and their usage. I have other queries to investigate request definitions, schedule information, parameters used when the request was submitted and so on. The queries in this post focus on their actual usage.

Additional Notes

ofile_size
This is the size of the request’s output file in bytes – I find it useful to see this value because if you’re comparing a request which runs a number of times, and the request is one which creates a lot of output, e.g Create Accounting, then you can check if the request did something meaningful – if the value is the same value most of the time then is a lot bigger on a certain day or for certain parameters, you can see that the request created a larger output file.
lfile_size
This is the size of the request’s log file in bytes.
parent_request_id
Concurrent Requests are often spawned / launched via another request – e.g. Create Accounting will trigger a series of jobs. If a request’s parent_request_id is populated, having that info to hand can be useful for following the sequence of jobs. There’s a peice of SQL you can run to look at concurrent request hierarchy.
argument_text
When you submit a concurrent request, the request normally accepts a number of parameters, or arguments. The first 25 arguments are listed in the fnd_concurrent_requests table as argument1 through to argument25. All of the arguments used in the request are concatenated into the argument_text field – even if the request has more than 25 parameters. Therefore reviewing the data held in argument_text can be useful. For years I only looked at argument_text, but when I realised the first 25 parameter values are held in their own fields, I started using those too, because it allows your queries to be narrowed down a lot more easily – e.g. return all records where argument2 = ‘200’ – rather than where argument_text is like ‘%200%’.
resub_count
If a request is submitted via a schedule, and runs every n days / hours / minutes, then each time it runs, the resub_count value is incremented by 1. It can be useful to review this info for some jobs, as you can see at a glance if the request has been run many times, or, if this value is NULL, if it’s been run as a one-off.
root_request_id
If a request is submitted via a schedule, then the root_request_id ID is the request_id of the first job that ever ran as part of the schedule. If your Purge Concurrent Request and/or Manager Data jobs haven’t deleted the data for that root job, then sometimes it can be useful to go back to that root_request_id to review it.

The SQL

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

    SELECT  fcr.request_id id
           , fcr.ofile_size
           , fcr.parent_request_id
--           , fcp.concurrent_program_name job_name
           , NVL(fcr.description, fcpt.user_concurrent_program_name) job
           , fcr.request_date
--           , fcr.status_code
           , 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
           , TO_CHAR(fcr.actual_start_date, 'Dy') day
           , TRUNC(fcr.actual_start_date) run_date
           , TO_CHAR((fcr.actual_start_date), 'HH24:MI:SS') start_
           , TO_CHAR((fcr.actual_completion_date), 'HH24:MI:SS') end_
           , CASE WHEN fcr.phase_code = 'R' AND fcr.actual_completion_date IS NULL THEN TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((SYSDATE-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) ELSE TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((fcr.actual_completion_date-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) END duration
           , CASE WHEN TRUNC(fcr.actual_start_date) = TRUNC(SYSDATE) THEN '*' END t
           , frt.responsibility_name resp
           , fu.user_name || ' (' || substr(fu.description, 0, 30) || ')' submitted_by
           , (REPLACE(REPLACE(fcr.completion_text,CHR(10),''),CHR(13),' ')) completion_text
--           , 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
           , fcr.argument_text
           , fcr.resub_count
           , fcr.root_request_id
        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
        JOIN applsys.fnd_concurrent_programs fcp     ON fcp.concurrent_program_id =  fcpt.concurrent_program_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
        JOIN applsys.fnd_responsibility_tl frt       ON fcr.responsibility_id =      frt.responsibility_id
   LEFT JOIN applsys.fnd_conc_pp_actions fcpa        ON fcr.request_id =             fcpa.concurrent_request_id AND fcpa.program_application_id = fcp.application_id
       WHERE 1 = 1
         AND fcr.request_date > '19-APR-2017'
--         AND fcr.request_date < '01-SEP-2016'
--         AND fcr.request_id IN (2096378)
         AND fcr.actual_start_date IS NOT NULL
--         AND fu.user_name IN ('SYSADMIN')
--         AND NVL(fcr.description, fcpt.user_concurrent_program_name) = 'Diagnostic Apps Check'
--         AND NVL(fcr.description, fcpt.user_concurrent_program_name) != 'OAM Applications Dashboard Collection'
--         AND fcr.actual_completion_date BETWEEN TO_DATE('17-JUN-2016 22:00:00', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('17-JUN-2016 23:59:00', 'DD-MON-YYYY HH24:MI:SS')
--         AND fcr.argument1 = '200'
--         AND fcr.argument_text like '%761574%'
--         AND fcr.actual_completion_date IS NOT NULL
--         AND fcr.phase_code = 'P'
--         AND fcr.phase_code = 'R'
--         AND fcr.status_code = 'E'
    ORDER BY fcr.actual_start_date DESC;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--         CONCURRENT REQUESTS
-- ############################################################################*/
SELECT   fcr.request_id id
--       , REPLACE(fcr.parent_request_id, -1, '') parent
       , NVL(fcr.description, fcpt.user_concurrent_program_name) job_name
--       , fcpt.description
       , fat.application_short_name app
       , 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
       , to_char (fcr.actual_start_date, 'Dy') day
       , TRUNC(fcr.actual_start_date) run_date
       , TO_CHAR((fcr.actual_start_date), 'HH24:MI:SS') start_
       , TO_CHAR((fcr.actual_completion_date), 'HH24:MI:SS') end_
       , CASE WHEN fcr.phase_code = 'R' AND fcr.actual_completion_date IS NULL THEN TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((SYSDATE-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) ELSE TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((fcr.actual_completion_date-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) END dur
       , CASE WHEN TRUNC(fcr.actual_start_date) = TRUNC(SYSDATE) THEN '*' END t
       , frt.responsibility_name resp
       , fu.user_name || ' (' || fu.description || ')' submitted_by
       , fcr.argument_text params
       , fcr.completion_text
       , fcr.printer
       , fcr.number_of_copies copies
    FROM applsys.fnd_concurrent_requests fcr
       , applsys.fnd_user fu
       , applsys.fnd_concurrent_programs_tl fcpt
       , applsys.fnd_application fat
       , applsys.fnd_responsibility_tl frt
   WHERE fcr.requested_by = fu.user_id
     AND fcr.concurrent_program_id = fcpt.concurrent_program_id
     AND fcr.program_application_id = fat.application_id 
     AND fcpt.application_id = fat.application_id
     AND fcr.responsibility_id = frt.responsibility_id
--     AND fcr.program_application_id = fcpt.application_id
--     AND NVL(fcr.description, fcpt.user_concurrent_program_name) NOT IN ('Actual Cost Worker', 'OAM Applications Dashboard Collection', 'PO Output for Communication', 'Receiving Transaction Processor', 'Requisition Import', 'Workflow Background Process')
--     AND fcpt.description != 'Request Set Stage Master Program'
--     AND NVL(fcr.description, fcpt.user_concurrent_program_name) IN ('PRC: Update Project Summary Amounts')
--     AND NVL(fcr.description, fcpt.user_concurrent_program_name) LIKE 'IEX%'
--     AND fcr.request_date > '01-JUN-2015'
--     AND fcr.actual_completion_date IS NOT NULL
--     AND fcr.status_code = 'E'
--     AND fu.user_name IN ('SYSADMIN')
--     AND fcr.request_id IN (18971266)
     AND fcr.phase_code = 'R'  -- running
--     AND fcr.status_code = 'E' -- error
--     AND fcr.phase_code != 'P' -- not pending
--     AND fcr.status_code NOT IN ('D','X')                 -- not cancelled (D) or teminated (X)
--     AND fcr.status_code = 'W'
ORDER BY fcr.actual_start_date DESC;

Additional Queries

-- ##############################################################################
--       CONCURRENT REQUEST - PENDING COUNT
-- ############################################################################*/

    SELECT   fcp.concurrent_program_name job
           , NVL(fcr.description, fcpt.user_concurrent_program_name) job
           , COUNT(*) ct
        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
        JOIN applsys.fnd_concurrent_programs fcp     ON fcp.concurrent_program_id =  fcpt.concurrent_program_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
        JOIN applsys.fnd_responsibility_tl frt       ON fcr.responsibility_id =      frt.responsibility_id
   LEFT JOIN applsys.fnd_conc_pp_actions fcpa        ON fcr.request_id =             fcpa.concurrent_request_id AND fcpa.program_application_id = fcp.application_id
       WHERE 1 = 1
         AND fcr.phase_code = 'P'  -- pending
   GROUP BY fcp.concurrent_program_name
          , NVL(fcr.description, fcpt.user_concurrent_program_name)
   ORDER BY 3 DESC
          , 2;

-- ##############################################################################
--         CONCURRENT REQUESTS - Volumes
-- ############################################################################*/

-- Volumes per hour

SELECT   TRUNC(fcr.actual_start_date) run_date
       , TO_CHAR((fcr.actual_start_date), 'HH24') start_
       , COUNT(*) ct
    FROM applsys.fnd_concurrent_requests fcr 
    JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id =  fcpt.concurrent_program_id
--   WHERE NVL(fcr.description, fcpt.user_concurrent_program_name) = 'Compile value set hierarchies'
GROUP BY TRUNC(fcr.actual_start_date)
       , TO_CHAR((fcr.actual_start_date), 'HH24')
ORDER BY 1 DESC, 2;

select user_id from applsys.fnd_user where user_name = 'MEWTSSP2';

-- by day
SELECT   trunc(fcr.request_date) date_
       , to_char (trunc(fcr.request_date), 'Dy') ddd
       , MIN(fcr.actual_start_date) min_start
       , MAX(fcr.actual_completion_date) max_end
       , count(*) job_ct
    FROM applsys.fnd_concurrent_requests fcr
   WHERE fcr.requested_by = 1157
GROUP BY trunc(fcr.request_date)
       , to_char (trunc(fcr.request_date), 'Dy')
ORDER BY trunc(fcr.request_date) DESC;

-- by resp
SELECT   frt.responsibility_name
       , count(*) ct
    FROM apps.fnd_concurrent_requests fcr
    JOIN apps.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.program_application_id = fcpt.application_id 
    JOIN apps.fnd_responsibility_tl frt       ON fcr.responsibility_id =     frt.responsibility_id 
   WHERE fcr.actual_completion_date IS NOT NULL
     AND fcr.actual_completion_date > SYSDATE - 2
GROUP BY frt.responsibility_name
ORDER BY 2 DESC;

-- https://community.oracle.com/thread/2622109
-- Volumes split into 30 minute blocks
WITH limitdata AS
(SELECT   fcr.request_id id
       , fcr.actual_start_date dt
    FROM applsys.fnd_concurrent_requests fcr
    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.actual_start_date BETWEEN TO_DATE('11-MAY-2016 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('11-MAY-2016 07:00:00', 'DD-MON-YYYY HH24:MI:SS')
     AND fcpt.user_concurrent_program_name NOT IN ('PO Output for Communication','Cost Manager','Workflow Background Process','OAM Applications Dashboard Collection','Actual Cost Worker'))
     ,chunks as (select rownum as chnk_no
                    ,0 + (86400/48) * (rownum -1) as secs_from
                    ,0 + (86400/48) * rownum -1 as secs_to
                from dual
               connect by rownum <= 48)
SELECT d
      ,tfrom
      ,tto
      ,COUNT(id) as cntr
  FROM (select  TO_CHAR(x.dt,'DD.MM.YYYY') as d
               ,TO_CHAR(x.dt + c.secs_from/86400,'HH24:MI') as tfrom
               ,TO_CHAR(x.dt + c.secs_to/86400,'HH24:MI') as tto
               ,c.chnk_no
               ,l.id
           from chunks c
                CROSS join (SELECT distinct TRUNC(dt) dt FROM limitdata) x
           left outer join limitdata l ON (    TRUNC(l.dt) = x.dt  
                                           AND to_number(to_char(l.dt,'SSSSS')) BETWEEN c.secs_from and c.secs_to)) x
GROUP by d, tfrom, tto, chnk_no
ORDER BY d, chnk_no;