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.

Table A–88 Initialization Blocks and Session Variables

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||'~'||D EFN.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||'~'||D EFN.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

Table A–88 (Cont.) Initialization Blocks and Session Variables

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

Table A–88 (Cont.) Initialization Blocks and Session Variables

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

Notes:

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

Table A–88 (Cont.) Initialization Blocks and Session Variables

- 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