Search Results amv_u_access

Overview

WF_AMV_CHN_UR is a database view owned by the APPS schema and registered under the FND - Application Object Library product in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to serve as a "Bulk Sync Directory Service User Role View for Role Orig System AMV_CHN." In practice, the view presents a flattened result set of user-to-role assignments, where the role is a channel defined in the Advanced Multichannel (AMV) module and the originating system is identified by the literal string 'AMV_CHN'. This structure follows the standard Oracle EBS pattern for directory integration views, which expose a uniform set of user, role, origination-system, and effective-date columns so that external directory services and provisioning engines can consume role membership data in bulk.

The naming convention reflects this purpose: the WF_ prefix indicates the Workflow directory services framework, AMV_CHN denotes the role origination system, and UR denotes "user role." The user's search term, "amv_u_access," corresponds directly to AMV_U_ACCESS, the central access table in the view definition that maps users and groups to secured AMV objects. The view therefore answers the question: which EBS users are currently designated as channel approvers, and to which channels are they assigned?

Underlying Base Objects

The ETRM metadata documents the following referenced base objects, all resolved through APPS synonyms:

• AMV_C_CHANNELS_B — the channel definition table, supplying CHANNEL_ID and channel identity.
• AMV_U_ACCESS — the access assignment table, supplying user/group type, access record, approver flag, and effective date ranges.
• JTF_RS_RESOURCE_EXTNS — the resource extension table, used to resolve the assigned party to a resource record with category 'EMPLOYEE'.
• PER_ALL_PEOPLE_F — the HR people table (date-tracked), used to derive the PERSON_ID for the user.
• FND_USER — the application user table, supplying USER_NAME and linking to a person through EMPLOYEE_ID.

The joins are tightly constrained. AMV_U_ACCESS rows are restricted to ACCESS_TO_TABLE_CODE = 'CHANNEL', USER_OR_GROUP_TYPE = 'USER', and CHL_APPROVER_FLAG = 'T'. The access record must match a channel identifier, and the assignment must be current: EFFECTIVE_START_DATE <= SYSDATE and NVL(EXPIRATION_DATE, SYSDATE) >= SYSDATE. The user or group identifier is then matched to JTF_RS_RESOURCE_EXTNS.RESOURCE_ID with category 'EMPLOYEE', whose SOURCE_ID resolves to a PER_ALL_PEOPLE_F row that is effective as of the truncated current date.

Key Columns

• USER_NAME — the FND_USER user name, i.e., the login identity being provisioned.
• USER_ORIG_SYSTEM — always the literal 'PER', indicating the user originates from the HR (PER) system.
• USER_ORIG_SYSTEM_ID — the PER_ALL_PEOPLE_F PERSON_ID of the user.
• ROLE_NAME — a concatenation of 'AMV_CHN' with the channel identifier, forming the composite role name.
• ROLE_ORIG_SYSTEM — the literal 'AMV_CHN', the role origination system.
• ROLE_ORIG_SYSTEM_ID — the AMV_C_CHANNELS_B CHANNEL_ID.
• START_DATE / EXPIRATION_DATE — both selected as TO_DATE(NULL); this view does not propagate the access table's date range and relies instead on the runtime SYSDATE filter.
• SECURITY_GROUP_ID — returned as NULL, consistent with multi-org security not being applied here.
• PARTITION_ID — a constant 6, indicating the partitioning category used for this role type.

Common Use Cases and Queries

Typical usage includes auditing which users hold channel approver authority, validating synchronization payloads delivered to a directory service, and troubleshooting why a user does or does not appear as a channel approver. Because the view filters on current effective dates and HR record currency, it is suited to point-in-time reconciliation rather than historical analysis.

A representative query lists all current channel approvers:

• SELECT user_name, role_name, role_orig_system_id FROM apps.wf_amv_chn_ur ORDER BY user_name, role_orig_system_id;

A targeted query identifies approvers for a specific channel:

• SELECT user_name FROM apps.wf_amv_chn_ur WHERE role_orig_system_id = :channel_id;

A reconciliation query confirms that a given user is exposed as a channel approver:

• SELECT role_name, role_orig_system_id FROM apps.wf_amv_chn_ur WHERE user_name = :user_name;

The absence of a row for a user who appears in AMV_U_ACCESS generally indicates an expired access record, a missing CHL_APPROVER_FLAG, a resource record not categorized as EMPLOYEE, or a discontinued HR assignment.