GL Trial Balance Report with 12 Month Trends

Post date: Aug 23, 2013 5:23:1 PM

I was recently asked to generate a Trial Balance extract from eBS with a 12 month trend. The following code was the end result of this extract. One key thing to note in this query is that the user can shoose if they want to include accounts which have a zero ending balance for the period in which the report is being run for.

SELECT

sob.name AS "Ledger"

, gb.currency_code AS "Currency"

, gb.period_name AS "Period"

, gcc.segment2 AS "Cost Center"

, gcc.segment3 AS "Account"

, (SELECT ffvtl.description

FROM apps.fnd_flex_values ffv

INNER JOIN apps.fnd_flex_values_tl ffvtl

ON ffv.flex_value_id = ffvtl.flex_value_id

INNER JOIN apps.fnd_flex_value_sets ffvs

ON ffv.flex_value_set_id = ffvs.flex_value_set_id

WHERE ffvs.flex_value_set_name = 'XXVAI_ACCOUNT'

AND ffv.flex_value = gcc.segment3) AS "Account Desc."

, gcc.segment1||'-'||

gcc.segment2||'-'||

gcc.segment3||'-'||

gcc.segment4||'-'||

gcc.segment5||'-'||

gcc.segment6||'-'||

gcc.segment7 AS "Account Distribution"

, ROUND(SUM(NVL(gb.begin_balance_dr,0) - NVL(gb.begin_balance_cr,0)),2) AS "Open Balance"

, ROUND(NVL(gb.period_net_dr,0),2) AS "Debit"

, ROUND(NVL(gb.period_net_cr,0),2) AS "Credit"

, ROUND(SUM(NVL(gb.period_net_dr,0) - NVL(gb.period_net_cr,0)),2) AS "Net Activity"

, ROUND(SUM((NVL(gb.period_net_dr,0) + NVL(gb.begin_balance_dr,0)))

- SUM(NVL(gb.period_net_cr,0) + NVL(gb.begin_balance_cr,0)),2) AS "Ending Balance"

, ROUND(SUM((NVL(gb1.period_net_dr,0) + NVL(gb1.begin_balance_dr,0)))

- SUM(NVL(gb1.period_net_cr,0) + NVL(gb1.begin_balance_cr,0)),2) AS "End Bal -1"

, ROUND(SUM((NVL(gb2.period_net_dr,0) + NVL(gb2.begin_balance_dr,0)))

- SUM(NVL(gb2.period_net_cr,0) + NVL(gb2.begin_balance_cr,0)),2) AS "End Bal -2"

, ROUND(SUM((NVL(gb3.period_net_dr,0) + NVL(gb3.begin_balance_dr,0)))

- SUM(NVL(gb3.period_net_cr,0) + NVL(gb3.begin_balance_cr,0)),2) AS "End Bal -3"

, ROUND(SUM((NVL(gb4.period_net_dr,0) + NVL(gb4.begin_balance_dr,0)))

- SUM(NVL(gb4.period_net_cr,0) + NVL(gb4.begin_balance_cr,0)),2) AS "End Bal -4"

, ROUND(SUM((NVL(gb5.period_net_dr,0) + NVL(gb5.begin_balance_dr,0)))

- SUM(NVL(gb5.period_net_cr,0) + NVL(gb5.begin_balance_cr,0)),2) AS "End Bal -5"

, ROUND(SUM((NVL(gb6.period_net_dr,0) + NVL(gb6.begin_balance_dr,0)))

- SUM(NVL(gb6.period_net_cr,0) + NVL(gb6.begin_balance_cr,0)),2) AS "End Bal -6"

, ROUND(SUM((NVL(gb7.period_net_dr,0) + NVL(gb7.begin_balance_dr,0)))

- SUM(NVL(gb7.period_net_cr,0) + NVL(gb7.begin_balance_cr,0)),2) AS "End Bal -7"

, ROUND(SUM((NVL(gb8.period_net_dr,0) + NVL(gb8.begin_balance_dr,0)))

- SUM(NVL(gb8.period_net_cr,0) + NVL(gb8.begin_balance_cr,0)),2) AS "End Bal -8"

, ROUND(SUM((NVL(gb9.period_net_dr,0) + NVL(gb9.begin_balance_dr,0)))

- SUM(NVL(gb9.period_net_cr,0) + NVL(gb9.begin_balance_cr,0)),2) AS "End Bal -9"

, ROUND(SUM((NVL(gb10.period_net_dr,0) + NVL(gb10.begin_balance_dr,0)))

- SUM(NVL(gb10.period_net_cr,0) + NVL(gb10.begin_balance_cr,0)),2) AS "End Bal -10"

, ROUND(SUM((NVL(gb11.period_net_dr,0) + NVL(gb11.begin_balance_dr,0)))

- SUM(NVL(gb11.period_net_cr,0) + NVL(gb11.begin_balance_cr,0)),2) AS "End Bal -11"

, ROUND(SUM(NVL(gb1.period_net_dr,0) - NVL(gb1.period_net_cr,0)),2) AS "Net Act -1"

, ROUND(SUM(NVL(gb2.period_net_dr,0) - NVL(gb2.period_net_cr,0)),2) AS "Net Act -2"

, ROUND(SUM(NVL(gb3.period_net_dr,0) - NVL(gb3.period_net_cr,0)),2) AS "Net Act -3"

, ROUND(SUM(NVL(gb4.period_net_dr,0) - NVL(gb4.period_net_cr,0)),2) AS "Net Act -4"

, ROUND(SUM(NVL(gb5.period_net_dr,0) - NVL(gb5.period_net_cr,0)),2) AS "Net Act -5"

, ROUND(SUM(NVL(gb6.period_net_dr,0) - NVL(gb6.period_net_cr,0)),2) AS "Net Act -6"

, ROUND(SUM(NVL(gb7.period_net_dr,0) - NVL(gb7.period_net_cr,0)),2) AS "Net Act -7"

, ROUND(SUM(NVL(gb8.period_net_dr,0) - NVL(gb8.period_net_cr,0)),2) AS "Net Act -8"

, ROUND(SUM(NVL(gb9.period_net_dr,0) - NVL(gb9.period_net_cr,0)),2) AS "Net Act -9"

, ROUND(SUM(NVL(gb10.period_net_dr,0) - NVL(gb10.period_net_cr,0)),2) AS "Net Act -10"

, ROUND(SUM(NVL(gb11.period_net_dr,0) - NVL(gb11.period_net_cr,0)),2) AS "Net Act -11"

FROM apps.gl_balances gb

INNER JOIN apps.gl_code_combinations gcc

ON gcc.code_combination_id = gb.code_combination_id

INNER JOIN apps.gl_sets_of_books sob

ON gb.currency_code = sob.currency_code

AND gb.ledger_id = sob.set_of_books_id

LEFT JOIN apps.gl_balances gb1

ON gb1.code_combination_id = gb.code_combination_id

AND gb1.currency_code = gb.currency_code

AND gb1.ledger_id = gb.ledger_id

AND gb1.actual_flag = gb.actual_flag

AND NVL(gb1.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb1.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -1),'MON-YY')

LEFT JOIN apps.gl_balances gb2

ON gb2.code_combination_id = gb.code_combination_id

AND gb2.currency_code = gb.currency_code

AND gb2.ledger_id = gb.ledger_id

AND gb2.actual_flag = gb.actual_flag

AND NVL(gb2.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb2.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -2),'MON-YY')

LEFT JOIN apps.gl_balances gb3

ON gb3.code_combination_id = gb.code_combination_id

AND gb3.currency_code = gb.currency_code

AND gb3.ledger_id = gb.ledger_id

AND gb3.actual_flag = gb.actual_flag

AND NVL(gb3.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb3.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -3),'MON-YY')

LEFT JOIN apps.gl_balances gb4

ON gb4.code_combination_id = gb.code_combination_id

AND gb4.currency_code = gb.currency_code

AND gb4.ledger_id = gb.ledger_id

AND gb4.actual_flag = gb.actual_flag

AND NVL(gb4.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb4.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -4),'MON-YY')

LEFT JOIN apps.gl_balances gb5

ON gb5.code_combination_id = gb.code_combination_id

AND gb5.currency_code = gb.currency_code

AND gb5.ledger_id = gb.ledger_id

AND gb5.actual_flag = gb.actual_flag

AND NVL(gb5.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb5.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -5),'MON-YY')

LEFT JOIN apps.gl_balances gb6

ON gb6.code_combination_id = gb.code_combination_id

AND gb6.currency_code = gb.currency_code

AND gb6.ledger_id = gb.ledger_id

AND gb6.actual_flag = gb.actual_flag

AND NVL(gb6.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb6.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -6),'MON-YY')

LEFT JOIN apps.gl_balances gb7

ON gb7.code_combination_id = gb.code_combination_id

AND gb7.currency_code = gb.currency_code

AND gb7.ledger_id = gb.ledger_id

AND gb7.actual_flag = gb.actual_flag

AND NVL(gb7.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb7.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -7),'MON-YY')

LEFT JOIN apps.gl_balances gb8

ON gb8.code_combination_id = gb.code_combination_id

AND gb8.currency_code = gb.currency_code

AND gb8.ledger_id = gb.ledger_id

AND gb8.actual_flag = gb.actual_flag

AND NVL(gb8.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb8.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -8),'MON-YY')

LEFT JOIN apps.gl_balances gb9

ON gb9.code_combination_id = gb.code_combination_id

AND gb9.currency_code = gb.currency_code

AND gb9.ledger_id = gb.ledger_id

AND gb9.actual_flag = gb.actual_flag

AND NVL(gb9.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb9.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -9),'MON-YY')

LEFT JOIN apps.gl_balances gb10

ON gb10.code_combination_id = gb.code_combination_id

AND gb10.currency_code = gb.currency_code

AND gb10.ledger_id = gb.ledger_id

AND gb10.actual_flag = gb.actual_flag

AND NVL(gb10.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb10.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -10),'MON-YY')

LEFT JOIN apps.gl_balances gb11

ON gb11.code_combination_id = gb.code_combination_id

AND gb11.currency_code = gb.currency_code

AND gb11.ledger_id = gb.ledger_id

AND gb11.actual_flag = gb.actual_flag

AND NVL(gb11.template_id,-1) = NVL(gb.template_id,-1)

AND UPPER(gb11.period_name) = to_char(ADD_MONTHS(to_date(gb.period_name,'MON-YY'), -11),'MON-YY')

WHERE gb.actual_flag = 'A'

AND gb.template_id IS NULL

AND gb.period_name = &PeriodName

AND sob.NAME LIKE &Ledger

AND gcc.segment2 LIKE &CostCenter

AND gcc.segment3 LIKE &AccountNumber

GROUP BY

sob.name

, gb.period_name

, gcc.segment2

, gcc.segment3

, gcc.segment1||'-'||

gcc.segment2||'-'||

gcc.segment3||'-'||

gcc.segment4||'-'||

gcc.segment5||'-'||

gcc.segment6||'-'||

gcc.segment7

, NVL(gb.period_net_dr,0)

, NVL(gb.period_net_cr,0)

, gb.currency_code

HAVING SUM((NVL(gb.period_net_dr,0) + NVL(gb.begin_balance_dr,0)))

- SUM(NVL(gb.period_net_cr,0) + NVL(gb.begin_balance_cr,0))

<> DECODE(UPPER(SUBSTR(:IncluceZeroBalances,1,1)),'Y',999999999999999999999,0)