Results for “pspbv_effort_report_templates”

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

Overview

PSPBV_EFFORT_REPORT_TEMPLATES is a read-only Oracle EBS view belonging to the PSP (Labor Distribution) product family. It exposes saved Effort Report selection criteria so that administrators and reporting processes can identify and reuse configured templates for effort certification and labor distribution workflows. Each row represents a single stored template, capturing the criteria that determine which employees, assignments, and award or project attributes are included when an Effort Report is generated and initiated.

The view is documented in the ETRM metadata for release 12.1.1 and 12.2.2. Its description states that it "displays the Effort Report selection criteria that can be reused." The underlying DDL ends with the clause WITH READ ONLY, confirming that the view is intended strictly for query and reporting purposes, not for transactional maintenance. Template creation and maintenance occur through native PSP Effort Report template setup, while this view offers a SQL-accessible projection of that configuration. This distinction matters for developers searching by award_type, since the column surfaces the award-based filter that restricts a template's population.

Underlying Base Objects

The view text documents its source objects explicitly. It is defined over a join between PSP_EFFORT_REPORT_TEMPLATES PER and GL_CODE_COMBINATIONS GCC, joined as follows:

• PER.GL_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

Consequently, PSP_EFFORT_REPORT_TEMPLATES is the primary base table supplying nearly all columns, while GL_CODE_COMBINATIONS contributes the account structure required to resolve the funding account associated with each template. The view also embeds several lookup descriptors and a key flexfield reference, including _LA:ENABLE_WORKFLOW_FLAG:PSP_LOOKUPS:PSP_ENABLE_WORKFLOW_FLAG:MEANING, _LA:USER_TEMPLATE_FLAG:PSP_LOOKUPS:PSP_USER_TEMPLATE_FLAG:MEANING, _LA:REPORT_TYPE:PSP_LOOKUPS:PSP_REPORT_TYPE:MEANING, and _KF:SQLGL:GL#:GCC:_CO, which translate coded values into descriptive meanings and expose the concatenated accounting flexfield. The documented metadata notes that no additional base objects are recorded beyond these two.

Key Columns

The column list exposes both business-meaningful attributes and technical identifiers. Central to a search on award_type is the AWARD_TYPE column, which records the award classification used to scope a template. Related selection criteria include PROJECT_TYPE_CLASS_CODE, PROJECT_TYPE, FUNDING_SOURCE_ID, TASK_ID, EXPENDITURE_TYPE, and ELEMENT_TYPE_ID. Additional notable columns are:

• TEMPLATE_ID and TEMPLATE_NAME — primary identifier and descriptive label for each template.
• PERSONS_EFFECTIVE_START_DATE / PERSONS_EFFECTIVE_END_DATE — validity window of the template.
• EFFORT_REPORT_BEGIN_DATE and EFFORT_PERIOD_END_DATE — the reporting period boundaries.
• EFFORT_REPORT_PERIOD_NAME — the naming convention applied to the effort period.
• REPORT_INITIATOR_ID and ORACLE_APPL_USER_NAME — the initiator identity.
• SUPERVISOR_ID, PEOPLE_GROUP_ID, SET_OF_BOOKS_ID, BUSINESS_GROUP_ID — organizational and ledger context.
• Lookup-meaning columns (TYPE_OF_TEMPLATE, REPORT_TYPE, ENABLE_WORKFLOW_FLAG), DATESTAMP_FOR_WARNING_REPORT, and audit columns such as LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, and CREATION_DATE.

Common Use Cases and Queries

Typical use cases include auditing which templates target a particular award type, verifying period and validity settings before scheduling effort certification runs, and integrating template configuration into custom reporting or data extracts. The following query isolates templates by award type:

• SELECT TEMPLATE_ID, TEMPLATE_NAME, AWARD_TYPE, PROJECT_TYPE, EFFORT_REPORT_PERIOD_NAME, PERSONS_EFFECTIVE_START_DATE, PERSONS_EFFECTIVE_END_DATE FROM PSPBV_EFFORT_REPORT_TEMPLATES WHERE AWARD_TYPE = :award_type ORDER BY TEMPLATE_NAME;

A second pattern lists currently active templates with their effective window:

• SELECT TEMPLATE_NAME, PROJECT_TYPE_CLASS_CODE, AWARD_TYPE, FUNDING_SOURCE_ID, SET_OF_BOOKS_ID FROM PSPBV_EFFORT_REPORT_TEMPLATES WHERE SYSDATE BETWEEN PERSONS_EFFECTIVE_START_DATE AND PERSONS_EFFECTIVE_END_DATE;

Because the view is read-only, all output should be treated as reference data; changes to templates must be performed through the standard PSP Effort Report setup rather than through SQL against this view.