Useful SQL Queries - Oracle EBS

Query to find Runtime, Status and Argument of a Concurrent Request

  SELECT fcp.user_concurrent_program_name, fcr.requested_by, fcr.request_id,

         fcr.request_date, fcr.phase_code, fcr.status_code,fcr.requested_start_date,

          fcr.actual_start_date,fcr.actual_completion_date,

         ROUND((nvl(fcr.actual_completion_date,sysdate) -fcr.actual_start_date) * 1440, 2)  

         "Runtime (in Minutes)"  ,argument_text

    FROM fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcp

   WHERE fcr.concurrent_program_id = fcp.concurrent_program_id

     AND fcp.user_concurrent_program_name LIKE ('%Accrual Detail Report%')

     AND fcr.requested_by='12345' --User id

ORDER BY fcr.request_date DESC

 

Query to find Responsibility/Request group attached to a Concurrent Program

SELECT DISTINCT fcpl.user_concurrent_program_name,

       fcp.concurrent_program_name,fapp.application_name,

       frg.request_group_name,fnrtl.responsibility_name

  FROM apps.fnd_request_groups frg,

       apps.fnd_application_tl fapp,

       apps.fnd_request_group_units frgu,

       apps.fnd_concurrent_programs fcp,

       apps.fnd_concurrent_programs_tl fcpl,

       apps.fnd_responsibility fnr,

       apps.fnd_responsibility_tl fnrtl

 WHERE frg.application_id = fapp.application_id

   AND frg.application_id = frgu.application_id

   AND frg.request_group_id = frgu.request_group_id

   AND frg.request_group_id = fnr.request_group_id

   AND frg.application_id = fnr.application_id

   AND fnr.responsibility_id = fnrtl.responsibility_id

   AND frgu.request_unit_id = fcp.concurrent_program_id

   AND frgu.unit_application_id = fcp.application_id

   AND fcp.concurrent_program_id = fcpl.concurrent_program_id

   AND fcp.concurrent_program_name LIKE 'XX%'

   AND fapp.application_name IN 'Receivables'

   AND fnrtl.LANGUAGE = 'US'

   AND fapp.LANGUAGE = 'US'

 

Query to find Responsibility attached to a Form

 

SELECT fff.function_name, frv.responsibility_name

  FROM fnd_responsibility_vl frv, fnd_form_functions fff

 WHERE fff.function_name LIKE 'XX%'      --Form Function Name

   AND frv.menu_id IN (SELECT me.menu_id

                         FROM fnd_menu_entries me

                   START WITH me.function_id = fff.function_id

             CONNECT BY PRIOR me.menu_id = me.sub_menu_id)