Search Results igs_sv_ev_add_dpd_blk_v

Overview

IGS_SV_EV_ADD_DPD_BLK_V is a reporting view owned by the APPS schema in the Oracle E-Business Suite Student System (IGS) product family. Its documented purpose is to present the "add dependent block for the Exchange Visitor" — that is, the set of dependent (family member) records that are being added to an exchange visitor's SEVIS-relevant record. The view is defined over the IGS_SV_DEPDNT_INFO table and filters that table to rows where DEPDNT_ACTION_TYPE equals 'A' (add). Because the view restricts output to add-action dependents and reshapes identity data into both legacy and long person-number formats, it functions as a dedicated extraction surface for downstream SEVIS batch processing, data validation, and operational reporting rather than as a general-purpose dependent query. The view is marked VALID in the ETRM registry and is exposed under the APPS schema, which is the standard convention for Oracle EBS views that application code and reporting tools consume. No object owner other than APPS is documented, and no separate base objects beyond IGS_SV_DEPDNT_INFO are referenced in the provided metadata.

Underlying Base Objects

The documented source object is a single table, IGS_SV_DEPDNT_INFO, aliased as DPDT in the view text. Every column projected by the view originates from that table, with three exceptions that are derived expressions. The relationship is a straightforward filter-and-project join-less definition: the WHERE clause restricts rows to DEPDNT_ACTION_TYPE = 'A'. Although the ETRM "referenced base objects" field lists none, the embedded view text explicitly names IGS_SV_DEPDNT_INFO, which is the authoritative base table. Dependent records in the Student System are typically keyed by BATCH_ID, PERSON_ID, and DEPDNT_ID, and this view surfaces those keys to allow correlation back to the batch and person records from which the dependent rows were generated.

Key Columns

The view exposes eighteen columns. Identity and matching columns include BATCH_ID, PERSON_ID, DEPDNT_ID, PERSON_NUMBER, and PERSON_ID_LONG. PERSON_NUMBER is derived by zero-padding the first ten characters of DEPDNT_ID via SUBSTR and TO_CHAR with the '0000000000' format mask, while PERSON_ID_LONG applies the same technique to the first fourteen characters with the '00000000000000' mask. Name and demographic columns include LAST_NAME, FIRST_NAME, MIDDLE_NAME, SUFFIX, GENDER, BIRTH_CITY, BIRTH_DATE, BIRTH_CNTRY_CODE, and BIRTH_CNTRY_RESN_CODE. The BIRTH_DATE column stores the dependent's date of birth and is the field most relevant to searches for "birth_date." Citizenship and residency are represented by CITIZENSHIP_CNTRY_CODE (sourced from CITIZEN_CNTRY_CODE) and PERM_RESIDENCY_CODE (sourced from LEGAL_RES_CNTRY_CODE), both aliased for semantic clarity. RELATIONSHIP and REMARKS complete the descriptive payload.

Common Use Cases and Queries

Typical use cases include extracting newly added dependents for SEVIS batch submission, validating that required demographic fields such as BIRTH_DATE and BIRTH_CNTRY_CODE are populated before transmission, and auditing dependent additions by batch. A representative query listing dependents with birth dates for a given batch is:

• SELECT batch_id, person_id, last_name, first_name, birth_date, gender, birth_cntry_code FROM apps.igs_sv_ev_add_dpd_blk_v WHERE batch_id = :p_batch_id;
• SELECT person_number, person_id_long, last_name, birth_date FROM apps.igs_sv_ev_add_dpd_blk_v WHERE birth_date IS NULL;
• SELECT relationship, COUNT(*) FROM apps.igs_sv_ev_add_dpd_blk_v GROUP BY relationship;
• SELECT batch_id, dependent_count FROM (SELECT batch_id, COUNT(*) dependent_count FROM apps.igs_sv_ev_add_dpd_blk_v GROUP BY batch_id) ORDER BY dependent_count DESC;

Because the view filters exclusively on add actions, queries should not be relied upon to retrieve update, delete, or terminate dependent records; those reside in the underlying table under different action type codes. All access should be granted through the APPS schema synonyms and responsibilities.