Results for “trx_status_code”

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

Overview

OKL_CS_LC_ACCOUNT_TAB_UV is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2, and is a component of the OKL — Leasing and Finance Management product family. As its name implies, and as confirmed by the ETRM documentation, the view is defined for the "Lease center accounts tab." It serves as the data source behind the Accounts tab within the Lease Center (Lease Management Center) user interface and the associated reporting and integration layers that surface consolidated and direct receivables activity to leasing users.

The view presents a unified, per-invoice picture of leasing receivables by combining two distinct streams: consolidated AR invoices held in the OKL consolidated AR header structures (OKL_CNSLD_AR_HDRS_B) and direct Oracle Receivables transactions that have been linked to leasing contracts through the OKL transaction-to-AR invoice bridge tables. Because the definition is a UNION ALL of these two branches, a single query against the view yields both consolidated and non-consolidated invoice rows in a consistent column layout, making it suitable for concurrent program output, OAF/Forms-based regions, and ad hoc inquiry.

Underlying Base Objects

The documented base objects referenced by the view are: AR_PAYMENT_SCHEDULES_ALL, FND_GLOBAL, FND_LOOKUPS, GL_LEDGERS_PUBLIC_V, HR_ALL_ORGANIZATION_UNITS, OKL_CNSLD_AR_HDRS_B, OKL_CNSLD_AR_LINES_B, OKL_CNSLD_AR_STRMS_B, OKL_TRX_AR_INVOICES_B, OKL_TXD_AR_LN_DTLS_B, OKL_TXL_AR_INV_LNS_B, RA_CUSTOMER_TRX_ALL, and RA_CUSTOMER_TRX_LINES_ALL.

The first branch joins OKL_CNSLD_AR_HDRS_B to FND_LOOKUPS (constrained to LOOKUP_TYPE = 'OKL_TRANSACTION_STATUS'), HR_ALL_ORGANIZATION_UNITS, and GL_LEDGERS_PUBLIC_V, and filters the header set to those with rows in OKL_CNSLD_AR_STRMS_B, OKL_CNSLD_AR_LINES_B, and AR_PAYMENT_SCHEDULES_ALL. The second branch traverses RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL into the OKL transaction invoice tables OKL_TRX_AR_INVOICES_B, OKL_TXL_AR_INV_LNS_B, and OKL_TXD_AR_LN_DTLS_B, again resolving the transaction status through FND_LOOKUPS and the ledger and organization through GL_LEDGERS_PUBLIC_V and HR_ALL_ORGANIZATION_UNITS. Both branches resolve the set of books (ledger) name and the operating unit organization name, providing a consistent reporting context.

Key Columns

• ID — in the consolidated branch, the OKL_CNSLD_AR_HDRS_B identifier; in the direct branch, the RA_CUSTOMER_TRX_ALL customer_trx_id.
• CONSOLIDATED_INVOICE_NUMBER / TRX_NUMBER — the invoice number from the consolidated header or from Receivables respectively.
• TRX_STATUS_CODE and TRX_STATUS — the raw lookup code and its translated meaning from FND_LOOKUPS for lookup type OKL_TRANSACTION_STATUS.
• DUE_DATE and DATE_CONSOLIDATED / TRX_DATE — the payment schedule due date, plus the consolidation date (consolidated rows) or the transaction date (direct rows).
• AMOUNT — the consolidated header amount, or AMOUNT_DUE_ORIGINAL from AR_PAYMENT_SCHEDULES_ALL in the direct branch.
• IXX_ID / BILL_TO_CUSTOMER_ID — the customer identifier (IXX_ID in the consolidated branch, bill-to customer in the direct branch).
• CURRENCY_CODE — the invoice currency.
• SET_OF_BOOKS, SET_OF_BOOKS_ID — ledger name and identifier from GL_LEDGERS_PUBLIC_V.
• ORG_NAME, ORG_ID — operating unit organization name and identifier from HR_ALL_ORGANIZATION_UNITS.
• INVOICE_PULL_YN — flag indicating whether the invoice was pulled into leasing processing.

Common Use Cases and Queries

The view is typically queried to populate the Lease Center Accounts tab, to reconcile consolidated AR invoices against their direct AR counterparts, and to drive downstream reports on invoice status, aging, and organizational distribution.

List invoices for a specific operating unit and ledger:

SELECT id, consolidated_invoice_number, trx_status,
       due_date, amount, currency_code, org_name
  FROM apps.okl_cs_lc_account_tab_uv
 WHERE org_id = :p_org_id
   AND set_of_books_id = :p_ledger_id
 ORDER BY due_date;

Identify invoices pending pull into leasing:

SELECT id, consolidated_invoice_number, trx_status, amount
  FROM apps.okl_cs_lc_account_tab_uv
 WHERE invoice_pull_yn = 'N'
   AND trx_status_code NOT IN ('CLOSED','CANCELLED');

Aggregate exposure by status and ledger:

SELECT set_of_books, trx_status, SUM(amount) total_amount
  FROM apps.okl_cs_lc_account_tab_uv
 GROUP BY set_of_books, trx_status;

Because the view joins several high-volume AR and OKL tables, queries should always be constrained by ORG_ID, SET_OF_BOOKS_ID, DUE_DATE, or invoice identifiers to avoid full scans across the UNION ALL branches.