Results for “consultation_result”

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

Overview

The PER_MEDICAL_ASSESSMENTS_V view is a seeded, VALID database object owned by the APPS schema within the Oracle E-Business Suite PER — Human Resources product family. Per the ETRM documentation, its stated purpose is to "support user interface" — that is, it is an interface-support view consumed by Oracle EBS forms, OAF pages, and related self-service screens that display medical assessment data. It is not a transactional base table but a projection layer that denormalizes foreign-key identifiers into human-readable, translated descriptions and joins in related organization and incident context.

The view is central to the "consultation_result" search context because it exposes both the raw lookup code CONSULTATION_RESULT and a decoded description column CONSULTATION_RESULT_DESC, derived via HR_GENERAL.DECODE_LOOKUP. This makes it the natural source for reporting on the outcome of employee medical consultations.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects:

• PER_MEDICAL_ASSESSMENTS (synonym) — the primary base table holding one row per medical assessment/consultation record. All MEA.* columns originate here.
• PER_WORK_INCIDENTS (synonym) — joined as INC to supply incident reference and date context (INCIDENT_REFERENCE, INCIDENT_DATE).
• HR_ALL_ORGANIZATION_UNITS and HR_ALL_ORGANIZATION_UNITS_TL (synonyms) — provide the organization name (ORGTL.NAME) associated with the examining organization.
• HR_GENERAL (package) — supplies the DECODE_LOOKUP function used to translate lookup codes into descriptions, wrapped with SUBSTR(..., 1, 80) to cap display length.

Because the base table objects are referenced as APPS synonyms, querying the view does not require knowledge of the underlying physical schema owner.

Key Columns

• MEDICAL_ASSESSMENT_ID — primary key of the assessment.
• PERSON_ID — the employee/subject of the assessment.
• EXAMINER_NAME, ORGANIZATION_ID, ORGTL.NAME — examiner and examining organization details.
• CONSULTATION_DATE, CONSULTATION_TYPE, CONSULTATION_TYPE_DESC — when the consultation occurred and its type (code plus decoded description).
• CONSULTATION_RESULT, CONSULTATION_RESULT_DESC — the raw lookup code and decoded result description, the definitive columns for "consultation_result" reporting.
• INCIDENT_ID, INCIDENT_REFERENCE, INCIDENT_DATE — linkage to any associated work incident.
• DISABILITY_ID, NEXT_CONSULTATION_DATE, DESCRIPTION — additional clinical and scheduling attributes.
• Extensive ATTRIBUTE1–ATTRIBUTE30 and MEA_INFORMATION1–MEA_INFORMATION30 flexfield columns, prefixed by ATTRIBUTE_CATEGORY and MEA_INFORMATION_CATEGORY, for descriptive flexfield and information-typed data.

Common Use Cases and Queries

Typical uses include decoded result reporting, incident correlation, and next-consultation scheduling extracts.

• Summarizing consultation outcomes by result description for a given population.
• Joining assessments to work incidents to trace result to cause.
• Extracting upcoming NEXT_CONSULTATION_DATE values for scheduling.

SELECT a.person_id,
       a.consultation_date,
       a.consultation_type_desc,
       a.consultation_result_desc,
       a.incident_reference,
       a.next_consultation_date
  FROM apps.per_medical_assessments_v a
 WHERE a.consultation_result = :result_code
   AND a.consultation_date >= :from_date
 ORDER BY a.consultation_date DESC;

Because the view already decodes lookup values and resolves organization names, it reduces reliance on custom joins for these display-oriented requirements.