WITH xds
AS (
SELECT xds.*
FROM xxeos_deftax_structure xds
WHERE xds.upload = 'Y'
)
SELECT x.a_konto,
xds.b_bezeichnung,
CASE
WHEN substr(x.a_konto, instr(x.a_konto, '_') + 1) LIKE '%.%'
THEN ' '
ELSE substr(x.a_konto, instr(x.a_konto, '_') + 1)
END AS "IC",
CASE
WHEN substr(x.a_konto, instr(x.a_konto, '_') + 1) LIKE '%.%'
THEN ' '
ELSE substr(xds.b_bezeichnung, instr(xds.b_bezeichnung, '_', - 1) + 1)
END AS "IC_Bezeichnung",
xds.c_ebene,
xds.d_uebergeordnete_position,
NULL e_vorgaenger,
xds.f_leer,
xds.g_leer,
xds.h_leer,
xds.i_ausweis,
xds.j_postentyp,
xds.k_postenart,
x.ledger,
x.period_name,
if (xds.j_postentyp='Verm%',x.amount, x.amount*-1) as Betrag,
x.amount,
x.path_
FROM (
SELECT z.*, ROW_NUMBER() OVER(PARTITION BY PATH_ ORDER BY PATH_, AMOUNT ASC) AS RN_WINDOW --WINDOW FUNCTION FOR filtering OUT "empty rows" WHEN actual matches WITH period have been found
FROM (
SELECT w.level__,
w.path_,
w.a_konto,
nvl(w.ledger, 'Total') ledger,
sum(w.amount) amount,
w.period_name period_name
FROM (
WITH gl AS (
SELECT gl.*
FROM gl_ledgers gl
WHERE 1=1
AND gl.complete_flag = 'Y'
AND gl.ledger_category_code = 'SECONDARY'
AND gl.currency_code = 'EUR'
AND EXISTS (
SELECT NULL
FROM gl_balances gb
WHERE 2=2
AND gl.ledger_id = gb.ledger_id
AND gl.currency_code = gb.currency_code
AND gb.actual_flag = 'A'
)
)
SELECT v.*,
gb.amount,
gb.period_name
FROM (
SELECT rowgen.column_value level__,
regexp_substr(u.path, '[^|]+', 1, rowgen.column_value) a_konto,
nvl(substr(u.path, 1, instr(u.path, '|', 1, rowgen.column_value) - 1), u.path) path_,
substr(u.a_konto, 1, instr(u.a_konto, '_') - 1) account,
substr(u.a_konto, instr(u.a_konto, '_') + 1) intercompany,
gl.name ledger
FROM (
SELECT LEVEL level_,
substr(sys_connect_by_path(nvl(xds.a_konto, 'x'), '|'), 2) path,
connect_by_isleaf,
xds.*
FROM xds
WHERE connect_by_isleaf = 1 connect BY nocycle xds.d_uebergeordnete_position = prior xds.a_konto start
WITH xds.d_uebergeordnete_position IS NULL
ORDER BY xds.deftax_structure_id
) u,
TABLE (xxen_util.rowgen(u.level_)) rowgen,
gl
) v,
(
SELECT
/*+ index(gb(period_name,ledger_id))*/
DISTINCT sum(nvl(gb.begin_balance_dr, 0) - nvl(gb.begin_balance_cr, 0) + nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0)) OVER (
PARTITION BY gl.name,
gcc.segment2,
gcc.segment5,
gb.period_name
) amount,
gl.name ledger,
gcc.segment2,
gcc.segment5,
gb.period_name
FROM gl,
gl_balances gb,
gl_code_combinations gcc
WHERE 2=2
AND gl.ledger_id = gb.ledger_id
AND gl.currency_code = gb.currency_code
AND gb.template_id IS NULL
AND gcc.summary_flag = 'N'
AND gb.actual_flag = 'A'
AND gb.code_combination_id = gcc.code_combination_id
) gb
WHERE v.ledger = gb.ledger(+)
AND v.account = gb.segment2(+)
AND v.intercompany = gb.segment5(+)
) w
GROUP BY grouping sets((
w.level__,
w.path_,
w.a_konto
), (
w.level__,
w.path_,
w.a_konto,
w.ledger,
period_name
))
) z
) x,
xds
WHERE x.a_konto = xds.a_konto(+)
AND x.ledger NOT LIKE '%Total%'
AND NOT (PERIOD_NAME IS NULL AND RN_WINDOW > 1)
and x.amount <> 0
ORDER BY x.ledger,
xds.deftax_structure_id
Telegram: DB developers channel