Demystifying SetIDs and TableSet Sharing

TableSet sharing is used in PeopleSoft to share configuration data among various institutions, business units, regulatory regions, and so forth. It is manifested in Prompt Tables that reference the underlying configuration records used in TableSet sharing (i.e. the records that include SetID as one of its key fields).

TableSet sharing is enabled by the use of SetIDs. Each configuration table that is to be used in TableSet sharing will include SetID as one of the key fields – thus allowing the data in the table to be split into different “sets”. Each one of these “sets” is then associated with a business unit (more on this later in the article) – thus allowing multiple business units to use the same SetID, and consequently share the same configuration data. Please NOTE – each “set” can be associated not just to a business unit; it could be associated with institution and regulatory region (as is the case in PeopleSoft Campus Solutions), and other fields – more specifically, it can be associated with whatever set control field is used. In PeopleSoft Financials, business unit is the most commonly used set control field. As already mentioned, institution and regulatory region are other commonly used ones in other PeopleSoft products, and there are others.

A great example of TableSet sharing is Account Chartfield in PeopleSoft Financials – General Ledger (GL) module. A single set of account values can be created and used by all business units, or multiple sets of account values can be created and shared by one or more specific business units. When transactions are entered, Application Processor makes the connection between the business unit, SetID and table values in order to build prompt lists.

Since TableSet sharing is generally most heavily used in PeopleSoft Financials, this article will concentrate on the use of TableSet sharing in General Ledger (GL). However, the same principles discussed here apply to any PeopleSoft product, not just PeopleSoft Financials – GL.

The explanation below has already been written by an unnamed author, without copyright – or at least, I found the article in such form! I decided to include this useful and insightful explanation of TableSet sharing, for the benefit of a wider PeopleSoft community. If you are the author of the following text, then please let the site administration (Praj Basnet) know – he would be happy to take it off the site if you object, or cite a reference to your article if you’re happy for it to remain on this site.

SetIDs enable what PeopleSoft calls "TableSet sharing." The idea is that a SetID value can be used to allow multiple business units to share a common set of setup table values. For instance, this works great for managing chartfield values. Consider the account chartfield. A single set of account values can be created and used by all business units, or multiple sets of account values can be created and shared by one or more specific business units. When transactions are entered, Application Processor makes the connection between the business unit, SetID and table values in order to build prompt lists. In this article I’ll discuss the logic employed by Application Process in building prompt lists and provide examples of the SQL that is generated as transactions are entered.

First, lets look at how SetIDs are created. SetIDs can be created in one of two ways. The first is during the process of adding a business unit. Consider how a business unit is added to General Ledger. You navigate to Go, Establish Business Units, Use A-M, General Ledger Definition. From this panel group you enter information about the business unit and a value for the Copy Unit for SetIds field. The value for this field can be an existing SetID or the business unit that is being added. If the value entered matches the business unit being created, both a SetID and a set control group are created for the business unit. All of the SetID values in the set control group will be set to the new business unit. If you enter an existing SetID, a new SetID and set control group are also created but the set control group will be modeled after the existing set control group. The SetID values in the new set control group will match those in the old set control group. The second way is to add a new SetID directly by navigating to Go, PeopleTools, Utilities, Use, TableSet ID. When a new SetID is added from this panel group, a set control record is also created. All of the SetID values in the set control group will be set to the new SetID. In both cases, either by adding a new SetID directly or by adding a new business unit, a SetID and an associated set control group will be created.

Let’s look at an example. We can create a new business unit and have the new set control group modeled after an existing set control group. In your demo database navigate to Go, Define Business Rules, Establish Business Units, Use A-M, General Ledger. Add a new business unit called M300. Enter the descriptions, base currency, and ‘M01’ for the Copy Unit for SetIDs field. Press the Create BU button. This will generate the new SetID and set control group records. Exit from this panel and navigate to Go, Utilities, Use, TableSet ID, Update/Display. Enter ‘M300’ for the SetID. Notice that the record already exists and the descriptions match the descriptions for the business unit we created. Now let’s look at the set control group. From the Utilities menu, navigate to Use, TableSet Control, and select the first option. Enter ‘M300’ for the set control value. Again, the record already exists because it was created when we created the business unit. And, notice that each of the SetID values has already been set. The values displayed here match the values for the ‘M01’ set control group. If we had entered ‘M300’ in the Copy Unit for SetIDs field when we created the business unit all of the SetID values would be set to ‘M300’.

You have now seen how a SetID and a set control group are created. Let’s take a look at how they relate to prompt tables. In our M300 set control group look at the SetID for record group FS_05. The SetID is ‘MFG’. In the Utilities menu navigate to Use, Record Group, Update/Display and enter ‘FS_05’ for the Record Group ID. This is the Accounts record group and it contains all of the records used throughout financials for prompting on account values. Scroll down the list and you should see GL_ACCOUNT_TBL. This is the record that contains all the account values for General Ledger. Remember in our set control group the SetID next to the FS_05 record group is set to ‘MFG’. This means that for any record in the FS_05 record group, use a default SetID value of ‘MFG’. Why is this important? If you look at any one of these records in Application Designer you’ll see that SetID is part of the key. When the system selects a set of accounts from GL_ACCOUNT_TBL to show in a prompt list it must decide which SetID value to select. If you look at the data in the GL_ACCOUNT_TBL you’ll find that there are three distinct SetID values –‘FS’,‘HC’, and ‘MFG’. So, the system has three sets of accounts each designated by a separate SetID value to choose from when it builds a prompt list.

Now let’s see how Application Processor will decide which list to display. To do this lets create a journal entry for our new business unit. First, navigate to Go, Define Business Rules, Establish Business Units, Use A-M, Ledgers for a Unit, and pick the first option. Enter ‘M300’ for the business unit. We need to define which ledger the business unit uses. Select Detail Ledger and enter ‘Actuals’ in the Ledger Group field. Enter ‘01’ in Calendar and check Jrnl Generator Dflt Ledger Grp. This will set the Ledger for our business unit to Actuals and allow us to create a journal.

Before we enter the journal lets look at a couple things in Application Designer. Open the GL_ACCOUNT_TBL and view its properties. Go to the Use tab and look at the Set Control Field. Notice that it is set to ‘BUSINESS_UNIT’. Remember earlier when we looked at our set control group the system prompted for a Set Control Value and we entered ‘M300’. Application Processor needs to know how to access the set control group to use with this record. By putting ‘BUSINESS_UNIT’ in this field we’re telling it to default to the business unit contained on the panel when the table is accessed. For instance, when we enter a journal entry we have to tell the system what business unit we’re going to create the journal for. Whatever business unit value we enter is the value that will be used as the set control value to find the set control group.

Now open the JRNL_LN record. Look at the Edits Dsiplay (click the Check Mark icon on the toolbar). Double-click the Account field. Notice that there is a Set Control Value here as well. When the type is set to ‘Prompt Table Edit’, the Set Control Value field will be active. If we want to override the value that will be used to locate the set control group to use, we would do it here. For example, suppose we were working in Accounts Payable and we wanted to access a list of GL accounts. Because Accounts Payable has its own set of business units that are mapped to GL business units using the field Business_Unit_GL, we would want to override the Set Control Value that is on the GL_ACCOUNT_TBL with BUSINESS_UNIT_GL. This would allow us to do just that.

Before we go through the example lets look at the steps Application Processor will go through to build a query for locating the list of prompt values. First, it has to know what values to use for each of the keys on the table. The table we want to look at is GL_ACCOUNT_TBL. The keys to the table are SetID, Account and EffDT. How will it resolve SetID? First it will look for a SetID on the panel. In the journal entry panels there is no SetID field. Next it will look at the GL_ACCOUNT_TBL record and see if there is a value in the Set Control Value field. In our case this value is set to ‘BUSINESS_UNIT’. Because of this it will look for a business unit on the panel. Of coarse it will find one and use this value to find the set control group. Suppose there was no business unit on the panel. It would then look at the record where the prompt table was defined to see if there was a set control value specified there. In our AP example we would have put ‘BUSINESS_UNIT_GL’ in which case it would search the panel for a business unit GL to access the set control group. It won’t try to resolve Account because it knows that we’re looking for a list of valid accounts. It knows this because the field that the prompt table (GL_ACCOUNT_TBL) has been associated with is Account. And for EffDT it will add the required sub-query to find the maximum effective dated record.

Now let’s look at the SQL when we access the account table from the journal entry panel. Turn on SQL tracing and then navigate to the journal entry panel. To get to the journal entry panel, navigate to Go, Process Financial Information, Process Journals, Use, Journal Entry, Journal Entry Header, Add, you can add a journal entry for our new business unit. For the Business Unit field, enter ‘M300’ and leave the remaining fields with the default values. Go to the Lines panel and navigate to the Account field on the journal line. Press the ‘F4’ key to see the list of values. Notice the SetID in the prompt list is ‘MFG’. Why? Because the SetId in the set control group is ‘MFG’ for record group FS_05, and GL_ACCOUNT_TBL belongs to the FS_05 record group. The queries you will see in the trace output include the following:

SELECT RECNAME, SETID, SETCNTRLVALUE
FROM PS_SET_CNTRL_REC
WHERE SETCNTRLVALUE = 'M300'
ORDER BY SETCNTRLVALUE, RECNAME

This loads a list of all the record names and SetIDs associated with the M300 set control group. The SET_CNTRL_REC table goes one level deeper than the TableSet Control panel we looked at earlier. It resolves the record groups into the records that each contains and provides a link between the set control value, the record name and the SetID to be used. Notice that the SETCNTRLVALUE for returned rows is equal to ‘M300’. From this table you can figure out exactly which SetID to use for the set control value of ‘M300’ and the ‘GL_ACCOUNT_TBL’ record. All this detail is automatically loaded into the swap files and will be used to generate queries for building prompt lists while the M300 business unit is being used.

SELECT SETID, ACCOUNT, DESCR, ACCOUNT_TYPE
FROM PS_GL_ACCOUNT_TBL A
WHERE SETID LIKE 'MFG%'
AND EFFDT= (SELECT MAX(B.EFFDT)
FROM PS_GL_ACCOUNT_TBL B
WHERE B.SETID=A.SETID
AND B.ACCOUNT=A.ACCOUNT
AND B.EFFDT<=TO_DATE('1998-12-22',
'YYYY-MM-DD'))
AND EFF_STATUS<>'I' ORDER BY SETID, ACCOUNT

This is the query that will find the list of prompt values. Using the information from the first query, Application Processor knows that the SetID for the set of accounts to be displayed is ‘MFG’. This is because for our set control group identified by the set control value of ‘M300’ has a SetID value of ‘MFG’ associated with the record group ‘FS_05’, and GL_ACCOUNT_TBL is in the record group FS_05.

This is a simple example of how Application Processor uses SetIDs to perform "TableSet sharing". We’ve seen the steps that the system goes through to generate a prompt list and what the resulting queries look like. It’s important to remember how SetIDs work and the steps that the Application Processor goes through to resolve the set control value that it will use. Often times developers will create new views of a table when all that really needs to be done is to change the set control value on the record where the prompt table is defined. Recall our AP example. Rather than creating a new view of the GL_ACCOUNT_TBL that replaces the SetID field with BUSINESS_UNIT_GL, simply change the Set Control Value on the prompt table definition to ‘BUSINESS_UNIT_GL’ to create the same effect. If it seems confusing, trace the SQL and experiment. As you change the Set Control Value fields on the record definition or on the prompt table definition or when you change the SetID on the set control group, you’ll be able to see the queries change and you should be able to achieve the desired effect.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License