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:
Image: Structure of PSCHGCTLHIST
This example illustrates the fields and controls on the Structure of PSCHGCTLHIST. You can find definitions for the fields and controls later on this page.
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:
Image: Viewing definition keys
This example illustrates the fields and controls on the Viewing definition keys. You can find definitions for the fields and controls later on this page.
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, definitionvalue1Note: 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.