Oracle Apps Knowledge Sharing

            In this post, we have shared a query which will help you to get the list of valid values of accounting flexifield post applying the security rules for current responsibility.

Key Security Profile Tables:

1. FND_FLEX_VALUE_RULE_USAGES
2. FND_FLEX_VALUE_RULES
3. FND_FLEX_VALUE_RULE_LINES

If you want to know more about above tables and how they are joined, please visit my old post. Security Rules Table Description

Basic Table level Architecture:

Query (R12.2.4):

-- Query for segments where security rules where defined
SELECT hou.short_code,
       hou.organization_id,
       FND_PROFILE.VALUE('RESP_ID') resp_id,
       hou.set_of_books_id ledger_id,
       gl.chart_of_accounts_id,
       fifs.segment_name,
       fifs.application_column_name,
       ffv.flex_value,
       ffv.description
  FROM hr_operating_units hou,
       gl_ledgers gl,
       fnd_id_flex_segments_vl fifs,
       fnd_flex_values_vl ffv,
       fnd_flex_value_rule_usages fvru,
       fnd_flex_value_rules fvr,
       fnd_flex_value_rule_lines fvrl
 WHERE hou.set_of_books_id = gl.ledger_id
   AND fifs.id_flex_num = gl.chart_of_accounts_id
   AND fifs.id_flex_code = 'GL#'
   AND fifs.enabled_flag = 'Y'
   AND fifs.flex_value_set_id = ffv.flex_value_set_id
   AND ffv.enabled_flag = 'Y'
   AND NVL(ffv.summary_flag,'N') = 'N'
   AND SYSDATE BETWEEN hou.date_from AND NVL(hou.date_to,SYSDATE+1)
   AND fvru.responsibility_id  = FND_PROFILE.VALUE('RESP_ID') -- For current resp
   AND fvru.flex_value_rule_id = fvr.flex_value_rule_id
   AND fvrl.flex_value_rule_id = fvr.flex_value_rule_id
   AND ffv.flex_value_set_id   = fvr.flex_value_set_id
   AND 1 = (CASE 
        WHEN fvrl.include_exclude_indicator = 'E' 
             AND (ffv.flex_value BETWEEN fvrl.flex_value_low AND fvrl.flex_value_high)
        THEN 2
        WHEN fvrl.include_exclude_indicator = 'I' 
             AND (ffv.flex_value BETWEEN fvrl.flex_value_low AND fvrl.flex_value_high)
             -- for some ou's first all are included and then some of 
             -- them are excluded. Hence, below logic is required
             AND NOT EXISTS (SELECT 1
                               FROM fnd_flex_value_rule_lines fvrl2
                              WHERE fvrl2.flex_value_rule_id = fvrl.flex_value_rule_id
                                AND fvrl2.include_exclude_indicator = 'E' 
                                AND (ffv.flex_value BETWEEN fvrl2.flex_value_low 
                                                        AND fvrl2.flex_value_high
                                    )
                            )
        THEN 1  
        ELSE 2      
        END
       )
UNION ALL
-- Query for segments where no security rules where defined
SELECT hou.short_code,
       hou.organization_id,
       FND_PROFILE.VALUE('RESP_ID') resp_id,
       hou.set_of_books_id ledger_id,
       gl.chart_of_accounts_id,
       fifs.segment_name,
       fifs.application_column_name,
       ffv.flex_value,
       ffv.description
  FROM hr_operating_units hou,
       gl_ledgers gl,
       fnd_id_flex_segments_vl fifs,
       fnd_flex_values_vl ffv
 WHERE hou.set_of_books_id = gl.ledger_id
   AND fifs.id_flex_num = gl.chart_of_accounts_id
   AND fifs.id_flex_code = 'GL#'
   AND fifs.enabled_flag = 'Y'
   AND fifs.flex_value_set_id = ffv.flex_value_set_id
   AND NVL(ffv.summary_flag,'N') = 'N'
   AND ffv.enabled_flag = 'Y'
   AND SYSDATE BETWEEN hou.date_from AND NVL(hou.date_to,SYSDATE+1)
   AND NOT EXISTS ( SELECT 1
                      FROM fnd_flex_value_rule_usages ffvu
                     WHERE ffvu.responsibility_id = FND_PROFILE.VALUE('RESP_ID')
                       AND ffvu.flex_value_set_id = ffv.flex_value_set_id
                  )