Results for “adoptive_childs_birth_date”

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

Overview

SSP_MATERNITIES is a transactional table in the SSP (Statutory Sick Pay / Statutory Maternity Pay) product module of Oracle E-Business Suite, owned by the SSP schema. It maintains the authoritative record of an employee's maternities and pregnancies as they relate to statutory leave and pay entitlements. The table is instantiated in both EBS 12.1.1 and 12.2.2 and is documented as VALID in the ETRM repository. Its functional purpose is to capture the full lifecycle of a maternity event — from the expected due date and notification of birth through to statutory pay start dates, return-to-work intentions, and, where relevant, adoption or partner-related events.

Within a Data Vault modeling view, the metadata's heuristic classification places this table as hub-leaning. This suggests SSP_MATERNITIES behaves as a durable business entity keyed by a stable identifier (MATERNITY_ID), with the surrounding SSP_MEDICALS and SSP_STOPPAGES tables operating as satellite-like dependents that carry event or detail context. This classification is a modeling suggestion inferred from the foreign-key topology rather than a documented architectural designation.

Key Information Stored

The documented physical schema for 12.2.2 contains 80 columns. The surrogate primary key is MATERNITY_ID, enforced by the SSP_MATERNITIES_PK index. A composite unique index, SSP_MATERNITIES_UK1, covers DUE_DATE, PERSON_ID, and LEAVE_TYPE, making this triplet the business-key candidate that uniquely identifies a maternity record in human terms.

The most significant columns include:

• PERSON_ID — the party or employee the maternity record belongs to.
• DUE_DATE — the expected date of birth, a anchor for statutory entitlement calculations.
• LEAVE_TYPE — distinguishes maternity from adoption or partner-related leave.
• ACTUAL_BIRTH_DATE and LIVE_BIRTH_FLAG — outcome data for the birth.
• NOTIFICATION_OF_BIRTH_DATE — the date the birth was formally notified.
• START_DATE_MATERNITY_ALLOWANCE and SMP_MUST_BE_PAID_BY_DATE — statutory pay scheduling fields.
• PAY_SMP_AS_LUMP_SUM — payment method indicator.
• INTEND_TO_RETURN_FLAG and STATED_RETURN_DATE — return-to-work planning.
• UNFIT_FOR_SCHEDULED_RETURN — exception tracking.
• START_DATE_WITH_NEW_EMPLOYER — re-employment information.
• MPP_START_DATE, PARTNER_STAT_PAY_START_DATE, PARTNER_RETURN_TO_WORK, and PARTNER_DEATH_DATE — partner-related statutory events.
• ADOPTIVE_CHILDS_BIRTH_DATE, MATCHING_DATE, PLACEMENT_DATE, and DISRUPTED_PLACEMENT_DATE — adoption lifecycle markers.
• OBJECT_VERSION_NUMBER — optimistic locking control.

Thirty generic MAT_INFORMATION columns and twenty ATTRIBUTE columns provide DDF-style descriptive flexfield capacity, while the standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) provide auditability.

Common Use Cases and Queries

Typical use cases include identifying employees approaching statutory maternity pay deadlines, reporting on expected versus actual birth dates, and reconciling return-to-work intentions. A representative query retrieves active maternity records for a given person:

• SELECT maternity_id, person_id, due_date, actual_birth_date, leave_type FROM ssp.ssp_maternities WHERE person_id = :p_person_id;
• SELECT maternity_id, due_date, smp_must_be_paid_by_date FROM ssp.ssp_maternities WHERE actual_birth_date IS NULL AND smp_must_be_paid_by_date > SYSDATE ORDER BY smp_must_be_paid_by_date;
• SELECT m.maternity_id, m.due_date, s.medical_id FROM ssp.ssp_maternities m JOIN ssp.ssp_medicals s ON s.maternity_id = m.maternity_id;

These patterns support statutory reporting, absence analysis, and integration with absence management processes through PER_ABSENCE_ATTENDANCES.

Related Objects

The documented relationships link SSP_MATERNITIES to several dependent tables:

• SSP_MEDICALS — references SSP_MATERNITIES via SSP_MEDICALS.MATERNITY_ID; holds medical certification associated with a maternity.
• SSP_STOPPAGES — references SSP_MATERNITIES via SSP_STOPPAGES.MATERNITY_ID; records stoppages affecting maternity pay.
• SSP_TEMP_AFFECTED_ROWS — references SSP_MATERNITIES via MATERNITY_ID; a transient processing table.
• PER_ABSENCE_ATTENDANCES — references SSP_MATERNITIES via MATERNITY_ID, connecting statutory maternity records to core HR absence tracking.
• SSP_EMPLOYEES and related SSP setup tables — provide the employer/employee statutory context through PERSON_ID.

Together these objects form the statutory maternity processing chain within the SSP module, with SSP_MATERNITIES acting as the hub table.