Search Results citizenship_cntry_code




Overview
Explore in AI Chat

Overview

IGS_SV_ADD_DPND_BLK_V is an Oracle E-Business Suite (EBS) view owned by the APPS schema within the IGS — Student System product family. It is a reporting and integration construct rather than a base transactional entity. Its purpose is to surface the data required to render the XML block that describes the addition of a new dependent for a student. In EBS 12.1.1 and 12.2.2, the Student System leverages such views to feed XML payloads consumed by external interfaces, self-service pages, and batch processes that build enrollment or personal-detail messages.

The view is documented as VALID in the ETRM repository. Because it is a view and not a table, it holds no data of its own; all values are derived at query time from its underlying base objects. The column aliasing (for example, CITIZEN_CNTRY_CODE surfaced as CITIZENSHIP_CNTRY_CODE) indicates the view was designed to present a stable external interface while the base table column names may differ or change.

Underlying Base Objects

The view is defined over two base tables in the APPS schema: IGS_SV_PERSONS (aliased PERS) and IGS_SV_DEPDNT_INFO (aliased DPDT). The ETRM excerpt lists no other referenced base objects, so these two tables constitute the complete documented dependency set.

The join restricts rows to active person records and to the "add" action on dependent information. Specifically, the view filters IGS_SV_PERSONS on RECORD_STATUS = 'C', and joins DPDT to PERS on both BATCH_ID and PERSON_ID. A further predicate limits results to DPDT.DEPDNT_ACTION_TYPE = 'A', meaning only rows representing the addition of a dependent are returned. The BATCH_ID acts as the grouping key tying each dependent to the batch that generated it, while PERSON_ID identifies the person record within that batch.

Key Columns

  • BATCH_ID — Identifier of the batch to which the dependent record belongs; the principal join and grouping key.
  • PERSON_ID — The person associated with the dependent information.
  • PRINT_FORM — Indicator controlling whether the dependent details are included when the form is printed.
  • LAST_NAME, FIRST_NAME, MIDDLE_NAME, SUFFIX — Name components of the dependent.
  • BIRTH_DATE, GENDER — Date of birth and gender of the dependent.
  • BIRTH_CNTRY_CODE — Country of birth.
  • CITIZENSHIP_CNTRY_CODE — The country-of-citizenship column (searched by users as citizenship_cntry_code). It is aliased from the base column CITIZEN_CNTRY_CODE on IGS_SV_DEPDNT_INFO; the alias exists so the external XML contract exposes a consistently named attribute.
  • VISA_TYPE — Visa classification of the dependent.
  • RELATIONSHIP — Relationship of the dependent to the student.
  • DEPDNT_ID — Unique identifier of the dependent record.
  • PERSON_NUMBER, PERSON_ID_LONG — Both are exposed as NULL literals in the view definition, reserved as placeholders for the external block format.
  • REMARKS — Free-text remarks attached to the dependent record.

The columns PERSON_NUMBER and PERSON_ID_LONG are deliberately projected as NULL; they provide fixed placeholders so consuming XML templates do not fail when the attribute is expected but unpopulated.

Common Use Cases and Queries

Typical usage involves extracting the dependent-addition block for a given batch prior to generating the XML message. The following query retrieves all dependent-addition rows for one batch:

  • SELECT batch_id, person_id, last_name, first_name, citizenship_cntry_code, relationship FROM apps.igs_sv_add_dpnd_blk_v WHERE batch_id = :p_batch_id;
  • SELECT person_id, dependent_id, birth_cntry_code, visa_type FROM apps.igs_sv_add_dpnd_blk_v WHERE citizenship_cntry_code = 'US';
  • SELECT COUNT(*) FROM apps.igs_sv_add_dpnd_blk_v WHERE batch_id = :p_batch_id;

These patterns are common in inbound/outbound interface validation, where the interface must confirm that dependent records exist and that key attributes such as citizenship and visa type are populated before the XML block is transmitted.