Search Results pa_projects_all_expend_v




Overview
Explore in AI Chat

Overview

PA_PROJECTS_ALL_EXPEND_V is a security-enabled Oracle Projects view owned by the APPS schema. Its documented purpose is to present the set of projects against which a user is authorized to perform searches, specifically to support List of Values (LOV) pickers in Oracle Subledgers (such as Payables, Purchasing, and other expenditure-capturing modules). Unlike a plain denormalized lookup, this view is not a simple projection of PA_PROJECTS_ALL; it embeds a substantial amount of filtering logic that enforces project status, template, project type, cross-charge, and function-based security rules at query time. As a result, the view is a functional dependency of subledger expenditure entry, since it governs which projects a user may select when charging expenditure to a project.

Underlying Base Objects

The view is defined over a join of several base objects. The principal table is PA_PROJECTS_ALL, aliased P, which supplies project identity and descriptive attributes. PA_PROJECT_TYPES_ALL (aliased PT) is joined on PROJECT_TYPE and ORG_ID to bring in the project type class code, the cross-charge provider flag, and to exclude project types whose CC_PRVDR_FLAG is 'Y' or whose PROJECT_TYPE is 'AWARD_PROJECT'. HR_ORGANIZATION_INFORMATION is referenced twice, as PLE and RLE, both filtered to ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION', and HR_ALL_ORGANIZATION_UNITS (aliased HR) is joined to PA_IMPLEMENTATIONS_ALL (IMP) on ORGANIZATION_ID. A second instance of PA_IMPLEMENTATIONS_ALL (IALL) is joined on ORG_ID to the project, establishing the receiving operating unit. The view also calls three PL/SQL dependencies at runtime: PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (to verify the project status permits new transactions), PA_SECURITY.ALLOW_QUERY (to enforce project security), and FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP') (to control cross-business-group visibility). Cross-charge relationships are resolved through PA_CC_ORG_RELATIONSHIPS.

Key Columns

The view exposes project identification and descriptive columns including PROJECT_ID, SEGMENT1 (the project number), NAME, DESCRIPTION, and PM_PROJECT_REFERENCE, together with ORG_ID identifying the operating unit. Scheduling and financial attributes include START_DATE, COMPLETION_DATE, PROJECT_CURRENCY_CODE, PROJECT_RATE_DATE, and PROJECT_RATE_TYPE. Status and control columns include PROJECT_STATUS_CODE, PROJECT_TYPE, PROJECT_TYPE_CLASS_CODE, and the NVL'd TEMPLATE_FLAG. Cross-charge columns include ALLOW_CROSS_CHARGE_FLAG, CC_PRVDR_FLAG, and CC_ALLOW_IU_FLAG. The view also exposes HR.NAME (the operating unit name), SET_OF_BOOKS_ID (from both implementation aliases), and the project name column. Note that SEGMENT1 appears twice in the projection, reflecting the source join.

Common Use Cases and Queries

The most common use is as the LOV source for subledger expenditure forms and custom reports requiring the same security-filtered project list. A typical query selects the identifying and descriptive columns for a given operating unit:

  • SELECT project_id, segment1, name, project_status_code, project_type_class_code FROM pa_projects_all_expend_v WHERE org_id = :org_id ORDER BY segment1;
  • SELECT segment1, name FROM pa_projects_all_expend_v WHERE segment1 LIKE :prefix || '%' AND org_id = :org_id;
  • SELECT project_id, set_of_books_id, project_currency_code FROM pa_projects_all_expend_v WHERE project_type_class_code = :class_code;

Because the view invokes PA_SECURITY.ALLOW_QUERY and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED for every row evaluated, queries against it can be expensive; restricting by ORG_ID and filtering on SEGMENT1 is advisable. The view returns only non-template projects whose status permits new transactions, so it is unsuitable as a general-purpose project master query — use PA_PROJECTS_ALL for that purpose.