Search Results as_collateral_cat_all




Overview
Explore in AI Chat

Overview

The AS_COLLATERAL_CAT_ALL table is a core data object within the Oracle E-Business Suite (EBS) Sales Foundation module (AS). It functions as a junction or intersection table, establishing a many-to-many relationship between promotional collateral and product categories. Its primary role is to define which specific marketing collateral items are associated with which product categories for a given sales promotion. This linkage is essential for managing targeted marketing campaigns, ensuring that promotional materials are correctly aligned with the relevant products in the catalog, and enabling precise reporting on promotion effectiveness by category.

Key Information Stored

The table's structure is defined by its primary and foreign key relationships. The documented primary key, AS_COLLATERAL_CATEGORIES_PK, is a composite key based on the PROMOTION_ID column. This indicates that the table's unique identifier is the combination of a promotion and its associated category. The two critical foreign key columns are:

  • PROMOTION_ID: Links directly to the AS_PROMOTIONS_ALL table, identifying the specific sales promotion to which the collateral-category mapping belongs.
  • CATEGORY_ID: Links directly to the AS_CATEGORIES_ALL table, identifying the product category associated with the collateral for the linked promotion.

While the exact descriptive columns are not detailed in the provided metadata, the table's purpose implies it stores the relationship itself, likely serving as a reference point for other transactional or reporting processes within the Sales module.

Common Use Cases and Queries

This table is central to queries that analyze or report on the structure of marketing promotions. A common use case is generating a list of all product categories linked to collateral for a specific promotion to validate campaign targeting. Another scenario involves identifying all promotions that target a particular high-value product category for performance review. A typical SQL pattern would join this table to its parent tables to retrieve meaningful descriptions:

SELECT prom.promotion_name, cat.category_name
FROM osm.as_collateral_cat_all cca,
     osm.as_promotions_all prom,
     osm.as_categories_all cat
WHERE cca.promotion_id = prom.promotion_id
AND cca.category_id = cat.category_id
AND prom.promotion_id = :p_promo_id;

This table is also critical for data integrity when setting up or cloning promotions, ensuring category associations are correctly maintained.

Related Objects

The AS_COLLATERAL_CAT_ALL table has defined foreign key dependencies on two primary master tables in the AS schema, as per the provided metadata:

  • AS_PROMOTIONS_ALL: The table is a child of AS_PROMOTIONS_ALL via the foreign key on the PROMOTION_ID column. This enforces that every collateral-category association must belong to a valid, existing promotion.
  • AS_CATEGORIES_ALL: The table is a child of AS_CATEGORIES_ALL via the foreign key on the CATEGORY_ID column. This ensures that every associated category is a valid entry in the product category hierarchy.

As a junction table, it is likely referenced by various promotional reporting views or APIs within the Sales Foundation module to provide a complete picture of promotion composition and targeting.