Results for “cs_kb_cat_group_denorm”

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

Overview

CS_KB_CAT_GROUP_DENORM is a denormalized association table in the Service (CS) module of Oracle E-Business Suite, owned by the CS schema. It stores the resolved, pre-expanded mapping between Knowledge Base category groups and their member (child) solution categories, together with visibility attributes. Its purpose is to flatten the hierarchical relationship between category groups and categories so that runtime queries — searching, browsing, and restricting Knowledge Base content — avoid recursive traversal of the category hierarchy. The suffix "DENORM" indicates the rows are derived and maintained from the normalized category structures rather than being entered directly by users.

In Data Vault terms, the FK structure suggests this object is best modeled as a link: it resolves many-to-many relationships between category groups, solution categories, and visibilities, and carries no naturally descriptive business attributes of its own. Treating it as a link (with the referenced hubs as CS_KB_CATEGORY_GROUPS_B, CS_KB_SOLN_CATEGORIES_B, and CS_KB_VISIBILITIES_B) is a reasonable modeling suggestion when mapping EBS tables into a dimensional or Data Vault warehouse, though the table also carries audit columns typical of an EBS transactional satellite.

Key Information Stored

The documented physical schema contains nine columns. The most important are:

• CATEGORY_GROUP_ID — identifies the parent Knowledge Base category group; part of the composite primary key and a foreign key to CS_KB_CATEGORY_GROUPS_B.
• CHILD_CATEGORY_ID — identifies the member solution category belonging to the group; part of the composite primary key and a foreign key to CS_KB_SOLN_CATEGORIES_B.
• VISIBILITY_ID — foreign key to CS_KB_VISIBILITIES_B, controlling which visibility profile governs the group-category pairing.
• VISIBILITY_POSITION — ordinal used to order or rank the category within the group for display and navigation.
• CREATION_DATE, CREATED_BY — audit columns recording when and by whom the denormalized row was created.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS "Who" audit columns capturing the most recent modification.

There is no single-column surrogate key. The primary key is the composite CS_KB_CAR_GROUP_DENORM_PK on (CATEGORY_GROUP_ID, CHILD_CATEGORY_ID). A unique index, CS_KB_CAT_GROUP_DENORM_U1, covers the same two columns and serves as the business-key candidate, confirming that a given category appears at most once per group.

Common Use Cases and Queries

Typical scenarios include rendering the category tree for a Knowledge Base group, validating that a category is reachable within a group before publishing a solution, and filtering accessible content by visibility profile. A common query pattern joins the denormalized table to itself or to the category group to list members:

• Listing categories in a group: SELECT CHILD_CATEGORY_ID, VISIBILITY_ID, VISIBILITY_POSITION FROM CS_KB_CAT_GROUP_DENORM WHERE CATEGORY_GROUP_ID = :group_id ORDER BY VISIBILITY_POSITION.
• Resolving visibility: join VISIBILITY_ID to CS_KB_VISIBILITIES_B to return the visibility name or code for reporting.
• Eligibility checks: SELECT 1 FROM CS_KB_CAT_GROUP_DENORM WHERE CATEGORY_GROUP_ID = :g AND CHILD_CATEGORY_ID = :c to confirm membership.
• Reconciliation: compare denormalized rows against the normalized category hierarchy to detect stale expansions after category maintenance.

Related Objects

The table is tightly coupled to the following Knowledge Base objects through the documented foreign keys:

• CS_KB_CATEGORY_GROUPS_B — referenced via CATEGORY_GROUP_ID; the parent group definition.
• CS_KB_SOLN_CATEGORIES_B — referenced via CHILD_CATEGORY_ID; the member solution category.
• CS_KB_VISIBILITIES_B — referenced via VISIBILITY_ID; the visibility profile applied to the pairing.
• CS_KB_CATEGORIES / CS_KB_CATEGORY_SETS — related Knowledge Base taxonomy tables that supply category context used during denormalization.
• CS_KB_SOLUTIONS / CS_KB_SOLN_CATEGORIES — solution records that are surfaced through category-group membership.

Because rows are derived, regeneration of this table is normally driven by Knowledge Base category-group maintenance routines rather than by direct DML, and integrations should treat it as read-mostly reference data.