Results for “igs_ad_hlth_ins_int_all”

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

Overview

IGS_AD_HLTH_INS_INT_ALL is a staging interface table in the Oracle E-Business Suite IGS (Student System) product schema. It holds health and insurance detail records for persons prior to import into the permanent student-system tables. Records are loaded by the Health and Insurance interface program, validated within the interface, and then transferred to the destination health and insurance tables. Retained rows generally correspond to records that have not yet been processed, failed validation, or were flagged for review.

The table is owned by the IGS schema, has status VALID, and is documented in ETRM 12.1.1 with 43 columns. The primary key is the unique constraint IGS_AD_HLTH_INS_INT_PK on the column INTERFACE_HLTH_ID. A heuristic Data Vault classification drawn from the foreign-key structure places this object as standalone; in practice this reflects its role as a transient interface area rather than a modelled warehouse entity, and any Data Vault design would more realistically treat it as a staging source for a person health/insurance satellite.

Key Information Stored

The surrogate primary key is INTERFACE_HLTH_ID, a system-generated sequence value uniquely identifying each staged row. Business-key candidates that drive matching and duplicate detection include ORG_ID combined with INTERFACE_ID and INTERFACE_RUN_ID, which scope a record to an operating unit and to the specific interface program invocation that produced it. MATCH_IND indicates the result of the matching process against existing person records, while DUP_HLTH_INS_ID identifies a suspected duplicate in the destination table.

Health and insurance content is carried in INSURANCE_PROVIDER, INSURANCE_CD, POLICY_NUMBER, START_DATE, and END_DATE, describing the carrier, insurance category, policy reference, and coverage window. Processing status is tracked with STATUS and ERROR_CODE, where a populated ERROR_CODE marks a row rejected during validation. INTCH interface rows also carry the standard concurrent-program audit columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE, plus CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN. ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 provide the flexfield-style descriptive columns used to carry supplemental values from the source extract.

Common Use Cases and Queries

The dominant use case is interface monitoring: identifying rows that failed validation before purge so they can be corrected and resubmitted. A typical query filters on a specific run and on error status.

• SELECT INTERFACE_HLTH_ID, POLICY_NUMBER, STATUS, ERROR_CODE FROM IGS.IGS_AD_HLTH_INS_INT_ALL WHERE INTERFACE_RUN_ID = :run_id AND ERROR_CODE IS NOT NULL;
• SELECT ORG_ID, INTERFACE_ID, COUNT(*) FROM IGS.IGS_AD_HLTH_INS_INT_ALL WHERE INTERFACE_RUN_ID = :run_id GROUP BY ORG_ID, INTERFACE_ID;
• SELECT INTERFACE_HLTH_ID, MATCH_IND, DUP_HLTH_INS_ID FROM IGS.IGS_AD_HLTH_INS_INT_ALL WHERE MATCH_IND IS NOT NULL;

Reporting uses include reconciliation of staged versus successfully imported policy counts, validation of coverage START_DATE and END_DATE to catch inverted or expired ranges, and duplicate analysis using DUP_HLTH_INS_ID. Because the table is an interface rather than a system of record, all queries should be scoped by INTERFACE_RUN_ID or ORG_ID to avoid mixing successive load cycles.

Related Objects

The table is documented as standalone in the mined foreign-key data, meaning no outbound foreign keys are enforced at the database level. Related processing objects follow the functional path of the interface rather than physical constraints.

• IGS_AD_HLTH_INS_INT_PK — the primary key constraint on INTERFACE_HLTH_ID.
• The IGS health and insurance base tables that hold the imported results, keyed by person and identified via DUP_HLTH_INS_ID and MATCH_IND.
• IGS person / student tables, matched through MATCH_IND during validation.
• The Health and Insurance import concurrent program, whose run is tracked by REQUEST_ID and INTERFACE_RUN_ID.
• Interface error reporting concurrent programs that consume STATUS and ERROR_CODE.

Direct DML against this table is not supported; corrections should be made through the interface program or the source extract rather than by updating staged rows in place.