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 chartfields.

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 Commitment Control module. 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_FILTEREDACCESS____PSFT', 'Department'||'~'||DEFN.SETID||'~'||DEFN.TREE_NAME||'~'||DEFN.TREE_NODE FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN, PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_OPR_RULES OPR WHERE OPR.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='DEPTID' AND OPR.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    UNION

    SELECT DISTINCT 'GL_SEC_COSTCENTER_FILTEREDACCESS____PSFT', 'Department'||'~'||DEFN.SETID||'~'||DEFN.TREE_NAME||'~'||DEFN.TREE_NODE FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN,PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_CLSS_RULES CLSS, PSOPRDEFN OP, PSROLEUSER ORL, PSROLECLASS RCL WHERE CLSS.OPRCLASS = RCL.CLASSID AND OP.OPRID = ORL.ROLEUSER AND ORL.ROLENAME = RCL.ROLENAME AND CLSS.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='DEPTID' AND OP.OPRID = 'VALUEOF(NQ_SESSION.USER)

    GL_SEC_COSTCENTER_FILTEREDACCESS____PSFT

    Dim – Natural Account

    SELECT DISTINCT 'GL_SEC_ACCOUNT_FILTEREDACCESS____PSFT', 'Account'||'~'||DEFN.SETID||'~'||DEFN.TREE_NAME||'~'||DEFN.TREE_NODE FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN, PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_OPR_RULES OPR WHERE OPR.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='ACCOUNT' AND OPR.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    UNION

    SELECT DISTINCT 'GL_SEC_ACCOUNT_FILTEREDACCESS____PSFT', 'Account'||'~'||DEFN.SETID||'~'||DEFN.TREE_NAME||'~'||DEFN.TREE_NODE FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN,PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_CLSS_RULES CLSS, PSOPRDEFN OP, PSROLEUSER ORL, PSROLECLASS RCL WHERE CLSS.OPRCLASS = RCL.CLASSID AND OP.OPRID = ORL.ROLEUSER AND ORL.ROLENAME = RCL.ROLENAME AND CLSS.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='ACCOUNT' AND OP.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    GL_SEC_ACCOUNT_FILTEREDACCESS____PSFT

    Dim – Balancing Segment

    SELECT DISTINCT 'GL_SEC_BALANCING_FILTEREDACCESS____PSFT', 'Fund Code'||'~'||DEFN.SETID||'~'||DEFN.TREE_NAME||'~'||DEFN.TREE_NODE FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN, PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_OPR_RULES OPR WHERE OPR.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='FUND_CODE' AND OPR.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    UNION

    SELECT DISTINCT 'GL_SEC_BALANCING_FILTEREDACCESS____PSFT', 'Fund Code'||'~'||DEFN.SETID||'~'||DEFN.TREE_NAME||'~'||DEFN.TREE_NODE FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN,PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_CLSS_RULES CLSS, PSOPRDEFN OP, PSROLEUSER ORL, PSROLECLASS RCL WHERE CLSS.OPRCLASS = RCL.CLASSID AND OP.OPRID = ORL.ROLEUSER AND ORL.ROLENAME = RCL.ROLENAME AND CLSS.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='FUND_CODE' AND OP.OPRID = 'VALUEOF(NQ_SESSION.USER) '

    GL_SEC_BALANCING_FILTEREDACCESS____PSFT

    Dim – GL Segment<n>

    SELECT DISTINCT 'GL_SEC_SEGMENT<n>_FILTEREDACCESS____PSFT', '< ChartfieldString>'||'~'||DEFN.SETID||'~'||DEFN.TREE_NAME||'~'||DEFN.TREE_NODE FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN, PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_OPR_RULES OPR WHERE OPR.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='<ChartfieldCode>' AND OPR.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    UNION

    SELECT DISTINCT 'GL_SEC_SEGMENT<n>_FILTEREDACCESS____PSFT', '< ChartfieldString>'||'~'||DEFN.SETID||'~'||DEFN.TREE_NAME||'~'||DEFN.TREE_NODE FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN,PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_CLSS_RULES CLSS, PSOPRDEFN OP, PSROLEUSER ORL, PSROLECLASS RCL WHERE CLSS.OPRCLASS = RCL.CLASSID AND OP.OPRID = ORL.ROLEUSER AND ORL.ROLENAME = RCL.ROLENAME AND CLSS.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='<ChartfieldCode>' AND OP.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    GL_SEC_SEGMENT<n>_FILTEREDACCESS____PSFT

    Connection Pool: "PeopleSoft OLTP"."PeopleSoft OLTP DbAuth Connection Pool"

    Note:

    For the Dim – GL Segment<n> init blocks, use the appropriate chartfield string and the chartfield code based on the chartfield you are securing. You can get the chartfield code from the PeopleSoft source system and the chartfield string should match the names used in file_glacct_segment_config_psft.csv file.- Use the default value for these variables as 'Default'.- All the variables created above should end with ____PSFT (4 '_' followed by the string PSFT). This is for multi source implementation where the same variable can be initialized using multiple SQL statements for multiple source systems.
  2. 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 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_FILTEREDACCESSLEVELS____PSFT', FIXED_HIER_LEVEL FROM W_COST_CENTER_DH WHERE LEVEL0_SECURITY_ID IN (VALUELISTOF(NQ_SESSION.GL_SEC_COSTCENTER_FILTEREDACCESS____ PSFT)) AND CURRENT_FLG='Y'

    GL_SEC_COSTCENTER_FILTEREDACCESSLEVELS____PSFT

    Dim – Natural Account

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

    GL_SEC_ACCOUNT_FILTEREDACCESSLEVELS____PSFT

    Dim – Balancing Segment

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

    GL_SEC_BALANCING_FILTEREDACCESSLEVELS____PSFT

    Dim – GL Segment<n>

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

    GL_SEC_SEGMENT<n>_FILTEREDACCESSLEVELS____PSFT

    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 1. Make sure you use the same names.

    • Use the default value for these variables as 0.

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

  3. Create a 'row wise' session initialization block and a corresponding session variable to get all the SETIDs 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____PSFT', 'Department'||'~'||DEFN.SETID FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN, PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_OPR_RULES OPR WHERE OPR.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='DEPTID' AND OPR.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    UNION

    SELECT DISTINCT 'GL_SEC_COSTCENTER_FILTEREDACCESSVALUESETS____PSFT', 'Department'||'~'||DEFN.SETID FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN,PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_CLSS_RULES CLSS, PSOPRDEFN OP, PSROLEUSER ORL, PSROLECLASS RCL WHERE CLSS.OPRCLASS = RCL.CLASSID AND OP.OPRID = ORL.ROLEUSER AND ORL.ROLENAME = RCL.ROLENAME AND CLSS.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='DEPTID' AND OP.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    GL_SEC_COSTCENTER_FILTEREDACCESSVALUESETS____PSFT

    Dim – Natural Account

    SELECT DISTINCT 'GL_SEC_ACCOUNT_FILTEREDACCESSVALUESETS____PSFT', 'Account'||'~'||DEFN.SETID FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN, PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_OPR_RULES OPR WHERE OPR.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='ACCOUNT' AND OPR.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    UNION

    SELECT DISTINCT 'GL_SEC_ACCOUNT_FILTEREDACCESSVALUESETS____PSFT', 'Account'||'~'||DEFN.SETID FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN,PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_CLSS_RULES CLSS, PSOPRDEFN OP, PSROLEUSER ORL, PSROLECLASS RCL WHERE CLSS.OPRCLASS = RCL.CLASSID AND OP.OPRID = ORL.ROLEUSER AND ORL.ROLENAME = RCL.ROLENAME AND CLSS.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='ACCOUNT' AND OP.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    GL_SEC_ACCOUNT_FILTEREDACCESSVALUESETS____PSFT

    Dim – Balancing Segment

    SELECT DISTINCT 'GL_SEC_BALANCING_FILTEREDACCESSVALUESETS____PSFT', 'Fund Code'||'~'||DEFN.SETID FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN, PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_OPR_RULES OPR WHERE OPR.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='FUND_CODE' AND OPR.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    UNION

    SELECT DISTINCT 'GL_SEC_BALANCING_FILTEREDACCESSVALUESETS____PSFT', 'Fund Code'||'~'||DEFN.SETID FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN,PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_CLSS_RULES CLSS, PSOPRDEFN OP, PSROLEUSER ORL, PSROLECLASS RCL WHERE CLSS.OPRCLASS = RCL.CLASSID AND OP.OPRID = ORL.ROLEUSER AND ORL.ROLENAME = RCL.ROLENAME AND CLSS.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='FUND_CODE' AND OP.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    GL_SEC_BALANCING_FILTEREDACCESSVALUESETS____PSFT

    Dim – GL Segment<n>

    SELECT DISTINCT 'GL_SEC_SEGMENT<n>_FILTEREDACCESSVALUESETS____PSFT', '<ChartfieldString>'||'~'||DEFN.SETID FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN, PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_OPR_RULES OPR WHERE OPR.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='<ChartfieldCode>' AND OPR.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    UNION

    SELECT DISTINCT 'GL_SEC_SEGMENT<n>_FILTEREDACCESSVALUESETS____PSFT', '<ChartfieldString>'||'~'||DEFN.SETID FROM PS_KSEC_RULES RULES, PS_KSEC_RULES_DEFN DEFN,PS_KSEC_RULES_EVEN EVENTS, PS_KSEC_CLSS_RULES CLSS, PSOPRDEFN OP, PSROLEUSER ORL, PSROLECLASS RCL WHERE CLSS.OPRCLASS = RCL.CLASSID AND OP.OPRID = ORL.ROLEUSER AND ORL.ROLENAME = RCL.ROLENAME AND CLSS.KSEC_RULE=EVENTS.KSEC_RULE AND EVENTS.KSEC_RULE=RULES.KSEC_RULE AND RULES.KSEC_RULE=DEFN.KSEC_RULE AND EVENTS.KSEC_EVENT='INQUIRE' AND RULES.KSEC_ATTRIB='A' AND DEFN.KSEC_RULE_PARAM='TRE' AND DEFN.CHARTFIELD='<ChartfieldCode>' AND OP.OPRID = 'VALUEOF(NQ_SESSION.USER)'

    GL_SEC_SEGMENT<n>_FILTEREDACCESSVALUESETS____PSFT

    Connection Pool: "PeopleSoft OLTP"."PeopleSoft OLTP DbAuth Connection Pool"

    Note:

    • For the Dim – GL Segment<n> init blocks, use the appropriate chartfield string and the chartfield code based on the chartfield you are securing. You can get the chartfield code from the PeopleSoft source system and the chartfield string should match the names used in file_glacct_segment_config_psft.csv file.

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

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

  4. Create a 'row wise' session initialization block and a corresponding session variable to get all the SETIDs to which user has full access for a given chartfield. 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_FULLACCESS____PSFT', COST_CENTER_LOV_ID, FROM W_COST_CENTER_D WHERE COST_CENTER_LOV_ID NOT IN VALUELISTOF(NQ_SESSION.GL_SEC_COSTCENTER_FILTEREDACCESSVALUESETS____PSFT)

    GL_SEC_COSTCENTER_FULLACCESS____PSFT

    Dim – Natural Account

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

    GL_SEC_ACCOUNT_ FULLACCESS____PSFT

    Dim – Balancing Segment

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

    GL_SEC_BALANCING_ FULLACCESS TS____PSFT

    Dim – GL Segment<n>

    SELECT DISTINCT 'GL_SEC_SEGMENT<n>_FULLACCESS____PSFT', SEGMENT_LOV_I FROM, W_GL_SEGMENT_D WHERE SEGMENT_LOV_ID NOT IN VALUELISTOF(NQ_SESSION.GL_SEC_SEGMENT<n>_FILTEREDACCESSVALUESETS____PSFT) AND SEGMENT_LOV_ID LIKE '<ChartfieldString>%'

    GL_SEC_SEGMENT<n>_ FULLACCESS____PSFT

    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 apply an appropriate filter to filter the SETIDs applicable for that chartfield. You can apply a filter on the chartfield string column which should be exactly similar to the one name used in file_glacct_segment_config_psft.csv file.

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

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

    • All the variables created above should end with ____PSFT (4 '_' followed by the string PSFT). 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.
  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', then you would set the expression for each of the 'Level <n> Security Id' column:

    INDEXCOL( IFNULL( VALUEOF(<n>, NQ_SESSION."GL_SEC_PROGRAM_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")
  3. Repeat the above steps for each of the segment dimension to be secured.

Applying Security Filters in the Data Security Application Roles

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_FILTERE+CESS") 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.