PeopleTools – Reference SETID controlled table by BUSINESS_UNIT

by

BUSINESS_UNIT <> SETID

Several tables are controlled by the SETID field in order to share data across Business Units or to isolate data to a specific Business Unit.  Table Set Control records enable both to happen simultaneously.  Several Business Units may share data in a table, such as locations, where one specific Business Unit may need its own location data.

How to tell if a table Is Table Set controlled.

On the record definition, Use Tab.

SETID should be the first field on the record and a primary key.

 

Table Set Control Configuration:

Navigation:

PeopleTools > Utilities > Administration

 

TableSet IDs
·         Initial Definition of a Set ID

  • Record Group
  • ·         Define groups of records that are controlled by SETID in the same way.
  • ·         These will be used in the TableSet Controls component to define how the SETID and Business Unit Relate
  • ·         Record Groups make it easier to define interaction by groups rather than hudreds or thousands of tables individually defined in the TableSet Controls  component
  • TableSet Controls
  • ·         For a specific SetControl Value, What SETID should be used with each of the record groups.
  • ·         Many of these may be defaulted to the same value as the Set Control Value
  • ·         Some or few may be specially defined as a different value to share or isolate value

 

For any Given Business Unit, how to look up data from a table controlled by SETID where the SETID may or may not equal the Business Unit Value

To correctly look up the agencies available to a specific business unit, use the SQL below.  The T.SETCNTRLVALUE resolves to the Business Unit value.

Note the specific record being queried and cross referenced goes in the “Where” Clause.

 

SELECT DISTINCT T.SETCNTRLVALUE /* resolves to Business Unit */
, A.CUS_AGENCY_CD
, A.DESCR
FROM PS_CUS_AGENCY_TBL A
, PS_SET_CNTRL_GROUP G
, PS_SET_CNTRL_REC R
, PS_SET_CNTRL_TBL T
WHERE R.REC_GROUP_ID = G.REC_GROUP_ID
AND R.recname = 'CUS_AGENCY_TBL'
AND G.SETCNTRLVALUE = T.SETCNTRLVALUE
AND R.SETCNTRLVALUE = G.SETID
AND R.REC_GROUP_ID = G.REC_GROUP_ID
AND A.SETID = R.SETID
Share ...