Search Results gr_all_items_v




Overview
Explore in AI Chat

Overview

GR_ALL_ITEMS_V is a reporting and integration view owned by the APPS schema within the Process Manufacturing Regulatory Management (GR) module of Oracle E-Business Suite. Its stated purpose is narrow and precise: it exposes item code and description pairs. In EBS releases 12.1.1 and 12.2.2, the view carries the status VALID and is registered as a documented object in the ETRM repository, which means it is supported for customer and partner reference rather than being an internal implementation artifact.

The view serves as a consolidated picklist source for regulatory workflows. Regulatory Management requires that certain compliance records be associated with items that have not yet been enrolled in the regulatory item register held in GR_ITEM_GENERAL. GR_ALL_ITEMS_V synthesizes that candidate set by combining enabled inventory items from a designated default warehouse with multilingual regulatory name entries. Because the union produces a stable two-column projection, the view is appropriate for LOV definitions, concurrent program parameters, and ad hoc reporting joins where only the item identifier and its descriptive text are required.

Underlying Base Objects

The documented base objects referenced by the view definition are:

  • MTL_SYSTEM_ITEMS (SYNONYM) — the master item table supplying SEGMENT1 and DESCRIPTION.
  • HR_ALL_ORGANIZATION_UNITS (SYNONYM) — the organization definition table used to resolve which items belong to the organization named by the profile option GR_WHSE_DEFAULT.
  • GR_ITEM_GENERAL (SYNONYM) — the regulatory item register; its ITEM_CODE column is used in a NOT IN subquery to filter out items already registered.
  • GR_MULTILINGUAL_NAME_TL (SYNONYM) — the translated name table that contributes regulatory-specific descriptions filtered by LABEL_CODE '11007' and the session language.
  • FND_PROFILE (PACKAGE) — invoked as FND_PROFILE.VALUE('GR_WHSE_DEFAULT') to obtain the default warehouse organization name at runtime.

The view is therefore not a simple projection over one table. It is a union of two distinct sources: the first arm filters MTL_SYSTEM_ITEMS by ENABLED_FLAG = 'Y' and by organization membership, while the second arm draws translated regulatory names for label code 11007. The left-to-right join between items and organization units on ORGANIZATION_ID anchors the first arm to a single warehouse.

Key Columns

  • ITEM_CODE — the item identifier. In the first arm it is MTL_SYSTEM_ITEMS.SEGMENT1; in the second arm it is GR_MULTILINGUAL_NAME_TL.ITEM_CODE. It is the primary join key for downstream references.
  • DESCRIPTION — the human-readable item description. The first arm uses MTL_SYSTEM_ITEMS.DESCRIPTION; the second arm uses GR_MULTILINGUAL_NAME_TL.NAME_DESCRIPTION, resolved for LANGUAGE = USERENV('LANG').

The result set is ordered by column 1 (ITEM_CODE), which gives deterministic presentation order in LOV windows and report output. No other columns are exposed, and the view carries no organization qualifier column, since the first arm is already constrained to the GR_WHSE_DEFAULT organization and the second arm is language-constrained rather than organization-constrained.

Common Use Cases and Queries

Typical scenarios include populating an item LOV on a regulatory data-entry form, driving a concurrent program parameter of type "Item," and producing exception reports that list enabled items not yet registered for compliance.

SELECT item_code, description
FROM   apps.gr_all_items_v
ORDER BY item_code;

To find candidate items pending regulatory enrollment for the default warehouse:

SELECT g.item_code, g.description
FROM   apps.gr_all_items_v g
WHERE  g.item_code LIKE :p_item_prefix
AND    NOT EXISTS (SELECT 1
                   FROM   apps.gr_item_general r
                   WHERE  r.item_code = g.item_code);

Because the view already embeds a NOT IN filter against GR_ITEM_GENERAL and depends on the GR_WHSE_DEFAULT profile, callers should not assume it returns the complete master item list. Where the default warehouse profile is unset or points to a non-existent organization, the first union arm returns no rows and only the multilingual arm contributes. Reports requiring all organizations should query MTL_SYSTEM_ITEMS directly rather than this view.