Search Results basic_ed

Overview

The view JAI_CMN_RG_23_II_V is a reporting object owned by the APPS schema within the JA – Asia/Pacific Localizations product family. It exposes excise register data for the RG-23 register (Part II, Inputs), a statutory register format used in Indian excise reporting within Oracle E-Business Suite. The view consolidates transaction-level excise duty amounts, register balances, organization and location context, and receipt references into a single denormalized structure suitable for reporting, reconciliation, and downstream integration. In Oracle EBS 12.1.1 and 12.2.2, the object is registered as a VALID view and is intended primarily for read-only consumption by localization reports, reconciliation programs, and customer-defined extracts.

The view does not itself store data; it derives its result set entirely from an underlying transaction table joined to descriptive and reference lookups. Because it is a view rather than a table, it carries no data integrity constraints of its own and inherits the integrity of its base objects. Its naming convention (JAI_CMN_RG_23_II_V) indicates a common (CMN) localization object tied to the RG-23 register.

Underlying Base Objects

Per the documented ETRM metadata, JAI_CMN_RG_23_II_V is defined over the following referenced base objects, all accessed through synonyms in the APPS schema:

• JAI_CMN_RG_23AC_II_TRXS — the primary transaction table supplying register line data, aliased as A. This is the driving table of the view.
• MTL_PARAMETERS — supplies the inventory organization code, joined on ORGANIZATION_ID.
• HR_LOCATIONS_ALL — supplies location code and description, joined on LOCATION_ID and inventory organization.
• HR_ALL_ORGANIZATION_UNITS — supplies the organization name, joined via MTL_PARAMETERS on ORGANIZATION_ID.
• RCV_SHIPMENT_HEADERS — supplies the receipt number, joined through RCV_TRANSACTIONS.
• RCV_TRANSACTIONS — links the register line's RECEIPT_REF to the shipment header via TRANSACTION_ID.

The joins to RCV_TRANSACTIONS and RCV_SHIPMENT_HEADERS are outer joins (denoted by the (+) operator in the view text), ensuring that register entries without a corresponding receiving transaction are still returned. The joins to MTL_PARAMETERS, HR_LOCATIONS_ALL, and HR_ALL_ORGANIZATION_UNITS are inner joins, so a valid organization and location are required for a row to appear.

Key Columns

The view exposes a ROW_ID (derived from the base table ROWID) in addition to the following functional columns. Several monetary columns use a DECODE/NVL construct that nets debit and credit amounts, returning a signed excise value:

• ROW_ID — the base table ROWID, useful for drill-back and row identification.
• NAME / ORGANIZATION_CODE / LOCATION_CODE / DESCRIPTION — descriptive context for the organization and location.
• SLNO, TRANSACTION_DATE, REGISTER_TYPE, REGISTER_ID, REGISTER_ID_PART_I, FIN_YEAR — register line identity and period attributes.
• BASIC_ED — the net basic excise duty, computed as DECODE(NVL(-A.DR_BASIC_ED,0),0,A.CR_BASIC_ED,-A.DR_BASIC_ED).
• ADDITIONAL_ED, OTHER_ED, ADDITIONAL_CVD — additional excise duty, other excise duty, and additional countervailing duty, each netted in the same manner.
• OPENING_BALANCE / CLOSING_BALANCE — register balances for the period.
• EXCISE_INVOICE_NO, RECEIPT_REF, RECEIPT_NUM, REFERENCE_NUM — statutory and receipt references.
• ORGANIZATION_ID, LOCATION_ID, VENDOR_ID, VENDOR_SITE_ID, CUSTOMER_ID, CUSTOMER_SITE_ID, CHARGE_ACCOUNT_ID — key identifiers for drill-down to vendors, customers, and accounting.

Common Use Cases and Queries

Typical uses include RG-23 Part II input register reporting, excise duty reconciliation against receiving transactions, and period-end extracts for statutory filing. A representative query filtering to a financial year and organization follows:

• SELECT organization_code, location_code, transaction_date, basic_ed, additional_ed, additional_cvd, closing_balance FROM jai_cmn_rg_23_ii_v WHERE fin_year = :fin_year AND organization_id = :org_id ORDER BY transaction_date, slno;
• SELECT register_type, SUM(basic_ed) total_basic_ed, SUM(additional_ed) total_additional_ed FROM jai_cmn_rg_23_ii_v WHERE fin_year = :fin_year GROUP BY register_type;
• SELECT excise_invoice_no, receipt_num, basic_ed FROM jai_cmn_rg_23_ii_v WHERE receipt_ref IS NOT NULL AND fin_year = :fin_year;

Because the view performs outer joins to receiving tables, reports using receipt columns should account for NULL RECEIPT_NUM values. For performance, filter on FIN_YEAR, ORGANIZATION_ID, and TRANSACTION_DATE to limit the scanned base transaction set.