Search Results day_of_week_code

Overview

The view APPS.IGS_PE_OFFICE_HRS_V is a reporting and integration object within the Oracle E-Business Suite Student System (IGS) product family. Its stated purpose is to expose daily visiting times for faculty, staff, and advisors, allowing institutional users to query when a given contact is available for in-person or scheduled meetings. The view presents office-hours records keyed to a contact preference, resolved against the Trading Community Architecture (TCA) party model, and enriched with a decoded day-of-week meaning supplied by an IGS lookup.

Because it is a view and not a base table, IGS_PE_OFFICE_HRS_V carries no independent storage; it is a read-only projection that joins scheduling data with party-identity data. This makes it suitable for concurrent programs, OBIEE or XML Publisher reports, and inbound integration extracts that need a denormalized, human-readable representation of office hours without replicating the multi-table join logic in every consumer. The object resides in the APPS schema, is flagged VALID, and is documented in ETRM for both 12.1.1 and 12.2.2.

Underlying Base Objects

The view text documents four referenced sources. The driving table is IGS_PE_OFFICE_HRS (aliased CPREF), which stores the office-hours intervals themselves, including the day-of-week code and start/end times. It is joined to HZ_CONTACT_PREFERENCES (HZPREF) on CONTACT_PREFERENCE_ID, which supplies the CONTACT_LEVEL_TABLE_ID and CONTACT_LEVEL_TABLE discriminator columns.

The party identity is resolved through HZ_PARTIES (HZ), joined where HZPREF.CONTACT_LEVEL_TABLE_ID = HZ.PARTY_ID and the contact level table is restricted to the literal 'HZ_PARTIES'. This effectively means the view currently surfaces office hours only for party-level contacts; contacts attached to other contact-level entities are excluded by the filter. Finally, IGS_LOOKUP_VALUES (LK) is joined on DAY_OF_WEEK_CODE = LOOKUP_CODE with LOOKUP_TYPE = 'IGS_WEEK_DAYS' to translate the stored code into a display meaning. The ETRM metadata notes no separately documented base objects, so these four tables are inferred from the embedded view text.

Key Columns

• ROW_ID — the ROWID of the IGS_PE_OFFICE_HRS row, useful for identifying or updating the underlying record.
• OFFICE_HRS_ID — primary identifier of the office-hours record.
• CONTACT_PREFERENCE_ID — the foreign key linking the office-hours row to its contact preference; this is the column most relevant to searches on contact_preference_id.
• PERSON_ID — sourced from HZPREF.CONTACT_LEVEL_TABLE_ID; identifies the party to whom the office hours belong.
• CONTACT_LEVEL_TABLE — the contact level discriminator, filtered to HZ_PARTIES.
• PERSON_NUMBER — the human-facing party number from HZ_PARTIES.
• DAY_OF_WEEK_CODE and DAY_OF_WEEK_M — the raw code and its decoded meaning from the IGS_WEEK_DAYS lookup.
• START_TM_DATE and END_TM_DATE — the start and end times of the visiting interval.
• Audit columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The view is typically queried to report a person's weekly availability or to look up the office hours associated with a known contact preference. A representative query is:

SELECT person_number, day_of_week_m, start_tm_date, end_tm_date
FROM apps.igs_pe_office_hrs_v
WHERE contact_preference_id = :p_contact_preference_id
ORDER BY day_of_week_code, start_tm_date;

Alternatively, filtering by party number produces a full schedule for an individual. Consumers should be aware that the view returns only party-level contacts and that time values are stored as dates, so formatting to a time-only display is required in the reporting layer.