Search Results gr_item_report_v1




Overview
Explore in AI Chat

Overview

GR_ITEM_REPORT_V1 is a reporting view owned by the APPS schema within the Oracle E-Business Suite Process Manufacturing Regulatory Management (GR) module. Its documented purpose is to present a consolidated list of valid regulatory items that can be consumed by reports. Rather than exposing the full breadth of item attributes held in the underlying Process Manufacturing and Inventory tables, the view deliberately narrows its projection to two columns: a unique item identifier (ITEM_NO) and a human-readable description (ITEM_DESC1). This makes it a lightweight, report-friendly source of truth for item lookups across the GR schema.

The view is marked with a status of VALID, indicating that it compiles successfully against the referenced objects in the current release. It is documented for both Oracle EBS 12.1.1 and 12.2.2, and behaves consistently across those releases because it relies on standard GR and Inventory synonyms rather than release-specific internal APIs. The view is particularly relevant to users who search for the column "item_desc1," since that is one of only two columns the view returns and is the description value surfaced for each regulatory item.

Underlying Base Objects

The view is defined over three documented base objects, all referenced through APPS synonyms:

  • IC_ITEM_MST — the Process Manufacturing item master. The first branch of the view reads ITEM_NO and ITEM_DESC1 directly from this table.
  • GR_ITEM_GENERAL — the Regulatory Management item general table, supplying the ITEM_CODE used in the second branch.
  • GR_MULTILINGUAL_NAME_TL — the translated/description table, joined by ITEM_CODE and LABEL_CODE (fixed at value '11007'), filtered to the session language via USERENV('LANG').

The structure is a UNION ALL of two distinct populations. The first populates the view from IC_ITEM_MST, the Process Manufacturing master item table. The second adds any regulatory items present in GR_ITEM_GENERAL that do not already exist in IC_ITEM_MST, using an outer join to GR_MULTILINGUAL_NAME_TL to resolve the language-appropriate description. The NOT IN subquery against IC_ITEM_MST prevents duplicate rows, and the entire result is ordered by the first column (ITEM_NO). This design ensures that all valid items from both the core item master and the regulatory item tables appear exactly once.

Key Columns

  • ITEM_NO — the unique item identifier. In the first branch it is sourced from IC_ITEM_MST.ITEM_NO; in the second it is the GR_ITEM_GENERAL.ITEM_CODE aliased to ITEM_NO. This is the primary joining and sorting key of the view and the column returned first in the ORDER BY clause.
  • ITEM_DESC1 — the primary description of the item. For items originating in IC_ITEM_MST this is the master description column. For regulatory-only items it is drawn from GR_MULTILINGUAL_NAME_TL.NAME_DESCRIPTION, resolved for the user's language and label code 11007. Because of the outer join, ITEM_DESC1 may be null for a regulatory item that has no matching multilingual row for the current session language.

Common Use Cases and Queries

This view is most commonly used as a simple, language-aware item lookup for regulatory reports, particularly those that need a description without pulling the full item master. A typical query retrieves all items and their descriptions:

  • SELECT ITEM_NO, ITEM_DESC1 FROM APPS.GR_ITEM_REPORT_V1 ORDER BY ITEM_NO;
  • Filtering by description text: SELECT ITEM_NO, ITEM_DESC1 FROM APPS.GR_ITEM_REPORT_V1 WHERE UPPER(ITEM_DESC1) LIKE UPPER('%&keyword%');
  • Direct item lookup: SELECT ITEM_DESC1 FROM APPS.GR_ITEM_REPORT_V1 WHERE ITEM_NO = :item_no;
  • Feeding a report or LOV: SELECT ITEM_NO, ITEM_DESC1 FROM APPS.GR_ITEM_REPORT_V1 WHERE ITEM_NO IN (SELECT ITEM_CODE FROM GR_ITEM_GENERAL);

Because it returns descriptions resolved to USERENV('LANG'), the view is well suited to multilingual reporting environments where the description should follow the user's session language. It is not intended for attribute-rich queries; for those, join back to IC_ITEM_MST, GR_ITEM_GENERAL, or the multilingual name table directly.