PeopleSoft Audit Table for Real Time Import

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

           mceclip0.png

  1. PS_CLASS_MTG_PAT

    Contains meeting data. Should audit insert, delete and all updates.

    PS_CLASS_MTG_PAT (Any field change.)

           mceclip1.png

  1. PS_CLASS_INSTR

    Contains instructor assignments. Should audit insert, delete and all updates.

    PS_CLASS_INSTR (Any field change.)

          mceclip2.png

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: 

Column Name Description
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
CLASS_NBR  
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),

            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)

          )


Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.