Results for “special_service_code”

6 results




Overview
Explore in AI Chat

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

Overview

IGS_PE_SN_SERVICE is a table in the Oracle E-Business Suite IGS (Student System) product family, which Oracle has classified as obsolete. The table holds details of a person's special need service information, capturing the relationship between an individual's recorded disability and the specific support services provisioned for that disability within an academic institution's student management environment. In functional terms, it functions as an intersection between a person's disability record and a codified special service classification, recording also the temporal window during which the service applies.

From a dimensional modeling perspective, the metadata's heuristic Data Vault classification of this object is link. This suggestion reflects the table's structure: it resolves a many-to-many style association between a person-disability entity and a service code, and it carries a defined business key composed of the disability identifier, the service code, and a start date. Modelers treating IGS_PE_SN_SERVICE as a link would place DISABILITY_ID and SPECIAL_SERVICE_CD as the referenced hub/foreign keys, with START_DT and END_DT participating in the link's driving key or effectivity construct.

The ETRM documentation notes that the table is not implemented in the reference database, consistent with the obsolete status of the IGS product line. Customers on EBS 12.1.1 and 12.2.2 who have legacy IGS data may still encounter the object in historical schemas, migration projects, or archived reporting extracts.

Key Information Stored

The documented physical schema for release 12.1.1 lists twelve columns owned by the IGS schema. The most significant columns are summarized below.

  • SN_SERVICE_ID — the surrogate primary key, enforced by the IGS_PE_SN_SERVICE_PK constraint and also by the unique index IGS_PE_SN_SERVICE_U1. This is the system-generated identifier for each service assignment row.
  • DISABILITY_ID — foreign key to IGS_PE_PERS_DISABLTY, identifying the person's disability record to which the special service is attached.
  • SPECIAL_SERVICE_CODE — foreign key to IGS_PE_CODE_CLASSES, providing the coded classification of the special-needs service being delivered.
  • SPECIAL_SERVICE_CD — a second service code column that participates with DISABILITY_ID and START_DT in the IGS_PE_SN_SERVICE_U2 unique index, functioning as a documented business-key candidate.
  • START_DT and END_DT — the effective date range over which the special service applies, with START_DT forming part of the business key.
  • DOCUMENTED_IND — a flag indicating whether the special need service has been formally documented.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS audit columns recording row provenance and change history.

The distinction between the surrogate key (SN_SERVICE_ID) and the business-key candidates (the U2 index over DISABILITY_ID, SPECIAL_SERVICE_CD, START_DT) is important for de-duplication and integration logic in any migration or warehouse design.

Common Use Cases and Queries

Typical usage centers on reporting and integration rather than live transactional processing, given the table's obsolete status. Common scenarios include reconciling special-needs service provisioning against disability registrations, auditing which services are active during a given date window, and extracting legacy records into a data warehouse.

A representative query joining the service rows to their disability parent is illustrated below.

  • SELECT s.SN_SERVICE_ID, s.DISABILITY_ID, s.SPECIAL_SERVICE_CD, s.START_DT, s.END_DT FROM IGS_PE_SN_SERVICE s WHERE s.DISABILITY_ID = :p_disability_id;
  • SELECT s.* FROM IGS_PE_SN_SERVICE s JOIN IGS_PE_PERS_DISABLTY d ON s.DISABILITY_ID = d.DISABILITY_ID WHERE d.PERSON_ID = :p_person_id;
  • Active-service filter: WHERE TRUNC(SYSDATE) BETWEEN s.START_DT AND NVL(s.END_DT, TRUNC(SYSDATE)) to isolate currently effective services.

Reporting use cases frequently aggregate counts of services by SPECIAL_SERVICE_CODE to profile demand for accommodations, or join to code translations for human-readable output.

Related Objects

The metadata identifies the following directly related objects through primary key and foreign key relationships.

  • IGS_PE_PERS_DISABLTY — referenced via IGS_PE_SN_SERVICE.DISABILITY_ID; the parent record describing a person's disability.
  • IGS_PE_CODE_CLASSES — referenced via IGS_PE_SN_SERVICE.SPECIAL_SERVICE_CODE; supplies valid values and classifications for the service code.
  • IGS_PE_SN_SERVICE_PK — the primary key constraint on SN_SERVICE_ID.
  • IGS_PE_SN_SERVICE_UK / IGS_PE_SN_SERVICE_U2 — the unique constraint spanning DISABILITY_ID, SPECIAL_SERVICE_CODE, and START_DT.
  • IGS_PE_SN_SERVICE_U1 — the unique index on SN_SERVICE_ID.

These relationships should guide any reconstruction of the special-needs data model during migration planning.