Results for “jl_br_ar_ret_interface_all”

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

Overview

JL_BR_AR_RET_INTERFACE_ALL is a Latin America Localizations (JL) interface table in the Oracle E-Business Suite database, owned by the JL schema. Its documented purpose is to serve as the staging interface for importing Brazilian bank returns into Oracle Receivables. External bank return files generated by collection banks are loaded into this table before the Oracle Receivables AutoLockbox-style processing or the Brazilian localization concurrent programs validate and apply the returned receipt records against open trade notes.

The table carries 35 documented columns and is classified heuristically as a standalone Data Vault entity; no foreign key relationships were mined from its constraints. In Data Vault modeling terms this object behaves as a staging or satellite-like landing table rather than a true hub or link, because it holds descriptive bank-return attributes tied to a source file record rather than defining a durable business entity key. Its physical primary key is JL_BR_AR_RET_INTERFACE_ALL_PK, spanning FILE_CONTROL and ENTRY_SEQUENTIAL_NUMBER, which is also the sole unique index (JL_BR_AR_RET_INTERFACE_U1) and therefore the business-key candidate.

Key Information Stored

The composite primary key is (FILE_CONTROL, ENTRY_SEQUENTIAL_NUMBER) — the same column pair forms the unique index and the business-key candidate. FILE_CONTROL identifies the logical bank return file or batch, while ENTRY_SEQUENTIAL_NUMBER identifies the ordinal line within that file. This second column is the exact term the user searched, and it is central to how records are sequenced and matched on import.

• FILE_CONTROL — the file-level control identifier, part one of the primary key.
• ENTRY_SEQUENTIAL_NUMBER — the sequential entry number within the file, part two of the primary key.
• REGISTER_TYPE — the register/record type, distinguishing header, detail, and trailer lines in the return file.
• REMITTANCE_CODE / OUR_NUMBER / YOUR_NUMBER — the identifiers that link the return line back to the original remittance and trade note.
• BANK_NUMBER, COLLECTOR_BANK_NUMBER, COLLECTOR_BRANCH_NUMBER — the collecting bank and branch identifiers.
• BANK_OCCURRENCE_CODE, OCCURRENCE_DATE — the bank return reason code and the date the occurrence was recorded.
• TRADE_NOTE_AMOUNT, TRADE_NOTE_TYPE — the amount and type of the trade note being returned or settled.
• BANK_CHARGE_AMOUNT, ABATEMENT_AMOUNT, DISCOUNT_AMOUNT, CREDIT_AMOUNT, INTEREST_AMOUNT_RECEIVED — financial components parsed from the return file.
• DUE_DATE, GENERATION_DATE — the note due date and file generation date.
• INSCRIPTION_NUMBER, COMPANY_CODE, COMPANY_USE, BANK_USE, PORTFOLIO_CODE — Brazilian fiscal, company, and portfolio references.
• ORG_ID — the operating unit that owns the interface record, plus standard WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN).

Common Use Cases and Queries

Typical usage involves loading a return file, validating its lines, and reconciling matched receipts against open trade notes. Reporting queries frequently group by FILE_CONTROL and order by ENTRY_SEQUENTIAL_NUMBER to reconstruct file order.

• Retrieving all lines for a given bank return file: SELECT * FROM JL_BR_AR_RET_INTERFACE_ALL WHERE FILE_CONTROL = :file_control ORDER BY ENTRY_SEQUENTIAL_NUMBER;
• Isolating records for one operating unit after import: ... WHERE ORG_ID = :org_id;
• Auditing return reasons: grouping counts by BANK_OCCURRENCE_CODE and REGISTER_TYPE.
• Reconciliation reporting: summing TRADE_NOTE_AMOUNT, BANK_CHARGE_AMOUNT, and INTEREST_AMOUNT_RECEIVED per FILE_CONTROL to compare against bank totals.

Related Objects

Because the documented constraint data classifies this table as standalone, no foreign keys to related Receivables tables were mined. The most significant related objects are reached logically through the bank and remittance identifiers rather than enforced constraints:

• JL_BR_AR_BANK_RETURN / return-processing concurrent programs — consume the staged rows.
• AR_CASH_RECEIPTS_ALL and AR_CASH_RECEIPT_HISTORY_ALL — populated when a returned line is matched to an open receipt.
• AR_PAYMENT_SCHEDULES_ALL — holds the trade note installments being settled or reversed, joined via OUR_NUMBER / YOUR_NUMBER.
• RA_CUSTOMERS / HZ_PARTIES — resolve CUSTOMER_NAME and INSCRIPTION_NUMBER to a customer account.
• JL_BR_AR_OCCURRENCE_DOCS_ALL — the Brazilian occurrence document that records the bank return event.