Search Results quantity_1

Overview

PA_EI_DENORM_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, belonging to the Projects (PA) product family. Its documented purpose is to present "Expenditure Item Info along with Denorm data in vertical fashion." In Oracle EBS 12.1.1 and 12.2.2, the view normalizes the horizontally-denormalized structure of the PA_EI_DENORM table — which stores up to six expenditure item slots as repeating column groups (EXPENDITURE_ITEM_ID_1 through _6, QUANTITY_1 through _6, RAW_COST_1 through _6, and so on) — into a conventional row-per-expenditure-item result set.

The view serves reporting and integration consumers that expect a flat, vertical layout of expenditure items rather than the wide denormalized form. Because the view is defined in the APPS schema and marked VALID, it can be referenced directly by custom reports, BI Publisher data templates, Oracle XML Publisher concurrent programs, and interfaces that need to read Projects expenditure data without navigating the denormalized table structure.

Underlying Base Objects

The ETRM metadata documents three referenced base objects for this view:

• PA_EXPENDITURE_ITEMS_ALL (SYNONYM) — the primary source of expenditure item rows, supplying the standard, non-denormalized attributes.
• PA_EI_DENORM (SYNONYM) — the denormalized staging/summary table whose repeating column groups are unpivoted by the view's UNION ALL branches.
• PA_UTILS (PACKAGE) — referenced within the view text via PA_UTILS.GETEXPTYPECOSTRATE, used to derive a cost rate for an expenditure type on a given expenditure item date.

The view is constructed as a UNION ALL. The first branch selects directly from PA_EXPENDITURE_ITEMS. Each subsequent branch selects from PA_EI_DENORM for one of the numbered slots, filtering on rows where the corresponding QUANTITY_n is not null and EXPENDITURE_ITEM_ID_n is null. In branches two through six, the derived and currency-related columns are returned as NULL or TO_NUMBER(NULL), since those attributes are only populated in the base expenditure item branch.

Key Columns

• EXPENDITURE_ID / EXPENDITURE_ITEM_ID — primary identifiers linking the row to its expenditure and expenditure item.
• TASK_ID — the project task to which the expenditure is charged.
• EXPENDITURE_TYPE — the expenditure type classification, also used as an input to the cost rate function.
• BILLABLE_FLAG — indicates whether the expenditure item is billable to the customer; a frequently filtered column in revenue and invoicing queries.
• EXPENDITURE_ITEM_DATE — the date of the expenditure item, used both for reporting periods and as the effective date passed to PA_UTILS.GETEXPTYPECOSTRATE.
• QUANTITY / RAW_COST — the quantity and raw (unconverted) cost of the item.
• Derived amount column — an NVL expression returning DENOM_RAW_COST when present, otherwise QUANTITY multiplied by the expenditure type cost rate retrieved via PA_UTILS.GETEXPTYPECOSTRATE.
• RECEIPT_CURRENCY_CODE / RECEIPT_CURRENCY_AMOUNT / RECEIPT_EXCHANGE_RATE — receipt currency and conversion attributes, populated only in the base expenditure item branch.
• UNIT_OF_MEASURE — the unit of measure for the quantity.

Common Use Cases and Queries

Typical uses include reconciliation of denormalized and base expenditure items, project cost reporting by billable status, and integration extracts that require one row per item. A representative query filtering on the user's search term is:

• SELECT expenditure_id, expenditure_item_id, task_id, expenditure_type, billable_flag, expenditure_item_date, quantity, raw_cost FROM apps.pa_ei_denorm_v WHERE billable_flag = 'Y';
• SELECT expenditure_type, SUM(NVL(denom_raw_cost, quantity * pa_utils.getexptypecostrate(expenditure_type, expenditure_item_date))) FROM apps.pa_ei_denorm_v WHERE billable_flag = 'Y' GROUP BY expenditure_type;

Because the view unions the base table with up to six denormalized slots, queries should be validated against PA_EXPENDITURE_ITEMS_ALL to confirm row counts and to detect any duplication introduced by the denormalized branches.