Results for “pa_expenditure_types”

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

Overview

PA_EXPENDITURE_TYPES is an Oracle Projects (PA) implementation table that stores the user-defined classifications of expenditures charged to projects and tasks. Every project cost transaction, supplier invoice distribution, expense report line, requisition distribution, and budget item must reference a valid expenditure type, making this table a central reference object that binds transactional subledger data to Projects cost collection, burdening, billing, and capitalisation logic. The table resides in the PA schema and is fully described in the ETRM 12.2.2 physical schema with 32 columns.

From a Data Vault modeling perspective, the metadata's heuristic classification is hub, with EXPENDITURE_TYPE_ID serving as the natural hub key and the derived business key EXPENDITURE_TYPE providing uniqueness. This is a reasonable suggestion because the table acts as a durable, insert-mostly registry of business concepts referenced by a very large number of downstream satellites and link tables, rather than storing transactional events itself.

Key Information Stored

Although the table contains 32 columns, the following represent the functional core:

• EXPENDITURE_TYPE_ID — surrogate primary key column and the target of unique index PA_EXPENDITURE_TYPES_U2; also referenced by PSP_EFFORT_REPORT_DETAILS.ELEMENT_TYPE_ID and PSP_EFFORT_REPORT_TEMPLATES.ELEMENT_TYPE_ID.
• EXPENDITURE_TYPE — user-facing business-key name; unique index PA_EXPENDITURE_TYPES_U1 enforces its uniqueness and the primary key PA_EXPENDITURE_TYPES_PK is documented against this column.
• EXPENDITURE_CATEGORY — foreign key to PA_EXPENDITURE_CATEGORIES, grouping the expenditure type into cost categories such as labour, material, or expense.
• SYSTEM_LINKAGE_FUNCTION — foreign key to PA_SYSTEM_LINKAGES, driving interface behaviour for downstream application processing.
• REVENUE_CATEGORY_CODE — classification used when the expenditure is billable.
• COST_RATE_FLAG — indicates whether cost rates are maintained for the expenditure type.
• UNIT_OF_MEASURE — default unit used when the type is consumed as an inventory-style resource.
• START_DATE_ACTIVE / END_DATE_ACTIVE — validity window controlling which types are selectable at transaction entry.
• ADW_NOTIFY_FLAG — flag controlling notification behaviour for the expenditure type.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield segments reserved for implementation-specific extensions.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns populated by the EBS concurrency framework.

Common Use Cases and Queries

Typical uses include validating expenditure types before they are attached to project transactions, reporting by cost category, filtering active types for value-set validation, and tracing which types are configured for supplier invoice accrual or expense reports.

• List active expenditure types with their category:

  SELECT e.expenditure_type, e.expenditure_category,
         c.category_description
  FROM   pa_expenditure_types e, pa_expenditure_categories c
  WHERE  e.expenditure_category = c.expenditure_category
  AND    (e.end_date_active IS NULL OR e.end_date_active > SYSDATE);

• Identify expenditure types referenced by unposted project transactions in the interface:

  SELECT t.expenditure_type, COUNT(*)
  FROM   pa_transaction_interface_all t
  GROUP  BY t.expenditure_type;

• Find supplier invoice distributions booked to a specific project expenditure type:

  SELECT d.invoice_id, d.expenditure_type, d.amount
  FROM   ap_invoice_distributions_all d
  WHERE  d.expenditure_type = :exp_type;

Reporting queries frequently join PA_EXPENDITURE_TYPES to PA_EXPENDITURE_CATEGORIES for category-level summaries and to PA_EXPENDITURE_ITEMS_ALL for cost accumulation detail. Because the table is used as a foreign-key target across AP, PO, GMS, IGC, PSP, and PJM schemas, it is best treated as a slowly-changing reference dimension rather than a transactional fact source.

Related Objects

The metadata's relationship data documents the more significant dependencies:

• PA_EXPENDITURE_CATEGORIES — referenced via PA_EXPENDITURE_TYPES.EXPENDITURE_CATEGORY; classifies the type into cost groupings.
• PA_SYSTEM_LINKAGES — referenced via PA_EXPENDITURE_TYPES.SYSTEM_LINKAGE_FUNCTION; determines interface processing rules.
• PA_EXPENDITURE_ITEMS_ALL — via EXPENDITURE_TYPE; the primary transaction storage for project costs.
• PA_TRANSACTION_INTERFACE_ALL — via EXPENDITURE_TYPE; inbound interface for cost transactions.
• AP_INVOICE_DISTRIBUTIONS_ALL and AP_EXPENSE_REPORT_LINES_ALL — via EXPENDITURE_TYPE; subledger sources that feed Projects.
• AP_INVOICES_ALL, AP_EXPENSE_REPORT_PARAMS_ALL, and AP_DISTRIBUTION_SET_LINES_ALL — additional AP-side references to the same business key.
• PSP_ELEMENT_TYPE_ACCOUNTS, PSP_SCHEDULE_LINES, PSP_SUMMARY_LINES, and PSP_DISTRIBUTION_INTERFACE — a family of Payroll/PSP objects that depend on expenditure type for costing and distribution.
• PJM_ORG_PARAMETERS and PJM_PROJECT_PARAMETERS — reference the table many times over (IPV, ERV, FREIGHT, TAX, MISC, PPV, and variance expenditure types) to define default-costing behaviour.
• PA_ALLOC_RULES_ALL — references the table through both TARGET_EXP_TYPE and OFFSET_EXP_TYPE for allocation rule configuration.
• PO_REQ_DISTRIBUTIONS_ALL, PON_AUCTION_ITEM_PRICES_ALL, and GMS_AWARD_EXP_TYPE_ACT_COST — further downstream references from Purchasing and Grants.

The breadth of inbound foreign keys confirms the object's role as a foundational reference table for the entire Projects cost model rather than a transactional or event table.