Search Results ams_act_access_denorm




Overview
Explore in AI Chat

Overview

The AMS_ACT_ACCESS_DENORM table is a core data object within the Oracle E-Business Suite Marketing (AMS) module. Functioning as a denormalized access association table, its primary role is to manage and store authorization data that governs user access to various marketing objects. In the context of Oracle EBS 12.1.1 and 12.2.2, this table serves as a critical component of the security and data access layer, enabling the application to efficiently determine which resources (users or roles) have permission to interact with specific marketing entities such as campaigns, activities, or lists. Its denormalized structure is designed for optimized performance during access validation checks, reducing the need for complex, multi-table joins at runtime.

Key Information Stored

The table's structure centers on mapping three key identifiers to create a unique access rule. The primary identifier is the ACCESS_DENORM_ID, a system-generated unique key. The core functional columns define the access association: the OBJECT_TYPE and OBJECT_ID pair identifies the specific marketing object (e.g., a campaign), while the RESOURCE_ID column identifies the user or resource granted access, typically linking to the JTF_RS_RESOURCE_EXTNS table. The presence of a unique key constraint (AMS_ACT_ACCESS_DENORM_UK) on these three columns ensures that duplicate access entries cannot exist, maintaining data integrity for the security model.

Common Use Cases and Queries

A primary use case is the real-time validation of a user's authorization to view or modify a marketing object. For instance, when a user attempts to open a campaign, the application queries this table to confirm an association exists between their resource ID and the campaign's object ID. Common reporting use cases involve auditing access permissions or generating user-specific dashboards. A typical query pattern would join this table to resource and object detail tables:

  • Find all campaigns a user can access: SELECT DISTINCT ac.campaign_name FROM ams_campaigns_all ac, ams_act_access_denorm aad WHERE aad.object_type = 'CAMP' AND aad.object_id = ac.campaign_id AND aad.resource_id = :p_resource_id;
  • List all users with access to a specific object: SELECT jre.resource_name FROM jtf_rs_resource_extns jre, ams_act_access_denorm aad WHERE aad.resource_id = jre.resource_id AND aad.object_id = :p_object_id AND aad.object_type = :p_object_type;

Related Objects

The table maintains defined foreign key relationships that anchor it within the EBS data model. The most critical documented relationship is with the JTF_RS_RESOURCE_EXTNS table via the RESOURCE_ID column. This join provides the human-readable details (name, email, etc.) for the resource granted access. While the ETRM excerpt does not list foreign keys for the OBJECT_TYPE and OBJECT_ID columns, these would logically reference various AMS base tables (like AMS_CAMPAIGNS_ALL or AMS_ACTIVITIES_ALL) depending on the object type value, forming the basis of the marketing security framework.