Results for “ams_act_access_denorm”

50+ results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The AMS_ACT_ACCESS_DENORM table is a denormalized access-association structure within the Oracle Marketing (AMS) module of Oracle E-Business Suite. It resides in the AMS schema and is documented as VALID in both EBS 12.1.1 and 12.2.2. Its stated purpose — "Access Association Denorm" — indicates that it stores pre-resolved relationships between marketing activities and the resources (users or resource parties) granted access to them, avoiding the need to traverse the full normalized activity-security model at query time. This denormalized design supports high-volume list building, activity search, and permission filtering, where joining the underlying activity, resource, and access tables on every read would be prohibitively expensive.

From a dimensional modeling perspective, the Data Vault classification mined from the foreign-key structure is satellite-leaning. This classification is a modeling suggestion rather than a documented property: the table's grain is defined by an activity object and a resource, with attributes such as access type, metrics-edit permission, and source code describing that combination. In Data Vault terms it behaves like a satellite attached to the activity-resource relationship, carrying descriptive and state attributes rather than serving as an independent business entity.

Key Information Stored

The table comprises twelve documented columns. The most significant are:

The physical schema is owned by AMS and contains twelve columns, with RESOURCE_ID carrying a foreign key to JTF_RS_RESOURCE_EXTNS.

Common Use Cases and Queries

The primary reporting scenario is retrieving the resources entitled to a given marketing activity. Because the table is already denormalized, this is a single-table lookup:

SELECT RESOURCE_ID, ACCESS_TYPE, EDIT_METRICS_YN
FROM   AMS.AMS_ACT_ACCESS_DENORM
WHERE  OBJECT_ID = :activity_id
AND    OBJECT_TYPE = 'ACTIVITY';

Reverse lookups determine which activities a resource may access, typically scoped to a particular permission level:

SELECT OBJECT_ID
FROM   AMS.AMS_ACT_ACCESS_DENORM
WHERE  RESOURCE_ID = :resource_id
AND    ACCESS_TYPE = :access_type;

Administrative reconciliation compares the denormalized rows against the authoritative access model to detect stale or orphaned entries, joining to JTF_RS_RESOURCE_EXTNS to validate that each resource remains active. Reporting extracts commonly join RESOURCE_ID to JTF_RS_RESOURCE_EXTNS for resource names, and filter on EDIT_METRICS_YN or SOURCE_CODE to isolate manually maintained or externally sourced access grants.

Related Objects

The documented foreign key defines the most important dependency: AMS_ACT_ACCESS_DENORM.RESOURCE_ID references JTF_RS_RESOURCE_EXTNS, the resource-extension table that resolves a resource identifier to its descriptive attributes. Beyond this documented relationship, the denormalized rows are derived from the marketing activity security model, so the activity header and access tables in the AMS schema are functionally related sources. The unique indexes AMS_ACT_ACCESS_DENORM_U1 (OBJECT_ID, RESOURCE_ID, OBJECT_TYPE) and AMS_ACT_ACCESS_DENORM_U2 (ACCESS_DENORM_ID), together with the AMS_ACT_ACCESS_DENORM_PK constraint, are the principal access paths and should be preserved in any query or extraction that targets this object.