Search Results pa_cost_distribution_lines_n7

Overview

APPS.PA_COST_EXCEPTIONS_SUM_V is a reporting view in Oracle EBS Projects (PA) that consolidates cost distribution lines which have failed or are pending transfer to General Ledger. It surfaces expenditure transactions whose TRANSFER_STATUS_CODE falls into the pending/rejected exception set, presenting each affected cost distribution line alongside its source expenditure item, the accounting period it belongs to, and human-readable exception reason and corrective action text. The view acts as the primary diagnostic surface for the "Cost Exceptions" workflow in Project Costing, allowing users and integrators to identify rejected distributions, understand why they were rejected, and obtain the corrective guidance maintained in the exception reason setup.

Because the view joins cost distribution lines, expenditure items, implementation options, and period definitions, it reconciles transaction-level detail with the period table required for GL transfer eligibility. The cost_distribution_line_num referenced by the user corresponds to the LINE_NUM column of PA_COST_DISTRIBUTION_LINES_ALL, exposed here as COST_DISTRIBUTION_LINE_NUM; it is the unique line identifier within an expenditure item's distribution set and is the column most commonly used when troubleshooting individual rejected lines.

Underlying Base Objects

The documented view definition selects from four primary objects and references several auxiliary ones:

• PA_COST_DISTRIBUTION_LINES_ALL (alias CDL) — the driving table, providing line number, PA date, transfer status, transfer rejection reason, quantities, and raw/project amounts.
• PA_EXPENDITURE_ITEMS_ALL (alias EI) — supplies expenditure item identity, dates, expenditure type, vendor, invoice, and document linkage attributes.
• PA_IMPLEMENTATIONS (alias IMP) — constrains the result set to organizations whose interface flags permit transfer for the relevant SYSTEM_LINKAGE_FUNCTION.
• PA_PERIODS_ALL (alias PRD) — provides the accounting period name by range-joining on PA_DATE.
• PA_EXCEPTION_REASONS_PUB (package) — called via GET_EXCEPTION_TEXT to translate status and rejection codes into exception reason and corrective action text.
• Additional documented references include PA_PERIODS, PA_PROJECTS_ALL, PA_PROJECT_TYPES_ALL, PA_PROJECT_UTILS, and XLA_EVENTS, the latter supporting subledger accounting event context.

The view is defined as a UNION of two branches, each tuned with a LEADING/USE_NL hint against the PA_COST_DISTRIBUTION_LINES_N7 index, reflecting the original optimization for exception-driven access paths.

Key Columns

• COST_DISTRIBUTION_LINE_NUM — the CDL line number, the user's search term; identifies the specific distribution line.
• EXPENDITURE_ITEM_ID, EXPENDITURE_ITEM_DATE, EXPENDITURE_TYPE — identity and classification of the source expenditure.
• TASK_ID, PA_DATE, PERIOD_NAME — project task, expenditure date, and the accounting period the line maps to.
• TRANSFER_STATUS_CODE ('P', 'R', 'X') and TRANSFER_REJECTION_REASON — the exception state codes.
• EXCEPTION_REASON and CORRECTIVE_ACTION — decoded text derived from PA_EXCEPTION_REASONS_PUB.
• AMOUNT, DENOM_AMOUNT, PROJECT_AMOUNT, QUANTITY — raw, denominated, and project currency values.
• CDL_EXCEPTION_TYPE — derived via DECODE on SYSTEM_LINKAGE_FUNCTION (ER/VI map to 'P', otherwise 'I').
• VENDOR_ID, INVOICE_ID, INVOICE_LINE_NUMBER, INVOICE_DISTRIBUTION_ID — Payables sourcing detail.

Common Use Cases and Queries

The view supports cost exception inquiries, reconciliation of unposted costs, and integration diagnostics. A representative query filtering on the distribution line number is:

SELECT cost_distribution_line_num, task_id, expenditure_item_id,
       period_name, transfer_status_code,
       exception_reason, corrective_action, amount
FROM   apps.pa_cost_exceptions_sum_v
WHERE  cost_distribution_line_num = :line_num
AND    org_id = :org_id;

Grouping by period and exception reason produces a management summary of the unposted cost backlog:

SELECT period_name, exception_reason, COUNT(*) lines, SUM(project_amount) amt
FROM   apps.pa_cost_exceptions_sum_v
GROUP  BY period_name, exception_reason;

Because the view returns only pending, rejected, or in-progress transfer statuses, it is well suited to daily exception monitoring reports and to integrations that must resolve rejects before the period close.