-- Consulta registrada el 2025-07-08T20:26:45.386899
select * from fnd_lookup_values_vl
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-08T15:42:41.666366
SELECT
* FROM 
  GL_CODE_COMBINATIONS
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-07T16:17:17.294526
select * FROM GL_CODE_COMBINATIONS
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-07T15:10:47.322249
SELECT
* FROM 
  GL_JE_LINES
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-07T14:17:25.078354
select * FROM GL_PERIODS
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-06T22:33:34.507959
SELECT
 
-- Header
h.LEDGER_ID,
h.JE_BATCH_ID,
h.POSTING_ACCT_SEQ_VALUE,
h.JE_HEADER_ID,
h.JE_CATEGORY,
h.JE_SOURCE,
h.NAME,
h.STATUS,
h.POSTED_DATE,
h.DESCRIPTION,
 
-- Line
l.JE_LINE_NUM, 
l.CODE_COMBINATION_ID,
l.PERIOD_NAME,
l.ACCOUNTED_DR,
l.ACCOUNTED_CR,
l.DESCRIPTION,
l.STATUS,

-- Code combinations
c.CODE_COMBINATION_ID,
c.SEGMENT1,
c.SEGMENT2,
c.SEGMENT3,
c.SEGMENT4,
c.SEGMENT5,
c.SEGMENT6,
c.SEGMENT7,
c.SEGMENT8,
c.CONCATENATED_SEGMENTS,
c.ACCOUNT_TYPE
 
from gl_je_headers h
inner join gl_je_lines l
on h.JE_HEADER_ID = l.JE_HEADER_ID
inner join gl_code_combinations c
on l.CODE_COMBINATION_ID = c.CODE_COMBINATION_ID
 
where c.ACCOUNT_TYPE in ('R', 'E')
and h.STATUS = 'P'
order by h.POSTED_DATE

fetch first 50 rows only

-- Consulta registrada el 2025-07-04T21:06:56.366649
SELECT
* FROM 
  GL_periods
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-04T20:36:45.141421
SELECT
  h.POSTING_ACCT_SEQ_ASSIGN_ID
FROM 
  GL_JE_HEADERS h
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-04T20:15:58.093466
SELECT 
    POSTING_ACCT_SEQ_ASSIGN_ID
FROM 
    GL_JE_HEADERS
WHERE 
    POSTING_ACCT_SEQ_ASSIGN_ID IS NOT NULL
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-04T20:14:10.717002
select * FROM GL_CODE_COMBINATIONS
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-04T20:09:17.680340
SELECT 
    JE_HEADER_ID,
    NAME,
    POSTING_ACCT_SEQ_ASSIGN_ID,
    DEFAULT_EFFECTIVE_DATE
FROM 
    GL_JE_HEADERS
WHERE 
    POSTING_ACCT_SEQ_ASSIGN_ID IS NOT NULL
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-04T19:59:56.541308
select * FROM GL_JE_HEADERS
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-04T16:38:40.684380
SELECT
    rev.JE_HEADER_ID         AS reversa_id,
    rev.NAME                 AS reversa_nombre,
    rev.DEFAULT_EFFECTIVE_DATE AS fecha_reversa,
    orig.JE_HEADER_ID        AS original_id,
    orig.NAME                AS original_nombre,
    orig.DEFAULT_EFFECTIVE_DATE AS fecha_original
FROM
    GL_JE_HEADERS rev
JOIN
    GL_JE_HEADERS orig
    ON rev.REVERSED_JE_HEADER_ID = orig.JE_HEADER_ID
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-04T16:33:33.818731
SELECT
    rev.JE_HEADER_ID         AS reversa_id,
    rev.NAME                 AS reversa_nombre,
    rev.DEFAULT_EFFECTIVE_DATE AS fecha_reversa,
    orig.JE_HEADER_ID        AS original_id,
    orig.NAME                AS original_nombre,
    orig.DEFAULT_EFFECTIVE_DATE AS fecha_original
FROM
    GL_JE_HEADERS rev
JOIN
    GL_JE_HEADERS orig
    ON rev.REVERSED_JE_HEADER_ID = orig.JE_HEADER_ID

-- Consulta registrada el 2025-07-04T16:32:20.224606
select * FROM GL_JE_HEADERS

-- Consulta registrada el 2025-07-04T15:08:05.862129
select * from gl_je_lines fetch first 100 rows only

-- Consulta registrada el 2025-07-04T15:03:03.327093
select * FROM GL_JE_HEADERS

-- Consulta registrada el 2025-07-04T15:00:34.799695
SELECT
 
-- Header
h.LEDGER_ID,
h.JE_BATCH_ID,
h.POSTING_ACCT_SEQ_VALUE,
h.JE_HEADER_ID,
h.JE_CATEGORY,
h.JE_SOURCE,
h.NAME,
h.STATUS,
h.POSTED_DATE,
h.DESCRIPTION,
 
-- Line
l.JE_LINE_NUM, 
l.CODE_COMBINATION_ID,
l.PERIOD_NAME,
l.ACCOUNTED_DR,
l.ACCOUNTED_CR,
l.DESCRIPTION,
l.STATUS,

-- Code combinations
c.CODE_COMBINATION_ID,
c.SEGMENT1,
c.SEGMENT2,
c.SEGMENT3,
c.SEGMENT4,
c.SEGMENT5,
c.SEGMENT6,
c.SEGMENT7,
c.SEGMENT8,
c.CONCATENATED_SEGMENTS,
c.ACCOUNT_TYPE
 
from gl_je_headers h
inner join gl_je_lines l
on h.JE_HEADER_ID = l.JE_HEADER_ID
inner join gl_code_combinations c
on l.CODE_COMBINATION_ID = c.CODE_COMBINATION_ID
 
where c.ACCOUNT_TYPE in ('R', 'E')
and h.STATUS = 'P'
order by h.POSTED_DATE

fetch first 50 rows only

-- Consulta registrada el 2025-07-04T14:59:21.579430
select distinct POSTING_ACCT_SEQ_ASSIGN_ID, je_category, period_name from gl_je_headers order by 1 fetch first 100 rows only

-- Consulta registrada el 2025-07-04T14:58:11.483227
select distinct POSTING_ACCT_SEQ_ASSIGN_ID, je_category, period_name from gl_je_headers fetch first 100 rows only

-- Consulta registrada el 2025-07-04T14:56:43.501545
select distinct POSTING_ACCT_SEQ_ASSIGN_ID from gl_je_headers fetch first 100 rows only

-- Consulta registrada el 2025-07-04T14:53:48.576714
select * from gl_je_headers where POSTING_ACCT_SEQ_VALUE = '1' fetch first 100 rows only

-- Consulta registrada el 2025-07-04T14:33:00.228413
select * from gl_je_headers fetch first 100 rows only

-- Consulta registrada el 2025-07-04T14:31:19.125650
SELECT
 
-- Header
h.LEDGER_ID,
h.JE_BATCH_ID,
h.POSTING_ACCT_SEQ_VALUE,
h.JE_HEADER_ID,
h.JE_CATEGORY,
h.JE_SOURCE,
h.NAME,
h.STATUS,
h.POSTED_DATE,
h.DESCRIPTION,
 
-- Line
l.JE_LINE_NUM, 
l.CODE_COMBINATION_ID,
l.PERIOD_NAME,
l.ACCOUNTED_DR,
l.ACCOUNTED_CR,
l.DESCRIPTION,
l.STATUS,

-- Code combinations
c.CODE_COMBINATION_ID,
c.SEGMENT1,
c.SEGMENT2,
c.SEGMENT3,
c.SEGMENT4,
c.SEGMENT5,
c.SEGMENT6,
c.SEGMENT7,
c.SEGMENT8,
c.CONCATENATED_SEGMENTS,
c.ACCOUNT_TYPE
 
from gl_je_headers h
inner join gl_je_lines l
on h.JE_HEADER_ID = l.JE_HEADER_ID
inner join gl_code_combinations c
on l.CODE_COMBINATION_ID = c.CODE_COMBINATION_ID
 
where c.ACCOUNT_TYPE in ('R', 'E')
and h.STATUS = 'P'
order by h.POSTED_DATE

fetch first 50 rows only

-- Consulta registrada el 2025-07-04T14:08:09.258188
SELECT
      PERIOD_NAME,
      PERIOD_SET_NAME,
      PERIOD_YEAR,
      PERIOD_NUM,
      ADJUSTMENT_PERIOD_FLAG,
      LAST_UPDATE_DATE,
      START_DATE,
      END_DATE,
      CREATION_DATE
    FROM GL_PERIODS
    WHERE PERIOD_SET_NAME in ('UAI')
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-04T14:00:10.611219
SELECT
      PERIOD_NAME,
      PERIOD_SET_NAME,
      PERIOD_YEAR,
      PERIOD_NUM,
      ADJUSTMENT_PERIOD_FLAG,
      LAST_UPDATE_DATE,
      START_DATE,
      END_DATE,
      CREATION_DATE
    FROM GL_PERIODS
    WHERE PERIOD_SET_NAME in ('UAI')

-- Consulta registrada el 2025-07-04T13:45:47.757190
select * FROM GL_PERIODS
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-03T21:54:12.886468
select * FROM GL_JE_LINES
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-03T21:36:44.799865
select * FROM GL_PERIODS
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-03T16:54:07.461415
WITH 
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
),
cte_presupuesto AS (
  SELECT 
    xbc.segment_value2 AS CGD,
    xbc.segment_value3 AS CCD,
    -SUM(xcb.funds_available_amount) AS pcargado
  FROM xcc_balances xcb
  JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
  JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
  WHERE xcb.control_budget_id = '300000003988192'
    AND xcb.period_name = 'Feb-25'
  GROUP BY xbc.segment_value2, xbc.segment_value3
),
basic_only_ppto AS (
  SELECT 
    'NO_CONSO.' || p.CGD || '.' || p.CCD AS LOOKUP_CODE,
    '' AS ACCOUNT_TYPE,
    'PRESUPUESTO SIN REAL – ' || p.CGD || '.' || p.CCD AS DESCRIPTION,
    4 AS NIVEL,
    NULL AS PARENT,
    p.CGD AS CGD,
    p.CCD AS CCD,
    '' AS CC, '' AS CSA, '' AS SA,
    0 AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM cte_presupuesto p
  WHERE NOT EXISTS (
    SELECT 1 FROM basic b WHERE b.CGD = p.CGD AND b.CCD = p.CCD
  )
),
basic_extendido AS (
  SELECT * FROM basic
  UNION
  SELECT * FROM basic_only_ppto
)
SELECT 
  b.LOOKUP_CODE,
  b.ACCOUNT_TYPE,
  b.DESCRIPTION,
  b.NIVEL,
  b.PARENT,
  b.CGD,
  b.CCD,
  b.cc,
  b.csa,
  b.sa,
  -- saldo consolidado real
  NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(x.saldo) 
         FROM basic_extendido x 
         WHERE x.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE b.saldo
    END, 0) AS saldo,
  -- saldo consolidado presupuesto
  NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(p.pcargado) 
         FROM (
           SELECT bp.LOOKUP_CODE, 
                  (SELECT NVL(SUM(pp.pcargado),0)
                   FROM cte_presupuesto pp
                   WHERE pp.CGD = bp.CGD AND pp.CCD = bp.CCD) AS pcargado
           FROM basic_extendido bp
         ) p
         WHERE p.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE (SELECT NVL(SUM(pcargado),0) 
            FROM cte_presupuesto 
            WHERE cte_presupuesto.CGD = b.CGD AND cte_presupuesto.CCD = b.CCD)
    END, 0) AS pcargado,
  b.DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN b.LOOKUP_CODE LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(c.DISPLAY_SEQUENCE) 
        FROM conso c 
        WHERE c.LOOKUP_CODE = SUBSTR(RTRIM(b.LOOKUP_CODE, '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM basic_extendido b
WHERE NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(x.saldo) 
         FROM basic_extendido x 
         WHERE x.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE b.saldo
    END, 0) <> 0
   OR NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(p.pcargado) 
         FROM (
           SELECT bp.LOOKUP_CODE, 
                  (SELECT NVL(SUM(pp.pcargado),0)
                   FROM cte_presupuesto pp
                   WHERE pp.CGD = bp.CGD AND pp.CCD = bp.CCD) AS pcargado
           FROM basic_extendido bp
         ) p
         WHERE p.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE (SELECT NVL(SUM(pcargado),0) 
            FROM cte_presupuesto 
            WHERE cte_presupuesto.CGD = b.CGD AND cte_presupuesto.CCD = b.CCD)
    END, 0) <> 0
ORDER BY 14, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T16:52:04.069984
WITH 
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
),
cte_presupuesto AS (
  SELECT 
    xbc.segment_value2 AS CGD,
    xbc.segment_value3 AS CCD,
    -SUM(xcb.funds_available_amount) AS pcargado
  FROM xcc_balances xcb
  JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
  JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
  WHERE xcb.control_budget_id = '300000003988192'
    AND xcb.period_name = 'Feb-25'
  GROUP BY xbc.segment_value2, xbc.segment_value3
),
basic_ppto AS (
  SELECT 
    LOOKUP_CODE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    CGD, CCD,
    CASE WHEN CGD IS NULL THEN 0 ELSE 
      (SELECT NVL(SUM(pcargado),0)
       FROM cte_presupuesto
       WHERE cte_presupuesto.CGD = b.CGD AND cte_presupuesto.CCD = b.CCD)
    END AS pcargado
  FROM basic b
)
SELECT 
  b.LOOKUP_CODE,
  b.ACCOUNT_TYPE,
  b.DESCRIPTION,
  b.NIVEL,
  b.PARENT,
  b.CGD,
  b.CCD,
  b.cc,
  b.csa,
  b.sa,
  -- saldo consolidado real
  NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(x.saldo) 
         FROM basic x 
         WHERE x.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE b.saldo
    END, 0) AS saldo,
  -- saldo consolidado presupuesto
  NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(bp.pcargado) 
         FROM basic_ppto bp 
         WHERE bp.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE (SELECT NVL(SUM(pcargado),0) 
            FROM cte_presupuesto 
            WHERE cte_presupuesto.CGD = b.CGD AND cte_presupuesto.CCD = b.CCD)
    END, 0) AS pcargado,
  b.DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN b.LOOKUP_CODE LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(c.DISPLAY_SEQUENCE) 
        FROM conso c 
        WHERE c.LOOKUP_CODE = SUBSTR(RTRIM(b.LOOKUP_CODE, '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM basic b
WHERE NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(x.saldo) 
         FROM basic x 
         WHERE x.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE b.saldo
    END, 0) <> 0
   OR NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(bp.pcargado) 
         FROM basic_ppto bp 
         WHERE bp.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE (SELECT NVL(SUM(pcargado),0) 
            FROM cte_presupuesto 
            WHERE cte_presupuesto.CGD = b.CGD AND cte_presupuesto.CCD = b.CCD)
    END, 0) <> 0
ORDER BY 14, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T16:50:18.369281
WITH 
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
),
cte_presupuesto AS (
  SELECT 
    xbc.segment_value2 AS CGD,
    xbc.segment_value3 AS CCD,
    -SUM(xcb.funds_available_amount) AS pcargado
  FROM xcc_balances xcb
  JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
  JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
  WHERE xcb.control_budget_id = '300000003988192'
    AND xcb.period_name = 'Feb-25'
  GROUP BY xbc.segment_value2, xbc.segment_value3
)
SELECT 
  COALESCE(b.LOOKUP_CODE, 'NO_CONSO.' || p.CGD || '.' || p.CCD) AS LOOKUP_CODE,
  b.ACCOUNT_TYPE,
  b.DESCRIPTION,
  b.NIVEL,
  b.PARENT,
  NVL(b.CGD, p.CGD) AS CGD,
  NVL(b.CCD, p.CCD) AS CCD,
  b.cc,
  b.csa,
  b.sa,
  -- saldo consolidado
  NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(x.saldo) 
         FROM basic x 
         WHERE x.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE b.saldo
    END, 0) AS saldo,
  -- presupuesto consolidado
  NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(NVL(pp.pcargado,0))
         FROM (
           SELECT COALESCE(b2.CGD, p2.CGD) AS CGD, COALESCE(b2.CCD, p2.CCD) AS CCD,
                  NVL(p2.pcargado, 0) AS pcargado,
                  COALESCE(b2.LOOKUP_CODE, 'NO_CONSO.' || p2.CGD || '.' || p2.CCD) AS LOOKUP_CODE
           FROM basic b2
           FULL OUTER JOIN cte_presupuesto p2 ON p2.CGD = b2.CGD AND p2.CCD = b2.CCD
         ) pp
         WHERE pp.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE NVL(p.pcargado,0)
    END, 0) AS pcargado,
  b.DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN COALESCE(b.LOOKUP_CODE,'') LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(c.DISPLAY_SEQUENCE) 
        FROM conso c 
        WHERE c.LOOKUP_CODE = SUBSTR(RTRIM(COALESCE(b.LOOKUP_CODE,''), '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM basic b
FULL OUTER JOIN cte_presupuesto p
  ON p.CGD = b.CGD AND p.CCD = b.CCD
WHERE NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(x.saldo) 
         FROM basic x 
         WHERE x.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE b.saldo
    END, 0) <> 0
   OR NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(NVL(pp.pcargado,0))
         FROM (
           SELECT COALESCE(b2.CGD, p2.CGD) AS CGD, COALESCE(b2.CCD, p2.CCD) AS CCD,
                  NVL(p2.pcargado, 0) AS pcargado,
                  COALESCE(b2.LOOKUP_CODE, 'NO_CONSO.' || p2.CGD || '.' || p2.CCD) AS LOOKUP_CODE
           FROM basic b2
           FULL OUTER JOIN cte_presupuesto p2 ON p2.CGD = b2.CGD AND p2.CCD = b2.CCD
         ) pp
         WHERE pp.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE NVL(p.pcargado,0)
    END, 0) <> 0
ORDER BY 14, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T16:19:39.584499
select * from FND_FLEX_VALUES_VL where VALUE_CATEGORY = 'Tipo Programa' and enabled_flag = 'Y'
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-03T16:18:41.660933
WITH 
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
),
cte_presupuesto AS (
  SELECT 
    xbc.segment_value2 AS CGD,
    xbc.segment_value3 AS CCD,
    -SUM(xcb.funds_available_amount) AS pcargado
  FROM xcc_balances xcb
  JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
  JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
  WHERE xcb.control_budget_id = '300000003988192'
    AND xcb.period_name = 'Feb-25'
  GROUP BY xbc.segment_value2, xbc.segment_value3
)
SELECT 
  COALESCE(b.LOOKUP_CODE, 'NO_CONSO.' || p.CGD || '.' || p.CCD) AS LOOKUP_CODE,
  b.ACCOUNT_TYPE,
  b.DESCRIPTION,
  b.NIVEL,
  b.PARENT,
  NVL(b.CGD, p.CGD) AS CGD,
  NVL(b.CCD, p.CCD) AS CCD,
  b.cc,
  b.csa,
  b.sa,
  NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(x.saldo) 
         FROM basic x 
         WHERE x.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE b.saldo
    END, 0) AS saldo,
  NVL(p.pcargado, 0) AS pcargado,
  b.DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN COALESCE(b.LOOKUP_CODE,'') LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(c.DISPLAY_SEQUENCE) 
        FROM conso c 
        WHERE c.LOOKUP_CODE = SUBSTR(RTRIM(COALESCE(b.LOOKUP_CODE,''), '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM basic b
FULL OUTER JOIN cte_presupuesto p
  ON p.CGD = b.CGD AND p.CCD = b.CCD
WHERE NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(x.saldo) 
         FROM basic x 
         WHERE x.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE b.saldo
    END, 0) <> 0
   OR NVL(p.pcargado, 0) <> 0
ORDER BY 14, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T16:17:27.605760
select count(distinct flex_value_id) from FND_FLEX_VALUES_VL where enabled_flag = 'Y'
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-03T16:17:10.320939
select count(flex_value_id) from FND_FLEX_VALUES_VL where enabled_flag = 'Y'
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-03T16:15:21.978359
WITH 
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
),
cte_presupuesto AS (
  SELECT 
    xbc.segment_value2 AS CGD,
    xbc.segment_value3 AS CCD,
    -SUM(xcb.funds_available_amount) AS pcargado
  FROM xcc_balances xcb
  JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
  JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
  WHERE xcb.control_budget_id = '300000003988192'
    AND xcb.period_name = 'Feb-25'
  GROUP BY xbc.segment_value2, xbc.segment_value3
)
SELECT 
  RTRIM(NVL(b.LOOKUP_CODE, 'NO.DATA'), '.') AS LOOKUP_CODE,
  b.ACCOUNT_TYPE,
  b.DESCRIPTION,
  b.NIVEL,
  b.PARENT,
  NVL(b.CGD, p.CGD) AS CGD,
  NVL(b.CCD, p.CCD) AS CCD,
  b.cc,
  b.csa,
  b.sa,
  -- saldo real: si no hay, mostrar 0
  NVL(
    CASE 
      WHEN b.cc IS NULL THEN 
        (SELECT SUM(x.saldo) 
         FROM basic x 
         WHERE x.LOOKUP_CODE LIKE b.LOOKUP_CODE || '%')
      ELSE b.saldo
    END, 0) AS saldo,
  -- presupuesto: si no hay, mostrar 0
  NVL(p.pcargado, 0) AS pcargado,
  b.DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN NVL(b.LOOKUP_CODE,'') LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(c.DISPLAY_SEQUENCE) 
        FROM conso c 
        WHERE c.LOOKUP_CODE = SUBSTR(RTRIM(NVL(b.LOOKUP_CODE,''), '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM basic b
FULL OUTER JOIN cte_presupuesto p
  ON p.CGD = b.CGD AND p.CCD = b.CCD
ORDER BY 14, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T16:14:31.503619
select * from FND_FLEX_VALUES_VL where VALUE_CATEGORY = 'Tipo Programa' and enabled_flag = 'Y'
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2025-07-03T16:10:22.760838
SELECT 
  xcb.period_name,
  xbc.segment_value2 AS CGD,
  xbc.segment_value3 AS CCD,
  SUM(xcb.funds_available_amount) AS monto
FROM xcc_balances xcb
JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
WHERE xcb.control_budget_id = '300000003988192'
  AND xcb.period_name = 'Feb-25'
GROUP BY xcb.period_name, xbc.segment_value2, xbc.segment_value3
ORDER BY 1,2,3

-- Consulta registrada el 2025-07-03T15:54:55.591206
WITH 
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
)
SELECT 
  RTRIM(a.LOOKUP_CODE, '.') AS LOOKUP_CODE,
  a.ACCOUNT_TYPE,
  a.DESCRIPTION,
  a.NIVEL,
  a.PARENT,
  a.CGD,
  a.CCD,
  a.cc,
  a.csa,
  a.sa,
  -- saldo único: propio o consolidado
  CASE 
    WHEN a.cc IS NULL 
      THEN (SELECT SUM(b.saldo)
            FROM basic b
            WHERE b.LOOKUP_CODE LIKE a.LOOKUP_CODE || '%')
    ELSE a.saldo
  END AS saldo,
  -- presupuesto del mes
  (SELECT -SUM(xcb.funds_available_amount)
   FROM xcc_balances xcb
   JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
   JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
   WHERE xcb.control_budget_id = '300000003988192'
     AND xbc.segment_value2 = a.CGD
     AND xbc.segment_value3 = a.CCD
     AND xcb.period_name = 'Feb-25') AS pcargado,
  a.DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN a.LOOKUP_CODE LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(c.DISPLAY_SEQUENCE) 
        FROM conso c 
        WHERE c.LOOKUP_CODE = SUBSTR(RTRIM(a.LOOKUP_CODE, '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM basic a
ORDER BY 14, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T15:53:26.065603
WITH 
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
),
jerarquia AS (
  SELECT 
    LOOKUP_CODE,
    SUM(saldo) AS saldo
  FROM basic
  GROUP BY LOOKUP_CODE
)
SELECT 
  RTRIM(a.LOOKUP_CODE, '.') AS LOOKUP_CODE,
  a.ACCOUNT_TYPE,
  a.DESCRIPTION,
  a.NIVEL,
  a.PARENT,
  a.CGD,
  a.CCD,
  a.cc,
  a.csa,
  a.sa,
  -- único saldo jerárquico
  (SELECT SUM(j.saldo)
   FROM jerarquia j
   WHERE j.LOOKUP_CODE LIKE a.LOOKUP_CODE || '%') AS saldo,
  -- presupuesto
  (SELECT -SUM(xcb.funds_available_amount)
   FROM xcc_balances xcb
   JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
   JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
   WHERE xcb.control_budget_id = '300000003988192'
     AND xbc.segment_value2 = a.CGD
     AND xbc.segment_value3 = a.CCD
     AND xcb.period_name = 'Feb-25') AS pcargado,
  a.DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN a.LOOKUP_CODE LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(c.DISPLAY_SEQUENCE) 
        FROM conso c 
        WHERE c.LOOKUP_CODE = SUBSTR(RTRIM(a.LOOKUP_CODE, '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM basic a
ORDER BY 14, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T15:51:43.117383
WITH 
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
)
SELECT 
  RTRIM(LOOKUP_CODE, '.') AS LOOKUP_CODE,
  ACCOUNT_TYPE,
  DESCRIPTION,
  NIVEL,
  PARENT,
  CGD,
  CCD,
  cc,
  csa,
  sa,
  -- tu único saldo: jerárquico
  (SELECT SUM(b.saldo) 
   FROM basic b 
   WHERE b.LOOKUP_CODE LIKE a.LOOKUP_CODE || '%') AS saldo,
  -- presupuesto del mes
  (SELECT -SUM(xcb.funds_available_amount)
   FROM xcc_balances xcb
   JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
   JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
   WHERE xcb.control_budget_id = '300000003988192'
     AND xbc.segment_value2 = a.CGD
     AND xbc.segment_value3 = a.CCD
     AND xcb.period_name = 'Feb-25') AS pcargado,
  DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN LOOKUP_CODE LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(DISPLAY_SEQUENCE) 
        FROM conso b 
        WHERE b.LOOKUP_CODE = SUBSTR(RTRIM(a.LOOKUP_CODE, '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM basic a
ORDER BY 14, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T15:47:11.386323
WITH 
-- Formas de presentación contable (p. ej. A, B, C, D)
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
),
amounts AS (
  SELECT LOOKUP_CODE, 
         LPAD(' ', NIVEL * 3 - 3, '_') || DESCRIPTION AS DESCRIPTION,
         ACCOUNT_TYPE, NIVEL, PARENT, CGD, CCD, cc, csa, sa, saldo,
         CASE WHEN cc IS NULL 
              THEN (SELECT SUM(b.saldo) 
                    FROM basic b 
                    WHERE b.LOOKUP_CODE LIKE a.LOOKUP_CODE || '%') 
              ELSE saldo 
         END AS tsaldo,
         DISPLAY_SEQUENCE
  FROM basic a
)
SELECT 
  RTRIM(LOOKUP_CODE, '.') AS LOOKUP_CODE,
  ACCOUNT_TYPE,
  DESCRIPTION,
  NIVEL,
  PARENT,
  CGD,
  CCD,
  cc,
  csa,
  sa,
  a.saldo AS saldo,
  a.tsaldo AS tsaldo,
  (SELECT -SUM(xcb.funds_available_amount)
   FROM xcc_balances xcb
   JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
   JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
   WHERE xcb.control_budget_id = '300000003988192'
     AND xbc.segment_value2 = a.CGD
     AND xbc.segment_value3 = a.CCD
     AND xcb.period_name = 'Feb-25') AS pcargado,
  DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN LOOKUP_CODE LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(DISPLAY_SEQUENCE) 
        FROM conso b 
        WHERE b.LOOKUP_CODE = SUBSTR(RTRIM(a.LOOKUP_CODE, '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM amounts a
ORDER BY 15, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T15:46:22.890110
WITH 
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
)
SELECT 
  RTRIM(LOOKUP_CODE, '.') AS LOOKUP_CODE,
  ACCOUNT_TYPE,
  DESCRIPTION,
  NIVEL,
  PARENT,
  CGD,
  CCD,
  cc,
  csa,
  sa,
  -- real consolidado jerárquico
  (SELECT SUM(b.saldo) 
   FROM basic b 
   WHERE b.LOOKUP_CODE LIKE a.LOOKUP_CODE || '%') AS tsaldo,
  -- presupuesto
  (SELECT -SUM(xcb.funds_available_amount)
   FROM xcc_balances xcb
   JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
   JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
   WHERE xcb.control_budget_id = '300000003988192'
     AND xbc.segment_value2 = a.CGD
     AND xbc.segment_value3 = a.CCD
     AND xcb.period_name = 'Feb-25') AS pcargado,
  DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN LOOKUP_CODE LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(DISPLAY_SEQUENCE) 
        FROM conso b 
        WHERE b.LOOKUP_CODE = SUBSTR(RTRIM(a.LOOKUP_CODE, '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM basic a
ORDER BY 14, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T15:31:48.622910
WITH 
-- Formas de presentación contable (p. ej. A, B, C, D)
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso
  UNION
  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )
  UNION
  SELECT * FROM no_conso
),
amounts AS (
  SELECT LOOKUP_CODE, 
         LPAD(' ', NIVEL * 3 - 3, '_') || DESCRIPTION AS DESCRIPTION,
         ACCOUNT_TYPE, NIVEL, PARENT, CGD, CCD, cc, csa, sa, saldo,
         CASE WHEN cc IS NULL 
              THEN (SELECT SUM(b.saldo) 
                    FROM basic b 
                    WHERE b.LOOKUP_CODE LIKE a.LOOKUP_CODE || '%') 
              ELSE saldo 
         END AS tsaldo,
         DISPLAY_SEQUENCE
  FROM basic a
)
SELECT 
  RTRIM(LOOKUP_CODE, '.') AS LOOKUP_CODE,
  ACCOUNT_TYPE,
  DESCRIPTION,
  NIVEL,
  PARENT,
  CGD,
  CCD,
  cc,
  csa,
  sa,
  a.saldo AS saldo,
  a.tsaldo AS tsaldo,
  (SELECT -SUM(xcb.funds_available_amount)
   FROM xcc_balances xcb
   JOIN xcc_control_budgets xcc ON xcc.control_budget_id = xcb.control_budget_id
   JOIN xcc_budget_accounts xbc ON xbc.budget_code_combination_id = xcb.budget_ccid
   WHERE xcb.control_budget_id = '300000003988192'
     AND xbc.segment_value2 = a.CGD
     AND xbc.segment_value3 = a.CCD
     AND xcb.period_name = 'Feb-25') AS pcargado,
  DISPLAY_SEQUENCE,
  CAST(
    CASE 
      WHEN LOOKUP_CODE LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT MIN(DISPLAY_SEQUENCE) 
        FROM conso b 
        WHERE b.LOOKUP_CODE = SUBSTR(RTRIM(a.LOOKUP_CODE, '.'), 1, 1)
      ) || '1'
    END AS INTEGER
  ) AS SORT_ORDER
FROM amounts a
ORDER BY 15, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-03T15:24:31.082938
WITH 
-- Formas de presentación contable (p. ej. A, B, C, D)
forms AS (
  SELECT LOOKUP_CODE, MEANING 
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'
),

-- Jerarquía de cuentas de gestión (estructura de consolidación)
conso AS (
  SELECT LOOKUP_CODE, DESCRIPTION, ATTRIBUTE1 AS PARENT, DISPLAY_SEQUENCE, TAG, 
         REGEXP_COUNT(LOOKUP_CODE, '[^.]') AS NIVEL
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'
),

-- Asignación de centro de costo a cuenta de gestión
assig AS (
  SELECT MEANING, ATTRIBUTE1 AS REP, ATTRIBUTE3 AS LOW, ATTRIBUTE2 AS CG, 
         ATTRIBUTE3 || '.' || ATTRIBUTE2 AS seg
  FROM FND_LOOKUP_VALUES_VL 
  WHERE LOOKUP_TYPE = 'UAI_CONS_GESTION_ASIGNACION'
),

-- Cruce para obtener subárea desde centro de costo
cc_attrs AS (
  SELECT FLEX_VALUE, ATTRIBUTE4, ATTRIBUTE12
  FROM fnd_flex_values_vl 
  WHERE VALUE_CATEGORY = 'Centro de Costo UAI'
),

-- Asientos contables (solo Feb-25, excluye 'STAT')
asientos AS (
  SELECT 
         gld.name,
         gjl.period_name,
         ACCOUNT_TYPE,
         gcc.segment4,
         gcc.segment3,
         fcc.ATTRIBUTE4 AS CSA,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Cuenta de Gestion UAI' 
            AND FLEX_VALUE = gcc.segment3) AS CG,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Centro de Costo UAI' 
            AND FLEX_VALUE = gcc.segment4) AS CC,
         (SELECT DESCRIPTION FROM fnd_flex_values_vl 
          WHERE VALUE_CATEGORY = 'Subarea' 
            AND FLEX_VALUE = fcc.ATTRIBUTE4) AS SA,
         NVL(gjl.accounted_cr, 0) - NVL(gjl.accounted_dr, 0) AS accounted
  FROM gl_je_headers gjh
  JOIN gl_je_lines gjl ON gjh.je_header_id = gjl.je_header_id
  JOIN gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
  JOIN gl_ledgers gld ON gjh.ledger_id = gld.ledger_id
  JOIN gl_je_sources gjs ON gjh.je_source = gjs.je_source_name
  JOIN gl_periods pc ON gld.period_set_name = pc.period_set_name 
                    AND gjl.period_name = pc.period_name
  JOIN cc_attrs fcc ON gcc.segment4 = fcc.FLEX_VALUE
  LEFT JOIN assig a ON a.REP = fcc.ATTRIBUTE12 AND a.CG = gcc.segment3
  WHERE gjl.status = 'P'
    AND gjl.currency_code <> 'STAT'
    AND gjl.period_name = 'Feb-25'
    AND gjh.NAME = 'CARGA INICIAL Carga Inicial'
),

-- Agrupación por CG + CC + SA
lineas AS (
  SELECT name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA, 
         SUM(accounted) AS accounted
  FROM asientos
  GROUP BY name, ACCOUNT_TYPE, segment4, segment3, CG, CC, CSA, SA
),

-- Combinaciones no consolidadas
no_conso AS (
  SELECT 
    'NO_CONSO.' || segment3 AS LOOKUP_CODE,
    MAX(ACCOUNT_TYPE) AS ACCOUNT_TYPE,
    'SIN CONSOLIDACIÓN – ' || MAX(CG) AS DESCRIPTION,
    0 AS NIVEL,
    NULL AS PARENT,
    segment3 AS CGD,
    segment4 AS CCD,
    MAX(CC) AS CC,
    MAX(CSA) AS CSA,
    MAX(SA) AS SA,
    SUM(accounted) AS saldo,
    -1 AS DISPLAY_SEQUENCE
  FROM lineas ln
  WHERE NOT EXISTS (
    SELECT 1
    FROM assig a
    WHERE a.CG = ln.segment3
      AND a.REP IN (
        SELECT ATTRIBUTE12 
        FROM cc_attrs 
        WHERE FLEX_VALUE = ln.segment4
      )
  )
  GROUP BY segment3, segment4
),

-- Mezcla de nodos + hojas + no_conso
basic AS (
  SELECT 
    LOOKUP_CODE || '.' AS LOOKUP_CODE, 
    '' AS ACCOUNT_TYPE, 
    DESCRIPTION, 
    NIVEL, 
    PARENT, 
    '' AS CGD, '' AS CCD, '' AS CC, '' AS CSA, '' AS SA, 
    TO_NUMBER(NULL) AS saldo, 
    DISPLAY_SEQUENCE
  FROM conso

  UNION

  SELECT 
    seg || '.' AS LOOKUP_CODE, 
    ACCOUNT_TYPE, 
    ln.CG AS DESCRIPTION, 
    4 AS NIVEL, 
    LOW AS PARENT, 
    segment3 AS CGD, 
    segment4 AS CCD, 
    ln.CC, 
    ln.CSA, 
    ln.SA, 
    accounted AS saldo, 
    NULL AS DISPLAY_SEQUENCE
  FROM assig asg
  JOIN lineas ln ON asg.CG = ln.segment3
  WHERE asg.REP IN (
    SELECT ATTRIBUTE12 FROM cc_attrs WHERE FLEX_VALUE = ln.segment4
  )

  UNION

  SELECT * FROM no_conso
),

-- Cálculo del total consolidado por nodo
amounts AS (
  SELECT LOOKUP_CODE, 
         LPAD(' ', NIVEL * 3 - 3, '_') || DESCRIPTION AS DESCRIPTION,
         ACCOUNT_TYPE, NIVEL, PARENT, CGD, CCD, cc, csa, sa, saldo,
         CASE WHEN cc IS NULL 
              THEN (SELECT SUM(b.saldo) 
                    FROM basic b 
                    WHERE b.LOOKUP_CODE LIKE a.LOOKUP_CODE || '%') 
              ELSE saldo 
         END AS tsaldo,
         DISPLAY_SEQUENCE
  FROM basic a
)

-- Resultado final ordenado
SELECT 
  CASE WHEN LOOKUP_CODE IN ('A.','B.','C.','D.') 
       THEN NULL 
       ELSE RTRIM(LOOKUP_CODE, '.') 
  END AS LOOKUP_CODE,

  ACCOUNT_TYPE,
  DESCRIPTION,
  NIVEL,
  PARENT,
  CGD,
  CCD,
  cc,
  csa,
  sa,
  a.saldo AS saldo,

  -- Signo y cálculo consolidado
  CASE 
    WHEN LOOKUP_CODE LIKE '1.%' OR LOOKUP_CODE = 'A.' THEN 1 
    ELSE 1 
  END *

  CASE 
    WHEN LOOKUP_CODE IN ('A.','B.','C.') 
      THEN (SELECT SUM(NVL(b.tsaldo, 0)) 
            FROM amounts b 
            WHERE b.PARENT || '.' LIKE a.LOOKUP_CODE)
    WHEN LOOKUP_CODE = 'D.' 
      THEN (SELECT SUM(NVL(b.tsaldo, 0)) 
            FROM amounts b 
            WHERE NIVEL = 1)
    ELSE a.tsaldo
  END AS tsaldo,

  -- Presupuesto
  (SELECT 
      -SUM(xcb.funds_available_amount)
    FROM xcc_balances xcb
    JOIN xcc_control_budgets xcc 
      ON xcc.control_budget_id = xcb.control_budget_id
    JOIN xcc_budget_accounts xbc 
      ON xbc.budget_code_combination_id = xcb.budget_ccid
    WHERE xcb.control_budget_id = '300000003988192'
      AND xbc.segment_value2 = CGD
      AND xbc.segment_value3 = CCD
      AND xcb.period_name = 'Feb-25') AS pcargado,

  DISPLAY_SEQUENCE,

  -- Orden personalizado para Power BI
  CAST(
    CASE 
      WHEN LOOKUP_CODE LIKE 'NO_CONSO%' THEN '0'
      ELSE (
        SELECT DISTINCT DISPLAY_SEQUENCE 
        FROM conso b 
        WHERE b.LOOKUP_CODE = SUBSTR(RTRIM(a.LOOKUP_CODE, '.'), 1, 1)
      ) || 
      CASE WHEN a.LOOKUP_CODE IN ('A.','B.','C.','D.') 
           THEN '0' 
           ELSE '1' 
      END
    END AS INTEGER
  ) AS SORT_ORDER

FROM amounts a
ORDER BY 15, 1
FETCH FIRST 1000 ROWS ONLY

-- Consulta registrada el 2025-07-02T21:59:46.188851
SELECT 
    xcc.name as nombre_control_presupuesto,              -- Tabla XCC_CONTROL_BUDGETS
    xcc.description as descripcion_control_presupuesto,  -- Tabla XCC_CONTROL_BUDGETS
    xcb.control_budget_id,                               -- Tabla XCC_BALANCES
    xcb.budget_ccid,                                     -- Tabla XCC_BALANCES
    xcb.period_name,                                     -- Tabla XCC_BALANCES
    xcb.funds_available_amount,                          -- Tabla XCC_BALANCES
    xcb.last_update_date AS balance_last_update_date    -- Tabla XCC_BALANCES
 
FROM xcc_balances xcb
INNER JOIN xcc_control_budgets xcc 
  ON xcc.control_budget_id = xcb.control_budget_id
 
WHERE xcc.control_budget_id in 
('300000003694565') --Presupuesto
ORDER BY xcb.control_budget_id

-- Consulta registrada el 2025-07-02T21:59:27.207835
SELECT 
    xcc.name as nombre_control_presupuesto,              -- Tabla XCC_CONTROL_BUDGETS
    xcc.description as descripcion_control_presupuesto,  -- Tabla XCC_CONTROL_BUDGETS
    xcb.control_budget_id,                               -- Tabla XCC_BALANCES
    xcb.budget_ccid,                                     -- Tabla XCC_BALANCES
    xcb.period_name,                                     -- Tabla XCC_BALANCES
    xcb.funds_available_amount,                          -- Tabla XCC_BALANCES
    xcb.last_update_date AS balance_last_update_date    -- Tabla XCC_BALANCES
 
FROM xcc_balances xcb
INNER JOIN xcc_control_budgets xcc 
  ON xcc.control_budget_id = xcb.control_budget_id
 
WHERE xcc.control_budget_id in 
('300000003694565') --Presupuesto
ORDER BY xcb.control_budget_id
FETCH FIRST 999 ROWS ONLY