SysAdmin Queries

-- ----------------------------------------------------------------------------------------
-- Query To get All Notifications Sent By A Particular Workflow
-- ----------------------------------------------------------------------------------------
select wn.notification_id nid,
wn.context,
wn.group_id,
wn.status,
wn.mail_status,
wn.message_type,
wn.message_name,
wn.access_key,
wn.priority,
wn.begin_date,
wn.end_date,
wn.due_date,
wn.callback,
wn.recipient_role,
wn.responder,
wn.original_recipient,
wn.from_user,
wn.to_user,
wn.subject
from wf_notifications wn, wf_item_activity_statuses wias
where wn.group_id = wias.notification_id
and wias.item_type = '&item_type'
and wias.item_key = '&item_key';

-- -----------------------------------------------------------------------------------------------------------------------------
-- To Check Workflow Mailer Up or down
-- -----------------------------------------------------------------------------------------------------------------------------

SELECT component_name as Component, component_status as Status FROM fnd_svc_components WHERE component_type = 'WF_MAILER'

-- --------------------------------------------------------------------------------------------------------------
-- Query To get the Activity Statuses For All Workflow Activities For A Particular Item Type and Item key
-- --------------------------------------------------------------------------------------------------------------
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2,
1
/
-- -------------------------------------------------------------------------------------------
-- Query To get Errored Workflow Activities For A Particular Item Type/ Item Key
-- -------------------------------------------------------------------------------------------

SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time

-- ---------------------------------------------------------------------------------------------------
-- Query To get Errored Process Activity Statuses For A Particular Item Type/Item Key
-- ----------------------------------------------------------------------------------------------------

SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2,
1

-- ---------------------------------------------------------------------------------------------
-- Query To get The Errored Activities For A Particular Item Type/Item Key
-- ---------------------------------------------------------------------------------------------
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time
/

Query To get Out Attribute Values Of A Workflow:
SELECT NAME attr_name,
nvl(text_value,
nvl(to_char(number_value),
to_char(date_value))) VALUE
FROM wf_item_attribute_values
WHERE item_type = upper('&item_type')
AND item_key = nvl('&item_key',
item_key)
/
-- -----------------------------------------------------------------------------------------------
-- Query To get Out Number Of Deferred Workflow Activities
-- -----------------------------------------------------------------------------------------------

SELECT COUNT(1),
was.item_type
FROM apps.wf_items wi,
apps.wf_item_activity_statuses was,
apps.wf_process_activities pra
WHERE wi.item_type = was.item_type
AND wi.item_key = was.item_key
AND wi.end_date IS NULL
AND was.end_date IS NULL
AND was.activity_status = 'DEFERRED'
--AND was.item_type = 'REQAPPRV'
AND was.item_type = wi.item_type
AND pra.instance_id(+) = was.process_activity
GROUP BY was.item_type;

-- ------------------------------------------------------------------------------------------------
-- Query To Get The Details Of Various Workflow Agent Listeners And Their Statuses
-- ------------------------------------------------------------------------------------------------

SELECT t.component_name,
p.owner,
p.queue_table,
t.correlation_id
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';

-- --------------------------------------------------------------------------------------------------
-- Query To get Records That Are Pending In Each Of The Workflow Agent Listener Queues
-- --------------------------------------------------------------------------------------------------

SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
')''||'' Count: ''||count(*) c from ' || p.owner || '.' || p.queue_table ||
' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
nvl2(t.correlation_id,
'and corrid like ''' || t.correlation_id || ''' ',
NULL) || 'having count(*)>0;'
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';

-- -----------------------------------------------------------------------------------
-- For finding COncurrent program attached to responsibility
-- -----------------------------------------------------------------------------------

SELECT
DISTINCT fcpt.user_concurrent_program_name, frt.responsibility_name
-- frg.request_group_name,
-- frgu.request_unit_type,
-- frgu.request_unit_id,
--,decode(instance,'CFGAPPRD','FAP1C','N')
FROM apps.fnd_Responsibility fr,
apps.fnd_responsibility_tl frt,
apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs_tl fcpt
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.concurrent_program_id = frgu.request_unit_id
AND frt.language = USERENV ('LANG')
AND fcpt.language = USERENV ('LANG')
AND fcpt.user_concurrent_program_name LIKE
'%%'
ORDER BY 2;
-- -----------------------------------------------------------------------------------
-- Query to get Menus & Functions attached to Responsibility.
-- -----------------------------------------------------------------------------------

SELECT LEVEL,
LPAD (' ', (LEVEL-1)*3)||prompt,
Description
FROM fnd_menu_entries_vl fme
WHERE prompt IS NOT NULL
AND grant_flag='Y'
AND NOT EXISTS (SELECT 1
FROM fnd_resp_functions frf,
fnd_responsibility_vl frv
WHERE frv.responsibility_id = frf.responsibility_id
AND frf.application_id = frv.application_id
AND frf.rule_type='M'
AND frf.action_id = NVL(fme.sub_menu_id,-1)
AND frv.responsibility_name=:RESP_NAME)
AND NOT EXISTS (SELECT 1
FROM fnd_resp_functions frf,
fnd_responsibility_vl frv
WHERE frv.responsibility_id = frf.responsibility_id
AND frf.application_id = frv.application_id
AND frf.rule_type='F'
AND frf.action_id = NVL(fme.function_id,-1)
AND frv.responsibility_name=:RESP_NAME)
CONNECT BY PRIOR sub_menu_id = menu_id
AND prompt IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM fnd_resp_functions frf,
fnd_responsibility_vl frv
WHERE frv.responsibility_id = frf.responsibility_id
AND frf.application_id = frv.application_id
AND frf.rule_type='M'
AND frf.action_id = NVL(fme.menu_id,-1)
AND frv.responsibility_name=:RESP_NAME)
START WITH menu_id = (SELECT menu_id
FROM fnd_responsibility_vl
WHERE responsibility_name=:RESP_NAME)
ORDER SIBLINGS BY entry_sequence

-- -----------------------------------------------------------------------------------
-- Find all menus to which function is attached
-- -----------------------------------------------------------------------------------

select fme.menu_id
,FM.MENU_NAME
,fme.entry_sequence
,fff.function_name
from fnd_menu_entries_vl fme
,fnd_menus_vl fm
,fnd_form_functions_vl fff
where fme.menu_id=fm.menu_id
and fme.function_id=fff.function_id
and function_name LIKE 'XX%';

-- -----------------------------------------------------------------------------------
-- Query for finding menu exclusion for a responsibility
-- -----------------------------------------------------------------------------------

select res.responsibility_name
, app.application_name
, res.responsibility_key
, res.description
, res.start_date
, res.end_date
, dat.data_group_name
, apd.application_name
, mnu.menu_name
, req.request_group_name
, apr.application_name
, decode(exc.rule_type,'F','Function','M', 'Menu', rule_type) Exclusion_Type
, decode(exc.rule_type,'F',(select function_name || ',' || description
from fnd_form_functions_vl fnc
where fnc.function_id = exc.action_id
)
,'M',(select menu_name || ',' || description
from fnd_menus_vl imn
where imn.menu_id = exc.action_id
)
, to_char(exc.action_id)
) Excluded_Menu_Or_func
from apps.fnd_responsibility_vl res
, apps.fnd_application_vl app
, apps.fnd_data_groups dat
, apps.fnd_menus_vl mnu
, apps.fnd_request_groups req
, apps.fnd_application_vl apd
, apps.fnd_application_vl apr
, apps.fnd_resp_functions exc
where res.application_id = app.application_id
and res.data_group_id = dat.data_group_id
and res.data_group_application_id = apd.application_id
and res.menu_id = mnu.menu_id
and req.request_group_id = res.request_group_id
and req.application_id = res.group_application_id
and apr.application_id = req.application_id
and exc.application_id = res.application_id
and exc.responsibility_id = res.responsibility_id
and responsibility_name ='APAC IT Support';

-- -----------------------------------------------------------------------------------
-- To find responsibilities assigned to particular user
-- -----------------------------------------------------------------------------------

SELECT resp_type
,user_id
,user_name
,responsibility_id
,responsibility_name
,start_date
,end_date
,application_short_name
,application_name
FROM (SELECT 'DIRECT' resp_type
,fu.user_id
,fu.user_name
,resp.responsibility_id
,resp.responsibility_name
,frd.start_date
,frd.end_date
,app.application_short_name
,app.application_name
FROM fnd_user fu
,fnd_user_resp_groups_direct frd
,fnd_responsibility_vl resp
,fnd_application_vl app
WHERE fu.user_id = frd.user_id
AND frd.responsibility_id = resp.responsibility_id
AND resp.application_id = app.application_id
UNION ALL
SELECT 'INDIRECT' resp_type
,fu.user_id
,fu.user_name
,resp.responsibility_id
,resp.responsibility_name
,fri.start_date
,fri.end_date
,app.application_short_name
,app.application_name
FROM fnd_user fu
,fnd_user_resp_groups_indirect fri
,fnd_responsibility_vl resp
,fnd_application_vl app
WHERE fu.user_id = fri.user_id
AND fri.responsibility_id = resp.responsibility_id
AND resp.application_id = app.application_id)
WHERE 1=1
AND user_name = 'AJTEST' -- Comment this if you need all user of a responsibility
AND responsibility_name = 'System Administrator'; -- Comment this if you need all responsibilities of a user

-- --------------------------------------------------------------------------------------
-- Profile Option
-- --------------------------------------------------------------------------------------

select user_profile_option_name ,sum(level_value)
from fnd_profile_option_values fp
,fnd_profile_options_vl fpv
where fp.profile_option_id=fpv.profile_option_id
and level_id =10004
group by fpv.user_profile_option_name
having sum(level_value)>2;

-- -----------------------------------------------------------------------------------
-- Query to get the profile option values
-- -----------------------------------------------------------------------------------

SELECT b.user_profile_option_name "Long Name" ,
a.profile_option_name "Short Name" ,
NVL(g.responsibility_name,c.level_value) "Level Value" ,
c.PROFILE_OPTION_VALUE "Profile Value",
b.sql_validation
FROM apps.fnd_profile_options a ,
apps.FND_PROFILE_OPTIONS_VL b ,
apps.FND_PROFILE_OPTION_VALUES c ,
apps.FND_USER d ,
apps.FND_USER e ,
apps.FND_RESPONSIBILITY_VL g ,
apps.FND_APPLICATION h
WHERE 1 =1
AND a.profile_option_name = b.profile_option_name
AND a.profile_option_id = c.profile_option_id
AND a.application_id = c.application_id
AND c.last_updated_by = d.user_id (+)
AND c.level_value = e.user_id (+)
AND c.level_value = g.responsibility_id (+)
AND c.level_value = h.application_id (+)
--
AND c.level_id = 10003
AND g.responsibility_name = 'APAC IT Support'
ORDER BY b.user_profile_option_name, c.level_id
;

-- -----------------------------------------------------------------------------------
-- Query for form function details
-- -----------------------------------------------------------------------------------

SELECT DISTINCT u.user_name, rtl.responsibility_name, ff.function_name, ffl.user_function_name
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_tl rtl,
fnd_user_resp_groups urg,
fnd_user u
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND urg.responsibility_id = r.responsibility_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = 'Y'
AND r.application_id = urg.responsibility_application_id
AND u.user_id = urg.user_id
AND UPPER (ffl.user_function_name) = UPPER ('Returns') --CES DEU1 Purchasing Plant Super User
AND FF.FUNCTION_ID = FFL.FUNCTION_ID
ORDER BY u.user_name;

-- -----------------------------------------------------------------------------------
-- Query to get all fuctions attached to responsibilities
-- -----------------------------------------------------------------------------------

SELECT DISTINCT faa.application_name application, rtl.responsibility_name,
ffl.user_function_name, ff.function_name, ffl.description,
ff.TYPE
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_vl rtl,
apps.fnd_application_all_view faa
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = 'Y'
AND ff.function_id = ffl.function_id
AND faa.application_id(+) = r.application_id
AND r.end_date IS NULL
AND rtl.end_date IS NULL
ORDER BY rtl.responsibility_name;

-- -----------------------------------------------------------------------------------
-- Following are the FND_PROFILE values that can be used in the PL/SQL code:
-- -----------------------------------------------------------------------------------

fnd_profile.value('PROFILEOPTION');
fnd_profile.value('MFG_ORGANIZATION_ID');
fnd_profile.value('ORG_ID');
fnd_profile.value('LOGIN_ID');
fnd_profile.value('USER_ID');
fnd_profile.value('USERNAME');
fnd_profile.value('CONCURRENT_REQUEST_ID');
fnd_profile.value('GL_SET_OF_BKS_ID');
fnd_profile.value('SO_ORGANIZATION_ID');
fnd_profile.value('APPL_SHRT_NAME');
fnd_profile.value('RESP_NAME');
fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

FND_GLOBAL.USER_ID;
FND_GLOBAL.APPS_INTIALIZE;
FND_GLOBAL.LOGIN_ID;
FND_GLOBAL.CONC_LOGIN_ID;
FND_GLOBAL.PROG_APPL_ID;
FND_GLOBAL.CONC_PROGRAM_ID;
FND_GLOBAL.CONC_REQUEST_ID;

-- -----------------------------------------------------------------------------------
-- AOL Defeinitions
-- -----------------------------------------------------------------------------------

--CONCURRENT_PROGRAM_NAME
SELECT USER_CONCURRENT_PROGRAM_NAME
FROM FND_CONCURRENT_PROGRAMS_TL
WHERE LANGUAGE = 'US'
AND APPLICATION_ID = 50001
ORDER BY USER_CONCURRENT_PROGRAM_NAME;

--FORM
SELECT USER_FORM_NAME , FORM_NAME
FROM FND_FORM_VL
WHERE APPLICATION_ID = 50001
ORDER BY 1,2;

--FORM FUNCTIONS
SELECT USER_FUNCTION_NAME
FROM FND_FORM_FUNCTIONS_VL
WHERE FORM_ID IN (SELECT FORM_ID
FROM FND_FORM_VL
WHERE APPLICATION_ID = 50001)
ORDER BY 1;

--LOOKUP
SELECT DISTINCT LOOKUP_TYPE
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE LIKE 'XX%'
ORDER BY LOOKUP_TYPE;

SELECT LOOKUP_TYPE,LOOKUP_CODE
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE LIKE 'XX%'
AND ENABLED_FLAG = 'Y'
ORDER BY LOOKUP_TYPE,LOOKUP_CODE;

--REQUEST GROUPS
SELECT fr.REQUEST_GROUP_NAME,fp.USER_CONCURRENT_PROGRAM_NAME
FROM FND_REQUEST_GROUPS fr
, FND_REQUEST_GROUP_UNITS fru
, FND_CONCURRENT_PROGRAMS_TL fp
WHERE fr.REQUEST_GROUP_ID = fru.REQUEST_GROUP_ID
AND fru.REQUEST_UNIT_ID = fp.CONCURRENT_PROGRAM_ID
AND fp.LANGUAGE = 'US'
AND fru.REQUEST_UNIT_TYPE = 'P'
AND fr.APPLICATION_ID = 50001
UNION
SELECT fr.REQUEST_GROUP_NAME,frs.USER_REQUEST_SET_NAME
FROM FND_REQUEST_GROUPS fr
, FND_REQUEST_GROUP_UNITS fru
, FND_REQUEST_SETS_TL frs
WHERE fr.REQUEST_GROUP_ID = fru.REQUEST_GROUP_ID
AND fru.REQUEST_UNIT_ID = frs.REQUEST_SET_ID
AND frs.LANGUAGE = 'US'
AND fru.REQUEST_UNIT_TYPE = 'S'
AND fr.APPLICATION_ID = 50001
ORDER BY 1,2;

--MENUS--
SELECT fm.MENU_NAME , PROMPT
, (CASE WHEN SUB_MENU_ID IS NOT NULL THEN (SELECT MENU_NAME
FROM FND_MENUS
WHERE MENU_ID = fmv.SUB_MENU_ID )
WHEN fmv.FUNCTION_ID IS NOT NULL THEN (SELECT USER_FUNCTION_NAME
FROM FND_FORM_FUNCTIONS_TL f
WHERE f.FUNCTION_ID = fmv.FUNCTION_ID
AND f.LANGUAGE = 'US')
END) MENU_FUNCTION
FROM FND_MENU_ENTRIES_VL fmv
, FND_MENUS fm
WHERE fm.MENU_ID = fmv.MENU_ID
AND fm.MENU_NAME LIKE 'XX%'

-- --------------------------------------------------------------------------------------------
-- Form Personalization query
-- --------------------------------------------------------------------------------------------

Select Distinct
A.Id,
A.Form_Name ,
A.Enabled,
C.User_Form_Name,
D.Application_Name ,
A.Description,
Ca.Action_Type,
Ca.Enabled,
Ca.Object_Type,
ca.message_type,
ca.message_text
from
FND_FORM_CUSTOM_RULES a,
FND_FORM b,
FND_FORM_TL c,
Fnd_Application_Tl D,
Fnd_Form_Custom_Actions ca
where a.form_name = b.form_name
And B.Form_Id = C.Form_Id
And B.Application_Id = D.Application_Id
--And D.Application_Id = 230 --For Order Management
And C.User_Form_Name Like 'Inventory%' --All the Forms that Start with Sales
And A.Enabled ='Y'
and a.id = ca.rule_id
--------------------------------------------------------------------------------------------
select ffv.form_id , ffv.form_name, ffv.user_form_name, ffv.description, ffcr.description, ffcr.sequence
from fnd_form_vl ffv,fnd_form_custom_rules ffcr
where ffv.form_name = ffcr.form_name
and ffcr.description like '%7663%';

-- -----------------------------------------------------------------------------------------
-- Template and its bursting path
-- -----------------------------------------------------------------------------------------
select
lt.application_short_name || '.' || lt.template_code || '.' || lt.default_language || '.' || lt.default_territory as burst_template_xdo_location
from
apps.xdo_templates_b lt
where lt.template_code ='XXAR0387'

-- -------------------------------------------------------------------------------------------
-- Query to Find Only Required fields in a particular DFF
-- -------------------------------------------------------------------------------------------

-- Query to Find All the fields in a particular DFF:-

SELECT ffv.descriptive_flexfield_name DFFName,
ffv.application_table_name TableName,
ffv.title Title,
ap.application_name Application,
att.column_seq_num SegmentNumber,
att.form_left_prompt SegmentName,
att.application_column_name,
fvs.flex_value_set_name ValueSet,
att.required_flag
FROM apps.fnd_descriptive_flexs_vl ffv,
apps.fnd_descr_flex_contexts_vl ffc,
apps.fnd_descr_flex_col_usage_vl att,
apps.fnd_flex_value_sets fvs,
apps.fnd_application_vl ap
WHERE ffv.descriptive_flexfield_name =
att.descriptive_flexfield_name
AND ap.application_id = ffv.application_id
AND ffv.descriptive_flexfield_name =
ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code =
att.descriptive_flex_context_code
AND fvs.flex_value_set_id = att.flex_value_set_id
AND ffv.title IN ('Invoice Line Information')
ORDER BY ffv.title;

-- Query to Find Only Required fields in a particular DFF

SELECT *
FROM ( SELECT ffv.descriptive_flexfield_name DFFName,
ffv.application_table_name TableName,
ffv.title Title,
ap.application_name Application,
att.column_seq_num SegmentNumber,
att.form_left_prompt SegmentName,
att.application_column_name,
fvs.flex_value_set_name ValueSet,
att.required_flag
FROM apps.fnd_descriptive_flexs_vl ffv,
apps.fnd_descr_flex_contexts_vl ffc,
apps.fnd_descr_flex_col_usage_vl att,
apps.fnd_flex_value_sets fvs,
apps.fnd_application_vl ap
WHERE ffv.descriptive_flexfield_name =
att.descriptive_flexfield_name
AND ap.application_id = ffv.application_id
AND ffv.descriptive_flexfield_name =
ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code =
att.descriptive_flex_context_code
AND fvs.flex_value_set_id = att.flex_value_set_id
AND ffv.title IN ('Invoice Line Information')
ORDER BY ffv.title)
WHERE required_flag = 'Y';

-- --------------------------------------------------------------------------------------
-- Request id and its resp
-- --------------------------------------------------------------------------------------
select c.request_id, r.responsibility_name
from fnd_responsibility_vl r, fnd_concurrent_requests c
where c.request_id = 97131851
and c.responsibility_application_id = r.application_id
and c.responsibility_id = r.responsibility_id

Oracle Apps World - Yogesh Rathi

Yogesh Rathi Menu

Thursday, May 23, 2019

SysAdmin Queries

Blog Archive