Search Results hz_customer_profiles_u1

Overview

The AR.HZ_CUSTOMER_PROFILES table stores the credit and collections characteristics associated with a customer account, a customer account site, or a party within Oracle E-Business Suite. It is a core component of the Oracle Receivables and Trading Community Architecture (TCA) data model, where credit limits, dunning and statement settings, collector assignment, credit review cycles, and related receivables control attributes are persisted for downstream credit management and collections processing.

A profile class defined in HZ_CUSTOMER_PROFILE_CLASSES can provide default values for the attributes in this table. These defaults are copied into HZ_CUSTOMER_PROFILES when a profile row is created and may then be modified to more closely match the specific customer account. The table is owned by the AR schema, is marked VALID at release 12.2.2, and resides in the APPS_TS_TX_DATA tablespace with PCT Free 10. It carries 120 documented columns.

From a heuristic Data Vault modeling perspective, the FK structure classifies this table as satellite-leaning. Its primary key, CUST_ACCOUNT_PROFILE_ID, is a surrogate identifier assigned from a sequence, while the meaningful business relationships (party, account, site use) are held as foreign keys. This structure is consistent with a descriptive satellite attached to customer account and party hubs rather than a standalone hub.

Key Information Stored

The surrogate primary key is CUST_ACCOUNT_PROFILE_ID (NUMBER(15)), the unique identifier of the customer profile. Two unique indexes are documented: HZ_CUSTOMER_PROFILES_U1 on CUST_ACCOUNT_PROFILE_ID, and HZ_CUSTOMER_PROFILES_U2 on the composite of PARTY_ID, CUST_ACCOUNT_ID, and SITE_USE_ID. The U2 index acts as the principal business-key candidate, since it enforces one credit profile per party/account/site-use combination.

• CUST_ACCOUNT_ID — foreign key to HZ_CUST_ACCOUNTS; identifies the customer account the profile belongs to.
• PARTY_ID — foreign key to HZ_PARTIES; identifies the party owning the profile.
• SITE_USE_ID — foreign key to HZ_CUST_SITE_USES_ALL; identifies the specific site use when the profile applies at site level.
• PROFILE_CLASS_ID — foreign key to HZ_CUST_PROFILE_CLASSES; the profile class that supplied default attribute values.
• COLLECTOR_ID — foreign key to AR_COLLECTORS; the collector assigned for collections activity.
• STATEMENT_CYCLE_ID — foreign key to AR_STATEMENT_CYCLES; drives statement generation cycles.
• AUTOCASH_HIERARCHY_ID — foreign key to AR_AUTOCASH_HIERARCHIES; controls automatic receipts matching hierarchy.
• STATUS — indicates whether the profile is active or inactive.
• CREDIT_CHECKING, CREDIT_HOLD, CREDIT_RATING, RISK_CODE, CREDIT_CLASSIFICATION — credit control and risk attributes.
• STANDARD_TERMS, OVERRIDE_TERMS, DISCOUNT_TERMS, TOLERANCE — payment terms and tolerance settings.
• NEXT_CREDIT_REVIEW_DATE, LAST_CREDIT_REVIEW_DATE, REVIEW_CYCLE — credit review scheduling attributes.
• PERCENT_COLLECTABLE, DUNNING_LETTERS, DUNNING_LETTER_SET_ID, INTEREST_CHARGES — collections and dunning configuration.

Common Use Cases and Queries

Typical reporting scenarios include identifying accounts with credit checking disabled, listing accounts past their next credit review date, and aggregating exposure by risk code or collector. Because U2 is a composite unique key, joins are usually seeded from either HZ_CUST_ACCOUNTS or HZ_PARTIES.

• Retrieve the active profile for a specific customer account:

  SELECT cust_account_profile_id, credit_rating, risk_code, credit_hold
FROM hz_customer_profiles
WHERE cust_account_id = :p_account_id AND status = 'A';

• Enforce the business key using the U2 columns:

  SELECT * FROM hz_customer_profiles
WHERE party_id = :p_party AND cust_account_id = :p_account AND site_use_id = :p_site;

• Credit review worklist, filtered by NEXT_CREDIT_REVIEW_DATE, joined to HZ_CUST_ACCOUNTS for the account number and to HZ_PARTIES for the party name.
• Collections posture report grouping by COLLECTOR_ID and STATEMENT_CYCLE_ID.
• Profile-class default audit: compare PROFILE_CLASS_ID defaults against overridden attributes.

Related Objects

The FK metadata for this table names the following significant related objects, all joined on the columns shown.

• HZ_CUST_ACCOUNTS via CUST_ACCOUNT_ID — the account hub this profile describes.
• HZ_PARTIES via PARTY_ID — the party owning the profile.
• HZ_CUST_SITE_USES_ALL via SITE_USE_ID — the site use when the profile is site-specific.
• HZ_CUST_PROFILE_CLASSES via PROFILE_CLASS_ID — source of default attribute values.
• AR_COLLECTORS via COLLECTOR_ID — assigned collector.
• AR_STATEMENT_CYCLES via STATEMENT_CYCLE_ID — statement cycle definition.
• AR_AUTOCASH_HIERARCHIES via AUTOCASH_HIERARCHY_ID — automatic receipts hierarchy.
• HZ_CUST_PROFILE_AMTS and HZ_CUST_PROFILE_AMTS_M — child tables referencing CUST_ACCOUNT_PROFILE_ID, holding currency-level credit limit amounts.

In addition to these FK relationships, the Receivables Credit Management and Collections concurrent programs read this table directly, and the Trading Community customer profile APIs operate on the same underlying columns. Non-unique indexes HZ_CUSTOMER_PROFILES_N1 (CUST_ACCOUNT_ID), N3 (SITE_USE_ID), and N4 (PROFILE_CLASS_ID) support these join paths efficiently.