Search Results purch_class




Overview
Explore in AI Chat

Overview

IC_PRCH_CLS_VW is a reporting view owned by the APPS schema in Oracle E-Business Suite, exposed through the ETRM (E-Business Suite Technical Reference Manual) for releases 12.1.1 and 12.2.2. The view provides a normalized, unified presentation of Oracle Process Manufacturing (OPM) purchasing classification codes, commonly searched under the term "purch_class." Its central purpose is to reconcile two historically distinct category models: the discrete manufacturing category structures held in Oracle Inventory (MTL) and the OPM class/category-set model held in the Process Manufacturing tables (GMI).

The view resolves the OPM class designated by the value PURCH_CLASS against the matching MTL category set and category structure, returning the concatenated category segment string, the surrogate category identifier, descriptive text, audit columns, and the standard descriptive flexfield (DFF) attribute columns. Because it presents a consistent column layout regardless of which underlying path supplied a row, IC_PRCH_CLS_VW is suited to reporting layers, interfaces, and integration programs that must read purchasing classification values without embedding the OPM-to-MTL mapping logic themselves.

As a view rather than a table, it carries no storage of its own; all values are derived at query time from the referenced base objects. This has performance implications for large category sets, since the segment matching involves string functions and a UNION operation.

Underlying Base Objects

The documented base objects referenced by the view are:

  • GMI_CATEGORY_SETS (synonym) — supplies the OPM category-set definition and, critically, the OPM_CLASS value that is filtered to 'PURCH_CLASS'.
  • MTL_CATEGORY_SETS (synonym) — supplies the MTL category-set record that links to a structure and carries the VALIDATE_FLAG used in the first branch of the UNION.
  • MTL_CATEGORIES_V (view) — supplies the category row itself: concatenated segments, description, category identifier, audit columns, disable date, DFF attributes, and attribute category.
  • MTL_CATEGORY_SET_VALID_CATS (synonym) — the category-set-to-category assignment table, joined in the second branch of the UNION to restrict results to categories actually valid for the category set.

The view is defined as a UNION of two SELECT statements. The first branch joins MTL_CATEGORIES_V, MTL_CATEGORY_SETS, and GMI_CATEGORY_SETS, and applies the predicate cs.validate_flag <> 'Y' together with an upper-case match on the first eight characters of the concatenated segments. The second branch adds MTL_CATEGORY_SET_VALID_CATS, filtering through gmi_category_sets.opm_class = 'PURCH_CLASS' and the category set valid-cats assignment. Both branches project an identical column list, allowing the UNION to return a single coherent result set.

Key Columns

  • Category concatenated segments — the first eight characters of CATEGORY_CONCAT_SEGS, exposed via SUBSTRB. This is the human-readable purchasing class code as displayed to users.
  • CATEGORY_ID — the surrogate primary key of the MTL category, used as the foreign-key target in downstream integrations.
  • Description — the category description truncated to 70 characters via SUBSTRB(c.DESCRIPTION,1,70).
  • CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard EBS audit columns, passed through unchanged for both branches.
  • Disable flag — a derived numeric column produced by DECODE(c.DISABLE_DATE, NULL, 0, 1), where 0 indicates an active class and 1 indicates a disabled one. Note the DISABLE_DATE itself is not exposed.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield context and segment values carried from the MTL category, enabling the view to surface OPM-specific classification attributes.
  • Literal placeholder columns — several columns are populated with literal zero, TO_NUMBER(NULL), or TO_CHAR(NULL). These exist solely to align the column lists of the two UNION branches and carry no functional data.

Common Use Cases and Queries

The primary use case is validating that a purchasing classification code entered by a user or interface exists and is active before an OPM purchase order, receipt, or lot is created. A typical validation query retrieves the class identifier and description for a given code:

  • SELECT category_id, description FROM apps.ic_prch_cls_vw WHERE category_concat_segs = UPPER(:p_code) AND disable_flag = 0;

A second scenario is populating a list of values (LOV) or reporting parameter set with all active purchasing classes:

  • SELECT category_concat_segs, description FROM apps.ic_prch_cls_vw WHERE disable_flag = 0 ORDER BY 1;

A third is auditing, where an integrator extracts the full purchasing classification population together with audit and flexfield data for reconciliation against the OPM class tables:

  • SELECT category_concat_segs, category_id, description, creation_date, last_update_date, attribute_category, attribute1 FROM apps.ic_prch_cls_vw;

Because the view performs SUBSTRB, UPPER, and UNION processing, queries should be filtered on the leading segment columns where possible. When tuning, the caller should confirm that the underlying MTL and GMI category-set indexes are present on the joined keys (CATEGORY_SET_ID and STRUCTURE_ID), and that the concatenated segment comparison benefits from the appropriate index on the MTL category view's base table.