Search Results hz_cust_profile_amts_n1

Overview

The AR.HZ_CUST_PROFILE_AMTS table is a core component of the Oracle E-Business Suite (EBS) Trading Community Architecture (TCA) and Receivables (AR) credit management model. It stores the credit limit amounts and related credit control parameters defined for a customer profile, segmented by currency. While the parent table HZ_CUSTOMER_PROFILES holds profile information for parties, customer accounts, and customer account sites, HZ_CUST_PROFILE_AMTS provides the multi-currency monetary detail: overall and transaction credit limits, dunning and statement thresholds, interest configuration, and penalty parameters. This separation allows a single profile to carry distinct limits per currency, which is essential for global organizations transacting in multiple currencies.

The table is owned by the AR schema, resides in the APPS_TS_TX_DATA tablespace, and has a status of VALID in the 12.2.2 ETRM footprint with 95 documented columns. Under a heuristic Data Vault classification, the table leans toward a satellite: it is a child of HZ_CUSTOMER_PROFILES and attaches descriptive, time-variant monetary attributes to that parent business key. This classification is a modeling suggestion, not a documented EBS construct.

Key Information Stored

The surrogate primary key is CUST_ACCT_PROFILE_AMT_ID, enforced by the unique index HZ_CUST_PROFILE_AMTS_U1. The business-key candidate is the composite of CUST_ACCOUNT_PROFILE_ID and CURRENCY_CODE, declared by the unique index HZ_CUST_PROFILE_AMTS_U2 — the index name directly matches the search term. This uniqueness guarantees at most one amount row per profile per currency.

The most significant columns are:

• CUST_ACCT_PROFILE_AMT_ID — surrogate primary key and unique identifier of the profile amount row.
• CUST_ACCOUNT_PROFILE_ID — foreign key to HZ_CUSTOMER_PROFILES; identifies the owning profile.
• CURRENCY_CODE — the currency in which the limits and thresholds are expressed.
• OVERALL_CREDIT_LIMIT — total credit exposure ceiling for the profile in the given currency.
• TRX_CREDIT_LIMIT — maximum credit permitted for a single transaction.
• MIN_DUNNING_AMOUNT and MIN_DUNNING_INVOICE_AMOUNT — dunning thresholds.
• MIN_STATEMENT_AMOUNT — minimum balance before a statement is generated.
• INTEREST_RATE, INTEREST_TYPE, INTEREST_FIXED_AMOUNT, and INTEREST_SCHEDULE_ID — late-charge interest configuration, with the schedule FK to CE_INTEREST_SCHEDULES.
• PENALTY_RATE, PENALTY_TYPE, PENALTY_FIXED_AMOUNT, and PENALTY_SCHEDULE_ID — penalty charge configuration.
• CUST_ACCOUNT_ID and SITE_USE_ID — optional denormalized references to the customer account and site use the profile applies to.
• Standard Who columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) and OBJECT_VERSION_NUMBER for concurrency control.

Common Use Cases and Queries

The table is queried primarily for credit exposure reporting, currency-specific limit enforcement, and dunning/interest configuration audits. A frequent pattern joins the amount rows back to the parent profile and to the customer account:

• List all currency limits for a profile: SELECT CURRENCY_CODE, OVERALL_CREDIT_LIMIT, TRX_CREDIT_LIMIT FROM HZ_CUST_PROFILE_AMTS WHERE CUST_ACCOUNT_PROFILE_ID = :profile_id;
• Reconcile profile limits to the profile class defaults in HZ_CUST_PROF_CLASS_AMTS to detect overridden customer-level limits.
• Review interest and penalty settings per currency before running Receivables interest and dunning concurrent programs; INTEREST_SCHEDULE_ID and PENALTY_SCHEDULE_ID link to CE_INTEREST_SCHEDULES.
• Aggregate exposure by currency: SELECT CURRENCY_CODE, SUM(OVERALL_CREDIT_LIMIT) FROM HZ_CUST_PROFILE_AMTS GROUP BY CURRENCY_CODE;
• Trace credit usage rows in HZ_CREDIT_USAGES and OE_CREDIT_BALANCES_ALL back to the governing limit row via CUST_ACCT_PROFILE_AMT_ID.

Related Objects

The table's foreign keys reference HZ_CUSTOMER_PROFILES (via CUST_ACCOUNT_PROFILE_ID), HZ_CUST_ACCOUNTS (via CUST_ACCOUNT_ID), HZ_CUST_SITE_USES_ALL (via SITE_USE_ID), and CE_INTEREST_SCHEDULES (via INTEREST_SCHEDULE_ID). Tables referencing this row as a parent include HZ_CREDIT_USAGES, OE_CREDIT_BALANCES_ALL, AR_LC_CUST_SITES_T, and AR_INTEREST_HEADERS_ALL, all through the CUST_ACCT_PROFILE_AMT_ID column. Defaults for the amount columns frequently originate from HZ_CUST_PROF_CLASS_AMTS, which mirrors the profile class hierarchy and is the natural comparison target for override analysis. Indexes HZ_CUST_PROFILE_AMTS_N1, N2, and N3 on CUST_ACCOUNT_PROFILE_ID, SITE_USE_ID, and CUST_ACCOUNT_ID respectively support these join paths efficiently.