Search Results pa_deductions_all_u2

Overview

PA.PA_DEDUCTIONS_ALL is a transactional table in the Oracle E-Business Suite Projects (PA) schema that stores deduction requests raised against suppliers in connection with project-related purchasing and change management activity. A deduction request represents an intent to recover amounts from a supplier by raising a debit memo, typically where a change order or change request has resulted in a cost that the supplier is contractually responsible for absorbing. The table therefore sits at the intersection of Oracle Projects, Oracle Purchasing, and Oracle Payables, acting as the staging and tracking record between a change document and the eventual debit memo in the Payables subledger.

From a Data Vault modeling perspective, the mined foreign key structure classifies this object heuristically as a standalone structure. Its only documented outbound relationship is to PA.PA_CONTROL_ITEMS via CI_ID, so it functions primarily as a self-contained record of a deduction event rather than as a true hub-and-link intersection. In practice it is best modeled as a satellite over the deduction request business key, with the control item and project references acting as dimension links.

Key Information Stored

The table contains 27 documented columns. The most operationally significant are:

• DEDUCTION_REQ_ID — System-generated numeric identifier and the surrogate primary key, enforced by unique index PA_DEDUCTIONS_ALL_U1.
• DEDUCTION_REQ_NUM — The user-visible deduction request number (VARCHAR2(30)); this is the business-key candidate, enforced by unique index PA_DEDUCTIONS_ALL_U2.
• PROJECT_ID — Identifies the project for which the deduction request was created.
• VENDOR_ID and VENDOR_SITE_ID — Identify the supplier and supplier site against which the debit memo is to be raised.
• CI_ID — The control item associated with the change document that triggered the deduction request; this is the sole documented foreign key, pointing to PA_CONTROL_ITEMS.
• CHANGE_DOC_NUM and CHANGE_DOC_TYPE — Denormalized change order or change request identifier and its type.
• PO_HEADER_ID and PO_NUMBER — The associated purchase order; PO_NUMBER is denormalized from PO_HEADER_ALL.SEGMENT1.
• DEBIT_MEMO_NUM and DEBIT_MEMO_DATE — The Payables debit memo created from the deduction request.
• CURRENCY_CODE, CONVERSION_RATETYPE, CONVERSION_RATEDATE, and CONVERSION_RATE — Currency and conversion detail for the debit memo transaction.
• TOTAL_AMOUNT and TOTAL_PFC_AMOUNT — The total deduction value and its project functional currency equivalent.
• DEDUCTION_REQ_DATE and STATUS — Request date and workflow/lifecycle state.
• ORG_ID — Multi-org operating unit identifier; DOCUMENT_TYPE distinguishes the deduction document class.
• Audit columns CREATION_DATE, CREATED_BY, LAST_UPDATION_DATE, LAST_UPDATED_BY.

Common Use Cases and Queries

Typical reporting scenarios include outstanding deductions by supplier, aging of deduction requests, and reconciliation of deduction requests to debit memos in Payables. The four non-unique indexes support the most common access paths: PA_DEDUCTIONS_ALL_N1 (PROJECT_ID, VENDOR_ID, CI_ID) supports project-and-supplier lookups; PA_DEDUCTIONS_ALL_N2 (VENDOR_ID, PO_HEADER_ID) supports supplier/PO reconciliation; PA_DEDUCTIONS_ALL_N3 (PROJECT_ID, DEDUCTION_REQ_DATE, VENDOR_ID) supports date-ranged project reporting; and PA_DEDUCTIONS_ALL_N4 (PROJECT_ID, STATUS) supports status filtering.

A representative query for open deductions by project:

• SELECT d.deduction_req_num, d.vendor_id, d.po_number, d.total_amount, d.currency_code, d.status FROM pa.pa_deductions_all d WHERE d.project_id = :project_id AND d.status = :status AND d.org_id = :org_id;

Reconciliation between the deduction request and the change document control item:

• SELECT d.deduction_req_num, d.change_doc_num, d.change_doc_type, ci.* FROM pa.pa_deductions_all d JOIN pa.pa_control_items ci ON ci.ci_id = d.ci_id;

Because ORG_ID is carried on the table, all queries should be filtered by operating unit where the client is multi-org enabled.

Related Objects

• PA.PA_CONTROL_ITEMS — Referenced by CI_ID; holds the control item associated with the triggering change document.
• PO.PO_HEADERS_ALL — Source of the denormalized PO_NUMBER (SEGMENT1) and the column behind PO_HEADER_ID.
• PO.PO_VENDORS / PO.PO_VENDOR_SITES_ALL — Resolve VENDOR_ID and VENDOR_SITE_ID to supplier name and site.
• AP.AP_INVOICES_ALL — The debit memo identified by DEBIT_MEMO_NUM / DEBIT_MEMO_DATE resides in the Payables invoice tables.
• PA.PA_PROJECTS_ALL — Resolves PROJECT_ID to project number and name.
• PA.PA_CHANGE_ORDERS / change management objects — Correlate with CHANGE_DOC_NUM and CHANGE_DOC_TYPE to trace the originating change.
• Oracle Projects and Payables APIs for deduction processing and debit memo creation — these consume the row after creation and populate DEBIT_MEMO_NUM on completion.