I am stuck on how to sum by levels, I have the following query:
SELECT
TREE.*,
FVVVL.DESCRIPTION,
--SUM(VALUES_GL.TOTAL)
VALUES_GL.TOTAL
FROM (
SELECT
PK1_START_VALUE,
PARENT_PK1_VALUE,
CONNECT_BY_ISCYCLE "Cycle",
LEVEL,
SYS_CONNECT_BY_PATH(PK1_START_VALUE, '/') "Path"
FROM
FND_TREE_NODE
WHERE TREE_CODE = 'CGM_ESF'
START WITH PK1_START_VALUE = 'ESF_A'
CONNECT BY NOCYCLE PRIOR PK1_START_VALUE = PARENT_PK1_VALUE AND LEVEL <= 5
--ORDER SIBLINGS BY PK1_START_VALUE
) TREE
INNER JOIN FND_VS_VALUES_VL FVVVL ON FVVVL.VALUE = TREE.PK1_START_VALUE
LEFT JOIN (
SELECT
NVL(GLL.ACCOUNTED_DR, GLL.ACCOUNTED_CR * -1 ) AS TOTAL,
GLL.PERIOD_NAME, TO_CHAR(GLL.EFFECTIVE_DATE, 'DD-MM-YYYY'),
GLL.CODE_COMBINATION_ID,
GLL.LEDGER_ID,
GCC.SEGMENT2
FROM GL_JE_LINES GLL
INNER JOIN GL_CODE_COMBINATIONS GCC ON GLL.CODE_COMBINATION_ID = GLL.CODE_COMBINATION_ID
) VALUES_GL ON VALUES_GL.SEGMENT2 = TREE.PK1_START_VALUE --AND TREE.LEVEL = 5
ORDER BY "Path"
Explanation of what I did in excel: Note: At the moment there are only 5 levels then that increases, but it would be like this:
- Level 4 should show the total of the sum of level 5.
- Level 3 should show the total of the sum of level 4.
- Level 2 must show the total of the sum of level 3
- Level 1 should show the total of the sum of level 2.
At the moment I have empty values:
Oracle FND_TREE_NODE table documentation: https://docs.oracle.com/en/cloud/saas/applications-common/21d/oedma/fndtreenode-22816.html
Thank you.
CodePudding user response：
MODEL clause example on simulated dataset derived from upper picture. It looks ok. Maybe you can try to adjust it to your data.
WITH
dataset AS -- dataset simulation
(
Select '13050501' "PK1", Null "PRNT", '0' "CCL", '1' "LVL", 100 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", Null "PRNT", '0' "CCL", '1' "LVL", 110 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", Null "PRNT", '0' "CCL", '1' "LVL", 120 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", 'ESF_A' "PRNT", '0' "CCL", '2' "LVL", 200 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", 'ESF_A' "PRNT", '0' "CCL", '2' "LVL", 210 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", 'ESF_ACT' "PRNT", '0' "CCL", '3' "LVL", 300 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", 'ESF_ACT' "PRNT", '0' "CCL", '3' "LVL", 310 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", 'ESF_ACT' "PRNT", '0' "CCL", '3' "LVL", 320 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", 'ESF_AC' "PRNT", '0' "CCL", '4' "LVL", 400 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", 'ESF_AC' "PRNT", '0' "CCL", '4' "LVL", 410 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", 'ESF_ADCC' "PRNT", '0' "CCL", '5' "LVL", 500 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", 'ESF_ADCC' "PRNT", '0' "CCL", '5' "LVL", 510 "AMNT" From DUAL UNION ALL
Select '13050501' "PK1", 'ESF_ADCC' "PRNT", '0' "CCL", '5' "LVL", 520 "AMNT" From DUAL
),
level_sums AS
(
SELECT LVL "LVL", Sum(AMNT) "TOTAL", Count(*) "RECS"
FROM dataset
GROUP BY LVL
ORDER BY LVL
),
all_data AS
(
SELECT
ds.LVL || '_' || Count(ds.LVL) OVER(PARTITION BY ds.LVL ORDER BY ds.LVL, ds.PK1, ds.PRNT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "ID",
Count(ds.LVL) OVER(PARTITION BY ds.LVL ORDER BY ds.LVL, ds.PK1, ds.PRNT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "RN",
CAST('/FULL/PATH/FROM/DATASET' as VARCHAR2(50)) "PATH",
ds.PK1,
ds.PRNT,
ds.CCL,
ds.LVL,
ds.AMNT,
ls.TOTAL
FROM
dataset ds
INNER JOIN
level_sums ls ON(ls.LVL = ds.LVL)
ORDER BY ds.LVL, ds.PK1, ds.PRNT
)
SELECT
PK1 "PK1_START_VALUE",
PRNT "PARENT_PK1_VALUE",
CCL "CYCLE",
LVL "LEVEL",
REPLACE('/' || REPLACE(PATH, '//', ''), '//', '/') "PATH",
CASE WHEN SubStr(ID, 1, 1) = '0' THEN TOTAL ELSE AMNT END "TOTAL",
CASE WHEN SubStr(ID, 1, 1) = '0' THEN 'SUM of level ' || To_Char(LVL - 1) ELSE 'Amount from dataset' END "NOTE"
FROM
( SELECT ID, LVL, PK1, PRNT, CCL, PATH, AMNT, TOTAL FROM all_data ) -- Modeling happens here
MODEL
DIMENSION BY (ID)
MEASURES (LVL, PATH, PK1, PRNT, CCL, AMNT, TOTAL)
RULES ITERATE(5)
(
PK1['0' || '_' || To_Char(ITERATION_NUMBER 1)] = Nvl(PRNT[To_Char(ITERATION_NUMBER 2) || '_' || '1'], PK1[To_Char(ITERATION_NUMBER 1) || '_' || '1']),
PRNT['0' || '_' || To_Char(ITERATION_NUMBER 1)] = PRNT[To_Char(ITERATION_NUMBER 1) || '_' || '1'],
CCL['0' || '_' || To_Char(ITERATION_NUMBER 1)] = CCL[To_Char(ITERATION_NUMBER 1) || '_' || '1'],
LVL['0' || '_' || To_Char(ITERATION_NUMBER 1)] = LVL[To_Char(ITERATION_NUMBER 1) || '_' || '1'],
PATH['0' || '_' || To_Char(ITERATION_NUMBER 1)] = PRNT[To_Char(ITERATION_NUMBER 0) || '_' || '1'] || '/' ||
PRNT[To_Char(ITERATION_NUMBER 1) || '_' || '1'] || '/' ||
CASE To_Char(ITERATION_NUMBER 2)
WHEN '6'
THEN PK1[To_Char(ITERATION_NUMBER 1) || '_' || '1']
ELSE PRNT[To_Char(ITERATION_NUMBER 2) || '_' || '1']
END,
TOTAL['0' || '_' || To_Char(ITERATION_NUMBER 1)] = TOTAL[To_Char(ITERATION_NUMBER) || '_' || '1']
)
ORDER BY
CASE WHEN SubStr(ID, 1, 1) = '0' THEN '99' || To_Char(LVL) ELSE To_Char(9990 - LVL) END
--
-- R e s u l t
--
-- PK1_START_VALUE PARENT_PK1_VALUE CYCLE LEVEL PATH TOTAL NOTE
-- --------------- ---------------- ----- ----- --------------------------------------------------- ---------- -----------------------------------------------------
-- ESF_A 0 1 /ESF_A SUM of level 0
-- ESF_ACT ESF_A 0 2 /ESF_A/ESF_ACT 330 SUM of level 1
-- ESF_AC ESF_ACT 0 3 /ESF_A/ESF_ACT/ESF_AC 410 SUM of level 2
-- ESF_ADCC ESF_AC 0 4 /ESF_ACT/ESF_AC/ESF_ADCC 930 SUM of level 3
-- 13050501 ESF_ADCC 0 5 /ESF_AC/ESF_ADCC/13050501 810 SUM of level 4
-- 13050501 ESF_ADCC 0 5 /FULL/PATH/FROM/DATASET 520 Amount from dataset
-- 13050501 ESF_ADCC 0 5 /FULL/PATH/FROM/DATASET 510 Amount from dataset
-- 13050501 ESF_ADCC 0 5 /FULL/PATH/FROM/DATASET 500 Amount from dataset
-- 13050501 ESF_AC 0 4 /FULL/PATH/FROM/DATASET 400 Amount from dataset
-- 13050501 ESF_AC 0 4 /FULL/PATH/FROM/DATASET 410 Amount from dataset
-- 13050501 ESF_ACT 0 3 /FULL/PATH/FROM/DATASET 310 Amount from dataset
-- 13050501 ESF_ACT 0 3 /FULL/PATH/FROM/DATASET 300 Amount from dataset
-- 13050501 ESF_ACT 0 3 /FULL/PATH/FROM/DATASET 320 Amount from dataset
-- 13050501 ESF_A 0 2 /FULL/PATH/FROM/DATASET 210 Amount from dataset
-- 13050501 ESF_A 0 2 /FULL/PATH/FROM/DATASET 200 Amount from dataset
-- 13050501 0 1 /FULL/PATH/FROM/DATASET 120 Amount from dataset
-- 13050501 0 1 /FULL/PATH/FROM/DATASET 110 Amount from dataset
-- 13050501 0 1 /FULL/PATH/FROM/DATASET 100 Amount from dataset