Search Results oe_credit_balances_u1

Overview

ONT.OE_CREDIT_BALANCES_ALL is a summary table in the Oracle E-Business Suite Order Management (ONT) schema. It stores the current credit exposure data for each individual customer and customer site that has been assigned a credit limit. For every customer or site with a credit limit — including limits inherited from default profiles — the table maintains one row per valid credit rule defined in the system. The table is populated and refreshed by the automatic credit check concurrent program, which aggregates order and payment activity into precalculated exposure values used by the credit checking engine during order entry and order import.

Because the table is periodically rebuilt by a concurrent program rather than transactionally maintained, it functions as a performance-oriented cache of credit exposure. The rows are organization-sensitive: ORG_ID is present, so balances are segregated by operating unit. Under a heuristic Data Vault classification, this object most closely resembles a hub-and-satellite hybrid, with CUST_ACCOUNT_ID and CREDIT_CHECK_RULE_ID forming the effective business composite key and the exposure measures acting as satellite attributes; however, given the summary/cache nature of the data, it is more accurately treated as a derived aggregate rather than a source-of-truth entity.

Key Information Stored

The table contains 36 documented columns. The most significant are:

• CREDIT_BALANCE_ID — surrogate primary key, enforced by the unique index OE_CREDIT_BALANCES_U1. This is the only documented unique index and therefore the sole business-key candidate in the physical model.
• CUST_ACCOUNT_ID — foreign key to HZ_CUST_ACCOUNTS; identifies the customer account whose exposure is summarized.
• CUST_ACCT_PROFILE_AMT_ID — foreign key to HZ_CUST_PROFILE_AMTS; populated when the credit limit originates from a customer or site profile.
• CREDIT_PROFILE_AMT_ID — foreign key to HZ_CREDIT_PROFILE_AMTS; populated when the credit limit originates from an operating unit default profile.
• CREDIT_CHECK_RULE_ID — foreign key to OE_CREDIT_CHECK_RULES; identifies the credit rule under which the exposure was computed.
• ORDERS — uninvoiced order value for the customer or site.
• ORDERS_ON_HOLD — value of orders currently on credit hold.
• PAYMENTS_OVERDUE — overdue receivable amount.
• PAYMENTS_AT_RISK — amount considered at risk of non-collection.
• OVERALL_EXPOSURE — sum of ORDERS, ORDERS_ON_HOLD, PAYMENTS_OVERDUE, and PAYMENTS_AT_RISK; the principal value compared against the credit limit.
• ORG_ID — operating unit segregation.
• Standard WHO and concurrent program columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and REQUEST_ID, which record the concurrent request that last refreshed each row.
• CONTEXT and ATTRIBUTE1–ATTRIBUTE15 — the standard extensibility (DFF) columns.

Common Use Cases and Queries

Typical uses include exposing current customer credit position to order entry users, generating credit exposure reports, and diagnosing why an order was placed on credit hold. A common query joins balances to the customer account and the credit rule:

• SELECT b.cust_account_id, b.credit_check_rule_id, b.orders, b.orders_on_hold, b.payments_overdue, b.payments_at_risk, b.overall_exposure FROM oe_credit_balances_all b WHERE b.org_id = :org_id AND b.cust_account_id = :cust_account_id;
• Correlating a balance row to its limit source: join CUST_ACCT_PROFILE_AMT_ID to HZ_CUST_PROFILE_AMTS, or CREDIT_PROFILE_AMT_ID to HZ_CREDIT_PROFILE_AMTS, depending on which column is populated.
• Identifying stale data by inspecting REQUEST_ID and PROGRAM_UPDATE_DATE against the last run of the automatic credit check program — if exposure is out of date, the concurrent program must be rerun.
• Reporting overall exposure by operating unit using ORG_ID, with drill-down via CUST_ACCOUNT_ID.

The secondary indexes OE_CREDIT_BALANCES_N1 through N4 on CUST_ACCOUNT_ID, CREDIT_CHECK_RULE_ID, CUST_ACCT_PROFILE_AMT_ID, and CREDIT_PROFILE_AMT_ID support these join and lookup patterns.

Related Objects

The most significant related objects, based on documented foreign key relationships and parent tables, are:

• HZ_CUST_ACCOUNTS — referenced via CUST_ACCOUNT_ID; the master customer account definition.
• HZ_CUST_PROFILE_AMTS — referenced via CUST_ACCT_PROFILE_AMT_ID; customer/site-level credit limit amounts.
• HZ_CREDIT_PROFILE_AMTS — referenced via CREDIT_PROFILE_AMT_ID; operating unit default credit limit amounts.
• OE_CREDIT_CHECK_RULES — referenced via CREDIT_CHECK_RULE_ID; defines the credit rules for which a balance row exists.
• OE_ORDER_HEADERS_ALL — the transactional source from which uninvoiced and on-hold order amounts are aggregated.
• RA_CUSTOMER_TRX_ALL and related receivables tables — the source of overdue and at-risk payment amounts.
• The automatic credit check concurrent program — the process that inserts and refreshes all rows in this table; no manual DML should be performed against it.