Search Results hz_cust_profile_amts_pk

Overview

HZ_CUST_PROFILE_AMTS is a Receivables (AR) transaction table in the Oracle E-Business Suite 12.1.1 and 12.2.2 data model. It stores the credit limits, dunning thresholds, interest parameters, and penalty configuration defined for a customer's credit profile. In the Oracle Trading Community Architecture (TCA) and Receivables model, a customer account is associated with one or more customer profiles, and each profile carries a set of currency-specific amount records. HZ_CUST_PROFILE_AMTS is that currency-level detail table, holding one row for every combination of customer profile and currency, with an optional site-use context.

The table is classified by the metadata as satellite-leaning under a Data Vault heuristic model. This suggests it is best understood as a descriptive satellite attached to the customer profile hub: it carries no independent identity beyond its surrogate key, and its business keys resolve through HZ_CUSTOMER_PROFILES. Practitioners modeling this data for analytics or integration should treat HZ_CUST_PROFILE_AMTS as dependent descriptive state rather than a standalone entity.

Key Information Stored

The table is documented with 95 physical columns. The surrogate primary key is CUST_ACCT_PROFILE_AMT_ID, enforced by index HZ_CUST_PROFILE_AMTS_PK. A second unique index, HZ_CUST_PROFILE_AMTS_U2, spans CUST_ACCOUNT_PROFILE_ID and CURRENCY_CODE, making that pair the effective business key candidate: one row per profile per currency.

The most operationally significant columns include:

• CUST_ACCT_PROFILE_AMT_ID — surrogate primary key, referenced by dependent credit-usage and balance tables.
• CUST_ACCOUNT_PROFILE_ID — foreign key to HZ_CUSTOMER_PROFILES, tying the amount row to its owning profile.
• CURRENCY_CODE — the currency in which the profile amounts are denominated; combined with CUST_ACCOUNT_PROFILE_ID forms the unique business key.
• TRX_CREDIT_LIMIT — transaction (order-level) credit limit.
• OVERALL_CREDIT_LIMIT — aggregate credit limit across all transactions for the customer.
• CUST_ACCOUNT_ID and SITE_USE_ID — foreign keys to HZ_CUST_ACCOUNTS and HZ_CUST_SITE_USES_ALL, allowing a profile amount to be scoped to a specific account or site use.
• MIN_DUNNING_AMOUNT and MIN_DUNNING_INVOICE_AMOUNT — thresholds below which dunning letters are suppressed.
• INTEREST_RATE, INTEREST_TYPE, INTEREST_FIXED_AMOUNT, INTEREST_SCHEDULE_ID, MIN_INTEREST_CHARGE and MAX_INTEREST_CHARGE — late-charge computation parameters, with INTEREST_SCHEDULE_ID referencing CE_INTEREST_SCHEDULES.
• PENALTY_TYPE, PENALTY_RATE, PENALTY_FIXED_AMOUNT and PENALTY_SCHEDULE_ID — penalty charge configuration.
• MIN_FC_BALANCE_AMOUNT, MIN_FC_INVOICE_AMOUNT, and their overdue-type and percent counterparts — foreign-currency collection thresholds.
• Standard WHO audit columns, OBJECT_VERSION_NUMBER for optimistic locking, and the DFF segments ATTRIBUTE1-15, JGZZ_ATTRIBUTE1-15 and GLOBAL_ATTRIBUTE1-20.

Common Use Cases and Queries

Typical reporting scenarios include retrieving the credit limit for a customer account in a given currency, comparing transaction versus overall limits, and auditing dunning and interest thresholds. A representative query joining the profile and amount tables:

• SELECT p.cust_account_profile_id, a.currency_code, a.trx_credit_limit, a.overall_credit_limit FROM hz_cust_profile_amts a JOIN hz_customer_profiles p ON p.cust_account_profile_id = a.cust_account_profile_id WHERE p.cust_account_id = :account_id;
• Site-scoped retrieval: filter on SITE_USE_ID to return limits that apply only to a particular bill-to or ship-to site use.
• Exposure reconciliation: join to HZ_CREDIT_USAGES on CUST_ACCT_PROFILE_AMT_ID to compare current usage against OVERALL_CREDIT_LIMIT.
• Interest and penalty audit: list profiles by INTEREST_SCHEDULE_ID or PENALTY_SCHEDULE_ID to verify charge configuration across currencies.

Because the table is currency-partitioned by the U2 unique index, reports should always group or filter by CURRENCY_CODE to avoid misreading aggregated limits. Concurrency checks should use OBJECT_VERSION_NUMBER when updating profile amounts programmatically.

Related Objects

HZ_CUST_PROFILE_AMTS sits at the centre of the credit configuration model, with the following most significant relationships:

• HZ_CUSTOMER_PROFILES — parent profile; joined on CUST_ACCOUNT_PROFILE_ID.
• HZ_CUST_ACCOUNTS — customer account context; joined on CUST_ACCOUNT_ID.
• HZ_CUST_SITE_USES_ALL — site-use scoping; joined on SITE_USE_ID.
• CE_INTEREST_SCHEDULES — interest schedule definition; joined on INTEREST_SCHEDULE_ID.
• HZ_CREDIT_USAGES — child table recording credit consumption; joins on CUST_ACCT_PROFILE_AMT_ID.
• OE_CREDIT_BALANCES_ALL — order management credit balances; joins on CUST_ACCT_PROFILE_AMT_ID.
• AR_INTEREST_HEADERS_ALL — generated interest headers; joins on CUST_ACCT_PROFILE_AMT_ID.
• AR_LC_CUST_SITES_T — letter-of-credit customer site association; joins on CUST_ACCT_PROFILE_AMT_ID.

Together these objects form the credit control and collections configuration footprint supported by HZ_CUST_PROFILE_AMTS.