據(jù))
1.知道FORM名稱查詢出具體掛在什么職責(zé)下要求通過查詢結(jié)果可以一步步展開對應(yīng)職責(zé)直接找到該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.并發(fā)程序可以在什么地方進(jìn)行提交。查詢結(jié)果要求 并發(fā)程序名稱簡稱職責(zé)可執(zhí)行程序包 等輸入條件可以是 并發(fā)程序名稱、數(shù)據(jù)庫程序包--2 --根據(jù)程序名稱查詢 SELECT PRO.USER_CONCURRENT_PROGRAM_NAME 程序名稱, PRO.CONCURRENT_PROGRAM_NAME 簡稱, --pro.executable_id, EXEC.EXECUTION_FILE_NAME 程序包, EXEC.EXECUTABLE_NAME 可執(zhí)行, --groups.request_group_id, RESP.RESPONSIBILITY_NAME 職責(zé) FROM FND_CONCURRENT_PROGRAMS_VL PRO JOIN FND_EXECUTABLES_FORM_V EXEC ON EXEC.EXECUTABLE_ID PRO.EXECUTABLE_ID LEFT JOIN FND_REQUEST_GROUP_UNITS GROUP_UNITS ON GROUP_UNITS.REQUEST_UNIT_ID PRO.CONCURRENT_PROGRAM_ID LEFT JOIN FND_REQUEST_GROUPS GROUPS ON GROUPS.REQUEST_GROUP_ID GROUP_UNITS.REQUEST_GROUP_ID LEFT JOIN FND_RESPONSIBILITY_VL RESP ON RESP.REQUEST_GROUP_ID GROUPS.REQUEST_GROUP_ID WHERE UPPER(PRO.USER_CONCURRENT_PROGRAM_NAME) LIKE CUX%; --根據(jù)程序包查詢 SELECT PRO.USER_CONCURRENT_PROGRAM_NAME 程序名稱, PRO.CONCURRENT_PROGRAM_NAME 簡稱, --pro.executable_id, EXEC.EXECUTION_FILE_NAME 程序包, EXEC.EXECUTABLE_NAME 可執(zhí)行, --groups.request_group_id, RESP.RESPONSIBILITY_NAME 職責(zé) FROM FND_CONCURRENT_PROGRAMS_VL PRO JOIN FND_EXECUTABLES_FORM_V EXEC ON EXEC.EXECUTABLE_ID PRO.EXECUTABLE_ID LEFT JOIN FND_REQUEST_GROUP_UNITS GROUP_UNITS ON GROUP_UNITS.REQUEST_UNIT_ID PRO.CONCURRENT_PROGRAM_ID LEFT JOIN FND_REQUEST_GROUPS GROUPS ON GROUPS.REQUEST_GROUP_ID GROUP_UNITS.REQUEST_GROUP_ID LEFT JOIN FND_RESPONSIBILITY_VL RESP ON RESP.REQUEST_GROUP_ID GROUPS.REQUEST_GROUP_ID WHERE UPPER(EXEC.EXECUTION_FILE_NAME) LIKE CUX_WIP_UPDATE_COST_PKG%;3.后臺查詢請求運行歷史。請求起止運行時間請求運行時間請求號碼參數(shù)請求階段、狀態(tài)提交職責(zé)提交人--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.查詢用戶對應(yīng)的職責(zé)用戶、用戶說明、職責(zé)、職責(zé)分配起止日期。--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.查詢關(guān)鍵性彈性域結(jié)構(gòu)關(guān)鍵性彈性域表標(biāo)題代碼結(jié)構(gòu)結(jié)構(gòu)代碼段編號段說明對應(yīng)的數(shù)據(jù)庫字段值集等。--5 SELECT flex.application_table_name, --表名 flex.id_flex_name, --標(biāo)題 flex.id_flex_code, --代碼 flex.set_defining_column_name ,-- 結(jié)構(gòu)列 stru.id_flex_structure_code, --結(jié)構(gòu)代碼 segments.segment_num, --段編號 segments.segment_name, --段說明 segments.application_column_name, --對應(yīng)的數(shù)據(jù)庫字段 --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.查詢說明性彈性域表、標(biāo)題、上下文、段編號、段說明、對應(yīng)的數(shù)據(jù)庫字段、值集等--6 SELECT desc_flex.title, --標(biāo)題 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, --對應(yīng)的數(shù)據(jù)庫字段 --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.查詢系統(tǒng)鎖住的對象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.根據(jù)請求ID查詢請求當(dāng)前正在運行的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.根據(jù)SESSION_ID查詢SESSION正在運行的SQLSELECT sql_text FROM v$sqltext_with_newlines WHERE hash_value IN (SELECT sql_hash_value FROM v$session sess WHERE sess.SID session_id); 從FORM取數(shù)據(jù)
1.知道FORM名稱查詢出具體掛在什么職責(zé)下要求通過查詢結(jié)果可以一步步展開對應(yīng)職責(zé)直接找到該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.并發(fā)程序可以在什么地方進(jìn)行提交。查詢結(jié)果要求 并發(fā)程序名稱簡稱職責(zé)可執(zhí)行程序包 等輸入條件可以是 并發(fā)程序名稱、數(shù)據(jù)庫程序包--2 --根據(jù)程序名稱查詢 SELECT PRO.USER_CONCURRENT_PROGRAM_NAME 程序名稱, PRO.CONCURRENT_PROGRAM_NAME 簡稱, --pro.executable_id, EXEC.EXECUTION_FILE_NAME 程序包, EXEC.EXECUTABLE_NAME 可執(zhí)行, --groups.request_group_id, RESP.RESPONSIBILITY_NAME 職責(zé) FROM FND_CONCURRENT_PROGRAMS_VL PRO JOIN FND_EXECUTABLES_FORM_V EXEC ON EXEC.EXECUTABLE_ID PRO.EXECUTABLE_ID LEFT JOIN FND_REQUEST_GROUP_UNITS GROUP_UNITS ON GROUP_UNITS.REQUEST_UNIT_ID PRO.CONCURRENT_PROGRAM_ID LEFT JOIN FND_REQUEST_GROUPS GROUPS ON GROUPS.REQUEST_GROUP_ID GROUP_UNITS.REQUEST_GROUP_ID LEFT JOIN FND_RESPONSIBILITY_VL RESP ON RESP.REQUEST_GROUP_ID GROUPS.REQUEST_GROUP_ID WHERE UPPER(PRO.USER_CONCURRENT_PROGRAM_NAME) LIKE CUX%; --根據(jù)程序包查詢 SELECT PRO.USER_CONCURRENT_PROGRAM_NAME 程序名稱, PRO.CONCURRENT_PROGRAM_NAME 簡稱, --pro.executable_id, EXEC.EXECUTION_FILE_NAME 程序包, EXEC.EXECUTABLE_NAME 可執(zhí)行, --groups.request_group_id, RESP.RESPONSIBILITY_NAME 職責(zé) FROM FND_CONCURRENT_PROGRAMS_VL PRO JOIN FND_EXECUTABLES_FORM_V EXEC ON EXEC.EXECUTABLE_ID PRO.EXECUTABLE_ID LEFT JOIN FND_REQUEST_GROUP_UNITS GROUP_UNITS ON GROUP_UNITS.REQUEST_UNIT_ID PRO.CONCURRENT_PROGRAM_ID LEFT JOIN FND_REQUEST_GROUPS GROUPS ON GROUPS.REQUEST_GROUP_ID GROUP_UNITS.REQUEST_GROUP_ID LEFT JOIN FND_RESPONSIBILITY_VL RESP ON RESP.REQUEST_GROUP_ID GROUPS.REQUEST_GROUP_ID WHERE UPPER(EXEC.EXECUTION_FILE_NAME) LIKE CUX_WIP_UPDATE_COST_PKG%;3.后臺查詢請求運行歷史。請求起止運行時間請求運行時間請求號碼參數(shù)請求階段、狀態(tài)提交職責(zé)提交人--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.查詢用戶對應(yīng)的職責(zé)用戶、用戶說明、職責(zé)、職責(zé)分配起止日期。--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.查詢關(guān)鍵性彈性域結(jié)構(gòu)關(guān)鍵性彈性域表標(biāo)題代碼結(jié)構(gòu)結(jié)構(gòu)代碼段編號段說明對應(yīng)的數(shù)據(jù)庫字段值集等。--5 SELECT flex.application_table_name, --表名 flex.id_flex_name, --標(biāo)題 flex.id_flex_code, --代碼 flex.set_defining_column_name ,-- 結(jié)構(gòu)列 stru.id_flex_structure_code, --結(jié)構(gòu)代碼 segments.segment_num, --段編號 segments.segment_name, --段說明 segments.application_column_name, --對應(yīng)的數(shù)據(jù)庫字段 --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.查詢說明性彈性域表、標(biāo)題、上下文、段編號、段說明、對應(yīng)的數(shù)據(jù)庫字段、值集等--6 SELECT desc_flex.title, --標(biāo)題 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, --對應(yīng)的數(shù)據(jù)庫字段 --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.查詢系統(tǒng)鎖住的對象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.根據(jù)請求ID查詢請求當(dāng)前正在運行的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.根據(jù)SESSION_ID查詢SESSION正在運行的SQLSELECT sql_text FROM v$sqltext_with_newlines WHERE hash_value IN (SELECT sql_hash_value FROM v$session sess WHERE sess.SID session_id);