Search Results pa_perf_transactions_u1

Overview

PA.PA_PERF_TRANSACTIONS is a key performance area (KPA) performance transaction table in the Oracle Projects (PA) schema of Oracle E-Business Suite 12.1.1 and 12.2.2. It records performance transaction data — the individual measured values, thresholds, and scoring attributes that Oracle Projects generates when evaluating projects, tasks, and related objects against performance rules. Each row represents one performance transaction and captures the measure value derived for a specific KPA, measure, period, and rule combination. The table is registered in FND Design Data as PA.PA_PERF_TRANSACTIONS, is valid in the documented release, and resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10.

Heuristic Data Vault classification derived from the foreign key structure characterizes this table as satellite-leaning. Modeling-wise, PA_PERF_TRANSACTIONS behaves as a satellite attached to the principal project and rule hubs, because it stores descriptive, time- and version-aware measurements about those entities rather than defining the entities themselves. Conservative modeling would treat PA_PROJECTS_ALL, PA_PERF_RULES, and PA_PERF_OBJECT_RULES as hubs and PA_PERF_TRANSACTIONS as a satellite capturing the measured business state; the RECORD_VERSION_NUMBER and audit columns reinforce this interpretation.

Key Information Stored

The surrogate primary key is PERF_TXN_ID (NUMBER(15)), which is also the single-column business-key candidate exposed through the unique index PA_PERF_TRANSACTIONS_U1.

The most significant columns include:

• PERF_TXN_ID — unique performance transaction identifier; the table's surrogate primary key.
• PERF_TXN_OBJ_TYPE / PERF_TXN_OBJ_ID — the object type and object identifier the transaction applies to; the leading columns of most nonunique indexes.
• PROJECT_ID — project identifier, foreign key to PA_PROJECTS_ALL; the primary project context for the measurement.
• RULE_ID — foreign key to PA_PERF_RULES, identifying the performance rule that produced the transaction.
• OBJECT_RULE_ID — foreign key to PA_PERF_OBJECT_RULES, associating the rule with a specific object.
• KPA_CODE — the key performance area code, a central grouping attribute for scoring and reporting.
• MEASURE_ID / MEASURE_VALUE — the custom measure identifier and the current measured value.
• PERIOD_NAME / PERIOD_TYPE — the reporting period name and the calendar type (GL or PA).
• THRESHOLD_FROM / THRESHOLD_TO / WEIGHTING — the threshold band and weighting applied to the measurement.
• CURRENT_FLAG / INCLUDED_IN_SCORING — flags indicating whether the row is the current version and whether it participates in scoring.
• RELATED_OBJ_TYPE / RELATED_OBJ_ID — the related object type and identifier (RELATED_OBJ_ID maps to PA_PROJECTS_ALL).
• PROGRAM_ID — foreign key to FND_CONCURRENT_REQUESTS, identifying the concurrent program that generated the row.
• RECORD_VERSION_NUMBER — optimistic locking/versioning column; audit columns include CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical usage centers on KPA scorecards, project performance monitoring, and exception reporting. Reporting queries commonly filter by CURRENT_FLAG = 'Y' and INCLUDED_IN_SCORING = 'Y' to obtain the active scoring set, then aggregate MEASURE_VALUE by KPA_CODE or PROJECT_ID.

• Retrieve current performance transactions for a project: SELECT perf_txn_id, kpa_code, measure_value FROM pa.pa_perf_transactions WHERE project_id = :project_id AND current_flag = 'Y';
• Join to PA_PERF_RULES on RULE_ID and PA_PERF_OBJECT_RULES on OBJECT_RULE_ID to obtain rule descriptions and object associations.
• Filter by PERF_TXN_OBJ_TYPE and PERF_TXN_OBJ_ID using index PA_PERF_KPA_TRANS_N1 (which also spans MEASURE_ID and CURRENT_FLAG) for object-level drill-downs.
• Filter by KPA_CODE alone (index PA_PERF_KPA_TRANS_N4) or by KPA_CODE plus INCLUDED_IN_SCORING (index PA_PERF_KPA_TRANS_N3) for KPA-scoped scoring extracts.
• Identify the concurrent process that created rows via PROGRAM_ID against FND_CONCURRENT_REQUESTS for audit and troubleshooting.
• Report exception items using EXCEPTION_FLAG and DATE_CHECKED.

Related Objects

The principal relationships documented for this table are:

• PA_PROJECTS_ALL — referenced by PROJECT_ID, RELATED_OBJ_ID, and PPL_PROJECT_ID; the central project master join.
• PA_PERF_RULES — referenced by RULE_ID; supplies the performance rule definition.
• PA_PERF_OBJECT_RULES — referenced by OBJECT_RULE_ID; links rules to objects.
• FND_CONCURRENT_REQUESTS — referenced by PROGRAM_ID; identifies the generating concurrent request.
• PA_PERF_COMMENTS — references this table via PERF_TXN_ID; holds commentary on a transaction.
• PA_PERF_KPA_TRANS — references this table via PERF_TXN_ID; the KPA-level transaction detail child.

Because PA_PERF_COMMENTS and PA_PERF_KPA_TRANS carry PERF_TXN_ID foreign keys, PA_PERF_TRANSACTIONS is the parent for those child tables, and joins should be driven through PERF_TXN_ID.