Useful Application & Database Scripts

Useful Application & Database Scripts

Script Name: Elapsed Time History of Concurrent Requests in a month

Script Details

SELECT pt.user_concurrent_program_name
       user_conc_program_name,
       r.priority,
       Count(*),
       Round(Avg(actual_completion_date - actual_start_date) * 1440 * 60, 2)
       AVGG,
       Round(Max(actual_completion_date - actual_start_date) * 1440 * 60, 2)
       MAXI,
       Round(Min(actual_completion_date - actual_start_date) * 1440 * 60, 2)
       MINI,
       p.concurrent_program_name
       concurrent_program_name
FROM   apps.fnd_concurrent_programs p,
       apps.fnd_concurrent_programs_tl pt,
       apps.fnd_concurrent_requests r
WHERE  r.concurrent_program_id = p.concurrent_program_id
       AND r.program_application_id = p.application_id
       AND r.concurrent_program_id = pt.concurrent_program_id
       AND r.program_application_id = pt.application_id
       AND r.status_code = 'C'
       AND r.actual_start_date IS NOT NULL
       AND To_char(actual_completion_date, 'MON') = '&1'
GROUP  BY pt.user_concurrent_program_name,
          r.priority,
          p.concurrent_program_name
ORDER  BY Count(*) DESC

Script Name: Current Logged on User count in EBS

Script Details

SELECT DISTINCT icx.session_id,

                  icx.user_id,

                  fu.user_name,

                  fu.description

    FROM icx_sessions icx, fnd_user fu

   WHERE     disabled_flag != 'Y'

         AND icx.pseudo_flag = 'N'

         AND (last_connect + 

              DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),

                      NULL, limit_time,

                      0   , limit_time,

                      fnd_profile.VALUE ('ICX_SESSION_TIMEOUT')/60) / 24) > SYSDATE

         AND icx.counter < limit_connects

         AND icx.user_id = fu.user_id;

Will add more..