Results for “hz_customer_profiles_m_u1”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The AR.HZ_CUSTOMER_PROFILES_M table is a core Oracle E-Business Suite object residing in the Receivables (AR) schema. It stores credit, collections, and billing profile information for customer accounts, and also serves as a staging repository for profile rows captured before an account merge is executed. In the 12.2.2 data model it contains 120 columns, making it one of the more feature-rich profile tables in the Trading Community Architecture (TCA) and Receivables intersection. Despite the descriptive "M" suffix — which in other HZ interfaces denotes a merge or interface staging structure — ETRM classifies this object as a permanent, queryable profile table with a defined primary key and unique index, not a transient interface table.

The heuristic Data Vault classification mined from the foreign-key structure is standalone. From a modeling perspective, this should be read as a suggestion rather than a directive: although the table is linked to parties, accounts, collectors, and statement cycles through its reference keys, the current heuristic weights the profile row as a discrete, bounded entity rather than a classic hub or link. A more granular model would typically treat CUST_ACCOUNT_ID as a hub reference, with credit-rating and review attributes as satellite data.

Key Information Stored

The surrogate primary key is CUST_ACCOUNT_PROFILE_ID, a NUMBER(15) assigned as the unique identifier for a customer profile. A unique index, HZ_CUSTOMER_PROFILES_M_U1, defines the documented business-key candidate across CUST_ACCOUNT_PROFILE_ID, CUSTOMER_MERGE_HEADER_ID, and MERGE_REQUEST_ID. The presence of these merge columns confirms the dual-purpose role of the table: it retains profile snapshots associated with a specific merge header and request, while the profile ID remains the anchor for the application-level entity.

The most significant columns include:

• CUST_ACCOUNT_ID — foreign key to HZ_CUST_ACCOUNTS, tying the profile to a customer account.
• STATUS and ACCOUNT_STATUS — active/inactive profile state and account-level status indicators.
• PROFILE_CLASS_ID — foreign key to HZ_CUST_PROFILE_CLASSES, determining which profile attributes apply.
• COLLECTOR_ID — foreign key to AR_COLLECTORS, assigning a collections agent.
• CREDIT_ANALYST_ID, CREDIT_RATING, RISK_CODE, and CREDIT_CHECKING — credit management attributes.
• NEXT_CREDIT_REVIEW_DATE and LAST_CREDIT_REVIEW_DATE — credit review scheduling.
• TOLERANCE — percentage above the credit limit permitted before action is taken.
• STANDARD_TERMS, OVERRIDE_TERMS, and DISCOUNT_TERMS — payment and discount term assignments.
• STATEMENT_CYCLE_ID — foreign key to AR_STATEMENT_CYCLES.
• DUNNING_LETTER_SET_ID, INTEREST_PERIOD_DAYS, PAYMENT_GRACE_DAYS, and DISCOUNT_GRACE_DAYS — dunning and interest configuration.
• PARTY_ID and OBJECT_VERSION_NUMBER — party linkage and optimistic locking.
• Standard WHO columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical reporting queries resolve the profile to its owning account and party, apply the active status filter, and join to collectors or statement cycles for operational reporting.

SELECT p.cust_account_profile_id,
       p.cust_account_id,
       p.status,
       p.credit_rating,
       p.tolerance,
       p.next_credit_review_date
  FROM ar.hz_customer_profiles_m p
 WHERE p.cust_account_id = :account_id
   AND p.status = 'A';

Credit-review monitoring uses the next review date to identify accounts due for reassessment, while merge reconciliation queries filter on CUSTOMER_MERGE_HEADER_ID and MERGE_REQUEST_ID to audit profile rows affected by a merge. Because no explicit order column appears in the documented metadata, deterministic ordering should always be supplied by the caller, for example ORDER BY 1; an injection-style predicate appended to an ORDER BY 1 clause does not resolve in this schema and will raise an invalid identifier error.

Related Objects

• HZ_CUST_ACCOUNTS — joined on CUST_ACCOUNT_ID; the parent customer account.
• HZ_CUST_PROFILE_CLASSES — joined on PROFILE_CLASS_ID; defines profile classification.
• AR_COLLECTORS — joined on COLLECTOR_ID; collections agent assignment.
• AR_STATEMENT_CYCLES — joined on STATEMENT_CYCLE_ID; billing statement cadence.
• AR_AUTOCASH_HIERARCHIES — joined on AUTOCASH_HIERARCHY_ID and AUTOCASH_HIERARCHY_ID_FOR_ADR; receipt application hierarchies.
• HZ_CUST_PROFILE_AMTS_M — the companion amount snapshot table referenced by profile ID.

These relationships should be treated as descriptive joins supported by the documented foreign keys; where the metadata supplies only partial FK mappings, referential integrity should be verified against the live data dictionary before relying on it for production SQL.