Search Results load_lead_data_to_interface

Overview

AMS_IMPORTCLIENT_PVT is a private PL/SQL package in the Oracle E-Business Suite (EBS) Applications schema (APPS) that belongs to the Oracle Marketing (AMS) module. It serves as the core import engine for loading customer and lead data from external sources, such as flat files, staging tables, or third-party systems, into the Oracle Marketing lead and list management interface structures. The package header carries the AUTHID CURRENT_USER directive, meaning it executes with the privileges of the calling user rather than the definer, and it was originally authored in 2001 (header revision 115.8) for release 11i, remaining a stable internal component through 12.1.1 and 12.2.2. Because it is classified as a private (PVT) package, it is not intended as a public API; it is invoked by other AMS packages ("referenced by 2 other packages" per the metadata) that expose the user-facing import workflow. Its primary role is to transform source records into rows in the AS_IMPORT_INTERFACE table, mapping source fields to Oracle Marketing's target columns, and to record or mark errors so that import processing can report failures against the original source lines.

Key Procedures and Functions

• WRITE_MESSAGE — Writes a log or error message to the message infrastructure. It accepts a return status output parameter together with a log reference ID, the message data, and a message type, providing a lightweight logging utility used by the other routines in the package.
• MARK_INSERT_LEAD_ERRORS — A private procedure that marks lead errors back onto the AMS_IMP_SOURCE_LINES table and inserts corresponding error rows into AMS_LIST_IMPORT_ERRORS. It takes at minimum an import list header ID and returns an x_return_status. This is the failure-handling counterpart to successful data loading.
• LOAD_LEAD_DATA_TO_INTERFACE — The procedure directly referenced by the user's search. It drives the loading of lead data from source structures into the interface table, reading the lead field mappings and populating AS_IMPORT_INTERFACE so that the standard lead import concurrent process can consume and validate the records.
• INSERT_LEAD_DATA — Inserts lead records into the interface, complementing the load logic and the error-marking routine as part of the end-to-end import cycle.
• INSERT_LIST_DATA — Performs the analogous insertion for list data, supporting import of list definitions and list membership in addition to leads.

The package also defines several collection typedefs (char_data_set_type_w, num_data_set_type_w, varchar2_2000_set_type, varchar2_4000_set_type, varchar2_150_set_type) used for bulk operations.

Tables Accessed

• AS_IMPORT_INTERFACE / AS_IMPORT_INTERFACE_S — The primary target of the load; rows inserted here are the staging records consumed by downstream import processing.
• AMS_IMP_LIST_HEADERS_ALL — The import job header, providing the import list header ID that scopes each run.
• AMS_IMP_SOURCE_LINES — The raw source rows; updated to mark records that failed validation.
• AMS_LIST_IMPORT_ERRORS / AMS_LIST_IMPORT_ERRORS_S — Stores per-record import errors for later review.
• AMS_LIST_SRC_FIELDS — Field-level source definitions mapped during the load.
• AS_LEAD_IMPORT_ERRORS — Lead-specific import error capture.
• DUAL, DBMS_SQL, PLITBLM — Utility objects used for dynamic SQL and PL/SQL table support.

Usage Notes

AMS_IMPORTCLIENT_PVT is invoked indirectly, typically from the Oracle Marketing "Import Leads" or list import concurrent programs and the associated import setup forms, rather than called directly by end users. Because it is classified PVT, customizations should not call it as a supported API; developers extending import behavior should invoke the public AMS APIs that reference it, or wrap the standard import concurrent program. When diagnosing import failures, administrators should examine AMS_LIST_IMPORT_ERRORS and the source-line status populated by MARK_INSERT_LEAD_ERRORS to determine which records were rejected during the LOAD_LEAD_DATA_TO_INTERFACE stage.