An audit table is a customized table that contains specific data that identifies sections in PeopleSoft. Existence of data in this table is an indication of a change and that Astra Schedule should import updated section information. Astra Schedule will poll the audit table for the existence of records, use the data contained in the audit table records to identify the appropriate section(s) in PeopleSoft, and transfer the most current data for these sections to Astra Schedule.
In order to use an audit table for real time updates, the table must be created in any PeopleSoft schema that Astra Schedule will interface with, and configured to be populated when changes in key tables occur. This may be achieved using People Tools or database triggers. When this table is in place and populated with data, Astra Schedule may be configured to access the table and will process the data within, updating corresponding records as needed.
Audit table records should be generated and inserted into a table named PS_AUDIT_AD_ASTRA7 based on changes to data in three tables in the PeopleSoft schema, as outlined below.
PeopleSoft Selective Auditing
This is a feature that allows audit table records generated by updates to only get inserted if the column for the value modified in the source table also exists in the audit table. For example, the source table has columns A, B ,C and D and the audit table has columns A, B and C and the source table is configured for selective audits. Updated column C on a row in the source table would insert a record into the audit table but updating column D would not.
The PS_CLASS_MTG_PAT and PS_CLASS_INSTR tables should be configured to audit all changes to these tables as almost all columns are desired. This reduces the number of columns in the audit table compared to using selective auditing.
PeopleSoft tables that are audited:
Contains section data. Should audit insert, delete and selective updates. Auditing selective updates is used to avoid generating lots of audit records during enrollment periods when the enrollment is being updated frequently.
PS_CLASS_TBL (Selective on only the fields in the Audit table.)
Contains meeting data. Should audit insert, delete and all updates.
PS_CLASS_MTG_PAT (Any field change.)
Contains instructor assignments. Should audit insert, delete and all updates.
PS_CLASS_INSTR (Any field change.)
For each change, regardless of the origination table in PeopleSoft, the same section data must be sent to the audit table. The columns specified in the audit table are all of those used by Astra Schedule to uniquely identify a given section and maintain the association between the two systems (Astra Schedule and PeopleSoft). The presence of a record in the audit table is the condition that will trigger an update by Astra Schedule, using the combination of columns to identify the section record.
The Audit table must be named PS_AUDIT_AD_ASTRA7. The Astra Schedule PeopleSoft user will require select, update, and delete permissions on the audit table. A public or private synonym for this table to the Astra Schedule user will also be required.
The following information may be used specifically or for reference to create the audit table:
|AUDIT_OPRID||Required by PeopleSoft/Peopletools|
|AUDIT_STAMP||Required by PeopleSoft/Peopletools|
|AUDIT_ACTN||Required by PeopleSoft/Peopletools|
|AUDIT_RECNAME||Required by PeopleSoft/Peopletools|
|STRM||Used to build section SIS key|
|CAMPUS||Used for location information on deleted sections|
|LOCATION||Used for location information on deleted sections|
|CRSE_ID||Used to build section SIS key|
|CRSE_OFFER_NBR||Used to build section SIS key|
|SESSION_CODE||Used to build section SIS key|
|CLASS_SECTION||Used to build section SIS key|
|SUBJECT||Used for selective audit on PS_CLASS_TBL to identify subject changes|
|CATALOG_NBR||Used for selective audit on PS_CLASS_TBL to identify course number changes|
|SSR_COMPONENT||Used for selective audit on PS_CLASS_TBL to identify meeting type changes|
|CLASS_STAT||Used for selective audit on PS_CLASS_TBL to identify canceled sections|
|ENRL_CAP||Used for selective audit on PS_CLASS_TBL to identify max enrollment changes|
|DESCR||Used for selective audit on PS_CLASS_TBL to identify section title changes|
|COMBINED_SECTION||Used for selective audit on PS_CLASS_TBL to identify cross list changes|
|PROCESSED||Used for process control and cleanup|
Example of the standard PeopleSoft audit table definition for Astra Schedule:
CREATE TABLE PS_AUDIT_AD_ASTRA7
AUDIT_OPRID VARCHAR2(30 BYTE),
AUDIT_STAMP DATE DEFAULT SYSDATE,
AUDIT_ACTN VARCHAR2(1 BYTE),
AUDIT_RECNAME VARCHAR2(15 BYTE),
STRM VARCHAR2(4 BYTE),
CAMPUS VARCHAR2(5 BYTE),
LOCATION VARCHAR2(10 BYTE),
CRSE_ID VARCHAR2(6 BYTE),
SESSION_CODE VARCHAR2(3 BYTE),
CLASS_SECTION VARCHAR2(4 BYTE),
SUBJECT VARCHAR2(8 BYTE),
CATALOG_NBR VARCHAR2(10 BYTE),
SSR_COMPONENT VARCHAR2(3 BYTE),
CLASS_STAT VARCHAR2(1 BYTE),
DESCR VARCHAR2(30 BYTE),
COMBINED_SECTION VARCHAR2(1 BYTE),
PROCESSED VARCHAR2(1 BYTE)