Search Results access_to_table_code

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:

• ACCESS_TO_TABLE_CODE and ACCESS_TO_TABLE_RECORD_ID — the polymorphic pointer that identifies the target object and the table it resides in; the code determines which of the four possible foreign parents is joined.
• USER_OR_GROUP_ID and USER_OR_GROUP_TYPE — the grantee, discriminated between a resource (JTF_RS_RESOURCE_EXTNS) and a group (JTF_RS_GROUPS_B).
• CAN_VIEW_FLAG, CAN_CREATE_FLAG, CAN_UPDATE_FLAG, CAN_DELETE_FLAG — the CRUD privilege set that defines the access kind.
• CHL_APPROVER_FLAG, CHL_REQUIRED_FLAG, CHL_REQUIRED_NEED_NOTIF_FLAG, CAN_CREATE_DIST_RULE_FLAG — channel-specific workflow controls for approvers, required access, notification, and distribution-rule creation.
• EFFECTIVE_START_DATE and EXPIRATION_DATE — validity window for date-effective security resolution.
• OBJECT_VERSION_NUMBER, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.
• SECURITY_GROUP_ID — the FND_SECURITY_GROUPS reference enforcing multi-org / data-security partitioning.

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.