Search Results get_lowest_cardinality

Overview

PSP_TEMPLATE_SELECTION is an Oracle E-Business Suite PL/SQL package owned by the APPS schema that supports the Template Selection process within Oracle's Grants and Labor Distribution (PSP) functionality. Its principal business role is to build and refine the population of persons or assignments that will be processed by a given payroll action, resolving the intersection of the request template's selection rules against HR, Payroll, Projects, and Grants data. The package is declared with AUTHID CURRENT_USER, so execution of its SQL references objects through the invoking user's privileges and APPS synonyms.

The package is organized into two parallel families of logic: a person-oriented path and an assignment-oriented path. The assignment procedures were introduced under a UVA bug fix (bug 4429787), which extended the original person-centric processing to assignment-level selection.

Key Procedures and Functions

The documented procedures are grouped by purpose:

• INSERT_INTO_TEMPLATE_HISTORY — Establishes a history record for the current payroll action and returns the associated request identifier.
• RANGE_CODE — Produces the SQL fragment or range specification used to drive the selection query for a given payroll action.
• GET_FINAL_SELECTION_LIST — Returns the final computed selection list, distinguishing whether person or assignment granularity applies.
• GET_LOWEST_CARDINALITY — Determines the report template with the lowest cardinality for the person-based path, optimizing the driving query for a request, effort window, business group, and set of books.
• PREPARE_INITIAL_PERSON_LIST — Builds the initial person population for the effort window and organizational context.
• PRUNE_INITIAL_PERSON_LIST — Removes persons from the initial list who fall outside the selection parameters.
• APPLY_EXCLUSION_CRITERIA — Applies exclusion rules to the person list, eliminating records that should not proceed. This is the procedure most commonly targeted by tuning and investigation, since exclusions frequently account for unexpectedly small result sets.
• GET_ASG_LOWEST_CARDINALITY, PREPARE_INITIAL_ASG_LIST, PRUNE_INITIAL_ASG_LIST, and APPLY_ASG_EXCLUSION_CRITERIA — The assignment-level counterparts of the person procedures.
• APPLY_FF_FORMULA_EXCLUSION — Applies Fast Formula based exclusion logic to the candidate population.
• GET_PARAMETER_VALUE — Retrieves configuration parameter values used across the selection process.

Tables Accessed

The package reads and writes through APPS synonyms. Selection logic draws on PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F for the base person and assignment populations, PER_ASSIGNMENT_STATUS_TYPES for active or suspended status filtering, and PER_TIME_PERIODS for effort date context. Organizational and financial scoping uses HR_ASSIGNMENT_SETS and HR_ASSIGNMENT_SET_AMENDMENTS, while PAY_PAYROLL_ACTIONS supplies the payroll action driving execution and PSP_REPORT_TEMPLATES supplies the template definitions whose cardinality is evaluated. Project and award driven selection references PA_PROJECTS_ALL and GMS_AWARDS_ALL. Working and output data are persisted in PSP_PRE_GEN_DIST_LINES_HISTORY, PSP_DISTRIBUTION_LINES_HISTORY, PSP_ADJUSTMENT_LINES_HISTORY, and PSP_REPORT_ERRORS / PSP_REPORT_ERRORS_S for error capture and reporting.

Usage Notes

PSP_TEMPLATE_SELECTION is invoked during concurrent processing of labor distribution and template selection requests rather than directly from end-user forms. The typical flow is INSERT_INTO_TEMPLATE_HISTORY, then the lowest-cardinality or prepare/prune sequence, followed by the relevant exclusion procedure, and finally GET_FINAL_SELECTION_LIST to materialize the results. The package is referenced by one other package, confirming it is an internal worker rather than a public API.

Because the procedures are not classified as a public API, custom code should avoid calling them directly. When investigating why a payroll action selected fewer persons or assignments than expected, apply_exclusion_criteria and its assignment counterpart are the primary diagnostic targets, since exclusions are applied after the initial list is prepared and pruned. Execution should be monitored through PSP_REPORT_ERRORS, which records errors raised during processing.