Search Results amv_u_access




Overview
Explore in AI Chat

Overview

AMV_U_ACCESS is a table in the AMV schema belonging to the Marketing Encyclopedia System (AMS) product family in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It functions as the central access-control repository that records which users or groups are granted access to specific marketing encyclopedia entities — channels, applications, and items — and the precise nature of that access through a set of capability flags. Because it is a pure relationship table with no descriptive attributes of its own, a Data Vault modeling heuristic classifies AMV_U_ACCESS as a link table: it connects subjects (users/groups) to securable objects (channels, applications, items) and carries the access semantics as link attributes.

Key Information Stored

The table is keyed on the surrogate primary key ACCESS_ID (AMV_U_ACCESS_PK / AMV_U_ACCESS_U1). A second unique index, AMV_U_ACCESS_U2, covers the business-key candidate combination ACCESS_TO_TABLE_CODE, ACCESS_TO_TABLE_RECORD_ID, USER_OR_GROUP_TYPE, and USER_OR_GROUP_ID, which together guarantee that a single grant is not duplicated for the same subject-object pair. The most significant columns are:

Common Use Cases and Queries

The most common requirement is determining who can see a given item, channel, or application, and the inverse — what a user can access. A typical query joins the polymorphic pointer to JTF_AMV_ITEMS_B for item-level reporting:

  • SELECT a.USER_OR_GROUP_ID, a.USER_OR_GROUP_TYPE, a.CAN_VIEW_FLAG, a.CAN_UPDATE_FLAG FROM AMV.AMV_U_ACCESS a WHERE a.ACCESS_TO_TABLE_CODE = :code AND a.ACCESS_TO_TABLE_RECORD_ID = :item_id;
  • Date-effective checks add SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EXPIRATION_DATE, SYSDATE+1).
  • Security audit reports list expired or over-broad grants by filtering on EXPIRATION_DATE and CAN_DELETE_FLAG.
  • Channel administration uses the CHL_* flags to reproduce the approver matrix for publishing workflows.

Related Objects

  • JTF_AMV_ITEMS_B — items secured via ACCESS_TO_TABLE_RECORD_ID.
  • AMV_C_CHANNELS_B — channels secured via ACCESS_TO_TABLE_RECORD_ID.
  • AMV_C_CATEGORIES_B — categories secured via the same column.
  • FND_APPLICATION — applications secured via ACCESS_TO_TABLE_RECORD_ID.
  • JTF_RS_RESOURCE_EXTNS and JTF_RS_GROUPS_B — the user/group parents of USER_OR_GROUP_ID.
  • FND_SECURITY_GROUPS — parent of SECURITY_GROUP_ID for data partitioning.