Results for “pspfv_effort_report_templates”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The database object PSPFV_EFFORT_REPORT_TEMPLATES is a view within the Oracle E-Business Suite (EBS) Labor Distribution module, operating under the PSP product code. This view serves as a comprehensive, denormalized reporting layer over the core Effort Report Templates entity, aggregating data from a wide array of HR, Payroll, Projects, and General Ledger tables. Because Effort Reporting is the mechanism by which an institution certifies that salaries charged to sponsored projects are reasonable and consistent with the actual work performed, this view is central to generating certification templates, auditing labor distribution, and driving the Effort Reporting workflow.

Notably, the ETRM documentation explicitly states that this view is "Not implemented in this database." This means it is a seeded object whose underlying query exists in the metadata but whose physical instantiation may depend on the deployment configuration of Labor Distribution. The user investigation for "people_group_name" confirms that the view exposes the People Group name via the PPG.GROUP_NAME column, aliased from PAY_PEOPLE_GROUPS, allowing reporting on effort templates segmented by People Group — a key driver for eligibility and workflow routing in Effort Reporting.

Underlying Base Objects

The view is defined over a complex join of transactional and setup tables. The driving table is PSP_EFFORT_REPORT_TEMPLATES PER, which holds the template header records. It is joined via outer joins to several supporting tables. PSP_EFFORT_REPORT_PERIODS supplies period start and active dates; PER_ALL_PEOPLE_F and PER_PEOPLE_F provide employee and supervisor details respectively; PAY_ELEMENT_TYPES_F supplies the element name for the template; PAY_PEOPLE_GROUPS supplies the People Group name; and PA_TASKS provides the task name. Additionally, GL_CODE_COMBINATIONS, GL_SETS_OF_BOOKS, and HR_ALL_ORGANIZATION_UNITS contribute accounting flexfield, ledger (Set of Books), and business group/Organization name data. The join conditions leverage effective-dating logic, using BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE for supervisor and element type relationships, and outer joins ((+) syntax) to preserve template rows where related reference data is absent.

Key Columns

• TEMPLATE_ID, TEMPLATE_NAME: Primary identifier and descriptive name of the Effort Report Template.
• PEOPLE_GROUP_ID, GROUP_NAME: The People Group identifier and its corresponding name, sourced from PAY_PEOPLE_GROUPS; the target of the user's "people_group_name" search.
• PERSON_ID, EMPLOYEE_NUMBER, FIRST_NAME, LAST_NAME: Employee details for the template's subject person.
• SUPERVISOR_ID, and the supervisor name fields PP1.FIRST_NAME/PP1.LAST_NAME: The designated certifying supervisor.
• EFFORT_REPORT_PERIOD_NAME, BEGIN_DATE, END_DATE: The reporting period association and its boundaries.
• SET_OF_BOOKS_ID, GL.NAME: The ledger context in which the template is generated.
• PROJECT_TYPE, AWARD_TYPE, FUNDING_SOURCE_ID, TASK_ID, TASK_NAME, EXPENDITURE_TYPE: Project and award attributes used to scope the effort certification.
• ELEMENT_TYPE_ID, ELEMENT_NAME: The payroll element being certified.
• REPORT_INITIATOR_ID, INITIATOR_APPS_NAME, ERROR_DATE_TIME: Workflow and error tracking metadata.
• Descriptive flexfield placeholders such as _LA:ENABLE_WORKFLOW_FLAG, _LA:USER_TEMPLATE_FLAG, and _LA:REPORT_TYPE: Lookup-driven attributes resolved from PSP_LOOKUPS.

Common Use Cases and Queries

Typical use cases include auditing template generation by People Group, validating supervisor assignments, and confirming that element and period data align with the certification cycle. A representative query for the user's search term is:

SELECT TEMPLATE_NAME, GROUP_NAME, EFFORT_REPORT_PERIOD_NAME,
       FIRST_NAME, LAST_NAME, PROJECT_TYPE, AWARD_TYPE
FROM   PSPFV_EFFORT_REPORT_TEMPLATES
WHERE  GROUP_NAME = :people_group_name
AND    EFFORT_REPORT_PERIOD_NAME = :period_name;

Because the view has not been confirmed as physically implemented, DBAs must verify its existence in a given instance; where absent, the equivalent query can be reconstructed from the base tables PSP_EFFORT_REPORT_TEMPLATES and PAY_PEOPLE_GROUPS. The view remains a valuable reference for building custom reports and integrations within Labor Distribution.