Scripts to find Monthly, Weekly & daily Concurrent Programs submitted in EBS

Below are some useful Scripts to find Monthly, Weekly & daily Concurrent Programs submitted in EBS. The scripts can be used to analyze the different types of requests submitted by different users. We have  found these scripts very useful in overall improving the performance of the Oracle Concurrent Programs in our Oracle EBS Production system.

1) It helped us identify the users who submit same programs at different timings
2) In some case we changed the execution times of certain concurrent programs.
3) Subsequently even the purging programs started to run much quicker.

Script to find Monthly, Weekly and daily concurrent programs submitted in EBS R12

SELECT concurrent_program_id,
  CONCURRENT_PROGRAM_NAME,
  USER_CONCURRENT_PROGRAM_NAME,
  APPLICATION_NAME,
  (LISTAGG(MODULE, '/') WITHIN GROUP (
ORDER BY MODULE)) Module_Name,
  SUM(count_r) Total_Requests_monthly,
  ROUND(SUM(count_r)*7/30) Weekly_Avg,
  ROUND(SUM(count_r)/30) Daily_Avg
FROM
  (SELECT r_s.MODULE_NAME1 module,
    r_s.*
  FROM
    (SELECT prg.concurrent_program_id,
      prg.concurrent_program_name ,
      prg.user_concurrent_program_name,
      app.application_name,
      upper(res_app.application_name) Module_name1 ,
      COUNT(1) count_r
    FROM FND_CONCURRENT_PROGRAMS_VL prg ,
      fnd_concurrent_requests req ,
      fnd_user fu ,
      fnd_Application_Tl app ,
      Fnd_Responsibility_Tl res ,
      fnd_Application_Tl res_app
    WHERE 1                      =1
    AND req.actual_start_date       < '14-JUL-2015' 
    AND req.actual_start_date + 30  > '14-JUL-2015' 
    AND prg.concurrent_program_id=req.concurrent_program_id
    AND req.requested_by         =fu.user_id
    AND app.application_id       =prg.application_id
    AND req.responsibility_id    =res.responsibility_id
    AND res_app.application_id   =res.application_id
    GROUP BY prg.concurrent_program_id,
      prg.concurrent_program_name ,
      prg.user_concurrent_program_name,
      app.application_name,
      upper(res_app.application_name)
    ) r_s
  )
GROUP BY concurrent_program_id,
  CONCURRENT_PROGRAM_NAME,
  USER_CONCURRENT_PROGRAM_NAME,
  APPLICATION_NAME
ORDER BY SUM(COUNT_R) DESC

Id	Name	User program name	Application	Monthly	Weekly	Daily
10	XLAGLTRN	Transfer Journal Entries to GL	Subledger Accounting	9225	2153	308
11	ALECTC	Check Event Alert	Alert	9181	2142	306
12	APMACR	Mass Additions Create	Payables	6558	1530	219

 

Script to find total count of concurrent request submitted by each user on a weekly basis

select  REQUESTED_BY
, USER_NAME
, SUM(decode (Week1, '1', NVL(val,-1), 0)) Day1
, SUM(decode (Week1, '2', NVL(val,-1), 0)) Day2
, SUM(decode (Week1, '3', NVL(val,-1), 0)) Day3       
, SUM(decode (Week1, '4', NVL(val,-1), 0)) Day4
, SUM(decode (Week1, '5', NVL(val,-1), 0)) Day5     
from                                   
(                                      
        SELECT req.requested_by
        ,fu.user_name
        ,ceil(to_char(to_Date(req.requested_start_date,'DD-MON-RRRR'),'DD')/7) Week1
        ,COUNT(1) val
        FROM fnd_concurrent_requests req
        ,fnd_user fu
        WHERE to_Date(req.requested_start_date,'DD-MON-RRRR') BETWEEN to_Date('01-'||:Mon||'-2015','DD-MON-RRRR')
        AND LAST_DAY(to_Date('01-'||:Mon||'-2015','DD-MON-RRRR'))
        AND req.requested_by=fu.user_id


        GROUP BY req.requested_by
        ,fu.user_name
        ,ceil(to_char(to_Date(req.requested_start_date,'DD-MON-RRRR'),'DD')/7)
)  Data1
group by REQUESTED_BY
, USER_NAME
ORDER BY 2

REQUESTED_BY	USER_NAME	Week1	Week2	Week3
4377	User1	24	24	0
4408	User2	2	0	0
2365	User3	4	2	1
3510	User4	2	0	0
4781	User5	11	10	4