Search Results first_order

Overview

PSP_TEMPLATE_SELECTION is an Oracle EBS Applications (APPS) package body that supports the Oracle Time and Labor / Payroll (PSP) template-driven reporting framework. Its primary business function is to resolve a selected report template and the associated selection criteria (effort date range, report layout, and sort/ordering instructions) into a concrete list of persons or assignments, and to persist that resolved population into template history and distribution history tables for downstream processing. In practice, the package acts as the engine that converts a configured template — identified by TEMPLATE_ID — into the actual set of records to be reported on, applying inclusion, exclusion, and formula-based filtering rules along the way.

The package is tightly coupled to the concurrent processing framework through PAY_PAYROLL_ACTIONS: it reads the parent request's LEGISLATIVE_PARAMETERS string to extract the user-supplied criteria, and it writes intermediate and final results into PSP history tables. The presence of an autonomous transaction procedure indicates that logging and history insertion are designed to commit independently of the main transactional flow.

Key Procedures and Functions

The package exposes thirteen documented procedures and functions:

• INSERT_INTO_TEMPLATE_HISTORY — Declared with PRAGMA AUTONOMOUS_TRANSACTION, this procedure reads the payroll action's legislative parameters, extracts the template ID, effort start and end dates, report layout, and the four sort/order pairs (FIRST_SORT/FIRST_ORDER through the fourth set), and writes a template history record.
• GET_PARAMETER_VALUE — A helper that parses a named key from the concatenated legislative parameter string (for example TEMPLATE_ID, START_DATE, END_DATE, REPORT_LAYOUT, FIRST_SORT, and FIRST_ORDER).
• RANGE_CODE — Assists in interpreting the date or range selection coded into the template criteria.
• GET_FINAL_SELECTION_LIST — Produces the final resolved selection list after all filtering stages complete.
• GET_LOWEST_CARDINALITY and GET_ASG_LOWEST_CARDINALITY — Determine the driving (most selective) data set for persons and assignments respectively, an optimization step used when building the initial candidate population.
• PREPARE_INITIAL_PERSON_LIST and PREPARE_INITIAL_ASG_LIST — Build the initial candidate lists of persons and assignments based on the template criteria.
• PRUNE_INITIAL_PERSON_LIST and PRUNE_INITIAL_ASG_LIST — Reduce the initial candidate lists using the applicable selection rules.
• APPLY_EXCLUSION_CRITERIA and APPLY_ASG_EXCLUSION_CRITERIA — Remove records that match configured exclusion rules for persons and assignments.
• APPLY_FF_FORMULA_EXCLUSION — Applies Fast Formula–based exclusion logic, allowing user-defined rules to further filter the population.

Tables Accessed

The package reads and writes a defined set of APPS-synonym tables. It queries PAY_PAYROLL_ACTIONS to obtain the request identifier and LEGISLATIVE_PARAMETERS. Person and assignment data come from PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, PER_ASSIGNMENT_STATUS_TYPES, and PER_TIME_PERIODS. Assignment set definitions and amendments are read from HR_ASSIGNMENT_SETS and HR_ASSIGNMENT_SET_AMENDMENTS. Project and award context is drawn from PA_PROJECTS_ALL and GMS_AWARDS_ALL. Template configuration is read from PSP_REPORT_TEMPLATES. Output and diagnostic records are written to PSP_ADJUSTMENT_LINES_HISTORY, PSP_DISTRIBUTION_LINES_HISTORY, PSP_PRE_GEN_DIST_LINES_HISTORY, and PSP_REPORT_ERRORS / PSP_REPORT_ERRORS_S.

Usage Notes

PSP_TEMPLATE_SELECTION is invoked primarily from concurrent programs that generate template-based distribution reports; the autonomous INSERT_INTO_TEMPLATE_HISTORY is called at the start of the run to snapshot the user's parameter selections. It is referenced by one other package, reinforcing its role as a shared selection utility rather than an entry-point API. Custom code should not call its internal procedures directly; instead, invoke the standard concurrent program and supply TEMPLATE_ID, START_DATE, END_DATE, REPORT_LAYOUT, and the sort/order parameters in the legislative parameter string.