Results for “pa_draft_revenues_all”

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

Overview

PA_DRAFT_REVENUES_ALL is the Oracle Projects (PA) transactional table that stores header-level information for draft revenue generated against projects. Each row represents a single draft revenue record produced by the revenue generation process, capturing the accounting context, transfer status, and the unbilled receivable (UBR) and unearned revenue (UER) postings that eventually flow to Oracle General Ledger. The table operates as the parent header for the unbilled and unearned lines held in PA_DRAFT_REVENUE_ITEMS, and it carries the organizational context through the ORG_ID column, making it an _ALL (multi-org) table secured by Oracle's Multi-Org architecture.

The object exists in both Oracle EBS 12.1.1 and 12.2.2 with the same 48-column documented physical schema, owned by the PA schema. Based on the foreign key topology mined from the ETRM metadata, the heuristic Data Vault classification is satellite-leaning. This designation reflects that the table is heavily dependent on parent entities — it references PA_PROJECTS_ALL, PA_AGREEMENTS_ALL, and GL_CODE_COMBINATIONS rather than acting as an independent hub or a pure intersection link. In a Data Vault model, the project and agreement references would typically be treated as descriptive context of a revenue event satellite.

Key Information Stored

The documented primary key is PA_DRAFT_REVENUES_PK, defined as the composite of PROJECT_ID and DRAFT_REVENUE_NUM. Two unique indexes refine this: PA_DRAFT_REVENUES_U1 (the business key on PROJECT_ID, DRAFT_REVENUE_NUM) and PA_DRAFT_REVENUES_U2 on the surrogate EVENT_ID. The most significant columns include:

• PROJECT_ID — Identifier of the project to which the draft revenue belongs; part of the composite business key.
• DRAFT_REVENUE_NUM — Sequential draft revenue number within a project; the second component of the business key.
• AGREEMENT_ID — Reference to the project agreement that governs the revenue.
• TRANSFER_STATUS_CODE — Indicates whether the draft revenue has been transferred to GL.
• UNBILLED_CODE_COMBINATION_ID and UNEARNED_CODE_COMBINATION_ID — GL code combinations for the UBR and UER accounting entries.
• UBR_UER_PROCESS_FLAG, UBR_SUMMARY_ID, UER_SUMMARY_ID — Track the unbilled receivable and unearned revenue summarization process.
• GL_DATE, GL_PERIOD_NAME, PA_PERIOD_NAME, PA_DATE — Accounting and project period context.
• DRAFT_REVENUE_NUM_CREDITED — Self-referencing column identifying the draft revenue that was credited by an adjustment.
• ADJUSTING_REVENUE_FLAG — Flags the record as an adjusting revenue entry.
• ORG_ID — Operating unit identifier for multi-org security.
• REVTRANS_UBR_DR, REVTRANS_UER_CR, REVTRANS_CURRENCY_CODE — Revenue transfer amounts and currency.
• REALIZED_GAINS_AMOUNT and REALIZED_LOSSES_AMOUNT — Foreign exchange gain/loss amounts with associated CCIDs.

Common Use Cases and Queries

Revenue analysts and project accountants query this table to audit the status of draft revenue, reconcile UBR/UER balances, and investigate transfer failures to GL. A typical pattern retrieves draft revenue for a project within a period:

• Listing draft revenues by project and period: SELECT DRAFT_REVENUE_NUM, TRANSFER_STATUS_CODE, GL_PERIOD_NAME FROM PA_DRAFT_REVENUES_ALL WHERE PROJECT_ID = :project_id;
• Identifying records not yet transferred: filtering on TRANSFER_STATUS_CODE combined with GL_DATE.
• Detecting generation failures through GENERATION_ERROR_FLAG and TRANSFER_REJECTION_REASON.
• Reporting UBR/UER postings by joining on the code combination IDs to GL_CODE_COMBINATIONS.
• Reconciling adjustments using DRAFT_REVENUE_NUM_CREDITED to trace credit relationships.

Because the table is multi-org, queries should include ORG_ID or rely on MOAC security profiles to restrict results to the appropriate operating unit.

Related Objects

The ETRM relationships establish this table's position within the Projects revenue model. Significant related objects include:

• PA_PROJECTS_ALL — Joined on PA_DRAFT_REVENUES_ALL.PROJECT_ID = PA_PROJECTS_ALL.PROJECT_ID; supplies project context.
• PA_AGREEMENTS_ALL — Joined on AGREEMENT_ID; provides agreement details.
• PA_DRAFT_REVENUE_ITEMS — Child detail lines keyed on PROJECT_ID and DRAFT_REVENUE_NUM.
• GL_CODE_COMBINATIONS — Referenced twice, via UNBILLED_CODE_COMBINATION_ID and UNEARNED_CODE_COMBINATION_ID.
• PA_DISTRIBUTION_WARNINGS — Depends on this table through PROJECT_ID and DRAFT_REVENUE_NUM.
• PA_MC_DRAFT_REVS_ALL — Multi-currency counterpart keyed on the same PROJECT_ID and DRAFT_REVENUE_NUM.
• PA_DRAFT_REVENUES_ALL (self-reference) — Via PROJECT_ID and DRAFT_REVENUE_NUM_CREDITED for adjustment linkages.

Together these relationships position PA_DRAFT_REVENUES_ALL as the central header table for analyzing project revenue generation and its downstream GL transfer.