Results for “pa_proj_ap_inv_distributions”

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

Overview

PA_PROJ_AP_INV_DISTRIBUTIONS is a Projects (PA) module view owned by the APPS schema. As documented in the ETRM 12.2.2 metadata, its purpose is to expose project-related Oracle Payables invoice distributions that have not yet been transferred into Oracle Projects. In other words, the view acts as a staging or reconciliation surface between Oracle Payables (AP) and Oracle Projects (PA): it lists AP distribution lines that reference a project but for which no corresponding project expenditure has yet been interfaced. This makes it a key diagnostic object for the Payables-to-Projects integration, commonly used to identify "uncosted" or "untransferred" invoice lines.

Because it is a view rather than a table, it presents no stored data. Its usefulness lies in the derived columns it computes — approval status, commitment information, cash-basis flags, currency conversion and quantity handling — that would otherwise require complex joins or PL/SQL calls. Valid in both 12.1.1 and 12.2.2, the object is central to period-end reconciliation and to troubleshooting invoices that fail to appear in Project Costing or Expenditure Inquiry.

Underlying Base Objects

The view is defined across a wide set of documented base objects, reflecting the depth of the AP–PO–PA relationship. The core sources are:

• AP_INVOICES_ALL — invoice header information such as invoice number, date, source, currency, payment status and invoice type.
• AP_INVOICE_DISTRIBUTIONS_ALL — the distribution lines themselves, including project and task references, amount, quantity, accounting date and line type.
• PO_DISTRIBUTIONS_ALL and PO_VENDORS — purchase order distribution context, accrual-on-receipt flags, PO line references, and vendor name.
• PA_PROJECTS_ALL, PA_TASKS, PA_TASKS_EXPEND_V, PA_EXPENDITURE_TYPES and PA_EXPEND_TYP_SYS_LINKS — project, task and expenditure-type validation and mapping.
• GL_LEDGERS — provides the SLA cash-basis ledger flag used for cash-basis accounting.
• HR_ORGANIZATION_UNITS — organization/operating unit context.
• AP_SELF_ASSESSED_TAX_DIST_ALL — self-assessed tax distributions.

Several PL/SQL packages are invoked directly in the view definition, notably AP_INVOICES_PKG.GET_APPROVAL_STATUS for the approval flag, PA_CMT_UTILS (GET_INV_CMT and GET_INV_VAR) for commitment and variance derivation, plus PA_TASK_UTILS, PA_UTILS4, HR_GENERAL, HR_SECURITY and FND_PROFILE for task, security and profile-dependent logic. This mixture of relational joins and function calls explains the view's complexity and its dependence on the AP and PA object stacks.

Key Columns

• INVOICE_NUM / INVOICE_DATE / INVOICE_CURRENCY_CODE — invoice identity and currency.
• VENDOR_NAME — supplier name from PO_VENDORS.
• DISTRIBUTION_LINE_NUMBER / DESCRIPTION / ACCOUNTING_DATE — distribution-level detail.
• POSTED_FLAG — decoded from D.POSTED_FLAG ('Y'/'N'), indicating whether the distribution has been posted.
• APPROVED_FLAG — derived via AP_INVOICES_PKG.GET_APPROVAL_STATUS, 'Y' when approved.
• Commitment and variance columns — computed through PA_CMT_UTILS.GET_INV_CMT and GET_INV_VAR, exposed for both entered and base currency variants.
• ACCRUE_ON_RECEIPT_FLAG / PO_LINE_ID — purchase order receipt accrual context.
• AMOUNT / PA_QUANTITY — monetary and project quantity amounts.
• PROJECT_ID / LINE_TYPE_LOOKUP_CODE — the project reference and AP line type.
• HISTORICAL_FLAG / PREPAY_AMOUNT_REMAINING — historical and prepayment status.
• SLA_LEDGER_CASH_BASIS_FLAG — cash-basis indicator from GL_LEDGERS.

Common Use Cases and Queries

Typical uses include identifying approved but untransferred project invoice distributions, reconciling AP to PA during period close, and investigating why an invoice line is missing from Project Expenditure Inquiry.

• List untransferred, approved distributions for a project:

  SELECT invoice_num, distribution_line_number, amount, project_id
  FROM   pa_proj_ap_inv_distributions
  WHERE  project_id = :project_id
  AND    approved_flag = 'Y';

• Find posted versus unposted lines: filter on posted_flag.
• Review commitment/variance detail for a specific invoice by invoice_num and currency_code.

Because the view calls PL/SQL functions per row, queries should be filtered tightly to avoid performance issues.