Search Results jai_repoext_v

Overview

JAI_REPOEXT_V is an Oracle E-Business Suite view owned by the APPS schema and delivered as part of the JA (Asia/Pacific Localizations) product family. Its documented purpose is to retrieve all service tax transactions based on the transaction source, making it the reporting and extraction layer through which service tax repository data is presented for downstream inquiry, reporting, and statutory compliance processing. In EBS 12.1.1 and 12.2.2 it carries a VALID status and is classified as a VIEW, meaning it holds no persistent data of its own; it projects and aggregates rows sourced from the transaction repository extraction infrastructure.

The view is registered under product "JA - Asia/Pacific Localizations," the localization layer responsible for India service tax and related statutory features. Because it consolidates tax repository detail into a single, query-friendly structure, it is commonly used as a source for concurrent program extracts, Oracle Reports, BI Publisher templates, and custom PL/SQL logic that must reconcile tax amounts across document lines.

Underlying Base Objects

The view text selects exclusively from a single base object, JAI_TRX_REPO_EXTRACT_GT, exposed in the APPS schema as a SYNONYM. The "_GT" suffix identifies this as a global temporary table, indicating that JAI_REPOEXT_V operates over session-scoped staging or extraction data rather than permanent transactional tables. Consequently, the view returns rows only within a session in which the extract global temporary table has been populated, typically by a prior localization program.

All filtering and consolidation logic is expressed through aggregation. The view applies MIN() and MAX() to identifier and amount columns and uses DECODE and NVL to normalize values, then groups the result set by the descriptive dimensions of each transaction. This design collapses multiple repository tax rows belonging to the same document line into a single summarized record keyed by TRANSACTION_SOURCE and SOURCE_TRX_TYPE.

Key Columns

• TRANSACTION_SOURCE and SOURCE_TRX_TYPE — the primary classification columns. TRANSACTION_SOURCE identifies the origin of the transaction, and SOURCE_TRX_TYPE further qualifies it; together they drive the transaction-source-based retrieval described in the view's documentation and are the fields users most often search for.
• TRANSACTION_REPOSITORY_ID and TRANSACTION_REFERENCE_ID — surrogate identifiers obtained via MIN() aggregation, used to link back to repository detail.
• PARTY_NAME, PARTY_ID — the counterparty associated with the service tax transaction.
• DOCUMENT_NUMBER, DOCUMENT_DATE, DOCUMENT_ID, DOCUMENT_LINE_NUM, DOCUMENT_LINE_ITEM, DOCUMENT_LINE_ID — the document header and line identity, providing the audit trail down to line level.
• DOCUMENT_LINE_DESC, DOCUMENT_LINE_QTY, DOCUMENT_LINE_UOM, DOCUMENT_LINE_AMT — line description, quantity, unit of measure, and amount.
• DOCUMENT_CURRENCY_CODE — normalized with NVL to default to 'INR' when null, reflecting the India localization context.
• SERVICE_TYPE_CODE — the classification of the taxable service.
• ORGANIZATION_NAME, ORGANIZATION_ID, LOCATION_NAME, LOCATION_ID, INVENTORY_ITEM_ID — the organization, location, and item context.
• REPOSITORY_TAX_AMT — derived conditionally: summed when DOCUMENT_LINE_ID is null, otherwise taken as the maximum repository tax amount.
• UPDATABLE_FLAG, GL_DATE, ST_INV_NUMBER — update eligibility, accounting date, and service tax invoice number.

Common Use Cases and Queries

Typical scenarios include retrieving all service tax lines for a given source, reconciling repository tax amounts against document line amounts, and feeding external statutory reports. A representative query filtering on the searched column is:

SELECT TRANSACTION_SOURCE, SOURCE_TRX_TYPE, DOCUMENT_NUMBER, DOCUMENT_DATE, PARTY_NAME, SERVICE_TYPE_CODE, REPOSITORY_TAX_AMT, GL_DATE FROM JAI_REPOEXT_V WHERE SOURCE_TRX_TYPE = :p_source_trx_type AND GL_DATE BETWEEN :p_from AND :p_to;

A second pattern groups results by transaction source for summary reporting:

SELECT TRANSACTION_SOURCE, SOURCE_TRX_TYPE, COUNT(*) line_count, SUM(REPOSITORY_TAX_AMT) total_tax FROM JAI_REPOEXT_V GROUP BY TRANSACTION_SOURCE, SOURCE_TRX_TYPE;

Because the definition aggregates over the JAI_TRX_REPO_EXTRACT_GT global temporary table, callers must ensure the extraction has been run in the same session before querying the view; otherwise it returns no rows.