Results for “ssp_ssprpsmp_v”

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

Overview

The view APPS.SSP_SSPRPSMP_V is a reporting object within the Oracle E-Business Suite statutory sick pay (SSP) module. It is owned by the APPS schema and holds a VALID status. Its documented purpose is to present the details of a Maternity Pay Period (MPP), including a set of derived dates, for use in Statutory Maternity Pay (SMP) reports. The object name is commonly associated with the search term pay_smp_as_lump_sum, because it exposes the PAY_SMP_AS_LUMP_SUM column that determines whether SMP is processed as a lump sum.

The view bridges the maternity record and the SMP entry detail by joining maternity data to a pre-aggregated entries view. It provides both the raw period dates (due date, MPP start, MPP end, actual birth date) and derived calendar values (Expected Week of Confinement and Qualifying Week). As a read-only reporting view, it is not intended for transactional data entry; it exists to support SMP reporting, validation, and downstream data extraction queries executed against the APPS schema.

Underlying Base Objects

The view text selects from two principal sources and invokes several supporting packages. The documented referenced base objects are:

• SSP_MATERNITIES (SYNONYM) — the primary maternity record, supplying MATERNITY_ID, DUE_DATE, MPP_START_DATE, ACTUAL_BIRTH_DATE, and PAY_SMP_AS_LUMP_SUM.
• SSP_SMP_ENTRIES_V (VIEW) — the SMP entries view, supplying DATE_FROM, which is used to derive the MPP end date.
• SSP_SMP_PKG (PACKAGE) — provides the functions EXPECTED_WEEK_OF_CONFINEMENT and QUALIFYING_WEEK, both called with MAT.DUE_DATE to produce derived values.
• HR_GENERAL, HR_SECURITY, FND_DATE, and FND_NUMBER (PACKAGES) — supporting utilities referenced within the view definition, typically for date/number handling and security filtering.

The two data sources are joined on MATERNITY_ID and grouped by the maternity attributes plus the two package-derived functions. The MPP end date is computed as NEXT_DAY(MAX(ENT.DATE_FROM), 'SATURDAY'), meaning the last pay period is extended to the Saturday following the latest SMP entry start date.

Key Columns

• MATERNITY_ID — Primary identifier linking the view to the maternity record.
• DUE_DATE — The expected date of confinement.
• MPP_START_DATE — Maternity Pay Period start date.
• MPP_END_DATE — Derived pay period end, calculated as the Saturday following the maximum SMP entry DATE_FROM.
• EWC — Expected Week of Confinement, derived via SSP_SMP_PKG.EXPECTED_WEEK_OF_CONFINEMENT(DUE_DATE).
• QW — Qualifying Week, derived via SSP_SMP_PKG.QUALIFYING_WEEK(DUE_DATE).
• ACTUAL_BIRTH_DATE — The actual date of birth recorded on the maternity record.
• PAY_SMP_AS_LUMP_SUM — Flag indicating whether SMP is paid as a lump sum rather than as a regular weekly amount.

Common Use Cases and Queries

The view is typically queried for SMP reporting, reconciling pay periods, and confirming dates and lump-sum treatment. A representative query follows:

• List maternity pay periods with derived dates:

  SELECT maternity_id, due_date, mpp_start_date,
         mpp_end_date, ewc, qw, actual_birth_date
    FROM apps.ssp_ssprpsmp_v
   WHERE mpp_start_date BETWEEN :p_from AND :p_to;

• Identify lump-sum SMP cases:

  SELECT maternity_id, due_date, actual_birth_date,
         mpp_start_date, mpp_end_date
    FROM apps.ssp_ssprpsmp_v
   WHERE pay_smp_as_lump_sum = 'Y';

• SMP report extract by qualifying week:

  SELECT maternity_id, due_date, ewc, qw
    FROM apps.ssp_ssprpsmp_v
   WHERE qw BETWEEN :p_qw_from AND :p_qw_to;

Because the view joins to SSP_SMP_ENTRIES_V and invokes HR security and package functions, queries should be run under an APPS responsibility with appropriate HR security access. Filtering on indexed maternity identifiers or date ranges improves performance for reporting extracts.