SQL to pull Hierarchy from F0901

[chanley54]

Hi,

Am sure this question has been asked quite a few times on this forum and yet I am unable see anything directly related. Am new to JDE and have been tasked with replacing a DLL (no source code) that spits out a text file of the Hierarchy.

The below code gets me very close to the hierarchy but does not quite match...any suggestions?

INSERT INTO #Temp_Universe_1
SELECT DISTINCT
MAX(B.GMOBJ + '.' + B.GMSUB) AS PARENT,
(A.GMOBJ + '.' + A.GMSUB) AS CHILD,
A.GMDL01 AS CHILDALIAS,
A.GMPEC AS POSTING_EDIT,
A.GMR001 AS DEM_ENE,
A.GMR002 AS GEN_UNIT,
A.GMR004 AS PURCH_PWR,
A.GMR008 AS ATTR_ACCT_TYPE,
A.GMLDA AS GEN,
CASE WHEN A.GMPEC = 'N' THEN NULL ELSE A.GMR021 END AS FERC
FROM
F0901 A
INNER JOIN
F0901 B ON
A.GMLDA > B.GMLDA AND
A.GMCO = A.GMCO AND
A.GMMCU = B.GMMCU AND
(A.GMOBJ + A.GMSUB) >= (B.GMOBJ + B.GMSUB)
WHERE
RTRIM(LTRIM(A.GMMCU)) <> 'MD'
AND A.GMLDA = 4
AND A.GMCO IN ('00001','00060','80001','80060','90160')
AND (RTRIM(LTRIM(B.GMOBJ) + '.' + RTRIM(LTRIM(B.GMSUB)) NOT IN (SELECT Parent FROM #Temp_Universe_2)
GROUP BY
A.GMOBJ + '.' + A.GMSUB,A.GMDL01, A.GMPEC, A.GMR001, A.GMR002, A.GMR004, A.GMR008, A.GMLDA,
CASE WHEN A.GMPEC = 'N' THEN NULL ELSE A.GMR021 END
ORDER BY 1,2;



INSERT INTO #Temp_Universe_2 SELECT * FROM #Temp_Universe_1;

TRUNCATE TABLE #Temp_Universe_1;