Results for “ams_jtf_rs_emp_v”

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

Overview

AMS_JTF_RS_EMP_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, classified under the AMS (Marketing) product family. As documented in the ETRM metadata for release 12.2.2 (and applicable to 12.1.1), the view was created to expose the details of Marketing resources. Rather than presenting raw resource extension data, it produces a denormalized, human-readable projection that joins the resource extension base table to its translation table, returning names in the session language alongside a broad set of employee, address, and organizational attributes. This makes the view a convenient integration and reporting surface for Marketing modules that need to resolve resource IDs into descriptive resource records without consumers having to replicate the join or language-handling logic.

Underlying Base Objects

The view is defined over two documented base objects, both referenced through APPS synonyms:

• JTF_RS_RESOURCE_EXTNS — the resource extension table (aliased B in the view text), supplying the core resource attributes, sourcing columns, and descriptive flexfield segments.
• JTF_RS_RESOURCE_EXTNS_TL — the translation table (aliased TL), supplying the language-dependent RESOURCE_NAME.

The view joins these tables on RESOURCE_ID and constrains the translation row with TL.LANGUAGE = USERENV('LANG'), so each query returns the resource name in the caller's current language. A FIRST_ROWS hint is embedded in the definition, reflecting the view's intended use as an interactive query source. The WHERE clause restricts output to resources with RESOURCE_ID > 0 and a CATEGORY limited to EMPLOYEE, PARTNER, or PARTY, effectively excluding other resource classifications.

Key Columns

The view exposes identity, audit, and descriptive columns. Key groupings include:

• Identity and audit: RESOURCE_ID, RESOURCE_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER.
• Resource naming: RESOURCE_NAME and FULL_NAME (both drawn from the translation table), plus FIRST_NAME, MIDDLE_NAME, and LAST_NAME sourced from the resource extension record.
• Employee linkage: EMPLOYEE_ID, EMPLOYEE_NUMBER, JOB_TITLE, EMAIL, PHONE, MANAGER_ID, and MANAGER_NAME.
• Address and organization: ADDRESS_ID, CONTACT_ID, ORG_ID, ORG_NAME, ADDRESS1 through ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, and COUNTRY.
• Classification and state: CATEGORY, STATUS, START_DATE_ACTIVE, END_DATE_ACTIVE, and ATTRIBUTE_CATEGORY with ATTRIBUTE1 through ATTRIBUTE15 for descriptive flexfield data.

Note that several columns are aliased from SOURCE_* columns, mapping the underlying resource extension fields to business-friendly names.

Common Use Cases and Queries

Typical scenarios include Marketing resource lookups, employee-facing list-of-values, and integration extracts that require resolved resource names and contact details. A representative query returns active employees with their organizational context:

SELECT resource_id, resource_number, full_name, email, job_title, org_name
FROM   ams_jtf_rs_emp_v
WHERE  category = 'EMPLOYEE'
AND    TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);

Because the view already constrains language and category, callers can filter further by ORG_ID, MANAGER_ID, or STATUS without reimplementing those predicates. Joins to marketing campaign or resource-assignment tables are commonly performed on RESOURCE_ID.