Search Results hz_cust_prof_class_amts_u2

Overview

The HZ_CUST_PROF_CLASS_AMTS table, owned by the AR schema, stores credit limit and related financial control information for customer profile classes at the currency level. A profile class may define different credit limits for each currency in which the enterprise conducts business, allowing organizations to manage exposure independently per currency. For example, the profile class "Large Independent Retail Stores" may carry an overall credit limit of 250,000 EUR and a per-transaction limit of 50,000 EUR, with separate rows for other currencies. Each row also carries collections-related attributes such as dunning thresholds, interest rates, penalty configuration, and minimum statement amounts. The table is classified under FND Design Data as AR.HZ_CUST_PROF_CLASS_AMTS and resides in the APPS_TS_TX_DATA tablespace with PCT Free 10.

From a Data Vault modeling perspective, the metadata heuristic classifies this table as satellite-leaning. This reflects its structure: the table holds descriptive, time-versioned attributes (limits, rates, thresholds) attached to a business key formed by PROFILE_CLASS_ID and CURRENCY_CODE, rather than acting as an independent hub of business entities. The recommended interpretation is that this table behaves as a satellite to the customer profile class hub, joined by the profile class identifier.

Key Information Stored

The surrogate primary key is PROFILE_CLASS_AMOUNT_ID, a NUMBER(15) column uniquely indexed by HZ_CUST_PROF_CLASS_AMTS_U1. The business-key candidate is the composite of PROFILE_CLASS_ID and CURRENCY_CODE, enforced by the unique index HZ_CUST_PROF_CLASS_AMTS_U2; this pair guarantees one credit configuration row per profile class per currency.

• PROFILE_CLASS_ID — Foreign key to HZ_CUST_PROFILE_CLASSES identifying the owning profile class.
• CURRENCY_CODE — The currency for which the limits apply; together with PROFILE_CLASS_ID forms the unique business key.
• OVERALL_CREDIT_LIMIT — The aggregate credit ceiling granted to customers in this profile class for the given currency.
• TRX_CREDIT_LIMIT — The maximum credit permitted for a single transaction.
• MIN_DUNNING_AMOUNT and MIN_DUNNING_INVOICE_AMOUNT — Thresholds below which dunning letters are not generated.
• MIN_STATEMENT_AMOUNT — Minimum balance required before a statement is produced.
• INTEREST_RATE, INTEREST_TYPE, INTEREST_FIXED_AMOUNT, INTEREST_SCHEDULE_ID — Configuration governing late-payment interest, including the schedule referenced from CE_INTEREST_SCHEDULES.
• PENALTY_TYPE, PENALTY_RATE, PENALTY_FIXED_AMOUNT, PENALTY_SCHEDULE_ID — Penalty charge configuration for delinquent balances.
• MAX_INTEREST_CHARGE and MIN_INTEREST_CHARGE — Bounds applied to calculated interest amounts.
• EXPIRATION_DATE — Date on which the credit configuration lapses.
• EXCHANGE_RATE_TYPE — Rate type applied when converting amounts to the ledger currency.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle ADF business components layer.

The table also carries the standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN), concurrent program columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE), a WH_UPDATE_DATE warehouse tracking column, and three attribute flexfield groupings: the DFF attributes (ATTRIBUTE_CATEGORY and ATTRIBUTE1–15), the global DFF (GLOBAL_ATTRIBUTE_CATEGORY and GLOBAL_ATTRIBUTE1–20), and the JGZZ (localization) attributes JGZZ_ATTRIBUTE_CATEGORY and JGZZ_ATTRIBUTE1–15, giving a documented total of 91 columns.

Common Use Cases and Queries

Credit management reporting is the most frequent use. A collections analyst may retrieve the effective limits for a profile class across all currencies to verify exposure. A representative query joins the class master to its currency-level limits:

• SELECT p.PROFILE_CLASS_NAME, a.CURRENCY_CODE, a.OVERALL_CREDIT_LIMIT, a.TRX_CREDIT_LIMIT FROM HZ_CUST_PROF_CLASS_AMTS a, HZ_CUST_PROFILE_CLASSES p WHERE a.PROFILE_CLASS_ID = p.PROFILE_CLASS_ID AND p.PROFILE_CLASS_NAME = :name;
• Interest and penalty audit: query INTEREST_SCHEDULE_ID, INTEREST_RATE, and PENALTY_RATE to confirm that late-charge configuration matches finance policy, joining CE_INTEREST_SCHEDULES for schedule detail.
• Utilization analysis: aggregate credit consumed against OVERALL_CREDIT_LIMIT by joining HZ_CREDIT_USAGES on PROFILE_CLASS_AMOUNT_ID, since that child table references this parent.
• Change tracking: filter on LAST_UPDATE_DATE and LAST_UPDATED_BY for audit extracts of credit limit revisions.
• Expiration monitoring: report rows where EXPIRATION_DATE falls within a forthcoming period to trigger review.

The U2 index on (PROFILE_CLASS_ID, CURRENCY_CODE) makes the join to HZ_CUST_PROFILE_CLASSES and currency-filtered lookups efficient.

Related Objects

• HZ_CUST_PROFILE_CLASSES — Parent table; joined on PROFILE_CLASS_ID. Holds the profile class definition itself.
• HZ_CREDIT_USAGES — Child table; references this table via PROFILE_CLASS_AMOUNT_ID, tracking credit consumption against the configured limits.
• CE_INTEREST_SCHEDULES — Referenced by INTEREST_SCHEDULE_ID; defines the interest schedule applied when interest is assessed.
• FND_USER — Referenced by the WHO columns CREATED_BY and LAST_UPDATED_BY.
• FND_CONCURRENT_REQUESTS — Referenced by REQUEST_ID for concurrent program provenance.
• FND_APPLICATION and FND_CONCURRENT_PROGRAM — Referenced by PROGRAM_APPLICATION_ID and PROGRAM_ID respectively.
• HZ_CUSTOMER_PROFILES — Related via the customer profile that inherits limits from its assigned profile class.