Results for “tenure_of_employment_code”

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

Overview

The IGS_AD_EMP_DTL view is a denormalized reporting object owned by the APPS schema within the IGS — Student System product family in Oracle EBS 12.1.1 and 12.2.2. It presents a flattened, human-readable projection of employment history records held in the Oracle Human Resources (PER/HZ) data model, combining raw employment attributes with decoded lookup meanings, occupational title descriptions, and employer party details. Its principal role is to support admissions, student records, and institutional reporting where an applicant's or student's employment background must be reviewed or extracted. Rather than joining the base employment history table to multiple lookup and party tables at query time, consumers can select directly from this view.

The view is especially useful when the attribute TENURE_OF_EMPLOYMENT is required, since it exposes both the coded value and its decoded meaning side by side.

Underlying Base Objects

The view text joins five underlying objects. The driving table is HZ_EMPLOYMENT_HISTORY (aliased EH), which stores one row per employment history record. The remaining objects are joined with outer joins to avoid losing employment rows when related data is missing:

• IGS_AD_HZ_EMP_DTL (aliased AHED) — joined on EMPLOYMENT_HISTORY_ID, supplying occupational title codes.
• IGS_PS_DIC_OCC_TITLS (aliased DOT) — joined on OCCUPATIONAL_TITLE_CODE, supplying the descriptive occupational title.
• HZ_PARTIES (aliased HZ) — joined on EMPLOYED_BY_PARTY_ID, supplying the employer party number and name.
• FND_LOOKUP_VALUES (aliased L1 and L2) — joined twice to decode employment type and tenure code against the lookup types HZ_EMPLOYMENT_TYPE and HZ_TENURE_CODE respectively, filtered by VIEW_APPLICATION_ID = 222, SECURITY_GROUP_ID = 0, and the session language.

Key Columns

• EMPLOYMENT_HISTORY_ID — Primary key of the employment record.
• PERSON_ID — Party identifier of the person whose employment is described.
• START_DT / END_DT — Employment period boundaries from BEGIN_DATE and END_DATE.
• TENURE_OF_EMPLOYMENT — The decoded meaning of the tenure code, sourced from the lookup with type HZ_TENURE_CODE; the coded counterpart is exposed as TENURE_OF_EMPLOYMENT_CODE.
• TYPE_OF_EMPLOYMENT / TYPE_OF_EMPLOYMENT_CODE — Decoded and coded employment type (HZ_EMPLOYMENT_TYPE).
• FRACTION_OF_EMPLOYMENT — Fraction of full tenure represented by the engagement.
• POSITION, OCCUPATIONAL_TITLE_CODE, OCCUPATIONAL_TITLE — Job title information.
• EMPLOYER, EMPLOYED_BY_PARTY_NUMBER, EMPLOYED_BY_PARTY_NAME — Employer identification.
• WEEKLY_WORK_HOURS, MILITARY_RANK, SERVED, STATION, REASON_FOR_LEAVING — Additional employment details.
• Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER) and concurrent program context columns.

Common Use Cases and Queries

Typical consumers include student admissions reporting, financial aid verification, and integration extracts. A frequently used pattern retrieves tenure information for a given person:

• SELECT person_id, start_dt, end_dt, employer, tenure_of_employment FROM igs_ad_emp_dtl WHERE person_id = :p_person_id;
• SELECT tenure_of_employment, COUNT(*) FROM igs_ad_emp_dtl GROUP BY tenure_of_employment; for distribution analysis.
• Joining PERSON_ID to HZ_PARTIES or HZ_PERSON_PROFILES to enrich student or applicant records.

Because the lookup joins are language-sensitive, results reflect the session language determined by USERENV('LANG').