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

Oracle Apps - SQLDEVELOPER is not returning data from Oracle Standard Policy Based Tables even after setting Policy Context

Sometimes SQLDEVELOPER is not returning data from Oracle Standard Policy Based Tables even after setting the policy context

i.e. 

MO_GLOBAL.SET_POLICY_CONTEXT('S', 83);

select * from org_organization_definitions; is returning 0 results

Proposed Solutions: 

Add this line in sqldeveloper.conf file and try one more time

 

AddVMOption -Doracle.net.disableOob=true

Oracle EBS - Query to get responsibility for each OU

Query mostly required when we have to run program from backend for each responsibility

SELECT fpo.profile_option_name

     , frt.responsibility_name resp_name

     , frt.application_id resp_appl_id

     , frt.responsibility_id resp_id

     , fp.profile_option_value org_id

     , fu.user_id

  FROM FND_PROFILE_OPTION_VALUES fp

     , fnd_responsibility_tl frt

     , fnd_profile_options fpo

     , fnd_user fu

 WHERE fp.level_id = 10003 

   AND fp.level_value = frt.responsibility_id

   AND fp.profile_option_id = fpo.profile_option_id

   AND fpo.profile_option_name = 'ORG_ID'

   AND fu.user_name = 'SYSADMIN'

   AND frt.responsibility_name like '%Superuser%'

ORDER BY  fp.profile_option_value,frt.responsibility_name;  

Setup Internet Explorer Mode in Microsoft Edge

Download policy files using following link

 Download Microsoft Edge for Business - Microsoft

Choose => Channel => Build => Platform and download "GET POLICY FILES" 

Uncompress "MicrosoftEdgePolicyTemplates.cab" file => "MicrosoftEdgePolicyTemplates.zip" =>

Go to "Windows" folder

Copy both folders "adm" and "admx" and paste to "c:\windows"

go to "Desktop\Edge\MicrosoftEdgePolicyTemplates\windows\admx" and paste following to "C:\Windows\PolicyDefinitions"

-   en-US <folder>

- msedge.admx

- msedgeupdate.admx

- msedgewebview2.admx

----

Now find 

Enable following

• Allows you to set whether Enterprise Mode Site List Manager 
• Configure Internet Explorer integration
• Configure the Enterprise Mode Site List
• Allow Internet Explorer Mode Testing

Now we can see 2 options in "More Settings"

• Open sites in Internet Explorer mode
• Open sites in Edge mode

Hurry!!!!!!!

Remove Junk or unwanted columns from data in Oracle/SQL/ PL/SQL using REGEXP_REPLACE

update hz_locations 

set address1=REGEXP_REPLACE(address1, '[^0-9A-Za-z/. ()-_]', ' ') 

where length(address1) <> lengthb(address1);