SQL Query to find Oracle Apps Concurrent Request Details

/*Query to find Error/Concurrent pragrams and their details based on date parameter*/
select fcr.request_id,
       fcp.USER_CONCURRENT_PROGRAM_NAME,
       flv.meaning,
      round((nvl(FCR.actual_completion_date,Sysdate) - FCR.actual_start_date)* 24 *60,2)  "TIME TAKEN SO FAR" ,
      to_char(fcr.actual_start_date,'DD-MON-RRRR HH24:MI:SS') start_date,
       to_char(fcr.actual_completion_date,'DD-MON-RRRR HH24:MI:SS') end_date,
      fcr.argument_text ,
       (select fu.user_name
          from apps.fnd_user fu
         where fu.user_id = fcr.requested_by) user_name,      
       fcr.status_code,
       (select fa.application_short_name
          from apps.fnd_application fa
         where fa.application_id = fcp.application_id) application_name,
       (select fr.responsibility_key from apps.fnd_responsibility fr where fr.responsibility_id = fcr.responsibility_id) responsibility_name,
       fcr.request_date,
       fcr.completion_text    
  from apps.fnd_concurrent_requests    fcr,
       apps.fnd_concurrent_programs_tl fcp,
       apps.fnd_lookup_values          flv
 where trunc(fcr.actual_start_date) >= trunc(sysdate-2) --Parameter
       and fcr.status_code in ('C','R','E','G','Q','W','X') and
       fcr.concurrent_program_id = fcp.concurrent_program_id and  
       fcp.language = 'US' and flv.language = 'US' and
       flv.lookup_code = fcr.status_code AND
       flv.lookup_type = 'CP_STATUS_CODE' and
       flv.start_date_active is not null
       and       fcp.USER_CONCURRENT_PROGRAM_NAME like 'GL Archive and Purge'
order by fcr.actual_start_date desc;

 SELECT fcp.concurrent_program_name "Prog Short Name",
  fcpt.user_concurrent_program_name "Program Name"   ,
  fcpt.description "Program desc"                    ,
  frg.request_group_name "Request Group"             ,
  frg.description "Request group desc"               ,
  frt.responsibility_name "Responsibility Name"      ,
  frt.description "Responsibility desc"
   FROM apps.fnd_responsibility fr    ,
  apps.fnd_responsibility_tl frt      ,
  apps.fnd_request_groups frg         ,
  apps.fnd_request_group_units frgu   ,
  apps.fnd_concurrent_programs_tl fcpt,
  apps.fnd_concurrent_programs fcp
  WHERE frt.responsibility_id  = fr.responsibility_id
AND fr.request_group_id        = frg.request_group_id
AND frg.request_group_id       = frgu.request_group_id
AND frgu.request_unit_id       = fcpt.concurrent_program_id
AND fcpt.concurrent_program_id = fcp.concurrent_program_id
AND frgu.request_unit_type     = 'P'
AND fcpt.user_concurrent_program_name LIKE '%Cross-Validation Rules Listing%'

SELECT

  frsv.user_request_set_name "Request Set Name"

, frsv.description "Request Set desc"

, frg.request_group_name "Request Group Name"

, frg.description "Request group desc"

, frt.responsibility_name "Responsibility Name"

, frt.description "Responsibility desc"

FROM

  fnd_responsibility fr

, fnd_responsibility_tl frt

, fnd_request_groups frg

, fnd_request_group_units frgu

, fnd_request_sets_vl frsv

WHERE

  frt.responsibility_id        = fr.responsibility_id

AND fr.request_group_id        = frg.request_group_id

AND frg.request_group_id       = frgu.request_group_id

AND frgu.request_unit_id       = frsv.request_set_id

AND frgu.request_unit_type     = 'S'

AND frsv.user_request_set_name =

  'GL Daily and Periodic Rates Import Request Set' 

/*Query to find Concurrent program details and request set*/

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 FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE ' Non Cash and TRecs Request Set'

AND FNRTL.LANGUAGE = 'US'

AND FAPP.LANGUAGE  = 'US';

/*Query to find Responsibility and Group Attached to concurrent request*/

SELECT

  frt.responsibility_name

, frg.request_group_name

, frg.description

FROM

  fnd_request_groups frg

,fnd_request_group_units frgu

,fnd_concurrent_programs fcp

,fnd_concurrent_programs_tl fcpt

,fnd_responsibility_tl frt

,fnd_responsibility frs

WHERE

  frgu.unit_application_id    = fcp.application_id

AND frgu.request_unit_id      = fcp.concurrent_program_id

AND frg.request_group_id      = frgu.request_group_id

AND frg.application_id        = frgu.application_id

AND fcpt.source_lang          = USERENV('LANG')

AND fcp.application_id        = fcpt.application_id

AND fcp.concurrent_program_id = fcpt.concurrent_program_id

AND frs.application_id        = frt.application_id

AND frs.responsibility_id     = frt.responsibility_id

AND frt.source_lang           = USERENV('LANG')

AND frs.request_group_id      = frg.request_group_id

AND frs.application_id        = frg.application_id

  -- AND   fcp.concurrent_program_name = <shortname>

AND fcpt.user_concurrent_program_name LIKE

  '%US AMERITAS Outbound Interface Program%'

AND frt.responsibility_name LIKE 'US Fin%';