Search Results ibe_dsp_tpl_ctg




Overview
Explore in AI Chat

Overview

IBE_DSP_TPL_CTG is a transactional mapping table within the Oracle E-Business Suite iStore (IBE) module. Its documented purpose is to store the mapping between catalog categories and logical display templates, allowing iStore to control how products belonging to a given category are rendered on the storefront. Each row associates a category, identified by CATEGORY_ID, with a logical template reference carried by the ITEM_ID column. The table is owned by the IBE schema and is marked VALID in ETRM for both the 12.1.1 and 12.2.2 releases.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is link. IBE_DSP_TPL_CTG behaves as an associative construct: it does not hold a descriptive business entity of its own, but rather records the relationship between a product category (MTL_CATEGORIES_B) and a template item (JTF_AMV_ITEMS_B). This suggests treating it as a link table rather than a hub or satellite when designing a warehouse layer, with the referenced categories and template items modeled as hubs.

Key Information Stored

The documented physical schema for 12.2.2 contains 10 columns. The most significant are described below.

  • TPL_CTG_ID — the surrogate primary key, defined by the IBE_DSP_TPL_CTG_PK constraint. It uniquely identifies each category-to-template mapping row and is the recommended join key for downstream references.
  • CATEGORY_ID — foreign key to MTL_CATEGORIES_B, identifying the product category being mapped. This is the primary business-key candidate alongside ITEM_ID.
  • ITEM_ID — foreign key to JTF_AMV_ITEMS_B, referencing the logical template item used for the category. Together with CATEGORY_ID it forms the unique business key via the IBE_DSP_TPL_CTG_UK1 constraint.
  • SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-organization / data-security partitioning of the mapping rows.
  • OBJECT_VERSION_NUMBER — used for optimistic locking and concurrency control during updates through the iStore application layer.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS WHO columns recording the audit trail of row creation and modification.

The combination of CATEGORY_ID and ITEM_ID in the IBE_DSP_TPL_CTG_UK1 unique index is the natural business key, while TPL_CTG_ID remains the technical surrogate that applications use for referential integrity.

Common Use Cases and Queries

Typical queries revolve around determining which template governs a category, or which categories use a given template.

  • Retrieve all category-template mappings for a storefront reporting extract:

SELECT tpl_ctg_id, category_id, item_id, last_update_date FROM ibe.ibe_dsp_tpl_ctg;

  • Join to MTL_CATEGORIES_B to resolve category names for a specific template:

SELECT c.category_id, c.category_name, t.item_id FROM ibe.ibe_dsp_tpl_ctg t, mtl_categories_b c WHERE t.category_id = c.category_id AND t.item_id = :item_id;

  • Detect duplicate or conflicting mappings where a category is associated with more than one template — useful for data-cleansing audits before storefront release.
  • Reconciliation of template assignments across environments, comparing CATEGORY_ID/ITEM_ID pairs between source and target instances.

Related Objects

  • MTL_CATEGORIES_B — referenced via CATEGORY_ID; holds the category definitions.
  • JTF_AMV_ITEMS_B — referenced via ITEM_ID; holds the logical template items.
  • FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; governs row-level security partitioning.
  • IBE_DSP_TPL_CTG_PK / IBE_DSP_TPL_CTG_UK1 — the primary and unique constraints enforcing identity and business-key uniqueness.