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:
PeopleTools > Utilities > Administration
· 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