Results for “pa_expenditure_items_u1”

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

Overview

PA.PA_EXPENDITURE_ITEMS_ALL is the master transaction table in Oracle Projects that stores individual expenditure items — the atomic units of cost and revenue that flow through the Project Costing, Billing, and Capitalization processes. Each row represents a single detail line associated with a parent expenditure (PA_EXPENDITURES_ALL) and is linked to a project, task, and expenditure type. The table is a multi-org view/table defined with a Type A Multi-Org designation, meaning queries automatically filter to the current operating unit via ORG_ID while data for other operating units is ignored. It resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 40, and its indexes live in APPS_TS_TX_IDX.

Because the table records transactional facts tied to a parent expenditure and enriched with descriptive attributes (currency, rate, cost, revenue, capitalization, cross-charge details), a Data Vault heuristic classifies it as satellite-leaning rather than a pure hub or link. Its grain is one row per expenditure item, making it a natural detail satellite to the PA_EXPENDITURES_ALL hub, while its many foreign keys to reference tables (currencies, rate types, expenditure types) act as link attachments.

Key Information Stored

The table contains 205 documented columns. The most consequential include:

• EXPENDITURE_ITEM_ID — surrogate primary key (PA_EXPENDITURE_ITEMS_PK) and the column behind the unique index PA_EXPENDITURE_ITEMS_U1.
• EXPENDITURE_ID — FK to the parent PA_EXPENDITURES_ALL row grouping related detail items.
• PROJECT_ID / TASK_ID — the project and task charged, both with nonunique indexes (PA_EXPENDITURE_ITEMS_N2, N17).
• EXPENDITURE_TYPE — classification linked to PA_EXPENDITURE_TYPES driving cost/revenue rules.
• EXPENDITURE_ITEM_DATE (along with EXPENDITURE_ID) — indexed by PA_EXPENDITURE_ITEMS_N1 for period-based reporting.
• RAW_COST / BURDEN_COST — the primary cost measures in project currency.
• BILLABLE_FLAG / BILL_HOLD_FLAG — billing eligibility controls; BILL_HOLD_FLAG participates in PA_EXPENDITURE_ITEMS_U1.
• COST_DISTRIBUTED_FLAG, REVENUE_DISTRIBUTED_FLAG, COST_BURDEN_DISTRIBUTED_FLAG, CC_BL_DISTRIBUTED_CODE — processing status flags indexed by PA_EXPENDITURE_ITEMS_N15/N16/N20.
• TRANSACTION_SOURCE and ORIG_TRANSACTION_REFERENCE — source-system lineage (indexed N10).
• SOURCE_EXPENDITURE_ITEM_ID / ADJUSTED_EXPENDITURE_ITEM_ID / TRANSFERRED_FROM_EXP_ITEM_ID — self-referencing FKs for adjustments and transfers.
• DENOM_ID — a denormalized surrogate used for reporting performance.
• ORG_ID — the multi-org operating unit discriminator.

Note that the documented physical schema lists PA_EXPENDITURE_ITEMS_U1 as covering (EXPENDITURE_ITEM_ID, BILL_HOLD_FLAG), a composite unique index that also serves as a business-key candidate for item/hold combinations.

Common Use Cases and Queries

Typical usage centers on cost and revenue reporting, unprocessed-item cleanup, and billing integration. A common pattern retrieves all items for a project whose costs have not yet been distributed:

• Reporting project cost detail: join to PA_EXPENDITURES_ALL on EXPENDITURE_ID and PA_PROJECTS_ALL on PROJECT_ID, filtering by a date range on EXPENDITURE_ITEM_DATE.
• Finding undistributed items: SELECT ... WHERE COST_DISTRIBUTED_FLAG = 'N' OR COST_BURDEN_DISTRIBUTED_FLAG = 'N'.
• Identifying billing candidates: WHERE BILLABLE_FLAG = 'Y' AND BILL_HOLD_FLAG = 'N' AND REVENUE_DISTRIBUTED_FLAG = 'N'.
• Tracing adjustments: self-join via ADJUSTED_EXPENDITURE_ITEM_ID or SOURCE_EXPENDITURE_ITEM_ID to reveal original versus correcting items.
• Cross-charge tracking: filter on CC_CROSS_CHARGE_CODE and CC_IC_PROCESSED_CODE.

The composite index N22 covering CC_XC and expenditure date supports these cross-charge and date-based queries efficiently.

Related Objects

PA_EXPENDITURE_ITEMS_ALL is central to the Projects data model. Significant related objects include:

• PA_EXPENDITURES_ALL — parent table joined via EXPENDITURE_ID.
• PA_TASKS and PA_PROJECTS_ALL — joined via TASK_ID and PROJECT_ID for project/task context.
• PA_EXPENDITURE_TYPES — joined via EXPENDITURE_TYPE for cost/revenue classification.
• PA_COST_DISTRIBUTION_LINES_ALL — child distribution detail linked via EXPENDITURE_ITEM_ID.
• PA_CUST_REV_DIST_LINES_ALL — revenue distribution detail.
• PA_DRAFT_INVOICE_DETAILS_ALL — billing invoice lines referencing items.
• PA_PROJECT_ASSET_LINE_DETAILS — capitalization linkage for asset generation.
• PA_TRANSACTION_INTERFACE_ALL — interface staging referencing items.
• PA_CC_DIST_LINES_ALL and PA_MC_CC_DIST_LINES_ALL — cross-charge distribution lines.
• PA_EXPENDITURE_ITEMS_ALL (self) — adjustment/transfer relationships via ADJUSTED_EXPENDITURE_ITEM_ID, SOURCE_EXPENDITURE_ITEM_ID, and TRANSFERRED_FROM_EXP_ITEM_ID.

These relationships confirm the table's role as the fulcrum between cost collection, burdening, and downstream billing and capitalization activity within Oracle Projects.