(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.