Search Results pa_ei_denorm_u2




Overview
Explore in AI Chat

Overview

PA.PA_EI_DENORM is a denormalized projection of the PA_EXPENDITURE_ITEMS_ALL table within the Oracle E-Business Suite Project Accounting (PA) schema. Its principal consumer is the Self-Service Time module, where the flattened structure supports matrix-style time entry — the spreadsheet-like grid in which users enter time and quantities across multiple project, task, and expenditure type combinations in a single page. Rather than joining a normalized set of expenditure rows at runtime, the application reads and writes the denormalized form maintained here, trading storage redundancy for interactive performance in the web UI.

The object is classified as VALID in the ETRM registry and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. The table is registered under FND Design Data as PA.PA_EI_DENORM. From a data modeling perspective, the metadata's heuristic Data Vault classification is standalone: the table references no parent entities through foreign keys, which is consistent with a denormalized reporting or staging structure rather than a conformed hub or link. Analysts modeling this object in a warehouse context should treat it as a candidate for a satellite-style structure, since each row represents a versioned snapshot of expenditure lines keyed by a system-generated identifier.

Key Information Stored

The table is physically wide, with 200 documented columns in the ETRM 12.2.2 schema. The columns of greatest operational significance are:

  • EXPENDITURE_ID — system-generated number that uniquely identifies the expenditure item; the anchor to PA_EXPENDITURE_ITEMS_ALL.
  • DENORM_ID — system-generated unique identifier for the denormalized row itself.
  • PERSON_ID — employee who incurred the charges. Always populated for labor and expense report charges; null for supplier invoices; optional for usages.
  • PROJECT_ID — project against which the expenditure is charged (and of the FIFO-marking event).
  • TASK_ID — task to which the expenditure item is charged.
  • EXPENDITURE_TYPE — classification of the type of work performed.
  • DEFAULT_SYS_LINK_FUNC — system linkage function that drives system processing for the item.
  • UNIT_OF_MEASURE_CODE / UNIT_OF_MEASURE — code and description of the unit used for the expenditure type.
  • DENORM_TOTAL_QTY — aggregated quantity across the denormalized line groups.
  • DENORM_TOTAL_AMOUNT — aggregated monetary amount across the denormalized line groups.
  • ADJUSTED_DENORM_ID — linkage to an adjusting denormalized row.
  • Seven repeating column groups (_1 through _7) covering EXPENDITURE_ITEM_ID_n, EXPENDITURE_ITEM_DATE_n, QUANTITY_n, RAW_COST_n, BILLABLE_FLAG_n, JOB_ID_n, and related attributes — this is the matrix structure that allows a single row to hold up to seven time-entry lines.

Two unique indexes exist: PA_EI_DENORM_U1 on (EXPENDITURE_ID, DENORM_ID) and PA_EI_DENORM_U2 on (DENORM_ID). The documented primary key PA_EI_DENORM_PK is (EXPENDITURE_ID, DENORM_ID), making the composite of those two columns the surrogate identifier, while PA_EI_DENORM_U2 (DENORM_ID alone) is the alternate unique business-key candidate. Two non-unique indexes, PA_EI_DENORM_N1 on (PROJECT_ID, TASK_ID) and PA_EI_DENORM_N2 on (TASK_ID), support project/task-oriented lookups.

Common Use Cases and Queries

The most common access patterns align with the indexed access paths. Matrix time-entry screens read rows by person and project/task; downstream reporting joins back to the normalized expenditure item to reconcile totals. Representative queries include:

  • Retrieve all denormalized rows for a project and task: SELECT * FROM pa.pa_ei_denorm WHERE project_id = :p AND task_id = :t; — exploits PA_EI_DENORM_N1.
  • Fetch a specific row by its surrogate identifier: SELECT * FROM pa.pa_ei_denorm WHERE denorm_id = :d; — exploits PA_EI_DENORM_U2.
  • Reconcile denormalized aggregates against PA_EXPENDITURE_ITEMS_ALL: join on expenditure_id and compare denorm_total_amount to the sum of raw_cost from the base table.
  • Audit matrix rows for a person over a date range using the repeating EXPENDITURE_ITEM_DATE_n columns.
  • Identify orphaned or read-only rows for a given task using the TASK_ID non-unique index.

Reports such as Self-Service Time entry audits, pre-approved time batch validation, and cost distribution verification are typical consumers. Because the table is a denormalized cache, direct DML outside of supported oracle Project Accounting APIs is not advisable; write operations are managed by the responsibility that drives Self-Service Time and flows through to PA_EXPENDITURE_ITEMS_ALL.

Related Objects

  • PA.PA_EXPENDITURE_ITEMS_ALL — the normalized source table of which PA_EI_DENORM is a denormalized representation; join on EXPENDITURE_ID. Column semantics of the denormalized table mirror this object.
  • PA.PA_EXPENDITURE_ITEMS — the primary key view alias over PA_EXPENDITURE_ITEMS_ALL; contains the master expenditure item rows referenced by EXPENDITURE_ID.
  • PA.PA_PROJECTS_ALL — join on PROJECT_ID for project attributes.
  • PA.PA_TASKS — join on TASK_ID for task attributes.
  • PER.PER_ALL_PEOPLE_F — join on PERSON_ID for employee attribution.
  • PA.PA_EXPENDITURE_TYPES — join on EXPENDITURE_TYPE for classification and unit-of-measure defaults.
  • PA.PA_LOOKUPS / PA.PA_UNITS_OF_MEASURE — for UNIT_OF_MEASURE_CODE resolution.
  • Oracle Self-Service Time (PA) module — the principal application consumer that reads and writes this table to drive matrix time entry.