Search Results pa_projects_expend_v




Overview
Explore in AI Chat

Overview

PA_PROJECTS_EXPEND_V is a Projects (PA) module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to present the set of projects to which expenditures can be charged. Unlike a simple projection over PA_PROJECTS_ALL, the view applies a substantial body of business rules at the database layer so that only expenditure-enabled projects are returned. This makes it a reliable source for validation logic, cross-module integrations, and reporting that must respect the same eligibility criteria enforced by the expenditure entry forms.

The view is particularly significant because it is security-aware and cross-charge-aware. It joins project definitions against the current operating unit context derived from PA_IMPLEMENTATIONS, and it honours the ALLOW_CROSS_CHARGE_FLAG on the project along with provider/receiver organization relationships. It also filters out projects that are not permitting charges and projects flagged as cross-charge providers. As a result, the view answers the practical question: "Given the current operating unit, which projects may I charge an expenditure to?"

Underlying Base Objects

The documented base objects referenced by PA_PROJECTS_EXPEND_V are a mix of tables, synonyms, views, and packages:

  • PA_PROJ_ALL_BASIC_UN_SEC_V (VIEW) – supplies the core project attributes; it is the secured/unsecured basic project view that the outer query selects from as alias P.
  • PA_IMPLEMENTATIONS (SYNONYM) – supplies the implementation row for the current operating unit, exposing ORG_ID, SET_OF_BOOKS_ID, and the cross-charge flags such as CC_ALLOW_IU_FLAG.
  • HR_ORGANIZATION_INFORMATION (SYNONYM) – joined as PLE (and in some branches as RLE) for the OPERATING UNIT INFORMATION context, used to compare the operating unit classification (ORG_INFORMATION2) between provider and receiver organizations.
  • PA_CC_ORG_RELATIONSHIPS (SYNONYM) – holds provider/receiver cross-charge relationships and the PRVDR_ALLOW_CC_FLAG that determines whether cross-charging between two operating units is permitted.
  • PA_BUDGETARY_CONTROL_OPTIONS (SYNONYM) – used in the EXISTS branch to allow projects where budgetary control is not enabled.
  • PA_PROJECTS_ALL (SYNONYM) – referenced within the budgetary control subquery to correlate PROJECT_ID.
  • PA_CROSS_BUSINESS_GRP (PACKAGE) and PA_PROJECT_UTILS (PACKAGE) – supporting PL/SQL packages referenced by the view definition.
  • FND_PROFILE (PACKAGE) – the standard profile option access package, typically used to resolve the current operating unit.

The joins are all outer joins in critical places, ensuring that missing organization information does not silently remove otherwise valid projects.

Key Columns

Common Use Cases and Queries

The view is commonly used to populate expenditure entry LOVs, validate chargeability in interfaces, and drive cross-module reporting where only chargeable projects should appear.

  • Listing chargeable projects for the current operating unit and currency.
  • Validating that a project number supplied by an external system is chargeable before inserting expenditure rows.
  • Reporting on projects that allow cross-charging into the current operating unit.

Sample query listing chargeable projects:

  • SELECT project_number, project_name, project_currency_code, allow_cross_charge_flag FROM pa_projects_expend_v WHERE org_id = :p_org_id AND NVL(start_date, SYSDATE) <= SYSDATE AND NVL(completion_date, SYSDATE) >= SYSDATE ORDER BY project_number_sort_order;
  • SELECT COUNT(*) FROM pa_projects_expend_v WHERE project_number = :p_project_number;
  • SELECT DISTINCT v.project_number, v.project_name FROM pa_projects_expend_v v WHERE v.allow_cross_charge_flag = 'Y' AND v.expenditure_org_id = :p_exp_org_id;

Because the view enforces operating unit, cross-charge, and budgetary control rules internally, queries should not attempt to re-implement that filtering; callers only need to supply the correct operating unit context.