Results for “employer_reference_number”

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

Overview

PQP_GB_PENSION_SCHEMES_V is a dictionary view owned by the APPS schema within the PQP – Public Sector Payroll product family. It is defined as a view of all base elements created by the pension schemes element template, giving report developers and integrators a denormalized, single-row-per-scheme representation of the pension configuration data that is otherwise scattered across element type extra information, element definitions, balance types, organizations, and pension type definitions. Rather than requiring consumers to decode the generic EEI_INFORMATION1 through EEI_INFORMATION19 columns of PAY_ELEMENT_TYPE_EXTRA_INFO, the view projects these generic attribute slots into business-meaningful column aliases such as PENSION_SCHEME_NAME, PENSION_CATEGORY, SCON, and ECON.

The view is validated and available in both Oracle EBS 12.1.1 and 12.2.2. Its principal role is as a reporting and integration surface: it supports the generation of statutory pension returns, scheme enrollment reporting, and interfaces that must reconcile payroll element configurations against pension provider and pension type master data. Because it reads from effective-dated tables (PAY_ELEMENT_TYPES_F, PQP_PENSION_TYPES_F), consumers must be aware that the view does not itself filter by effective date; date-range predicates are the responsibility of the calling query.

Underlying Base Objects

The view is defined over the following documented base objects (all accessed through APPS synonyms unless noted): PAY_ELEMENT_TYPE_EXTRA_INFO (the driving extra-information rows carrying the PQP_GB_PENSION_SCHEME_INFO information type), PAY_ELEMENT_TYPES_F (base element definition, including element name, reporting name, classification, legislation, and effective dates), PAY_ELEMENT_CLASSIFICATIONS, PAY_BALANCE_TYPES (pensionable salary balance), HR_ALL_ORGANIZATION_UNITS (pension provider), and PQP_PENSION_TYPES_F (pension type identifier and name). Two PL/SQL packages are also referenced: FND_DATE, used via CANONICAL_TO_DATE to convert the stored canonical date value in EEI_INFORMATION5 into an actual DATE, and FND_NUMBER, used via CANONICAL_TO_NUMBER to convert EEI_INFORMATION16 into the associated OCP element type ID. A scalar subquery against PAY_ELEMENT_TYPE_EXTRA_INFO (aliased EEIT2) retrieves the parent scheme name for the associated OCP element type where EEI_INFORMATION12 is null, establishing the internal scheme-to-element hierarchy.

Key Columns

• PENSION_SCHEME_NAME – EEI_INFORMATION1; the human-readable scheme identifier.
• PENSION_CATEGORY – EEI_INFORMATION4; the category classification of the scheme.
• PENSION_YEAR_START_DT – canonical date converted from EEI_INFORMATION5.
• PENSION_PROVIDER_ID / PENSION_PROVIDER_NAME – organization ID and name of the pension provider.
• PENSION_TYPE_ID / PENSION_TYPE_NAME – from PQP_PENSION_TYPES_F.
• EMPLOYEE_DEDUCTION_METHOD, EMPLOYER_DEDUCTION_METHOD, EMPLOYER_COMPONENT – deduction mechanics, with the indicator decoded to Y/N from EEI_INFORMATION7.
• SCON / ECON – scheme and employer contracting-out numbers (EEI_INFORMATION9 and EEI_INFORMATION19).
• SCHEME_REFERENCE_NUMBER / EMPLOYER_REFERENCE_NUMBER – statutory scheme and employer references.
• ASSOCIATED_OCP_ELE_TYPE_ID – numeric form of EEI_INFORMATION16, linking to the associated OCP element.
• ADDITIONAL_CONTRIBUTION, ADDED_YEARS, FAMILY_WIDOWER – scheme options held in EEI_INFORMATION13, 14, and 15.
• SCHEME_PREFIX, PENSION_SCHEME_TYPE – EEI_INFORMATION18 and EEI_INFORMATION8.
• PENSIONABLE_SALARY_BALANCE_ID / PENSIONABLE_SALARY_BALANCE – from PAY_BALANCE_TYPES.
• ELEMENT_TYPE_ID, ELEMENT_NAME, REPORTING_NAME, CLASSIFICATION_NAME, BUSINESS_GROUP_ID, LEGISLATION_CODE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE – core element definition attributes from PAY_ELEMENT_TYPES_F and PAY_ELEMENT_CLASSIFICATIONS.
• THIRD_PARTY_PAYMENT – decoded Y/N from the third-party-pay-only flag.

Common Use Cases and Queries

Typical uses include auditing pension scheme setups for a legislative region, driving pension return extracts, and validating that each scheme carries the required SCON, ECON, and reference numbers before a payroll run. A representative query listing scheme and provider detail for a business group is:

SELECT pension_scheme_name,
       pension_category,
       pension_provider_name,
       pension_type_name,
       scon,
       econ,
       pensionable_salary_balance
  FROM   apps.pqp_gb_pension_schemes_v
 WHERE   business_group_id = :p_business_group_id
   AND   legislation_code = 'GB'
   AND   effective_start_date <= :p_date
   AND   (effective_end_date IS NULL OR effective_end_date >= :p_date)
 ORDER BY pension_scheme_name;

To identify schemes missing employer components, a filter on EMPLOYER_COMPONENT = 'N' isolates those without an employer-side deduction method. Because the view exposes both the associated OCP element type ID and the scheme name subquery, it also supports recursive-style reporting that maps an associated OCP element back to its parent scheme. Note that the underlying table names include the _F suffix on effective-dated entities, so any additional joins outside the view must apply date-tracked predicates to avoid duplicate rows.