Results for “hz_tenure_code”

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

Overview

APPS.IGS_AD_EMP_DTL is a reporting and integration view within the Oracle E-Business Suite that consolidates employment history information for parties (persons) recorded in the Human Capital Management / Trading Community Architecture (TCA) data model, enriched with Oracle Student System (IGS) occupational detail. The view exposes a denormalized, human-readable projection of the HZ_EMPLOYMENT_HISTORY table, resolving coded values into their meanings and joining supplementary detail such as occupational title codes. It is used principally for admissions, student-record, and third-party reporting where an institution must display or transmit a party's employment background together with descriptive lookups.

A key feature is the resolution of the HZ_TENURE_CODE lookup. The user search term hz_tenure_code maps directly to the TENURE_OF_EMPLOYMENT column and its underlying TENURE_OF_EMPLOYMENT_CODE column, both derived from the HZ_TENURE_CODE lookup type. This makes the view the natural access point when reporting on tenure of employment in a decoded, presentation-ready form.

Underlying Base Objects

The view is defined over several base and supporting objects joined with outer joins to preserve employment-history rows even when optional detail is absent:

• HZ_EMPLOYMENT_HISTORY EH — the primary driving table holding employment history records, including dates, employer, position, hours, and the coded TENURE_CODE and EMPLOYMENT_TYPE_CODE.
• IGS_AD_HZ_EMP_DTL AHED — the IGS admissions employment detail extension table, supplying the OCCUPATIONAL_TITLE_CODE for the employment-history record.
• IGS_PS_DIC_OCC_TITLS DOT — the occupational titles dictionary, providing the descriptive OCCUPATIONAL_TITLE.
• HZ_PARTIES HZ — the TCA party table, supplying the employer party number and name via EMPLOYED_BY_PARTY_ID.
• FND_LOOKUP_VALUES L1 — resolves HZ_EMPLOYMENT_TYPE codes into the TYPE_OF_EMPLOYMENT meaning.
• FND_LOOKUP_VALUES L2 — resolves HZ_TENURE_CODE codes into the TENURE_OF_EMPLOYMENT meaning.

Lookup joins are restricted to VIEW_APPLICATION_ID = 222, SECURITY_GROUP_ID = 0, and the session language via USERENV('LANG'), ensuring the correct translated meaning is returned.

Key Columns

• ROW_ID — the ROWID of the employment-history row, enabling direct DML anchoring.
• EMPLOYMENT_HISTORY_ID / PERSON_ID — primary key and party identifier.
• START_DT / END_DT — begin and end dates of employment (from BEGIN_DATE and END_DATE).
• TENURE_OF_EMPLOYMENT (L2.MEANING) and TENURE_OF_EMPLOYMENT_CODE — the decoded and raw values sourced from the HZ_TENURE_CODE lookup; these are the columns targeted by the hz_tenure_code search.
• TYPE_OF_EMPLOYMENT / TYPE_OF_EMPLOYMENT_CODE — decoded and raw employment-type values from HZ_EMPLOYMENT_TYPE.
• FRACTION_OF_EMPLOYMENT — the fraction of full-time tenure (FRACTION_OF_TENURE).
• POSITION, OCCUPATIONAL_TITLE_CODE, OCCUPATIONAL_TITLE — job title and standardized occupational title.
• EMPLOYER / EMPLOYED_BY_PARTY_NUMBER / EMPLOYED_BY_PARTY_NAME — employer identification details.
• CONTACT, WEEKLY_WORK_HOURS, COMMENTS, REASON_FOR_LEAVING — supplementary descriptive attributes.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER, and concurrent-program columns.

Common Use Cases and Queries

The view is commonly used to report employment histories by tenure classification, to display occupational detail in admissions screens, and to feed integration extracts requiring decoded lookups.

• Reporting all parties with a given tenure category:

SELECT person_id, start_dt, end_dt, tenure_of_employment_code,
       tenure_of_employment, type_of_employment
FROM   apps.igs_ad_emp_dtl
WHERE  tenure_of_employment_code = :p_tenure_code;

• Listing employment history with employer name and occupational title:

SELECT person_id, position, occupational_title,
       employer, employed_by_party_number
FROM   apps.igs_ad_emp_dtl
WHERE  person_id = :p_person_id
ORDER BY start_dt DESC;

Because the lookup joins are outer joins, rows lacking a valid HZ_TENURE_CODE value still appear, returning TENURE_OF_EMPLOYMENT_CODE with a null meaning; queries filtering on decoded values should therefore account for this behavior.