Search Results mtl_object_grants_v




Overview
Explore in AI Chat

Overview

MTL_OBJECT_GRANTS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered under the INV (Inventory) product module. It is a denormalized, join-based view that presents user and group grants assigned against Inventory-related objects, resolved through menu-based roles defined in Oracle Applications security infrastructure. Rather than storing data itself, the view surfaces a consolidated perspective of the FND_GRANTS authorization model by linking menu definitions, trading community party records, and object definitions into a single readable result set.

The view plays a meaningful role in reporting and integration scenarios where implementers need to audit which parties (users or groups) currently hold grants to Inventory objects, when those grants begin and expire, and which OWF-style menu role governs them. It becomes particularly relevant when tracing the unique grant identifier—GRANT_GUID—which is a column the view explicitly exposes, making it searchable in the context of the user's query for "grant_guid".

Underlying Base Objects

The view's SQL text defines an inner join across four documented base objects:

  • FND_GRANTS (SYNONYM) — The core authorization grants table, supplying grantee type, grantee key, grant GUID, instance PK values, menu ID, and audit columns (creation and update metadata).
  • FND_MENUS (SYNONYM) — Provides the menu name and menu ID that function as the role definition in the result set.
  • HZ_PARTIES (SYNONYM) — The trading community party master, supplying PARTY_NAME and PARTY_ID for the grantee.
  • FND_OBJECTS_VL (VIEW) — A translated object definition view that supplies OBJ_NAME as OBJECT_NAME, resolving the OBJECT_ID to a human-readable label.

The join predicate filters grants to INSTANCE_TYPE = 'INSTANCE' and constrains the grantee key to either 'HZ_PARTY:' || PARTY_ID (for grantee type USER) or 'HZ_GROUP:' || PARTY_ID (for grantee type GROUP). Only rows where the grant's OBJECT_ID matches a defined object in FND_OBJECTS_VL are returned, so orphaned grants without a resolvable object are excluded.

Key Columns

  • ROLE_NAME / ROLE_ID — The menu name and menu identifier, representing the role that scopes the grant.
  • PARTY_NAME / PARTY_ID — The granted party (user or group) resolved from HZ_PARTIES.
  • START_DATE / END_DATE — Effective window of the grant; null or future END_DATE typically indicates an open-ended grant.
  • INSTANCE_PK1_VALUE / INSTANCE_PK2_VALUE — Instance-level key values that identify the specific object instance the grant applies to.
  • GRANTEE_TYPE / GRANTEE_KEY — Indicates whether the grantee is a USER or GROUP and carries the encoded HZ reference key.
  • GRANT_GUID — The globally unique identifier for the grant record; used for external referencing and cross-system reconciliation.
  • OBJECT_ID / OBJECT_NAME — The Inventory object identifier and its resolved display name from FND_OBJECTS_VL.
  • Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN for change tracking.

Common Use Cases and Queries

Typical uses include auditing active Inventory object grants for a given party, retrieving the grant identified by a specific GRANT_GUID, and producing role-to-party access reports. The following examples reflect the view's documented column set.

  • Look up a grant by its unique identifier: 
    SELECT role_name, party_name, object_name, start_date, end_date
FROM   apps.mtl_object_grants_v
WHERE  grant_guid = :p_grant_guid;
  • List all active grants for a named party: 
    SELECT role_name, object_name, grantee_type, start_date, end_date
FROM   apps.mtl_object_grants_v
WHERE  party_name = :p_party_name
AND   (end_date IS NULL OR end_date > SYSDATE);
  • Report grants by role and object type: 
    SELECT role_name, object_name, COUNT(*) grant_count
FROM   apps.mtl_object_grants_v
GROUP  BY role_name, object_name
ORDER  BY grant_count DESC;

Because the view draws on security and party master tables, queries should be run with appropriate APPS-level privileges. Note that the join restricts results to grants whose grantee key matches a valid HZ party, so grants referencing inactive or unmatched parties will not appear.