A New Method for Calculating trial balance)

(Prerequisites: In the GL module, each journal_line in the gl_je_lines table has two columns: accounted_dr and accounted_cr, indicating the debit amount and credit amount of the line; each record in the gl_balances table stores the total amount of debit history generated by an account at the beginning of a historical accounting period begin_balance_dr, and the total amount of credit history at the beginning of the period begin_balance_cr .)

I recently submitted a trial balance by date report in tuning. The requirement is as follows:

The user enters two dates: start_date and end_date. The report finds the starting balance of all accounts in a sob for this period.Beginning_balance, Debit amountDr_amount, Credit amountCr_amount, Total amount (borrow-loan)Net_change,Final BalanceEnding_balance.

The original report is calculated in this way (Red is the calculation result column ).

1)First, call a function for each account to calculate the beginning_balance of start_date.

Calculation method:

Find the period_name of start_date, and findBegin_balance_dr, Begin_balance_cr.The two accounts are subtracted to obtain the opening balance of the account in the period. period_beginning_balance

Go to the gl_je_lines table,Find thisAccountFrom the first day of the periodStart_date-1All loans incurred. Sum (accounted_dr) => period_bydate_dr,

Sum (accounted_cr) => period_bydate_cr.The actual amount of the account during the period is obtained: period_net_change.

Finally, period_beginning_balance + period_net_change to obtainBeginning_balance.

2)From the gl_je_lines table, sum refers to the amount of all loans incurred by this account from start_date to end_date.

Sum (accounted_dr) =>Dr_amount, Sum (accounted_cr) =>Cr_amount

Dr_mount-cr_mount =>Net_change.

3)Beginning_balance + net_change =Ending_balance

Because the gl_je_lines table needs to be filled with the sum action, the terrorism coefficient index of this report increases as the time period between start_date and end_date is increased.

After several days of SQL statement execution on the report, I suddenly thought of a problem, that is, the amount of loans that occurred during the historical accounting period in gl_je_lines In the GL module, and the total loan amount in history will be sum to the gl_balances table ., that is to say, the sum action we have done above, in fact, a lot of GL has already done for us.

For example, we require beginning_balance, dr_amount, cr_amount, net_change, ending_balance for a sob between-4-5 and-8-5.

According to the above statement, if the first day of the accounting period of 2006-4-5 is-, the first day of the accounting period of-8-5 is-8-1.

Our begin_balance is still obtained according to the first method.

Dr_amountWe can use beginning_dr of 2006-8-6 minus beginning_dr of 2006-4-5,

Cr_amountIt can also be obtained in the same way.

The first step in method 1 has actually been obtained from the two beginning_dr, but it is not used:

Begin_balance_dr+ Period_bydate_dr = beginning_dr

Begin_balance_cr+ Period_bydate_cr = beginning_cr

That is to say, we use the first step of method 1 to obtain the begin_dr and begin_cr of start_date and end_date respectively.

We can use these four values to spell all the column values we need.

Calculate the two values of end_date as ending_dr, ending_cr.

Beginning_balance= Beginning_dr-beginning_cr

Dr_amount= Ending_dr-beginning_dr

Cr_amount= Ending_cr-beginning_cr

Net_change= Dr_amnount-cr_amount

Ending_balance= Ending_dr-ending_cr

According to this method, the knowledge of sum is 2006-4-1 to 2006-4-5, and the journal between 2006-8-1 to 2006-8-5, instead of sum all.