Search Results pay_magnetic_records




Overview
Explore in AI Chat

Overview

PAY_MAGNETIC_RECORDS 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 control the detailed formatting of the fixed-format version of the magnetic tape process. In practice, the table acts as the line-level definition layer for third-party payment and bank transmission layouts, allowing a payroll implementation to describe how individual records are structured within a magnetic tape or electronic funds transfer file. Each row defines a record segment that belongs to a magnetic block, and the block-to-record hierarchy ultimately drives how payment data is formatted and written to the output file.

From a data modeling perspective, the heuristic Data Vault classification of this table is a link. This classification is inferred from the foreign key structure: the table contains two relationships pointing to PAY_MAGNETIC_BLOCKS (via MAGNETIC_BLOCK_ID and NEXT_BLOCK_ID), indicating it functions as an associative or relationship entity between magnetic block definitions rather than as a standalone hub of business keys or a descriptive satellite.

Key Information Stored

The table's physical schema in 12.2.2 contains 12 documented columns. The most significant columns are:

  • MAGNETIC_BLOCK_ID — Identifies the parent magnetic block to which the record definition belongs; part of the primary key and a foreign key to PAY_MAGNETIC_BLOCKS.
  • SEQUENCE — Orders records within a block; part of the primary key and defines the positional order in which record lines are emitted.
  • NEXT_BLOCK_ID — Foreign key to PAY_MAGNETIC_BLOCKS indicating the subsequent block in the transmission flow.
  • BLOCK_LABEL — Descriptive label applied to the block or record segment for identification.
  • BLOCK_ROW_LABEL — Label applied to the specific row within the block, supporting readability and diagnostics.
  • FORMULA_ID — References the formula used to compute or format the record content.
  • OVERFLOW_MODE — Controls behavior when record content exceeds the allotted fixed-format field width.
  • FREQUENCY — Determines how often the record is produced within the file generation cycle.
  • LAST_RUN_EXECUTED_MODE — Records the execution mode used during the most recent run.
  • ACTION_LEVEL — Defines the level at which the record's action or formatting rule is applied.
  • XML_PROC_NAME — Names the XML processing procedure associated with the record definition.

The surrogate primary key is PAY_MAGNETIC_RECORDS_PK. In 12.2.2 the documented unique index is PAY_MAGNETIC_RECORDS_PK (MAGNETIC_BLOCK_ID, SEQUENCE, ZD_EDITION_NAME). ZD_EDITION_NAME is the editioning column introduced by the Online Patching (AD Online Patching) infrastructure, and it participates in the unique key under the editioning model.

Common Use Cases and Queries

Typical scenarios involve validating or troubleshooting a bank transmission layout before running the magnetic tape process. A common pattern retrieves all record definitions for a given block in the intended order:

  • SELECT magnetic_block_id, sequence, block_label, block_row_label, formula_id, overflow_mode FROM pay_magnetic_records WHERE magnetic_block_id = :block_id ORDER BY sequence;
  • Join to PAY_MAGNETIC_BLOCKS to trace the flow from a record to its block and next block: SELECT r.sequence, r.block_label, b.block_name, r.next_block_id FROM pay_magnetic_records r, pay_magnetic_blocks b WHERE r.magnetic_block_id = b.magnetic_block_id;
  • Audit the last execution mode and overflow handling: SELECT magnetic_block_id, sequence, last_run_executed_mode, overflow_mode FROM pay_magnetic_records WHERE last_run_executed_mode IS NOT NULL;

Reporting use cases include documenting transmission layouts for audit, verifying formula assignments, and confirming sequencing after a patch or configuration migration.

Related Objects

The most significant related objects, based on the documented foreign key relationships, are:

  • PAY_MAGNETIC_BLOCKS — Referenced by PAY_MAGNETIC_RECORDS.MAGNETIC_BLOCK_ID; the parent block definition table.
  • PAY_MAGNETIC_BLOCKS — Referenced by PAY_MAGNETIC_RECORDS.NEXT_BLOCK_ID; defines the subsequent block in the flow.
  • PAY_MAGNETIC_RECORDS_PK — The primary key constraint (MAGNETIC_BLOCK_ID, SEQUENCE, ZD_EDITION_NAME).
  • FF_FORMULAS / FF_FORMULAS_TL — The formula definition tables underlying the FORMULA_ID reference.
  • PAY_MAGNETIC_LAYOUTS — The broader layout entity that organizes blocks and records for a given transmission.

Together these objects form the configuration fabric that governs fixed-format magnetic tape output in the Oracle Payroll module.