Results for “prenote_date”

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

Overview

PAY_EXTERNAL_ACCOUNTS is a Payroll (PAY) module table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the external bank account details that enable payments to be made to payees, third parties, and organizations through the Oracle Payroll payment process. Each row represents a discrete disbursement account — typically a bank account identified by routing or branch information combined with an account number — held against a specific territory and payment currency configuration.

The table functions as the payroll-side registry of disbursement accounts that the Cash Management and Payables banking model also consumes. Rather than duplicating a bank account per payroll, Oracle Payroll links to the shared banking entities via AP_BANK_ACCOUNTS_ALL and AP_BANK_BRANCHES, using the PAYROLL_BANK_ACCOUNT_ID foreign key column to preserve a single, consistent account definition across subledgers. The application-defined descriptive flexfield is materialized through the SEGMENT1 through SEGMENT30 columns.

From a dimensional modeling perspective, the mined relationship metadata classifies this object as hub-leaning. In Data Vault terms it behaves as a hub: it carries a single, stable surrogate key (EXTERNAL_ACCOUNT_ID), uniquely and independently identifies a business concept (an external payment account), and is referenced by multiple downstream satellites and link-style tables. Modeling it as a hub with dependent satellites for descriptive and status attributes would be consistent with its referential footprint.

Key Information Stored

The surrogate primary key is EXTERNAL_ACCOUNT_ID, enforced by the unique index PAY_EXTERNAL_ACCOUNTS_PK. This column is the single documented unique index and therefore the sole defined business-key candidate in the metadata provided; no alternate unique constraint is documented, so EXTERNAL_ACCOUNT_ID remains the authoritative identifier for joins.

• EXTERNAL_ACCOUNT_ID — surrogate primary key; the identifier referenced by every dependent table.
• TERRITORY_CODE — the country or territory governing the account, which determines applicable bank-validation and formatting rules.
• PRENOTE_DATE — the date a pre-notification (prenote) was sent or completed, used to validate account authenticity before live disbursement.
• ID_FLEX_NUM — the descriptive flexfield structure number that binds the SEGMENT columns to a specific flexfield definition.
• SUMMARY_FLAG — indicates whether the row is a summary (parent) record rather than a detail account.
• ENABLED_FLAG — controls whether the account is active and selectable for payment processing.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range for the account, supporting date-tracked validity.
• SEGMENT1 – SEGMENT30 — the descriptive flexfield segments holding account-specific attributes configured per implementation.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS audit columns recording who created and last changed the row.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OA Framework and PL/SQL APIs to detect concurrent updates.

Common Use Cases and Queries

Reporters most often resolve a payroll payment method or transaction step back to its underlying bank account. Because the user search referenced ap_bank_accounts_all, the typical query joins through the shared banking tables:

• List all active external accounts for a territory: SELECT external_account_id, territory_code, prenote_date FROM pay_external_accounts WHERE enabled_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
• Reconcile payroll accounts to Payables banking: SELECT p.external_account_id, a.bank_account_name, a.bank_account_num FROM pay_external_accounts p JOIN ap_bank_accounts_all a ON a.payroll_bank_account_id = p.external_account_id;
• Trace organization payment methods: SELECT m.org_payment_method_id, p.external_account_id FROM pay_org_payment_methods_f m JOIN pay_external_accounts p ON m.external_account_id = p.external_account_id;
• Audit flexfield content by querying SEGMENT columns filtered on ID_FLEX_NUM.

Common reporting scenarios include prenote tracking, active-account population for a pay run, and validation of accounts referenced by PAY_PERSONAL_PAYMENT_METHODS_F and PAY_PSS_TRANSACTION_STEPS.

Related Objects

• AP_BANK_ACCOUNTS_ALL — joined on AP_BANK_ACCOUNTS_ALL.PAYROLL_BANK_ACCOUNT_ID = PAY_EXTERNAL_ACCOUNTS.EXTERNAL_ACCOUNT_ID; supplies the canonical bank account definition.
• AP_BANK_BRANCHES — joined via PAYROLL_BANK_ACCOUNT_ID; provides branch and institution detail.
• PAY_PERSONAL_PAYMENT_METHODS_F — personal payment methods referencing EXTERNAL_ACCOUNT_ID for employee-directed disbursement.
• PAY_ORG_PAYMENT_METHODS_F — organization-level payment methods using EXTERNAL_ACCOUNT_ID.
• PAY_PSS_TRANSACTION_STEPS — references both EXTERNAL_ACCOUNT_ID and O_EXTERNAL_ACCOUNT_ID in payment transaction processing.
• CE_UPG_PAY_ACCTS_TMP and CE_UPG_PURGE_PAY_ACCTS — Cash Management upgrade and purge staging tables keyed on EXTERNAL_ACCOUNT_ID.