Search Results gb_wnu

Overview

APPS.PAY_WNU_ASG_INFO_V is a reporting and integration view in Oracle E-Business Suite (EBS) Release 12.1.1 and 12.2.2 that exposes assignment-level "WNU" (Whole of Government / Workforce Not Utilised) extra information stored against HR assignments. In the EBS data model, "WNU" refers to the GB_WNU information type — a legislative reporting construct used primarily in UK payroll and public sector deployments to track the relationship between a current assignment and its predecessor assignment, and to flag assignments that must be excluded from WNU statutory returns.

The view presents a narrow, business-friendly projection over the ETRM base object PER_ASSIGNMENT_EXTRA_INFO. Rather than forcing report authors and integrators to filter the generic extra-information table themselves, the view pre-filters on INFORMATION_TYPE = 'GB_WNU', renames the cryptic AEI_INFORMATION1/AEI_INFORMATION2 attributes to meaningful business names (PREVIOUS_ASSIGNMENT_NUMBER and NOT_INCLUDED_IN_WNU), and re-labels the primary key as WNU_ASG_EXTRA_INFO_ID. This makes the view a stable, self-documenting interface for WNU reporting, interfaces, and extracts.

The view is defined in the APPS schema and is therefore accessible to any EBS responsibility whose data grants permit APPS object access. It is read-only by design; no DML is exposed.

Underlying Base Objects

The view is defined over a single base object, exposed through the APPS synonym PER_ASSIGNMENT_EXTRA_INFO. The ETRM metadata documents the referenced object as PER_ASSIGNMENT_EXTRA_INFO (SYNONYM), confirming a one-to-one view-to-table relationship with a WHERE-clause filter rather than a join.

PER_ASSIGNMENT_EXTRA_INFO is the HRMS assignment-level extra information table. Each row stores a flexfield-described attribute block for a given assignment and information type. Because assignment extra information is legislated and extensible, the table uses the standard AEI_INFORMATION1 through AEI_INFORMATIONn columns, a DFF context column (AEI_INFORMATION_CATEGORY), and the standard OBJECT_VERSION_NUMBER for optimistic locking consistency.

The 1:0..n relationship direction is significant: one HR assignment (PER_ALL_ASSIGNMENTS_F) may carry at most one GB_WNU detail row, since the combination of assignment and information type is typically unique for this information type. The view does not join to PER_ALL_ASSIGNMENTS_F, so consumers must join assignment context (person, payroll, effective dates) themselves.

Key Columns

• WNU_ASG_EXTRA_INFO_ID — Surrogate primary key of the underlying assignment extra information row. Use for precise row identification and de-duplication.
• ASSIGNMENT_ID — Foreign key to the assignment (PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID). This is the business join key for person, payroll, and assignment-level reporting.
• INFORMATION_TYPE — Always the constant 'GB_WNU' in this view. Retained so consumers can carry the type through extracts without ambiguity.
• AEI_INFORMATION_CATEGORY — The descriptive flexfield context code for the extra information block (the DFF context under which the GB_WNU attributes are defined).
• WNU_OBJECT_VERSION_NUM — Renamed OBJECT_VERSION_NUMBER, used for optimistic locking integrity checks in integrations that post back to HRMS.
• PREVIOUS_ASSIGNMENT_NUMBER — Mapped from AEI_INFORMATION1. Holds the assignment number of the predecessor assignment, establishing the WNU continuity chain across assignment changes.
• NOT_INCLUDED_IN_WNU — Mapped from AEI_INFORMATION2. A flag indicating this assignment is excluded from the WNU return (for example, where an assignment is out of scope for statutory reporting).

Common Use Cases and Queries

Typical usage centres on statutory reporting, audit reconciliation, and HR data migration. A payroll or MI developer lists all WNU flags for an assignment population:

• Reconciling WNU continuity: join the view to PER_ALL_ASSIGNMENTS_F to retrieve assignment number, effective dates, and payroll, then compare PREVIOUS_ASSIGNMENT_NUMBER against prior periods to validate the assignment chain.
• Exclusion reporting: select assignments where NOT_INCLUDED_IN_WNU is populated, to evidence why they were omitted from the WNU return.
• Integration and migration: extract the GB_WNU rows into a staging table, carrying WNU_ASG_EXTRA_INFO_ID and WNU_OBJECT_VERSION_NUM so that any update respects HRMS version-number integrity.

Sample SQL:

SELECT v.assignment_id,
       paaf.assignment_number,
       v.previous_assignment_number,
       v.not_included_in_wnu,
       v.aei_information_category
FROM   apps.pay_wnu_asg_info_v v,
       apps.per_all_assignments_f paaf
WHERE  paaf.assignment_id = v.assignment_id
AND    SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
ORDER BY paaf.assignment_number;

For exclusion lists only:

SELECT assignment_id, not_included_in_wnu
FROM   apps.pay_wnu_asg_info_v
WHERE  not_included_in_wnu IS NOT NULL;

Because the view reads directly from PER_ASSIGNMENT_EXTRA_INFO, no concurrent programme or index is introduced — performance depends on the underlying table's indexes on ASSIGNMENT_ID and INFORMATION_TYPE. Report authors should always filter on ASSIGNMENT_ID or join on assignment for efficient execution.