Search Results xtr_deduction_calcs




Overview
Explore in AI Chat

Overview

XTR_DEDUCTION_CALCS is a Treasury (XTR) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the pre-defined deduction calculation methods used when setting up tax and brokerage schedules. In the Oracle ETRM (Enterprise Treasury and Risk Management) data model, this table functions as a reference or setup object that drives how deductions — such as taxes and brokerage fees — are computed against Treasury deals. Each row defines a valid combination of deal type, amount basis, calculation basis, and calculation type, which other Treasury setup and transaction tables consult when applying deductions.

The table is owned by the XTR schema and carries a status of VALID. From a Data Vault modeling perspective, the heuristic classification is hub-leaning. This suggests that XTR_DEDUCTION_CALCS behaves primarily as a reference hub, holding the unique business keys that identify a deduction calculation method, rather than acting as a transactional link or a descriptive satellite. In practice, its role is closest to a setup/lookup table whose keys are referenced by downstream configuration tables.

Key Information Stored

The documented physical schema comprises six columns. The most important are the business-key columns that together identify a deduction calculation method:

  • DEAL_TYPE — Identifies the category of Treasury deal to which the deduction calculation applies.
  • AMOUNT_TYPE — Specifies the amount basis used for the deduction (for example, principal or interest).
  • CALC_BASIS — Defines the basis on which the calculation is performed.
  • CALC_TYPE — Identifies the calculation method applied.
  • DESCRIPTION — A descriptive label for the deduction calculation method.
  • ZD_EDITION_NAME — The edition name column characteristic of Oracle's edition-based redefinition (EBR) architecture, supporting online patching in 12.2.x.

The surrogate primary key, XTR_DEDUCTION_CALCS_PK, is defined over DEAL_TYPE, AMOUNT_TYPE, CALC_BASIS, and CALC_TYPE. A separate unique index, XTR_DEDUCTION_CALCS_U1, covers the same four columns plus ZD_EDITION_NAME, representing the business-key candidate under EBR. This distinction matters: the primary key enforces uniqueness of the logical combination, while the unique index extends uniqueness across the edition dimension.

Common Use Cases and Queries

Typical scenarios involve retrieving valid deduction calculation methods for a given deal type, or validating that a tax/brokerage setup references a defined method. A simple lookup query follows:

  • SELECT deal_type, amount_type, calc_basis, calc_type, description FROM xtr.xtr_deduction_calcs WHERE deal_type = :deal_type;
  • SELECT a.deal_type, a.calc_type, b.description FROM xtr.xtr_tax_brokerage_setup a, xtr.xtr_deduction_calcs b WHERE a.deal_type = b.deal_type AND a.calc_type = b.calc_type;

Reporting use cases include generating a matrix of available deduction methods by deal type and validating configuration completeness before deal processing. Because the table is a low-volume setup object, queries are frequently joined to tax and brokerage configuration data rather than scanned directly.

Related Objects

The most significant dependent object is XTR_TAX_BROKERAGE_SETUP, which holds a foreign key referencing XTR_DEDUCTION_CALCS on the composite columns DEAL_TYPE and CALC_TYPE. This relationship establishes XTR_DEDUCTION_CALCS as the referenced (parent) table for tax and brokerage schedule configuration.

  • XTR_TAX_BROKERAGE_SETUP — References DEAL_TYPE and CALC_TYPE; the primary consumer of deduction calculation definitions.
  • XTR_DEDUCTION_CALCS_PK — Primary key constraint on DEAL_TYPE, AMOUNT_TYPE, CALC_BASIS, CALC_TYPE.
  • XTR_DEDUCTION_CALCS_U1 — Unique index including ZD_EDITION_NAME.

These objects collectively govern how Treasury deals capture associated taxes and brokerage costs, and any modification to the deduction definitions should be assessed against the referencing setup table to preserve referential integrity.