Results for “end_effective_date”

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

Overview

The APPS.INVBV_ITEM_CATEGORIES view is an Oracle E-Business Suite database object owned by the APPS schema and classified under the INV — Inventory product family. In the ETRM metadata for 12.1.1 and 12.2.2 it carries the status VALID and is annotated as "Retrofitted," indicating that the object was carried forward from an earlier code line (notably the 11i family) into the current release without functional redesign. Such retrofitted views are typically preserved to maintain backward compatibility for customizations, reports, and interfaces that reference the original object name.

Conceptually, INVBV_ITEM_CATEGORIES presents item category definitions — the classification codes used to group inventory items into categories and category sets. Because the view is defined with a WITH READ ONLY clause, it functions strictly as a query surface: no DML is permitted against it, and all maintenance of category data must occur through the base table or through supported concurrent programs and forms. This makes the view appropriate for reporting, integration extracts, and validation lookups, not for data loading.

Underlying Base Objects

The view is defined over a single documented base object: MTL_CATEGORIES, referenced in the defining query through the synonym MTL_CATEGORIES (aliased as CA). No joins to MTL_CATEGORY_SETS, MTL_ITEM_CATEGORIES, or MTL_CATEGORY_ASSIGNMENTS appear in the documented view text, so the view exposes category-level attributes only, not the item-to-category assignments themselves.

MTL_CATEGORIES stores the category entities belonging to category sets (each category row references a structure/category set via STRUCTURE_ID). The view performs a straightforward projection of selected columns from this table, together with two constant expressions: a literal flexfield context token '_KF:INV:MCAT:CA' and a derived column aliased as CATEGORY_NAME. Because it is a simple, non-joining view over a synonym, it inherits the base table's partitioning, indexing, and security characteristics, and Query of the view is subject to the same underlying table privileges.

Key Columns

• "_KF:CATEGORY_NAME" — A flexfield-style key column that carries the literal context '_KF:INV:MCAT:CA'. It serves as the descriptive identifier hook for the category in Key Flexfield (KFF) aware contexts and is typically reported alongside DESCRIPTION.
• DESCRIPTION — The user-entered description of the category, sourced from MTL_CATEGORIES.DESCRIPTION.
• END_EFFECTIVE_DATE — The disable date of the category (mapped to DISABLE_DATE in the base table), indicating when the category ceases to be selectable for new assignments.
• CATEGORY_ID — The surrogate primary key of the category; the join key to MTL_ITEM_CATEGORIES and other child tables.
• STRUCTURE_ID — Identifies the category set (structure) to which the category belongs.
• LAST_UPDATED_DATE / LAST_UPDATED_BY — Audit columns reflecting the last modification timestamp and the user or process that performed it.
• CREATED_DATE / CREATED_BY — Audit columns recording initial row creation.

Note that the column list in the view definition uses APPLICATION-style naming (LAST_UPDATE_DATE, CREATED_BY) while the projected labels shown in the ETRM column block render as LAST_UPDATED_DATE and CREATED_DATE. Consumers should verify actual column names against the deployed view before use in code.

Common Use Cases and Queries

The view is most often used to enumerate categories for a given category set, to resolve CATEGORY_ID values for integration payloads, and to identify disabled categories by comparing END_EFFECTIVE_DATE to the current date.

List all categories within a specific category set:

SELECT category_id, description, structure_id
FROM   apps.invbv_item_categories
WHERE  structure_id = :p_structure_id;

Retrieve category descriptions for reporting by joining on CATEGORY_ID:

SELECT c.category_id, c.description, mic.inventory_item_id
FROM   apps.invbv_item_categories c,
       apps.mtl_item_categories mic
WHERE  c.category_id = mic.category_id
AND    mic.organization_id = :p_org_id;

Identify currently active categories:

SELECT category_id, description
FROM   apps.invbv_item_categories
WHERE  end_effective_date IS NULL
OR     end_effective_date > SYSDATE;

Because the object is read-only and retrofitted, organizations migrating from 11i should confirm that custom reports referencing INVBV_ITEM_CATEGORIES continue to resolve in 12.2.2, where the preferred equivalent is generally a direct query against MTL_CATEGORIES with the appropriate category set context.