Search Results amv_u_access_n1

Overview

AMV.AMV_U_ACCESS is a transactional access-control table in the Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 AMV schema. It records which users or groups have access to a given resource — a channel, an application, or an item — and what kind of access they are permitted. The table is owned by the AMV schema, resides in the APPS_TS_TX_DATA tablespace (PCT Free 10), and holds a status of VALID in the ETRM 12.2.2 documented schema. Its twenty-two columns span the standard Who audit columns, an access-target discriminator, a user/group discriminator, and a set of granular flag columns governing view, create, delete, update, distribution-rule creation, and channel-related permissions.

From a heuristic Data Vault classification (mined from the foreign-key structure), this object is best modelled as a link. It associates two principal business entities — a resource identified by ACCESS_TO_TABLE_CODE and ACCESS_TO_TABLE_RECORD_ID, and an actor identified by USER_OR_GROUP_TYPE and USER_OR_GROUP_ID — while carrying descriptive attributes (the CAN_* flags, effective and expiration dates) that characterize the relationship. This link classification is a modelling suggestion; the physical table is a conventional EBS table with two unique indexes and one non-unique index.

Key Information Stored

The surrogate primary key is ACCESS_ID, defined by the unique index AMV_U_ACCESS_U1. The business-key candidate is the composite captured by AMV_U_ACCESS_U2, comprising ACCESS_TO_TABLE_CODE, ACCESS_TO_TABLE_RECORD_ID, USER_OR_GROUP_TYPE, and USER_OR_GROUP_ID. The most significant columns are:

• ACCESS_ID — numeric surrogate identifier and primary key.
• ACCESS_TO_TABLE_CODE — discriminator naming the target table (channel, application, item, or category) to which access is granted.
• ACCESS_TO_TABLE_RECORD_ID — identifier of the specific record in the target table.
• USER_OR_GROUP_ID — identifier of the user or group receiving access.
• USER_OR_GROUP_TYPE — indicates whether the grantee is a user or a group.
• EFFECTIVE_START_DATE and EXPIRATION_DATE — the validity window for the access grant.
• CAN_VIEW_FLAG, CAN_CREATE_FLAG, CAN_DELETE_FLAG, CAN_UPDATE_FLAG — four CRUD permission flags.
• CAN_CREATE_DIST_RULE_FLAG — permission to create a distribution rule on the target record.
• CHL_APPROVER_FLAG, CHL_REQUIRED_FLAG, CHL_REQUIRED_NEED_NOTIF_FLAG — channel approver and notification controls.
• OBJECT_VERSION_NUMBER — locking column used for optimistic concurrency.
• SECURITY_GROUP_ID — reference to the FND security group.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard Who audit columns.

Common Use Cases and Queries

Typical scenarios include determining whether a user may view or modify a channel, application, item, or category; auditing who currently holds a permission on a record; and reporting on grants that are about to expire. The non-unique index AMV_U_ACCESS_N1 (USER_OR_GROUP_ID, USER_OR_GROUP_TYPE) supports lookups by grantee.

• Resolve effective permissions for a user on a channel: SELECT can_view_flag, can_update_flag FROM amv_u_access WHERE access_to_table_code = :code AND access_to_table_record_id = :id AND user_or_group_id = :uid AND user_or_group_type = :type AND SYSDATE BETWEEN NVL(effective_start_date, SYSDATE) AND NVL(expiration_date, SYSDATE+1);
• List all grantees for an item’s record ID, joining to the item master.
• Report access grants expiring within a date range for quarterly security reviews.
• Audit channel-approver designations using CHL_APPROVER_FLAG.

Related Objects

The foreign-key metadata identifies the following significant dependencies, all joined on the columns shown:

• JTF_RS_RESOURCE_EXTNS — joined via USER_OR_GROUP_ID to resolve a user grantee.
• JTF_RS_GROUPS_B — joined via USER_OR_GROUP_ID to resolve a group grantee.
• AMV_C_CHANNELS_B — joined via ACCESS_TO_TABLE_RECORD_ID when the target is a channel.
• FND_APPLICATION — joined via ACCESS_TO_TABLE_RECORD_ID when the target is an application.
• JTF_AMV_ITEMS_B — joined via ACCESS_TO_TABLE_RECORD_ID when the target is an item.
• AMV_C_CATEGORIES_B — joined via ACCESS_TO_TABLE_RECORD_ID when the target is a category.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID.

Because ACCESS_TO_TABLE_CODE selects among four target tables, queries against AMV_U_ACCESS must filter on that column before joining to the corresponding master table.