Results for “hist_end_dt”

50+ results




Overview
Explore in AI Chat

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The view IGS_PS_TCH_RESP_HIST_V belongs to the IGS – Student System product family within Oracle E-Business Suite, a module now classified as obsolete in the ETRM (E-Business Suite Technical Reference Manual) documentation for Release 12.1.1 and 12.2.2. Its stated purpose is to merge teaching responsibility history records with current teaching responsibility details, allowing the full evolution of a teaching assignment to be accessed across time, up to and including the current day. This temporal consolidation makes the view a natural reporting and integration surface for institutions that need to reconstruct who taught which unit version, in which organizational unit, and at what effort percentage, at any point in the academic timeline.

The view is explicitly recorded as "Not implemented in this database" in the documented metadata for the referenced environment, meaning it is a legacy or de-supported object that may be absent from many 12.1.1 / 12.2.2 instances despite being documented. Coverage in production systems will therefore vary, and the view should be treated as obsolete in any new development.

Underlying Base Objects

The documented view text is defined over three IGS tables:

  • IGS_PS_TCH_RESP_HIST (aliased TRH1, TRH2) — the historical teaching responsibility records, including historical start and end dates and the historical actor.
  • IGS_PS_TCH_RESP (aliased TR1, TR2) — the current (live) teaching responsibility rows.
  • IGS_PS_UNIT_VER (aliased UV) — the unit version, used to constrain the historical branch to valid unit/version combinations.

The view is a UNION ALL of two queries. The first joins history to the live record on unit code, version number, organization unit code, and organization unit start date using outer joins ((+)), and is further restricted by an inner join to IGS_PS_UNIT_VER. The second branch reverse-outer-joins the live table to history, grouping by live responsibility attributes and deriving HIST_END_DT from NVL(MAX(TRH2.HIST_END_DT), TR2.LAST_UPDATE_DATE). The ETRM record lists no separately documented referenced base objects beyond those appearing in the view text.

Key Columns

  • UNIT_CD, VERSION_NUMBER — the unit and version that identify what was taught.
  • ORG_UNIT_CD, OU_START_DT — the teaching organizational unit and its effective start date.
  • HIST_START_DT, HIST_END_DT — the effective window of the responsibility record; the second union branch emits a NULL start date and a derived end date, marking a current-day record.
  • HIST_WHO — the historical actor associated with the record.
  • PERCENTAGE — teaching effort. It is resolved as NVL(TRH1.PERCENTAGE, NVL(TO_NUMBER(IGS_AU_GEN_004.AUDP_GET_TRH_COL(...)), TR1.PERCENTAGE)), so the historical percentage takes precedence, then the custom API AUDP_GET_TRH_COL, then the current percentage.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns, exposed for whichever source row supplied the record.

The function AUDP_GET_TRH_COL, referenced in IGS_AU_GEN_004, is a site-specific customization hook and is the object most commonly searched alongside this view; its presence implies the view was extended locally to supply a fallback percentage where historical data is incomplete.

Common Use Cases and Queries

Typical usage reconstructs teaching effort over time for a unit version and organizational unit, or reports on current teaching responsibility with its historical context.

SELECT unit_cd, version_number, org_unit_cd, ou_start_dt,
       hist_start_dt, hist_end_dt, percentage
FROM   igs_ps_tch_resp_hist_v
WHERE  unit_cd = :unit_cd
AND    version_number = :version_number
ORDER  BY ou_start_dt, hist_start_dt;

The view supports point-in-time analysis by filtering on the historical window:

SELECT org_unit_cd, percentage
FROM   igs_ps_tch_resp_hist_v
WHERE  unit_cd = :unit_cd
AND    version_number = :version_number
AND    hist_start_dt <= :as_of_date
AND    NVL(hist_end_dt, :as_of_date) >= :as_of_date;

The UNION ALL structure means duplicate-looking rows can appear where a current responsibility also has history; queries should typically apply DISTINCT or filter on the presence of HIST_START_DT depending on whether the historical or current representation is required. Because the object is obsolete and may not exist in a given 12.1.1 or 12.2.2 database, availability should be confirmed before use, and any dependency on AUDP_GET_TRH_COL must be validated against the local IGS_AU_GEN_004 package.