Search Results current_npw_flag




Overview
Explore in AI Chat

Overview

APPS.PA_EXPENDITURES_V is a reporting and integration view in Oracle E-Business Suite (12.1.1 and 12.2.2) that presents the contents of the PA_EXPENDITURES table enriched with descriptive attributes for the person who incurred each expenditure. It is owned by the APPS schema and is intended primarily for operational reporting, extract routines, and downstream integration where a human-readable employee name and employee number are required alongside the raw expenditure record.

The view is especially notable for the way it resolves the identity of the incurring party. Because expenditures can be incurred by either a regular employee or a non-payroll worker (NPW), the view introduces conditional logic so that the correct identifier is returned depending on whether the person is flagged as an employee or as a non-payroll worker.

Underlying Base Objects

The documented referenced base objects for the view are:

  • PA_EXPENDITURES (synonym) — the primary expenditure table, aliased as E, supplying all expenditure columns.
  • PER_ALL_PEOPLE_F (synonym) — the people table, aliased as p, supplying the person's full name, employee number, and the current employee / non-payroll worker flags.
  • PA_EXPENDITURES_UTILS (package) — the utility package, invoked as pa_expenditures_utils.getorgtlname, used to derive the organization name from the incurred-by organization identifier.

Key Columns

The view exposes the full set of expenditure columns, including EXPENDITURE_ID, EXPENDITURE_STATUS_CODE, EXPENDITURE_ENDING_DATE, EXPENDITURE_CLASS_CODE, INCURRED_BY_PERSON_ID, INCURRED_BY_ORGANIZATION_ID, EXPENDITURE_GROUP, CONTROL_TOTAL_AMOUNT, ENTERED_BY_PERSON_ID, DESCRIPTION, INITIAL_SUBMISSION_DATE, the functional and accounting currency attributes (denom_currency_code, acct_currency_code, acct_rate_date, acct_rate_type, acct_exchange_rate), audit columns, and the ten descriptive flexfield attribute columns. Several derived columns are added:

  • INCURRED_BY_EMPLOYEE_NAME — the person's full name from PER_ALL_PEOPLE_F.
  • INCURRED_BY_EMPLOYEE_NUMBER — a decode that returns the employee_number when current_employee_flag is 'Y', otherwise the npw_number when current_npw_flag is 'Y', otherwise NULL.
  • INcurred_by_organization_id name — resolved via pa_expenditures_utils.getorgtlname.

The join uses an outer join to PER_ALL_PEOPLE_F on person_id, with row filtering that selects the most recent effective row for the person where the relevant flag (current_employee_flag or current_npw_flag, selected via person_type = 'CWK') equals 'Y' and the effective date range brackets the expenditure ending date.

Common Use Cases and Queries

Typical use cases include expenditure extracts for interfaces, audit reports identifying the incurring worker, and reporting that must display a friendly name and identifier rather than raw IDs.

SELECT expenditure_id,
       expenditure_ending_date,
       incurred_by_employee_name,
       incurred_by_employee_number,
       control_total_amount
FROM   apps.pa_expenditures_v
WHERE  expenditure_status_code = 'APPROVED';

Because the view performs a correlated subquery against PER_ALL_PEOPLE_F for effective-dated row resolution, performance can degrade on large volumes; filtering by expenditure_ending_date or expenditure_id is advisable. The presence of current_npw_flag logic means the view correctly handles conting