Search Results pafv_expenditure_groups




Overview
Explore in AI Chat

Overview

PAFV_EXPENDITURE_GROUPS is an APPS-owned, read-only business view in the Oracle E-Business Suite Projects (PA) module. It presents information about expenditure batches entered into Oracle Projects, exposing the attributes that describe each batch: its identifier, status, description, transaction source, organization, and the control totals used to reconcile the batch against the interface data it carries. The view is defined with the WITH READ ONLY clause, so it is intended exclusively for reporting, inquiry, and integration extraction rather than for data maintenance. It is valid in both Oracle EBS 12.1.1 and 12.2.2, and its definition is documented in the ETRM under object type VIEW with owner APPS. Functionally, the view denormalizes the parent expenditure group records and decorates them with human-readable meanings, so that reports do not need to join to lookup and translation tables themselves. This makes it convenient for operational reports that monitor batch entry, unprocessed and partially processed expenditure, and the amounts posted through the transaction interface.

Underlying Base Objects

The view is defined over four base objects, all referenced via APPS synonyms. PA_EXPENDITURE_GROUPS_ALL (aliased PEG) supplies the core expenditure batch rows, including the group identifier, description, ending date, transaction source, system linkage function, organization, and audit columns. PA_SYSTEM_LINKAGES (PSL) is joined on the system linkage function to resolve the meaning of the linkage used by the batch. PA_TRANSACTION_SOURCES (PTS) is outer-joined on TRANSACTION_SOURCE to supply the transaction source description. HR_ALL_ORGANIZATION_UNITS_TL (TL) is joined on ORG_ID and filtered to the session language via USERENV('LANG'), providing the translated organization name. The definition also embeds two value-set style tokens: one deriving a status meaning from PA_LOOKUPS and one enforcing security on ORG_ID through _SEC:PEG.ORG_ID. Because the view is read-only and exposes the organization column, it can participate in Oracle Projects' organization-level security model when queried from within the application.

Key Columns

  • EXPENDITURE_GROUP — The unique identifier of the expenditure batch.
  • DESCRIPTION — The batch's descriptive text as entered by the user.
  • SYSTEM_LINKAGE_FUNCTION — The translated meaning of the system linkage associated with the batch.
  • EXPENDITURE_ENDING_DATE — The accounting/reporting end date associated with the batch.
  • CONTROL_COUNT — The batch's expected (control) number of interface records; used for reconciliation.
  • CONTROL_TOTAL_AMOUNT — The batch's expected (control) total amount. This is the column users most often target, since it enables comparison of the amount recorded on the batch header against the sum of the transactional detail being interfaced or processed.
  • TRANSACTION_SOURCE — The identifier of the source that created the batch.
  • TRANSACTION_SOURCE_DESCRIPTION — The descriptive name of that source.
  • ORG_ID / ORGANIZATION_NAME — The operating unit/organization key and its translated name.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — Standard audit columns.

Common Use Cases and Queries

The view is commonly used to report on batch entry and status, to identify batches by source or ending date, and to reconcile control values against actual processed detail. Because the status and organization name are pre-resolved, reports can filter and group without additional joins.

Listing batches with their control totals by source and ending date:

  • SELECT expenditure_group, description, system_linkage_function, expenditure_ending_date, control_count, control_total_amount, transaction_source_description, organization_name FROM apps.pafv_expenditure_groups WHERE expenditure_ending_date >= :from_date ORDER BY expenditure_ending_date, expenditure_group;

Aggregating control totals for reconciliation:

  • SELECT transaction_source_description, COUNT(*) group_count, SUM(control_count) total_records, SUM(control_total_amount) total_amount FROM apps.pafv_expenditure_groups GROUP BY transaction_source_description;

Filtering a single batch of interest:

  • SELECT expenditure_group, system_linkage_function, control_count, control_total_amount FROM apps.pafv_expenditure_groups WHERE expenditure_group = :batch_name;