Search Results igs_week_days

Overview

The view APPS.IGS_PE_OFFICE_HRS_V is a reporting and integration object within the Oracle E-Business Suite (EBS) environment, addressing the Person/Party contact infrastructure delivered by the Oracle student system modules. In release 12.1.1 and 12.2.2, this view consolidates office-hours records maintained against a party's contact preferences, resolving the underlying coded values into a presentation-ready result set. Its role is to expose weekly office-hour availability — the day, start time, and end time at which a party (typically a faculty member, advisor, or staff contact) is reachable — in a denormalized form suitable for concurrent programs, Oracle Reports, Oracle Application Express pages, and outbound interface extracts.

A defining characteristic of the view is that it is not a simple single-table read. It joins the office-hours detail table to the Trading Community Architecture (TCA) contact preference and party tables, and to the lookup values table, so consumers receive both the stored code and its translated meaning. This spares downstream developers from re-implementing the same joins and the same lookup resolution for the IGS_WEEK_DAYS lookup type on every query.

Underlying Base Objects

The view is defined over four base objects, combined through an inner-join construction. Though the ETRM metadata documents no explicit base-object list, the view text published in the same source identifies them unambiguously:

• IGS_PE_OFFICE_HRS (aliased CPREF) — the driving table holding the office-hours rows, keyed by OFFICE_HRS_ID and linked to a contact preference.
• HZ_CONTACT_PREFERENCES (aliased HZPREF) — the TCA contact preference header, providing the contact level and the party linkage.
• HZ_PARTIES (aliased HZ) — the TCA party master, supplying the party number used as the person identifier.
• IGS_LOOKUP_VALUES (aliased LK) — the lookup values table, restricted to LOOKUP_TYPE = 'IGS_WEEK_DAYS', providing the descriptive meaning for each day-of-week code.

The join predicates enforce referential integrity: office hours to contact preference by CONTACT_PREFERENCE_ID; contact preference to party by CONTACT_LEVEL_TABLE_ID, conditioned on CONTACT_LEVEL_TABLE = 'HZ_PARTIES'; and day-of-week code to lookup code. Because all joins are inner joins, rows whose day code has no matching lookup value, or whose contact preference is not a party-level preference, are silently excluded.

Key Columns

• ROW_ID — the rowid of the underlying office-hours record, useful for direct-row addressing.
• OFFICE_HRS_ID — primary identifier of the office-hours record.
• CONTACT_PREFERENCE_ID — foreign key to the parent contact preference.
• PERSON_ID / PERSON_NUMBER — the party identifier and the user-facing party number for the person holding the office hours.
• DAY_OF_WEEK_CODE — the stored day code validated against the IGS_WEEK_DAYS lookup type.
• DAY_OF_WEEK_M — the translated, displayable meaning of that day code.
• START_TM_DATE and END_TM_DATE — the beginning and ending time of the office-hours window.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

A frequent requirement is producing a readable weekly availability listing per person. The translated DAY_OF_WEEK_M column removes the need for an additional lookup join:

• SELECT person_number, day_of_week_m, start_tm_date, end_tm_date FROM igs_pe_office_hrs_v WHERE person_number = :p_party_number ORDER BY day_of_week_code, start_tm_date;
• SELECT day_of_week_m, COUNT(*) FROM igs_pe_office_hrs_v GROUP BY day_of_week_m ORDER BY 2 DESC; — distribution of office hours across weekdays.
• SELECT person_number, day_of_week_m, start_tm_date, end_tm_date FROM igs_pe_office_hrs_v WHERE start_tm_date > end_tm_date; — validation query for records with inverted time windows.

Because the view depends on TCA party and contact preference data, secured access is normally granted through the APPS schema, and reporting should filter by party or contact preference to avoid full scans on large installations.