【Oracle 组织树】

Oracle 组织树嵌套使用，有多个site厂，取苏华新site下的几个部门

CONNECT BY
ORG_PARENT_CODE = PRIOR ORG_CODE
START WITH
ORG_NAME = ‘苏华新site’

SELECT
	LEVEL AS SPC_ORG_LEVEL,
	ORG_NAME,
	ORG_CODE,
	CASE
		WHEN LEVEL = 1 THEN '1'
		ELSE ORG_PARENT_CODE
	END ORG_PARENT_CODE ,
	STATUS,
	'SP' AS LAST_EVENT_USER,
	LAST_EVENT_TIME
FROM
	(
		SELECT t.*
	FROM
		B_4A_ORG_INFO t
	CONNECT BY
		ORG_PARENT_CODE = PRIOR ORG_CODE
	START WITH
		ORG_NAME = '苏华新site') a
CONNECT BY
	PRIOR ORG_CODE = ORG_PARENT_CODE
START WITH
	ORG_NAME IN ('面厂',
	'整厂',
	'品处');