Extracting PSFT Chartfield Hierarchy Data in OBIA 11.1.1.7.1

BI Apps 11.1.1.7.1 supports 26 chartfields with 3 of them considered to be mandatory chartfields and are mapped to specific dimensions as shown below.

Department – Cost Center Dimension

Fund – Balancing Segment Dimension

Account – Natural Account Dimension

All the remaining chartfields are mapped to one specific dimension which is GL Segment Dimension. The physical table is shared for all chartfields but there are multiple logical dimensions in the RPD which you can use. You will need to apply the appropriate filters for these logical dimensions to filter the data relevant to those chartfields.

If you want to extract the trees defined for these chartfields into the DW, you will need to configure some parameters as explained below. For each chartfield, there are two sets of parameters that you can configure.

• · List of Tree Structures
• · List of Trees within the Tree Structures configured as in 1.

1. The first parameter is considered to be a mandatory parameter and you will need to configure this parameter by giving a comma separated list of all the Tree Structures you need to extract for that Chartfield.
2. The second parameter is considered to be an optional parameter. If you don’t configure this parameter then all the Trees pertaining to the Tree Structures configured in 1 will be extracted. If you need only specific trees to be extracted within those tree structures, then configure this parameter by giving a comma separated list of SETID + Tree Name.

The actual parameter names for each dimension are given in the table below

Dimension	Parameter Name
Cost Center	TREE_STRUCT_ID_LIST
	TREE_SETID_NAME_LIST
Balancing Segment	TREE_STRUCT_ID_LIST
	TREE_SETID_NAME_LIST
Natural Account	TREE_STRUCT_ID_LIST
	TREE_SETID_NAME_LIST
GL Segment	TREE_STRUCT_ID_LIST_<CF>
	TREE_SETID_NAME_LIST_<CF>

Since GL Segment dimension supports multiple Chartfields we have one set of parameters for each chartfield. You will need to configure those many parameters based on the number of Chartfields you need in BI.

For e.g. say you have configured the program chartfield, then you will need to configure TREE_STRUCT_ID_LIST_PROGRAM and TREE_SETID_NAME_LIST_PROGRAM. Screenshots for one such set of parameters along with the sample values are given below

The following are the FSM tasks you need to configure for setting these parameters:

1) Configure Data Load Parameters for People Soft Trees

2) Configure Trees to be extracted for GL Account Chartfields for PeopleSoft

The following section gives you some sample SQL’s which you can use as a reference to get the values of these parameters for each chartfield. Please note that these are to be used just as references and you will have to validate the parameter values before you configure them.

1) For Tree Structures

SELECT TREE_STRCT_ID FROM PSTREESTRCT WHERE DTL_RECNAME=<Detail Table of your CF>;

E.g. for the Department chartfield which maps to the Cost Center Dimension you can use

SELECT TREE_STRCT_ID FROM PSTREESTRCT WHERE DTL_RECNAME='DEPT_TBL';

2) For Trees within Tree Structures

SELECT A.SETID||'~'||A.TREE_NAME FROM PSTREEDEFN A, PSTREESTRCT B WHERE A.TREE_STRCT_ID=B.TREE_STRCT_ID AND B.DTL_RECNAME=<Detail Table of your CF>;

E.g. for the Department chartfield which maps to the Cost Center Dimension you can use

SELECT A.SETID||'~'||A.TREE_NAME FROM PSTREEDEFN A, PSTREESTRCT B WHERE A.TREE_STRCT_ID=B.TREE_STRCT_ID AND B.DTL_RECNAME='DEPT_TBL';

Note:

1) This feature allows you to extract multiple effective dated trees as well. By default the out of the box code extracts only the current version of any tree.

2) If you need to extract multiple effective dated versions, then you will need to set the value of the parameter TREE_CURRENT_VERSION_ONLY to N.

3) There is a patch which you need to apply when you extract multiple versions or else the code creates duplicates for each version. Please check with the support team to get the relevant patch.