Results for “consolidation_date”
27 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
JAI_CMN_RG_CONSLDATIONS is a transactional table owned by the JA schema within the Oracle E-Business Suite Asia/Pacific Localizations module. Its documented purpose is to store transaction information generated during RG (Regulatory/Reporting Group) Consolidation processing. This consolidation functionality supports statutory reporting requirements specific to Asia/Pacific country localizations, where invoice, payment, and tax-related balances must be periodically aggregated and reconciled against prior period results.
From a data modeling perspective, the mined metadata suggests a Data Vault classification of link. The table carries a single-column surrogate primary key and multiple foreign key references to core Human Resources master data, which is characteristic of a link entity connecting business transactions to organizational and location context. It should be viewed as an integration or association point rather than a pure descriptive satellite.
Key Information Stored
The table contains 66 documented columns. The most significant include:
- CONSOLIDATION_ID — the surrogate primary key defined by the unique index JAI_CMN_RG_CONSLDATIONS_PK. This is the only documented business-key candidate; no separate alternate unique index is listed.
- CONSOLIDATION_DATE and PREV_CONSOLIDATION_DATE — the current and prior consolidation run dates, enabling period-over-period comparison.
- ORGANIZATION_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, identifying the operating unit or organization being consolidated.
- LOCATION_ID — foreign key to HR_LOCATIONS_ALL, identifying the physical or reporting location.
- CUR_/CON_/TOD_/PREV_ prefixed amount columns — organized in family groups covering RG23AP2, RG23CP2, PLA, and NET_PAYABLE. The prefixes denote Current, Consolidated, Today (transactional delta), and Previous values. Each family contains BED (Basic Excise Duty), SED (Special Excise Duty), OED (Other Excise Duty), CESS, and RID (Reference ID) variants.
- PAYMENT_ID_REF — links consolidation records to payment transactions.
- REMARKS — free-form annotation for the consolidation run.
- CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER — standard audit and optimistic locking columns.
Common Use Cases and Queries
Typical scenarios include statutory excise reconciliation reporting, verification of consolidation balances against sub-ledger transactions, and period-over-period variance analysis.
- Retrieving the latest consolidation for an organization and location:
SELECT CONSOLIDATION_ID, CONSOLIDATION_DATE,
CUR_NET_PAYABLE, CON_NET_PAYABLE
FROM JA.JAI_CMN_RG_CONSLDATIONS
WHERE ORGANIZATION_ID = :p_org_id
AND LOCATION_ID = :p_loc_id
ORDER BY CONSOLIDATION_DATE DESC;
- Comparing current versus previous RG23AP2 duties:
SELECT CONSOLIDATION_DATE,
CUR_RG23AP2_BED, PREV_RG23AP2_RID,
CUR_RG23AP2_CESS, CON_RG23AP2_CESS
FROM JA.JAI_CMN_RG_CONSLDATIONS
WHERE CONSOLIDATION_DATE BETWEEN :p_from AND :p_to;
- Joining to organization and location for reporting:
SELECT h.name org_name, l.location_code,
r.CONSOLIDATION_DATE, r.TOD_PLA_SED
FROM JA.JAI_CMN_RG_CONSLDATIONS r,
HR_ALL_ORGANIZATION_UNITS h,
HR_LOCATIONS_ALL l
WHERE r.ORGANIZATION_ID = h.organization_id
AND r.LOCATION_ID = l.location_id;
Related Objects
- HR_ALL_ORGANIZATION_UNITS — joined via JAI_CMN_RG_CONSLDATIONS.ORGANIZATION_ID.
- HR_LOCATIONS_ALL — joined via JAI_CMN_RG_CONSLDATIONS.LOCATION_ID.
- JAI_CMN_RG_CONSLDATIONS_PK — the primary key constraint enforcing CONSOLIDATION_ID uniqueness.
- PAYMENT_ID_REF linkage — associates consolidation rows with payment records in the JA payments sub-ledger.
- RG23AP2 / RG23CP2 / PLA transaction tables in the JA module, which supply the underlying BED, SED, OED, and CESS values summarized here.
- Standard EBS audit columns reference FND_USER via CREATED_BY and LAST_UPDATED_BY.
-
Stores Transaction information of RG Consolidation.
-
Back up table for Inventory onhand
-
Back up table for Inventory onhand
-
View: JAI_INV_RGCO_AEL_V 12.2.2
Base view of RG consolidation
APPS.JAI_INV_RGCO_AEL_V·↳ AP_SUPPLIER_SITES_ALL·↳ GL_DAILY_CONVERSION_TYPES·↳ GL_IMPORT_REFERENCES·Explore JA module →
-
Stores Transaction information of RG Consolidation.
-
VIEW: INV.MTL_MOQD_BACKUP# 12.2.2
-
VIEW: INV.MTL_MOQD_BACKUP# 12.2.2
-
TABLE: INV.MTL_MOQD_BACKUP 12.2.2
-
TABLE: INV.MTL_MOQD_BACKUP 12.1.1
-
View: JAI_INV_RGCO_AEL_SL_V 12.2.2
APPS.JAI_INV_RGCO_AEL_SL_V·↳ HR_GENERAL·↳ HR_SECURITY·↳ JAI_INV_RGCO_AEL_V·Explore JA module →
-
View: JAI_INV_RGCO_AEL_GL_V 12.2.2
APPS.JAI_INV_RGCO_AEL_GL_V·↳ HR_GENERAL·↳ HR_SECURITY·↳ JAI_INV_RGCO_AEL_V·Explore JA module →
-
eTRM - JA Tables and Views 12.2.2
The 'JA_CN_VOUCHER_NUMBER' table stores the voucher number of GL journal lines.
-
eTRM - JA Tables and Views 12.1.1
The 'JA_CN_SYSTEM_PARAMETERS_ALL' table stores information defined by 'System Options' form, 'Electronic Accounting Book Export' program will get most data from data in the table. A legal entity can only have one record in the table.
-
eTRM - INV Tables and Views 12.1.1
-
eTRM - INV Tables and Views 12.2.2