Search Results emp_cmpy

Overview

The APPS.ENG_CHANGE_ROUTE_PEOPLE_V view is a reporting and integration construct within the Oracle E-Business Suite Engineering (ENG) module. It exposes the individuals and roles assigned to steps within an Engineering Change Order (ECO) routing workflow, normalizing assignee data drawn from multiple sources—Workflow roles (menus) and trading-partner or party records—into a single, uniformly shaped result set. The view is defined as a UNION ALL of two branches: the first resolves assignees whose ASSIGNEE_TYPE_CODE is 'ROLE' against FND_MENUS_TL, while the second resolves party-based assignees (persons and organizations) through Oracle's party model.

Because routing participation drives change-order approvals, notifications, and audit history, this view is frequently consumed by custom reports, workflow extensions, and integration interfaces that need a denormalized, human-readable picture of who is responsible at each routing step. It is owned by the APPS schema and is typically granted to reporting and read-only responsibilities.

Underlying Base Objects

The view is defined over the following documented objects:

• ENG_CHANGE_ROUTE_PEOPLE_VL (VIEW) — the primary source of routing-person rows, contributing identifiers, flags, response data, and audit columns.
• FND_LOOKUPS (VIEW) — joined twice to translate coded values into meanings: ENG_ROUTE_ASSIGNEE_TYPES for the assignee type and ENG_CHANGE_ROUTE_STATUSES for the response status.
• FND_MENUS_TL (SYNONYM) — supplies the role name (USER_MENU_NAME) when the assignee is a role.
• HZ_PARTIES (SYNONYM) — supplies the party name for person and organization assignees.
• HZ_RELATIONSHIPS (SYNONYM) — used to derive the company affiliation associated with a person assignee.
• FND_GLOBAL (PACKAGE) — referenced via USERENV('LANG') to filter translated menu names to the session language.

The relationship is essentially a master routing-person record (from the VL view) enriched with descriptive names and lookup meanings. The two branches of the union differ only in how the assignee name and company name are sourced.

Key Columns

• ROW_ID, ROUTE_PEOPLE_ID, STEP_ID — primary and foreign identifiers linking the assignee to a routing step.
• ASSIGNEE_ID, ASSIGNEE_NAME — the assignee identifier and its resolved display name.
• ASSIGNEE_TYPE_CODE, ASSIGNEE_TYPE — the assignee category code and its lookup meaning (for example, ROLE).
• ASSIGNEE_COMPANY_NAME — the affiliated company; populated only in the party branch (the role branch returns a null/empty string).
• ADHOC_PEOPLE_FLAG — indicates whether the assignee was added ad hoc rather than defined on the template.
• RESPONSE_CODE, RESPONSE, RESPONSE_DATE, RESPONSE_DESCRIPTION — the assignee's action and its lookup meaning.
• WF_NOTIFICATION_ID — the Workflow notification associated with the routing assignment.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, ORIGINAL_SYSTEM_REFERENCE — standard audit and concurrent-program tracking columns.

Common Use Cases and Queries

Typical uses include listing all participants for a given routing step, identifying outstanding approvers, and joining routing people to change-order headers for status reporting. Note that the documentation does not expose an emp_cmpy column; company affiliation is surfaced through ASSIGNEE_COMPANY_NAME via HZ_PARTIES and HZ_RELATIONSHIPS.

Retrieve all assignees for a step:

• SELECT route_people_id, step_id, assignee_name, assignee_type, response FROM apps.eng_change_route_people_v WHERE step_id = :step_id;

List role-based assignees with their response status:

• SELECT assignee_name, assignee_type, response, response_date FROM apps.eng_change_route_people_v WHERE assignee_type_code = 'ROLE' ORDER BY response_date;

Identify party assignees and their company:

• SELECT assignee_name, assignee_company_name, adhoc_people_flag FROM apps.eng_change_route_people_v WHERE assignee_type_code <> 'ROLE';

Because the view applies outer joins to the response lookup, assignees without a recorded response appear with a null RESPONSE and can be isolated with WHERE response IS NULL to support pending-approval reporting.