I am writing a query that is used to reconcile a legacy system that interfaces it's trial balance into the R12 Oracle GL. This was only supposed to go on for a month or two, but it is likely to go on for 6 months. To please the auditors we need to provide proof that the legacy system is in balance with Oracle GL. Per my audit requirements, I need to do a full reconciliation starting from the conversion month (life to date amount) and then PTD for each month after.
The legacy account is placed into attribute1 on the journals lines. The legacy system uses balancing segments that are also used on-platform in Oracle for this division, i.e., Procure-to-Pay was cut over to Oracle, but not everything yet. Thus, I cannot rely on the GL_BALANCES table to get the info, I have to get it from the JE_LINES.
My problem is not the single query for the single month. But when I try to combine the queries with a union, with the intent of aggregating each measurement period into it's own column, the group by is required after each select statement rather than allowing me to put it at the end of the UNION. (When I put the group by at the end of the UNION, i get the "not a single-group" function)
Thus, I get duplicate legacy accounts for each discrete month being measured. When I have duplicate legacy accounts in my Oracle data, I cannot rely on the vlookup in excel to accurately match to a legacy account. I know there are more sophisticated ways to provide this output, but I'm hoping to get this info out in a simple query.
I thank you in advance for any advice you can provide
Example of the data output (the goal for me is to get the two rows to appear as one based on the commonality on the LEGACY_ACCOUNT and the ORACLE ACCOUNT
| LEGACY ACCOUNT | ORACLE ACCOUNT | JUN_15 | JUL_15 | AUG_15 | SEP_15 | OCT_15 | NOV_15 | DEC_15 |
|---|
| 010000001109000003584 | 190-600552-1001-100231-000-0000-0000-0000 | 0 | 0 | -242961.04 | 0 | 0 | 0 | 0 |
| 010000001109000003584 | 190-600552-1001-100231-000-0000-0000-0000 | 0 | 192588.02 | 0 | 0 | 0 | 0 | 0 |
| | | | | | | | |
Here is a simplified version of my code which returns the two records. In my research I had found a number of conversations where it was shown that the group by could be put at the end of the select statement. However, when I remove the group by after the first select statement I get the SQL Error: ORA-00937: not a single-group group function
select
l.attribute1 LEGACY_ACCOUNT,
C.SEGMENT1||'-'||C.SEGMENT2||'-'||C.SEGMENT3||'-'||C.SEGMENT4||'-'||C.SEGMENT5||'-'||C.SEGMENT6||'-'||C.SEGMENT7||'-'||C.SEGMENT8 COMBINATION,
to_number('0') JUN_15,
sum(nvl(l.accounted_dr,0.00)-nvl(l.accounted_cr,0.00)) JUL_15,
to_number('0') AUG_15,
to_number('0') SEP_15,
to_number('0') OCT_15,
to_number('0') NOV_15,
to_number('0') DEC_15
from
gl.gl_je_batches b,
gl.gl_je_headers h,
gl.gl_je_lines l,
gl.gl_code_combinations c,
gl.gl_je_sources_tl j
where b.je_batch_id = h.je_batch_id
and h.je_header_id = l.je_header_id
and l.code_combination_id = c.code_combination_id
and h.je_source = j.je_source_name
and c.segment1 in ('190','191','192','193','194','195','196','197','198','199',
'200','203','205','206','330','331','332','333','334','335','336','337')
and j.language = 'US'
and h.PERIOD_NAME in ('JUL-15')
group by
l.attribute1,
C.SEGMENT1||'-'||C.SEGMENT2||'-'||C.SEGMENT3||'-'||C.SEGMENT4||'-'||C.SEGMENT5||'-'||C.SEGMENT6||'-'||C.SEGMENT7||'-'||C.SEGMENT8
UNION
select
l.attribute1 LEGACY_ACCOUNT,
C.SEGMENT1||'-'||C.SEGMENT2||'-'||C.SEGMENT3||'-'||C.SEGMENT4||'-'||C.SEGMENT5||'-'||C.SEGMENT6||'-'||C.SEGMENT7||'-'||C.SEGMENT8 COMBINATION,
to_number('0') JUN_15,
to_number('0') JUL_15,
sum(nvl(l.accounted_dr,0.00)-nvl(l.accounted_cr,0.00)) AUG_15,
to_number('0') SEP_15,
to_number('0') OCT_15,
to_number('0') NOV_15,
to_number('0') DEC_15
from
gl.gl_je_batches b,
gl.gl_je_headers h,
gl.gl_je_lines l,
gl.gl_code_combinations c,
gl.gl_je_sources_tl j
where b.je_batch_id = h.je_batch_id
and h.je_header_id = l.je_header_id
and l.code_combination_id = c.code_combination_id
and h.je_source = j.je_source_name
and c.segment1 in ('190','191','192','193','194','195','196','197','198','199',
'200','203','205','206','330','331','332','333','334','335','336','337')
and j.language = 'US'
and h.PERIOD_NAME in ('AUG-15')
group by
l.attribute1,
C.SEGMENT1||'-'||C.SEGMENT2||'-'||C.SEGMENT3||'-'||C.SEGMENT4||'-'||C.SEGMENT5||'-'||C.SEGMENT6||'-'||C.SEGMENT7||'-'||C.SEGMENT8
order by 1