Results for “hr_nusjob_base_v”

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

Overview

HR_NUSJOB_BASE_V is a reporting view owned by the APPS schema within the Oracle E-Business Suite Human Resources (PER) product family. Its purpose is to expose job definition data in a denormalized, user-facing form that resolves the language-specific job name for the current session, rather than requiring the report or interface to join the job base table and its translation table manually. The view presents a single row per job per language, filtered to the language of the running session as returned by USERENV('LANG').

The object carries a VALID status and is documented against both Oracle EBS 12.1.1 and 12.2.2. It is widely referenced in HRMS reporting, concurrent program queries, and integration extracts that require a readable JOB_NAME alongside the surrogate key JOB_ID. Because the view is a synonym-based definition in the APPS schema, custom reports and Oracle Discoverer/EBS SQL extracts can query it directly without needing grants on the underlying translated tables. It is important to note that the view is an internal, non-seeded object delivered with the HRMS schema; it is not typically exposed as a global descriptive flexfield or API, and consumers should treat its column list as a documented, unsupported-for-modification contract. The user search term "job_name" maps directly to the view's aliased column, which explains why HR_NUSJOB_BASE_V surfaces in job-title lookups.

Underlying Base Objects

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

• PER_JOBS (synonym) — the base job definition table. It holds the primary key JOB_ID, the BUSINESS_GROUP_ID (the business group that owns the job), and the effective dating columns DATE_FROM and DATE_TO.
• PER_JOBS_TL (synonym) — the translation (TL) table for jobs. It stores the language-specific NAME for each JOB_ID, keyed by JOB_ID and LANGUAGE.

The join condition is PJT.JOB_ID = PJ.JOB_ID AND PJT.LANGUAGE = USERENV('LANG'), returning the localized name for the session language. This is the standard Oracle Applications multilingual design pattern: the base table holds language-independent attributes, while the _TL table carries translated names, with the view resolving the language at runtime. Since both operands are synonyms owned by APPS, the view runs under the calling schema's privileges and inherits the standard EBS security model applied to PER_JOBS.

Key Columns

• JOB_ID — the unique, language-independent identifier of the job. It is the primary key of PER_JOBS and the join key to PER_JOBS_TL.
• JOB_NAME — the translated display name of the job for the session language; this is the column users search for when looking up a job title.
• BUSINESS_GROUP_ID — the business group that owns the job record; the standard partition for HRMS security and reporting.
• DATE_FROM — the effective start date of the job definition.
• DATE_TO — the effective end date of the job definition; a null value indicates the job is currently active.

Common Use Cases and Queries

The principal use case is retrieving readable job names in reporting and integration queries, filtering by business group and effective dates. A representative query is:

• SELECT job_id, job_name FROM hr_nusjob_base_v WHERE business_group_id = :p_bg_id ORDER BY job_name;
• SELECT job_id, job_name, date_from, date_to FROM hr_nusjob_base_v WHERE date_to IS NULL OR date_to >= SYSDATE;
• SELECT j.job_name FROM hr_nusjob_base_v j WHERE UPPER(j.job_name) LIKE UPPER('Account%'); — a typical job-name lookup.

Additional scenarios include populating LOVs in custom concurrent programs, feeding downstream integration interfaces that need a language-specific job label, and joining the view to PER_ALL_POSITIONS or assignment tables for title-based reporting. Because the language is resolved from the session via USERENV('LANG'), the same query returns different JOB_NAME values for different language environments; reports and interfaces should therefore set the session language explicitly to guarantee consistent output. Always constrain by BUSINESS_GROUP_ID and filter by DATE_FROM/DATE_TO to avoid retrieving historical or out-of-scope job rows.