Results for “pa_dist_list_denorm”

34 results




Overview
Explore in AI Chat

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

Overview

PA_DIST_LIST_DENORM is a denormalized (materialized) table in the Oracle EBS Projects (PA) schema that maintains the expanded roster of people associated with a distribution list, along with their access details. Distribution lists in Oracle Projects are used to route notifications, approvals, and workflow messages to a defined audience; PA_DIST_LIST_DENORM provides a pre-resolved, query-friendly projection of that audience so that downstream processes do not have to repeatedly walk the hierarchical definition stored in PA_DISTRIBUTION_LISTS. Records are populated from PA_DISTRIBUTION_LISTS whenever a given LIST_ID does not already exist in the denormalized table, making it a derived cache rather than a system of record.

From a Data Vault modeling perspective, the foreign key topology suggests this object behaves as a link: it associates a distribution list, a resource type, and a resource source (person or resource), with the surrogate-style composite key (LIST_ID, RESOURCE_TYPE_ID, RESOURCE_SOURCE_ID) forming the unique business key. The access-level and menu attributes act as descriptive payload rather than independent hubs.

Key Information Stored

The table contains 11 documented columns. The most significant are:

  • LIST_ID — Identifier of the parent distribution list; the primary grouping key and the first component of the unique index.
  • RESOURCE_TYPE_ID — Foreign key to PA_RESOURCE_TYPES, classifying the type of resource (for example person, group, or role) included in the list.
  • RESOURCE_SOURCE_ID — Identifies the specific resource or person instance; the third component of the unique business key.
  • ACCESS_LEVEL — The access or permission level granted to the listed resource for the distribution list.
  • MENU_ID — Foreign key to FND_MENUS, linking the resource to a specific menu used in the access context.
  • RECORD_VERSION_NUMBER — Optimistic locking / concurrency control column.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS WHO columns capturing audit and provenance metadata.

The unique index PA_DIST_LIST_DENORM_U1 on (LIST_ID, RESOURCE_TYPE_ID, RESOURCE_SOURCE_ID) is the documented business-key candidate. No separate single-column surrogate primary key is documented; the composite unique index effectively serves that role.

Common Use Cases and Queries

Typical uses include listing the resolved members of a distribution list, auditing access levels, and reporting which menus are attached to list members. Because the table is denormalized, queries are simpler and faster than traversing PA_DISTRIBUTION_LISTS.

  • Members of a list: SELECT RESOURCE_TYPE_ID, RESOURCE_SOURCE_ID, ACCESS_LEVEL FROM PA_DIST_LIST_DENORM WHERE LIST_ID = :list_id;
  • Join to resource types for labels: SELECT d.LIST_ID, t.NAME, d.RESOURCE_SOURCE_ID FROM PA_DIST_LIST_DENORM d, PA_RESOURCE_TYPES t WHERE d.RESOURCE_TYPE_ID = t.RESOURCE_TYPE_ID;
  • Access audit by menu: SELECT d.LIST_ID, d.MENU_ID, d.ACCESS_LEVEL FROM PA_DIST_LIST_DENORM d WHERE d.MENU_ID = :menu_id;
  • Change tracking: filter on LAST_UPDATE_DATE for incremental extracts.

Reporting scenarios include distribution-list membership reports, access-level compliance reviews, and reconciliation between the denormalized cache and its PA_DISTRIBUTION_LISTS source.

Related Objects

  • PA_DISTRIBUTION_LISTS — Source of records; this table is populated from it when a LIST_ID does not yet exist.
  • PA_RESOURCE_TYPES — Referenced via RESOURCE_TYPE_ID, providing resource type classification.
  • FND_MENUS — Referenced via MENU_ID, defining the menu associated with access.
  • PA_DIST_LIST_DENORM_U1 — Unique index enforcing the business key.
  • PER_ALL_PEOPLE_F / PER_ALL_PEOPLE_X — Commonly joined to resolve RESOURCE_SOURCE_ID to a person when the resource type represents individuals.
  • FND_USER — Frequently joined in notification and workflow contexts to resolve the application user.

Because this object is a derived cache, interfaces that depend on it should tolerate periodic refresh from PA_DISTRIBUTION_LISTS.