Search Results pjm_projects_all_ou_v




Overview
Explore in AI Chat

Overview

The APPS.PJM_PROJECTS_ALL_OU_V view is a Project Manufacturing (PJM) reporting object that consolidates project-level operating unit information into a single queryable structure. Its primary purpose is to expose a unified list of projects across multiple operating units while explicitly excluding award projects and project templates. The view is defined with a UNION ALL of two distinct data sources, which enables it to blend standard Oracle Projects records with Seiban numbering data maintained within the PJM module.

From a reporting and integration perspective, this view simplifies cross-operating-unit lookups that would otherwise require joining multiple base tables and applying filtering logic. Because the view pre-filters PROJECT_TYPE <> 'AWARD_PROJECT' and excludes rows where TEMPLATE_FLAG = 'Y', consumers of the view obtain a working set of projects suitable for manufacturing and Seiban-related transactions without additional predicate logic. The view carries a VALID status in the APPS schema and is available in Oracle EBS 12.1.1 and 12.2.2.

Underlying Base Objects

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

  • PA_PROJECTS_ALL (SYNONYM) — the primary Oracle Projects table containing all project definitions across operating units. The view selects from this source with the filter PROJECT_TYPE <> 'AWARD_PROJECT' and NVL(TEMPLATE_FLAG, 'N') <> 'Y', returning a SEIBAN_NUMBER_FLAG of 2.
  • PJM_SEIBAN_NUMBERS (SYNONYM) — the Project Manufacturing Seiban number table. Rows from this source are unioned in with a SEIBAN_NUMBER_FLAG of 1, drawing the project identifier, number, and name from Seiban columns and sourcing OPERATING_UNIT from the Seiban record.

The UNION ALL preserves all rows from both branches without deduplication, and both branches are normalized into a consistent column layout so that downstream consumers cannot distinguish the origin except through the Seiban flag.

Key Columns

  • PROJECT_ID — the unique project identifier; sourced from P.PROJECT_ID for Projects rows and S.PROJECT_ID for Seiban rows.
  • PROJECT_NUMBER — the project number, taken from SEGMENT1 for Projects rows and PROJECT_NUMBER for Seiban rows.
  • PROJECT_NAME — the project name; in the Seiban branch this is populated from PROJECT_NAME.
  • PROJECT_DESCRIPTION — descriptive text; for Seiban rows the project name is reused as the description.
  • PROJECT_NUMBER_SORT_ORDER — a sortable project number; for Seiban rows this equals PROJECT_NUMBER.
  • START_DATE and COMPLETION_DATE — project dates from PA_PROJECTS_ALL; these are returned as TO_DATE(NULL) for all Seiban rows.
  • SEIBAN_NUMBER_FLAG — a discriminator column: 1 indicates a Seiban record, 2 indicates a standard project record.
  • OPERATING_UNIT — the operating unit identifier; from P.ORG_ID for standard projects and S.OPERATING_UNIT for Seiban rows.

Common Use Cases and Queries

This view is most commonly used to drive cross-operating-unit project lists in Project Manufacturing environments, particularly where Seiban numbering is in use. The explicit exclusion of award projects makes it appropriate for manufacturing and costing contexts rather than for award or grant management reporting.

A basic query retrieves all projects visible through the view:

SELECT PROJECT_ID, PROJECT_NUMBER, PROJECT_NAME, OPERATING_UNIT, SEIBAN_NUMBER_FLAG
FROM   APPS.PJM_PROJECTS_ALL_OU_V;

Isolating Seiban-backed projects uses the flag column:

SELECT PROJECT_NUMBER, PROJECT_NAME, OPERATING_UNIT
FROM   APPS.PJM_PROJECTS_ALL_OU_V
WHERE  SEIBAN_NUMBER_FLAG = 1;

Standard (non-Seiban) projects with active date ranges are obtained with:

SELECT PROJECT_NUMBER, PROJECT_NAME, START_DATE, COMPLETION_DATE
FROM   APPS.PJM_PROJECTS_ALL_OU_V
WHERE  SEIBAN_NUMBER_FLAG = 2
AND    COMPLETION_DATE IS NULL OR COMPLETION_DATE >= SYSDATE;

Because the view already filters award projects and templates, integrations and reports referencing it do not need to reapply those exclusions.