Results for “pa_proj_expend_view”

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

Overview

PA_PROJ_EXPEND_VIEW is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the Projects (PA) product family. It presents a consolidated, denormalized read of project expenditure items across both cost and revenue, allowing report authors and integrators to query a single object rather than joining the underlying transactional tables. The view surfaces labor, expense report, and usage expenditure lines together with their associated project, task, person, vendor, organization, job, billing, and distribution attributes. It is marked as VALID and exists across the 12.1.1 and 12.2.2 releases. Because it resolves foreign keys into descriptive names and applies lookup and currency logic at query time, it is commonly used in custom reports, concurrent program extracts, and inbound/outbound interfaces requiring human-readable expenditure detail.

Underlying Base Objects

The view is defined over a set of core PA and shared HR/AP objects. The principal source is PA_EXPENDITURE_ITEMS_ALL (synonym), which supplies the expenditure item grain and columns such as EXPENDITURE_ITEM_ID, QUANTITY, RAW_COST, BURDEN_COST, and bill/revenue amounts. The PA_EXPENDITURES_ALL synonym and PA_COST_DIST_LINES_ALL_B are referenced for expenditure and cost distribution attributes, including distribution flags and SYSTEM_REFERENCE columns. Project and task context comes from PA_PROJECTS and PA_TASKS. Expenditure classification is joined through PA_EXPENDITURE_TYPES, while status meanings are resolved from PA_LOOKUPS. Personnel data is drawn from PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, and PER_JOBS, and supplier data from the PO_VENDORS view. Two PL/SQL packages, PA_EXPENDITURES_UTILS and PA_UTILS4, are invoked within the view text to translate organization names and unit-of-measure descriptions, meaning the view carries function-call overhead and should be filtered before retrieval.

Key Columns

• EXPENDITURE_ITEM_ID — Unique identifier for the expenditure item; the primary join key back to PA_EXPENDITURE_ITEMS_ALL.
• PROJECT_ID, SEGMENT1, NAME — Internal ID, project number, and project name for the owning project.
• INCURRED_BY_PERSON_ID, FULL_NAME, EMPLOYEE_NUMBER / NPW_NUMBER — Party who incurred the cost, with employee number falling back to NPW number for non-employees.
• VENDOR_ID, VENDOR_NAME, SEGMENT1 — Supplier details populated for non-labor, vendor-sourced expenditure.
• INCURRED_BY_ORGANIZATION_ID, OVERRIDE_TO_ORGANIZATION_ID — Owning and override organizations, resolved to names via PA_EXPENDITURES_UTILS.GETORGTLNAME.
• ASSIGNMENT, JOB, BILL_JOB_ID, BILL_JOB_BILLING_TITLE — Assignment and billing job information derived from PER_ALL_ASSIGNMENTS_F, PER_JOBS, and override fields.
• EXPENDITURE_TYPE, EXPENDITURE_CATEGORY — Classification of the expenditure line and its parent category.
• TASK_ID, TASK_NUMBER, TASK_NAME — Task-level attribution where the expenditure is task-charged.
• EXPENDITURE_ITEM_DATE, QUANTITY, UNIT_OF_MEASURE — Date and quantity context for the line.
• BILL_RATE, BILL_AMOUNT, RAW_COST, BURDEN_COST — Billing and cost figures at raw and burdened levels.
• ADJUSTED_REVENUE / RAW_REVENUE, ACCRUED_REVENUE — Revenue amounts, with adjusted revenue preferred over raw revenue.
• BILLABLE_FLAG, BILL_HOLD_FLAG, COST_DISTRIBUTED_FLAG, REVENUE_DISTRIBUTED_FLAG — Status flags controlling billing eligibility and distribution completion.
• NON_LABOR_RESOURCE, ORGANIZATION_ID, SYSTEM_LINKAGE_FUNCTION — Non-labor resource identity and the source system linkage (USG, VI, PJ, WIP, INV, BTC).
• DENOM_CURRENCY_CODE, DENOM_RAW_COST, DENOM_BURDENED_COST — Denominating currency and converted cost amounts.

Common Use Cases and Queries

A frequent requirement is extracting billable, undistributed expenditure for a project. Note that filters should be applied on the base columns and the view queried narrowly, since the embedded PL/SQL functions evaluate per row:

• Project cost and revenue analysis by expenditure category and task.
• Unbilled or on-hold expenditure review prior to invoice generation.
• Vendor versus employee (labor/non-labor) spend breakdowns.
• Organization-level cross-charging and override reporting.
• Interface extracts where descriptive names, rather than IDs, are required.

A representative query is:

SELECT expenditure_item_id, segment1, name, expenditure_type,
  expenditure_item_date, raw_cost, burden_cost, bill_amount,
  adjusted_revenue, billable_flag, task_number
FROM apps.pa_proj_expend_view
WHERE project_id = :p_project_id
  AND billable_flag = 'Y'
  AND expenditure_item_date BETWEEN :p_from AND :p_to;

Because the view exposes the labor, expense, and usage lines in a single structure, it remains a practical foundation for custom Projects reporting in both 12.1.1 and 12.2.2, provided query predicates are applied early to constrain the function-driven column derivation.