Results for “ams_hz_b2c_mapping_v”

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

Overview

AMS_HZ_B2C_MAPPING_V is a valid database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS (Marketing) product family. Its documented purpose is to store the mapping of the AMS_IMP_SOURCE_LINES table and to support the population of data from an external data file during list import processing. The view functions as a staging and transformation layer: raw columnar values held in the import source lines (generic COL10 through COL70 attributes) are re-projected into semantically named fields that correspond to Trading Community Architecture (TCA) party, person, address, and contact point attributes. This projection allows Marketing import routines to translate a flat, position-based source file layout into structured attributes that can be validated, deduplicated, and ultimately loaded into HZ tables.

Underlying Base Objects

The documented referenced base objects for this view are AMS_IMP_SOURCE_LINES and AMS_IMP_LIST_HEADERS_ALL, both exposed in the APPS schema through synonyms. AMS_IMP_SOURCE_LINES is the primary source table, and the view selects nearly all of its columns—including IMPORT_SOURCE_LINE_ID, IMPORT_LIST_HEADER_ID, PARTY_ID, LOAD_STATUS, ENABLED_FLAG, OBJECT_VERSION_NUMBER, NOTES, RESOURCE_ID, BATCH_ID, LOCATION_ID, CONTACT_POINT_ID, and the administrative WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN). The AMS_IMP_LIST_HEADERS_ALL object provides the parent import list header context. The view inherits the multi-tenant and audit characteristics of these base tables, which is relevant for Oracle EBS 12.1.1 and 12.2.2 installations where Marketing list import is configured.

Key Columns

The view exposes two categories of columns. The first identifies the import record and its processing state: IMPORT_SOURCE_LINE_ID (primary key of the source line), IMPORT_LIST_HEADER_ID (parent import list), IMPORT_SUCCESSFUL_FLAG, LOAD_STATUS, ENABLED_FLAG, DEDUPE_KEY, and OBJECT_VERSION_NUMBER. The second maps positional source columns to named TCA attributes. Party and person attributes include PARTY_ID, PERSON_FIRST_NAME (COL10), PERSON_MIDDLE_NAME (COL11), PERSON_LAST_NAME (COL12), PERSON_NAME_SUFFIX (COL13), PERSON_NAME_PREFIX (COL14), PERSON_PRE_NAME_ADJUNCT (COL37), SALUTATION (COL38), GENDER (COL34), and HOUSEHOLD_INCOME (COL35). Address attributes include COUNTRY (COL17), ADDRESS1 (COL18), ADDRESS2 (COL19), CITY (COL20), COUNTY (COL21), STATE (COL22), PROVINCE (COL23), POSTAL_CODE (COL24), and the extended address elements COL51 through COL67 (address3, address4, apartment flag, PO box, house number, street, suite, postal plus4, and related flags). Contact attributes include EMAIL_ADDRESS (COL26), phone country code, area code, number, and extension (COL27–COL30), plus BEST_TIME_CONTACT_BEGIN and BEST_TIME_CONTACT_END (COL15–COL16) and TIME_ZONE (COL25). SITE_USE_TYPE (COL36) governs the intended use of the resulting party site. Notably, the view does not expose a column literally named PARTY_LOCATION_ID; the nearest documented equivalents are LOCATION_ID and CONTACT_POINT_ID, which map to the underlying party site and contact point records.

Common Use Cases and Queries

Typical usage involves monitoring the status of a list import batch, validating that source columns are being mapped to the correct TCA attributes, and diagnosing failed or disabled source lines. A representative query retrieves mapped person and address data for lines belonging to a specific import list:

• SELECT import_source_line_id, party_id, person_first_name, person_last_name, address1, city, state, postal_code, country, email_address, load_status FROM apps.ams_hz_b2c_mapping_v WHERE import_list_header_id = :p_header_id AND enabled_flag = 'Y';
• SELECT import_source_line_id, load_status, import_successful_flag, dedupe_key FROM apps.ams_hz_b2c_mapping_v WHERE load_status <> 'COMPLETE';
• Joining to AMS_IMP_LIST_HEADERS_ALL on IMPORT_LIST_HEADER_ID to correlate source lines with their parent import list header.

Because the view presents positional source columns under descriptive aliases, it is particularly useful for verifying file-to-attribute mapping before and after a Marketing list import run, and for building reconciliation reports that compare staged source values against the corresponding HZ party, location, and contact point records identified by PARTY_ID, LOCATION_ID, and CONTACT_POINT_ID.