Using an Audit Table for Real-time Import

The interface with Astra Schedule VII and PeopleSoft for real time import may optionally be configured to use an audit table in the PeopleSoft database schema. 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:

 

1.PS_CLASS_TBL
 
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.)

 

2.PS_CLASS_MTG_PAT
 
Contains meeting data. Should audit insert, delete and all updates.
 
PS_CLASS_MTG_PAT  (Any field change.)

 

3.PS_CLASS_INSTR
 
Contains instructor assignments. Should audit insert, delete and all updates.
 
PS_CLASS_INSTR   (Any field change.)

 

Note regarding combined (aka cross-listed) sections:  Testing has revealed that changing a meeting pattern or room assignment on a combined section will only generate an audit record for the meeting that was updated even though all meeting records that were in the combined section were updated.  This meant that only one section would get updated in Astra Schedule and it would be out of sync with the other meetings in the cross-list.  This was addressed in the real-time ("differencer") configuration file query by adding an additional query to identify cross-listed meetings for any sections updated and adding them to the list of sections that gets imported.

 

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:

 

Columns in the audit table:

 

 

 

Example of the standard PeopleSoft audit table definition for Astra Schedule version VII:

 

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),

           CRSE_OFFER_NBR NUMBER,

           SESSION_CODE VARCHAR2(3 BYTE),

           CLASS_SECTION VARCHAR2(4 BYTE),    

           CLASS_NBR NUMBER,

           SUBJECT VARCHAR2(8 BYTE),

           CATALOG_NBR VARCHAR2(10 BYTE),

           SSR_COMPONENT VARCHAR2(3 BYTE),

           CLASS_STAT VARCHAR2(1 BYTE),

           ENRL_CAP NUMBER,

           DESCR VARCHAR2(30 BYTE),

           COMBINED_SECTION VARCHAR2(1 BYTE),

           PROCESSED VARCHAR2(1 BYTE)

         )

Page url: ?peoplesoftsisinterfaceaddendum_audittable.htm