记录根据BOM子节点反查BOM根节点的一段SQL

WITH BOM_TREE AS (SELECT PECODE, CONNECT_BY_ISLEAF AS IS_LEAF, CONNECT_BY_ROOT(LID) AS LID FROM PSEBOMLINE START WITH 1=1 AND CHANGEDODDNUM='[CHANGEDODDNUM]' CONNECT BY PRIOR PECODE = CECODE) SELECT AA.* FROM ( SELECT 'PSEBOMLINE_'|| SYS_GUID() AS EID, B.WORKCENTER, B.WORKCENTERNAME, B.EID AS ROUTEID, D.LID, D.PECODE, D.PENAME, D.CECODE, D.CENAME, D.LOCATIONNUM, D.LQUALITY, D.VPPS, D.STGROUPSCODE, D.FULOCODE, D.AUTOADDNO, D.ROOTCODE FROM PRODUCTROUTEMAIN B LEFT JOIN (SELECT A.ROOTCODE, C.* FROM PSEBOMLINE C LEFT JOIN (SELECT DISTINCT PECODE AS ROOTCODE, LID FROM BOM_TREE WHERE IS_LEAF = 1) A ON C.LID = A.LID WHERE 1=1 AND CHANGEDODDNUM='[CHANGEDODDNUM]') D ON B.PRODUCTCODE = D.ROOTCODE WHERE B.ROUTERTYPE = '0' AND B.STATE = '1' AND B.PRODUCTCODE IN (SELECT DISTINCT PECODE FROM BOM_TREE WHERE IS_LEAF = 1) ) AA