Search Results residency_status_cd




Overview
Explore in AI Chat

Overview

The view IGS_PE_RES_DTLS is an Oracle E-Business Suite (EBS) database object owned by the APPS schema within the IGS – Student System product family. It exposes residency determination detail records associated with persons (learners, applicants, or students) tracked by the institution. Its principal role is to provide a read-optimized, multi-org-secured presentation of residency evaluation data, allowing reporting, integration, and inquiry functions to retrieve residency classification and status information without directly querying the underlying base table.

The view is marked VALID and is documented in the ETRM repository for EBS 12.1.1 and 12.2.2. Residency processing in the Student System determines whether a learner qualifies for in-state, out-of-state, or other residency-based tuition treatment, and this view surfaces the supporting evidence and evaluation outcomes that drive those determinations.

Underlying Base Objects

Per the documented view text, IGS_PE_RES_DTLS is defined over a single base object: IGS_PE_RES_DTLS_ALL. The alias TAB is applied throughout the SELECT list, and all projection columns are derived from this table. No joins, unions, or additional documented base objects are present in the view definition.

The view performs no aggregation or filtering; it is a straightforward projection that renames the ROWID pseudo-column to ROW_ID and carries through the multi-org discriminator ORG_ID from the _ALL table. Two columns—START_DT and END_DT—are synthesized using TO_DATE(NULL), meaning the view deliberately exposes date placeholders as NULL rather than mapping them to real columns. Because the base table is the multi-org (_ALL) variant, this view inherits operating-unit partitioning, and queries are typically subject to the current session's ORG_ID through the Multi-Org Access Control (MOAC) mechanism.

Key Columns

  • ROW_ID – The base table ROWID, useful for direct row addressing in DML-oriented tooling.
  • ORG_ID – Operating unit discriminator; governs multi-org access and security.
  • RESIDENT_DETAILS_ID – Primary identifier of the residency detail record.
  • PERSON_ID – Foreign key to the person for whom residency is evaluated.
  • RESIDENCY_CLASS_CD – Residency classification code (for example, resident versus non-resident categories).
  • RESIDENCY_STATUS_CD – The residency status code, central to determining the outcome of a residency evaluation; the column most commonly searched by users.
  • EVALUATION_DATE and EVALUATOR – When and by whom the residency determination was performed.
  • START_DT / END_DT – Effective date boundaries; documented as TO_DATE(NULL) and therefore null-valued in this view.
  • COMMENTS – Free-text notes attached to the residency detail.
  • Audit columns – CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 – Descriptive flexfield (DFF) segments supporting institution-specific extensions.
  • CAL_TYPE and SEQUENCE_NUMBER – Calendar type and sequence identifiers supporting ordering and context.

Common Use Cases and Queries

Typical uses include residency compliance reporting, financial-aid and tuition-aid eligibility checks, and integration feeds that synchronize residency status into downstream systems. A frequent query retrieves all residency details for a given person:

SELECT person_id, residency_class_cd, residency_status_cd,
       evaluation_date, evaluator
FROM   apps.igs_pe_res_dtls
WHERE  person_id = :p_person_id;

A broader reporting query filters by a specific residency status code while respecting the operating unit:

SELECT resident_details_id, person_id, residency_class_cd,
       residency_status_cd, evaluation_date
FROM   apps.igs_pe_res_dtls
WHERE  residency_status_cd = :p_status_cd
AND    org_id = :p_org_id;

Analysts commonly count records grouped by classification and status to monitor determination outcomes, and integration developers select by LAST_UPDATE_DATE to identify records changed since the last extract cycle. Because the view is a simple projection over IGS_PE_RES_DTLS_ALL, queries should be constrained on ORG_ID and indexed keys such as PERSON_ID to avoid full scans.