Search Results igs_pe_hlth_ins_pk




Overview
Explore in AI Chat

Overview

The IGS_PE_HLTH_INS_ALL table is a core data object within the now-obsolete Oracle Student System (IGS) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It functions as a master table for storing health insurance information associated with individuals, primarily students, within the institutional database. Its role is to maintain a historical and current record of a person's health insurance coverage, which was critical for compliance, student services, and administrative reporting in the legacy student system. The "_ALL" suffix indicates it is a multi-organization enabled table, designed to store data partitioned by a business group or operating unit. It is important to note that the provided metadata explicitly states this table is "Not implemented in this database," suggesting it may be a reference or placeholder structure in standard deployments, with actual implementations potentially using customizations or alternative objects.

Key Information Stored

The table's primary purpose is to link a person with specific health insurance details. The key columns, as indicated by its constraints, are HEALTH_INS_ID, PERSON_ID, and INSURANCE_ID. The HEALTH_INS_ID column serves as the unique primary key identifier for each health insurance record. The PERSON_ID column is a foreign key that links the record to a specific individual in the HZ_PARTIES table, which is the central repository for parties (people and organizations) in Oracle EBS. The INSURANCE_ID is a foreign key linking to the IGS_PE_CODE_CLASSES table, which likely stored the valid types or classifications of insurance plans available within the system. While the full column list is not provided, typical data in such a table would include policy numbers, coverage effective and end dates, insurer details, and potentially dependent information.

Common Use Cases and Queries

In a functional IGS environment, this table would support operations related to student enrollment compliance, health center administration, and emergency contact procedures. Common reporting use cases would include generating lists of students with expired insurance for follow-up, verifying coverage for clinical placements, or auditing insurance data for a specific academic term. A typical query pattern would join this table to person and code tables to retrieve readable information. For example, a basic select statement might be: SELECT p.party_name, c.code, h.coverage_start_date, h.coverage_end_date FROM igs_pe_hlth_ins_all h, hz_parties p, igs_pe_code_classes c WHERE h.person_id = p.party_id AND h.insurance_id = c.code_id AND h.coverage_end_date < SYSDATE; This would identify individuals with lapsed insurance coverage.

Related Objects

The table has documented foreign key relationships with two other critical EBS tables, defining its integration points within the data model.

  • HZ_PARTIES: This is the foundational Trading Community Architecture (TCA) table. The relationship is established via the column IGS_PE_HLTH_INS_ALL.PERSON_ID, which references a PARTY_ID in HZ_PARTIES. This join retrieves the person's name and other core attributes.
  • IGS_PE_CODE_CLASSES: This IGS-specific code lookup table. The relationship is via the column IGS_PE_HLTH_INS_ALL.INSURANCE_ID, which references a code in IGS_PE_CODE_CLASSES. This join provides the description and classification of the insurance type.
The primary key constraint IGS_PE_HLTH_INS_PK on HEALTH_INS_ID ensures uniqueness and would be referenced by any child tables or views dependent on this health insurance entity.