Results for “okl_ext_billing_intf_all”

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

Overview

The OKL_EXT_BILLING_INTF_ALL table is a transient staging interface owned by the OKL schema within Oracle E-Business Suite's Leasing and Finance Management module (OKL). Its documented description, "External Billing Interface," defines its role as a landing area for billing records sourced from external systems that must be validated, transformed, and consumed by the ETRM (Enterprise Tax and Revenue Management) invoicing and tax assessment processes. In release 12.1.1 and 12.2.2 the table is a multi-org enabled object, evidenced by the included ORG_ID column.

Because the table carries 83 physical columns and a single foreign key referencing JAI_CMN_CUS_ADDRESSES, a heuristic Data Vault classification would place it as a standalone structure. In Data Vault terms this suggests it functions as an independent table rather than a hub, link, or satellite with strong parent-child dependency. Its only documented outbound reference is CUSTOMER_ADDRESS_ID, meaning the table does not participate in a dense network of foreign-key relationships.

Key Information Stored

• SEQUENCE_NUMBER – the primary row identifier and surrogate key for interface processing order.
• TRX_STATUS_CODE and ERROR_MESSAGE – status of the interface record and any validation failure text.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE – concurrent program context tracking the run that populated or processed the row.
• CUSTOMER_ID, CUSTOMER_REF, CUSTOMER_ADDRESS_ID, CUSTOMER_ADDRESS_REF – the billed party and its address reference; CUSTOMER_ADDRESS_ID is the documented FK to JAI_CMN_CUS_ADDRESSES.
• CONTRACT_ID, CONTRACT_NUMBER, STY_ID, STY_NAME – the lease contract and stream referenced by the billing event.
• ASSET_ID, ASSET_NUMBER – the asset being billed.
• INVOICE_DATE, AMOUNT, CURRENCY_CODE, RECORD_TYPE – core financial attributes describing the invoice and its type.
• TAX_ASSESSMENT_AMOUNT, TAX_ASSESSMENT_DATE, MLRT_TAX – tax liability amounts computed for the line.
• TAX_JURSDCTN_* and MLRT_TAX_JURSDCTN_* columns – jurisdiction breakdowns (county, city, state, school, country and multilingual equivalents).
• TAX_VENDOR_ID, TAX_VENDOR_NAME, TAX_VENDOR_SITE_ID, TAX_VENDOR_SITE_NAME – vendor attributed for tax remittance.
• ORG_ID – operating unit identifier enabling multi-org security.
• INTERFACE_ATTRIBUTE1–15, ATTRIBUTE1–15 – extensibility fields populated by external source systems.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER – standard Who columns and optimistic locking support.

Beyond the surrogate key, business identifiers such as CONTRACT_NUMBER, CUSTOMER_REF, and TAI_ID serve as natural key candidates; the ETRM documentation does not enumerate a unique index explicitly, so no unique constraint is asserted here.

Common Use Cases and Queries

Typical uses include monitoring interface load status, reconciling failed rows, and feeding downstream tax reporting. A common pattern checks unprocessed records:

SELECT SEQUENCE_NUMBER, CONTRACT_NUMBER, CUSTOMER_REF, AMOUNT, TRX_STATUS_CODE, ERROR_MESSAGE FROM OKL.OKL_EXT_BILLING_INTF_ALL WHERE TRX_STATUS_CODE = 'ERROR' AND ORG_ID = :p_org_id;

Reporting queries frequently aggregate tax amounts by jurisdiction, e.g. grouping on TAX_JURSDCTN_STE and TAX_JURSDCTN_CNTY, or sum TAX_ASSESSMENT_AMOUNT by TAX_VENDOR_ID. Concurrent program diagnostics join on REQUEST_ID to trace a load back to its FND_CONCURRENT_REQUESTS record. Interface reconciliation commonly filters on DATE_TRANSMISSION and INVOICE_DATE to bound a processing window.

Related Objects

• JAI_CMN_CUS_ADDRESSES – referenced by OKL_EXT_BILLING_INTF_ALL.CUSTOMER_ADDRESS_ID; supplies customer address attributes.
• OKL_CONTRACTS / OKL_STRM_* – contract and stream sources joined via CONTRACT_ID and STY_ID.
• OKL_ASSETS – asset master joined via ASSET_ID / ASSET_NUMBER.
• FND_CONCURRENT_REQUESTS / FND_CONCURRENT_PROGRAMS – program context via REQUEST_ID and PROGRAM_ID.
• RA_CUSTOMER_TRX_ALL – downstream receivables invoice repository populated after interface processing.
• JAI_CMN_TAX_LINES / tax vendor tables – tax remittance consumers keyed on TAX_VENDOR_ID.
• HR_OPERATING_UNITS – operating unit join via ORG_ID.

Because the table is documented as standalone, integration is driven primarily through concurrent program processing rather than cascading referential constraints.