Results for “je_ch_payment_ref”

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

Overview

The JE_CH_PAYMENT_REF table is a localization reference table owned by the AP (Payables) schema within the JE - European Localizations product. Its documented purpose is to store payment batch information required for Swiss Electronic Funds Transfer (EFT) processing. In Oracle EBS 12.1.1 and 12.2.2, Swiss payment formats (such as the DTA and ESR/BESR variants) require structured reference fields to be carried alongside each payment so that the generated payment file and the corresponding bank reconciliation records can be tied back to the originating invoice, supplier, and settlement instruction.

Because Swiss EFT formats impose a rigid, positional reference structure, this table provides a dedicated storage location for up to ten generic reference slots (REF1 through REF10). These slots absorb the format-specific narrative content that has no natural home in the core Payables payment tables. In the broader localization architecture, the table acts as a supplementary carrier for outbound Swiss payment data generated during the payment batch build process.

From a Data Vault modeling perspective, the mined relationship structure classifies this object heuristically as standalone, with no documented foreign key dependencies to other tables. Each row is therefore best modelled as a satellite-style descriptive record — specifically a payment-reference satellite keyed by the payment batch and payment identifiers — rather than as a hub or link between business entities.

Key Information Stored

The table has 13 documented columns. The most significant are the identifiers that form the primary key and the payload columns that hold the Swiss payment references.

• PAYMENT_TYPE — identifies the category or method of the Swiss payment being processed; part of the composite business key.
• CHECKRUN_NAME — the name of the Payables payment batch (checkrun) under which the payment was created; part of the composite business key.
• CHECK_NUMBER — the payment document number assigned to the individual payment; part of the composite business key.
• REF1 through REF10 — ten sequential generic reference fields that carry the format-specific narrative content required by Swiss EFT layouts, such as participant numbers, ESR reference numbers, invoice references, and other settlement identifiers.

The documented primary key JE_CH_PAYMENT_REF_PK is defined on the composite of PAYMENT_TYPE, CHECKRUN_NAME, and CHECK_NUMBER. The unique index JE_CH_PAYMENT_REF_U1 is defined on exactly the same three columns, confirming that this trio is the authoritative business-key candidate and that exactly one reference record exists per payment and payment batch combination. The surrogate primary key therefore coincides with the business key rather than being a separate system-generated identifier.

Common Use Cases and Queries

The most common reporting scenario is retrieving all reference fields for a given payment batch so that the Swiss EFT file can be audited or regenerated. A typical query joins the reference table to the payment batch and payment distributions:

• Batch-level audit: SELECT payment_type, checkrun_name, check_number, ref1, ref2, ref3 FROM ap.je_ch_payment_ref WHERE checkrun_name = :p_checkrun;
• Payment-level lookup: SELECT ref1, ref2, ref3, ref4 FROM ap.je_ch_payment_ref WHERE check_number = :p_check AND checkrun_name = :p_checkrun;
• ESR/BESR reconciliation: match REF1/REF2 against bank statement reference data to reconcile incoming Swiss payments against the originating batch.
• Reference completeness validation: count rows where the applicable REFn columns are null, to detect payments that will fail Swiss format validation before the EFT file is transmitted.

Because the table is a localization reference store rather than a transactional master, queries are typically read-only and driven by CHECKRUN_NAME or CHECK_NUMBER, both of which are indexed through the primary key and unique index.

Related Objects

The documented metadata identifies no foreign key relationships, and the table is classified as standalone. The following Payables objects are the most significant related structures by semantic and join-key association. Where join columns are not documented, they are inferred from the shared identifiers CHECKRUN_NAME and CHECK_NUMBER.

• AP_CHECKS_ALL — the core payment table; joined on the concatenation of CHECKRUN_NAME and CHECK_NUMBER.
• AP_INVOICE_PAYMENTS_ALL — links each payment to the invoices it settles; joined on CHECK_ID, which is correlated to CHECK_NUMBER/CHECKRUN_NAME.
• AP_PAYMENT_SCHEDULES_ALL — settlement schedules for the invoices referenced in the Swiss payment narrative.
• AP_BATCHES_ALL — the payment batch header; joined on CHECKRUN_NAME.
• IBY_PAYMENTS_ALL — the Payments module record corresponding to each disbursement.
• IBY_PAY_INSTRUCTIONS_ALL — payment instruction and format metadata for the Swiss EFT method.
• Swiss EFT format definitions and the Payables payment format concurrent programs that consume REF1–REF10 when producing the outbound file.

These associations position JE_CH_PAYMENT_REF as a supporting localization satellite to the core Payables payment model, queried primarily during file generation, audit, and Swiss bank reconciliation.