Understanding Change Control Information Reporting

Currently, no predefined reports for retrieving Change Control information are available. However, you can create your own reports by querying the Change Control tables.

The two tables that you can use for reporting are the Change Control History table (PSCHGCTLHIST) and the Change Control Locking table (PSCHGCTLLOCK). These tables have an almost identical column structure:

The main difference between these two tables is that PSCHGCTLHIST contains a CHGCTRL_ACTION field, while PSCHGCTLLOCK does not.

Each PeopleSoft definition in these tables is uniquely identified by numeric codes (DEFINITIONID columns) and names (DEFINITIONVALUE columns). The different DEFINITIONID and DEFINITIONVALUE column pairs correspond to the various definition key types and values for each kind of definition. You can see these definition keys when you view the upgrade definition window. For example, translate values have these keys—Field Name, Field Value, and Effective Date, as in this example:

In the Change Control tables, the row containing the first translate value in the preceding example has the following field values:

DEFINITIONVALUE1	DEFINITIONVALUE2	DEFINITIONVALUE3
AE_ABEND_ACTION	A	1900-01-01

When reporting on a particular definition type, you want to retrieve definition values, but you must limit the query by using the definition IDs for the definition type. The following tables list all of the upgradable definition types, their corresponding definition ID codes, and the type of value that each ID represents (in parentheses).

This table lists PeopleTools definition types and their definition IDs:

Definition Type	DEFINITIONID1	DEFINITIONID2	DEFINITIONID3	DEFINITIONID4
Access Groups	17 (name)	0	0	0
Activities	18 (name)	0	0	0
Application Engine Programs	66 (name)	Not applicable (NA)	NA	NA
Application Engine Sections	66 (name)	77 (section)	NA	NA
Application Package	104 (name)	NA	NA	NA
Approval Rule Sets	85 (name)	21 (effective date)	NA	NA
Business Interlink	64 (name)	NA	NA	NA
Business Processes	7 (name)	NA	NA	NA
Colors	19 (name)	25 (user ID)	0	0
Components	10 (name)	39 (market)	NA	NA
Component Interfaces	74 (name)	NA	NA	NA
Cube Definitions	54 (name)	55 (description)	NA	NA
Cube Instance Definitions	56 (name)	57 (description)	NA	NA
Dimensions	51 (name)	52 (dimension type)	53 (description)	0
Fields	6 (name)	0	0	0
Field Formats	23 (family name)	0	0	0
File Layout Definitions	71 (name)	NA	NA	NA
HTML	90 (name)	95 (type)	NA	NA
Images	91 (name)	95 (type)	NA	NA
Indexes	1 (name)	24 (index ID)	0	0
Job Definitions	27 (name)	NA	NA	NA
Menus	3 (name)	0	0	0
Message Catalog Entries	48 (message set number)	48 (message number)	16 (language code)	50 (description)
Message Channels	61 (name)	NA	NA	NA
Message Definitions	60 (name)	NA	NA	NA
Mobile Page Important! PeopleSoft Mobile Agent is a deprecated product. These features exist for backward compatibility only.	111 (name)	NA	NA	NA
Message Nodes	62 (name)	NA	NA	NA
Pages	9 (name)	NA	0	0
PeopleCode	See PeopleCode Definition Types	NA	NA	NA
Problem Type	109 (name)	NA	NA	NA
Process Definitions	29 (process type)	28 (name)	0	0
Process Type Definitions	29 (name)	26 (operating system)	20 (database type)	NA
Project	255 (name)	NA	NA	NA
Queries	30 (name)	25 (user ID)	0	0
Records	1 (name)	2 (RecField name)	0	0
Recurrence Definitions	31 (name)	NA	NA	NA
Roles	32 (name)	0	0	0
Server Definitions	33 (name)	NA	NA	NA
SQL	65 (name)	81 (SQL type)	NA	NA
Styles	35 (name)	0	0	0
Style Sheets	94 (name)	NA	NA	NA
Translate Values	6 (database field name)	22 (value)	21 (effective date)	NA
Trees	34 (setID)	68 (user key value)	36 (tree name)	21 (effective date)
Tree Structures	37 (name)	0	0	0

This table lists PeopleCode definition types and their definition IDs:

Definition Type	DEFINITIONID1	DEFINITIONID2	DEFINITIONID3	DEFINITIONID4
Application Engine	66 (PeopleSoft Application Engine program)	77 (section, market, database type, effective date)	78 (step)	12 (method)
Application Package	104 (application package)	NA	NA	NA
Component Interface	74 (business component)	12 (method)	NA	NA
Menu	3 (menu)	4 (bar)	5 (item)	12 (method)
Message	60 (message)	12 (method)	NA	NA
Page	9 (panel)	16 (language code)	12 (method)	NA
Page Field	9 (panel)	16 (language code)	67 (field)	12 (method)
Component	10 (panel group)	39 (market)	12 (method)	NA
Component Record	10 (panel group)	39 (market)	1 (record)	12 (method)
Component Record Field	10 (panel group)	39 (market)	1 (record)	2 (fieldname, method)
Record	1 (record)	2 (field)	12 (method)	NA
Subscription	60 (message)	87 (subscription)	12 (method)	NA

These are the supported definition types for change control:

Activity
Application Engine Program
Application Package
Approval Rule Set
Business Interlink
Business Process
Component
Component Interface
Field
File Layout
HTML
Image
Menu
Message
Message Channel
Problem Type
Page
Project
Record
SQL
Style Sheet

When reporting on Change Control history, consider one other field: CHGCTRL_ACTION. This field stores the one-letter code for the various actions that Change Control history tracks:

A (Add)
C (Copy)
D (Delete)
I (Informational)
L (Lock)
R (Rename)
U (Unlock)

Here's an example of a SQL query to report on all deleted definitions:

select oprid, definitionvalue1, definitionvalue2, 
definitionvalue3, definitionvalue4, dttm_stamp, projectname, 
incident_id, descrlong 
from pschgctlhist
  where chgctrl_action = 'D'
  order by oprid, definitionvalue1

Note: Full history tracking is supported only for PeopleSoft Application Designer definitions: business processes, business process maps, fields, menus, panels, panel groups, projects, and records. Other definition types have history entries only when the CHGCTL_ACTION field value is C, and only if they have been upgraded.

Understanding Change Control Information Reporting

PeopleTools Definition Types

PeopleCode Definition Types

Change Control Supported Definition Types