Search Results jtf_access_denorm

Overview

APPS.AMS_ACCESS_DENORM_PVT is a private PL/SQL package within Oracle Advanced Marketing (AMS) that maintains the access denormalization tables supporting Marketing resource security. Its purpose is to keep the denormalized access tables — principally AMS_ACT_ACCESS_DENORM and JTF_RS_GROUPS_DENORM — synchronized with the normalized access and group definitions held in AMS_ACT_ACCESS, JTF_RS_GROUPS_B, and JTF_RS_GROUP_MEMBERS. Denormalization is used in EBS Marketing to avoid expensive runtime joins when resolving which resources a user may view or edit; this package encapsulates the logic that flattens those normalized relationships into the denormalized form.

The package is classified as a PVT (private) API, meaning it is not intended as a public integration surface. It declares AUTHID CURRENT_USER, so it executes with the privileges of the calling session rather than the package owner. Its header identifies it as amsvdens.pls, with the source revision dating to early releases of the 11i marketing code line that carried forward into 12.1.1 and 12.2.2.

Key Procedures and Functions

The ETRM 12.2.2 metadata documents fifteen procedures and functions in total. The most significant, and the one most commonly searched, is:

• INSERT_RESOURCE — Inserts a resource (with its associated object type and object ID) into AMS_ACT_ACCESS_DENORM, evaluating the edit-metrics flag. If a row for the resource already exists, the procedure compares the existing edit-metrics value with the incoming value; where the existing value is 'N' and the incoming value is 'Y', the row is updated. An overloaded variant of this procedure omits the message-data OUT parameter and returns only the return status.
• UPDATE_RESOURCE — Updates the denormalized access row for a given resource and object. When the incoming edit-metrics value is 'N' while the existing value is 'Y', it checks whether the resource still belongs to another group granting 'Y' access; if a matching 'Y' row is found, no change is made, otherwise the row is updated. An overloaded form omits the message-data parameter.
• DELETE_RESOURCE — Removes the denormalized access entry for a resource and object, evaluating edit-metrics in the same manner.
• INSERT_GROUP, UPDATE_GROUP, DELETE_GROUP — Corresponding maintenance operations for group-level denormalization, keeping JTF_RS_GROUPS_DENORM aligned with group membership changes.
• AMS_OBJECT_DENORM, AMS_ACCESS_DENORM, JTF_ACCESS_DENORM — Higher-level driver routines that perform bulk denormalization for objects, for access records, and for the JTF resource-security side respectively.

All resource-level procedures follow a consistent signature pattern: an IN parameter block (resource ID, object type, object ID, edit-metrics flag) followed by standard OUT parameters carrying return status, message count, and message data.

Tables Accessed

The package reads and writes the following objects, referenced through APPS synonyms:

• AMS_ACT_ACCESS_DENORM and AMS_ACT_ACCESS_DENORM_S — the primary denormalized access table and its sequence, which INSERT_RESOURCE, UPDATE_RESOURCE, and DELETE_RESOURCE populate and maintain.
• AMS_ACT_ACCESS — the normalized source of access definitions consulted when evaluating whether a resource retains access through another path.
• JTF_RS_GROUPS_B, JTF_RS_GROUPS_DENORM, and JTF_RS_GROUP_MEMBERS — resource group definitions and membership, used by the group-level procedures and by the JTF access denormalization driver.
• FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_REQUESTS — concurrent manager catalog tables, referenced to identify and track denormalization requests submitted as concurrent programs.
• DUAL and PLITBLM — the standard single-row utility table and the PL/SQL index-by table support object.

Usage Notes

Because AMS_ACCESS_DENORM_PVT is a private package, it is not documented for direct customer invocation and should be treated as an internal implementation detail of the Marketing access model. It is referenced by one other package in the ETRM catalog, which acts as the caller orchestrating denormalization. In practice it is invoked from Marketing setup and administration forms when access records or group memberships are created, changed, or removed, and from concurrent programs that rebuild denormalized access data in bulk. The consistent return-status and message-stack OUT parameters indicate it is designed to be called from PL/SQL with standard EBS error-handling conventions. Customizations should not call INSERT_RESOURCE or its siblings directly; instead, they should rely on the supported public APIs and concurrent processes that invoke this package, since direct calls bypass validation and messaging performed at higher layers.