Results for “campaign_code”

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

Overview

The AST_COLLTRL_HISTORY_V view is a TeleSales (AST) reporting object in the APPS schema that consolidates the historical activity of marketing collateral delivered to customers and prospects. It merges two distinct channels of collateral distribution — physical shipments executed through the Order Management (OE) pipeline and electronic deliveries executed through the Advanced Marketing (AMS) fulfillment infrastructure — into a single, consistent result set for reporting and integration. This union-style design allows TeleSales agents, marketing analysts, and downstream extracts to query one object rather than reconciling OE_ORDER_LINES_ALL and JTF_FM_CONTENT_HISTORY independently.

The view is registered as VALID and is defined over synonym references to the OE, HZ, MTL, and AMS schemas. It is principally consumed for campaign response analysis, collateral spend and volume tracking, and customer contact history displays within the TeleSales HTML user interface.

Underlying Base Objects

The documented base objects are AMS_DELIVERABLES_ALL_B, AMS_DELIVERABLES_ALL_TL, HZ_CUST_ACCOUNTS, HZ_PARTIES, JTF_FM_CONTENT_HISTORY, JTF_FM_REQUEST_HISTORY, MTL_SYSTEM_ITEMS, OE_ORDER_HEADERS_ALL, and OE_ORDER_LINES_ALL. The first half of the UNION joins MTL_SYSTEM_ITEMS to OE_ORDER_LINES_ALL on organization and inventory item, then to OE_ORDER_HEADERS_ALL on header identifier, and finally to HZ_PARTIES through HZ_CUST_ACCOUNTS using the sold-to contact relationship. This branch returns only lines with a non-null request date and is labelled with a REQUEST_TYPE of PHYSICAL.

The second half joins the AMS deliverable base and translation tables to JTF_FM_CONTENT_HISTORY and JTF_FM_REQUEST_HISTORY, resolving the party through HZ_PARTIES. Content type 10 restricts this branch to the relevant deliverable content class, and the result is labelled ELECTRONIC with a hard-coded shipping method of EMAIL. Because JTF_FM_REQUEST_HISTORY is itself a view, recursive dependency tracking in ETRM should be extended to its underlying request tables when performing impact analysis.

Key Columns

• ORDER_NUMBER — OE order number for physical shipments; the AMS branch substitutes the request history identifier, so values are not strictly numeric in all rows.
• REQUEST_DATE — order line request date for physical records and submission timestamp for electronic records.
• REQUEST_STATUS — derived status using DECODE over the OPEN, BOOKED, and CANCELLED flags, yielding ENTERED, BOOKED, CANCELLED, or UNKNOWN. The AMS branch supplies the outcome code.
• PARTY_ID / FULL_NAME — customer identifier and a formatted name that resolves person and organization parties differently.
• REQUEST_TYPE — PHYSICAL or ELECTRONIC, the discriminator between the two UNION branches.
• CAMPAIGN_CODE / ITEM_NAME / ITEM_DESCRIPTION — marketing source identifier and item or deliverable naming.
• SHIPPED_QTY — the shipped quantity taken from OE_ORDER_LINES_ALL.SHIPPED_QUANTITY for physical rows; the AMS branch returns a literal zero.
• QUANTITY / UOM — ordered quantity or delivered quantity depending on branch, with the order quantity UOM for physical records.
• SHIPPING_METHOD / MAIL_TO — carrier method for physical shipments or the literal EMAIL, with the electronic destination address in MAIL_TO.

Common Use Cases and Queries

A frequent requirement is identifying collateral that has been ordered but not yet shipped, which the SHIPPED_QTY column supports directly. Because the AMS branch always returns zero for shipped quantity, filter on REQUEST_TYPE to isolate physical records before applying quantity logic.

SELECT order_number, request_date, full_name, item_name, shipped_qty, quantity FROM apps.ast_colltrl_history_v WHERE request_type = 'PHYSICAL' AND shipped_qty = 0 AND request_status = 'BOOKED';

Analysts also profile the electronic channel separately, aggregating deliverable volume by campaign and item:

SELECT campaign_code, item_name, COUNT(*) delivered_count FROM apps.ast_colltrl_history_v WHERE request_type = 'ELECTRONIC' GROUP BY campaign_code, item_name;

A third common pattern is a customer-level contact history extract joining PARTY_ID to HZ_PARTIES or HZ_CUST_ACCOUNTS for enrichment, ordered by REQUEST_DATE descending. Performance is best when queries constrain REQUEST_DATE or PARTY_ID, since both UNION branches read high-volume transaction tables.