Oracle EBS - Multi Org Tables - MO_GLOBAL Set Policy / Org - AOL Tables

 
Setup Environment on Toad/SQL Developer
-------------------------------------------
BEGIN MO_GLOBAL.SET_POLICY_CONTEXT('S',101); END;
Note: SQL Developer is not providing data after that setting then check NLS_LANG parameter

HR_ALL_ORGANIZATION_UNITS
HR_OPERATING_UNITS
ORG_ORGANIZATION_DEFINITIONS => Inventory Org for OU
HRFV_BUSINESS_GROUPS => Business Group
HR_LEGAL_ENTITIES => LE
GL_LEDGERS => Ledger
GL_PERIODS_V
GL_PERIODS
GL_PERIOD_TYPES
FND_CURRENCIES
GL_CODE_COMBINATIONS
GLFV_CHARTS_OF_ACCOUNTS => Chart of accounts
AOL Tables
=====================
AD_APPLIED_PATCHES
AD_BUGS
FND_ENV_CONTEXT
FND_PRODUCT_GROUPS
----
FND_APPLICATION
FND_APPLICATION_VL
----
FND_CONCURRENT_PROGRAMS_TL
FND_CONCURRENT_REQUESTS
----
FND_DESCRIPTIVE_FLEXS_VL
FND_DESCR_FLEX_COL_USAGE_VL
FND_DESCR_FLEX_CONTEXTS_VL
----
FND_FLEX_VALUE_SETS
FND_FLEX_VALUE_SETS
FND_FLEX_VALUES
----
FND_ID_FLEX_SEGMENTS
FND_ID_FLEX_STRUCTURES
FND_ID_FLEX_STRUCTURES_TL
----
FND_LOOKUP_TYPES
FND_LOOKUP_VALUES
----
FND_PROFILE_OPTION_VALUES
FND_PROFILE_OPTIONS
----
FND_REQUEST_GROUP_UNITS
FND_REQUEST_GROUPS
----
FND_REQUEST_SETS_TL
----
FND_RESPONSIBILITY
FND_RESPONSIBILITY_TL
FND_SEGMENT_ATTRIBUTE_VALUES

FND_USER

Query to get values of the Independent/Dependent Value Set

The query will provide the value set values entered from the front-end. For query-based value sets, we need to use direct queries.

SELECT ffvs.flex_value_set_id

     , ffvs.flex_value_set_name

     , ffvs.description 
     , ffvs.validation_type

     , ffv.flex_value

     , ffvt.description

     , ffv.enabled_flag
     , ffv.last_update_date

     , ffv.last_updated_by
FROM fnd_flex_value_sets ffvs

   , fnd_flex_values ffv

   , fnd_flex_values_tl ffvt
WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id
  and ffv.flex_value_id = ffvt.flex_value_id
  and ffvt.language = 'US'
  and flex_value_set_name in ('PROJECT_TYPE')
ORDER BY flex_value_set_name, flex_value asc
 

Query - Get DFF Structure Information

Following is the query, which can provide the DFF structure on a specific table in Oracle EBS/Fusion.

 

SELECT ffv.descriptive_flexfield_name DFF_Name,
ffv.application_table_name App_Table_Name,
ffv.title ,
ap.application_name ,
ffc.descriptive_flex_context_code Context_Code,
ffc.descriptive_flex_context_name Context_Name,
ffc.description Context_Desc,
ffc.enabled_flag Context_Enable_Flag,
att.column_seq_num Segment_Number,
att.form_left_prompt Segment_Name,
att.application_column_name app_column_name,
fvs.flex_value_set_name Value_Set_Name,
att.display_flag ,
att.enabled_flag ,
att.required_flag 
FROM fnd_descriptive_flexs_vl ffv,
     fnd_descr_flex_contexts_vl ffc,
     fnd_descr_flex_col_usage_vl att,
     fnd_flex_value_sets fvs,
     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.application_table_name like 'PJF_PROJECTS_ALL%'
ORDER BY att.column_seq_num