Search Results ams_request_history




Overview
Explore in AI Chat

Overview

The AMS_REQUEST_HISTORY table is a core transactional data object within the Oracle E-Business Suite Marketing (AMS) module. It functions as a historical log for physical collateral requests generated through the telesales channel. Its primary role is to record the association between a customer request, the specific marketing collateral item ordered, and the corresponding cover letter details. This table is essential for tracking the fulfillment lifecycle of marketing material requests, providing an auditable trail from order placement to delivery. The presence of foreign keys to other AMS tables indicates its integration within the broader marketing execution and campaign management data model.

Key Information Stored

The table's primary purpose, as defined in the ETRM documentation, is to capture the cover letter and the linkage between an order and a collateral item. While a full column list is not provided in the excerpt, the metadata explicitly identifies several critical components. The REQUEST_HISTORY_ID column serves as the unique primary key for each historical record. The COLLATERAL_ID column is a crucial foreign key that links directly to the AMS_DELIVERABLES_ALL_B table, identifying the specific marketing asset (e.g., brochure, sample kit) requested. The ORDER_ID column connects the request to the associated sales order, facilitating integration with order management processes. Additional columns likely store the cover letter text or a reference to it, the request date, and status information.

Common Use Cases and Queries

A primary use case involves auditing and reporting on telesales-driven collateral fulfillment. Analysts can query this table to determine which collateral items are most frequently requested or to track request volumes over time. Troubleshooting specific customer requests often involves joining this table to deliverables and order data. A typical query to find all historical requests for a specific collateral item, leveraging the user's search term, would be:

  • SELECT arh.request_history_id, arh.order_id, arh.collateral_id, ad.deliverable_name FROM ams_request_history arh, ams_deliverables_all_b ad WHERE arh.collateral_id = ad.deliverable_id AND arh.collateral_id = :p_collateral_id;

Another common pattern is generating a report of all requests associated with a particular sales order to understand the complete package of materials sent to a customer.

Related Objects

The AMS_REQUEST_HISTORY table has defined relationships with several other key AMS objects, as indicated by its foreign key constraints. Its most direct relationship is with the AMS_DELIVERABLES_ALL_B table via the COLLATERAL_ID foreign key. This link is fundamental for retrieving descriptive information about the requested marketing collateral, such as its name, type, and version. While not explicitly listed in the provided metadata, the ORDER_ID column strongly implies a relationship with order management tables, potentially OE_ORDER_HEADERS_ALL. The table is also referenced by its primary key constraint, AMS_REQUEST_HISTORY_PK, and may be referenced by other AMS views or interfaces that provide reporting on marketing request history.