Configuring GL Segment Security

GL Segment Security can be applied on the qualified GL Segment Dimensions: 'Dim – Cost Center', 'Dim – Natural Account' & 'Dim – Balancing Segment', as well as the 10 generic dimensions 'Dim – GL Segment1 to 'Dim – GL Segment 10' which are configurable to be any of the accounting segments.

Before setting up the security, you need to first identify which of these segment dimensions you need to apply security on depending on your security requirements and the security setup in the E-Business Suite system. Once that is determined the following steps to configure the RPD metadata need to be repeated for each of the securing segment dimension.

  1. Create a 'row wise' session initialization block and a corresponding session variable to get all the parent nodes the user has access to in a tree. Use the SQL queries and session variable names as given in the table below depending on the dimension that is secured.
    Dimension SQL Variable Name

    Dim – Cost Center

    SELECT DISTINCT 'GL_SEC_COSTCENTER_VALUESETS____EBS', COST_CENTER_LOV_ID FROM W_COST_CENTER_D WHERE ROW_WID > 0

    GL_SEC_COSTCENTER_VALUESETS____EBS

    Dim – Natural Account

    SELECT DISTINCT 'GL_SEC_ACCOUNT_VALUESETS____EBS', NATURAL_ACCOUNT_LOV_ID FROM W_NATURAL_ACCOUNT_D WHERE ROW_WID > 0

    GL_SEC_ACCOUNT_VALUESETS____EBS

    Dim – Balancing Segment

    SELECT DISTINCT 'GL_SEC_BALANCING_VALUESETS____EBS', BALANCING_SEGMENT_LOV_ID FROM W_BALANCING_SEGMENT_D WHERE ROW_WID > 0

    GL_SEC_BALANCING_VALUESETS____EBS

    Dim – GL Segment<n>

    SELECT DISTINCT 'GL_SEC_SEGMENT<n>_VALUESETS____EBS', SEGMENT<n>_ATTRIB FROM W_GLACCT_SEG_CONFIG_TMP

    GL_SEC_SEGMENT<n>_VALUESETS____EBS

    Connection Pool: "Oracle Data Warehouse"."Oracle Data Warehouse Repository Initblocks Connection Pool"

    Note:

    For the generic GL Segment dimensions, Dim – GL Segment 1 - 10, you will need to select the corresponding segment column from W_GLACCT_SEG_CONFIG_TMP which will have all the value sets corresponding to that segment.- Use the default value for these variables as 'Default'.- All the variables created above should end with ____EBS (4 '_' followed by the string EBS). This is for multi source implementation where the same variable can be initialized using multiple SQL's for multiple source systems.
  2. Create a 'row wise' session initialization block and a corresponding session variable to get all the parent nodes the user has access to in a value set. Use the SQL queries and session variable names as given in the table below depending on the dimension that is secured.
    Dimension SQL Variable Name

    Dim – Cost Center

    select DISTINCT 'GL_SEC_COSTCENTER_FILTEREDACCESS____EBS', TO_CHAR(C.FLEX_VALUE_SET_ID) ||'~'||C.FLEX_VALUE from FND_FLEX_VALUE_RULE_USAGES a, FND_FLEX_VALUE_RULE_LINES B, FND_FLEX_VALUES C

    where a.FLEX_VALUE_RULE_ID = B.FLEX_VALUE_RULE_ID and a.FLEX_VALUE_SET_ID = B.FLEX_VALUE_SET_ID and B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID and C.FLEX_VALUE between B.FLEX_VALUE_LOW and B.FLEX_VALUE_HIGH and B.INCLUDE_EXCLUDE_INDICATOR = 'I' and C.SUMMARY_FLAG = 'Y' and TO_CHAR(a.FLEX_VALUE_SET_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_COSTCENTER_VALUESETS____EBS) and TO_CHAR(a.RESPONSIBILITY_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_EBS_RESP_ID) and a.APPLICATION_ID = 101

    GL_SEC_COSTCENTER_FILTEREDACCESS____EBS

    Dim – Natural Account

    select DISTINCT 'GL_SEC_ACCOUNT_FILTEREDACCESS____EBS', TO_CHAR(C.FLEX_VALUE_SET_ID) ||'~'||C.FLEX_VALUE from FND_FLEX_VALUE_RULE_USAGES a, FND_FLEX_VALUE_RULE_LINES B, FND_FLEX_VALUES C

    where a.FLEX_VALUE_RULE_ID = B.FLEX_VALUE_RULE_ID and a.FLEX_VALUE_SET_ID = B.FLEX_VALUE_SET_ID and B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID and C.FLEX_VALUE between B.FLEX_VALUE_LOW and B.FLEX_VALUE_HIGH and B.INCLUDE_EXCLUDE_INDICATOR = 'I' and C.SUMMARY_FLAG = 'Y' and TO_CHAR(a.FLEX_VALUE_SET_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_ACCOUNT_VALUESETS____EBS) and TO_CHAR(a.RESPONSIBILITY_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_EBS_RESP_ID) and a.APPLICATION_ID = 101

    GL_SEC_ACCOUNT_FILTEREDACCESS____EBS

    Dim – Balancing Segment

    select DISTINCT 'GL_SEC_BALANCING_FILTEREDACCESS____EBS', TO_CHAR(C.FLEX_VALUE_SET_ID) ||'~'||C.FLEX_VALUE from FND_FLEX_VALUE_RULE_USAGES a, FND_FLEX_VALUE_RULE_LINES B, FND_FLEX_VALUES C

    where a.FLEX_VALUE_RULE_ID = B.FLEX_VALUE_RULE_ID and a.FLEX_VALUE_SET_ID = B.FLEX_VALUE_SET_ID and B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID and C.FLEX_VALUE between B.FLEX_VALUE_LOW and B.FLEX_VALUE_HIGH and B.INCLUDE_EXCLUDE_INDICATOR = 'I' and C.SUMMARY_FLAG = 'Y' and TO_CHAR(a.FLEX_VALUE_SET_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_BALANCING_VALUESETS____EBS) and TO_CHAR(a.RESPONSIBILITY_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_EBS_RESP_ID) and a.APPLICATION_ID = 101

    GL_SEC_BALANCING_FILTEREDACCESS____EBS

    Dim – GL Segment<n>

    select DISTINCT 'GL_SEC_SEGMENT<n>_FILTEREDACCESS____EBS', TO_CHAR(C.FLEX_VALUE_SET_ID) ||'~'||C.FLEX_VALUE from FND_FLEX_VALUE_RULE_USAGES a, FND_FLEX_VALUE_RULE_LINES B, FND_FLEX_VALUES C

    where a.FLEX_VALUE_RULE_ID = B.FLEX_VALUE_RULE_ID and a.FLEX_VALUE_SET_ID = B.FLEX_VALUE_SET_ID and B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID and C.FLEX_VALUE between B.FLEX_VALUE_LOW and B.FLEX_VALUE_HIGH and B.INCLUDE_EXCLUDE_INDICATOR = 'I' and C.SUMMARY_FLAG = 'Y' and TO_CHAR(a.FLEX_VALUE_SET_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_SEGMENT<n>_VALUESETS____EBS) and TO_CHAR(a.RESPONSIBILITY_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_EBS_RESP_ID) and a.APPLICATION_ID = 101

    GL_SEC_SEGMENT<n>_FILTEREDACCESS____EBS

    Connection Pool: "Oracle EBS OLTP"."Oracle EBS OLTP DbAuth Connection Pool"

    Note:

    • The 2nd highlighted variable name in the SQL comes from the variable names defined in Step 1. Make sure you use the same names.

    • Use the default value for these variables as 'Default'

    • All the variables created above should end with ____EBS (4 '_' followed by the string EBS). This is for multi source implementation where the same variable can be initialized using multiple SQL's for multiple source systems.

  3. Create a 'row wise' session initialization block and a corresponding session variable to get the level in the hierarchy the above nodes fall in a hierarchical value set. Use the SQL queries and session variable names as given in the table below depending on the dimension that is secured.
    Dimension SQL Variable Name

    Dim – Cost Center

    SELECT DISTINCT 'GL_SEC_COSTCENTER_FILTEREDACCESSLEVELS____EBS', FIXED_HIER_LEVEL FROM W_COST_CENTER_DH WHERE LEVEL0_SECURITY_ID IN (VALUELISTOF(NQ_SESSION.GL_SEC_COSTCENTER_FILTEREDACCESS____EBS)) AND CURRENT_FLG='Y'

    GL_SEC_COSTCENTER_FILTEREDACCESSLEVELS____EBS

    Dim – Natural Account

    SELECT DISTINCT 'GL_SEC_ACCOUNT_FILTEREDACCESSLEVELS____EBS', FIXED_HIER_LEVEL FROM W_NATURAL_ACCOUNT_DH WHERE LEVEL0_SECURITY_ID IN (VALUELISTOF(NQ_SESSION.GL_SEC_ ACCOUNT_FILTEREDACCESS____EBS)) AND CURRENT_FLG='Y'

    GL_SEC_ACCOUNT_FILTEREDACCESSLEVELS____EBS

    Dim – Balancing Segment

    SELECT DISTINCT 'GL_SEC_BALANCING_FILTEREDACCESSLEVELS____EBS', FIXED_HIER_LEVEL FROM W_BALANCING_SEGMENT_DH WHERE LEVEL0_SECURITY_ID IN (VALUELISTOF(NQ_SESSION.GL_SEC_ BALANCING_FILTEREDACCESS____EBS)) AND CURRENT_FLG='Y'

    GL_SEC_BALANCING_FILTEREDACCESSLEVELS____EBS

    Dim – GL Segment<n>

    SELECT DISTINCT 'GL_SEC_SEGMENT<n>_FILTEREDACCESSLEVELS____EBS', FIXED_HIER_LEVEL FROM W_GL_SEGMENT_DH WHERE LEVEL0_SECURITY_ID IN (VALUELISTOF(NQ_SESSION.GL_SEC_SEGMENT<n>_FILTEREDACCESS____EBS)) AND CURRENT_FLG='Y'

    GL_SEC_SEGMENT<n>_FILTEREDACCESSLEVELS____EBS

    Connection Pool: "Oracle Data Warehouse"."Oracle Data Warehouse Repository Initblocks Connection Pool"

    Note:

    • The 2nd highlighted variable name in the SQL comes from the variable names defined in Step 2. Make sure you use the same names.

    • Use the default value for these variables as 0.

    • All the variables created above should end with ____EBS (4 '_' followed by the string EBS). This is for multi source implementation where the same variable can be initialized using multiple SQL's for multiple source systems.

  4. Create a 'row wise' session initialization block and a corresponding session variable to get all the value sets to which user has partial access for a given segment. Use the SQL queries and session variable names as given in the table below depending on the dimension that is secured.
    Dimension SQL Variable Name

    Dim – Cost Center

    select DISTINCT 'GL_SEC_COSTCENTER_FILTEREDACCESSVALUESETS____EBS', TO_CHAR(A.FLEX_VALUE_SET_ID) FROM FND_FLEX_VALUE_RULE_USAGES A WHERE TO_CHAR(A.FLEX_VALUE_SET_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_COSTCENTER_VALUESETS____EBS) AND TO_CHAR(A.RESPONSIBILITY_ID) = VALUELISTOF(GL_SEC_EBS_RESP_ID)AND A.APPLICATION_ID = 101

    GL_SEC_COSTCENTER_FILTEREDACCESSVALUESETS____EBS

    Dim – Natural Account

    select DISTINCT 'GL_SEC_ACCOUNT_FILTEREDACCESSVALUESETS____EBS', TO_CHAR(A.FLEX_VALUE_SET_ID) FROM FND_FLEX_VALUE_RULE_USAGES A WHERE TO_CHAR(A.FLEX_VALUE_SET_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_ACCOUNT_VALUESETS____EBS) AND TO_CHAR(A.RESPONSIBILITY_ID) = VALUELISTOF(GL_SEC_EBS_RESP_ID)AND A.APPLICATION_ID = 101

    GL_SEC_ACCOUNT_FILTEREDACCESSVALUESETS____EBS

    Dim – Balancing Segment

    select DISTINCT 'GL_SEC_BALANCING_FILTEREDACCESSVALUESETS____EBS', TO_CHAR(A.FLEX_VALUE_SET_ID) FROM FND_FLEX_VALUE_RULE_USAGES A WHERE TO_CHAR(A.FLEX_VALUE_SET_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_BALANCING_VALUESETS____EBS) AND TO_CHAR(A.RESPONSIBILITY_ID) = VALUELISTOF(GL_SEC_EBS_RESP_ID)AND A.APPLICATION_ID = 101

    GL_SEC_BALANCING_FILTEREDACCESSVALUESETS____EBS

    Dim – GL Segment<n>

    select DISTINCT 'GL_SEC_SEGMENT<n>_FILTEREDACCESSVALUESETS____EBS', TO_CHAR(A.FLEX_VALUE_SET_ID) FROM FND_FLEX_VALUE_RULE_USAGES A WHERE TO_CHAR(A.FLEX_VALUE_SET_ID) = VALUELISTOF(NQ_SESSION.GL_SEC_SEGMENT<n>_VALUESETS____EBS) AND TO_CHAR(A.RESPONSIBILITY_ID) = VALUELISTOF(GL_SEC_EBS_RESP_ID)AND A.APPLICATION_ID = 101

    GL_SEC_SEGMENT<n>_FILTEREDACCESSVALUESETS____EBS

    Connection Pool: "Oracle EBS OLTP"."Oracle EBS OLTP DbAuth Connection Pool"

    Note:

    • The 2nd highlighted variable name in the SQL comes from the variable names defined in Step 1. Make sure you use the same names.

    • Use the default value for these variables as 'Default'.

    • All the variables created above should end with ____EBS (4 '_' followed by the string EBS). This is for multi source implementation where the same variable can be initialized using multiple SQL's for multiple source systems.

    Dimension SQL Variable Name

    Dim – Cost Center

    SELECT DISTINCT 'GL_SEC_COSTCENTER_FULLACCESS____EBS', COST_CENTER_LOV_ID, FROM W_COST_CENTER_D WHERE COST_CENTER_LOV_ID NOT IN VALUELISTOF(NQ_SESSION.GL_SEC_COSTCENTER_FILTEREDACCESSVALUESETS____EBS)

    GL_SEC_COSTCENTER_FULLACCESS____EBS

    Dim – Natural Account

    SELECT DISTINCT 'GL_SEC_ACCOUNT_FULLACCESS____EBS', NATURAL_ACCOUNT_LOV_ID, FROM W_ NATURAL_ACCOUNT _D WHERE NATURAL_ACCOUNT_LOV_ID NOT IN VALUELISTOF(NQ_SESSION.GL_SEC_ACCOUNT_FILTEREDACCESSVALUESETS____EBS)

    GL_SEC_ACCOUNT_ FULLACCESS____EBS

    Dim – Balancing Segment

    SELECT DISTINCT 'GL_SEC_BALANCING_FULLACCESS____EBS', BALANCING_SEGMENT_LOV_ID, FROM W_ BALANCING_SEGMENT_D WHERE BALANCING_SEGMENT _LOV_ID NOT IN VALUELISTOF(NQ_SESSION.GL_SEC_BALANCING_FILTEREDACCESSVALUESETS____EBS)

    GL_SEC_BALANCING_ FULLACCESS TS____EBS

    Dim – GL Segment<n>

    SELECT DISTINCT 'GL_SEC_SEGMENT<n>_FULLACCESS____EBS', SEGMENT<n>_ATTRIB, FROM W_GLACCT_SEG_CONFIG_TMP WHERE SEGMENT<n>_ATTRIB NOT IN VALUELISTOF(NQ_SESSION.GL_SEC_SEGMENT<n>_FILTEREDACCESSVALUESETS____EBS)

    GL_SEC_SEGMENT<n>_ FULLACCESS____EBS

    Connection Pool: "Oracle Data Warehouse"."Oracle Data Warehouse Repository Initblocks Connection Pool"

    Note:

    • For the generic GL Segment dimensions, Dim – GL Segment 1 - 10, you will need to select the corresponding segment column from W_GLACCT_SEG_CONFIG_TMP which will have all the value sets corresponding to that segment.

    • The 2nd highlighted variable name in the SQL comes from the variable names defined in Step 4. Make sure you use the same names.

    • Use the default value for these variables as 'Default'.

    • All the variables created above should end with ____EBS (4 '_' followed by the string EBS). This is for multi source implementation where the same variable can be initialized using multiple SQL's for multiple source systems.

    -

Setting Logical Column Expressions in the BMM Layer

Each dimension has 32 security columns Level 0 Security Id through Level 31 Security Id as shown below.

  1. Modify the expression for each of these logical columns using the hierarchy level variable you created.
    This screenshot is described in surrounding text.

    Each dimension has 32 security columns Level 0 Security Id through Level 31 Security Id as shown below. Modify the expression for each of these logical columns using the hierarchy level variable you created.

  2. Open the logical table source of the dimension that maps to the warehouse dimension table and set the expression for each of these columns using the example from "Dim – Cost Center" dimension.

    For example, if you are securing by "Dim – GL Segment3" and the hierarchy level variable for this segment is "GL_SEC_SEGMENT3_FILTEREDACCESSLEVELS", you would set the expression for each of the "Level <n> Security Id" column:

    INDEXCOL( IFNULL( VALUEOF(<n>, NQ_SESSION."GL_SEC_SEGMENT3_FILTEREDACCESSLEVELS"),  VALUEOF(0, NQ_SESSION."GL_SEC_SEGMENT3_FILTEREDACCESSLEVELS")), 
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_DH_Security_Segment3"."LEVEL31_SECURITY_ID", 
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_DH_Security_Segment3"."LEVEL30_SECURITY_ID", 
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_DH_Security_Segment3"."LEVEL29_SECURITY_ID", 
…and so on for each security id column…
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_DH_Security_Segment3"."LEVEL1_SECURITY_ID", 
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_DH_Security_Segment3"."LEVEL0_SECURITY_ID")

Applying Security Filters in the Data Security Application Notes

Apply security filters to the appropriate segement dimensions.

  1. Navigate to Manage, then Identity from the menu, open the 'General Ledger Data Security' application role and navigate to Permissions, then Data Filters.

    For each of the logical facts secured under this role, you will see some existing filters, which are handling ledger security. You will need to append the segment security filters to this with an 'AND' condition. A snippet of the segment security filters to be appended for a given segment dimension is given below, assuming the security is on 'Dim – GL Segment3' and the session variable prefix used in the previous steps was 'GL_SEC_SEGMENT3'.

    (
"Core"."Dim - GL Segment3"."Segment Value Set Code" IS NULL OR 
((
"Core"."Dim - GL Segment3"."Segment Value Set Code" = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3_FULLACCESS") OR
"Core"."Dim - GL Segment3"."Level 0 Security Id"    = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3_FILTEREDACCESS") OR 
"Core"."Dim - GL Segment3"."Level 1 Security Id"    = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3_FILTEREDACCESS") OR 
"Core"."Dim - GL Segment3"."Level 2 Security Id"    = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3_FILTEREDACCESS") OR 
...and so on for each security id column...
"Core"."Dim - GL Segment3"."Level 30 Security Id"   = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3_FILTEREDACCESS") OR 
"Core"."Dim - GL Segment3"."Level 31 Security Id"   = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3_FILTEREDACCESS")
)
AND 
"Core"."Dim - GL Segment3"."Current Flag Security" = 'Y')
)
  2. Repeat the above for each segment dimension that is secured using appropriate variable names for each segment and appending each block of filters with an AND.

    For example, if you are securing by cost center and segment3 dimensions, the filter will look like this, which includes the ledger security:

    /* Ledger security filters */
(
"Core"."Dim - Ledger"."Key Id" = VALUEOF(NQ_SESSION."LEDGER")
)
/* cost center segment security filters */
AND
 (
"Core"."Dim - Cost Center"."Cost Center Value Set Code" IS NULL OR 
((
"Core"."Dim - Cost Center"."Cost Center Value Set Code" = VALUEOF(NQ_SESSION."GL_SEC_COST_CENTER_FULLACCESS") OR
"Core"."Dim - Cost Center"."Cost Center Level 0 Security Id"    = VALUEOF(NQ_SESSION."GL_SEC_COST_CENTER _FILTEREDACCESS") OR 
"Core"."Dim - Cost Center"."Cost Center Level 1 Security Id"    = VALUEOF(NQ_SESSION."GL_SEC_COST_CENTER _FILTEREDACCESS") OR 
"Core"."Dim - Cost Center"."Cost Center Level 2 Security Id"    = VALUEOF(NQ_SESSION."GL_SEC_COST_CENTER _FILTEREDACCESS") OR 
...and so on for each security id column...
"Core"."Dim - Cost Center"."Cost Center Level 30 Security Id"   = VALUEOF(NQ_SESSION."GL_SEC_COST_CENTER _FILTEREDACCESS") OR 
"Core"."Dim - Cost Center"."Cost Center Level 31 Security Id"   = VALUEOF(NQ_SESSION."GL_SEC_COST_CENTER _FILTEREDACCESS")
)
AND 
"Core"."Dim - Cost Center"."Current Flag Security" = 'Y')
)
/* segment3 security filters */
AND
 (
"Core"."Dim - GL Segment3"."Segment Value Set Code" IS NULL OR 
((
"Core"."Dim - GL Segment3"."Segment Value Set Code" = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3_FULLACCESS") OR
"Core"."Dim - GL Segment3"."Level 0 Security Id"    = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3 _FILTEREDACCESS") OR 
"Core"."Dim - GL Segment3"."Level 1 Security Id"    = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3_FILTEREDACCESS") OR 
"Core"."Dim - GL Segment3"."Level 2 Security Id"    = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3_FILTEREDACCESS") OR 
...and so on for each security id column...
"Core"."Dim - GL Segment3"."Level 30 Security Id"   = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3_FILTEREDACCESS") OR 
"Core"."Dim - GL Segment3"."Level 31 Security Id"   = VALUEOF(NQ_SESSION."GL_SEC_SEGMENT3 _FILTEREDACCESS")
)
AND 
"Core"."Dim - GL Segment3"."Current Flag Security" = 'Y')
)

    Note:

    When a tree has more than one version, the security filters are always applied on the current version for that tree (CURRENT_FLG='Y'). However you can navigate through any other version of the tree in the reports but security will always be applied on the current version.