Results for “acad_ci_sequence_number”

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

Overview

IGS_AD_APPL_HIST_ALL is a table in the Oracle E-Business Suite IGS (Student System) product family. Its documented description states that it "describes history of changes to admission application." In other words, the object captures a temporal, audit-style record of how an admission application has evolved over time, storing prior and current attribute values alongside effective-date boundaries. This supports the tracking of application state changes—such as transitions between admission statuses, changes to admission category, or updates to fee handling—rather than simply holding the current application record.

The ETRM metadata records the product as "IGS - Student System (Obsolete)" and notes that the object is "Not implemented in this database" in the reference environment. This is an important qualifier: in the 12.1.1 documentation baseline, the table is documented structurally but is not materialized in the captured database. Its documented owner is IGS, and the physical schema comprises 21 columns. Because the documented relationship data identifies the table as "standalone" (no incoming or outgoing foreign-key dependencies were mined), a Data Vault modeling suggestion would classify this object as a satellite: it records descriptive, time-stamped attributes about an admission application business key rather than acting as a hub or link. This is a heuristic suggestion derived from the FK structure and should be treated as a modeling aid only.

Key Information Stored

The most significant columns fall into three groups: the primary key, the business-key candidate, and the descriptive history payload.

• Primary key columns: PERSON_ID, ADMISSION_APPL_NUMBER, HIST_START_DT. The documented primary key IGS_AD_APPL_HIST_ALL_PK is composed of these three columns.
• Business-key candidate: the unique index IGS_AD_APPL_HIST_U1 covers PERSON_ID, ADMISSION_APPL_NUMBER, and HIST_START_DT. Note that this mirrors the primary key rather than introducing a separate surrogate, so the "surrogate primary key" here is effectively the composite business key of person, application number, and history start date.
• History boundary columns: HIST_START_DT and HIST_END_DT define the effective period of each historical version, while HIST_WHO identifies the actor responsible for the change.
• Application attribute payload: APPL_DT (application date), ADMISSION_CAT (admission category), ADM_APPL_STATUS (application status), ADM_FEE_STATUS (fee status), and TAC_APPL_IND (application indicator).
• Calendar and sequence context: ACAD_CAL_TYPE, ACAD_CI_SEQUENCE_NUMBER, ADM_CAL_TYPE, and ADM_CI_SEQUENCE_NUMBER identify the academic and admission calendar instances associated with the application.
• Process classification: S_ADMISSION_PROCESS_TYPE distinguishes the admission process type.
• Standard audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and the organization-scoping ORG_ID.

Common Use Cases and Queries

The principal use case is reconstructing the state of an admission application as of a given point in time, which supports audit reporting, point-in-time admissions analysis, and reconciliation of status transitions.

• Point-in-time application state: retrieve the row effective on a given date using HIST_START_DT <= :as_of AND (HIST_END_DT IS NULL OR HIST_END_DT > :as_of), filtered by PERSON_ID and ADMISSION_APPL_NUMBER.
• Status-change audit trail: order rows by HIST_START_DT for a given applicant to trace how ADM_APPL_STATUS and ADM_FEE_STATUS changed over time.
• Who-changed-what reporting: group by HIST_WHO to attribute changes to actors, joined with personnel tables.
• Admissions funnel reporting: aggregate by ADMISSION_CAT, S_ADMISSION_PROCESS_TYPE, and calendar instance columns to analyze application volumes across ACAD_CAL_TYPE and ADM_CAL_TYPE periods.

Related Objects

The documented metadata classifies this table as standalone, with no mined foreign-key relationships. Consequently, joins must be inferred from the shared business keys that also appear in other IGS admissions objects:

• Base admission application table (for example IGS_AD_APPL_ALL or its equivalent): join on PERSON_ID and ADMISSION_APPL_NUMBER to compare historical rows against the current application.
• Person / party records: join on PERSON_ID to resolve applicant identity.
• Academic calendar objects: join on ACAD_CAL_TYPE and ACAD_CI_SEQUENCE_NUMBER to resolve calendar context.
• Admission calendar objects: join on ADM_CAL_TYPE and ADM_CI_SEQUENCE_NUMBER.
• Admission category and status lookups: join on ADMISSION_CAT and ADM_APPL_STATUS.
• Process type reference: join on S_ADMISSION_PROCESS_TYPE.
• Lookup values: the S_-prefixed column suggests a seeded lookup source, typically FND_LOOKUP_VALUES.
• Organization definition: join on ORG_ID to HR_OPERATING_UNITS for multi-org scoping.
• Standard audit joins: FND_USER on CREATED_BY / LAST_UPDATED_BY.

Given the table's obsolete status and absence from the reference database, consumers should verify its existence in their specific environment before building dependencies against it.