Query to get Profile values for all the Levels in Oracle Apps R12

SELECT   SUBSTR (e.profile_option_name, 1, 25) internal_name,
         SUBSTR (pot.user_profile_option_name, 1, 60) name_in_forms,
         DECODE (a.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Resp',
                 10004, 'User',
                 10005, 'Server',
                 10007, 'Server + Resp',
                 a.level_id
                ) levell,
         DECODE (a.level_id,
                 10001, 'Site',
                 10002, c.application_short_name,
                 10003, b.responsibility_name,
                 10004, d.user_name,
                 10005, n.node_name,
                 10007, m.node_name || ' + ' || b.responsibility_name,
                 a.level_id
                ) level_value,
         NVL (a.profile_option_value, 'Is Null') VALUE,
         TO_CHAR (a.last_update_date, 'DD-MON-YYYY HH24:MI') last_update_date,
         dd.user_name last_update_user
    FROM fnd_profile_option_values a,
         fnd_responsibility_tl b,
         fnd_application c,
         fnd_user d,
         fnd_profile_options e,
         fnd_nodes n,
         fnd_nodes m,
         fnd_responsibility_tl x,
         fnd_user dd,
         fnd_profile_options_tl pot
   WHERE e.profile_option_name LIKE 'MFG_ORGANIZATION_ID'
     AND e.profile_option_name = pot.profile_option_name(+)
     AND e.profile_option_id = a.profile_option_id(+)
     AND a.level_value = b.responsibility_id(+)
     AND a.level_value = c.application_id(+)
     AND a.level_value = d.user_id(+)
     AND a.level_value = n.node_id(+)
     AND a.level_value_application_id = x.responsibility_id(+)
     AND a.level_value2 = m.node_id(+)
     AND a.last_updated_by = dd.user_id(+)
     AND pot.LANGUAGE = 'US'
ORDER BY e.profile_option_name

Queries to get Oracle Form details in Oracle Apps R12

-- Starting of Script for form and its assigned responsibility

SELECT Ffv.TYPE
      ,ff.form_name
      ,Ffv.Function_Name
      ,Ffv.User_Function_Name
      ,Ffv.Description    
      --,fm.menu_name
      ,frl.responsibility_name
      ,Fme.entry_sequence
      ,fme.prompt    
FROM   Fnd_Form_Functions_Vl Ffv
      ,fnd_form ff
      ,Fnd_Menu_Entries_Vl Fme    
      ,fnd_menus fm
      ,fnd_responsibility fr
      ,fnd_responsibility_tl frl
WHERE  ff.form_id            = ffv.form_id
AND    fme.function_id       = ffv.function_id
AND    fm.menu_id            = fme.menu_id
AND    fr.menu_id            = fme.menu_id
AND    frl.responsibility_id = fr.responsibility_id
AND    ff.form_Name          = :form_name;

-- Starting of Script for form personalizations details and its assigned responsibility
SELECT ffr.form_name
      ,ffr.function_name
      ,ffr.description
      ,ffr.sequence
      ,ffr.trigger_event
      ,ffr.trigger_object
      ,ffr.enabled
      ,ffa.SEQUENCE action_seq
      ,ffa.action_type
      ,ffa.SUMMARY action_desc
      ,ffa.enabled action_enabled
      ,ffa.object_type
      ,ffa.target_object
      ,ffa.property_name
      ,ffa.property_value
FROM   fnd_form_custom_rules ffr
      ,fnd_form_custom_actions ffa
WHERE  ffr.ID = ffa.rule_id
and    ffr.form_name = :form_name;

Query to get Patches and Application Install details in Oracle Apps R12

---------------------------------
SELECT a.application_name,
       DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
       patch_level, a.application_id
  FROM fnd_application_vl a, fnd_product_installations b
 WHERE a.application_id = b.application_id;

---------------------------------
 SELECT   patch_name, patch_type, maint_pack_level, creation_date
    FROM ad_applied_patches
ORDER BY creation_date DESC;

---------------------------------

SELECT aru_release_name || '.' || minor_version || '.'
       || tape_version VERSION,
       start_date_active updated, row_source_comments "how it is done",
       base_release_flag "Base version"
  FROM ad_releases
 WHERE end_date_active IS NULL
---------------------------------
SELECT a.applied_patch_id, a.patch_name, a.patch_type, b.patch_drvier_id,
       b.driver_file_name, b.orig_patch_name, b.creation_date, b.platform,
       b.source_code, b.creationg_date, b.file_size, b.merged_driver_flag,
       b.merge_date
  FROM ad_applied_patches a, ad_patch_drivers b
 WHERE a.applied_patch_id = b.applied_patch_id
   AND a.patch_name = :PATCH_NUMBER

Get Concurrent Request Set Details in Oracle Apps R12

SELECT   rs.user_request_set_name "Request Set", rss.display_sequence seq,
         cp.user_concurrent_program_name "Concurrent Program",
         e.executable_name, e.execution_file_name, lv.meaning file_type,
         fat.application_name "Application Name"
FROM     fnd_request_sets_vl rs,
         fnd_req_set_stages_form_v rss,
         fnd_request_set_programs rsp,
         fnd_concurrent_programs_vl cp,
         fnd_executables e,
         fnd_lookup_values lv,
         fnd_application_tl fat
   WHERE 1 = 1
     AND rs.application_id = rss.set_application_id
     AND rs.request_set_id = rss.request_set_id
     AND rs.user_request_set_name     = :p_request_set_name
     AND e.application_id = fat.application_id
     AND rss.set_application_id = rsp.set_application_id
     AND rss.request_set_id = rsp.request_set_id
     AND rss.request_set_stage_id = rsp.request_set_stage_id
     AND rsp.program_application_id = cp.application_id
     AND rsp.concurrent_program_id = cp.concurrent_program_id
     AND cp.executable_id = e.executable_id
     AND cp.executable_application_id = e.application_id
     AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
     AND lv.lookup_code = e.execution_method_code
     AND lv.LANGUAGE = 'US'
     AND fat.LANGUAGE = 'US'
     AND rs.end_date_active IS NULL
ORDER BY 1, 2;


-- Starting of Script to find the request set assigned to a responsibility

SELECT frt.responsibility_name
      ,fcpt.user_request_set_name
      ,frst.request_set_stage_id
      ,frst.user_stage_name
FROM   apps.fnd_Responsibility fr
      ,apps.fnd_responsibility_tl frt
      ,apps.fnd_request_groups frg
      ,apps.fnd_request_group_units frgu
      ,apps.fnd_request_Sets_tl fcpt
      ,fnd_request_set_stages_tl frst
WHERE  frt.responsibility_id = fr.responsibility_id
AND    frg.request_group_id = fr.request_group_id
AND    frgu.request_group_id = frg.request_group_id
AND    fcpt.request_set_id = frgu.request_unit_id
AND    frst.request_set_id = fcpt.request_set_id
AND    frst.LANGUAGE = fcpt.LANGUAGE
AND    frt.LANGUAGE = USERENV('LANG')
AND    fcpt.LANGUAGE = USERENV('LANG')
AND    fcpt.user_request_set_name = :request_set_name
ORDER BY frt.responsibility_name
        ,frst.request_set_stage_id
        ,fcpt.user_request_set_name
        ,frst.user_stage_name

How to Kill the Session in Oracle

SELECT SID, SERIAL#, STATUS  FROM V$SESSION
----------------------------------------------
                1        17         INACTIVE
                5        566       INACTIVE
                9        55         ACTIVE

ALTER  SYSTEM KILL SESSION 'sid,serial#'

ALTER  SYSTEM KILL SESSION '1,17'
/

ALTER  SYSTEM KILL SESSION '5,566'
/

Query to get Trace File details of a Concurrent Request in Oracle Apps R12

SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_process_id,
       'Trace Flag: ' || req.enable_trace,
          'Trace Name:
'
       || dest.VALUE
       || '/'
       || LOWER (dbnm.VALUE)
       || '_ora_'
       || oracle_process_id
       || '.trc',
       'Prog. Name: ' || prog.user_concurrent_program_name,
       'File Name: ' || execname.execution_file_name
       || execname.subroutine_name,
          'Status : '
       || DECODE (phase_code, 'R', 'Running')
       || '-'
       || DECODE (status_code, 'R', 'Normal'),
       'SID Serial: ' || ses.SID || ',' || ses.serial#,
       'Module : ' || ses.module
  FROM fnd_concurrent_requests req,
       v$session ses,
       v$process proc,
       v$parameter dest,
       v$parameter dbnm,
       fnd_concurrent_programs_vl prog,
       fnd_executables execname
 WHERE  req.request_id = :REQUEST_ID
   AND req.oracle_process_id = proc.spid(+)
   AND proc.addr = ses.paddr(+)
   AND dest.NAME = 'user_dump_dest'
   AND dbnm.NAME = 'db_name'
   AND req.concurrent_program_id = prog.concurrent_program_id
   AND req.program_application_id = prog.application_id
   AND prog.application_id = execname.application_id

Check who are all logged into the instance Today in Oracle Apps R12

SELECT   fu.user_name "User Name", papf.full_name "Employee Name",
         NVL (papf.email_address, fu.email_address) "Email Address"
    FROM per_all_people_f papf, fnd_user fu, fnd_logins fl
   WHERE TRUNC (fl.start_time) = TRUNC (SYSDATE)
     AND fu.user_id = fl.user_id
     AND papf.person_id(+) = fu.employee_id
     AND fu.user_name NOT IN ('INTERFACE', 'SYSADMIN', 'GUEST')
GROUP BY fu.user_name,
         papf.full_name,
         NVL (papf.email_address, fu.email_address)
ORDER BY fu.user_name