Results for “cust_prof_class_amount_id”

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

Overview

AR_CUST_PROF_CLASS_AMOUNTS is a Receivables (AR) transaction table that stores the currency-specific amount limits and thresholds associated with a customer profile class. In Oracle EBS, customer profile classes act as templates that govern credit, collections, statement, and dunning behavior. Because monetary controls must be expressed against a specific currency, Oracle separates the profile class definition (a single logical record) from its currency-denominated values. This table is that second layer: each row represents the limits defined for one profile class in one currency.

Rows are created and maintained through the Receivables customer profile class setup forms, and they are read at runtime by credit checking, collections, dunning letter generation, late-charge and interest computation, and automatic receipt application logic. The table is therefore a control table rather than a transactional or history table; it holds configuration that downstream AR transactions consume.

From a Data Vault modeling perspective, the heuristic classification of this object is a link. The metadata documents two foreign keys — CUSTOMER_PROFILE_CLASS_ID referencing AR_CUSTOMER_PROFILE_CLASSES and CURRENCY_CODE referencing FND_CURRENCIES — and the combination of those two references forms the natural business key of the row. In a vault design, this table would typically be represented as a link between the customer profile class hub and the currency hub, with its amount columns carried on the link or on a dependent satellite.

Key Information Stored

The table is documented in the 12.2.2 physical schema as having 71 columns, including the standard Oracle audit columns (LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN) and several groups of descriptive flexfield columns (ATTRIBUTE1–ATTRIBUTE15, GLOBAL_ATTRIBUTE_CATEGORY plus GLOBAL_ATTRIBUTE1–GLOBAL_ATTRIBUTE20, and the JGZZ attributes). The columns of greatest functional significance are:

• CUST_PROF_CLASS_AMOUNT_ID — the system-generated surrogate primary key. It is enforced by AR_CUST_PROF_CLASS_AMOUNTS_PK and is also the sole column of unique index AR_CUST_PROF_CLASS_AMOUNTS_U1.
• CUSTOMER_PROFILE_CLASS_ID — the identifier of the owning customer profile class; a required foreign key to AR_CUSTOMER_PROFILE_CLASSES.
• CURRENCY_CODE — the currency in which the amount limits are expressed; a required foreign key to FND_CURRENCIES.
• TRX_CREDIT_LIMIT and OVERALL_CREDIT_LIMIT — credit limits applied to individual transactions and to the customer's overall exposure within this currency.
• MIN_DUNNING_AMOUNT and MIN_DUNNING_INVOICE_AMOUNT — thresholds below which dunning correspondence is suppressed or a minimum invoice amount is required.
• MAX_INTEREST_CHARGE and INTEREST_RATE — the interest rate and the ceiling applied when calculating late charges on overdue items.
• MIN_STATEMENT_AMOUNT — the balance below which a customer statement is not generated.
• AUTO_REC_MIN_RECEIPT_AMOUNT — the minimum receipt amount eligible for automatic receipt application.
• MIN_FC_BALANCE_AMOUNT and MIN_FC_INVOICE_AMOUNT — minimum foreign-currency balance and invoice thresholds.

The business key established by unique index AR_CUST_PROF_CLASS_AMOUNTS_U2 is the pair CUSTOMER_PROFILE_CLASS_ID and CURRENCY_CODE. This guarantees that a profile class may define at most one set of amount limits per currency, and it is the natural key that application and integration logic should use rather than the surrogate identifier.

Common Use Cases and Queries

Typical reporting and integration scenarios resolve the amount limits for a profile class in a given currency, or enumerate every currency configured for a class. A representative query is:

SELECT cpc.name                 profile_class,
       pcam.currency_code,
       pcam.trx_credit_limit,
       pcam.overall_credit_limit,
       pcam.interest_rate,
       pcam.max_interest_charge
FROM   ar.ar_cust_prof_class_amounts pcam,
       ar.ar_customer_profile_classes cpc
WHERE  pcam.customer_profile_class_id = cpc.customer_profile_class_id
AND    pcam.currency_code = :currency_code
ORDER BY cpc.name;

Credit-review audits commonly compare the limits held here with exposure computed from open receivables, joining profile class to customer to transaction to detect accounts whose balance exceeds OVERALL_CREDIT_LIMIT. Similarly, dunning and collections analysts query MIN_DUNNING_AMOUNT and MIN_DUNNING_INVOICE_AMOUNT to explain why a customer was excluded from a dunning run. Data-conversion and setup-verification scripts use the unique key of profile class plus currency to upsert rows during migration, and validation queries use the same key to detect duplicates or missing currency coverage.

Because profile classes are consulted for every credit check, these columns are also useful as an input to security and SOX-style control reports that evidence who changed a credit limit, using the standard audit columns.

Related Objects

• AR_CUSTOMER_PROFILE_CLASSES — the parent setup table. Joined on CUSTOMER_PROFILE_CLASS_ID; supplies the profile class name and non-currency behavior flags.
• FND_CURRENCIES — the currency definition table. Joined on CURRENCY_CODE; supplies currency name and precision.
• Customers and customer sites reference a profile class, so customer-level limits are derived through CUSTOMER_PROFILE_CLASS_ID.
• The Credit Management and Collections workbench concurrent programs read these limits when evaluating credit exposure and generating dunning letters.
• Automatic Receipt Application and statement generation routines consume AUTO_REC_MIN_RECEIPT_AMOUNT and MIN_STATEMENT_AMOUNT.
• Interest and late-charge calculation logic reads INTEREST_RATE and MAX_INTEREST_CHARGE.