T100 开发笔记-210928

SELECT
ROWNUM,
bmbaent 企业,
bmbasite 据点,
connect_by_root(bmaa001) root,
bmaa001 主件物料,
bmba003 元件物料,
bmba005 创建时间,
bmba006 失效时间,
bmba009 项次,
bmba011 组成用量,
bmba012 主件底数,
LEVEL level1,
sys_connect_by_path(LEVEL, '∠') level2
FROM
bmaa_t
INNER JOIN bmba_t ON
bmbaent = bmaaent
AND bmbasite = bmaasite
AND bmba001 = bmaa001
AND bmba002 = bmaa002

WHERE  bmba005 <= SYSDATE  --创建日期不能大于当天
    AND (bmba006 > SYSDATE
        OR bmba006 IS NULL )  --失效日期不能空或小于当天
START WITH
bmaaent = 100
AND bmaasite = 'ALL' --据点
AND bmaa001 IN (SELECT DISTINCT bmba001 FROM bmba_t WHERE bmba001 NOT in
(SELECT bmba003 FROM bmba_t))  --所有没有上层的BOM为根BOM
AND bmaastus = 'Y'
CONNECT BY
bmaa001 = PRIOR bmba003
AND bmbaent = 100
AND bmbasite = 'ALL'
ORDER BY
ROWNUM