Search Results pa_gl_dr_except_sum_v




Overview
Explore in AI Chat

Overview

PA_GL_DR_EXCEPT_SUM_V is an APPS-owned database view within the Oracle E-Business Suite Projects (PA) module. Its purpose is to identify Draft Revenues that prevent a Projects period from being closed. When revenue generated in Oracle Projects has not been interfaced to Oracle General Ledger, Oracle Projects cannot complete the period-close process; this view surfaces the specific draft revenue records responsible for that blockage and presents them in a summarized, actionable form.

The view returns summary information on draft revenues that have not been interfaced to GL and are therefore preventing the close of a period. It is a reconciliation and diagnostics aid rather than a transactional entry object — it exposes the offending records, the amounts involved, the GL period affected, and human-readable explanations of the underlying exception. Both the EXCEPTION_REASON and CORRECTIVE_ACTION columns are derived at query time through calls to the PA_EXCEPTION_REASONS_PUB package, so the output text reflects the delivered exception messaging for revenue exceptions. The view is valid and available in both 12.1.1 and 12.2.2 environments, and is exposed through the GL_INTERFACE synonym chain and associated GL and PA base objects.

Underlying Base Objects

The view is defined over several synonym-backed base tables and two PL/SQL packages. The core transactional sources are PA_DRAFT_REVENUES_ALL (the draft revenue header) and PA_DRAFT_REVENUE_ITEMS (the revenue distribution lines that carry the amounts summed by the view). PA_IMPLEMENTATIONS_ALL supplies the implementation settings, including the INTERFACE_REVENUE_TO_GL_FLAG controlling whether revenue transfers to GL and the SAME_PA_GL_PERIOD flag. GL_PERIOD_STATUSES provides the GL period name and date boundaries, restricted to APPLICATION_ID = 8721 (the Oracle Projects application) with ADJUSTMENT_PERIOD_FLAG = 'N'.

  • PA_DRAFT_REVENUES_ALL — draft revenue headers, transfer status and rejection reason.
  • PA_DRAFT_REVENUE_ITEMS — revenue lines whose AMOUNT values are aggregated by SUM.
  • PA_IMPLEMENTATIONS_ALL — implementation flags and set of books context.
  • GL_PERIOD_STATUSES — GL period name and date range used to bucket revenue by period.
  • PA_EXCEPTION_REASONS_PUB — package function GET_EXCEPTION_TEXT supplying exception and corrective-action text.
  • PA_EXPENDITURES_UTILS — package function GETORGTLNAME returning the operating unit name.
  • GL_INTERFACE, GL_JE_CATEGORIES, GL_JE_SOURCES — GL-side objects referenced in the interface context.

Key Columns

  • DRAFT_REVENUE_NUM / PROJECT_ID / AGREEMENT_ID — identify the offending draft revenue, project, and agreement.
  • GL_DATE / PERIOD_NAME — the accounting date and GL period into which the revenue should have been interfaced.
  • AMOUNT — the summed revenue item amount held up for that draft revenue.
  • TRANSFER_STATUS_CODE / TRANSFER_REJECTION_REASON — the transfer state driving the exception.
  • EXCEPTION_REASON / CORRECTIVE_ACTION — descriptive text generated via PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT.
  • ORG_ID / OU_NAME — operating unit identifier and its translated name.
  • SAME_PA_GL_PERIOD / SET_OF_BOOKS_ID — implementation and ledger context.

Common Use Cases and Queries

The principal use is diagnosing why a Projects period will not close. A typical query lists all blocking draft revenues for a ledger and period:

  • SELECT project_id, draft_revenue_num, period_name, amount, exception_reason, corrective_action FROM pa_gl_dr_except_sum_v WHERE set_of_books_id = :ledger_id AND period_name = :period ORDER BY amount DESC;
  • Filter by operating unit to route remediation: WHERE org_id = :org_id.
  • Report totals per period to quantify exposure: SELECT period_name, SUM(amount) FROM pa_gl_dr_except_sum_v GROUP BY period_name;

Because the view aggregates revenue items and unions summary results, it is suited to exception reporting and period-close dashboards rather than line-level drill-down.