从form取数据

1.知道FORM名称，查询出具体挂在什么职责下，要求通过查询结果可以一步步展开对应职责直接找到该FORM，输入条件为FORM名称

--1
SELECT form.form_name,
       --form.form_id,
       --func.function_id,
       --func.function_name,
       --menu.MENU_ID,
       menu.PROMPT,
       resp.RESPONSIBILITY_NAME
  FROM fnd_form_vl form
  JOIN fnd_form_functions_vl func
    ON form.form_id = func.form_id
  JOIN fnd_menu_entries_vl menu
    ON menu.function_id = func.FUNCTION_ID
    join fnd_responsibility_vl resp
    on resp.MENU_ID = menu.MENU_ID
 WHERE upper(form_name) = 'CUXAMFINE';

2.并发程序可以在什么地方进行提交。查询结果要求  ：并发程序名称，简称，职责，可执行，程序包  等
输入条件可以是 并发程序名称、数据库程序包

--2
 --根据程序名称查询
 SELECT pro.user_concurrent_program_name 程序名称,
        pro.concurrent_program_name      简称,
        --pro.executable_id,
        exec.execution_file_name 程序包,
        exec.executable_name     可执行,
        --groups.request_group_id,
        resp.responsibility_name 职责
   FROM fnd_concurrent_programs_vl pro
   JOIN fnd_executables_form_v exec
     ON exec.executable_id = pro.executable_id
   JOIN fnd_request_group_units group_units
     ON group_units.request_unit_id = pro.concurrent_program_id
   JOIN fnd_request_groups groups
     ON groups.request_group_id = group_units.request_group_id
   JOIN fnd_responsibility_vl resp
     ON resp.request_group_id = groups.request_group_id
  WHERE  pro.user_concurrent_program_name = 'Task Purge Program';
 --根据程序包查询
SELECT pro.user_concurrent_program_name 程序名称,
       pro.concurrent_program_name      简称,
       --pro.executable_id,
       exec.execution_file_name 程序包,
       exec.executable_name     可执行,
       --groups.request_group_id,
       resp.responsibility_name 职责
  FROM fnd_concurrent_programs_vl pro
  JOIN fnd_executables_form_v exec
    ON exec.executable_id = pro.executable_id
  JOIN fnd_request_group_units group_units
    ON group_units.request_unit_id = pro.concurrent_program_id
  JOIN fnd_request_groups groups
    ON groups.request_group_id = group_units.request_group_id
  JOIN fnd_responsibility_vl resp
    ON resp.request_group_id = groups.request_group_id
   AND exec.execution_file_name = 'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS';

3.后台查询请求运行历史。请求起止运行时间，请求运行时间，请求号码，参数，请求阶段、状态，提交职责，提交人

--3
SELECT  req_sum.requested_start_date,
        req_sum.actual_completion_date,
        req_sum.actual_completion_date - req_sum.requested_start_date runtime,
       --requested_by,
       --req_sum.responsibility_id,
       req_sum.request_id,
       req_sum.ARGUMENT_TEXT,
       req_sum.PHASE_CODE,
       req_sum.status_code,
       req_sum.requestor,
       resp.RESPONSIBILITY_NAME
  FROM fnd_conc_req_summary_v req_sum, 
       fnd_responsibility_vl resp
  where req_sum.RESPONSIBILITY_ID = resp.RESPONSIBILITY_ID;

4.查询用户对应的职责，用户、用户说明、职责、职责分配起止日期。

--4
SELECT fnd_user.user_name,
       fnd_user.description,
       resp.responsibility_name,
       indirect.start_date,
       indirect.end_date
  FROM fnd_user_resp_groups_indirect indirect,
       fnd_user,
       fnd_responsibility_vl         resp
 WHERE indirect.user_id = fnd_user.user_id
   AND resp.responsibility_id = indirect.responsibility_id
 ORDER BY fnd_user.user_name;

5.查询关键性弹性域结构
关键性弹性域表，标题，代码，结构，结构代码，段编号，段说明，对应的数据库字段，值集等。

--5
SELECT flex.application_table_name, --表名
       flex.id_flex_name, --标题
       flex.id_flex_code, --代码
       flex.set_defining_column_name ,-- 结构列
       stru.id_flex_structure_code, --结构代码
       segments.segment_num, --段编号
       segments.segment_name, --段说明
       segments.application_column_name, --对应的数据库字段
      --segments.flex_value_set_id ,--值集id
      value_set.flex_value_set_name --值集
  FROM fnd_id_flexs              flex,
       fnd_id_flex_structures_vl stru,
       fnd_id_flex_segments_vl   segments,
       fnd_flex_value_sets value_set
 WHERE flex.id_flex_code = stru.id_flex_code
   AND segments.id_flex_num = stru.id_flex_num
   and segments.ID_FLEX_CODE = stru.ID_FLEX_CODE
   and value_set.flex_value_set_id = segments.FLEX_VALUE_SET_ID
   AND flex.id_flex_code = 'GLLE'
   order by stru.id_flex_structure_code,segments.segment_num;

6.查询说明性弹性域
表、标题、上下文、段编号、段说明、对应的数据库字段、值集等

--6
SELECT desc_flex.title, --标题
       desc_flex.application_table_name, --表
       desc_flex.form_context_prompt, --上下文
       col_usage.column_seq_num, --段编号
       col_usage.end_user_column_name, --段说明
       col_usage.application_column_name, --对应的数据库字段
       --col_usage.flex_value_set_id ,--值集id
       value_set.flex_value_set_name --值集
  FROM fnd_descriptive_flexs_vl    desc_flex,
       fnd_descr_flex_col_usage_vl col_usage,
       fnd_flex_value_sets         value_set
 WHERE desc_flex.descriptive_flexfield_name = col_usage.descriptive_flexfield_name
   AND value_set.flex_value_set_id = col_usage.flex_value_set_id
   AND desc_flex.descriptive_flexfield_name LIKE 'BATCH_USSGL_TRANSACTION_CODE';

7.查询系统锁住的对象，SESSION_ID、对象名称等

--7
SELECT o.object_name,
       s.machine,
       s.sid,
       s.serial#
  FROM v$locked_object l,
       dba_objects     o,
       v$session       s
 WHERE l.object_id　 = 　o.object_id
   AND l.session_id = s.sid;

8.根据请求ID，查询请求当前正在运行的SQL

--8
SELECT sql_text
  FROM v$sqltext_with_newlines
 WHERE hash_value IN (SELECT sql_hash_value
                        FROM v$session
                       WHERE paddr IN (SELECT addr
                                         FROM v$process
                                        WHERE spid = (SELECT b.oracle_process_id
                                                        FROM apps.fnd_concurrent_requests b
                                                       WHERE b.request_id = &req_number)))
 ORDER BY piece;

9.根据SESSION_ID，查询SESSION正在运行的SQL

SELECT sql_text
  FROM v$sqltext_with_newlines
 WHERE hash_value IN (SELECT sql_hash_value
                        FROM v$session sess
                       WHERE sess.SID = &session_id);