Results for “hr_pump_batch_lines”

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

Overview

HR_PUMP_BATCH_LINES is a table in the HR schema of Oracle E-Business Suite, owned by the PER (Human Resources) product. It functions as a staging and execution container for batch-driven calls into Oracle's Human Resources public APIs. Each row in this table stores the complete set of information required to make a single API call, including the input parameters and the user keys that resolve an entity instance. After the API is invoked, OUT-parameter values — including generated identifiers and status codes — are written back into the same row. This makes the table both a request queue and a response log, allowing a batch process to submit many API invocations in a controlled, restartable, and auditable manner.

The table is the transactional line-level detail of the HR Pump framework. The parent HR_PUMP_BATCH_HEADERS table groups lines into a named batch, while HR_API_MODULES identifies which API is to be called for a given line. The design supports high-volume data loads, concurrent worker processing, and error diagnosis because the status of every individual API call is persisted.

From a data-modeling perspective, the heuristic Data Vault classification is satellite-leaning. The table records descriptive and state attributes attached to a batch and an API module, rather than acting as an independent business hub. This classification should be treated as a modeling suggestion; in Oracle EBS terms, the table behaves as a work-unit or detail record subordinate to the batch header.

Key Information Stored

The physical schema in ETRM 12.2.2 reports 239 columns. The most significant are:

• BATCH_LINE_ID — the surrogate primary key, defined by the unique index HR_PUMP_BATCH_LINES_PK. It uniquely identifies each API-call line.
• BATCH_ID — the foreign key to HR_PUMP_BATCH_HEADERS, linking the line to its controlling batch. Together with BATCH_LINE_ID it forms the secondary unique index HR_PUMP_BATCH_LINES_UK1, a business-key candidate.
• API_MODULE_ID — the foreign key to HR_API_MODULES, identifying which HR API module is to be invoked for this line.
• LINE_STATUS — the processing status of the line, indicating whether the API call is pending, successful, or in error.
• LINK_VALUE — a link or correlation value, typically used to associate the line with an external source record or to thread related calls together.
• USER_SEQUENCE and PROCESS_SEQUENCE — ordering controls that determine line sequence for user-defined ordering and internal processing respectively.
• PVAL001 through PVAL230 — a large array of generic VARCHAR-style parameter columns holding the IN and OUT values for the API call. Each slot corresponds to a parameter position, and OUT values are written back after execution.
• PLONGVAL — a long-value column used to hold oversized parameter payloads that do not fit the standard PVAL slots.
• BUSINESS_GROUP_NAME — the business group context for the API call, a standard Enterprise Group orientation column across HR tables.

The user-key table HR_PUMP_BATCH_LINE_USER_KEYS separately stores the business identifiers used to resolve the target entity, referencing BATCH_LINE_ID back to this table.

Common Use Cases and Queries

Typical usage centers on monitoring batch progress, diagnosing failed API calls, and extracting parameter values for audit. A common query lists lines belonging to a batch with their status:

• Batch monitoring: SELECT batch_line_id, api_module_id, line_status, link_value FROM hr_pump_batch_lines WHERE batch_id = :batch_id ORDER BY process_sequence;
• Error diagnosis: filter on LINE_STATUS to identify failed calls and inspect the PVAL columns for returned error codes and messages.
• Throughput reporting: join to HR_PUMP_BATCH_HEADERS to summarize line counts and success ratios per batch, useful for load-balancing and scheduling.
• Parameter extraction: query the relevant PVAL slot for a specific API module to recover the IN values originally submitted, supporting reconciliation of employee, assignment, or payroll data loads.

Because OUT values are persisted, the table also serves as an audit trail for generated object identifiers and statuses returned by the API.

Related Objects

• HR_PUMP_BATCH_HEADERS — parent batch definition; join on HR_PUMP_BATCH_LINES.BATCH_ID = HR_PUMP_BATCH_HEADERS.BATCH_ID.
• HR_API_MODULES — API registry; join on HR_PUMP_BATCH_LINES.API_MODULE_ID = HR_API_MODULES.API_MODULE_ID.
• HR_PUMP_BATCH_LINE_USER_KEYS — child table storing business user keys; join on HR_PUMP_BATCH_LINES.BATCH_LINE_ID = HR_PUMP_BATCH_LINE_USER_KEYS.BATCH_LINE_ID.
• HR_API_MODULE_PARAMETERS — defines parameter positions mapped to the PVAL slots.
• HR_PUMP_BATCHES / batch processing programs — concurrent programs that populate and consume these lines.

These objects together form the HR Pump batch processing framework within PER.