Results for “insurance_cd”

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

Overview

IGS_PE_HLTH_INS_ALL is a table in the Oracle E-Business Suite IGS (Student System) product family, documented at the 12.1.1 schema level and remaining catalogued through 12.2.2. It describes health insurance details associated with a person record within the Student System. In the ETRM metadata this object is explicitly marked "Not implemented in this database," which indicates that the entity is part of the delivered schema definition but is not instantiated in every installation. Its presence in the data dictionary therefore reflects product design rather than guaranteed runtime availability.

From a modeling perspective, the mined Data Vault classification for this table is link. This is a heuristic suggestion: the table records an association between a person and an insurance designation, rather than serving as a pure descriptive hub or a fully attributed satellite. Analysts should treat the link classification as a starting point for lineage and integration work, not as a binding architectural declaration by Oracle.

Key Information Stored

The table is documented with 35 columns. The most significant are the identifiers, the insurance descriptors, the validity dates, the multi-tenant and audit columns, and the descriptive flexfield attributes.

• HEALTH_INS_ID — the surrogate primary key, enforced by constraint IGS_PE_HLTH_INS_PK and additionally covered by unique index IGS_PE_HLTH_INS_U1. This column is the definitive unique identifier for a health insurance record and is the strongest business-key candidate documented in the metadata.
• PERSON_ID — foreign key to HZ_PARTIES, identifying the person to whom the insurance detail belongs. This anchors the table to the Trading Community Architecture party model.
• INSURANCE_ID — foreign key to IGS_PE_CODE_CLASSES, referencing the coded classification of the insurance.
• INSURANCE_CD — the insurance code value carried directly on the row alongside the coded reference.
• INSURANCE_PROVIDER — the name or identifier of the insurance carrier.
• POLICY_NUMBER — the policy identifier issued by the provider.
• START_DATE and END_DATE — the coverage validity window, essential for determining whether insurance was active at a given point in time.
• ORG_ID — the operating unit discriminator supporting multi-org access control.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 — the descriptive flexfield segment, providing extensibility for installation-specific health insurance data.

Common Use Cases and Queries

Primary use cases center on verifying a student's insurance coverage, reporting on active policies by provider, and integrating party insurance data into downstream records. A typical active-coverage query joins the person and coded classification tables and filters on the validity window.

• Retrieve all insurance records for a person: select from IGS_PE_HLTH_INS_ALL where PERSON_ID equals the target party identifier.
• Determine coverage as of a date: filter where START_DATE is less than or equal to the reference date and END_DATE is null or greater than the reference date.
• Report by provider or policy: aggregate on INSURANCE_PROVIDER or POLICY_NUMBER, using ORG_ID to scope results to the correct operating unit.
• Resolve the insurance classification by joining INSURANCE_ID to IGS_PE_CODE_CLASSES, and the party identity by joining PERSON_ID to HZ_PARTIES.
• Extract flexfield data by combining ATTRIBUTE_CATEGORY with the relevant ATTRIBUTE columns for installation-specific reporting.

Related Objects

The documented foreign keys and surrounding Student System objects establish the following significant relationships:

• HZ_PARTIES — joined on IGS_PE_HLTH_INS_ALL.PERSON_ID to HZ_PARTIES.PARTY_ID, supplying party identity.
• IGS_PE_CODE_CLASSES — joined on INSURANCE_ID to resolve the insurance classification.
• IGS_PE_HLTH_INS_PK / IGS_PE_HLTH_INS_U1 — the primary key constraint and supporting unique index on HEALTH_INS_ID.
• Other IGS_PE person entities that share the PERSON_ID anchor and rely on HZ_PARTIES for party context.

Because the table is marked obsolete and not implemented in the reference database, consumers should validate availability before depending on it in customizations, concurrent programs, or interface extracts.