Results for “ssp_medicals”

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

Overview

SSP_MEDICALS is a transactional table in the SSP (Statutory Sickness Pay) product module of Oracle E-Business Suite, holding the medical evidence records associated with an employee's sickness absence or maternity absence. Each row represents a discrete piece of evidence — a medical certificate, a self-certificate, or maternity documentation — captured against a person's absence record. The table is central to SSP processing because statutory payments depend on the timely receipt and validation of qualifying evidence.

The table resides in the SSP schema and is marked VALID in the documented 12.2.2 environment, with 46 physical columns. Under a heuristic Data Vault classification mined from its foreign key structure, the object is satellite-leaning: it carries descriptive, time-variant attributes about a business transaction whose identity is anchored elsewhere. In dimensional modelling terms it is best treated as an evidence fact/satellite attached to the absence and maternity hubs, rather than as an independent entity.

Use is relevant in both Oracle EBS 12.1.1 and 12.2.2. The 12.2.2 documentation confirms the schema described below; earlier releases present the same functional intent, and any column-level differences should be verified against the specific installation.

Key Information Stored

The surrogate primary key is MEDICAL_ID, enforced by the SSP_MEDICALS_PK index. The business-key candidate is captured by the unique index SSP_MEDICALS_UK1, which spans ABSENCE_ATTENDANCE_ID, MATERNITY_ID, EVIDENCE_DATE, and EVIDENCE_SOURCE — meaning one evidence record per absence, maternity case, date, and source. The most significant columns are:

• MEDICAL_ID — surrogate primary key identifying the evidence row.
• ABSENCE_ATTENDANCE_ID — foreign key to PER_ABSENCE_ATTENDANCES, linking the evidence to the underlying sickness absence.
• MATERNITY_ID — foreign key to SSP_MATERNITIES, linking evidence to a maternity absence.
• EVIDENCE_DATE — the effective date of the evidence, part of the business key.
• EVIDENCE_SOURCE — origin of the evidence (for example, doctor, self, employer), also part of the business key.
• MEDICAL_TYPE — classification of the medical document.
• SELF_CERTIFICATE — indicates whether the record is a self-certification.
• PREDICTED_FITNESS_DATE — anticipated date the person is fit to return to work.
• EVIDENCE_STATUS — processing or validation status of the evidence.
• EVIDENCE_DETAILS — free-text or structured detail of the evidence received.
• EVIDENCE_RECEIVED_DATE — date the evidence was physically received.
• ACCEPT_LATE_EVIDENCE_FLAG and REASON_FOR_LATE_EVIDENCE — control late evidence for SSP entitlement.
• EMPLOYER_CONFIRMATION_SENT — tracks employer notification activity.
• PARTNER_NAME, PARTNER_NI_NUMBER, PARTNER_ADDRESS, PARTNER_EMPLOYER_NAME_ADDRESS, BIRTH_CERTIFICATE_RECEIVED — maternity-related partner and birth evidence attributes.
• Standard audit and DFF columns: OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and ATTRIBUTE_CATEGORY plus ATTRIBUTE1–ATTRIBUTE20.

Common Use Cases and Queries

Typical reporting scenarios include auditing outstanding evidence for an absence, reconciling SSP entitlement against evidence received dates, and identifying late or self-certified evidence.

• Retrieve all evidence for a given absence:
  SELECT * FROM ssp.ssp_medicals WHERE absence_attendance_id = :p_absence_id;
• List evidence not yet received or awaiting status update:
  SELECT medical_id, absence_attendance_id, evidence_date, evidence_status FROM ssp.ssp_medicals WHERE evidence_received_date IS NULL;
• Join to maternity cases for partner and birth certificate reporting:
  SELECT m.medical_id, mt.maternity_id, m.evidence_date, m.birth_certificate_received FROM ssp.ssp_medicals m JOIN ssp.ssp_maternities mt ON mt.maternity_id = m.maternity_id;
• Summarise self-certificate volumes by source and period for compliance reporting.

Related Objects

The following objects are the most significant dependencies for SSP_MEDICALS:

• SSP_MATERNITIES — referenced by SSP_MEDICALS.MATERNITY_ID; the maternity absence record to which evidence attaches.
• PER_ABSENCE_ATTENDANCES — referenced by SSP_MEDICALS.ABSENCE_ATTENDANCE_ID; the sickness absence attendance.
• SSP_MEDICALS_PK / SSP_MEDICALS_UK1 — the primary and unique supporting indexes.
• SSP absence and payment entities — SSP processing and pay calculation objects that consume validated evidence to determine statutory entitlement.
• PER_ALL_PEOPLE_F — the person context for absence and maternity records indirectly related through ABSENCE_ATTENDANCE_ID.

Because the object is satellite-leaning, queries should join outward to the absence or maternity parent to recover full person and period context.