ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B52C6BB09E788499E0530100007F4F38 FND Flex Hierarchy Y select distinct ffvnh.parent_flex_value value, (select ffvv.description||case when ffvv.enabled_flag='Y' and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) then null else ' (inactive)' end from fnd_flex_values_vl ffvv where ffvnh.flex_value_set_id=ffvv.flex_value_set_id and ffvv.parent_flex_value_low is null and ffvnh.parent_flex_value=ffvv.flex_value)||' ('||nvl2(ffvnh.is_child,null,xxen_util.meaning('TOP','TOP_BOTTOM_PERF',283)||' ')||xxen_util.meaning('CCONTAIN','ICX_CONDITIONS',0)||' '||xxen_util.meaning(ffvnh.max_range_attribute,'RANGE_ATTRIBUTE',0)||')' description, ffvnh.is_child, ffvnh.max_range_attribute from ( select distinct ffvnh.flex_value_set_id, ffvnh.parent_flex_value, (select 'Y' from gl_seg_val_norm_hierarchy gsvnh where ffvnh.parent_flex_value=gsvnh.child_flex_value and ffvnh.flex_value_set_id=gsvnh.flex_value_set_id and rownum=1) is_child, max(ffvnh.range_attribute) over (partition by ffvnh.parent_flex_value) max_range_attribute from fnd_flex_value_norm_hierarchy ffvnh where ffvnh.flex_value_set_id in ( select ffvs.flex_value_set_id from fnd_flex_value_sets ffvs where ffvs.flex_value_set_name=:$flex$.flex_value_set union all select fifsv.flex_value_set_id from fnd_id_flex_segments_vl fifsv where fifsv.form_left_prompt=:$flex$.hierarchy_segment and fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101) or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y') ) ) ) ffvnh order by ffvnh.is_child desc, ffvnh.max_range_attribute desc, length(ffvnh.parent_flex_value), ffvnh.parent_flex_value US 8E2FF36EDF1979D2E0530100007F1FF2 FND Key Flexfield Y select fif.id_flex_name value, fav.application_name||': '||fif.application_table_name||' ('||fif.id_flex_code||')' description from fnd_application_vl fav, fnd_id_flexs fif where fav.application_id=fif.application_id order by fif.id_flex_name US A3AB189B606C4736E0530100007FD533 FND Key Flexfield Structure Y select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code||': '||fifsv.description description from fnd_id_flexs fif, fnd_id_flex_structures_vl fifsv where (fifsv.id_flex_code=:$flex$.fnd_key_flexfield_code or fif.id_flex_name=:$flex$.fnd_key_flexfield) and fif.application_id=fifsv.application_id and fif.id_flex_code=fifsv.id_flex_code order by fifsv.id_flex_structure_name US 8E2FF36EDE8479D2E0530100007F1FF2 FND User Name Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US FND user names, excluding iRecruitment candidates. 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US 3A3568A7050FAF06E06362FB09059370Y select ffvs.flex_value_set_name, xxen_util.meaning(ffvs.validation_type,'SEG_VAL_TYPES',0) validation_type, ffvs0.flex_value_set_name parent_flex_value_set, ffvv.parent_flex_value_low independent_value, ffvv.flex_value, ffvv.flex_value_meaning translated_value, ffvv.description, xxen_util.yes(ffvv.enabled_flag) enabled, ffvv.start_date_active, ffvv.end_date_active, xxen_util.yes(ffvv.summary_flag) parent, ffvnh.child_flex_value_low||nvl2(ffvnh.child_flex_value_low,'-',null)||ffvnh.child_flex_value_high child_range, xxen_util.meaning(ffvnh.range_attribute,'RANGE_ATTRIBUTE',0) range_attribute, ffhv.hierarchy_name rollup_group, ffvv.hierarchy_level, (select distinct listagg(ffvnh.parent_flex_value,', ') within group (order by ffvnh.parent_flex_value) over () from fnd_flex_value_norm_hierarchy ffvnh where 2=2 and ffvv.summary_flag=decode(ffvnh.range_attribute,'P','Y','N') and ffvv.flex_value between ffvnh.child_flex_value_low and ffvnh.child_flex_value_high and ffvs.flex_value_set_id=ffvnh.flex_value_set_id) parent_values, &value_attributes ffvv.compiled_value_attributes, &dff_columns xxen_util.user_name(ffvv.created_by) created_by, xxen_util.client_time(ffvv.creation_date) creation_date, xxen_util.user_name(ffvv.last_updated_by) last_updated_by, xxen_util.client_time(ffvv.last_update_date) last_update_date, ffvv.flex_value_set_id from fnd_flex_value_sets ffvs, fnd_flex_value_sets ffvs0, fnd_flex_values_vl ffvv, fnd_flex_values ffv, fnd_flex_hierarchies_vl ffhv, fnd_flex_value_norm_hierarchy ffvnh where 1=1 and ffvs.parent_flex_value_set_id=ffvs0.flex_value_set_id(+) and ffvs.flex_value_set_id=ffvv.flex_value_set_id and ffvv.structured_hierarchy_level=ffhv.hierarchy_id(+) and ffvv.flex_value_set_id=ffvnh.flex_value_set_id(+) and ffvv.flex_value=ffvnh.parent_flex_value(+) order by ffvs.flex_value_set_name, ffvv.parent_flex_value_low, ffvv.flex_value, ffvnh.child_flex_value_low 73DFA150D9911E17E0535754720AA8A9 US FND Flex Values V1 Report for all flex values and the hierarchies they are included in. Column 'Hierarchy Position' can be used to validate your account hierarchy setup and check which account segment values are not included in any (or a specific) hierarchy yet. &dff_columns &value_attributes 1=1 2=2 1 10 1=1 ffvv.flex_value_set_id in ( select fifsgv.flex_value_set_id from fnd_id_flexs fif, fnd_id_flex_segments_vl fifsgv where fif.id_flex_name=:id_flex_name and fif.application_id=fifsgv.application_id and fif.id_flex_code=fifsgv.id_flex_code ) LOV FND Key Flexfield 8E2FF36EDF1979D2E0530100007F1FF2 Y select fif.id_flex_name value, fav.application_name||': '||fif.application_table_name||' ('||fif.id_flex_code||')' description from fnd_application_vl fav, fnd_id_flexs fif where fav.application_id=fif.application_id order by fif.id_flex_name F Utilisé par Key Flexfield US Used by Key Flexfield 2 20 1=1 ffvv.flex_value_set_id in ( select fifsgv.flex_value_set_id from fnd_id_flex_structures_vl fifsv, fnd_id_flex_segments_vl fifsgv where fifsv.id_flex_structure_name=:id_flex_structure_name and fifsv.application_id=fifsgv.application_id and fifsv.id_flex_code=fifsgv.id_flex_code and fifsv.id_flex_num=fifsgv.id_flex_num ) LOV FND Key Flexfield Structure A3AB189B606C4736E0530100007FD533 Y select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code||': '||fifsv.description description from fnd_id_flexs fif, fnd_id_flex_structures_vl fifsv where (fifsv.id_flex_code=:$flex$.fnd_key_flexfield_code or fif.id_flex_name=:$flex$.fnd_key_flexfield) and fif.application_id=fifsv.application_id and fif.id_flex_code=fifsv.id_flex_code order by fifsv.id_flex_structure_name F Utilisé par Key Flex Structure US Used by Key Flex Structure 3 30 1=1 ffvs.flex_value_set_name=:flex_value_set_name LOV custom Y select ffvs.flex_value_set_name value, ffvs.description from fnd_flex_value_sets ffvs, fnd_flex_validation_tables ffvt where ( :$flex$.fnd_key_flexfield is null or ffvs.flex_value_set_id in ( select fifsgv.flex_value_set_id from fnd_id_flexs fif, fnd_id_flex_segments_vl fifsgv where fif.id_flex_name=:$flex$.fnd_key_flexfield and fif.application_id=fifsgv.application_id and fif.id_flex_code=fifsgv.id_flex_code ) ) and ( :$flex$.fnd_key_flexfield_structure is null or ffvs.flex_value_set_id in ( select fifsgv.flex_value_set_id from fnd_id_flex_structures_vl fifsv, fnd_id_flex_segments_vl fifsgv where fifsv.id_flex_structure_name=:$flex$.fnd_key_flexfield_structure and fifsv.application_id=fifsgv.application_id and fifsv.id_flex_code=fifsgv.id_flex_code and fifsv.id_flex_num=fifsgv.id_flex_num ) ) and (ffvs.validation_type in ('D','I','X','Y') or ffvs.validation_type='F' and ffvt.summary_allowed_flag='Y') and ffvs.flex_value_set_id in (select ffv.flex_value_set_id from fnd_flex_values ffv) and ffvs.flex_value_set_id=ffvt.flex_value_set_id(+) order by ffvs.flex_value_set_name F Ensemble de valeurs Flex US Flex Value Set 4 &value_attributes select 'xxen_util.meaning(substr(ffvv.compiled_value_attributes,'|| (row_number() over (partition by ffvq.flex_value_set_id order by ffvq.assignment_date,ffvq.value_attribute_type)*2-1) ||',1),'''||fvat.lookup_type||''',0) '|| decode(fvat.value_attribute_type, 'FIN_ITEM_SET_FLAG','financial_category_set', 'LOB_HIER_TOP_NODE','"Line of Business Hier Top Node"', 'DETAIL_BUDGETING_ALLOWED','budgeting_allowed', 'DETAIL_POSTING_ALLOWED','posting_allowed', 'GL_ACCOUNT_TYPE','account_type', 'GL_CONTROL_ACCOUNT','third_party_control', 'RECONCILIATION FLAG','reconcile', 'GL_LEDGER_SET','ledger_set' )||',' text from fnd_flex_value_sets ffvs, fnd_flex_validation_qualifiers ffvq, fnd_value_attribute_types fvat where ffvs.flex_value_set_name=:flex_value_set_name and ffvs.flex_value_set_id=ffvq.flex_value_set_id and ffvq.id_flex_application_id=fvat.application_id and ffvq.id_flex_code=fvat.id_flex_code and ffvq.segment_attribute_type=fvat.segment_attribute_type and ffvq.value_attribute_type=fvat.value_attribute_type order by ffvq.flex_value_set_id, ffvq.assignment_date, ffvq.value_attribute_type F Ensemble de valeurs Flex US Flex Value Set 5 40 1=1 ffvv.parent_flex_value_low=:independent_value Char US Independent Value 6 50 1=1 lower(ffvv.flex_value) like lower(:flex_value_like) Char F Valeur Flex comme US Flex Value like 7 60 1=1 ffvv.created_by=xxen_util.user_id(:created_by) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name F Créé par US Created By 8 70 1=1 ffvv.creation_date>=:creation_date_from Date F Date de création du US Creation Date From 9 80 1=1 ffvv.creation_date<:creation_date_to+1 Date US Creation Date To 10 90 1=1 ffvv.last_updated_by=xxen_util.user_id(:last_updated_by) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name F Dernière mise à jour par US Last Updated By 11 100 1=1 ffvv.last_update_date>=:last_update_date_from Date F Date de la dernière mise à jour du US Last Update Date From 12 110 1=1 ffvv.last_update_date<:last_update_date_to+1 Date US Last Update Date To 13 120 1=1 ffvv.enabled_flag='Y' and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y F Actif uniquement US Active only 14 130 1=1 not exists (select null from fnd_flex_value_hierarchies ffvh where ffvs.flex_value_set_id=ffvv.flex_value_set_id and ffvh.parent_flex_value=:parent_flex_value and ffvv.flex_value between ffvh.child_flex_value_low and ffvh.child_flex_value_high) LOV FND Flex Hierarchy B52C6BB09E788499E0530100007F4F38 Y select distinct ffvnh.parent_flex_value value, (select ffvv.description||case when ffvv.enabled_flag='Y' and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) then null else ' (inactive)' end from fnd_flex_values_vl ffvv where ffvnh.flex_value_set_id=ffvv.flex_value_set_id and ffvv.parent_flex_value_low is null and ffvnh.parent_flex_value=ffvv.flex_value)||' ('||nvl2(ffvnh.is_child,null,xxen_util.meaning('TOP','TOP_BOTTOM_PERF',283)||' ')||xxen_util.meaning('CCONTAIN','ICX_CONDITIONS',0)||' '||xxen_util.meaning(ffvnh.max_range_attribute,'RANGE_ATTRIBUTE',0)||')' description, ffvnh.is_child, ffvnh.max_range_attribute from ( select distinct ffvnh.flex_value_set_id, ffvnh.parent_flex_value, (select 'Y' from gl_seg_val_norm_hierarchy gsvnh where ffvnh.parent_flex_value=gsvnh.child_flex_value and ffvnh.flex_value_set_id=gsvnh.flex_value_set_id and rownum=1) is_child, max(ffvnh.range_attribute) over (partition by ffvnh.parent_flex_value) max_range_attribute from fnd_flex_value_norm_hierarchy ffvnh where ffvnh.flex_value_set_id in ( select ffvs.flex_value_set_id from fnd_flex_value_sets ffvs where ffvs.flex_value_set_name=:$flex$.flex_value_set union all select fifsv.flex_value_set_id from fnd_id_flex_segments_vl fifsv where fifsv.form_left_prompt=:$flex$.hierarchy_segment and fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101) or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y') ) ) ) ffvnh order by ffvnh.is_child desc, ffvnh.max_range_attribute desc, length(ffvnh.parent_flex_value), ffvnh.parent_flex_value F Manquant dans la hiérarchie US Missing in Hierarchy 15 140 1=1 ffvv.summary_flag=decode(xxen_util.lookup_code(:parent_or_child,'DIRECTION_CODE',222),'P','Y','N') LOV custom Y select flv.meaning value, null description from fnd_lookup_values flv where flv.lookup_code in ('P','C') and flv.lookup_type(+)='DIRECTION_CODE' and flv.view_application_id(+)=222 and flv.language(+)=userenv('lang') and flv.security_group_id(+)=0 order by flv.lookup_code desc select xxen_util.meaning('C','DIRECTION_CODE',222) from dual where :$flex$.missing_in_hierarchy is not null F Parent ou enfant US Parent or Child 16 150 2=2 ffvnh.parent_flex_value like :hierarchy_name_like Char F Valeur du parent comme US Parent Value like 17 160 &dff_columns select xxen_util.dff_columns(p_table_name=>'fnd_flex_values',p_table_alias=>'ffv') sql_text from dual LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Show DFF Attributes