Results for “edw_sec_dim_access_v”

15 results




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

Overview

EDW_SEC_DIM_ACCESS_V is a reporting view owned by the APPS schema within the BIS (Business Intelligence System / Applications BIS) product family of Oracle E-Business Suite. Its purpose is to expose the security dimension access assignments maintained by the Enterprise Data Warehouse (EDW) security model in a form that is directly consumable by reports, dashboards, and downstream extracts. The view answers the operational question of which responsibility is granted access to which dimension and level, returning descriptive names for responsibilities, applications, dimensions, and levels rather than the raw numeric identifiers alone. Because the view joins the EDW security assignment table to the standard EBS responsibility and application views, it allows business users and report authors to interpret security configuration without needing to resolve foreign keys manually. It is valid in both 12.1.1 and 12.2.2 environments, and the schema-qualified implementation is APPS.EDW_SEC_DIM_ACCESS_V.

Underlying Base Objects

The view text is documented and resolves through five referenced objects. The primary source is the EDW security dimension access table (EDW_SEC_DIM_ACCESS, aliased EDW), which stores the RESP_ID, DIM_ID, LEVEL_ID, and ACCESS_VALUE assignments. It is joined to FND_RESPONSIBILITY_VL (aliased RESP) on RESP_ID = RESPONSIBILITY_ID for the responsibility name, and to FND_APPLICATION_VL (aliased APPN) on the responsibility's APPLICATION_ID for the application name. Dimension and level descriptive attributes are supplied by EDW_SEC_DIM_INFO_V (aliased DIM, keyed on DIM_ID) and EDW_SEC_LVL_INFO_V (aliased LVL, keyed on LEVEL_ID). The join is further constrained so that DIM.DIM_ID = LVL.DIM_ID, ensuring the level belongs to the same dimension. The view uses SELECT DISTINCT to eliminate duplication (noted in the source as added due to a wildcard query problem), and it also selects EDW.ROWID. ETRM lists no further referenced base objects beyond these.

Key Columns

  • RESP_ID — the responsibility identifier; the primary key most users search on.
  • ROW_ID — the row identifier from the source EDW table.
  • DIM_ID / LEVEL_ID — dimension and level identifiers that define the security granularity.
  • RESPONSIBILITY / APPLICATION — descriptive name of the responsibility and its owning application.
  • DIMENSION / LEVEL_NAME — long descriptive names of the dimension and level.
  • DIM_SHORT_NAME / LEVEL_SHORT_NAME — abbreviated names, useful in constrained report layouts.
  • ACCESS_VALUE — the access granted (for example, a read or full-access indicator) at the dimension/level intersection.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns for the assignment record.

Common Use Cases and Queries

The view is typically used to audit or reverse-engineer EDW security grants for a given responsibility, or to list all responsibilities touching a dimension. A common pattern is to filter by RESP_ID when investigating a specific responsibility's dimension access:

  • Find access by responsibility: SELECT responsibility, application, dimension, level_name, access_value FROM apps.edw_sec_dim_access_v WHERE resp_id = :p_resp_id;
  • Find responsibilities granted a specific dimension: SELECT resp_id, responsibility, access_value FROM apps.edw_sec_dim_access_v WHERE dimension = :p_dim;
  • Inventory all assignments for audit: SELECT responsibility, dimension, level_name, access_value, last_update_date FROM apps.edw_sec_dim_access_v ORDER BY responsibility, dimension;

Because the view already resolves RESPONSIBILITY and APPLICATION, it is preferable to querying EDW_SEC_DIM_ACCESS directly whenever human-readable output is required.