Results for “return_occurrence”

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

Overview

APPS.JL_BR_AR_BANK_RETURNS_V is a reporting view in Oracle EBS that consolidates bank return information for Brazilian receivables (Accounts Receivable) processing. It joins bank return transaction data held in JL_BR_AR_BANK_RETURNS with bank occurrence definitions in JL_BR_AR_BANK_OCCURRENCES, and enriches the result with party, customer account, and bank organization profile details from the HZ trading community model. The view exposes a denormalized, ready-to-query record set that combines transactional return events, bank occurrence classification, and human-readable party names and bank numbers.

Its role is primarily informational and integration-oriented: it presents the state of returned trade notes / collection documents as received from Brazilian banks, together with the amounts and statuses needed for reconciliation and downstream reporting. Because the view joins to HZ_ORGANIZATION_PROFILES with a SYSDATE-based effective date filter, it always resolves the currently effective bank number for the bank party.

Underlying Base Objects

The documented base objects referenced by this view are:

• JL_BR_AR_BANK_RETURNS (SYNONYM) — the primary transactional table, aliased A. It supplies the return event, occurrence code, dates, document identifiers, parties, and amounts.
• JL_BR_AR_BANK_OCCURRENCES (SYNONYM) — aliased B. Joined on BANK_PARTY_ID and BANK_OCCURRENCE_CODE, restricted to BANK_OCCURRENCE_TYPE = 'RETURN_OCCURRENCE'. Provides STD_OCCURRENCE_CODE.
• HZ_CUST_ACCOUNTS (SYNONYM) — aliased D. Joined on CUST_ACCOUNT_ID = A.CUSTOMER_ID to link the customer account to its party.
• HZ_PARTIES (SYNONYM) — referenced twice: alias C (customer) via D.PARTY_ID, supplying CUSTOMER_NAME; and alias bank via bank.PARTY_ID = A.BANK_PARTY_ID.
• HZ_ORGANIZATION_PROFILES (SYNONYM) — aliased bankprofile. Joined to the bank party and filtered on SYSDATE between the effective start and end dates, supplying BANK_NUMBER.

Note the view text also selects A.ROWID AS ROW_ID, providing a unique row identifier for the underlying return record.

Key Columns

• RETURN_ID, ROW_ID — unique identifiers for the return record.
• BANK_OCCURRENCE_CODE, STD_OCCURRENCE_CODE — the bank's occurrence code and the standardized occurrence code.
• OCCURRENCE_DATE, GENERATION_DATE, PROCESSING_DATE, DUE_DATE — lifecycle dates for the return and the related trade note.
• FILE_CONTROL, ENTRY_SEQUENTIAL_NUMBER — bank file identity and sequence position.
• DOCUMENT_ID, OUR_NUMBER, TRADE_NOTE_NUMBER — identifiers for the related document / trade note.
• BANK_PARTY_ID, BANK_NUMBER, COLLECTOR_BANK_PARTY_ID, COLLECTOR_BRANCH_PARTY_ID — bank and collecting bank identifiers; BANK_NUMBER is resolved from the effective organization profile.
• TRADE_NOTE_AMOUNT, BANK_CHARGE_AMOUNT, ABATEMENT_AMOUNT, DISCOUNT_AMOUNT, CREDIT_AMOUNT, INTEREST_AMOUNT_RECEIVED — financial amounts associated with the return.
• CUSTOMER_ID, CUSTOMER_NAME — customer account and party name.
• RETURN_INFO, BANK_USE, COMPANY_USE — bank and company free-text/usage fields.
• ORG_ID — operating unit identifier, supporting multi-org filtering.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — audit columns.

Common Use Cases and Queries

Typical usage includes reconciliation of bank returns, reporting on return amounts by bank or customer, and integration feeds that extract return data for downstream systems. The 'return_occurrence' concept relates to the BANK_OCCURRENCE_TYPE = 'RETURN_OCCURRENCE' filter applied inside the view.

Example — returns for the current operating unit:

• SELECT return_id, trade_note_number, customer_name, bank_number, trade_note_amount, occurrence_date FROM apps.jl_br_ar_bank_returns_v WHERE org_id = :p_org_id ORDER BY occurrence_date DESC;

Example — aggregate return amounts by bank:

• SELECT bank_party_id, bank_number, COUNT(*) return_count, SUM(trade_note_amount) total_amount FROM apps.jl_br_ar_bank_returns_v WHERE occurrence_date BETWEEN :start_date AND :end_date GROUP BY bank_party_id, bank_number;

Example — locate a specific return by trade note:

• SELECT return_id, document_id, std_occurrence_code, credit_amount, interest_amount_received FROM apps.jl_br_ar_bank_returns_v WHERE trade_note_number = :p_note_number;