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)
 
  • November 1, 2014 | 23 views