Results for “live_birth_flag”

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

Overview

SSP_SSPWSMAT_V is a database view owned by the APPS schema within the SSP (Statutory Sick Pay / Statutory Maternity Pay) product of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. It is documented as the base view for the SSPWSMAT form maternities block, meaning it provides the queryable data layer behind the maternity record management form within the SSP module. The view is a direct projection over the SSP_MATERNITIES table, presenting one row per maternity record while resolving a number of derived statutory dates at runtime. Because it exposes a stable, denormalised column set that already includes computed statutory week and date values, it functions both as the form's data source and as a convenient reporting and integration surface for statutory maternity data. The presence of WHO-style audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER confirms it is a standard Oracle EBS view suitable for concurrent program extracts, Discoverer/BI Publisher reports, and inbound/outbound interface development.

Underlying Base Objects

The documented referenced base objects for this view are SSP_MATERNITIES (accessed through a synonym), SSP_SMP_PKG, SSP_APAB_PKG, SSP_APAD_PKG, SSP_PAB_PKG, SSP_PAD_PKG, and SSP_SAP_PKG. The view text selects exclusively from SSP_MATERNITIES, aliased as MAT. The additional package references arise because several columns are not stored on the table but are computed through SSP_SMP_PKG functions: EXPECTED_WEEK_OF_CONFINEMENT, EARLIEST_MPP_START_DATE, QUALIFYING_WEEK, and CONTINUOUS_EMPLOYMENT_DATE, each evaluated against DUE_DATE. The remaining package references reflect the broader statutory payment and absence frameworks with which the maternity record interacts. From a dependency perspective, SSP_MATERNITIES is the single physical source, and the packages are the derivation logic, so any change to SSP_SMP_PKG calculation rules is immediately visible through this view.

Key Columns

The view exposes the full maternity record. Principal columns include:

• MATERNITY_ID / PERSON_ID — primary key of the maternity record and the associated person.
• DUE_DATE — the expected date of confinement, which drives all statutory derivations.
• MPP_START_DATE — the maternity pay period start date as held on the record.
• EWC, EARLIEST_MPP_START, QW, CONT_EMP_START — derived values returning the expected week of confinement, earliest permissible MPP start, qualifying week, and continuous employment date.
• PAY_SMP_AS_LUMP_SUM, SMP_MUST_BE_PAID_BY_DATE — lump-sum payment indicator and the statutory payment deadline.
• START_DATE_WITH_NEW_EMPLOYER — the date the employee began work with a new employer, relevant to continuity of employment and SMP eligibility determinations.
• START_DATE_MATERNITY_ALLOWANCE — the commencement date for Maternity Allowance purposes.
• NOTIFICATION_OF_BIRTH_DATE, LIVE_BIRTH_FLAG, ACTUAL_BIRTH_DATE — birth notification and outcome details.
• STATED_RETURN_DATE, INTEND_TO_RETURN_FLAG, UNFIT_FOR_SCHEDULED_RETURN — return-to-work intention and fitness data.
• ATTRIBUTE_CATEGORY through ATTRIBUTE20 — descriptive flexfield context and segments.

Common Use Cases and Queries

Typical uses include auditing maternity records for a person, reconciling derived statutory dates against stored values, and extracting maternity data for payroll or statutory reporting. A basic query is:

• SELECT maternity_id, person_id, due_date, start_date_with_new_employer, start_date_maternity_allowance FROM apps.ssp_sspwsmat_v WHERE person_id = :p_person_id;
• SELECT person_id, due_date, ewc, qw, cont_emp_start, earliest_mpp_start FROM apps.ssp_sspwsmat_v WHERE due_date BETWEEN :p_from AND :p_to ORDER BY due_date;
• SELECT maternity_id, pay_smp_as_lump_sum, smp_must_be_paid_by_date FROM apps.ssp_sspwsmat_v WHERE pay_smp_as_lump_sum = 'Y';

Because the derived columns invoke SSP_SMP_PKG functions per row, queries over large date ranges can be costly; filtering on PERSON_ID or DUE_DATE is recommended. Joining to PER_ALL_PEOPLE_F on PERSON_ID is common to enrich results with employee details.