Query to see - One particular concurrent program is attached to which all responsibilities

SELECT * FROM Apps.Fnd_responsibility_tl WHERE Responsibility_id IN (SELECT Responsibility_id FROM Apps.Fnd_responsibility WHERE (Request_group_id, Application_id) IN (SELECT Request_group_id, Application_id FROM Apps.Fnd_request_groups Frg WHERE Request_group_id IN (SELECT Request_group_id FROM Apps. Fnd_request_group_units WHERE Request_unit_id IN (SELECT Concurrent_program_id FROM Apps. Fnd_concurrent_programs_vl WHERE Queue_control_flag = 'N' AND User_concurrent_program_name LIKE '%Bridge%Item%'))))

SQL to find out the scheduled concurrent programs

SELECT fcr.request_id
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
, CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE
'n/a'
END set_days_of_week
, fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text "PARAMETERS"
, '------>' dates
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
, TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
, TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
, '------>' holds
, DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
, CASE
WHEN fcr.hold_flag = 'Y'
THEN SUBSTR(
u2.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = 'Y'
THEN fcr.last_update_date
END last_update_date
, '------>' prints
, fcr.number_of_copies print_count
, fcr.printer
, fcr.print_style
, '------>' schedule
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE
'n/a'
END run_once
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
'Days of week: ' ||
decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
end days_of_week
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 1, 31),'1',1) > 0 THEN
'Set Days of Month: ' ||
decode(substr(fcrc.class_info, 1, 1), '1', '1st, ') ||
decode(substr(fcrc.class_info, 2, 1), '1', '2nd, ') ||
decode(substr(fcrc.class_info, 3, 1), '1', '3rd, ') ||
decode(substr(fcrc.class_info, 4, 1), '1', '4th, ') ||
decode(substr(fcrc.class_info, 5, 1), '1', '5th, ') ||
decode(substr(fcrc.class_info, 6, 1), '1', '6th, ') ||
decode(substr(fcrc.class_info, 7, 1), '1', '7th, ') ||
decode(substr(fcrc.class_info, 8, 1), '1', '8th, ') ||
decode(substr(fcrc.class_info, 9, 1), '1', '9th, ') ||
decode(substr(fcrc.class_info, 10, 1), '1', '10th, ') ||
decode(substr(fcrc.class_info, 11, 1), '1', '11th, ') ||
decode(substr(fcrc.class_info, 12, 1), '1', '12th, ') ||
decode(substr(fcrc.class_info, 13, 1), '1', '13th, ') ||
decode(substr(fcrc.class_info, 14, 1), '1', '14th, ') ||
decode(substr(fcrc.class_info, 15, 1), '1', '15th, ') ||
decode(substr(fcrc.class_info, 16, 1), '1', '16th, ') ||
decode(substr(fcrc.class_info, 17, 1), '1', '17th, ') ||
decode(substr(fcrc.class_info, 18, 1), '1', '18th, ') ||
decode(substr(fcrc.class_info, 19, 1), '1', '19th, ') ||
decode(substr(fcrc.class_info, 20, 1), '1', '20th, ') ||
decode(substr(fcrc.class_info, 21, 1), '1', '21st, ') ||
decode(substr(fcrc.class_info, 22, 1), '1', '22nd, ') ||
decode(substr(fcrc.class_info, 23, 1), '1', '23rd,' ) ||
decode(substr(fcrc.class_info, 24, 1), '1', '24th, ') ||
decode(substr(fcrc.class_info, 25, 1), '1', '25th, ') ||
decode(substr(fcrc.class_info, 26, 1), '1', '26th, ') ||
decode(substr(fcrc.class_info, 27, 1), '1', '27th, ') ||
decode(substr(fcrc.class_info, 28, 1), '1', '28th, ') ||
decode(substr(fcrc.class_info, 29, 1), '1', '29th, ') ||
decode(substr(fcrc.class_info, 30, 1), '1', '30th, ') ||
decode(substr(fcrc.class_info, 31, 1), '1', '31st. ')
ELSE
'n/a'
END days_of_month
, CASE WHEN fcrc.class_type = 'S' AND substr(fcrc.class_info, 32, 1) = '1' THEN
'Yes'
ELSE
'n/a'
END last_day_of_month_ticked
, fcrc.CLASS_INFO
FROM applsys.fnd_concurrent_requests fcr
, applsys.fnd_user fu
, applsys.fnd_user u2
, applsys.fnd_concurrent_programs fcp
, applsys.fnd_concurrent_programs_tl fcpt
, applsys.fnd_printer_styles_tl fpst
, applsys.fnd_conc_release_classes fcrc
, applsys.fnd_responsibility_tl frt
, apps.fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.last_updated_by = u2.user_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND fcr.phase_code = 'P'
AND fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL)
= 'CONCURRENT PROGRAM NAME'
AND 1=1
ORDER BY fu.description, fcr.requested_start_date asc;

How to select value of profile through query

=================================
select fnd_profile.VALUE ( 'XXX_BPEL_TEST_SERVER_URL' )
into v_var
from dual;

or use this in plsql block :

v_var1 := fnd_profile.VALUE ( 'XXX_BPEL_TEST_SERVER_URL' );
=================================

To get all the users which have all the responsibilities given as parameter

================================================================
SELECT SUM (num), user_name, tot_num
FROM (SELECT 1 num, frgd.user_id, fu.user_name, frgd.responsibility_id,
tot.tot_num
FROM fnd_user_resp_groups_direct frgd,
fnd_user fu,
fnd_responsibility_tl frt,
(SELECT COUNT (1) tot_num
FROM fnd_responsibility_tl
WHERE responsibility_name IN
('Application Developer', 'Purchasing')
AND LANGUAGE = 'US') tot
WHERE 1 = 1
AND frgd.user_id = fu.user_id
AND frgd.responsibility_id = frt.responsibility_id
AND frgd.responsibility_application_id = frt.application_id
--AND FRGD.USER_ID = 15732
--AND fu.user_name like 'PGARG'
AND LANGUAGE = 'US'
AND frt.responsibility_name IN
('Application Developer', 'Purchasing'))
GROUP BY user_name, tot_num
HAVING SUM (num) = tot_num
================================================================

Query to know path of log file and out file of a concurrent program

=============================
SELECT logfile_name, outfile_name
FROM fnd_concurrent_requests
WHERE request_id = 449842
=============================

Query to know all the menus/ Responsibilities name where one given form is refered

Query to know all the menus/ Responsibilities name where one given form is refered
============================================================
SELECT RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE MENU_ID IN (SELECT menu_id
FROM fnd_menu_entries_vl
WHERE FUNCTION_ID IN ( SELECT FUNCTION_ID
FROM fnd_menu_entries_vl
WHERE PROMPT = 'Define'))
UNION
SELECT RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE MENU_ID IN (SELECT MENU_ID
FROM fnd_menu_entries_vl
WHERE SUB_MENU_ID IN(SELECT menu_id
FROM fnd_menu_entries_vl
WHERE FUNCTION_ID IN
( SELECT FUNCTION_ID
FROM fnd_menu_entries_vl
WHERE PROMPT = 'Define')));
==============================================================

Query to get Application Short Name, Base Path, Application Id If you know Application Name

SELECT fa.application_short_name, fat.application_id, fa.basepath,
fa.product_code
FROM fnd_application_tl fat, fnd_application fa
WHERE fat.application_id = fa.application_id
AND fat.application_name = 'Purchasing'

Query to get the request group of a concurrent program

======================================================

SELECT REQUEST_GROUP_NAME
FROM FND_REQUEST_GROUPS where REQUEST_GROUP_ID in
(SELECT REQUEST_GROUP_ID
FROM FND_REQUEST_GROUP_UNITS WHERE (request_unit_id=820) )
----------------------------------------------------------
SELECT CONCURRENT_PROGRAM_ID
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE queue_control_flag = 'N'
and USER_CONCURRENT_PROGRAM_NAME = 'TK Electronic Customs Interface - Set'
order by application_id, user_concurrent_program_name

/*if you do not find this request group directly attached to in any of the responsibilkity then go to the function and query in parameter field
with this request group , then you will get the function name - get the function id ,
--through this function id you will get to know in which menu this is attached (through below query ),
through menu name you will get to know the tresponsibility name.*/

SELECT entry_sequence, prompt, description, grant_flag, menu_id,
sub_menu_id, function_id, last_update_date, last_updated_by,
created_by, creation_date, last_update_login, row_id
FROM fnd_menu_entries_vl
WHERE function_id = 26045

SELECT menu_name, user_menu_name, TYPE, description, menu_id,
last_update_date, last_updated_by, last_update_login, creation_date,
created_by, row_id
FROM fnd_menus_vl
where menu_id = 79018
---------------------------------
Request set
SELECT request_set_id, user_request_set_name, request_set_name, description,
start_date_active, end_date_active, print_together_flag,
allow_constraints_flag,application_id,
concurrent_program_id, owner, created_by, creation_date,
last_update_date, last_updated_by, last_update_login, icon_name,
printer, start_stage
FROM fnd_request_sets_vl
WHERE ('SYS' = 'SYS' OR owner = 1304)
AND (user_request_set_name = 'TK Electronic Customs Interface - Set')
ORDER BY application_id, request_set_name

========================================================

Getting current concurrent request number in plsql fired by concurrent request

select FND_GLOBAL.CONC_REQUEST_ID from dual;

To get the Organization Name if you know its Id

select * from org_organization_definition where organization_id = 121;

Oracle Applications SQL Query to know all the menus Responsibilities name where one given form is refered

Oracle Applications SQL Query to know all the menus Responsibilities name where one given form is refered
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
RESPONSIBILITY_NAME FROM fnd_responsibility_vl
WHERE
MENU_ID IN
(SELECT menu_id
FROM fnd_menu_entries_vl
WHERE
FUNCTION_ID IN
( SELECT
FUNCTION_ID
FROM
fnd_menu_entries_vl
WHERE
PROMPT = 'Define'))
UNION
SELECT
RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE
MENU_ID IN
(SELECT
MENU_ID
FROM
fnd_menu_entries_vl
WHERE
SUB_MENU_ID IN
(SELECT
menu_id
FROM
fnd_menu_entries_vl
WHERE
FUNCTION_ID IN
( SELECT
FUNCTION_ID
FROM
fnd_menu_entries_vl
WHERE
PROMPT = 'Define')))
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications Query to get the current users of oracle apps and forms or concurrent programms opened by them (Through front end and back end

Oracle Applications Query to get the current users of oracle apps and forms or concurrent programms opened by them (Through front end and back end
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Front End ---> System Administration - Security - User - Monitor

Back End Query --- SELECT
USER_NAME, RESPONSIBILITY_NAME, USER_FORM_NAME, TIME, PID, USER_ID, RESP_APPL_ID, RESPONSIBILITY_ID, FORM_ID, FORM_APPL_ID
FROM
FND_SIGNON_AUDIT_VIEW
where
RESPONSIBILITY_NAME IS NOT NULL
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications SQL Query to get apps user data in just plain sql using something like userenv or srw to extract say their org_id, etc.?

Oracle Applications SQL Query to get apps user data in just plain sql using something like userenv or srw to extract say their org_id, etc.?
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
select
fnd_global.user_id,
fnd_global.resp_id,
fnd_global.resp_appl_id,
fnd_global.security_group_id,
fnd_global.user_name,
fnd_global.resp_name,
fnd_global.application_name,
fnd_global.application_short_name,
fnd_global.login_id,
fnd_global.conc_login_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
fnd_global.conc_request_id,
fnd_global.current_language,
fnd_global.base_language,
fnd_global.org_id,
fnd_global.org_name
from dual;
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications Query to know the fmb name if you know the form name and the path for its fmx and fmb as well

Oracle Applications Query to know the fmb name if you know the form name and the path for its fmx and fmb as well
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
ffv.form_name, ffv.user_form_name, ffv.description,
(SELECT application_name FROM fnd_application_tl WHERE application_id = ffv.application_id) application_name,
(SELECT basepath FROM fnd_application WHERE application_id = ffv.application_id) basepath,
form_id,
audit_enabled_flag
FROM
fnd_form_vl ffv
WHERE
(form_id >= 0) AND UPPER (user_form_name) LIKE '%MASS CHANGE%'

-----Path we have to derive from BASEPATH variable displayed in query



!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name

Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
B.CONCURRENT_PROGRAM_NAME , A.USER_EXECUTABLE_NAME ,
DECODE( A.EXECUTION_METHOD_CODE , 'I' , 'PL/SQL Stored Procedure' , 'H' ,'Host' , 'S' ,'Immediate' , 'J' ,'Java Stored Procedure' , 'K', 'Java concurrent program' , 'M', 'Multi Language Function' , 'P', 'Oracle reports' , 'B', 'Request Set Stage Function' , 'A', 'Spawned' , 'L', 'SQL*Loader' , 'Q', 'SQL*Plus' , 'E', 'Pearl concurrent Programm' , 'Unkown Type') ,
A.EXECUTION_FILE_NAME , A.EXECUTION_FILE_PATH
FROM
FND_EXECUTABLES_FORM_V A , FND_CONCURRENT_PROGRAMS_VL B
WHERE
A.EXECUTABLE_ID = B.EXECUTABLE_ID AND A.APPLICATION_ID = B.APPLICATION_ID AND A.executable_id > 4 AND upper(B.USER_CONCURRENT_PROGRAM_NAME) LIKE 'XX INVENTORY CONVERSION%'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications Query to get all the oracle apps responsibilities responsibility having by one oracle apps user

Oracle Applications Query to get all the oracle apps responsibilities responsibility having by one oracle apps user
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SELECT
FRT.RESPONSIBILITY_NAME, FAT.APPLICATION_NAME, FURGD.START_DATE, FURGD.END_DATE
FROM
FND_USER_RESP_GROUPS_DIRECT FURGD, FND_RESPONSIBILITY_TL FRT, FND_RESPONSIBILITY FT, FND_APPLICATION_TL FAT, FND_USER FU
WHERE
FURGD.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FRT.APPLICATION_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FAT.APPLICATION_ID AND FURGD.RESPONSIBILITY_ID = FT.RESPONSIBILITY_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FT.APPLICATION_ID AND FURGD.USER_ID =FU.USER_ID AND FT.VERSION IN ('4','W','M','H') AND FU.USER_NAME= 'PGARG'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications SQL Query to get all the apps users from FND_USER having particular Responsibility

Oracle Applications SQL Query to get all the apps users from FND_USER having particular Responsibility
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SELECT
fu.user_name, frt.responsibility_name, fat.application_name, furgd.start_date, furgd.end_date
FROM
fnd_user_resp_groups_direct furgd, fnd_responsibility_tl frt, fnd_responsibility ft, fnd_application_tl fat, fnd_user fu
WHERE
furgd.responsibility_id = frt.responsibility_id AND furgd.responsibility_application_id = frt.application_id AND furgd.responsibility_application_id = fat.application_id AND furgd.responsibility_id = ft.responsibility_id AND furgd.responsibility_application_id = ft.application_id AND furgd.user_id = fu.user_id AND ft.VERSION IN ('4', 'W', 'M', 'H') AND frt.responsibility_name = 'Application Developer'

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle applications SQL Query to select all the functions Submenu comprises by a menu if you know its responsibility name

Oracle applications SQL Query to select all the functions Submenu comprises by a menu if you know its responsibility name
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SELECT
0 num,
RPAD ('Hirarchy for ' '-' user_menu_name,60,'-') '(' menu_id ')--(Main Menu)' menu_hirarchy
FROM fnd_menus_vl
WHERE user_menu_name = 'ABM Intelligence'
UNION
SELECT
ROWNUM num,
RPAD ( LPAD (' ', 7 * LEVEL) entry_sequence '-' NVL (prompt, function_id),60,'-')
'(' NVL (sub_menu_id, function_id) ')--(' DECODE (sub_menu_id, NULL, 'Function', 'Sub Menu') ')'
menu_hirarchy
FROM fnd_menu_entries_vl
START WITH menu_id = (SELECT menu_id FROM fnd_menus_vl WHERE menu_id =
(SELECT menu_id FROM fnd_responsibility_vl WHERE UPPER (responsibility_name) =
'APPLICATION DEVELOPER'))
CONNECT BY PRIOR sub_menu_id = menu_id
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!