GL_BALANCES

1. Summary

GL_BALANCES contains actual, budget, and encumbrance balances for detail and summary accounts.

This table stores ledger currency, foreign currency, and statistical balances for each accounting period that has ever been opened.

ACTUAL_FLAG is either ‘A’, ‘B’, or ‘E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ‘E’, then ENCUMBRANCE_TYPE_ID is required.

2. Balances Calculations

GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns.

 The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.

An account’s year-to-date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR.

Example

decode(map.account_type,’Asset’,greatest(sum(period1.begin_balance_dr + period1.period_net_dr)-sum(period1.begin_balance_cr + period1.period_net_cr),0),’Liability’,(greatest(sum(period1.begin_balance_dr + period1.period_net_dr)-sum(period1.begin_balance_cr + period1.period_net_cr),0)),q'[Ownership/Stockholder’s Equity]’,(greatest(sum(period1.begin_balance_dr + period1.period_net_dr)-sum(period1.begin_balance_cr + period1.period_net_cr),0)),null) “Ledger Debit Amount”,

decode(map.account_type,’Asset’,greatest(sum(period1.begin_balance_cr + period1.period_net_cr)-sum(period1.begin_balance_dr + period1.period_net_dr),0),’Liability’,(greatest(sum(period1.begin_balance_cr + period1.period_net_cr)-sum(period1.begin_balance_dr + period1.period_net_dr),0)),q'[Ownership/Stockholder’s Equity]’,(greatest(sum(period1.begin_balance_cr + period1.period_net_cr)-sum(period1.begin_balance_dr + period1.period_net_dr),0)),null) “Ledger Credit Amount”,

3. Foreign Currency Transactions or Journals

Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ‘R’, to indicate that the row is a candidate for revaluation.

For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net (_BEQ) columns contain the converted ledger currency balance.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL.

Example

For US Company whose functional currency is USD, and if transaction (AP Invoice) is entered is in INR. Here entered Dr and Entered Cr are in INR and Accounted Dr and Cr is in USD.  (Context is for Liability account)

In GL Balances there will be two rows for this liability account, one for currency INR and other for currency USD.

A row with INR (Foreign Currency) will have TRANSLATED_FLAG set to ‘R’. For foreign currency (INR) rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net (_BEQ) columns contain the converted ledger currency balance (USD).

A row with USD (Functional Currency) will have TRANSLATED_FLAG set to NULL

4. Foreign Currency Translation

Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ‘Y’ or ‘N’. ‘N’ indicates that the translation is out of date (i.e., the account needs to be re-translated). ‘Y’ indicates that the translation is current.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL.

The columns that end in _ADB are not used. Also, the REVALUATION_STATUS column is not used.