Search Results jai_cmn_tax_ctgs_all_pk




Overview
Explore in AI Chat

Overview

JAI_CMN_TAX_CTGS_ALL is a transactional and setup table owned by the JA schema within the Oracle E-Business Suite Asia/Pacific Localizations module (product code JA). It stores tax categories and maintains the link between those categories and Excise item classes. In EBS 12.1.1 and 12.2.2, this object functions as the central definition repository from which India localization (and broader Asia/Pacific) tax determination logic derives tax treatment for inventory, purchasing, order management, receivables, payables, and shipping transactions.

The table is multi-organization aware through its ORG_ID column, which carries a foreign key to HR_ALL_ORGANIZATION_UNITS. Every row is uniquely identified by the surrogate primary key TAX_CATEGORY_ID, generated under the constraint JAI_CMN_TAX_CTGS_ALL_PK. From a Data Vault modeling perspective, the heuristic classification for this object is hub-leaning: it carries the stable business key for a tax category and is referenced by a large set of surrounding tables as a foreign-key target. Analytical models that normalize the JAI tax domain could reasonably treat JAI_CMN_TAX_CTGS_ALL as a hub, with descriptive attributes such as names, flags, and effective rates distributed as satellites.

Key Information Stored

The table contains 16 documented columns. The most significant are:

  • TAX_CATEGORY_ID — surrogate primary key; the value propagated into all downstream transaction tables.
  • TAX_CATEGORY_NAME and TAX_CATEGORY_DESC — the user-facing identifier and description of the tax category.
  • ITEM_CLASS_CD — links the tax category to its associated Excise item class, the core India-localization concept this table was designed to capture.
  • TAX_CLASSIFICATION — classifies the tax treatment applied to the category.
  • INTER_STATE_FLAG — indicates whether the category applies to inter-state transactions, a key driver for CST vs. local tax determination.
  • EFFECTIVE_TDS_RATE — the withholding (TDS) rate effective for the category.
  • START_DATE and END_DATE — date-range effectiveness controlling when the category is active.
  • ORG_ID — operating unit / organization identifier, foreign key to HR_ALL_ORGANIZATION_UNITS.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/Forms-based maintenance UI.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

Only TAX_CATEGORY_ID is documented as a unique index (business-key candidate) via JAI_CMN_TAX_CTGS_ALL_PK; no separate natural-key unique constraint is documented, so the surrogate remains the definitive identifier.

Common Use Cases and Queries

Typical scenarios include validating tax-category setups before go-live, reconciling transaction-level tax values, and reporting excise/TDS exposure. A common pattern joins the category definition to a transaction table:

SELECT c.TAX_CATEGORY_ID, c.TAX_CATEGORY_NAME, c.TAX_CLASSIFICATION,
       c.INTER_STATE_FLAG, c.EFFECTIVE_TDS_RATE
FROM   JA.JAI_CMN_TAX_CTGS_ALL c
WHERE  c.ORG_ID = :p_org_id
AND    TRUNC(SYSDATE) BETWEEN c.START_DATE AND NVL(c.END_DATE, TRUNC(SYSDATE));

A second pattern resolves categories referenced on invoice lines:

SELECT l.INVOICE_LINE_ID, x.TAX_CATEGORY_NAME
FROM   JA.JAI_AP_INVOICE_LINES l,
       JA.JAI_CMN_TAX_CTGS_ALL  x
WHERE  l.TAX_CATEGORY_ID = x.TAX_CATEGORY_ID;

Reporting teams also use the table to audit inter-state flags against tax classification, and to detect overlapping effective-date ranges within the same organization.

Related Objects

The table is a heavily referenced parent, as the foreign-key relationships demonstrate:

Its own parent dependency is HR_ALL_ORGANIZATION_UNITS through ORG_ID. Any change to a row in this table therefore ripples across the AP, AR, PO, OM, INV, and shipping subledgers that consume the tax-category identifier.