Search Results pa_deduction_transactions_n1

Overview

PA.PA_DEDUCTION_TRANSACTIONS_ALL is a transactional table in the Oracle Projects (PA) schema that stores deduction transactions raised against supplier work performed on a project. A deduction transaction represents a monetary or quantity-based reduction applied to the amount otherwise payable to a supplier, typically in the context of supplier cost accrual, deduction requests, and project expenditure processing. Each row ties a deduction request to a specific project, task, and expenditure item, capturing both the original and overridden quantity and currency amounts.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and is indexed in APPS_TS_TX_IDX. In Oracle EBS 12.1.1 and 12.2.2, the table is owned by the PA schema and is registered in FND Design Data as PA.PA_DEDUCTION_TRANSACTIONS_ALL. From a Data Vault modeling perspective, the metadata heuristically classifies this object as standalone, which suggests it functions as an independent link/hub candidate rather than a dependent satellite. The absence of a documented FK to a parent deduction request header in the supplied metadata supports treating DEDUCTION_REQ_ID as a soft business reference rather than an enforced relational constraint.

Key Information Stored

The table contains 23 documented columns. The most significant are summarized below.

• DEDUCTION_REQ_TRAN_ID — System-generated number that uniquely identifies the deduction transaction. This is the primary surrogate key and the column behind the unique index PA_DEDUCTION_TRANSACTIONS_U1.
• EXPENDITURE_ITEM_ID — The expenditure item mapped to a deduction line. It is the second unique business-key candidate, enforced by PA_DEDUCTION_TRANSACTIONS_U2, and is the only documented foreign key, referencing PA_EXPENDITURE_ITEMS_ALL.
• DEDUCTION_REQ_ID — Identifier of the deduction request to which the transaction belongs. This is a soft (non-enforced) parent reference.
• PROJECT_ID and TASK_ID — The project and task to which the deduction transaction is charged. Both participate in the non-unique index PA_DEDUCTION_TRANSACTIONS_N1 along with EXPENDITURE_ITEM_DATE.
• EXPENDITURE_TYPE and EXPENDITURE_ORG_ID — Classification of the work performed and the expenditure organization bearing the cost.
• QUANTITY and OVERRIDE_QUANTITY — Original supplier work quantity versus the user-modified quantity when an expenditure item is mapped to a deduction line.
• EXPENDITURE_ITEM_DATE and GL_DATE — Date work was performed versus the accounting date.
• PROJFUNC_CURRENCY_CODE, ORIG_PROJFUNC_AMOUNT, and OVERRIDE_PROJFUNC_AMOUNT — Project functional currency and original versus overridden amounts in that currency.
• CONVERSION_RATETYPE, CONVERSION_RATEDATE, and CONVERSION_RATE — Currency conversion attributes supporting the amount columns.
• Audit columns CREATION_DATE, CREATED_BY, LAST_UPDATED_DATE (documented as LAST_UPDATION_DATE in the excerpt), and LAST_UPDATED_BY.

The distinction between the two unique indexes is important: U1 enforces row-level uniqueness on the surrogate DEDUCTION_REQ_TRAN_ID, while U2 enforces that any given EXPENDITURE_ITEM_ID appears at most once in this table.

Common Use Cases and Queries

The table is typically queried for supplier deduction reporting, expenditure reconciliation, and currency conversion validation.

• Retrieve all deduction transactions for a project and task within a date range:

  SELECT deduction_req_tran_id, expenditure_item_id,
         quantity, override_quantity, projfunc_currency_code
    FROM pa.pa_deduction_transactions_all
   WHERE project_id = :project_id
     AND task_id    = :task_id
     AND expenditure_item_date BETWEEN :start_date AND :end_date;

• Reconcile a deduction line back to its expenditure item:

  SELECT d.deduction_req_tran_id, i.expenditure_item_id,
         i.quantity, d.override_quantity
    FROM pa.pa_deduction_transactions_all d,
         pa.pa_expenditure_items_all  i
   WHERE d.expenditure_item_id = i.expenditure_item_id
     AND d.deduction_req_id = :req_id;

• Identify transactions where the user overrode the supplier quantity or amount by comparing QUANTITY to OVERRIDE_QUANTITY and ORIG_PROJFUNC_AMOUNT to OVERRIDE_PROJFUNC_AMOUNT.
• Validate currency conversion by inspecting CONVERSION_RATETYPE, CONVERSION_RATEDATE, and CONVERSION_RATE alongside the functional amounts.
• Feed downstream subledger or payables reporting by joining GL_DATE and expenditure organization identifiers.

Related Objects

The following objects have the most significant documented or expected relationships to PA_DEDUCTION_TRANSACTIONS_ALL.

• PA.PA_EXPENDITURE_ITEMS_ALL — Referenced via the enforced foreign key EXPENDITURE_ITEM_ID. This is the primary join path for expenditure detail.
• PA.PA_DEDUCTION_REQUESTS_ALL (or equivalent deduction request header) — Parent business reference via DEDUCTION_REQ_ID. Not enforced by a documented FK, but logically the header source.
• PA.PA_PROJECTS_ALL — Join on PROJECT_ID for project attributes.
• PA.PA_TASKS — Join on TASK_ID for task-level reporting.
• PA.PA_EXPENDITURE_TYPES — Lookup by EXPENDITURE_TYPE for work classification.
• HR_OPERATING_UNITS / PA.PA_ORGANIZATIONS_ALL — Reference by EXPENDITURE_ORG_ID for organization reporting.
• GL.GL_DAILY_CONVERSION_TYPES — Aligns with CONVERSION_RATETYPE for currency conversion validation.

Because the metadata exposes no public API specific to this table, integration is normally performed through seeded Oracle Projects deduction request processing and reporting views rather than direct DML. Direct inserts and updates should be avoided; changes flow through the deduction request workflow that maintains both original and override values consistently.