Results for “eng_security_people_v”

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

Overview

ENG_SECURITY_PEOPLE_V is an APPS-owned database view in the Oracle E-Business Suite Engineering (ENG) module, documented as VALID in ETRM for both 12.1.1 and 12.2.2. The view consolidates Oracle EBS application users into a single unified list of "people," classified by a PERSON_TYPE discriminator, and resolves each entry to a PERSON_ID and USER_ID pair. Its principal role is to supply a normalized roster of internal employees, vendor users, and customer users that engineering security routines can consume when evaluating who may access engineering data. Rather than joining separately to HR, Trading Community Architecture (TCA), and FND user tables, consumers query one object and read the PERSON_TYPE value to determine the user's origin. The view is therefore typically referenced by engineering security setup forms, concurrent programs, and reporting queries that need to enumerate or filter the population of FND users by category.

Underlying Base Objects

The view is defined over four referenced base objects: FND_USER, PER_ALL_PEOPLE_F, HZ_CODE_ASSIGNMENTS, and HZ_CUST_ACCOUNTS, all accessed through APPS synonyms. The definition is a three-branch UNION ALL. The first branch joins FND_USER to PER_ALL_PEOPLE_F on EMPLOYEE_ID equals PERSON_ID, restricting to current employees (CURRENT_EMPLOYEE_FLAG = 'Y') whose effective date range brackets SYSDATE, and labels the row 'INTERNAL'. The second branch joins FND_USER to HZ_CODE_ASSIGNMENTS where OWNER_TABLE_NAME is 'HZ_PARTIES', CLASS_CODE is 'VENDOR_USER', and CLASS_CATEGORY is 'POS_PARTICIPANT_TYPE'; rows must have a null EMPLOYEE_ID and a populated PERSON_PARTY_ID, and are labeled 'VENDOR'. The third branch returns FND_USER rows having a matching active HZ_CUST_ACCOUNTS record (STATUS = 'A') with null EMPLOYEE_ID and populated PERSON_PARTY_ID, labeled 'CUSTOMER'. The outer query filters on the FND user start date being on or before SYSDATE and the end date being null or on or after SYSDATE, so the view exposes only currently active users.

Key Columns

• PERSON_ID — For internal users, the HR party identifier from PER_ALL_PEOPLE_F; for vendor and customer users, the FND_USER.PERSON_PARTY_ID. It is the TCA/HR person key rather than the FND user identifier.
• USER_ID — The FND_USER primary key, the value most commonly stored in security and audit columns.
• USER_NAME — The FND_USER login name, used for display and for filtering by known user.
• PERSON_TYPE — The discriminator with values 'INTERNAL', 'VENDOR', or 'CUSTOMER', indicating the source branch from which the row was produced.

Because only these four columns are projected, the view deliberately hides the underlying dates and party attributes; effective dating is applied internally so that callers receive a clean, active-user picture.

Common Use Cases and Queries

Typical scenarios include enumerating internal employees who can be granted engineering access, listing vendor users associated with supplier responsibilities, and identifying customers with active accounts for read-only engineering collaboration. A frequent query filters on the user's search term, as in the following example, which returns only internal users matching a name pattern:

SELECT person_id, user_id, user_name, person_type
FROM apps.eng_security_people_v
WHERE person_type = 'INTERNAL'
ORDER BY user_name;

To validate that a specific login is available and to see how it is classified, a lookup by user name is used:

SELECT person_id, user_id, user_name, person_type
FROM apps.eng_security_people_v
WHERE user_name = :p_user_name;

Because the view already restricts to active FND users whose start and end dates bracket SYSDATE, no additional effective-date predicate is required. When joining to engineering security tables, join on USER_ID; when joining to TCA party-based data, join on PERSON_ID. Note that EMPLOYEE_ID-null vendor and customer rows will not appear in PER_ALL_PEOPLE_F, so any downstream join to HR person views must account for the PERSON_TYPE value to avoid dropping those rows.