Search Results final_sql_string

Overview

AMS.AMS_EXP_LIST_HEADERS_ALL is an Oracle E-Business Suite table in the Oracle Marketing (AMS) schema that stores the name and other descriptive details about exported data. In EBS 12.1.1 and 12.2.2 it functions as the header repository for export list definitions produced by the marketing export subsystem, capturing the template, ownership, status, scheduling, and — per the column FINAL_SQL_STRING — the resolved SQL statement used to generate the export. The table resides in tablespace APPS_TS_TX_DATA with PCTFREE 10 and is classified as VALID.

Under a heuristic Data Vault classification mined from its foreign-key structure, this object presents as a standalone entity — effectively a hub-style anchor for export header identity, with descriptive and scheduling attributes behaving as satellite-style payload. The classification is a modeling suggestion rather than a documented derivation; the only documented outbound reference is SECURITY_GROUP_ID → FND_SECURITY_GROUPS, and no downstream dependents were captured.

Key Information Stored

The table carries 30 documented columns; the following are the most significant.

• EXPORT_LIST_HEADER_ID (NUMBER) — unique identifier and surrogate primary key, enforced by unique index AMS_EXP_LIST_HEADERS_ALL_U2.
• NAME (VARCHAR2 120) — user-visible export name; indexed non-uniquely by AMS_EXP_LIST_HEADERS_ALL_N1, making it the practical business-key lookup column.
• EXP_TEMPLATE_ID (NUMBER) — foreign key to AMS_EXP_TEMPLATE; indexed by the non-unique AMS_EXP_LIST_HEADERS_ALL_U1, which links the export to its originating template.
• FINAL_SQL_STRING — the resolved SQL text driving export generation; typically the attribute a user investigates when diagnosing export output.
• EXPORT_DATA (CLOB 4000) — stores the export data payload, backed by LOB index SYS_IL0000192401C00017$$.
• EXPORT_TYPE (VARCHAR2 30) — export type, e.g. b2b or b2c.
• STATUS_CODE, STATUS_DATE, USER_STATUS_ID — export status and its timestamp/internal reference.
• OWNER_USER_ID and VIEW_APPLICATION_ID — ownership and the application from which the record is viewable.
• REQUEST_ID — concurrent process that produced the export.
• PARENT_EXP_HEADER_ID, RECURRING_EXP_NAME — self-referencing parent linkage and recurring export naming.
• CANCEL_FLAG, CANCEL_DATE — cancellation control.
• REPEAT_TIME, REPEAT_INTERVAL, REPEAT_UNIT, REPEAT_MODE, REPEAT_START_TIME, REPEAT_END_TIME — scheduling attributes for recurring exports.
• OBJECT_VERSION_NUMBER — optimistic locking column.
• SECURITY_GROUP_ID — hosting/security grouping; references FND_SECURITY_GROUPS.
• Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) support audit reporting.

Common Use Cases and Queries

Typical scenarios include auditing active exports, tracing an export back to its template, and inspecting the generated SQL.

• Listing recent export headers: SELECT export_list_header_id, name, status_code, status_date FROM ams.ams_exp_list_headers_all WHERE status_code = 'ACTIVE' ORDER BY status_date DESC;
• Retrieving the resolved SQL for a named export: SELECT name, final_sql_string FROM ams.ams_exp_list_headers_all WHERE name = :name;
• Joining to templates: SELECT h.name, t.template_name FROM ams.ams_exp_list_headers_all h, ams.ams_exp_template t WHERE h.exp_template_id = t.exp_template_id;
• Correlating exports with concurrent requests via REQUEST_ID against FND_CONCURRENT_REQUESTS.
• Reporting recurring exports by filtering on REPEAT_INTERVAL, REPEAT_UNIT, and REPEAT_MODE.

Related Objects

• AMS.AMS_EXP_TEMPLATE — referenced through EXP_TEMPLATE_ID; the template that defines export structure.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID (the sole documented FK).
• AMS.AMS_EXP_LIST_HEADERS_ALL — self-reference via PARENT_EXP_HEADER_ID for parent/child export hierarchies.
• FND_CONCURRENT_REQUESTS — correlates via REQUEST_ID to identify the concurrent program run.
• FND_USER — resolves OWNER_USER_ID and WHO columns to user identities.
• FND_APPLICATION — resolves VIEW_APPLICATION_ID to the viewing application.