Results for “per_checklist_items_v”

20 results




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

Overview

PER_CHECKLIST_ITEMS_V is a read-only reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PER (Human Resources) product family. It exposes personal checklist item records — the individual tasks assigned to a person as part of a checklist, such as new-hire onboarding steps, exit clearance items, or compliance activities. Rather than requiring reports and interfaces to resolve raw lookup codes themselves, the view joins the base checklist item data to HR_LOOKUPS twice, once for the item definition and once for the status, presenting decoded meaning columns alongside the stored codes. This makes it a convenient integration point for HRMS extracts, BI Publisher reports, OBIEE/OTBI-style extractions, and custom concurrent programs that need human-readable checklist data without embedding lookup logic. The view is defined with outer joins to both lookup instances, so checklist rows are never dropped when a lookup value is missing or end-dated.

Underlying Base Objects

The view is defined over three documented objects. The driving table is PER_CHECKLIST_ITEMS (referenced in the data dictionary through the PER_CHECKLIST_ITEMS synonym), which holds one row per assigned checklist item. HR_LOOKUPS is joined twice: LKUP1 aliases the lookup of type CHECKLIST_ITEM on ITEM_CODE, and LKUP2 aliases the lookup of type CHECKLIST_STATUS on STATUS. Both joins are outer joins (denoted by the (+) syntax), and both include the lookup type predicate in the join condition rather than the WHERE clause, ensuring lookup misses degrade to NULLs instead of eliminating rows. The HR_API package is listed as a referenced object, reflecting the HRMS business logic layer that maintains these records. No DML is permitted against the view; inserts and updates must go through the base table or the HR_API package, which enforces object version checking via OBJECT_VERSION_NUMBER.

Key Columns

  • CHECKLIST_ITEM_ID — Primary key of the checklist item record.
  • PERSON_ID — Party/person identifier the checklist item is assigned to; joins to PER_PEOPLE_F and related person views.
  • ITEM_CODE — Code of the checklist item definition; source of the LKUP1 join to HR_LOOKUPS.
  • ITEM — Decoded meaning of ITEM_CODE from the CHECKLIST_ITEM lookup type.
  • DATE_DUE — Date by which the item is expected to be completed.
  • DATE_DONE — Date the item was actually completed; NULL while outstanding. This is the column most commonly filtered or reported against to measure completion performance.
  • STATUS — Stored status code, joined to the CHECKLIST_STATUS lookup type.
  • STATUS_MEANING — Decoded status description (for example, completed or pending), useful in reports without further lookup joins.
  • NOTES — Free-text notes attached to the item.
  • OBJECT_VERSION_NUMBER — Optimistic locking column maintained by HR_API.
  • ATTRIBUTE_CATEGORY, ATTRIBUTE1–30 — Descriptive flexfield context and segments, available for client-specific reporting.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard WHO audit columns.

Common Use Cases and Queries

Typical uses include onboarding and offboarding tracking, outstanding-task aging, and audit extracts. A frequent pattern centers on date_done, as the user search suggests — identifying items completed within a period, or still open because DATE_DONE is NULL.

  • Items completed in a date range: SELECT person_id, item_code, date_due, date_done FROM per_checklist_items_v WHERE date_done BETWEEN :p_start AND :p_end;
  • Outstanding items past due: SELECT person_id, item, date_due, status_meaning FROM per_checklist_items_v WHERE date_done IS NULL AND date_due < SYSDATE ORDER BY date_due;
  • Completion turnaround: SELECT person_id, item, date_done - date_due days_variance FROM per_checklist_items_v WHERE date_done IS NOT NULL;
  • Status summary for reporting: SELECT status_meaning, COUNT(*) FROM per_checklist_items_v GROUP BY status_meaning;

Because lookups are outer-joined, ITEM and STATUS_MEANING may be NULL for orphaned or end-dated codes; reports should handle these gracefully. The view is valid in both 12.1.1 and 12.2.2 and is safe for read-only querying in all supported editions.