Query to Find Defaulting Rules in Order Management for specific Attribute

Query to Find Defaulting Rules in Order Management for specific Attribute  like in below case finding the defaulting rules for REQUEST_DATE (Customer_Request_date or Shcedule_Request_date) field


SELECT dadr.DATABASE_OBJECT_NAME,
       dadr.ATTRIBUTE_CODE,
       dac.ATTRIBUTE_CODE,
       dac.DATABASE_OBJECT_NAME,
       aoa.column_name,
       aoa.attribute_application_id,
       aoa.REQUIRED_FLAG,
       DADR.*
  FROM oe_def_attr_def_rules dadr,
       oe_def_attr_condns dac,
       ak_object_attributes aoa
 WHERE     dadr.ATTR_DEF_CONDITION_ID = dac.ATTR_DEF_CONDITION_ID
       AND dac.ENABLED_FLAG = 'Y'
       AND dac.ATTRIBUTE_CODE = aoa.ATTRIBUTE_CODE
       AND dac.DATABASE_OBJECT_NAME = aoa.DATABASE_OBJECT_NAME
       AND UPPER (DADR.SRC_API_FN) LIKE '%REQUEST_DATE%'

Query to find the last run date of a Concurrent Program in Oracle Apps

Query to find the last run date of a Concurrent Program

--input:     Concurrent Program name

SELECT MAX (fcr.REQUEST_DATE)

  FROM fnd_concurrent_requests          fcr,

       fnd_concurrent_programs_tl       fcpt

 WHERE     fcr.concurrent_program_id = fcpt.concurrent_program_id

       AND fcr.program_application_id = fcpt.application_id

       AND fcr.actual_start_date > SYSDATE - :days_to_check

       AND fcpt.user_concurrent_program_name LIKE '%' || :Program_Name || '%'

Query to find the responsibilities which can run the concurrent program in Oracle Apps

Query to find the responsibilities which can run the concurrent program

-- input:     Concurrent Program name


SELECT frv.responsibility_name
  FROM apps.fnd_request_group_units frgu,
       apps.fnd_responsibility_vl frv,
       apps.fnd_concurrent_programs_vl fcpv
 WHERE     frv.REQUEST_GROUP_ID = frgu.REQUEST_GROUP_ID
       AND fcpv.concurrent_program_id = frgu.REQUEST_UNIT_ID
       AND NVL (frv.end_date, SYSDATE + 1) > SYSDATE
       AND EXISTS
              (SELECT 'Y'
                 FROM apps.fnd_user_resp_groups grps
                WHERE     grps.responsibility_id = frv.responsibility_id
                      AND NVL (grps.end_date, SYSDATE + 1) > SYSDATE)
       AND fcpv.user_concurrent_program_name like '%'||:Program_Name||'%'
Order By 1

Concurrent Program - Query to find Status of Concurrent Requests in Oracle Apps

Query will list the details for the concurrent program like 
Concurrent Program name, Arguments, log / output file path and the Status 


   SELECT DISTINCT fcp.user_concurrent_program_name,
                fcp.concurrent_program_name,
                fcr.request_id,
                fcr.request_date,
                flv.meaning status,
                fcr.status_code,
                fcr.completion_text,
                fcr.logfile_name,
                fcr.outfile_name,
                fcr.argument_text
  FROM apps.fnd_concurrent_programs_vl fcp,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_lookup_values          flv
 WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
   AND trunc(fcr.last_update_date) <= trunc(SYSDATE)
   AND flv.lookup_code = fcr.status_code
   AND flv.lookup_type = 'CP_STATUS_CODE'
   AND flv.language = 'US'
   AND fcr.request_id = :P_Request_ID
 ORDER BY fcr.request_date,
          fcr.request_id DESC;

Query to Find Concurrent Program Details using Executable Name in Oracle Apps

--Find Concurrent Program Details using Executable Name 

SELECT fa.application_short_name,
       frg.request_group_name,
       fe.execution_file_name, 
       fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application fa
 WHERE frgu.request_unit_id = fcp.concurrent_program_id
       AND frgu.request_group_id = frg.request_group_id 
       AND fe.executable_id = fcp.executable_id 
       AND FRG.application_id = fa.application_id
       AND fe.executable_name = :P_Executable_Name

  ;

Query to find out Concurrent programs attached to request group in Oracle Apps

Find Out Concurrent Program attache to user

SELECT request_group_name,
       DECODE (request_unit_type,
               'A', 'Application',
               'P', 'Program',
               'S', 'Request Set',
               '-NA-'
              ) request_unit_type,
       user_concurrent_program_name program_name
  FROM fnd_request_groups a,
       fnd_request_group_units b,
       fnd_concurrent_programs_vl c
 WHERE a.request_group_id = b.request_group_id
   AND b.request_unit_id = c.concurrent_program_id
   AND (c.CONCURRENT_PROGRAM_NAME = :P_Conc_Prog_Short_Name
   OR request_group_name = 'OM Concurrent Programs')
   AND request_unit_type = 'P'
;