Results for “igs_ad_prospect_v”

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

Overview

The view APPS.IGS_AD_PROSPECT_V is a reporting and integration object delivered with the Oracle E-Business Suite Student System (IGS) product family. Its documented purpose is to describe the various person type instances of a person — specifically, the subset of those instances that have been classified as prospects. The view consolidates prospect identity data with person type definitions and lookup decoding so that its consumers receive not only the raw prospect record but also a human-readable rendering of the prospect's position in the recruitment funnel.

Because the view is owned by APPS and combines several base objects into a single denormalized shape, it is typically used in the student recruitment and admissions domain to report on prospective students, to feed external CRM or marketing systems, and to drive inquiry-to-applicant conversion analysis. Its central functional attribute is FUNNEL_STATUS, which the user searching for "funnel_status" would have been seeking.

Underlying Base Objects

The view text exposes the following referenced objects, all in the APPS schema:

• IGS_PE_TYP_INSTANCES_ALL — the person type instance table, aliased TI, supplying PERSON_ID, FUNNEL_STATUS, PERSON_TYPE_CODE, and the audit columns. Its ROWID is projected as ROW_ID.
• IGS_PE_PERSON_TYPES — the person type definition table, aliased PT; joined on PERSON_TYPE_CODE and filtered to SYSTEM_TYPE = 'PROSPECT', so only prospect system types qualify.
• IGS_LOOKUP_VALUES — joined on FUNNEL_STATUS = LOOKUP_CODE with LOOKUP_TYPE = 'PERSON_TYPE_FUNNEL_STATUS', providing the decoded MEANING as FUNNEL_STATUS_MEANING.
• IGS_PE_PERSON_BASE_V — joined on PERSON_ID, supplying PERSON_NUMBER.

The ETRM metadata lists no additional documented base objects beyond these, meaning the view's lineage is fully expressed by the four joins above. Note that a date predicate restricts results to currently active person type instances: END_DATE IS NULL or the current date falling between START_DATE and END_DATE.

Key Columns

• ROW_ID — the ROWID of the underlying IGS_PE_TYP_INSTANCES_ALL row, useful for direct row addressing.
• PERSON_ID / PERSON_NUMBER — the prospect's primary person identifiers.
• FUNNEL_STATUS — the coded value indicating the prospect's stage in the recruitment funnel.
• FUNNEL_STATUS_MEANING — the decoded description of FUNNEL_STATUS from the PERSON_TYPE_FUNNEL_STATUS lookup.
• PERSON_TYPE_CODE / SYSTEM_TYPE — the prospect's person type categorization; SYSTEM_TYPE is filtered to 'PROSPECT'.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns inherited from the base instance table.

Common Use Cases and Queries

A frequent requirement is to count prospects by funnel stage:

SELECT funnel_status, funnel_status_meaning, COUNT(*) prospect_count
FROM   apps.igs_ad_prospect_v
GROUP  BY funnel_status, funnel_status_meaning
ORDER  BY prospect_count DESC;

A second scenario retrieves detail for a specific prospect:

SELECT person_number, person_id, person_type_code,
       funnel_status_meaning, last_update_date
FROM   apps.igs_ad_prospect_v
WHERE  person_number = :p_person_number;

A third use is integration extraction, selecting only active prospect records and joining to additional IGS tables for address or contact enrichment. Because the view already applies the SYSTEM_TYPE = 'PROSPECT' and effective-date filters, callers need not repeat those predicates. All queries should return consistent results across 12.1.1 and 12.2.2, as the object is documented as VALID in both releases.