Search Results published_flag




Overview
Explore in AI Chat

Overview

PA_FIN_PERCENT_COMP_HIST_V is an APPS-owned database view within the Oracle E-Business Suite Projects (PA) module. It presents a denormalized, query-ready projection of financial task percent-complete history and progress rollup data for project structures. The view consolidates information from the project element version hierarchy, percent-complete transaction records, and progress rollup records, exposing a single row per eligible financial task or structure element across the effective or working structure version.

The view is intended primarily for reporting and integration rather than transactional processing. It restricts output to structures of structure type 6 (financial structures), excludes linked tasks via the LINK_TASK_FLAG <> 'Y' predicate, and applies a strict structure-status filter so that only the latest effective published version, or the current working version where no published version yet exists, is returned. This makes the view suitable for percent-complete dashboards, progress analysis extracts, and downstream interfaces that must reflect a single, unambiguous version of the structure at any point in time.

Underlying Base Objects

The view is defined over eight documented base objects. The structural backbone is formed by PA_PROJ_ELEMENTS, PA_PROJ_ELEMENT_VERSIONS, and PA_PROJ_ELEM_VER_STRUCTURE, joined on project and element identifiers to resolve each element to its active structure version. PA_PROJ_STRUCTURE_TYPES and PA_STRUCTURE_TYPES supply the structure classification used to constrain the result set to financial structures. Transactional percent-complete data is sourced from PA_PERCENT_COMPLETES, while aggregate progress information is supplied by PA_PROGRESS_ROLLUP. The PA_PROJ_ELEMENTS_UTILS package is invoked at the column level through CHECK_IS_FINANCIAL_TASK to flag elements that qualify as financial tasks.

The join predicates enforce versioning integrity: PPE.PROJ_ELEMENT_ID equals PPV.PROJ_ELEMENT_ID and PPE.PROJECT_ID equals PPV.PROJECT_ID, and PPV.PARENT_STRUCTURE_VERSION_ID equals PPVS.ELEMENT_VERSION_ID. A NOT EXISTS subquery against PA_PROJ_ELEM_VER_STRUCTURE (aliased STR2) suppresses working structures whenever a published structure already exists for the same project and element.

Key Columns

  • Element identifier and name — Derived via DECODE: for PA_TASKS the element number and name from PA_PROJ_ELEMENTS are returned; for PA_STRUCTURES the version number and name from PA_PROJ_ELEM_VER_STRUCTURE are used.
  • OBJECT_TYPE — Distinguishes whether the row represents a task (PA_TASKS) or a structure (PA_STRUCTURES).
  • PERCENT_COMPLETE_ID and PROGRESS_ROLLUP_ID — Surrogate keys from PA_PERCENT_COMPLETES and PA_PROGRESS_ROLLUP respectively.
  • AS_OF_DATE — NVL of PPC.DATE_COMPUTED and PPR.AS_OF_DATE, providing the effective timestamp for the reported percentage.
  • COMPLETED_PERCENTAGE — NVL chain preferring the percent-complete transaction value, then the rollup completed percentage, then the effective rollup percent complete.
  • CURRENT_FLAG — NVL of PPC.CURRENT_FLAG, falling back to a DECODE over PPR.CURRENT_FLAG that normalizes 'Y' to 'Y' and all other values to 'N'.
  • PUBLISHED_FLAG — NVL of PPC.PUBLISHED_FLAG, falling back to a DECODE over PPR.STRUCTURE_VERSION_ID where NULL yields 'Y' and non-NULL yields 'N'.
  • DESCRIPTION and PROGRESS_COMMENT — Free-text fields carried from PA_PERCENT_COMPLETES.

Common Use Cases and Queries

The view is typically used to report the latest percent-complete picture for financial tasks, to identify unpublished progress records, and to feed progress data into external reporting layers. Because the PUBLISHED_FLAG column is exposed directly, a frequent pattern is filtering on publication status:

  • SELECT project_id, completed_percentage, as_of_date FROM pa_fin_percent_comp_hist_v WHERE published_flag = 'Y' AND object_type = 'PA_TASKS'; — returns published percent-complete values for financial tasks.
  • SELECT project_id, percent_complete_id, published_flag, current_flag FROM pa_fin_percent_comp_hist_v WHERE current_flag = 'N'; — surfaces superseded percent-complete records for audit or reconciliation.
  • SELECT object_type, COUNT(*) FROM pa_fin_percent_comp_hist_v GROUP BY object_type; — validates the balance of task versus structure rows in the financial structure history.

Queries should generally constrain on PROJECT_ID or PROJ_ELEMENT_ID to avoid scanning the full financial structure population, and consumers should be aware that the NVL/DECODE logic means PUBLISHED_FLAG and CURRENT_FLAG may originate from either the percent-complete record or the progress rollup record depending on which source is populated.