Results for “ams_imp_source_lines”

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

Overview

AMS_IMP_SOURCE_LINES is a Marketing (AMS) module staging table within the Oracle E-Business Suite 12.1.1 and 12.2.2 data model. It stores Persons or Organizations (Prospects) imported from external sources prior to their validation, deduplication, and promotion into the Trading Community Architecture (TCA) registry. The table therefore functions as the inbound landing zone for list-import and prospect-loading processes executed through the Oracle Marketing import concurrent programs.

The documented physical schema in ETRM 12.2.2 records 417 columns owned by the AMS schema, with status VALID. A primary key constraint, AMS_IMP_SOURCE_LINES_PK, is defined on IMPORT_SOURCE_LINE_ID, and a unique index, AMS_IMP_SOURCE_LINES_U1, is also declared on IMPORT_SOURCE_LINE_ID. Based on the foreign key topology supplied in the metadata, the table exhibits satellite-leaning characteristics: it is dominated by descriptive import attributes, a large set of generic COL1 through COL350 placeholder columns, CUSTOM_COLUMN1 through CUSTOM_COLUMN25, and CUSTOM_PROCESSING_STATUS, all of which attach to a central import header and to TCA entities rather than forming a hub or link in their own right. This classification is offered as a heuristic modeling suggestion rather than a normative designation.

Key Information Stored

The table blends a small set of structural and status columns with a very wide, position-based attribute area. The most significant columns are:

• IMPORT_SOURCE_LINE_ID — the surrogate primary key and the unique business-key candidate; it identifies each staging row and is the column referenced by dependent child tables.
• IMPORT_LIST_HEADER_ID — foreign key to AMS_IMP_LIST_HEADERS_ALL, linking the staged line to its parent import list.
• PARTY_ID — foreign key to HZ_PARTIES, populated once the staged prospect is matched or created in TCA.
• ORGANIZATION_ID — a second foreign key to HZ_PARTIES, holding the organization-level party reference for the imported record.
• CONTACT_POINT_ID and LOCATION_ID — foreign keys to HZ_CONTACT_POINTS and HZ_LOCATIONS respectively, resolving the contact and address data for the imported prospect.
• IMPORT_SUCCESSFUL_FLAG, ENABLED_FLAG, IMPORT_FAILURE_REASON, and LOAD_STATUS — status and diagnostic columns that record the outcome of the import attempt.
• RE_IMPORT_LAST_DONE_DATE and RECORD_PROCESSED — control columns for re-import cycles and row-level processing state.
• DEDUPE_KEY and DUPLICATE_FLAG — support the deduplication logic that determines whether a staged prospect matches an existing party.
• REQUEST_ID and BATCH_ID — identify the concurrent request and batch that processed the row.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org style access control.
• OBJECT_VERSION_NUMBER — the standard EBS optimistic locking column.
• COL1 through COL350, CUSTOM_COLUMN1 through CUSTOM_COLUMN25, and CUSTOM_PROCESSING_STATUS — generic, position-mapped staging columns used to absorb arbitrary columns from the external source file.

Common Use Cases and Queries

The table is most often queried to diagnose import failures, to report on the success rate of a prospect load, and to reconcile staged records against their resulting TCA parties. Typical patterns include:

• Identifying rows that failed validation, for example filtering on IMPORT_SUCCESSFUL_FLAG and reading IMPORT_FAILURE_REASON, or joining to AMS_LIST_IMPORT_ERRORS on IMPORT_SOURCE_LINE_ID for structured error text.
• Measuring import throughput per list by grouping on IMPORT_LIST_HEADER_ID and counting rows, which supports load monitoring and batch performance reporting.
• Reconciling staged prospects with TCA by joining PARTY_ID to HZ_PARTIES, and joining LOCATION_ID to HZ_LOCATIONS or CONTACT_POINT_ID to HZ_CONTACT_POINTS to confirm that address and contact data resolved correctly.
• Auditing re-import behavior via RE_IMPORT_LAST_DONE_DATE and RECORD_PROCESSED where lists are periodically refreshed from an external marketing system.

Related Objects

• AMS_IMP_LIST_HEADERS_ALL — parent import list header; joined on AMS_IMP_SOURCE_LINES.IMPORT_LIST_HEADER_ID = AMS_IMP_LIST_HEADERS_ALL.IMPORT_LIST_HEADER_ID.
• AMS_LIST_IMPORT_ERRORS — child table capturing per-line import errors; joined on IMPORT_SOURCE_LINE_ID.
• AMS_PARTY_SOURCES — child table recording the source system for a promoted prospect; joined on IMPORT_SOURCE_LINE_ID.
• HZ_PARTIES — TCA party registry; referenced twice, via PARTY_ID and ORGANIZATION_ID.
• HZ_CONTACT_POINTS — referenced via CONTACT_POINT_ID for phone, email, and other contact methods.
• HZ_LOCATIONS — referenced via LOCATION_ID for the imported address.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for access control.