Search Results pa_alloc_offset_projects_v

Overview

PA_ALLOC_OFFSET_PROJECTS_V is a reporting view owned by the APPS schema in Oracle EBS Projects (PA). Its purpose is to expose the subset of projects that are eligible to serve as offset projects during allocation processing. In Oracle Projects, allocation rules and allocation definitions can route burdened or distributed amounts to a designated offset project, and the list of candidates presented to the user must exclude project templates and must be restricted to projects whose current status permits the creation of new transactions.

Rather than storing any data of its own, the view layers two business filters over PA_PROJECTS: a template exclusion and a status-eligibility check performed through the PA_PROJECT_UTILS package. Because the filtering logic is encapsulated in the view, allocation setup forms, concurrent processes, and custom reporting all reference a single, consistent definition of a valid offset project. The view is documented as VALID and is exposed under APPS in both EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The view is defined over two documented objects: the PA_PROJECTS synonym (resolving to the PA_PROJECTS base table) and the PA_PROJECT_UTILS PL/SQL package. Its defining query is:

• PA_PROJECTS (SYNONYM): Supplies the project master rows, including project identifier, number, name, and operating unit. It is the driving table of the view.
• PA_PROJECT_UTILS (PACKAGE): Invoked as PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(PROJECT_STATUS_CODE, 'NEW_TXNS'). This function evaluates whether the project's status class and status code allow the 'NEW_TXNS' (new transactions) system action, returning 'Y' or 'N'.

The two predicates applied are PAP.TEMPLATE_FLAG <> 'Y' and the status function returning 'Y'. Because the status check is a PL/SQL call within the WHERE clause, the view is not a simple join and cannot be merged into a calling statement; the function is evaluated per candidate project row. The view also carries an ORG_ID reference, so results are scoped by operating unit (MOAC) when queried through standard EBS security constructs.

Key Columns

The documented column list maps to four attributes:

• PROJECT_ID: The internal unique identifier of the project (PA_PROJECTS.PROJECT_ID). This is the value typically stored on allocation setup records and used as the foreign key in downstream processing.
• PROJECT_NUMBER: The user-visible project number (PA_PROJECTS.SEGMENT1), used as the short reference in forms and reports.
• PROJECT_NAME: The descriptive project name (PA_PROJECTS.NAME).
• ORG_ID: The operating unit identifier (PA_PROJECTS.ORG_ID), which supports Multi-Org Access Control filtering in 12.1.1 and 12.2.2.

Note that the view text selects PAP.PROJECT_ID, PAP.SEGMENT1, PAP.NAME, and PAP.ORG_ID, while the presented column names are PROJECT_ID, PROJECT_NUMBER, PROJECT_NAME, and ORG_ID. Consumers should reference the view's column names rather than the underlying table column names.

Common Use Cases and Queries

The primary use case is populating a list of valid offset projects in allocation definitions and in custom allocation reporting. Typical queries include looking up the project name for an offset project already stored on an allocation rule, and validating that a stored offset project remains eligible after its status changes.

• List all eligible offset projects: SELECT project_id, project_number, project_name, org_id FROM apps.pa_alloc_offset_projects_v ORDER BY project_number;
• Resolve a known offset project: SELECT project_number, project_name FROM apps.pa_alloc_offset_projects_v WHERE project_id = :p_project_id;
• Validate eligibility of assigned offsets: SELECT a.offset_project_id FROM pa_alloc_rules a WHERE NOT EXISTS (SELECT 1 FROM apps.pa_alloc_offset_projects_v v WHERE v.project_id = a.offset_project_id); — rows returned are offset projects that fail the template or status test.
• Operating-unit scoped listing: SELECT project_number, project_name FROM apps.pa_alloc_offset_projects_v WHERE org_id = :p_org_id ORDER BY project_name;

Because eligibility is evaluated at query time, the result set changes as project statuses change. Any integration or extract that persists results should be re-run after status transitions rather than cached indefinitely.