Search Results pa_draft_revenues




Overview
Explore in AI Chat

Overview

APPS.PA_DR_EXCEPTIONS_SUM_V is a reporting view in Oracle Projects (PA) that consolidates draft revenue records which require attention before they can be successfully transferred to Oracle General Ledger or interfaced downstream. The name follows the standard Projects naming convention: PA_DR denotes Draft Revenue, EXCEPTIONS identifies records flagged with abnormal transfer status, and SUM_V confirms that the view aggregates amounts and is read-only. It presents a summarized, reason-annotated exception report rather than raw transaction detail.

The view is relevant only when the PA implementation is configured to interface revenue to GL, as enforced by the predicate on PA_IMPLEMENTATIONS. This makes it a diagnostic companion to the revenue generation and interface processes (PRC: Generate Draft Revenue and PRC: Interface Revenue to GL), giving implementers and functional users a single source for identifying blocked or rejected draft revenue.

Underlying Base Objects

The documented base objects referenced by the view are PA_DRAFT_REVENUES, PA_DRAFT_REVENUE_ITEMS, PA_IMPLEMENTATIONS, PA_PERIODS, XLA_EVENTS, and the PA_EXCEPTION_REASONS_PUB package. Synonyms for GL_INTERFACE, GL_JE_CATEGORIES, and GL_JE_SOURCES are also listed in the metadata, reflecting the revenue-to-GL interface context, though they are not referenced explicitly in the visible SELECT text.

PA_DRAFT_REVENUES is the driving table, supplying the draft revenue header key (DRAFT_REVENUE_NUM, PROJECT_ID), the accounting date (PA_DATE), agreement, transfer status, rejection reason, generation error flag, and the EVENT_ID used in the second UNION branch. PA_DRAFT_REVENUE_ITEMS provides the detail amounts summed in the AMOUNT column. PA_PERIODS resolves the accounting period name through a BETWEEN join on start and end date. PA_IMPLEMENTATIONS gates the output via INTERFACE_REVENUE_TO_GL_FLAG. XLA_EVENTS is joined in the second branch to detect subledger accounting events whose status or process status is not yet Processed. PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT is called twice to translate a status and reason code pair into readable exception text and corrective action.

The view is a UNION of two logical sets: revenue lines whose transfer status is Pending, Rejected, Exception, or Transferred (P, R, X, T), and lines with transfer status Accepted (A) whose associated XLA event remains incomplete. A GROUP BY over the draft revenue key, date, period, agreement, and reason collapses items into a summarized amount per exception.

h4>Key Columns
  • DRAFT_REVENUE_NUM / PROJECT_ID — Composite identifier of the draft revenue and its project; the drill-down path back to PA_DRAFT_REVENUES.
  • PA_DATE — Accounting date of the draft revenue, used to derive the period.
  • PERIOD_NAME — Accounting period from PA_PERIODS, the natural reporting axis for exception aging.
  • AGREEMENT_ID — Revenue agreement associated with the draft revenue.
  • AMOUNT — Sum of the underlying PA_DRAFT_REVENUE_ITEMS amounts, showing the revenue value at risk.
  • TRANSFER_REJECTION_REASON — Raw code explaining why the transfer failed.
  • EXCEPTION_REASON — Translated exception text obtained from PA_EXCEPTION_REASONS_PUB for the REV_EXCP exception type.
  • CORRECTIVE_ACTION — Corresponding guidance text returned by the same package, instructing the user how to resolve the exception.

Common Use Cases and Queries

The principal use case is a period-end reconciliation listing every draft revenue that cannot reach GL, with its amount and remediation guidance. A secondary use case is exception aging by accounting period, and a third is identifying accepted revenues whose XLA events are stuck.

A representative query returning open exceptions for a period:

  • SELECT project_id, draft_revenue_num, period_name, amount, exception_reason, corrective_action FROM apps.pa_dr_exceptions_sum_v WHERE period_name = 'JAN-24' ORDER BY amount DESC;
  • SELECT period_name, exception_reason, SUM(amount) total_at_risk, COUNT(*) exception_count FROM apps.pa_dr_exceptions_sum_v GROUP BY period_name, exception_reason ORDER BY period_name;
  • SELECT draft_revenue_num, project_id, pa_date, amount, exception_reason FROM apps.pa_dr_exceptions_sum_v WHERE agreement_id = :p_agreement_id;

These queries support the revenue interface troubleshooting workflow: isolate the exception, read the corrective action text, resolve the underlying condition, and regenerate or reinterface the draft revenue.