Search Results terms_date

Overview

JL_BR_AP_CONSOLID_INVOICES_V is an APPS-owned database view within the JL – Latin America Localizations product family. It is a retrofitted object, meaning it was carried forward into the EBS 12.1.1 and 12.2.2 code lines from an earlier release and revalidated rather than redesigned. The view presents a denormalized, reporting-friendly projection of the JL_BR_AP_CONSOLID_INVOICES entity, which stores consolidated supplier invoice headers used by the Brazilian localization for accounts payable processing. Its role is primarily declarative: it exposes the consolidated invoice attributes alongside descriptive flexfield columns and enriches each row with vendor, vendor site, payment terms, and pay group descriptions sourced from standard Oracle Payables and Purchasing views. Users searching for the CANCELLED_AMOUNT column will find that this view surfaces it directly, alongside CANCELLED_DATE and CANCELLED_BY, making it a convenient source for reporting on partially or fully cancelled consolidated invoices without joining back to the base table.

Underlying Base Objects

The view is defined over a single driving table with four outer-joined lookups. The documented referenced base objects are AP_TERMS (synonym), FND_GLOBAL (package), JL_BR_AP_CONSOLID_INVOICES (synonym), PO_LOOKUP_CODES (view), PO_VENDORS (view), and PO_VENDOR_SITES (view). In the view text, JL_BR_AP_CONSOLID_INVOICES is aliased as JLCON and supplies every column in the select list except the five descriptive attributes the view adds. PO_VENDORS is outer-joined on VENDOR_ID to provide VENDOR_NAME and SEGMENT1. PO_VENDOR_SITES is outer-joined on VENDOR_SITE_ID to provide VENDOR_SITE_CODE. AP_TERMS is outer-joined on TERMS_ID to provide the term NAME. PO_LOOKUP_CODES is outer-joined twice on the same row — once on LOOKUP_CODE = PAY_GROUP_LOOKUP_CODE and once with LOOKUP_TYPE = 'PAY GROUP' — to yield the pay group DESCRIPTION. All joins use the (+) operator, so a consolidated invoice row is returned even when any lookup is missing, and the ROWID of the base row is preserved as the first selected column.

Key Columns

• CONSOLIDATED_INVOICE_ID – primary identifier of the consolidated invoice record; joins to the base table.
• CANCELLED_AMOUNT – the monetary value cancelled against the consolidated invoice; central to cancellation reporting.
• CANCELLED_DATE and CANCELLED_BY – timestamp and application user who performed the cancellation.
• INVOICE_AMOUNT – original consolidated invoice amount in INVOICE_CURRENCY_CODE.
• AMOUNT_APPLICABLE_TO_DISCOUNT – portion of the invoice eligible for early payment discount.
• VENDOR_ID, VENDOR_NAME, SEGMENT1 – supplier identity from PO_VENDORS.
• VENDOR_SITE_ID, VENDOR_SITE_CODE – supplier site identity from PO_VENDOR_SITES.
• TERMS_ID, NAME, TERMS_DATE – payment terms reference and derived due date basis.
• PAY_GROUP_LOOKUP_CODE, PAY_GROUP_FLAG, pay group DESCRIPTION – payment grouping configuration.
• ORG_ID – operating unit identifier, enabling Multi-Org secured filtering.
• SET_OF_BOOKS_ID – ledger context for the invoice.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 – descriptive flexfield context and segments.
• Audit columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical consumption is in custom concurrent programs, BI Publisher reports, and ad hoc SQL for Brazilian AP reconciliation. Because the view already resolves vendor and terms descriptions, it reduces join complexity. A representative query reconciling cancelled amounts by vendor is:

SELECT vendor_name, invoice_num, invoice_amount,
       cancelled_amount, cancelled_date
FROM   apps.jl_br_ap_consolid_invoices_v
WHERE  org_id = :p_org_id
AND    cancelled_amount IS NOT NULL
AND    cancelled_date >= :p_from_date
ORDER BY cancelled_date DESC;

A second common pattern aggregates cancellation exposure per ledger:

SELECT set_of_books_id, SUM(invoice_amount) total_invoiced,
       SUM(NVL(cancelled_amount,0)) total_cancelled
FROM   apps.jl_br_ap_consolid_invoices_v
GROUP BY set_of_books_id;

Standard Multi-Org and security predicates apply, so queries should constrain ORG_ID. Note the outer joins mean vendor, terms, and pay group columns may be null, and the view text depends on the FND_GLOBAL package for session context, so it must be queried by an APPS-privileged or appropriately granted user.