Search Results batch_sequence




Overview
Explore in AI Chat

Overview

APPS.PAY_PDT_BATCH_LINES_V2 is a reporting view in the Oracle E-Business Suite payroll product family. Its name follows the Oracle convention of indicating a second-generation ("V2") representation of batch line data originating in the Payroll Data Transformation (PDT) flow. The view is built on top of the earlier view PAY_PDT_BATCH_LINES_V1 and applies a pivoting transformation, as stated explicitly in the view text comments: the underlying rows are pivoted so that all input value names belonging to a single element are presented in one row rather than in a normalized, one-row-per-input format.

In the context of Oracle EBS 12.1.1 and 12.2.2, the object is read-only by design. It exposes payroll batch line data — including element identification, assignment, cost allocation keyflex, batch status, and segment values — in a denormalized wide format that is convenient for concurrent-program extracts, BI Publisher reports, and integration interfaces. It carries no application logic of its own beyond the pivot; data integrity and business meaning are inherited from the underlying PDT batch line objects.

Underlying Base Objects

According to the documented ETRM 12.2.2 metadata, the sole referenced base object for PAY_PDT_BATCH_LINES_V2 is:

  • PAY_PDT_BATCH_LINES_V1 (VIEW) — the first-level view supplying the pre-pivoted result set.

The view text confirms this relationship: the outer SELECT wraps "the result set of the previous view" and always resolves from PAY_PDT_BATCH_LINES_V1. The dependency chain is therefore hierarchical — V2 depends entirely on V1, which in turn resolves payroll batch line data from the underlying PAY_PDT_BATCH_LINES and related payroll element tables. Because the pivot is performed with MAX(DECODE(...)) aggregate expressions, no join or GROUP BY is visible in the documentation excerpt beyond the implied aggregation on the V1 columns, meaning every non-pivoted column listed (for example batch_line_id, assignment_id, batch_id) serves as the grouping key for the pivot. Any extension or customization should be applied at the V1 or base table level, since V2 is an APPS-owned, non-editable shipped view.

Key Columns

The view projects a broad set of identifier and descriptive columns alongside the pivoted input names. Column names are unique to this view and are not prefixed.

  • batch_sequence — the position of the line within its batch; the column surfaced when users search for "batch_sequence". It provides deterministic ordering of batch lines for reporting.
  • batch_id and batch_line_id — surrogate keys identifying the batch header and the individual batch line.
  • batch_line_status — the processing state of the line (for example, unprocessed, validated, transferred, or error).
  • element_type_id, element_name, and legislation_code — the payroll element being processed and the legislative context.
  • assignment_id and assignment_number — the assignment to which the element is applied.
  • cost_allocation_keyflex_id and concatenated_segments — the cost allocation flexfield reference and its rendered concatenated form.
  • effective_date, entry_type, and reason — the effective dating and entry metadata for the line.
  • segment1 through segment30 — the element input value segments (up to thirty).
  • value_1 through value_15 — the pivoted input values, paired with name_1 through name_15, which are produced by MAX(DECODE(iv_seq, n, name, NULL)) for n = 1 to 15.
  • attribute_category and attribute1 through attribute20 — descriptive flexfield columns available for client-specific extensions.

Common Use Cases and Queries

The primary use case is extracting payroll batch lines in a wide format, one row per batch line, with element input names and values denormalized for downstream consumption. Typical scenarios include reconciliation of transferred batch lines, audits of cost allocation, and feeds into third-party payroll or general ledger interfaces.

The most common query filters or orders by batch_sequence so that lines are consumed in their intended processing order:

  • SELECT batch_id, batch_line_id, batch_sequence, assignment_number, element_name, batch_line_status, value_1, name_1, value_2, name_2 FROM apps.pay_pdt_batch_lines_v2 WHERE batch_id = :p_batch_id ORDER BY batch_sequence;
  • SELECT batch_sequence, assignment_number, element_name, concatenated_segments FROM apps.pay_pdt_batch_lines_v2 WHERE batch_line_status = 'E' ORDER BY batch_id, batch_sequence;
  • SELECT element_name, COUNT(*) FROM apps.pay_pdt_batch_lines_v2 WHERE effective_date BETWEEN :p_from AND :p_to GROUP BY element_name;

Because V2 is defined over PAY_PDT_BATCH_LINES_V1, queries against it inherit the pivot's aggregation semantics and should not be used to retrieve raw, unsummarized input name rows; that granularity is available only from the V1 view or the base tables. For large batches, filter on batch_id or effective_date before joining to additional payroll objects to preserve performance.