Results for “total_tax_amt”

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

Overview

JAI_RGM_REFS_ALL is a transaction repository table owned by the JA schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is delivered as part of the Asia/Pacific Localizations product family and supports the India Tax Collected at Source (TCS) and related withholding/tax regime functionality. As documented in the ETRM metadata, the table "stores transactions with TCS type of Tax" and functions as a central TCS repository, holding a consolidated record of taxable transactions that have been processed through the JA regime and tax engine.

The table has 36 columns and is created across both 12.1.1 and 12.2.2 environments with a status of VALID. Its primary key, JAI_RGM_REFS_PK, is defined on TRX_REF_ID, and a unique index JAI_RGM_REFS_U1 also exists on TRX_REF_ID. Based on the configuration of foreign keys and its role in the JA tax settlement and certificate processes, a heuristic modeling exercise (as seen in ETRM metadata) suggests classifying this entity as satellite-leaning in Data Vault terms — it carries descriptive and transactional attributes tied to a central transaction reference, though its numerous foreign keys to parties, documents, and regimes also lend it a link-like character. This classification should be treated as a modeling suggestion rather than an official Oracle designation.

Key Information Stored

The table's 36 columns capture tax-relevant transaction details. The most significant columns include:

• TRX_REF_ID — The surrogate primary key and unique business key of the repository, uniquely identifying each TCS transaction reference.
• TRANSACTION_ID — Foreign key to JAI_RGM_ITEM_GEN_DOCS, linking the record to the underlying generated document or transaction.
• REGIME_ID — Foreign key to JAI_RGM_DEFINITIONS, identifying the tax regime applicable to the transaction.
• PARTY_ID / PARTY_SITE_ID / PARTY_TYPE — Parties, sites, and party types involved, with PARTY_ID referencing HZ_PARTIES and PARTY_SITE_ID referencing HZ_PARTY_SITES.
• ORG_ID / ORGANIZATION_ID / LOCATION_ID — Organization, operating unit, and location identifiers, referencing HR_ALL_ORGANIZATION_UNITS and HZ_LOCATIONS.
• SOURCE_DOCUMENT_ID / SOURCE_DOCUMENT_LINE_ID / SOURCE_REF_DOCUMENT_ID — Pointers to the originating customer transaction and line (RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL).
• SOURCE_DOCUMENT_AMT / LINE_AMT / TOTAL_TAX_AMT — Monetary amounts for the source document, line, and total tax captured.
• FIN_YEAR — Financial year of the transaction, relevant for TCS reporting and thresholds.
• THRESHOLD_SLAB_ID — Foreign key to JAI_AP_TDS_THHOLD_SLABS, tying the transaction to a threshold slab for TCS computation.
• SETTLEMENT_ID — Foreign key to JAI_RGM_SETTLEMENTS, linking to settlement records.
• CERTIFICATE_ID — Foreign key to JAI_RGM_CERTIFICATES, linking tax certificates issued.
• OBJECT_VERSION_NUMBER — Standard EBS optimistic locking column, alongside CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE audit columns.

The surrogate key TRX_REF_ID is distinct from other document identifiers and enforces uniqueness via JAI_RGM_REFS_U1.

Common Use Cases and Queries

Because JAI_RGM_REFS_ALL is the TCS repository, it is commonly queried for tax reconciliation, reporting, and audit. Typical scenarios include retrieving all TCS transactions for a party or financial year, identifying transactions by source document, and reconciling settlement and certificate activity.

For example, a query to list TCS references for a given party and financial year:

SELECT r.TRX_REF_ID, r.SOURCE_DOCUMENT_ID, r.TOTAL_TAX_AMT, r.FIN_YEAR
FROM   JA.JAI_RGM_REFS_ALL r
WHERE  r.PARTY_ID = :party_id
AND    r.FIN_YEAR = :fin_year;

A join to the tax detail table JAI_RGM_TAXES on TRX_REF_ID provides the individual tax lines associated with each reference. Similarly, joining to RA_CUSTOMER_TRX_ALL via SOURCE_DOCUMENT_ID allows reporting of the original invoice or document details alongside TCS amounts. Threshold monitoring and certificate reporting queries typically join THRESHOLD_SLAB_ID and CERTIFICATE_ID to their respective master tables.

Related Objects

The most significant related objects and their join columns are:

• JAI_RGM_TAXES — References JAI_RGM_REFS_ALL.TRX_REF_ID via JAI_RGM_TAXES.TRX_REF_ID (child of this table).
• JAI_RGM_DEFINITIONS — Joined via REGIME_ID, providing regime configuration.
• JAI_RGM_ITEM_GEN_DOCS — Joined via TRANSACTION_ID and PARENT_TRANSACTION_ID.
• JAI_RGM_SETTLEMENTS — Joined via SETTLEMENT_ID.
• JAI_RGM_CERTIFICATES — Joined via CERTIFICATE_ID.
• JAI_AP_TDS_THHOLD_SLABS — Joined via THRESHOLD_SLAB_ID.
• HZ_PARTIES / HZ_PARTY_SITES / HZ_LOCATIONS — Joined via PARTY_ID, PARTY_SITE_ID, and LOCATION_ID.
• RA_CUSTOMER_TRX_ALL / RA_CUSTOMER_TRX_LINES_ALL — Joined via SOURCE_DOCUMENT_ID, SOURCE_REF_DOCUMENT_ID, and SOURCE_DOCUMENT_LINE_ID.
• AR_CASH_RECEIPTS_ALL — Joined via APP_FROM_DOCUMENT_ID and APP_TO_DOCUMENT_ID.
• HR_ALL_ORGANIZATION_UNITS — Joined via ORG_ID and ORGANIZATION_ID.

These relationships confirm the table's central position in the JA TCS data model, serving both as a repository of transaction references and as a parent for tax detail records.