Search Results mailing_postal_code

Overview

The AS_INVALID_ADDR_CONTACT_V view belongs to the Oracle EBS Sales Foundation (AS) product family and is documented as a "View of contacts with invalid addresses." It functions as a diagnostic reporting object that isolates contact records whose mailing addresses have failed validation or were flagged as invalid by the address validation process. Because the view joins contact, customer, address, and territory information into a single denormalized result set, it allows functional and technical users to identify contacts requiring remediation without navigating multiple transactional tables.

In EBS release 12.1.1 and 12.2.2, this view supports data-quality and cleanup workflows, particularly in environments where address validation (through the AS module's invalid address tracking mechanism) has identified records that cannot be reliably used for correspondence, statement generation, or integration feeds. The view is read-only and is not implemented as a stored object in every database; the ETRM metadata for 12.2.2 explicitly notes "Not implemented in this database," meaning that depending on the installed product set and patching level, the object may only be present where the address validation feature has been deployed.

Underlying Base Objects

The view text reveals four base tables and one view in the FROM clause, all fully qualified against the APPS schema:

• RA_CONTACTS (alias CONT) — the primary driver, supplying contact identity, name, job title, status, and the foreign keys CONTACT_ID, CUSTOMER_ID, ADDRESS_ID, and MAILING_ADDRESS_ID.
• RA_CUSTOMERS (alias CUST) — supplies customer name and number, joined on CUSTOMER_ID.
• RA_ADDRESSES (alias CONT_ADDR) — the contact's address details, joined on MAILING_ADDRESS_ID = ADDRESS_ID.
• AS_INVALID_ADDR (alias INADDR) — the invalid address registry, joined on ADDRESS_ID; this restricts output to records known to be invalid.
• FND_TERRITORIES_TL (alias TERR2) — supplies the territory short name, outer-joined on COUNTRY with LANGUAGE tied to USERENV('LANG').

The joins are inner except for the territory lookup, ensuring contacts appear even when a matching territory translation row is absent.

Key Columns

The projection exposes contact-level, customer-level, and address-level attributes. Notably, the columns listed in the ETRM metadata (such as MAILING_ADDRESS1, MAILING_CITY, MAILING_COUNTRY_CODE) are the user-facing aliases, while the underlying view text selects them from RA_ADDRESSES as ADDRESS1, CITY, COUNTRY, and so forth. Key columns include:

• CONTACT_ID, LAST_NAME, FIRST_NAME, CONTACT_NUMBER — contact identity for remediation tracking.
• CONTACT_STATUS_CODE / CONTACT_ACTIVE — the DECODE expression marks active ('A') contacts with an asterisk for quick visual filtering.
• CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER — customer context.
• MAILING_ADDRESS_ID and MAILING_ADDRESS1..4, CITY, STATE, PROVINCE, POSTAL_CODE, COUNTY, COUNTRY_CODE — the invalid address content itself.
• MAILING_COUNTRY — the translated territory short name derived from FND_TERRITORIES_TL.

This structure directly answers the user's "mailing_address1" search: the mailing address line is surfaced as MAILING_ADDRESS1, a primary target for correction.

Common Use Cases and Queries

Typical use cases include auditing invalid contact addresses before a mail campaign, preparing data correction scripts, and building exception reports for the Sales Foundation data steward. A representative query follows:

SELECT contact_number, last_name, first_name, customer_name, mailing_address1, mailing_city, mailing_country FROM apps.as_invalid_addr_contact_v WHERE contact_active = '*' ORDER BY customer_name, last_name;

To locate records specifically for a given territory or postal pattern, add predicates on MAILING_COUNTRY or MAILING_POSTAL_CODE. Because the object may not exist in every instance, technologists should verify its presence via DBA_VIEWS or ALL_VIEWS before embedding it in a concurrent program or report.