Search Results pa_rep_reqt_projs_v

Overview

PA_REP_REQT_PROJS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PA (Projects) product family. It exposes a filtered list of projects that carry at least one open assignment, presenting each project's internal identifier, its descriptive name, its user-facing project number, and a concatenated display value combining name and number. The view was introduced to support reporting and integration scenarios—particularly those tied to project resource assignment and requirement workflows—where consumers need a concise, assignment-qualified project list rather than the full PA_PROJECTS_ALL population.

A frequent search term associated with this object is "project_name_number," reflecting the view's most operationally useful column, PROJECT_NAME_NUMBER. That column pre-formats the project name and project number into a single string using the pattern NAME(NUMBER), eliminating the need for downstream reports to assemble this label themselves. Because the view is a simple, read-only projection over base tables, it can be queried safely in custom reports, concurrent programs, Oracle Discoverer worksheets, OBIEE/XML Publisher data models, and integration extracts without affecting transactional data integrity.

Underlying Base Objects

The view is defined over two documented base objects, both referenced as synonyms in the APPS schema:

• PA_PROJECTS_ALL — the primary project master (aliased as PROJ in the view text). It supplies PROJECT_ID, NAME, and SEGMENT1, and is the driving table for the project attributes returned.
• PA_PROJECT_ASSIGNMENTS — the project assignment detail (aliased as ASMT). It is used exclusively in a correlated subquery to restrict the result set to projects having an assignment row where ASSIGNMENT_TYPE equals 'OPEN_ASSIGNMENT'.

The relationship is a semi-join rather than an outer join: a project appears in the view only if it has a qualifying assignment. The subquery applies ROWNUM = 1 to short-circuit evaluation once a matching assignment is found, so the view returns each project at most once even when multiple open assignments exist. Note that the ETRM description text references the internal name "pa_rep_asmt_projs_v," indicating the view is closely related to assignment-oriented reporting objects; the delivered implementation, however, is PA_REP_REQT_PROJS_V.

Key Columns

• PROJECT_ID — the unique numeric identifier of the project (PROJ.PROJECT_ID). This is the join key to PA_PROJECTS_ALL, PA_PROJECT_ASSIGNMENTS, and virtually all other PA transaction tables.
• PROJECT_NAME — the descriptive project name (PROJ.NAME), typically the long-form title used in user interfaces and reports.
• PROJECT_NUMBER — the user-facing project number (PROJ.SEGMENT1), the short identifier that users recognize and search on in EBS forms.
• PROJECT_NAME_NUMBER — a concatenation of the name and number in the form NAME(NUMBER), intended as a ready-made display label for LOVs, reports, and dashboards.

All four columns are derived directly from PA_PROJECTS_ALL except for the concatenated label; no aggregation or calculation beyond string concatenation occurs. Because PROJECT_ID is exposed, the view can be joined back to the base tables to retrieve additional descriptive or organizational attributes not included in the projection.

Common Use Cases and Queries

The view is most often used when a report or integration must list projects that are actively staffed or that have open resource assignments. Typical scenarios include resource-requirement reporting, assignment dashboards, LOV sources for custom forms, and validation queries in interfaces that only accept assignment-bearing projects.

• Listing all assignment-qualified projects with a formatted label:
  SELECT project_id, project_name_number FROM pa_rep_reqt_projs_v ORDER BY project_name_number;
• Resolving a user-supplied project number to its identifier:
  SELECT project_id, project_name FROM pa_rep_reqt_projs_v WHERE project_number = :p_number;
• Joining to assignment detail for a staffing report:
  SELECT v.project_name_number, a.assignment_id, a.assignment_type FROM pa_rep_reqt_projs_v v, pa_project_assignments a WHERE v.project_id = a.project_id AND a.assignment_type = 'OPEN_ASSIGNMENT';

Because the view internally filters on ASSIGNMENT_TYPE = 'OPEN_ASSIGNMENT', projects without open assignments—such as newly created or fully closed projects—will not appear. Report developers requiring the complete project population should query PA_PROJECTS_ALL directly. In Oracle EBS 12.1.1 and 12.2.2, the view definition is identical and is delivered under the APPS schema with VALID status; no editioning or partitioning differences apply to the view itself.