Results for “pa_expenditures_all”

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

Overview

PA_EXPENDITURES_ALL is the master grouping table within the Oracle Projects (PA) module of Oracle E-Business Suite. It stores the control record that groups individual expenditure items incurred by employees or organizations for a defined expenditure period. Each row represents one expenditure batch — for example, a Pre-Approved Expenditure (PreApp), an expense report submission, a supplier invoice batch, or an internal cost transfer — and carries the header-level amounts, currency, status, and routing information against which the associated line items are validated.

Expenditure batches drive the entire Projects cost lifecycle: item entry, adjustment, approval routing, cost distribution, and transfer to Oracle Payables, Oracle Purchasing, or Oracle Payroll. Because the table is keyed by a surrogate identifier and holds descriptive and status attributes that change over time, a heuristic Data Vault classification of satellite-leaning is reasonable: EXPENDITURE_ID functions as a durable key, while CONTROL_TOTAL_AMOUNT, EXPENDITURE_STATUS_CODE, TRANSFER_STATUS_CODE, and WF_STATUS_CODE are mutable descriptors best modeled as satellite attributes.

Key Information Stored

The primary key is EXPENDITURE_ID, and the unique index PA_EXPENDITURES_U1 enforces uniqueness on that same column. Expenditure batches are identified in functional terms by the combination of EXPENDITURE_GROUP, EXPENDITURE_CLASS_CODE, EXPENDITURE_ENDING_DATE, and INCURRED_BY_PERSON_ID or INCURRED_BY_ORGANIZATION_ID.

• EXPENDITURE_ID — surrogate primary key, propagated to all dependent item, routing, and interface tables.
• EXPENDITURE_GROUP — foreign key to PA_EXPENDITURE_GROUPS_ALL; names the batch used to segregate a set of items at entry.
• EXPENDITURE_CLASS_CODE — indicates the batch type (PreApproved, expense report, supplier invoice, usage, internal cost, etc.).
• EXPENDITURE_ENDING_DATE — the period end the batch belongs to; central to period-based reporting.
• EXPENDITURE_STATUS_CODE — lifecycle state such as Entered, Submitted, Approved, Released, or Rejected.
• CONTROL_TOTAL_AMOUNT — the control total against which the sum of PA_EXPENDITURE_ITEMS_ALL is reconciled.
• INCURRED_BY_PERSON_ID / INCURRED_BY_ORGANIZATION_ID — the employee or organization that incurred the batch; the latter joins to HR_ALL_ORGANIZATION_UNITS.
• ENTERED_BY_PERSON_ID — the user who entered the batch.
• DENOM_CURRENCY_CODE / ACCT_CURRENCY_CODE — transaction and accounting currencies, both foreign keys to FND_CURRENCIES.
• ACCT_RATE_TYPE, ACCT_RATE_DATE, ACCT_EXCHANGE_RATE — conversion rate type (to GL_DAILY_CONVERSION_TYPES), date, and rate applied for accounting.
• VENDOR_ID — supplier associated with invoice-related batches, referencing PO_VENDORS.
• ORG_ID — operating unit, enforcing multi-org security.
• WF_STATUS_CODE and TRANSFER_STATUS_CODE — workflow approval and downstream transfer progress.

Common Use Cases and Queries

Typical reporting reconciles batch control totals to item sums, audits unapproved or untransferred batches, and extracts expenditure data for period-end cost reporting.

• Pending approval audit: SELECT EXPENDITURE_ID, EXPENDITURE_STATUS_CODE FROM PA_EXPENDITURES_ALL WHERE EXPENDITURE_CLASS_CODE = 'EXPENSE REPORT' AND EXPENDITURE_STATUS_CODE <> 'APPROVED';
• Control total reconciliation against items: join to PA_EXPENDITURE_ITEMS_ALL on EXPENDITURE_ID and compare SUM of item amounts with CONTROL_TOTAL_AMOUNT.
• Period reporting: filter by EXPENDITURE_ENDING_DATE and ORG_ID, aggregating CONTROL_TOTAL_AMOUNT by EXPENDITURE_CLASS_CODE.
• Transfer monitoring: query TRANSFER_STATUS_CODE and WF_STATUS_CODE to identify batches awaiting transfer to Payables or interface tables.

Related Objects

• PA_EXPENDITURE_ITEMS_ALL — joins on EXPENDITURE_ID; holds the individual detail lines of each batch.
• PA_EXPENDITURE_GROUPS_ALL — referenced via EXPENDITURE_GROUP; defines the batch grouping.
• PA_ROUTINGS — references EXPENDITURE_ID; stores approval routing for the batch.
• HR_ALL_ORGANIZATION_UNITS — referenced via INCURRED_BY_ORGANIZATION_ID.
• PO_VENDORS — referenced via VENDOR_ID.
• FND_CURRENCIES and GL_DAILY_CONVERSION_TYPES — referenced via DENOM_CURRENCY_CODE, ACCT_CURRENCY_CODE, and ACCT_RATE_TYPE.
• PA_EXPENDITURE_HISTORY, PA_EXP_HISTORY_AR, PA_EXPENDITURES_AR, and PA_EI_DENORM_AR — dependent and archived counterparts keyed by EXPENDITURE_ID.
• PSP_ENC_SUMMARY_LINES, PSP_SUMMARY_LINES, and their _ARCH variants — summarized cost and encumbrance lines referencing EXPENDITURE_ID.
• PA_TRANSACTION_INTERFACE_ALL and PA_ALLOC_TXN_DETAILS — interface and allocation tables that link back to the expenditure batch.