Search Results as_collateral_v

Overview

AS_COLLATERAL_V is a reporting view within the Oracle E-Business Suite Sales Foundation (AS) module. In Oracle EBS 12.1.1 and 12.2.2, the AS module supports sales promotional activities, and this view specifically exposes collateral-related promotional data. Collateral in this context refers to promotional materials and physical marketing assets — such as printed brochures, kits, and inventory items — that are tracked against promotional campaigns.

The view presents a denormalized, read-only representation of collateral promotions, joining the base promotion record with descriptive lookups, territory information, price list names, and word processor configuration. Its purpose is to provide a single queryable source for collateral reporting, integration, and inquiry screens, sparing consumers from manually reconstructing the joins across the multiple underlying tables. As a view (rather than a table), it carries no storage of its own; it is defined entirely by its SELECT statement over the base objects described below.

Per the documented ETRM metadata, this view is marked "Not implemented in this database," meaning the view text is supplied for reference but the object may not be deployed in every environment. This is relevant when troubleshooting "invalid object" or metadata-only lookups in ETRM.

Underlying Base Objects

The view is defined over the following base objects, as documented in its view text:

• AS_PROMOTIONS (P) — the driving table, filtered to rows where P.TYPE = 'C' (collateral).
• AS_WORD_PROCESSORS (WP) — outer-joined to supply word processor attributes.
• AS_LOOKUPS (LKUP1) — outer-joined on lookup type 'COLLATERAL_STATUS'.
• AS_LOOKUPS (LKUP2) — outer-joined on lookup type 'LANGUAGE'.
• FND_TERRITORIES_TL (TERR) — outer-joined by country code and language.
• SO_PRICE_LISTS_VL (SPL) — outer-joined by price list ID.

All joins except the driving table use Oracle outer-join syntax (the (+) operator), ensuring that a collateral promotion row is returned even where lookup, territory, word processor, or price list data is absent.

Key Columns

The view exposes a broad column set. High-value columns include:

• PROMOTION_ID, CODE, NAME — the promotional identifier and its user-facing code and name.
• STATUS — decoded so that kits map to PERSONAL_KIT or PUBLIC_KIT based on PUBLIC_FLAG; otherwise the raw status code is returned.
• LKUP1.MEANING / LKUP2.MEANING — the translated collateral status and language descriptions.
• TERRITORY_SHORT_NAME — the country/territory short name.
• CURRENCY_CODE, EXPECTED_PROMOTION_COST, ACTUAL_PROMOTION_COST, COLLATERAL_CHARGEBACK_AMT — financial attributes of the collateral promotion.
• EXPECTED_NUM_OF_RESPONSES, ACTUAL_NUM_OF_RESPONSES — response-tracking metrics.
• COLLATERAL_QTY_ON_HAND, COLLATERAL_INVENTORY_ITEM_ID, COLLATERAL_ORGANIZATION_ID — inventory linkage for physical collateral.
• WORD_PROCESSOR_ID / WORD_PROCESSOR_NAME and derived DECODE(...) flags indicating whether a word processor or inventory item exists, plus PRINT_FLAG, FAX_FLAG, EMAIL_FLAG.
• PRICE_LIST_ID / SPL.NAME — associated price list.
• ATTRIBUTE1–ATTRIBUTE15, ATTRIBUTE_CATEGORY — descriptive flexfield columns.
• LAST_UPDATE_DATE, CREATED_BY — standard Who columns.

Common Use Cases and Queries

Typical scenarios include collateral inventory reporting, promotional cost analysis, and integration extracts that feed external marketing systems.

List active collateral promotions with status and territory:

SELECT promotion_id, code, name, status,
       territory_short_name, enabled_flag
FROM   as_collateral_v
WHERE  enabled_flag = 'Y';

Identify collateral linked to inventory items for stock checks:

SELECT promotion_id, name, collateral_inventory_item_id,
       collateral_organization_id, collateral_qty_on_hand
FROM   as_collateral_v
WHERE  collateral_inventory_item_id IS NOT NULL;

Cost and response comparison for kits:

SELECT name, status, currency_code,
       expected_promotion_cost, actual_promotion_cost,
       expected_num_of_responses, actual_num_of_responses
FROM   as_collateral_v
WHERE  status IN ('PERSONAL_KIT','PUBLIC_KIT');

Because the view is metadata-only in some environments, implementations should confirm its existence before publishing dependent reports, and consider building equivalent queries directly against AS_PROMOTIONS where the view is unavailable.