Results for “edit_metrics_yn”
24 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:
- ACCESS_DENORM_ID — the surrogate primary key, enforced by the unique index AMS_ACT_ACCESS_DENORM_U2 and by the constraint AMS_ACT_ACCESS_DENORM_PK.
- OBJECT_ID, OBJECT_TYPE, and RESOURCE_ID — the business-key triplet, enforced by the unique index AMS_ACT_ACCESS_DENORM_U1. OBJECT_ID identifies the secured object (typically a marketing activity), OBJECT_TYPE qualifies the object domain, and RESOURCE_ID identifies the resource holding access.
- ACCESS_TYPE — the nature or level of access granted to the resource for the object, distinguishing such states as full or limited visibility.
- EDIT_METRICS_YN — a flag indicating whether the associated resource may edit the activity's metrics, providing a finer permission granularity than access type alone.
- SOURCE_CODE — identifies the origin or provenance of the denormalized row, useful for attribution and reconciliation against the normalized source.
- CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard EBS WHO-column audit set, capturing row creation and last modification context.
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.
-
Access Association Denorm
-
Access Association Denorm
-
eTRM - AMS Tables and Views 12.2.2
This table is used to store tracking data for web advertisement and offer type schedules
-
eTRM - AMS Tables and Views 12.1.1
This table is used to store tracking data for web advertisement and offer type schedules