Search Results ra_item_exception_rates_u1

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

Overview

The AR.RA_ITEM_EXCEPTION_RATES table is a Receivables (AR) transaction data object within the Oracle E-Business Suite 12.1.1 / 12.2.2 environment. It stores tax exception rates that Oracle Receivables applies when calculating tax for a specific item shipped to a specific location. In this context, an "exception" is a rate that overrides the ordinary location-based tax defined during tax setup, allowing organizations to impose item-and-destination-specific taxation where standard geography rules are insufficient. The table is owned by the AR schema, references the FND design data AR.RA_ITEM_EXCEPTION_RATES, and is physically resident in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Its documented structure comprises 48 columns, including 15 standard attribute flex columns and ten location-segment columns.

Under a Data Vault modeling heuristic derived from its foreign-key structure, the table leans toward a satellite classification. It records descriptive, time-bounded attributes—rates, reason codes, and effective dates—that qualify a business relationship between an item and a location context rather than serving as a transitive link between hubs in its own right. This classification is best treated as a modeling suggestion, not as an enforced data-warehouse design.

Key Information Stored

The surrogate primary key is ITEM_EXCEPTION_RATE_ID, a NUMBER(15) that uniquely identifies each exception rate row. Its uniqueness is enforced through the index RA_ITEM_EXCEPTION_RATES_U1, and while the metadata nominates this index as the only unique (business-key candidate) index, the documented column semantics make clear that the true business key is a composite of item and location context rather than the surrogate alone.

The most significant columns are:

• ITEM_ID — unique identifier of the item to which the exception rate applies.
• RATE_CONTEXT — VARCHAR2(30) location structure identifier; the internal ID applying to the tax rate descriptive flexfield.
• LOCATION_CONTEXT — the corresponding location context that qualifies the row.
• LOCATION1_RATE through LOCATION10_RATE — internal location IDs for each segment of the tax rate flexfield.
• LOCATION_ID_SEGMENT_1 through LOCATION_ID_SEGMENT_10 — the individual location segment identifiers used for tax determination and foreign-key resolution.
• REASON_CODE — code documenting the business justification for the exception.
• START_DATE and END_DATE — the effective date range during which the exception rate is valid.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — standard WHO audit columns tracking row provenance.
• ATTRIBUTE1 through ATTRIBUTE15 and ATTRIBUTE_CATEGORY — the descriptive flexfield extension columns.

Common Use Cases and Queries

The principal use case is auditing and reporting on item-specific tax overrides—particularly identifying which items carry an exception rate, for which location, and during which effective period. A representative query joining the exception to transaction lines is:

• SELECT l.trx_line_id, l.item_exception_rate_id, e.item_id, e.reason_code FROM ra_customer_trx_lines_all l, ra_item_exception_rates e WHERE l.item_exception_rate_id = e.item_exception_rate_id;

Analysts frequently need to resolve the location segments to their descriptions and validate effective dating:

• SELECT item_id, location_id_segment_1, start_date, end_date FROM ra_item_exception_rates WHERE item_id = :p_item_id AND TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date, TRUNC(SYSDATE));

Because the FK relationships point each LOCATION_ID_SEGMENT_n column to AR.AR_LOCATION_VALUES_OLD, inquiries commonly join on that table to translate segment IDs into human-readable location values. Reporting on inactive or expired exceptions, and reconciling interface staging rows in AR_TRX_LINES_GT, are additional recurring requirements.

Related Objects

Several objects reference or depend on this table:

• AR.RA_CUSTOMER_TRX_LINES_ALL — references ITEM_EXCEPTION_RATE_ID, storing it on each invoice line to record which exception rate determined the tax.
• AR.AR_TRX_LINES_GT — a global temporary staging table carrying ITEM_EXCEPTION_RATE_ID during transaction import.
• AR.AR_LOCATION_VALUES_OLD — the target of ten foreign keys, one per LOCATION_ID_SEGMENT_1 through LOCATION_ID_SEGMENT_10, resolving location values.
• The index RA_ITEM_EXCEPTION_RATES_N1 — a nonunique index on ITEM_ID and LOCATION_ID_SEGMENT_1, supporting item-and-location lookups.
• RA_ITEM_EXCEPTION_RATES_PK — the primary key constraint on ITEM_EXCEPTION_RATE_ID.