Search Results additional_information




Overview
Explore in AI Chat

Overview

PA_TEMPLATE_REQUIREMENTS_V is an APPS-owned database view within the Oracle Projects (PA) module. It presents a denormalized, reporting-friendly projection of project assignment requirements that originate from team templates. Rather than requiring report authors and integrators to join numerous Projects and shared reference tables manually, the view consolidates assignment attributes, template names, project role meanings, subteam details, location and calendar descriptions, and descriptive flexfield columns into a single queryable structure.

The view is particularly relevant when resolving subteam party information. Users searching for project_subteam_party_id will find this column exposed directly, alongside the related project_subteam_id and project_sub_team_name, which allows assignment-level reporting to be tied back to a specific subteam party record. Positions status is VALID, confirming it is a supported object in EBS 12.1.1 and 12.2.2. The view serves as a convenient read interface for both custom reports and integration extracts that must reflect template-driven staffing requirements without traversing the underlying normalized model.

Underlying Base Objects

The view is defined over several Projects synonyms and views, joined through inner and outer relationships. The core driving object is PA_PROJECT_ASSIGNMENTS (aliased ASGN), which supplies the assignment identity, dates, effort, role, status, calendar, work type, and flexfield attributes. PA_TEAM_TEMPLATES (TT) provides the assignment template name through the join on ASSIGNMENT_TEMPLATE_ID = TEAM_TEMPLATE_ID. PA_PROJECT_ROLE_TYPES (PRT) yields the project role meaning, while PA_PROJECT_STATUSES (PS) supplies the project status name.

Subteam information is sourced from PA_PROJECT_SUBTEAMS (PST) and PA_PROJECT_SUBTEAM_PARTIES (PSP), which are joined to expose project_subteam_id, project_sub_team_name, and project_subteam_party_id. Location data is sourced from PA_LOCATIONS (LOC), FND_TERRITORIES_TL (FTT), and JTF_CALENDARS_TL (CALTL), the latter two being outer-joined and language-filtered through USERENV('LANG'). Additional descriptive values come from PA_WORK_TYPES_V and PA_LOOKUPS. The package PA_ROLE_JOB_BG_UTILS is referenced in the object metadata as a supporting dependency for role and job logic.

Key Columns

  • assignment_id / assignment_name / assignment_number — Primary identifiers for the staffing assignment record.
  • project_subteam_party_id — The party identifier associated with the project subteam; used to trace staffing requirements to a specific subteam member or party record.
  • project_subteam_id / project_sub_team_name — The subteam key and its descriptive name.
  • project_role_id / project_role_name — Role definition and its translated meaning.
  • team_template_name — Name of the team template from which the requirement derives.
  • status_code / status_name — Assignment status code and project status description.
  • start_date, end_date, assignment_duration — Assignment window; duration is computed as TRUNC(end_date) - TRUNC(start_date) + 1.
  • assignment_effort, min_resource_job_level, max_resource_job_level — Effort and job-level constraints for the requirement.
  • calendar_id, calendar_name, calendar_type, resource_calendar_percent — Calendar context for the assignment.
  • attribute_category, attribute1–attribute15 — Descriptive flexfield columns for extensible reporting.

Common Use Cases and Queries

Typical uses include staffing requirement reports, template-driven assignment extracts, and integration feeds requiring subteam party detail. A query isolating subteam parties might read:

SELECT assignment_id, assignment_name,
       project_subteam_id, project_sub_team_name,
       project_subteam_party_id, project_role_name,
       start_date, end_date, assignment_effort
FROM   apps.pa_template_requirements_v
WHERE  project_subteam_party_id IS NOT NULL
ORDER BY assignment_id;

Reporting by template and status is equally common:

SELECT team_template_name, status_name,
       COUNT(*) requirement_count
FROM   apps.pa_template_requirements_v
GROUP BY team_template_name, status_name;

Because the view applies outer joins for location, territory, and calendar translations, columns such as city, region, and territory_short_name may be null; queries should therefore avoid restrictive predicates on those columns unless a value is mandatory.