Query To Check GL Balances with JE Lines Balances (Compare period_balance with line_bal)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

SELECT
    code.concatenated_segments,
    bal_table.period_dr,
    bal_table.period_cr,
    bal_table.period_balances,
    bal_table.year_to_date_balance,
    SUM(nvl(line_table.accounted_dr,0)) line_dr,
    SUM(nvl(accounted_cr,0)) line_cr,
    SUM(nvl(line_table.accounted_dr,0)) - SUM(nvl(accounted_cr,0)) line_bal
FROM
    (
        SELECT
            bal.code_combination_id,
            bal.currency_code,
            bal.ledger_id,
            bal.period_name,
            SUM(begin_balance_dr) begin_dr,
            SUM(begin_balance_cr) begin_cr,
            SUM(period_net_dr) period_dr,
            SUM(period_net_cr) period_cr,
            SUM(period_net_dr) - SUM(period_net_cr) period_balances,
            SUM(begin_balance_dr) + SUM(period_net_dr) end_dr,
            SUM(begin_balance_cr) + SUM(period_net_cr) end_cr,
            ( SUM(begin_balance_dr) + SUM(period_net_dr) ) - ( SUM(begin_balance_cr) + SUM(period_net_cr) ) year_to_date_balance
        FROM
            gl_balances bal
        GROUP BY
            bal.code_combination_id,
            bal.currency_code,
            bal.ledger_id,
            bal.period_name
    ) bal_table,
    gl_je_lines line_table,
    gl_code_combinations_kfv code
WHERE      bal_table.code_combination_id = line_table.code_combination_id
    AND    bal_table.code_combination_id = code.code_combination_id
    AND    bal_table.period_name = line_table.period_name
    AND    bal_table.ledger_id = line_table.ledger_id
    AND    code.segment1 = '51'
    --AND bal_table.code_combination_id = 719953
    AND    bal_table.period_name = '12-2016'
    AND    bal_table.currency_code = 'USD'
    AND    bal_table.ledger_id = 2021
GROUP BY
    code.concatenated_segments,
    bal_table.period_dr,
    bal_table.period_cr,
    bal_table.period_balances,
    bal_table.year_to_date_balance