Query to find GL Flexfield security rule assignments to responsibilities

SELECT GLLG.LEDGER_ID,GLLG.NAME, GLLG.LEDGER_CATEGORY_CODE ,

      FLX.APPLICATION_ID, FLX.ID_FLEX_CODE, FLX.ID_FLEX_NAME ,
       STR_TL.ID_FLEX_NUM, STR.ID_FLEX_STRUCTURE_CODE, STR_TL.ID_FLEX_STRUCTURE_NAME ,STR_TL.DESCRIPTION ,
       SEG.SEGMENT_NAME,SEG.SEGMENT_NUM,SEG.APPLICATION_COLUMN_NAME,SEG.ENABLED_FLAG,SEG.SECURITY_ENABLED_FLAG--,SEG.FLEX_VALUE_SET_ID

      ,FLX_RULES.*


FROM FND_ID_FLEXS FLX
    ,FND_ID_FLEX_STRUCTURES_TL STR_TL
    ,FND_ID_FLEX_STRUCTURES STR
    ,FND_ID_FLEX_SEGMENTS SEG
    ,GL_LEDGERS GLLG
 
 ,(SELECT    APS.APPLICATION_NAME          ,
             APS.APPLICATION_ID            ,
             FVR.FLEX_VALUE_RULE_NAME    ,
             RSP.RESPONSIBILITY_ID         RESPONSIBILITY_ID,
             RSP.RESPONSIBILITY_KEY        RESPONSIBILITY,
             FVR.FLEX_VALUE_SET_ID , VS.FLEX_VALUE_SET_NAME, VS.DESCRIPTION FLEX_VALUE_SET_DESC,
             DECODE(FVRL.INCLUDE_EXCLUDE_INDICATOR,'E','EXCLUDE','I','INCLUDE') INC_EXC,
             FVRL.FLEX_VALUE_LOW, FVRL.FLEX_VALUE_HIGH,            
             USR.USER_ID, USR.USER_NAME, USR_GRP.START_DATE, USR_GRP.END_DATE            
           

        FROM FND_FLEX_VALUE_RULES        FVR,
             FND_FLEX_VALUE_RULE_USAGES  RU,
             FND_FLEX_VALUE_RULE_LINES FVRL,
             FND_RESPONSIBILITY          RSP,
             FND_APPLICATION_TL          APS,
             FND_FLEX_VALUE_SETS         VS,
             APPS.FND_USER_RESP_GROUPS USR_GRP,
             APPS.FND_USER USR
           
           
       WHERE  1=1
       AND FVR.FLEX_VALUE_RULE_ID = RU.FLEX_VALUE_RULE_ID
       AND FVR.FLEX_VALUE_RULE_ID = FVRL.FLEX_VALUE_RULE_ID
       AND FVR.FLEX_VALUE_SET_ID = FVRL.FLEX_VALUE_SET_ID    
         AND RU.RESPONSIBILITY_ID   = RSP.RESPONSIBILITY_ID
         AND RU.APPLICATION_ID      = APS.APPLICATION_ID
         AND FVR.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID
       
         AND RSP.RESPONSIBILITY_ID = USR_GRP.RESPONSIBILITY_ID
         AND USR_GRP.USER_ID = USR.USER_ID
     
    )FLX_RULES
 

WHERE 1=1

AND STR.APPLICATION_ID = STR_TL.APPLICATION_ID
and STR.ID_FLEX_CODE = STR_TL.ID_FLEX_CODE
and STR.ID_FLEX_NUM = STR_TL.ID_FLEX_NUM

AND FLX.APPLICATION_ID = STR_TL.APPLICATION_ID
AND FLX.ID_FLEX_CODE = STR_TL.ID_FLEX_CODE
AND STR_TL.LANGUAGE = USERENV('LANG')

AND STR_TL.APPLICATION_ID = SEG.APPLICATION_ID
AND STR_TL.ID_FLEX_CODE = SEG.ID_FLEX_CODE
AND STR_TL.ID_FLEX_NUM = SEG.ID_FLEX_NUM
AND STR_TL.ID_FLEX_NUM = GLLG.CHART_OF_ACCOUNTS_ID


AND FLX_RULES.FLEX_VALUE_SET_ID(+) = SEG.FLEX_VALUE_SET_ID


--AND FLX.ID_FLEX_NAME = 'System Items'
--AND STR_TL.ID_FLEX_STRUCTURE_NAME  = 'System Items'
AND STR.ID_FLEX_STRUCTURE_CODE = 'SYSTEM_ITEMS'
--AND  GLLG.LEDGER_ID = 1078 --SET OF BOOK

Query to find application user responsibilities

select a.USER_NAME, a.EMPLOYEE_ID, b.RESPONSIBILITY_ID, c.RESPONSIBILITY_NAME, b.START_DATE, b.END_DATE
from apps.FND_USER a, apps.FND_USER_RESP_GROUPS b, apps.FND_RESPONSIBILITY_TL c
where a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
and lower(user_name) = 'iftikhar.hussain'

Table Suffixes in Oracle Apps (_ALL, _TL, _VL, _V,_F,_VL,_A,_AVN)

Very common thing, people get confuse why such type of naming convention oracle have used. Here is note for some of them:

• _ALL : Table holds all the information about different operating units. Multi-Org environment. You can also set the client_info to specific operating unit to see the data specific to that operating unit only.
• _TL are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.
• _B these are the BASE tables.
  They are very important and the data is stored in the table with all validations.
  It is supposed that these table will always contain the perfect format data.
  If anything happens to the BASE table data, then it is a data corruption issue.
• _F these are date tracked tables, which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap. Many think they are Secured data. Guess someone from Oracle confirms.
• _V tables are the views created on base tables
• _VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG').
• _S are sequences, used for finding new values for the primary key of a table.
• _A are Audit Shadow Tables
• _AVN and _ACN are Audit Shadow Views (when data was changed, and with what values
• MRC_V
  MRC (Multiple Reporting Currency) is supported by the following Oracle Applications:
  Oracle Assets,Oracle Cash Management, Oracle Cost Management, Oracle General Ledger, Oracle Global Accounting Engine, Oracle Payables, Oracle Receivables, Oracle Purchasing, Oracle Projects
  For the subledgers that support

List of Custom Responsibilities

SELECT DISTINCT fav.application_name, frv.responsibility_key, frv.responsibility_name, frv.description
FROM fnd_responsibility_vl FRV, fnd_application_vl FAV
WHERE FRV.application_id = FAV.application_id
--AND FRV.responsibility_key LIKE 'IHG%'

List of Custom Request Groups

SELECT DISTINCT fav.application_name, frg.request_group_name, frg.description, frg.request_group_code
FROM fnd_request_groups FRG, FND_APPLICATION_VL fav
WHERE frg.application_id = fav.application_id
--AND frg.request_group_code LIKE 'IHG%'

List of Custom Oracle Reports

SELECT DISTINCT FAV.application_name, FCP.user_concurrent_program_name, FCP.concurrent_program_name, FCP.description
FROM fnd_concurrent_programs_vl fcp, FND_EXECUTABLES_FORM_V fef, fnd_application_vl FAV
WHERE fcp.executable_id = fef.executable_id
AND fcp.application_id = FAV.application_id
--AND fcp.concurrent_program_name like 'IHG%'
AND fef.execution_method_code = 'P'