Search Results pa_deduction_transactions_all

Overview

PA_DEDUCTION_TRANSACTIONS_ALL is a Projects (PA) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores detailed information about deduction requests raised against suppliers. Within the Oracle Projects architecture, deductions represent amounts withheld or recovered from a supplier's payment, typically in the context of supplier cost adjustments, quality holds, or contractual chargebacks tied to project expenditures. Each row in this table captures a single deduction request transaction line, linking that request to the underlying project, task, expenditure type, and expenditure item that generated the deduction.

The table is owned by the PA schema and is classified as VALID in the ETRM 12.2.2 data dictionary. It contains 23 documented columns. The heuristic Data Vault classification mined from the foreign-key structure is standalone, which suggests that, from a modeling perspective, this object behaves as an independent hub-like entity anchored by its own surrogate key rather than serving purely as a link between two parent hubs. Practitioners designing a Data Vault representation should treat DEDUCTION_REQ_TRAN_ID as the natural business key of that hub.

Key Information Stored

The table's two unique indexes identify its business-key candidates: PA_DEDUCTION_TRANSACTIONS_U1 on DEDUCTION_REQ_TRAN_ID and PA_DEDUCTION_TRANSACTIONS_U2 on EXPENDITURE_ITEM_ID. DEDUCTION_REQ_TRAN_ID therefore functions as the primary transaction identifier, while EXPENDITURE_ITEM_ID provides a one-to-one association to the originating expenditure item.

The most significant columns include:

• DEDUCTION_REQ_ID — identifier of the parent deduction request header to which this transaction line belongs.
• DEDUCTION_REQ_TRAN_ID — unique transaction-line identifier (U1 business key).
• EXPENDITURE_ITEM_ID — foreign key to PA_EXPENDITURE_ITEMS_ALL, tying the deduction to a specific expenditure item (U2 business key).
• PROJECT_ID and TASK_ID — the project and task charged or affected by the deduction.
• EXPENDITURE_TYPE and EXPENDITURE_ORG_ID — the expenditure type classification and the owning expenditure organization.
• EXPENDITURE_ITEM_DATE and GL_DATE — the expenditure item date and the general ledger accounting date.
• QUANTITY and OVERRIDE_QUANTITY — the original and manually overridden deduction quantities.
• ORIG_PROJFUNC_AMOUNT and OVERRIDE_PROJFUNC_AMOUNT — the original and overridden amounts in project functional currency.
• CONVERSION_RATETYPE, CONVERSION_RATEDATE, and CONVERSION_RATE — currency conversion attributes used when the transaction currency differs from the project functional currency.
• PROJFUNC_CURRENCY_CODE and AMOUNT — the project functional currency and the resulting transaction amount.
• DESCRIPTION — free-text explanation of the deduction.
• CREATION_DATE, CREATED_BY, LAST_UPDATION_DATE, and LAST_UPDATED_BY — standard audit columns. (Note the non-standard spelling of LAST_UPDATION_DATE.)

Common Use Cases and Queries

Typical scenarios include reconciling supplier deductions against project expenditures, auditing overrides applied to quantities or amounts, and reporting deduction activity by project, task, or supplier for cost recovery analysis.

A representative query joins the table to expenditure items and projects to surface deduction details:

• SELECT d.DEDUCTION_REQ_TRAN_ID, d.PROJECT_ID, d.TASK_ID, d.EXPENDITURE_ITEM_ID, d.ORIG_PROJFUNC_AMOUNT, d.OVERRIDE_PROJFUNC_AMOUNT, d.PROJFUNC_CURRENCY_CODE FROM PA_DEDUCTION_TRANSACTIONS_ALL d WHERE d.PROJECT_ID = :project_id;
• SELECT d.*, e.EXPENDITURE_ITEM_DATE, e.EXPENDITURE_TYPE FROM PA_DEDUCTION_TRANSACTIONS_ALL d, PA_EXPENDITURE_ITEMS_ALL e WHERE d.EXPENDITURE_ITEM_ID = e.EXPENDITURE_ITEM_ID;

Because the table is _ALL, it is a multi-org partitioned (or org-striped) table; queries should respect operating unit and MO security where applicable.

Related Objects

The following objects are most significant to PA_DEDUCTION_TRANSACTIONS_ALL:

• PA_EXPENDITURE_ITEMS_ALL — referenced through EXPENDITURE_ITEM_ID; the primary FK relationship and the source of expenditure detail.
• PA_DEDUCTION_REQUESTS (and related header tables) — supplies DEDUCTION_REQ_ID, the parent request context.
• PA_PROJECTS_ALL and PA_TASKS — resolve PROJECT_ID and TASK_ID to meaningful project and task names.
• PA_EXPENDITURE_TYPES — decodes the EXPENDITURE_TYPE classification.
• PA_PROJECT_CURRENCIES / GL_DAILY_RATES — support currency conversion rate and rate-date validation.
• PA_DEDUCTION_TRANSACTIONS_V (view) — where present, presents a user-friendly projection for reporting.

Oracle Projects APIs, including deduction and expenditure adjustment interfaces, write to this table, so direct DML should be avoided in favour of supported API calls.