Results for “settlement_date”

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

Overview

JAI_RGM_STL_BALANCES is a transactional table owned by the JA schema within the Asia/Pacific Localizations product family of Oracle E-Business Suite. It stores settlement details organized by settlement of regime for each accounting period. In the context of Oracle EBS 12.1.1 and 12.2.2, this table supports the Regime Settlement functionality used predominantly by Asian localizations for tax, service, and regulatory settlement processing, where balances accrued against parties and locations must be periodically settled and reconciled.

Each row represents a settlement balance record tied to a specific settlement, party, party type, location, tax type, and service type. The table functions as the balance-carrying detail behind the settlement header, capturing debit and credit positions as well as credit utilized amounts per period.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure suggests this object behaves as a link table, since it primarily connects business entities (settlements, organizations, locations) rather than storing immutable descriptive attributes of a single hub. This classification should be treated as a modeling suggestion rather than a definitive architectural designation.

Key Information Stored

The table contains 17 documented columns. The most operationally significant are:

• STL_BALANCE_ID — Surrogate primary key defined by constraint JAI_RGM_STL_BALANCES_PK; uniquely identifies each balance row.
• SETTLEMENT_ID — Foreign key to JAI_RGM_SETTLEMENTS; identifies the parent settlement. Combined with other business attributes, it participates in the unique index JAI_RGM_STL_BALANCES_UK1.
• SETTLEMENT_DATE — Business date of the settlement to which the balance applies.
• PARTY_ID — Foreign key to HR_ALL_ORGANIZATION_UNITS; the organization or party against which the balance is held.
• PARTY_TYPE — Classifies the party for settlement purposes (for example, supplier, customer, or internal entity).
• LOCATION_ID — Foreign key to HR_LOCATIONS_ALL; the location associated with the settlement balance.
• TAX_TYPE — Identifies the tax regime or tax category being settled.
• SERVICE_TYPE_CODE — Distinguishes the service or regime classification driving the balance.
• DEBIT_BALANCE — Debit-side monetary balance for the period.
• CREDIT_BALANCE — Credit-side monetary balance for the period.
• CREDIT_UTILIZED — Portion of credit that has been applied or consumed in settlement.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Applications framework (OAF) to detect concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS audit columns tracking who created and last modified the row.

Common Use Cases and Queries

Typical usage includes settlement reconciliation, period-end balance reporting, and audit of credit utilization by party and location. A common query retrieves balances for a given settlement:

• Join JAI_RGM_STL_BALANCES to JAI_RGM_SETTLEMENTS on SETTLEMENT_ID to obtain settlement context.
• Aggregate DEBIT_BALANCE, CREDIT_BALANCE, and CREDIT_UTILIZED by PARTY_ID, TAX_TYPE, or SERVICE_TYPE_CODE for period reporting.
• Filter by SETTLEMENT_DATE range to isolate balances for a closed or open accounting period.

A representative pattern:

SELECT b.party_id, b.tax_type, SUM(b.debit_balance), SUM(b.credit_balance), SUM(b.credit_utilized) FROM jai_rgm_stl_balances b, jai_rgm_settlements s WHERE b.settlement_id = s.settlement_id AND b.settlement_date BETWEEN :from_date AND :to_date GROUP BY b.party_id, b.tax_type;

Because TAX_TYPE and SERVICE_TYPE_CODE segment the balances, these columns are frequently used in regulatory reporting extracts and in reconciliation between the localization ledger and settlement subledger.

Related Objects

• JAI_RGM_SETTLEMENTS — Parent entity; joined via SETTLEMENT_ID.
• HR_ALL_ORGANIZATION_UNITS — Organization/party master; joined via PARTY_ID.
• HR_LOCATIONS_ALL — Location master; joined via LOCATION_ID.
• JAI_RGM_STL_BALANCES_PK — Primary key constraint on STL_BALANCE_ID.
• JAI_RGM_STL_BALANCES_UK1 — Unique index on SETTLEMENT_ID, SETTLEMENT_DATE, PARTY_ID, PARTY_TYPE, LOCATION_ID, and TAX_TYPE, enforcing business-key uniqueness.

Because the JA localizations settlement process references organization and location masters, any purge, archive, or reconciliation procedure involving JAI_RGM_SETTLEMENTS must account for dependent rows in this table to preserve referential integrity.