Search Results cust_acct_profile_amt_id




Overview
Explore in AI Chat

Overview

APPS.HZ_PROFILE_AMTS_V is a consolidated reporting view within the Oracle E-Business Suite Receivables and Trading Community Architecture (TCA) data model. Its purpose is to present credit limit amounts across the four distinct levels at which credit exposure can be defined in Oracle Credit Management: profile class, customer account, operating unit, and item category. The view uses a UNION of four subqueries, each drawn from a different underlying amount table, and normalizes the results into a single uniform row shape.

For users searching on trx_credit_limit, this view is the central integration point because each of its four branches exposes the TRX_CREDIT_LIMIT column alongside OVERALL_CREDIT_LIMIT. The view therefore allows a single query to retrieve per-transaction credit limits defined at any level of the credit hierarchy, which is essential for reporting, credit checking, and external integration. It is delivered under the APPS schema and is available in both EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The view is defined over four base objects, all referenced through APPS synonyms in the documented metadata:

  • HZ_CUST_PROF_CLASS_AMTS — amounts defined at the profile class level, returned with PROFILE_TYPE = 'Profile_Class'.
  • HZ_CUST_PROFILE_AMTS — amounts defined at the customer account profile level, returned with PROFILE_TYPE = 'Customer'.
  • HZ_CREDIT_PROFILE_AMTS — amounts defined against a credit profile, used for the Operating_Unit and Item_Category branches.
  • HZ_CREDIT_PROFILES — the parent credit profile table, joined to HZ_CREDIT_PROFILE_AMTS to distinguish operating unit level rows (organization_id IS NOT NULL) from item category level rows (item_category_id IS NOT NULL).

The four SELECT statements are combined with UNION, and each branch substitutes literal -1 values for the identifier columns not relevant to that level. This technique produces a single, wide result set without NULLs in the key columns, simplifying downstream filters.

Key Columns

  • PROFILE_TYPE — literal discriminator indicating the credit level: 'Profile_Class', 'Customer', 'Operating_Unit', or 'Item_Category'.
  • TRX_CREDIT_LIMIT — the per-transaction credit limit amount, the primary column of interest for the searched term.
  • OVERALL_CREDIT_LIMIT — the aggregate credit limit applied across all transactions for the given profile.
  • CURRENCY_CODE — the currency in which the limits are expressed; limits are typically defined per currency.
  • PROFILE_CLASS_ID, CUST_ACCOUNT_PROFILE_ID, CREDIT_PROFILE_ID — level-specific identifiers; the column relevant to the active PROFILE_TYPE carries a real value while others hold -1.
  • PROFILE_CLASS_AMOUNT_ID, CREDIT_PROFILE_AMT_ID, CUST_ACCT_PROFILE_AMT_ID — surrogate row identifiers from the respective base amount tables.

Common Use Cases and Queries

Typical scenarios include retrieving the transaction credit limit for a specific customer account, auditing limits defined at operating unit or item category level, and feeding credit limit values into external credit-checking or reporting systems.

Retrieve all transaction credit limits for a given customer account:

SELECT profile_type, currency_code, trx_credit_limit, overall_credit_limit
FROM   apps.hz_profile_amts_v
WHERE  cust_account_profile_id = :cust_account_profile_id;

List all operating unit level limits by currency:

SELECT credit_profile_id, currency_code, trx_credit_limit
FROM   apps.hz_profile_amts_v
WHERE  profile_type = 'Operating_Unit';

Because the view blends four sources, always filter on PROFILE_TYPE or on the appropriate non-negative identifier column to avoid mixing levels. Note that the -1 sentinel values (rather than NULLs) are used consistently across all branches, so equality filters must account for this convention.