Search Results igs_ps_sh_usec_occurs_u1

Overview

The IGS_PS_SH_USEC_OCCURS table is a shadow or audit table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically in the IGS (iGrants) schema. Its primary role is to maintain a historical record of specific field values from the IGS_PS_USEC_OCCURS (Unit Section Occurrences) table before they are updated. This design pattern is critical for tracking changes to key scheduling and resource assignment data, supporting audit trails, compliance, and data integrity within the application's academic scheduling module. The table stores the previous state of a unit section's occurrence details, such as its schedule days, times, location, and instructor, whenever those values are modified.

Key Information Stored

The table's structure is designed to capture the old values of specific operational columns alongside standard audit metadata. The most critical columns include:

• USECSH_ID: The primary key, a unique numeric identifier for each shadow record created.
• UNIT_SECTION_OCCURRENCE_ID: A foreign key linking back to the specific occurrence record in the main IGS_PS_USEC_OCCURS_ALL table that was modified.
• Day-of-week columns (MONDAY through SUNDAY): Each VARCHAR2 column stores the old value indicating whether the section occurred on that specific day.
• START_TIME and END_TIME: DATE columns storing the previous start and end times for the section occurrence.
• BUILDING_CODE and ROOM_CODE: NUMBER columns storing the old location identifiers.
• INSTRUCTOR_ID: A VARCHAR2(2000) column storing the previous instructor identifier(s).
• Standard WHO Columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN): Capture the user and timestamp for the creation of the shadow record itself.

Common Use Cases and Queries

This table is primarily leveraged for audit reporting and historical analysis. A common use case is to generate a report detailing all changes made to a unit section's schedule or resources over a given period. For instance, an administrator may need to audit all room assignment changes for compliance purposes. A typical query would join the shadow table with the main occurrence table to compare old and new values.

Sample Query Pattern:
SELECT sh.UNIT_SECTION_OCCURRENCE_ID,
sh.OLD_ROOM_CODE AS prior_room,
curr.ROOM_CODE AS current_room,
sh.LAST_UPDATE_DATE AS change_date
FROM IGS.IGS_PS_SH_USEC_OCCURS sh
JOIN IGS.IGS_PS_USEC_OCCURS_ALL curr ON sh.UNIT_SECTION_OCCURRENCE_ID = curr.UNIT_SECTION_OCCURRENCE_ID
WHERE sh.ROOM_CODE IS NOT NULL
AND sh.ROOM_CODE != curr.ROOM_CODE
AND sh.LAST_UPDATE_DATE BETWEEN :P_START_DATE AND :P_END_DATE
ORDER BY sh.LAST_UPDATE_DATE DESC;

This table is typically populated by application logic or database triggers on the main IGS_PS_USEC_OCCURS table and is not intended for direct transactional use by end-users.

Related Objects

The IGS_PS_SH_USEC_OCCURS table has defined relationships with other key objects in the EBS data model, primarily through foreign key constraints.

• Primary Key: The table's primary key is IGS_PS_SH_USEC_OCCURS_PK on the USECSH_ID column.
• Foreign Key (References): The UNIT_SECTION_OCCURRENCE_ID column is a foreign key that references the UNIT_SECTION_OCCURRENCE_ID in the IGS_PS_USEC_OCCURS_ALL table. This establishes the direct link between a shadow record and the live occurrence record it audits.
• Indexes: Two unique indexes support data integrity and query performance:
  • IGS_PS_SH_USEC_OCCURS_U1 on USECSH_ID (supports the primary key).
  • IGS_PS_SH_USEC_OCCURS_U2 on UNIT_SECTION_OCCURRENCE_ID.