Results for “jl_br_ar_collect_docs_u1”

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

Overview

JL.JL_BR_AR_COLLECTION_DOCS_ALL is a transactional table in the Oracle E-Business Suite JL schema that is specific to the Brazilian (Latin America) localization for Oracle Receivables. The table stores collection documents — the individual receivable items (payment schedules) that have been selected for submission to a bank for collection (cobrança), or that have already been transmitted. Each row represents one collection document associated with a single payment schedule, and it functions as the operational intersection between customer transactions, bank transfer batches (borderôs), and the Brazilian banking collection process.

The table resides in the APPS_TS_TX_DATA tablespace, confirming its classification as transactional data rather than reference or setup data. Its physical schema in ETRM 12.2.2 documents 76 columns, and it is the parent of several downstream collection-processing tables. From a data modeling perspective, the mined heuristic classification is satellite-leaning: the table stores descriptive and status attributes attached to the payment-schedule/bank-collection relationship rather than pure hub identifiers. Treat this as a suggested modeling orientation only; the operational structure remains a standard EBS transactional entity keyed by a surrogate identifier.

Key Information Stored

The following columns are the most significant for querying, integration, and reconciliation:

• DOCUMENT_ID — Surrogate primary key, enforced by JL_BR_AR_COLLEC_DOCS_ALL_PK. It uniquely identifies each collection document.
• BORDERO_ID — Foreign key to JL_BR_AR_BORDEROS_ALL. Links the document to its bank transfer batch (borderô).
• PAYMENT_SCHEDULE_ID — The payment schedule (receivable installments) the document corresponds to; joins to AR_PAYMENT_SCHEDULES_ALL.
• DOCUMENT_STATUS — Controlled by lookup type JLBR_AR_COLLECTION_DOCS_STATUS; tracks whether a document is selected, sent, returned, or cancelled.
• DOCUMENT_TYPE — Controlled by lookup type JLBR_AR_DOCUMENT_TYPE.
• ORIGIN_TYPE — Indicates the origin of the collection document.
• CUSTOMER_TRX_ID — The originating transaction in RA_CUSTOMER_TRX_ALL.
• BANK_ACCOUNT_ID — The bank account used for collection; joins to AR_RECEIPT_METHOD_ACCOUNTS_ALL.
• RECEIPT_METHOD_ID — Receipt method associated with the collection; joins to AR_RECEIPT_METHODS.
• CASH_RECEIPT_ID — The actual receipt generated upon collection settlement.
• DUE_DATE / SELECTION_DATE — The document due date and the date it was selected for collection.
• OUR_NUMBER — The collection document identifier assigned by the bank (nosso número).
• FACTORING_RATE and FACTORING_RATE_PERIOD — Trade note discount percentage and period.
• CASH_CCID, EARNED_CCID, ON_ACCOUNT_CCID, and the other accounting distribution columns — foreign keys to GL_CODE_COMBINATIONS used during automatic accounting generation.

The business-key candidate is not the surrogate DOCUMENT_ID. A composite or alternate unique index, though not documented here beyond the surrogate, is typically derived from BORDERO_ID plus PAYMENT_SCHEDULE_ID or OUR_NUMBER.

Common Use Cases and Queries

Typical reporting requirements focus on documents pending transmission, collection success rates, and reconciliation between bank returns and receivables.

• Listing documents for a specific borderô: SELECT DOCUMENT_ID, PAYMENT_SCHEDULE_ID, DOCUMENT_STATUS FROM JL_BR_AR_COLLECTION_DOCS_ALL WHERE BORDERO_ID = :bordero_id;
• Finding unconfirmed documents by status: filter on DOCUMENT_STATUS using the lookup type JLBR_AR_COLLECTION_DOCS_STATUS.
• Reconciling collections to customer transactions: join CUSTOMER_TRX_ID to RA_CUSTOMER_TRX_ALL.
• Checking accounting distributions: join the various _CCID columns to GL_CODE_COMBINATIONS.
• Tracking bank returns: join to JL_BR_AR_BANK_RETURNS_ALL on DOCUMENT_ID to identify rejected or returned items.

Related Objects

The most significant related objects, based on the documented foreign-key relationships, are:

• JL_BR_AR_BORDEROS_ALL — parent batch table via BORDERO_ID.
• AR_PAYMENT_SCHEDULES_ALL — the receivable installments via PAYMENT_SCHEDULE_ID.
• RA_CUSTOMER_TRX_ALL — the source transaction via CUSTOMER_TRX_ID.
• AR_RECEIPT_METHOD_ACCOUNTS_ALL — bank account linkage via BANK_ACCOUNT_ID.
• AR_RECEIPT_METHODS — collection method via RECEIPT_METHOD_ID.
• JL_BR_AR_BANK_RETURNS_ALL — downstream returns referencing DOCUMENT_ID.
• JL_BR_AR_OCCURRENCE_DOCS_ALL — occurrence documents referencing DOCUMENT_ID.
• GL_CODE_COMBINATIONS — accounting segments via the multiple _CCID columns.

These relationships confirm the table's central role in the Brazilian receivables collection lifecycle, bridging Receivables transactions, bank transmission, and General Ledger accounting.