Results for “pqp_gap_duration_summary”

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

Overview

PQP_GAP_DURATION_SUMMARY is an Oracle EBS table owned by the HR schema and delivered under the PQP (Public Sector Payroll) product family. It stores summarized gap duration measurements that arise when absence plans define eligibility gaps—periods in which an employee does not satisfy a service or accrual requirement. Rather than recalculating gap spans transactionally, PQP persists pre-aggregated durations here so downstream payroll and benefits processing can evaluate entitlement without re-deriving the underlying absence history.

The table sits at the boundary between absence configuration and payroll calculation. Its foreign key to PQP_GAP_ABSENCE_PLANS confirms that each summary row is tied to a defined gap rule, while ASSIGNMENT_ID anchors the measurement to a specific employee assignment. The documented physical schema lists 15 columns in release 12.2.2.

Under heuristic Data Vault modeling derived from the mined foreign key structure, the object is classified as standalone. It references parent plans but is not itself referenced as a hub by dependent child tables within the documented relationship set. In modeling terms it behaves closest to a satellite, capturing descriptive duration metrics attached to an assignment–plan combination rather than acting as a shared business key hub.

Key Information Stored

The surrogate primary key is GAP_DURATION_SUMMARY_ID, enforced by PQP_GAP_DURATION_SUMMARY_PK. This is a system-generated identifier with no business meaning; it exists solely to give each summary row a stable, joinable identity.

Business-key candidates are the combination of ASSIGNMENT_ID and GAP_ABSENCE_PLAN_ID, optionally qualified by SUMMARY_TYPE and GAP_LEVEL, which together describe which employee, which absence plan rule, and which aggregation perspective a row represents. The measurement itself is carried in DURATION_IN_DAYS and DURATION_IN_HOURS, providing both calendar-day and time-based views of the same gap span. DATE_START and DATE_END bound the period over which the duration was assessed.

Standard EBS audit and concurrency columns are present: OBJECT_VERSION_NUMBER for optimistic locking, and CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN for WHO-column auditing.

Common Use Cases and Queries

Typical usage centers on determining how much service or absence credit an assignment has accumulated against a configured plan rule. A representative query retrieves the most recent summary for an assignment and plan:

• SELECT duration_in_days, duration_in_hours, date_start, date_end FROM hr.pqp_gap_duration_summary WHERE assignment_id = :p_assignment_id AND gap_absence_plan_id = :p_plan_id AND summary_type = :p_type;
• Aggregated reporting by plan: SELECT gap_absence_plan_id, SUM(duration_in_hours) FROM hr.pqp_gap_duration_summary WHERE date_start >= :p_from GROUP BY gap_absence_plan_id;
• Reconciliation against PQP_GAP_ABSENCE_PLANS to validate that every populated plan has a corresponding summary row.
• Feeding payroll element inputs where entitlement depends on pre-computed gap durations.

Reports should filter on ASSIGNMENT_ID and date ranges to avoid full scans, since the table can grow with each recalculation cycle.

Related Objects

The documented foreign key identifies the principal parent relationship:

• PQP_GAP_ABSENCE_PLANS — joined via GAP_ABSENCE_PLAN_ID; supplies the gap rule definition that a summary row measures against.
• PER_ALL_ASSIGNMENTS_F — joined via ASSIGNMENT_ID; provides the employee assignment context (not a documented FK but the natural business linkage for ASSIGNMENT_ID).
• PQP_GAP_ABSENCE_PLAN_DTLS — likely detail-level configuration for the plan, useful when interpreting SUMMARY_TYPE and GAP_LEVEL.
• Absence and accrual tables in the PQP and PER schemas, which supply the raw absence events that durations summarize.

Because the object is classified as standalone, no documented child tables depend on it; integration is primarily through the assignment and plan keys rather than referential chains.