Results for “bank_code”

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

Overview

The view JL_BR_AP_COLLECTION_DOCS_V is a Latin America Localization (JL) object owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes bank collection document information maintained by the Brazilian Accounts Payable localization, providing a denormalized, reporting-friendly projection of collection documents (boletos, borderôs, and related banking instruments) enriched with vendor and bank branch descriptive attributes. In the ETRM metadata the object is annotated as "Retrofitted," indicating that its definition was adapted as part of the 12.2 upgrade cycle while preserving the original column contract consumed by localization forms, concurrent programs, and downstream reports. As a view, it carries no independent storage; it is a read-only query surface over the collection document entity, suited to inquiry screens, extract programs, and integration interfaces that need the paid amount and other financial attributes of a collection document without joining vendor and bank reference views manually.

Underlying Base Objects

The documented base objects referenced by the view are: JL_BR_AP_COLLECTION_DOCS (SYNONYM, the primary driving entity), CE_BANK_BRANCHES_V (VIEW), PO_VENDORS (VIEW), PO_VENDOR_SITES (VIEW), and FND_GLOBAL (PACKAGE). The main SELECT draws every column of the collection document table—aliased JLDOC—using JLDOC.ROWID as the first projected value, then LEFT-joins bank branch and vendor information. CE_BANK_BRANCHES_V (aliased BB) supplies BANK_NUMBER, BANK_NAME, BANK_BRANCH_NAME, and BRANCH_NUMBER. PO_VENDORS (aliased PV) supplies VENDOR_NAME and SEGMENT1, while PO_VENDOR_SITES (aliased PVS) supplies VENDOR_SITE_CODE and the global attribute fields used in a DECODE/SUBSTR expression that constructs a CNPJ-style formatted identifier. FND_GLOBAL is referenced to supply the organization or user context (typically ORG_ID), which also appears as a stored column on the document entity.

Key Columns

• BANK_COLLECTION_ID — Primary key of the collection document; the join key for all dependent detail tables.
• INVOICE_ID — Links the document to the AP invoice it settles.
• CURRENCY_CODE, AMOUNT, PAID_AMOUNT — The document currency, nominal document amount, and the amount actually paid, respectively. PAID_AMOUNT is the column most commonly queried by users searching this view.
• DUE_DATE, ISSUE_DATE, DISCOUNT_DATE, DISCOUNT_AMOUNT — Maturity, issuance, and early-settlement discount terms.
• ARREARS_DATE, ARREARS_CODE, ARREARS_INTEREST, ABATE_AMOUNT, PENALTY_FEE_AMOUNT, PENALTY_FEE_DATE, OTHER_ACCRETIONS — Interest, penalty, rebate, and accretion components used in Brazilian collection settlement calculations.
• DOCUMENT_NUMBER, OUR_NUMBER, PAYMENT_NUM, DOCUMENT_TYPE, STATUS_LOOKUP_CODE — Identification and lifecycle status of the instrument.
• SET_OF_BOOKS_ID, ORG_ID, VENDOR_ID, VENDOR_SITE_ID, BANK_BRANCH_ID — Accounting, operating unit, and party context.
• BANK_NAME, BANK_NUMBER, BANK_BRANCH_NAME, BRANCH_NUMBER, VENDOR_NAME, SEGMENT1, VENDOR_SITE_CODE — Descriptions joined from the bank branch and vendor views.
• ATTRIBUTE1 through ATTRIBUTE30, ATTRIBUTE_CATEGORY — Descriptive flexfield segments.

Common Use Cases and Queries

Typical scenarios include reconciling collection documents against payments, reporting outstanding versus settled amounts by vendor or bank, and feeding treasury extracts. Because PAID_AMOUNT is the searched term, the most frequent query filters or aggregates on it:

• Listing settled documents for a vendor: SELECT document_number, amount, paid_amount FROM jl_br_ap_collection_docs_v WHERE vendor_id = :p_vendor_id AND paid_amount > 0;
• Reconciling paid against nominal amount: SELECT bank_collection_id, amount, paid_amount, amount - paid_amount balance FROM jl_br_ap_collection_docs_v WHERE org_id = :p_org_id AND status_lookup_code = 'PAID';
• Joining to AP invoices: SELECT v.document_number, v.paid_amount, i.invoice_num FROM jl_br_ap_collection_docs_v v, ap_invoices_all i WHERE v.invoice_id = i.invoice_id;
• Aggregating by bank and branch: SELECT bank_name, branch_number, SUM(paid_amount) FROM jl_br_ap_collection_docs_v GROUP BY bank_name, branch_number;

Queries should always be constrained by ORG_ID or SET_OF_BOOKS_ID to respect multi-org and ledger security, and performance is best served by filtering on BANK_COLLECTION_ID, INVOICE_ID, or VENDOR_ID, since the view performs outer joins to the vendor and bank branch sources on every execution.