Results for “invoice_status”

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

Overview

OKL_CS_PAY_INVOICES_UV is a consolidated, user-facing view in the APPS schema belonging to the OKL (Leasing and Finance Management) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a denormalized, reporting-friendly projection of payables invoices generated from OKL contract transactions. The view joins OKL consolidated AP invoice data to Oracle Payables invoice headers, supplier master and supplier site records, FND lookup translations, and payment terms, resolving internal codes into descriptive meanings and exposing payment and amount-paid information in a single query surface.

Its principal role is to support contract-to-payables reconciliation, payment status inquiry, and supplier-facing reporting where the business user requires both the original OKL invoice context and the downstream AP invoice and settlement data. Because it resolves lookups such as payment method, transaction status, and payables invoice type into their displayable meanings, it is frequently consumed by concurrent programs, Oracle Reports, OAF pages, and ad hoc discoverer or BI Publisher queries within the Leasing module.

Underlying Base Objects

The ETRM 12.2.2 metadata records the following referenced base objects: AP_INVOICES_ALL, AP_TERMS_TL, FND_APPLICATION, FND_GLOBAL, FND_LOOKUPS, OKL_CNSLD_AP_INVS_ALL, PO_VENDORS, and PO_VENDOR_SITES_ALL, most of which are accessed through APPS synonyms.

The driving table is OKL_CNSLD_AP_INVS_ALL (aliased CNSLD), which holds the consolidated OKL invoice records. It is joined to AP_INVOICES_ALL (API) on the condition that API.APPLICATION_ID is the OKL application, API.PRODUCT_TABLE equals 'OKL_CNSLD_AP_INVS_ALL', and CNSLD.CNSLD_AP_INV_ID equals TO_NUMBER(API.REFERENCE_KEY1). Organization context is preserved using NVL comparisons on ORG_ID. Supplier data is joined through PO_VENDORS and PO_VENDOR_SITES_ALL on VENDOR_ID and VENDOR_SITE_ID. Translation of coded values is provided by three aliases of FND_LOOKUPS (payment methods, transaction status, payables invoice type) and by AP_TERMS_TL filtered to the session language via USERENV('LANG'). FND_APPLICATION scopes the invoice join to the OKL application short name, and FND_GLOBAL supplies session context.

Key Columns

• IPVS_ID — Identifier from the consolidated OKL invoice source record.
• CURRENCY — Currency code of the invoice amount.
• INVOICE_NUMBER / INVOICE_TYPE — OKL invoice number and its type, the latter resolved through the OKL_PAYABLES_INVOICE_TYPE lookup.
• AMOUNT — Invoice amount as recorded on the OKL consolidated record.
• PAID_AMOUNT — Amount paid on the corresponding AP invoice (API.AMOUNT_PAID). This is the column most relevant to searches for paid_amount, and it reflects the settled portion of the AP invoice, not the original OKL amount.
• INVOICE_STATUS — Descriptive status derived from OKL_TRANSACTION_STATUS lookups.
• TRANSACTION_TYPE — Meaning from the OKL_PAYABLES_INVOICE_TYPE lookup.
• PAYMENT_METHOD / PAYMENT_METHOD_CODE — Simultaneously expose the lookup meaning and the underlying coded value for OKL_PAYMENT_METHODS.
• VENDOR_NAME, VENDOR_SITE_CODE, VENDOR_ID — Supplier and supplier site attributes.
• PAYMENTS_TERMS — Term name from AP_TERMS_TL for the associated payment term identifier.
• CNSLD_AP_INV_ID, INVOICE_ID, IPPS_ID/TERM_ID — Technical keys linking back to the OKL consolidated invoice, the AP invoice, and the payment term.

Common Use Cases and Queries

Typical usage includes identifying invoices awaiting payment, reconciling OKL contract billings against AP settlement, and reporting paid versus outstanding balances by supplier. Because the view already resolves lookup meanings, it is generally preferred over querying the base tables directly for operational inquiry screens.

The following examples illustrate practical access patterns:

• Retrieve all invoices with any amount paid:
  SELECT invoice_number, vendor_name, amount, paid_amount
FROM apps.okl_cs_pay_invoices_uv
WHERE paid_amount > 0;
• Determine outstanding balance by subtracting the settled portion:
  SELECT invoice_number, vendor_name, amount - paid_amount outstanding
FROM apps.okl_cs_pay_invoices_uv
WHERE amount - paid_amount > 0;
• Aggregate total billed versus total paid per supplier:
  SELECT vendor_name, SUM(amount) billed, SUM(paid_amount) settled
FROM apps.okl_cs_pay_invoices_uv
GROUP BY vendor_name;
• Filter by status or payment method using the translated meanings, for example WHERE invoice_status = 'Approved' or WHERE payment_method = 'Check', avoiding the need to know lookup codes.

Implementers should note that the join to AP_INVOICES_ALL is anchored on REFERENCE_KEY1 and the OKL product table, so records without a matching AP invoice are excluded. Queries should also respect organization security where ORG_ID is populated, since the view uses NVL-based organization matching rather than the standard ORG_ID profile filter.