oracle-外币记账时总账余额表gl_balance变化（上）

就那GL_JE_LINES 上的一行贷方凭证来说，本位币是人民币，记账币种是USD,记账金额1美元,汇率 USD/CNY= 6
在EBS过账后，总账余额表针对一行凭证会生成两条记录，详细如下：

GL_JE_LINES 数据

ENTERED_CR 是原币币种（USD）-借方金额 字段值为1
ACCOUNTED_CR 是本位币币种（CNY）-贷方金额 字段值是 6

GL_BALANCES 数据

重点关注币种字段和当期相关字段

CURRENCY_CODE	TRANSLATED_FLAG	PERIOD_NET_DR	PERIOD_NET_CR	PERIOD_NET_DR_BEQ	PERIOD_NET_CR_BEQ
CNY		0	6	0	0
USD	R	0	1	0	6

可以看出EBS字段设计的精妙来，后面分析当发生重估和折算时，总账余额表相关字段是如何变化的。

参考资料

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.

这儿用中文简单解释一下 TRANSLATED_FLAG ，发生外币的时候，外币行上，在未进行重估(Translation)时，TRANSLATED_FLAG 的标识是R，如果需要重估则显示N，所以 当我们去余额时，如果以本币取余额的话，条件上要加上 TRANSLATED_FLAG is null ,这样避免取到外币行上。