Search Results ams_access_denorm_pvt




Overview
Explore in AI Chat

Overview

AMS_ACCESS_DENORM_PVT is a private PL/SQL package body in the APPS schema that supports Oracle Marketing (AMS) access control and denormalization processing. Its role is to maintain the denormalized access tables that record which resources, user groups, and organizational entities are permitted to view or act upon marketing objects such as campaigns, events, and activities. Rather than resolving access rights through runtime joins against the base security and group membership tables, Oracle EBS pre-computes and stores these relationships in dedicated denormalized tables. AMS_ACCESS_DENORM_PVT provides the procedural logic that inserts, updates, and deletes those denormalized records so that query performance against marketing objects remains predictable as the volume of users, groups, and objects grows. As a PVT package, it is an internal implementation layer invoked by the public API AMS_ACCESS_PVT and by internal denormalization routines; it is not intended to be called directly by customer code. The package is validated in both Oracle EBS 12.1.1 and 12.2.2 and is documented in ETRM with 15 procedures and functions.

Key Procedures and Functions

The documented program units fall into three functional groupings:

  • Resource maintenance — INSERT_RESOURCE, UPDATE_RESOURCE, and DELETE_RESOURCE manage the denormalized access rows associated with individual resources (such as a user or resource identified through JTF_RS_GROUPS / JTF_RS_GROUP_MEMBERS). INSERT_RESOURCE creates the access rows when a resource is granted access; UPDATE_RESOURCE refreshes them when the underlying grant changes; DELETE_RESOURCE removes them when access is revoked.
  • Group maintenance — INSERT_GROUP, UPDATE_GROUP, and DELETE_GROUP perform the equivalent lifecycle for user groups, keeping the denormalized group access entries synchronized with changes to group membership.
  • Denormalization drivers — AMS_OBJECT_DENORM, AMS_ACCESS_DENORM, and JTF_ACCESS_DENORM coordinate the broader denormalization process. AMS_OBJECT_DENORM operates at the level of a marketing object, AMS_ACCESS_DENORM resolves the access entries for an object, and JTF_ACCESS_DENORM extends the logic to the common JTF resource/group model used across EBS applications.

The package also declares private helper logic (three additional documented units) that supports these public-facing procedures; consistent with ETRM convention, parameter signatures are not reproduced here.

Tables Accessed

The package references the following tables through APPS synonyms:

  • AMS_ACT_ACCESS — the base (non-denormalized) activity access definition; read as the source of truth for grants.
  • AMS_ACT_ACCESS_DENORM / AMS_ACT_ACCESS_DENORM_S — the denormalized access table and its sequence, written by the insert/update/delete procedures.
  • JTF_RS_GROUPS_B, JTF_RS_GROUP_MEMBERS — resource group definitions and membership, used to resolve which resources belong to a group.
  • JTF_RS_GROUPS_DENORM — the denormalized group access table maintained by the group procedures.
  • FND_CONCURRENT_PROGRAMS / FND_CONCURRENT_REQUESTS — concurrent program and request metadata, supporting submission and status checks for denormalization runs.
  • DUAL and PLITBLM — utility references used internally for PL/SQL table (index-by table) operations and single-row selection.

Package dependencies include AMS_ACCESS_PVT, AMS_UTILITY_PVT, FND_API, FND_GLOBAL, and FND_MSG_PUB, confirming reliance on the standard EBS API error-handling, message, and environment context utilities.

Usage Notes

AMS_ACCESS_DENORM_PVT is invoked indirectly. Marketing forms and the public AMS_ACCESS_PVT API call it when access grants are created, modified, or removed, and concurrent programs that rebuild denormalized access data use it to refresh the affected tables in bulk. Because it is designated PVT, direct invocation from custom code is not supported; any customization should call the public AMS_ACCESS_PVT API instead, ensuring that FND_MSG_PUB error handling and FND_GLOBAL context are applied correctly. Administrators troubleshooting stale marketing access should confirm that the associated denormalization concurrent request completed successfully, since incomplete or failed runs leave AMS_ACT_ACCESS_DENORM and JTF_RS_GROUPS_DENORM out of step with the base access tables.