Results for “selection_date”

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

Overview

The view APPS.JL_BR_AR_BORDEROS_V is a Latin America Localization (JL) reporting object that exposes Brazilian Bordero (bank collection remittance) records in a fully denormalized, description-enriched form. A Bordero is the Brazilian banking instrument by which a company remits a batch of customer receivables (boletos) to a bank for collection; the base transactional data is stored in JL_BR_AR_BORDEROS, and this view joins that table to lookup and party/bank metadata so that a single query returns both the operational attributes and their human-readable meanings.

In Oracle EBS 12.1.1 and 12.2.2 the object is a VALID view owned by APPS and documented in ETRM as "Retrofitted" — meaning it was reverse-engineered into the ETRM repository rather than seeded with a formal description. Its principal role is to support Oracle Reports output (the view text selects OUTPUT_PROGRAM_ID and PROGRAM_NAME from AP_PAYMENT_PROGRAMS) and inquiry screens for the Brazilian Receivables localization, without requiring the caller to resolve the underlying Foreign Keys or FND lookup codes.

Underlying Base Objects

ETRM 12.2.2 documents the following referenced base objects, all resolved through APPS synonyms:

• JL_BR_AR_BORDEROS — the driving table (A), one row per Bordero, supplying all primary attributes.
• FND_LOOKUPS (view), aliased as D, E and J, providing the decoded values for BORDERO_TYPE, BORDERO_STATUS and OUTPUT_FORMAT.
• AR_SYSTEM_PARAMETERS (F), supplying GLOBAL_ATTRIBUTE1 as AR_SYS_PARAM_GLOBAL_ATTRIBUTE1.
• AP_PAYMENT_PROGRAMS (I), supplying the concurrent program name for the output format.
• CE_BANK_ACCOUNTS and CE_BANK_ACCT_USES_ALL, joined on BANK_ACCT_USE_ID / BANK_ACCOUNT_ID, supplying bank account number and secondary reference.
• HZ_PARTIES, HZ_ORGANIZATION_PROFILES, HZ_CODE_ASSIGNMENTS and HZ_RELATIONSHIPS, used to resolve the bank party, branch party, branch number and bank number, with date-effective joins on the organization profile.
• FND_GLOBAL (package), referenced to supply ORG_ID context.

All joins are inner-style equality joins across the TCA bank model, so a Bordero row is returned only where the bank, branch and account relationships are all valid.

Key Columns

• BORDERO_ID, SELECTION_CONTROL_ID — primary identifier and the selection run that generated the Bordero.
• BORDERO_TYPE / BORDERO_TYPE_DESC, BORDERO_STATUS / BORDERO_STATUS_DESC — coded value and FND-decoded meaning.
• TOTAL_COUNT, TOTAL_AMOUNT — number of receivables and the remitted value.
• SELECTION_DATE, REMITTANCE_DATE, REFUSED_DATE, CANCELLATION_DATE, COLLECTION_DATE, WRITE_OFF_DATE, DATE_IN_RECEIPT, RECEIVED_DATE — the lifecycle datelines of the Bordero.
• OUTPUT_PROGRAM_ID / PROGRAM_NAME, OUTPUT_FORMAT / OUTPUT_FORMAT_DESC — the concurrent program and layout used to generate the bank file.
• BANK_NAME, BANK_BRANCH_NAME, BRANCH_NUMBER, BANK_NUMBER, BANK_PARTY_ID — bank/branch identification resolved from TCA.
• BANK_ACCOUNT_NUM, AP_BNK_ACCTS_GLOBAL_ATTRIBUTE7 — the collecting account.
• ORG_ID and the standard WHO columns (CREATION_DATE, LAST_UPDATE_DATE, etc.).

Common Use Cases and Queries

Typical uses include reconciliation of remitted versus collected Boderos, bank file regeneration auditing, and operational reporting by status and date range. Queries must supply the operating unit context, since ORG_ID is a security attribute:

• List open Boderos for an operating unit and bank.
• Trend remittance volume by selection date.
• Identify Boderos refused or cancelled after remittance.

Sample SQL:

SELECT v.bordero_id,
       v.bordero_type_desc,
       v.bordero_status_desc,
       v.bank_name,
       v.bank_branch_name,
       v.bank_account_num,
       v.total_count,
       v.total_amount,
       v.remittance_date,
       v.collection_date,
       v.program_name,
       v.output_format_desc
  FROM apps.jl_br_ar_borderos_v v
 WHERE v.org_id = :p_org_id
   AND v.remittance_date BETWEEN :p_from_date AND :p_to_date
   AND v.bordero_status_desc IN ('Remitted','Collected')
 ORDER BY v.remittance_date, v.bordero_id;

Aggregate variant:

SELECT v.org_id,
       v.bank_name,
       COUNT(*)          bordero_count,
       SUM(v.total_amount) remitted_amount
  FROM apps.jl_br_ar_borderos_v v
 WHERE v.selection_date >= TRUNC(SYSDATE,'MM')
 GROUP BY v.org_id, v.bank_name;