Search Results trx_ref_id

Overview

The JAI_RGM_REFS_ALL table is a core transactional repository within the Oracle E-Business Suite Asia/Pacific Localizations module, specifically designed for Tax Collected at Source (TCS) compliance. In Oracle EBS 12.1.1 and 12.2.2, TCS is a statutory requirement in jurisdictions like India, where a seller (collector) must deduct a percentage of tax from certain payments made to a supplier. This table serves as the central ledger, storing detailed records of all business transactions that are subject to TCS regimes. Its primary role is to maintain the integrity and audit trail of TCS applicability, calculations, and settlements by linking transactional data from source documents like invoices and receipts to the corresponding tax regime definitions, party details, and generated TCS certificates.

Key Information Stored

The table's structure is defined by its extensive foreign key relationships, which indicate the critical data points it stores. The primary key is the TRX_REF_ID, a unique system-generated identifier for each TCS-relevant transaction record. Other essential columns include REGIME_ID, linking to the specific TCS rule (JAI_RGM_DEFINITIONS), and PARTY_ID, identifying the supplier or party from whom tax is collected. Crucially, it stores references to the source transaction documents via SOURCE_DOCUMENT_ID (tying to RA_CUSTOMER_TRX_ALL for invoices) and APP_TO_DOCUMENT_ID or APP_FROM_DOCUMENT_ID (tying to AR_CASH_RECEIPTS_ALL for receipts). The table also holds keys for associated settlement records (SETTLEMENT_ID), generated TCS certificates (CERTIFICATE_ID), and applicable organizational context (ORG_ID).

Common Use Cases and Queries

This table is central to TCS reporting, reconciliation, and audit processes. A common use case is tracing the TCS lifecycle for a specific invoice. For example, to find all TCS records for a given transaction, one might query using the source document identifier. The user's search for "trx_ref_id" suggests a need to locate or join data using this primary key, often for detailed reporting or troubleshooting.

• Identifying TCS applications for a customer transaction: SELECT * FROM jai_rgm_refs_all WHERE source_document_id = <customer_trx_id>;
• Linking TCS records to party and regime details for a report: SELECT ref.trx_ref_id, def.regime_code, party.party_name FROM jai_rgm_refs_all ref, jai_rgm_definitions def, hz_parties party WHERE ref.regime_id = def.regime_id AND ref.party_id = party.party_id AND ref.org_id = <org_id>;
• Finding unreconciled TCS transactions: SELECT * FROM jai_rgm_refs_all WHERE settlement_id IS NULL;

Related Objects

JAI_RGM_REFS_ALL is a hub table with dependencies to numerous key EBS objects. Its primary foreign keys define its core relationships: JAI_RGM_DEFINITIONS for the tax regime, HZ_PARTIES and HZ_PARTY_SITES for supplier details, and RA_CUSTOMER_TRX_ALL and AR_CASH_RECEIPTS_ALL for source transactional data. It is a parent table to JAI_RGM_TAXES, which holds the detailed tax lines for each TRX_REF_ID. Other significant related tables include JAI_RGM_SETTLEMENTS for payment reconciliations, JAI_RGM_CERTIFICATES for TCS certificate generation, and JAI_RGM_ITEM_GEN_DOCS for document tracking. The relationships to HR_ALL_ORGANIZATION_UNITS and HZ_LOCATIONS provide the necessary operating unit and geographical context for the transactions.