Search Results pay_patch_status

Overview

PAY_PATCH_STATUS is a Payroll (PAY) module table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to track the application of patches against the Payroll product, storing identification, phase, process type, and application status details for each patch record. The table is registered as VALID in the ETRM repository and physically carries thirteen columns under the 12.2.2 documented schema.

From a modeling perspective, the mined Data Vault classification for this object is hub-leaning. This suggests treatment as a hub entity that anchors the business key of a patch registration, with downstream references (notably from PAY_US_GEO_UPDATE) behaving as dependent relationships. Analysts building a dimensional or Data Vault layer may therefore model PAY_PATCH_STATUS as a central hub keyed on patch identity, with descriptive attributes such as status, phase, and release handled as satellite content.

Key Information Stored

The table exposes a compact set of tracking attributes. The most significant are:

• ID — the surrogate primary key. It is the single column of the documented primary key constraint PAY_PATCH_STATUS_PK.
• PATCH_NUMBER — the patch identifier, the principal business identifier for the registration record.
• PATCH_NAME — the descriptive name associated with the patch number.
• PHASE — the processing phase reached by the patch application.
• PROCESS_TYPE — the classification of the process used to apply the patch.
• STATUS — the current state of the patch application; the central operational attribute of the row.
• APPLIED_DATE — the date the patch was applied.
• UPDATE_DATE — the last modification timestamp of the record.
• DESCRIPTION — free-form explanatory text for the patch entry.
• LEGISLATION_CODE — the legislative context, relevant where patch application varies by country or region.
• APPLICATION_RELEASE — the application release against which the patch was applied.
• PREREQ_PATCH_NAME — the prerequisite patch that must precede this one.
• ZD_EDITION_NAME — the editioning column used for Online Patching in EBS 12.2.x.

The documented unique index PAY_PATCH_STATUS_PK covers (ID, ZD_EDITION_NAME), reflecting the 12.2 edition-aware uniqueness model. Because this composite index carries both the surrogate key and the edition column, business-key candidates remain anchored to ID rather than to PATCH_NUMBER alone; PATCH_NUMBER should be treated as the natural business identifier but is not documented as uniquely constrained on its own.

Common Use Cases and Queries

The primary use case is operational and upgrade reporting: determining which patches have been applied to a Payroll installation, at which phase, and with what status. Typical queries include:

• Listing all applied patches by release: SELECT patch_number, patch_name, applied_date FROM hr.pay_patch_status WHERE application_release = :release ORDER BY applied_date;
• Identifying incomplete or failed applications: SELECT patch_number, phase, process_type, status FROM hr.pay_patch_status WHERE status <> 'COMPLETE';
• Legislation-specific reporting: SELECT patch_number, status FROM hr.pay_patch_status WHERE legislation_code = :leg;
• Dependency analysis using PREREQ_PATCH_NAME to reconstruct the intended application order.
• Edition-scoped queries in 12.2.x, constraining or grouping by ZD_EDITION_NAME for Online Patching diagnostics.

The table also supports audit-style reporting on UPDATE_DATE and APPLIED_DATE to establish the timeline of maintenance activity on a Payroll instance.

Related Objects

The documented foreign-key relationship is limited but explicit: PAY_US_GEO_UPDATE references PAY_PATCH_STATUS on the ID column (PAY_US_GEO_UPDATE.ID → PAY_PATCH_STATUS). This establishes that geography update records are keyed to the patch registration from which they derive, and it is the mechanism that makes the hub-leaning classification meaningful.

Beyond that documented link, dependency should be assumed from the standard Payroll patching infrastructure — the AD and ADPATCH driver processes that populate the table, and the standard Payroll patch application views and reports that read STATUS and PHASE for upgrade verification. The principal join column across these relationships is ID, the surrogate primary key, while PATCH_NUMBER and PREREQ_PATCH_NAME support business-level reconciliation between this table and external patch inventories maintained outside the database.