Search Results load_error_message

Overview

AMS_LEAD_MAPPING_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Oracle Marketing (AMS) module. Its documented status is VALID across the 12.1.1 and 12.2.2 releases. The view presents a denormalized, business-friendly projection of the AMS_IMP_SOURCE_LINES staging table, translating positional column names (COL1, COL2, COL243, and so on) into meaningful attribute names that correspond to lead and contact import fields. In this way the view stores the mapping configuration used by the Marketing import process, which populates lead data from an external data file into the staging area before validation and insertion into the production lead tables.

Within Oracle EBS reporting and integration, the view functions as a semantic layer over raw import staging data. Rather than exposing cryptic positional columns, AMS_LEAD_MAPPING_V gives report developers, interface developers, and technical consultants a stable, documented interface for querying imported lead records, their load status, and the header context to which they belong.

Underlying Base Objects

The ETRM metadata for 12.2.2 documents a single referenced base object: AMS_IMP_SOURCE_LINES, exposed to the view through a synonym. AMS_LEAD_MAPPING_V is therefore a thin SELECT-based view that maps each physical column of the staging table to an alias. The primary key and foreign key columns—IMPORT_SOURCE_LINE_ID and IMPORT_LIST_HEADER_ID—are carried through unchanged, as are the standard EBS Who columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) and the OBJECT_VERSION_NUMBER used for optimistic locking. Because the view performs no joins to other AMS tables such as AMS_IMPORT_LISTS, header-level information is limited to the IMPORT_LIST_HEADER_ID foreign key, which must be joined separately when full list metadata is required.

Key Columns

• IMPORT_SOURCE_LINE_ID / IMPORT_LIST_HEADER_ID — surrogate identifiers linking each staged lead line to its parent import header.
• LOAD_STATUS / IMPORT_SUCCESSFUL_FLAG — control columns indicating whether the staged row was processed and whether import succeeded.
• SOURCE_SYSTEM (COL1) / IMP_LIST_HEADER_NUMBER (COL2) — origin of the data and the human-readable header reference.
• Contact identity — FIRST_NAME (COL26), LAST_NAME (COL24), MIDDLE_INITIAL (COL33), SALUTATION (COL23), JOB_TITLE (COL27).
• Communication attributes — EMAIL_ADDRESS (COL21), PHONE_NUMBER (COL28), AREA_CODE (COL31), and EXTENSION (COL32), together with FAX_NUMBER (COL36) and FAX_AREA_CODE (COL37). Note that a distinct FAX_EXTENSION column does not appear in the documented mapping; fax information is represented by FAX_NUMBER and FAX_AREA_CODE only.
• Address columns — ADDRESS1–ADDRESS4 (COL247–COL250), CITY (COL16), STATE (COL18), POSTAL_CODE (COL17), COUNTRY (COL246).
• Preference flags — DO_NOT_EMAIL_FLAG (COL38), DO_NOT_FAX_FLAG (COL39), DO_NOT_PHONE_FLAG (COL40).
• Marketing attributes — CUSTOMER_CATEGORY_CODE (COL6), CUSTOMER_RANK (COL11), POTENTIAL_REVENUE_CURR_FY (COL9), POTENTIAL_REVENUE_NEXT_FY (COL10), BUDGET_AMOUNT (COL43), CURRENCY_CODE (COL45).

Common Use Cases and Queries

Typical scenarios include auditing staged imports before they are promoted to live leads, troubleshooting failed loads, and reconciling count of source lines against successfully imported contacts.

Query staged leads for a specific import header:

SELECT import_source_line_id,
       first_name,
       last_name,
       email_address,
       phone_number,
       extension,
       fax_number,
       load_status,
       import_successful_flag
  FROM ams_lead_mapping_v
 WHERE import_list_header_id = :p_header_id
 ORDER BY import_source_line_id;

Identify unresolved errors:

SELECT import_source_line_id, customer_name, load_status
  FROM ams_lead_mapping_v
 WHERE NVL(import_successful_flag,'N') = 'N';

Because the view maps directly to a staging synonym and performs no filtering, developers should always qualify queries by IMPORT_LIST_HEADER_ID or LOAD_STATUS to avoid full-table scans and to respect the transient nature of import staging data.