Search Results pa_ei_denorm




Overview
Explore in AI Chat

Overview

PA_EI_DENORM is a denormalized staging and presentation table in the Oracle Projects (PA) schema, designed to hold expenditure item records optimized for online time entry in Oracle E-Business Suite releases 12.1.1 and 12.2.2. Its documented purpose is to flatten the normally normalized expenditure item model into a single, wide row so that time entry screens and self-service pages can retrieve, validate, and display project, task, resource, and cost attributes without repeated joins to PA_EXPENDITURE_ITEMS and its satellites. The table carries a documented physical schema of 200 columns, which is characteristic of a denormalization pattern where repeating attribute blocks are replicated across adjacent positions.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone, with no mined foreign key dependencies to external tables. That classification is a modeling suggestion rather than a structural fact: although PA_EI_DENORM holds descriptive and numeric attributes that behave like satellite content, its composite key of EXPENDITURE_ID and DENORM_ID also serves as a link between an expenditure item and a time-entry grouping context. Analysts constructing a vault model should treat the table as a hybrid satellite-link construct rather than a pure hub.

Key Information Stored

The composite primary key is enforced by PA_EI_DENORM_PK on (EXPENDITURE_ID, DENORM_ID). Two unique indexes are documented as business-key candidates: PA_EI_DENORM_U1 on (EXPENDITURE_ID, DENORM_ID) and PA_EI_DENORM_U2 on DENORM_ID alone. This indicates that EXPENDITURE_ID identifies the source expenditure item while DENORM_ID identifies the denormalized row or grouping instance, and that DENORM_ID is unique in its own right.

The most significant columns include PERSON_ID, PROJECT_ID, TASK_ID, and EXPENDITURE_TYPE, which describe the resource, project, task, and classification of the reported time. BILLABLE_FLAG governs whether the entry may be billed. UNIT_OF_MEASURE_CODE, UNIT_OF_MEASURE, and the per-position quantity columns such as QUANTITY_1 through QUANTITY_7 hold the hours or units reported in each denormalized slot. Matching cost attributes include RAW_COST_1 through RAW_COST_7, RAW_COST_RATE_1 through RAW_COST_RATE_7, ORGANIZATION_ID_1 through ORGANIZATION_ID_7, and OVERRIDE_TO_ORGANIZATION_ID_1 through _7. The columns EXPENDITURE_ITEM_DATE_1 through _7 carry the expenditure item dates, while EXPENDITURE_ITEM_ID_1 through _7 map each position back to the authoritative expenditure item. Totals are persisted in DENORM_TOTAL_QTY and DENORM_TOTAL_AMOUNT, and the table retains standard audit columns CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The principal use case is online time entry, where the entry form reads and writes denormalized rows to reduce round trips to the base expenditure tables. A secondary use case is validation and pre-processing before the denormalized data is promoted into PA_EXPENDITURE_ITEMS by the time entry and cost distribution programs.

  • Retrieving a time entry line by its denormalized identifier: SELECT * FROM PA.PA_EI_DENORM WHERE DENORM_ID = :p_denorm_id;
  • Listing all denormalized rows for a person within a period: SELECT DENORM_ID, PROJECT_ID, TASK_ID, DENORM_TOTAL_QTY, DENORM_TOTAL_AMOUNT FROM PA.PA_EI_DENORM WHERE PERSON_ID = :p_person_id ORDER BY CREATION_DATE;
  • Reconciling denormalized totals against the source expenditure item: SELECT d.DENORM_ID, d.DENORM_TOTAL_AMOUNT, e.raw_cost FROM PA.PA_EI_DENORM d, PA.PA_EXPENDITURE_ITEMS e WHERE d.EXPENDITURE_ID = e.expenditure_item_id AND d.DENORM_ID = :p_denorm_id;
  • Identifying billable time for a project: SELECT PROJECT_ID, SUM(DENORM_TOTAL_QTY) FROM PA.PA_EI_DENORM WHERE BILLABLE_FLAG = 'Y' AND PROJECT_ID = :p_project_id GROUP BY PROJECT_ID;

Related Objects

Because the documented metadata classifies PA_EI_DENORM as standalone, the FK relationships below are inferred from the column semantics and the standard Oracle Projects data model rather than from mined constraints.

  • PA_EXPENDITURE_ITEMS — joined on EXPENDITURE_ID = expenditure_item_id; the authoritative source of the denormalized values in positions _1 through _7.
  • PA_EXPENDITURE_ITEM_DETAILS — joined on the expenditure item identifiers carried in the position columns.
  • PA_PROJECTS_ALL — joined on PROJECT_ID for project name, number, and organization context.
  • PA_TASKS — joined on TASK_ID for task name and service type.
  • PER_ALL_PEOPLE_F — joined on PERSON_ID to resolve the time reporter.
  • PA_EXPENDITURE_TYPES — joined on EXPENDITURE_TYPE for validation and reporting.
  • PA_ORG_UNITS / HR_ALL_ORGANIZATION_UNITS — joined on ORGANIZATION_ID_n and OVERRIDE_TO_ORGANIZATION_ID_n.
  • PA_TIME_ENTRY and the OTL time entry concurrent programs, which populate and consume this denormalized structure during transfer to Oracle Time and Labor.