SQLize Online / PHPize Online  /  SQLtest Online

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