Search Results overall_credit_limit




Overview
Explore in AI Chat

Overview

HZ_PROFILE_AMTS_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite Receivables (AR). Its purpose is to consolidate credit limit information that is otherwise dispersed across several related hierarchy tables into a single, uniformly structured result set. Within the Oracle Trading Community Architecture (TCA) and credit management model, credit limits can be defined at multiple profile levels — profile class, customer account, operating unit, and item category. The view assembles all of these levels and tags each row with a PROFILE_TYPE discriminator, enabling downstream reports, credit-checking logic, and integrations to query one object rather than navigating four separate base tables.

The view is defined in ETRM as product AR – Receivables and carries status VALID. It is particularly relevant to searches involving the overall_credit_limit concept, since OVERALL_CREDIT_LIMIT is one of the ten columns it exposes.

Underlying Base Objects

The view text is a four-way UNION ALL constructed UNION of source tables. Per ETRM metadata, the referenced base objects are HZ_CUST_PROF_CLASS_AMTS, HZ_CUST_PROFILE_AMTS, HZ_CREDIT_PROFILE_AMTS, and HZ_CREDIT_PROFILES (all accessed through public synonyms).

  • HZ_CUST_PROF_CLASS_AMTS — supplies rows typed as PROFILE_CLASS, holding profile-class-level limits.
  • HZ_CUST_PROFILE_AMTS — supplies rows typed as CUSTOMER, holding customer-account-level limits.
  • HZ_CREDIT_PROFILE_AMTS joined to HZ_CREDIT_PROFILES — supplies rows typed as OPERATING_UNIT where ORGANIZATION_ID is not null, and as ITEM_CATEGORY where ITEM_CATEGORY_ID is not null.

Because each UNION branch selects from a different table, the view uses sentinel values of -1 to fill identifier columns that do not apply to a given profile type. A surrogate key column (for example PROFILE_CLASS_AMOUNT_ID or CREDIT_PROFILE_AMT_ID) is populated only for its corresponding branch, letting consumers join back to the correct detail table if needed.

Key Columns

  • PROFILE_TYPE — literal discriminator: PROFILE_CLASS, CUSTOMER, OPERATING_UNIT, or ITEM_CATEGORY.
  • CURRENCY_CODE — the currency in which the amounts are expressed; limits are currency-specific.
  • TRX_CREDIT_LIMIT — the transaction-level credit limit at the given profile level.
  • OVERALL_CREDIT_LIMIT — the aggregate/total credit limit ceiling for the profile, the column most commonly targeted by searches.
  • Source identifiers — PROFILE_CLASS_ID, CUST_ACCOUNT_PROFILE_ID, CREDIT_PROFILE_ID, PROFILE_CLASS_AMOUNT_ID, CREDIT_PROFILE_AMT_ID, and CUST_ACCT_PROFILE_AMT_ID, each populated for its applicable branch and set to -1 otherwise.

Common Use Cases and Queries

Typical uses include reporting all credit limits for a customer across every applicable profile level, and comparing transaction versus overall limits for credit exposure analysis.

SELECT profile_type, currency_code, trx_credit_limit, overall_credit_limit
FROM   apps.hz_profile_amts_v
WHERE  profile_type = 'CUSTOMER'
ORDER BY currency_code;

SELECT profile_type, currency_code, overall_credit_limit
FROM   apps.hz_profile_amts_v
WHERE  overall_credit_limit > 0;

Because the view is a UNION of independent branches, filtering on PROFILE_TYPE before joining to detail tables improves performance and clarity. Note that no organization or customer name is exposed; consumers join the returned identifiers to HZ_CUST_ACCOUNTS, HZ_CUST_PROFILES, or HZ_CREDIT_PROFILES to obtain descriptive context.