Search Results day_of_week_code

Overview

IGS.IGS_PE_OFFICE_HRS is an extension table in the Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 environment, owned by the IGS schema. According to the ETRM metadata, this table extends HZ_CONTACT_PREFERENCES and is used to store office hours for staff and faculty members. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free value of 10, and it is registered as an FND Design Data object under the identifier IGS.IGS_PE_OFFICE_HRS. The object status is VALID, confirming active availability within the data dictionary.

From a data modeling perspective, the heuristic Data Vault classification mined from the foreign key structure identifies this object as standalone. This suggests that, in a Data Vault representation, the table behaves as an isolated entity rather than participating in a classic hub, link, or satellite relationship. Practitioners modeling this data should treat it as an extension attribute source keyed by its own surrogate primary key, with CONTACT_PREFERENCE_ID providing the conceptual linkage to the parent contact preference record.

Key Information Stored

The table contains 10 documented columns. The most important are summarized below.

• OFFICE_HRS_ID (NUMBER) — Surrogate primary key and unique identifier for each office hours record. Enforced by unique index IGS_PE_OFFICE_HRS_PK on APPS_TS_TX_IDX.
• CONTACT_PREFERENCE_ID (NUMBER, 15) — Parent Contact Preference identifier that associates the office hours entry with a specific staff or faculty contact preference record lower. Indexed non-uniquely via IGS_PE_OFFICE_HRS_N1.
• DAY_OF_WEEK_CODE (VARCHAR2, 30) — The day of the week for which the office hours apply. This is the column most commonly searched by users interested in scheduling and availability.
• START_TM_DATE (DATE) — Start time of the visit; stores both date and time components.
• END_TM_DATE (DATE) — End time of the visit; stores both date and time components.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns capturing the identity and timestamp of the creating and last-updating users and sessions.

The only documented unique index is IGS_PE_OFFICE_HRS_PK, making OFFICE_HRS_ID the sole business-key candidate recorded in the metadata. CONTACT_PREFERENCE_ID functions as a de facto foreign key linking to the parent, though the dependency section notes the table does not reference any database object explicitly.

Common Use Cases and Queries

This table is commonly queried for faculty and staff availability reporting, scheduling dashboards, and student self-service features that display office hours. A typical query retrieving office hours for a specific contact preference follows:

SELECT OFFICE_HRS_ID
     , DAY_OF_WEEK_CODE
     , START_TM_DATE
     , END_TM_DATE
FROM   IGS.IGS_PE_OFFICE_HRS
WHERE  CONTACT_PREFERENCE_ID = :p_contact_preference_id
ORDER BY DAY_OF_WEEK_CODE, START_TM_DATE;

A frequent search pattern centers on day_of_week_code, for example filtering all records for a particular weekday:

SELECT CONTACT_PREFERENCE_ID, START_TM_DATE, END_TM_DATE
FROM   IGS.IGS_PE_OFFICE_HRS
WHERE  DAY_OF_WEEK_CODE = :p_day_of_week_code;

Reporting use cases include generating weekly schedules, calculating total available hours per staff member, and integrating with calendar systems. Because START_TM_DATE and END_TM_DATE carry full date components, developers should apply TRUNC or TO_CHAR formatting when isolating time-of-day values.

Related Objects

The primary conceptual parent is HZ_CONTACT_PREFERENCES, which this table extends; joins link via CONTACT_PREFERENCE_ID. The APPS synonym IGS_PE_OFFICE_HRS exposes the table to application code. Due to the standalone classification, the metadata documents no explicit inbound or outbound foreign key dependencies. Related IGS staff and faculty objects, such as those holding person or contact information that surfaced CONTACT_PREFERENCE_ID, are the next most significant integration points in practice.