Results for “mtl_category_set_valid_cats_v”

49 results




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

Overview

MTL_CATEGORY_SET_VALID_CATS_V is an Oracle E-Business Suite Inventory (INV) view owned by the APPS schema. As documented in the ETRM metadata for release 12.1.1 and 12.2.2, its functional description is recorded simply as "10SC ONLY," indicating the view is a restricted, single-purpose construct rather than a general-purpose reporting interface. It presents the set of category-to-category-set assignments held in MTL_CATEGORY_SET_VALID_CATS, enriched with the descriptive category set name and the fully concatenated category segment value. The view is used whenever a form, concurrent program, or integration needs to resolve a valid category identifier into a human-readable category description within the correct category set — for example, in item assignment, sourcing, or list-of-values logic. Because the metadata labels it 10SC ONLY, its status must be treated as VALID but version-sensitive: it should not be assumed to be a stable public interface across all point releases, and ETRM records no further extension or customization guidance for it.

Underlying Base Objects

The view text is a four-way join. The driving object is MTL_CATEGORY_SET_VALID_CATS (recorded as a SYNONYM), which supplies the validation rows linking CATEGORY_SET_ID to CATEGORY_ID and the standard WHO/audit columns. MTL_CATEGORY_SETS_TL (SYNONYM) provides the translated CATEGORY_SET_NAME; the join constrains MCST.LANGUAGE to USERENV('LANG'), so the view returns the category set name in the session's language. MTL_CATEGORIES_B_KFV (VIEW) — the key flexfield "concatenated segments" view over the category key flexfield — provides CATEGORY_CONCAT_SEGMENTS and is joined on CATEGORY_ID. A correlated subquery against MTL_CATEGORY_SETS_B (SYNONYM) resolves the STRUCTURE_ID of the category set and constrains the key flexfield view so that the returned concatenated category belongs to the same flexfield structure as the category set. The ROW_ID column is projected from the rowid of MTL_CATEGORY_SET_VALID_CATS.

Key Columns

  • ROW_ID — rowid of the underlying MTL_CATEGORY_SET_VALID_CATS row; useful for updates or de-duplication.
  • CATEGORY_SET_ID — identifier of the category set (flexfield structure context).
  • CATEGORY_ID — identifier of the category within MTL_CATEGORIES_B_KFV.
  • CATEGORY_SET_NAME — translated set name from MTL_CATEGORY_SETS_TL in the session language.
  • CATEGORY_CONCAT_SEGMENTS — the concatenated key flexfield segment display value for the category; this is the descriptive value users typically want.
  • Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, inherited from the base validation table.

Common Use Cases and Queries

Typical use is to translate a category set / category pair into a display value, or to enumerate valid categories for a given set in the current language. Because the view already resolves the key flexfield concatenation, it removes the need for callers to join MTL_CATEGORIES_B_KFV themselves.

  • Resolving a category for a specific set:

SELECT category_set_name, category_concat_segments FROM mtl_category_set_valid_cats_v WHERE category_set_id = :p_set_id AND category_id = :p_category_id;

  • Listing valid categories for a set:

SELECT category_id, category_concat_segments FROM mtl_category_set_valid_cats_v WHERE category_set_id = :p_set_id ORDER BY category_concat_segments;

  • Confirming a category set assignment as an existence check before insert into item-category relationships.

Queries should filter on CATEGORY_SET_ID and CATEGORY_ID to leverage the underlying joins, and note that CATEGORY_SET_NAME reflects the session language via USERENV('LANG').