Search Results jl_br_ar_tx_group_all_pk

Overview

The JL_BR_AR_TX_GROUP_ALL table is a legacy data object within the Oracle E-Business Suite, specifically part of the JL (Latin America Localizations) product module. Its primary purpose was to define and manage Brazilian tax group configurations, which are critical for calculating and applying region-specific tax rules on transactions. As explicitly noted in the official documentation, this table is "no longer used" in the context of releases 12.1.1 and 12.2.2, indicating its functionality has been superseded or deprecated. Its role was to serve as a junction table, linking tax groups (GROUP_TAX_ID) with specific tax categories (TAX_CATEGORY_ID) while also considering various Brazilian fiscal attributes to determine the correct tax treatment.

Key Information Stored

The table's composite primary key defines the unique combination of attributes required for a valid tax group rule. The key columns, and their likely purposes based on standard Brazilian localization logic, are:

• GROUP_TAX_ID: Foreign key to AR_VAT_TAX_ALL_B, identifying the master tax code or group.
• TAX_CATEGORY_ID: Foreign key to JL_BR_AR_TX_CATEG_ALL, identifying a specific tax category (e.g., ICMS, IPI, PIS, COFINS).
• END_DATE_ACTIVE: The date on which this specific group rule becomes inactive, supporting historical tracking.
• TRANSACTION_NATURE: A critical attribute defining the fiscal nature of the transaction (e.g., sale, transfer, return, sample), which directly influences tax applicability and rates.
• CONTRIBUTOR_TYPE: Indicates the tax contributor type (e.g., taxpayer, non-taxpayer, exempt) for the involved party.
• ESTABLISHMENT_TYPE: Defines the type of establishment (e.g., industrial, commercial, rural producer) involved in the transaction.

An additional notable column is TAX_CATEGORY_TO_REDUCE_ID, which forms a foreign key to the tax category table, likely used to define tax reduction or credit scenarios between different tax categories.

Common Use Cases and Queries

Given its deprecated status, direct operational use cases are minimal in current implementations. Historically, it was queried to validate or report on configured tax rules for Brazilian transactions. A typical analytical query might have joined related tables to understand the full tax setup. For audit or migration purposes, a sample query to retrieve all active rules for a specific transaction nature would be:

SELECT gt.group_tax_id,
       gt.tax_category_id,
       gt.transaction_nature,
       tc.name tax_category_name,
       v.name tax_name
FROM jl_br_ar_tx_group_all gt,
     jl_br_ar_tx_categ_all tc,
     ar_vat_tax_all_b v
WHERE gt.tax_category_id = tc.tax_category_id
  AND gt.group_tax_id = v.tax_id
  AND gt.transaction_nature = 'SALE' -- Example value
  AND (gt.end_date_active IS NULL OR gt.end_date_active > SYSDATE)
  AND gt.org_id = :p_org_id;

Related Objects

The table maintains defined foreign key relationships with other core localization tables, as documented in the ETRM metadata:

• JL_BR_AR_TX_CATEG_ALL (via TAX_CATEGORY_ID and ORG_ID): This relationship links the group rule to a specific Brazilian tax category, providing details like tax type and legal definitions.
• AR_VAT_TAX_ALL_B (via GROUP_TAX_ID): This links to the core EBS tax master table (AR_VAT_TAX_ALL_B), which defines the general tax code, rate, and recovery rules.
• JL_BR_AR_TX_CATEG_ALL (via TAX_CATEGORY_TO_REDUCE_ID and ORG_ID): This second relationship to the tax category table facilitates complex tax calculations, such as determining which tax category's amount can be reduced or credited against another.

The table's primary key constraint is JL_BR_AR_TX_GROUP_ALL_PK.