-- -----------------------------------------------------------------------------------
-- 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
-- --------------------------------------------------------------------------------------
-- FND_DATE.CANONICAL_TO_DATE (Oracle error we receive - Ora-01861)
-- --------------------------------------------------------------------------------------
Error - Oracle error we receive - Ora-01861 literal dose not match the format string
1. Date parameters for Oracle apps concurrent programs in Oracle Applications R12 must be set up using the FND_STANDARD_DATE valueset.
2. And The date value entered by the user at runtime is passed to the underlying program(PLSQL package, RDF etc.) as a DATE-TIME STRING and not as a DATE.
This format of VARCHAR2 data item is "YYYY/MM/DD HH24:MI:SS" (e.g. "2019/05/10 00:00:00").
and we need value as 10-MAY-19
In order to use this VARCHAR2 value in a WHERE clause , it can be converted to a DATE value using the fnd_date.canonical_to_date function,
as shown in the following example:
Sample code is as below.
PROCEDURE XX(p_start_date IN VARCHAR2,ERRBUFF IN VARCHAR2,RETCODE IN
VARCHAR2)
IS
l_start_date DATE;
BEGIN
l_start_date:=FND_DATE.CANONICAL_TO_DATE(p_start_date);
END;
-- --------------------------------------------------------------------------------------
--find concurrent program usage since they have been installed -fnd_conc_prog_onsite_info
-- --------------------------------------------------------------------------------------
SELECT Distinct fa.application_short_name,
fcpt.USER_CONCURRENT_PROGRAM_NAME,
fcp.concurrent_program_name concurrent_program_short_name,
fcp.concurrent_program_id,
fe.EXECUTABLE_NAME,
fe.execution_file_name,
(CASE
WHEN REGEXP_COUNT (UPPER (fe.EXECUTION_FILE_NAME), '\.', 1) = 2
THEN
(SUBSTR (
SUBSTR (UPPER (fe.EXECUTION_FILE_NAME),
INSTR (UPPER (fe.EXECUTION_FILE_NAME),
'.',
1,
1)
+ 1),
1,
INSTR (SUBSTR (UPPER (fe.EXECUTION_FILE_NAME),
INSTR (UPPER (fe.EXECUTION_FILE_NAME),
'.',
1,
1)
+ 1),
'.')
- 1))
WHEN REGEXP_COUNT (UPPER (fe.EXECUTION_FILE_NAME), '\.', 1) = 1
THEN
(SUBSTR (UPPER (fe.EXECUTION_FILE_NAME),
1,
INSTR (UPPER (fe.EXECUTION_FILE_NAME), '.') - 1))
ELSE
UPPER (fe.EXECUTION_FILE_NAME)
END)
OBJECT_NAME,
fcp.executable_id,
fe.EXECUTION_METHOD_CODE,
flv.meaning,
fcpoi.RESET_DATE,
fcpoi.LAST_RUN_DATE,
fcpoi.SUCCESSFUL_COMPLETION,
fcpoi.WARNING_COMPLETION,
fcpoi.ERROR_COMPLETION,
(NVL(fcpoi.SUCCESSFUL_COMPLETION,0) + NVL(fcpoi.WARNING_COMPLETION,0) + NVL(fcpoi.ERROR_COMPLETION,0) )TOTAL_EXECUTIONS
FROM fnd_conc_prog_onsite_info fcpoi,
fnd_concurrent_programs_tl FCPT,
fnd_concurrent_programs FCP,
fnd_executables fe,
fnd_lookup_values flv,
fnd_application fa
WHERE fcpoi.CONCURRENT_PROGRAM_ID = fcpt.CONCURRENT_PROGRAM_ID
AND fcpoi.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
AND fcpt.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
AND fcp.executable_id = fe.executable_id
AND flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND flv.LOOKUP_CODE = fe.EXECUTION_METHOD_CODE
AND flv.Language ='US'
AND FCP.application_id = fa.application_id
AND ( fa.application_short_name LIKE 'XX%'
OR fe.executable_name LIKE 'XX%'
OR fe.executable_name LIKE 'FSSC%'
OR fa.application_short_name LIKE 'NOETIX%')
0 comments:
Post a Comment