Search Results rate_group




Overview
Explore in AI Chat

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

Overview

XTR_TAX_BROKERAGE_RATES is a Treasury (XTR) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores rate groups and the associated rates applied to withholding tax and brokerage amounts on treasury deals. It functions as a configuration and calculation reference table: rather than defining a single flat rate per transaction type, it supports tiered, effective-dated, and amount-banded rate definitions through its MIN_AMT and MAX_AMT bounds combined with EFFECTIVE_FROM. This enables the Treasury engine to resolve the correct withholding or brokerage charge dynamically based on the transaction amount, the applicable reference type, and the relevant date.

The table is owned by the XTR schema and is documented as VALID. Its heuristic Data Vault classification, mined from its foreign key structure, is standalone—that is, it exhibits no outbound FK dependencies on other objects and may therefore be modeled as a standalone or reference-style structure rather than as a conventional hub, link, or satellite. This classification is a modeling suggestion only; the table is a native EBS configuration table, not a Data Vault artifact.

Key Information Stored

The physical schema, as documented in ETRM 12.2.2, comprises thirteen columns. The most significant are:

  • RATE_GROUP — the named grouping of rates; the principal business identifier used to cluster related rate definitions.
  • REF_TYPE — the reference type that qualifies the rate group, distinguishing categories such as withholding tax versus brokerage treatment.
  • EFFECTIVE_FROM — the date from which the rate definition becomes valid, enabling effective-dated rate versioning.
  • MIN_AMT / MAX_AMT — the lower and upper amount bounds defining the band to which the rate applies, supporting graduated or tiered rate schedules.
  • INTEREST_RATE — the percentage rate applied within the defined band, where the charge is expressed as a rate.
  • CMF_BROKERAGE_RATE — the brokerage rate applied for the corresponding transaction or market convention.
  • FLAT_AMOUNT — a fixed monetary amount charged where the fee is not rate-derived.
  • AUDIT_INDICATOR — a flag governing whether the rate or its application is tracked for audit purposes.
  • CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON — the standard WHO-column audit trail.

The primary key is a composite unique key, XTR_TAX_BROKERAGE_RATES_UK1, defined over RATE_GROUP, REF_TYPE, EFFECTIVE_FROM, MIN_AMT, and MAX_AMT. There is no separate single-column surrogate key; the business key itself serves as the unique identifier. This means a given rate group and reference type can hold multiple rows distinguished only by their effective date and amount band.

Common Use Cases and Queries

Typical usage centers on rate maintenance, rate resolution, and audit reporting. A rate-resolution query retrieves the applicable rate for a given group, reference type, amount, and date:

SELECT rate_group, ref_type, effective_from,
       min_amt, max_amt, interest_rate,
       cmf_brokerage_rate, flat_amount
  FROM   xtr.xtr_tax_brokerage_rates
 WHERE   rate_group    = :p_rate_group
   AND   ref_type      = :p_ref_type
   AND   effective_from <= :p_effective_date
   AND   :p_amount BETWEEN min_amt AND max_amt
 ORDER BY effective_from DESC;

Reporting scenarios include extracting all defined bands for a rate group to validate that no gaps or overlaps exist across MIN_AMT/MAX_AMT ranges, and producing an effective-dated history of rate changes for a given REF_TYPE. Auditors frequently query CREATED_BY, CREATED_ON, UPDATED_BY, and UPDATED_ON to trace who introduced or amended a rate and when. The AUDIT_INDICATOR column supports filtering to only those rate definitions subject to formal audit tracking. Because the table is standalone, most queries can be executed without joins to other treasury tables.

Related Objects

Given its standalone classification, XTR_TAX_BROKERAGE_RATES has no documented outbound foreign keys to other tables. It is instead referenced by—and consumed from—treasury processing objects that resolve rates during deal processing and cash management, including the Treasury transaction and settlement logic that reads RATE_GROUP and REF_TYPE to determine withholding and brokerage charges. Related objects of significance include the XTR Treasury deal and transaction tables that carry the RATE_GROUP and REF_TYPE values used as lookup keys, the XTR reference-type and rate-group setup components that define the valid values for REF_TYPE, and the standard FND audit and lookup infrastructure underlying the WHO columns. Because the metadata does not document explicit foreign key constraints, any join relationships should be confirmed against the deployed 12.1.1 / 12.2.2 instance before relying on them in custom SQL.