WITH
data AS -- dataset simulation
(
Select '1' LVL, 'ESF_A' PK1_START_VALUE, '' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'ACTIVOS' DESCRIPTION, '39190828556' SUM_TOTAL_ONE, '-25229236703' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '2' LVL, 'ESF_ACT' PK1_START_VALUE, 'ESF_A' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'ACTIVO' DESCRIPTION, '39190828556' SUM_TOTAL_ONE, '-25229236703' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_AC' PK1_START_VALUE, 'ESF_ACT' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Activo corriente' DESCRIPTION, '39020965705' SUM_TOTAL_ONE, '-25229281532' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_ADCC' PK1_START_VALUE, 'ESF_AC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Deudores comerciales y otras cuentas por cobrar' DESCRIPTION, '37908898836' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_AEE' PK1_START_VALUE, 'ESF_AC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Efectivo y equivalentes de efectivo' DESCRIPTION, '-8270170' SUM_TOTAL_ONE, '-2011950' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_AIM' PK1_START_VALUE, 'ESF_AC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Inventario de minerales' DESCRIPTION, '134677932' SUM_TOTAL_ONE, '-25227269582' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_AIMC' PK1_START_VALUE, 'ESF_AC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Activos por impuestos corrientes' DESCRIPTION, '985659107' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_AIMT' PK1_START_VALUE, 'ESF_AC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Inventario de materiales' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_ANC' PK1_START_VALUE, 'ESF_ACT' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Activo no corriente' DESCRIPTION, '169862851' SUM_TOTAL_ONE, '44829' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_ADIR' PK1_START_VALUE, 'ESF_ANC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Diferido por impuesto de renta' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_AINT' PK1_START_VALUE, 'ESF_ANC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Intangibles' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_ANCC' PK1_START_VALUE, 'ESF_ANC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Deudores comerciales y otras cuentas por cobrar' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_ANPE' PK1_START_VALUE, 'ESF_ANC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Propiedad, planta y equipo, neto' DESCRIPTION, '169862851' SUM_TOTAL_ONE, '44829' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_APEE' PK1_START_VALUE, 'ESF_ANC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Proyecto de exploración y evaluación' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_A' PARENT From DUAL UNION ALL
Select '1' LVL, 'ESF_PYP' PK1_START_VALUE, '' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'PASIVOS Y PATRIMONIO' DESCRIPTION, '-16924393691' SUM_TOTAL_ONE, '-3789583' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '2' LVL, 'ESF_EQY' PK1_START_VALUE, 'ESF_PYP' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Patrimonio' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_ECE' PK1_START_VALUE, 'ESF_EQY' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Capital emitido' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_EGPA' PK1_START_VALUE, 'ESF_EQY' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Ganancias (pérdidas) acumuladas' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '37050103' PK1_START_VALUE, 'ESF_EGPA' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Ganancia O Perdida De Resultados Anteriores' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_EORI' PK1_START_VALUE, 'ESF_EQY' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Otro resultado integral (ESFA)' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '37050102' PK1_START_VALUE, 'ESF_EORI' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Utilidades Retenidas Por Conversion Niif' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_EPCA' PK1_START_VALUE, 'ESF_EQY' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Prima en colocación de acciones' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '32050501' PK1_START_VALUE, 'ESF_EPCA' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Prima En Colocacion De Acciones' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_ER' PK1_START_VALUE, 'ESF_EQY' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Reservas ' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '33050501' PK1_START_VALUE, 'ESF_ER' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Reserva Legal' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '33050502' PK1_START_VALUE, 'ESF_ER' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Otras Reserva Legal' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '33150501' PK1_START_VALUE, 'ESF_ER' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Para Beneficencia Y Civismo' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '33150502' PK1_START_VALUE, 'ESF_ER' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Para Futuros Ensanches' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '33150503' PK1_START_VALUE, 'ESF_ER' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Adquis 0 Reposicion De Prop P Y E ' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '33150504' PK1_START_VALUE, 'ESF_ER' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Expansion De La Operacion-Reposic De Activos' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_EREA' PK1_START_VALUE, 'ESF_EQY' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Resultados de ejercicios anteriores ' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '37050101' PK1_START_VALUE, 'ESF_EREA' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Utilidades Acumuladas' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_ERNP' PK1_START_VALUE, 'ESF_EQY' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Resultado neto del perÃodo' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, '36050501' PK1_START_VALUE, 'ESF_ERNP' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Utilidades Del Ejercicio' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '2' LVL, 'ESF_PS' PK1_START_VALUE, 'ESF_PYP' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Pasivo' DESCRIPTION, '-16924393691' SUM_TOTAL_ONE, '-3789583' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_PNC' PK1_START_VALUE, 'ESF_PS' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Pasivos no corriente' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_PNAP' PK1_START_VALUE, 'ESF_PNC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Acreedores y otras cuentas por pagar' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_PNBE' PK1_START_VALUE, 'ESF_PNC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Pasivo por beneficios a empleados' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_PNCV' PK1_START_VALUE, 'ESF_PNC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Cuentas por pagar compañÃas vinculadas' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_PNOP' PK1_START_VALUE, 'ESF_PNC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Otros Pasivos' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_PPD' PK1_START_VALUE, 'ESF_PNC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Provisión por desmantelamiento' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '3' LVL, 'ESF_PSC' PK1_START_VALUE, 'ESF_PS' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Pasivos corriente' DESCRIPTION, '-16924393691' SUM_TOTAL_ONE, '-3789583' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_PACP' PK1_START_VALUE, 'ESF_PSC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Acreedores y otras cuentas por pagar' DESCRIPTION, '-1099337488' SUM_TOTAL_ONE, '-2683771' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_PBE' PK1_START_VALUE, 'ESF_PSC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Pasivo por beneficios a empleados' DESCRIPTION, '-15828782849' SUM_TOTAL_ONE, '-1421012' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_PCPV' PK1_START_VALUE, 'ESF_PSC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Cuentas por pagar compañÃas vinculadas' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_PCYP' PK1_START_VALUE, 'ESF_PSC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Créditos y préstamos' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_PIC' PK1_START_VALUE, 'ESF_PSC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Pasivos por impuesto corriente' DESCRIPTION, '3726646' SUM_TOTAL_ONE, '315200' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL UNION ALL
Select '4' LVL, 'ESF_POP' PK1_START_VALUE, 'ESF_PSC' PARENT_PK1_VALUE, '300000002652023' LEGAL_ENTITY_ID, 'Otros Pasivos' DESCRIPTION, '0' SUM_TOTAL_ONE, '0' SUM_TOTAL_TWO, 'ESF_PYP' PARENT From DUAL
)
SELECT XMLSERIALIZE(
CONTENT
DBMS_XMLGEN.GETXMLTYPE(
DBMS_XMLGEN.NEWCONTEXTFROMHIERARCHY(
Q'[
SELECT LEVEL,
XMLELEMENT(
EVALNAME(
NVL2(
PARENT_PK1_VALUE,
'CHILD',
'PARENT_PRINCIPAL'
)
),
XMLELEMENT("LVL",LVL),
XMLELEMENT("PK1_START_VALUE",PK1_START_VALUE),
XMLELEMENT("LEGAL_ENTITY_ID",LEGAL_ENTITY_ID),
XMLELEMENT("DESCRIPTION",DESCRIPTION),
XMLELEMENT("SUM_TOTAL_ONE",SUM_TOTAL_ONE),
XMLELEMENT("SUM_TOTAL_TWO",SUM_TOTAL_TWO),
XMLELEMENT("PARENT",PARENT)
)
FROM DATA D
START WITH PARENT_PK1_VALUE IS NULL
CONNECT BY PARENT_PK1_VALUE = PRIOR PK1_START_VALUE
AND LEGAL_ENTITY_ID = PRIOR LEGAL_ENTITY_ID
]'
)
)
INDENT
SIZE = 2
) XMLDOC
FROM DUAL