Search Results person_name

Overview

AMW_PERSON_COMPANY_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It belongs to the AMW product family — Internal Controls Manager — which is the EBS module used to document, test, and certify internal controls over financial reporting. Within that module, this view exposes a denormalized, reporting-friendly projection of the relationship between a person (an employee or other individual party) and the organization that employs them. Its documented description is simply "View for person's company."

The view exists to simplify access to what would otherwise be a multi-table join against the Trading Community Architecture (TCA) registry. Rather than requiring report authors and integrators to understand relationship codes, subject/object semantics, and date-effective status logic, the view presents a flat result set of person and company identifiers and names. It is typically consumed in AMW-related reports, control certification listings, and custom extractions where a person-to-employer association is needed. Because AMW relies heavily on TCA party data for identifying users, employees, and their organizational affiliations, this view functions as a convenience layer over the registry rather than as a transactional object. It holds no data of its own; all values are derived at query time.

Underlying Base Objects

The documented view text shows that AMW_PERSON_COMPANY_V is defined over two TCA base tables: HZ_PARTIES and HZ_RELATIONSHIPS.

• HZ_PARTIES (alias P) — the driving table, supplying the person side of the join via PARTY_ID.
• HZ_RELATIONSHIPS (alias R) — supplies the employment relationship linking the person to an organization. The join is an outer join (indicated by the (+) operator) on SUBJECT_ID, constrained to SUBJECT_TYPE = 'PERSON', SUBJECT_TABLE_NAME = 'HZ_PARTIES', OBJECT_TYPE = 'ORGANIZATION', OBJECT_TABLE_NAME = 'HZ_PARTIES', and RELATIONSHIP_CODE = 'EMPLOYEE_OF'.

The relationship row is further filtered so that only active, currently effective associations qualify: START_DATE must be less than or equal to the current system date, STATUS must be 'A' (active), and END_DATE must either be null or greater than or equal to the current date. Because the join to HZ_RELATIONSHIPS is an outer join, persons with no active EMPLOYEE_OF relationship still appear, with null COMPANY_ID and COMPANY_NAME. The COMPANY_NAME column is populated by a scalar subquery against HZ_PARTIES, selecting PARTY_NAME for the organization identified by R.OBJECT_ID. No other base objects are documented in the ETRM metadata for this view.

Key Columns

• PERSON_ID — the PARTY_ID from HZ_PARTIES for the individual; the primary identifier for the person.
• PERSON_NAME — the formatted display name of the person, produced by calling HZ_FORMAT_PUB.FORMAT_NAME(PARTY_ID), the standard TCA name-formatting API. This honors the person's name style and formatting rules rather than returning a raw column.
• COMPANY_ID — the OBJECT_ID of the matching HZ_RELATIONSHIPS row, i.e., the PARTY_ID of the employing organization. Null when no active EMPLOYEE_OF relationship exists.
• COMPANY_NAME — the PARTY_NAME of that organization, resolved by scalar subquery against HZ_PARTIES. Null when COMPANY_ID is null.

The view exposes no additional attributes such as relationship start/end dates, status, or party attributes; consumers needing those details must query HZ_RELATIONSHIPS directly.

Common Use Cases and Queries

Typical uses include AMW control and certification reports listing employees alongside their employing organizations, validation of person-to-company mappings before control assignments, and integration extracts feeding downstream reporting or identity systems. Because it is a view with no DML capability, it is safe for ad hoc querying.

A simple retrieval by person name:

• SELECT person_id, person_name, company_id, company_name FROM apps.amw_person_company_v WHERE person_name LIKE '%SMITH%';

To list only persons with an active employing organization:

• SELECT person_name, company_name FROM apps.amw_person_company_v WHERE company_id IS NOT NULL ORDER BY company_name, person_name;

To count employees per company:

• SELECT company_id, company_name, COUNT(*) FROM apps.amw_person_company_v WHERE company_id IS NOT NULL GROUP BY company_id, company_name;

Note that PERSON_NAME derives from the TCA name-formatting function, which can be relatively expensive on large result sets; restricting by PERSON_ID or COMPANY_ID where possible improves performance. Join criteria involving the view should reference PERSON_ID or COMPANY_ID as stable keys rather than names.