Results for “per_disabilities_d”

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

Overview

PER_DISABILITIES_D is a read-only view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PER (Human Resources) product family. The ETRM documentation describes it as "Used by Datetrack History." In Oracle HRMS, Datetrack is the date-tracking framework that maintains the effective-dated history of person and assignment records. The "_D" suffix is the standard naming convention for the datetrack history view over a "_F" (date-tracked base) table — here PER_DISABILITIES_F. The view presents the chronological, effective-dated history of disability records so that prior and current versions of an employee's disability registration, categorization, and work-restriction data remain queryable after changes are saved.

In the 12.1.1 and 12.2.2 releases the view is deployed with the WITH READ ONLY clause, confirming its purpose as a reporting and integration surface rather than a maintenance path. It also performs lookup decoding at the database layer, so consumers such as BI Publisher reports, Oracle Discoverer workbooks, and interface extracts receive human-readable category, status, and reason values without joining to FND_LOOKUPS directly.

Underlying Base Objects

The ETRM metadata lists the following referenced base objects: PER_DISABILITIES_F, PER_WORK_INCIDENTS, PER_MEDICAL_ASSESSMENTS, HR_ALL_ORGANIZATION_UNITS, FND_USER, and the HR_GENERAL package.

• PER_DISABILITIES_F — the primary date-tracked table supplying disability attributes, effective dates, and disability registration fields.
• PER_WORK_INCIDENTS — outer-joined (+) on INCIDENT_ID to supply incident reference and incident date.
• PER_MEDICAL_ASSESSMENTS — outer-joined on DISABILITY_ID, filtered to CONSULTATION_RESULT = 'DI', supplying the consultation date.
• HR_ALL_ORGANIZATION_UNITS — outer-joined on ORGANIZATION_ID to resolve the organization name.
• FND_USER — outer-joined on LAST_UPDATED_BY to resolve the user name of the last updater.
• HR_GENERAL — the HRMS utility package whose DECODE_LOOKUP function converts lookup codes for DISABILITY_CATEGORY, DISABILITY_STATUS, and DISABILITY_REASON into their display meanings, truncated with SUBSTR to 80 characters.

Because every secondary table is outer-joined, the view preserves a disability history row even when no incident, assessment, organization, or user record is available.

Key Columns

The view exposes the datetrack keys DISABILITY_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, which together identify each historical version of a disability record.

• CATEGORY, STATUS, REASON — decoded lookup meanings for disability category, status, and reason.
• FTE, DEGREE, DESCRIPTION, WORK_RESTRICTION — quota FTE, degree of disability, free-text description, and work-restriction detail.
• INCIDENT_REFERENCE, INCIDENT_DATE — the related work incident.
• CONSULTATION_DATE — the medical assessment consultation date for result 'DI'.
• ORGANIZATION_NAME — the disability record's owning organization.
• REGISTRATION_ID, REGISTRATION_DATE, REGISTRATION_EXP_DATE — the registration identifier, registration date, and registration expiry date. The registration_exp_date column is the field most commonly used to drive expiry monitoring, renewal notifications, and compliance reporting on disability registrations.
• LAST_UPDATE_DATE, LAST_UPDATED_BY — change-audit columns, with the latter resolved to a user name.

Common Use Cases and Queries

Typical uses include registration expiry tracking, disability-category headcount reporting, and audit extracts of historical disability changes.

Retrieve current registration data whose expiry falls within the next 90 days:

SELECT disability_id, registration_id, registration_date, registration_exp_date, category
FROM apps.per_disabilities_d
WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
AND registration_exp_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 90
ORDER BY registration_exp_date;

List the full datetrack history for a single registration:

SELECT disability_id, effective_start_date, effective_end_date, status, registration_exp_date
FROM apps.per_disabilities_d
WHERE registration_id = :registration_id
ORDER BY effective_start_date, effective_end_date;

Summarize active disability records by organization:

SELECT organization_name, category, COUNT(*)
FROM apps.per_disabilities_d
WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date
GROUP BY organization_name, category;

When querying "current" rows, always bound SYSDATE against the effective dates; historical rows will otherwise inflate counts. Because the view is read-only and unindexed in its own right, filter predicates on DISABILITY_ID, ORGANIZATION_NAME, or dates substantially improve performance.