Search Results birth_or_selected




Overview
Explore in AI Chat

Overview

AMS_LT_HZ_CITIZENSHIP is a read-only data source view owned by the APPS schema and registered under the AMS (Marketing) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as a filtered projection of the TCA citizenship entity, exposing a curated column set that Oracle Marketing uses for list generation, audience segmentation, and campaign targeting. The view is part of the AMS_LT (Marketing "List Template" / data source) family of objects, which supply the queryable record sources consumed by the Marketing List Builder and related campaign workbench components.

The view does not store data. It is a runtime access layer whose primary purpose is to restrict the TCA citizenship records to active rows and to surface only the attributes required by marketing sourcing. The name and structure identify it as a "base view" rather than a secured or row-restricted view; row-level visibility is governed by TCA access controls on the underlying entity rather than by the view itself. It is documented as VALID with the owner APPS and is listed in the ETRM metadata as a view over HZ_CITIZENSHIP.

Underlying Base Objects

The view is defined over a single documented base object, HZ_CITIZENSHIP, exposed through a synonym. HZ_CITIZENSHIP is the TCA (Trading Community Architecture) entity that records the citizenship and nationality information of a party, including the relationship between a party, a country of citizenship, and the associated recognition and disownment dates. Because the source is a synonym resolving to the TCA registry table, the view inherits the TCA data model's partitioning, indexing, and multi-tenant access characteristics.

The defining predicate is WHERE STATUS = 'A', which limits the result set to active citizenship records. Rows with inactive or logically deleted status are excluded at the view level, so consumers of AMS_LT_HZ_CITIZENSHIP never see historical or superseded citizenship rows unless the status is reactivated. This filtering is significant for marketing use, because it prevents stale or disowned citizenship associations from entering audience queries. No joins, outer joins, or aggregations appear in the view text; it is a straight column projection with a single filter, which keeps query performance close to that of the base table and allows the optimizer to push predicates down to HZ_CITIZENSHIP.

Key Columns

  • CITIZENSHIP_ID — Primary identifier for the citizenship record; the join key back to HZ_CITIZENSHIP and to related TCA entities.
  • BIRTH_OR_SELECTED — Indicator distinguishing citizenship acquired by birth from citizenship selected or elected by the party. This is the column most commonly referenced in list-building predicates that target nationality-based segments.
  • PARTY_ID — Identifier of the party (person or organization) to whom the citizenship belongs; the principal join to HZ_PARTIES and to marketing audience sources.
  • COUNTRY_CODE — Country of citizenship, typically referencing FND territory or ISO country codes.
  • DATE_DISOWNED and DATE_RECOGNIZED — Effective dates marking when the citizenship was renounced or recognized.
  • DOCUMENT_REFERENCE and DOCUMENT_TYPE — Supporting documentation details for the citizenship claim.
  • END_DATE — End date of the citizenship record's validity.

Common Use Cases and Queries

Typical usage is within Marketing list templates and custom audience queries that select parties by nationality. A representative query filters active citizens of a given country and returns party identifiers for downstream campaign processing:

  • SELECT party_id, country_code FROM apps.ams_lt_hz_citizenship WHERE country_code = 'US';
  • SELECT party_id FROM apps.ams_lt_hz_citizenship WHERE birth_or_selected = 'B';
  • SELECT c.party_id, p.party_name FROM apps.ams_lt_hz_citizenship c, apps.hz_parties p WHERE c.party_id = p.party_id AND c.country_code = 'GB';

Because the view is not indexed independently, joins to HZ_PARTIES or HZ_PERSON_PROFILES should be driven from the base table's primary and foreign key indexes. Queries should apply the COUNTRY_CODE and PARTY_ID predicates early to exploit selectivity, and developers should avoid applying functions to filtered columns such as BIRTH_OR_SELECTED where indexing or predicate pushdown may be affected. The view is a supported, documented data source for AMS and is not intended for direct DML; all maintenance of citizenship data occurs against HZ_CITIZENSHIP through the standard TCA APIs.