XinYu_RZMES/.svn/pristine/92/923d1a51d682e926a452b2185188d4a60c1a895c.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="选择菜单">

	<query id="menu.Menu.Select" desc="获取菜单" fetchSize="10">
		<![CDATA[
			SELECT LEVEL, MNSCR_ID, MNSCR_NM FROM TBZ01_MNSCR
			WHERE MNSCR_TP = 'M' AND UPCHECK_YN = 'Y'
			START WITH P_MNSCR_ID = '*'
			CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID
			ORDER SIBLINGS BY MNSCR_DSP_SEQ
		]]>
	</query> 

	<query id="menu.User.Menu.Select" desc="获取用户菜单" fetchSize="10">
		<![CDATA[
			SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			WHERE MNSCR_TP = 'M' AND UPCHECK_YN = 'Y'
			START WITH MNSCR_ID IN (
				SELECT DISTINCT P.MNSCR_ID
				FROM TBZ01_USERPRIV P INNER JOIN TBZ01_USER U ON (P.USER_CD = U.USER_CD)
				WHERE P.USER_CD IN (?, ?)
				  AND NVL(U.LOCKED_YN, 'N') = 'N')		-- 参数：用户、角色代码
			CONNECT BY PRIOR P_MNSCR_ID = MNSCR_ID		-- 查找父菜单
			UNION
			SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			WHERE MNSCR_TP = 'M' AND UPCHECK_YN = 'Y'
			START WITH P_MNSCR_ID IN (
				SELECT DISTINCT P.MNSCR_ID
				FROM TBZ01_USERPRIV P INNER JOIN TBZ01_USER U ON (P.USER_CD = U.USER_CD)
				WHERE P.USER_CD IN (?, ?)
				  AND NVL(U.LOCKED_YN, 'N') = 'N')		-- 参数：用户、角色代码
			CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID		-- 查找子菜单
		]]>
	</query> 

	<query id="menu.Dept.Menu.Select" desc="获取部门菜单" fetchSize="10">
		<![CDATA[
			SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			WHERE MNSCR_TP = 'M' AND UPCHECK_YN = 'Y'
			START WITH MNSCR_ID IN (
				SELECT DISTINCT MNSCR_ID FROM TBZ01_DEPTPRIV
				WHERE DT_CD IN (
					SELECT DEPT_CD FROM TBZ01_DEPT
					START WITH DEPT_CD = ?				-- 参数：部门代码
					CONNECT BY PRIOR DEPT_CD = P_DEPT_CD))
			CONNECT BY PRIOR P_MNSCR_ID = MNSCR_ID		-- 查找父菜单
			UNION
			SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			WHERE MNSCR_TP = 'M' AND UPCHECK_YN = 'Y'
			START WITH P_MNSCR_ID IN (
				SELECT DISTINCT MNSCR_ID FROM TBZ01_DEPTPRIV
				WHERE DT_CD IN (
					SELECT DEPT_CD FROM TBZ01_DEPT
					START WITH DEPT_CD = ?				-- 参数：部门代码
					CONNECT BY PRIOR DEPT_CD = P_DEPT_CD))
			CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID		-- 查找子菜单
		]]>
	</query> 

	<query id="menu.User.Scr.Select" desc="获取用户界面" fetchSize="10">
		<![CDATA[
			SELECT LEVEL, MNSCR_ID, MNSCR_NM, MNSCR_URL FROM TBZ01_MNSCR
			WHERE UPCHECK_YN = 'Y'
			  AND (MNSCR_TP = 'M' OR MNSCR_ID IN (
			  	SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			  	WHERE MNSCR_TP = 'S'
			  	START WITH MNSCR_ID IN (
					SELECT DISTINCT MNSCR_ID FROM TBZ01_USERPRIV
					WHERE USER_CD IN (?, ?))			-- 参数：用户、角色代码
				CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID))
			START WITH MNSCR_ID = ?						-- 参数：上级菜单(E01)
			CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID
			ORDER SIBLINGS BY MNSCR_DSP_SEQ
		]]>
	</query> 

	<query id="menu.Dept.Scr.Select" desc="获取部门界面" fetchSize="10">
		<![CDATA[
			SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			WHERE UPCHECK_YN = 'Y'
			  AND MNSCR_ID IN (
			  	SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
			  	WHERE MNSCR_TP = 'S'
			  	START WITH MNSCR_ID IN (
					SELECT DISTINCT MNSCR_ID FROM TBZ01_DEPTPRIV
					WHERE DT_CD IN (
						SELECT DEPT_CD FROM TBZ01_DEPT
						START WITH DEPT_CD = ?			-- 参数：部门代码
						CONNECT BY PRIOR DEPT_CD = P_DEPT_CD))
				CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID)
			START WITH MNSCR_ID = ?						-- 参数：上级菜单(E01)
			CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID
		]]>
	</query> 

	<query id="menu_01.select" desc="获取菜单(角色 and 部门 or 用户)" fetchSize="10">
		<![CDATA[
			SELECT LEVEL,
				MNSCR_ID,	-- 菜单/画面 ID
				MNSCR_NM	-- 菜单/画面名
			FROM TBZ01_MNSCR
			WHERE UPCHECK_YN = 'Y' AND MNSCR_ID IN (
				SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
				WHERE MNSCR_TP = 'M'
				  AND ('Y' = ? OR MNSCR_ID IN (						-- 参数：最高管理
					SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
					START WITH MNSCR_ID IN (
						SELECT DISTINCT P_MNSCR_ID FROM TBZ01_MNSCR
						START WITH MNSCR_ID IN (
							SELECT DISTINCT MNSCR_ID FROM TBZ01_DEPTPRIV
							WHERE DT_CD IN (
								SELECT DEPT_CD FROM TBZ01_DEPT
								START WITH DEPT_CD = ?				--  参数：部门代码
								CONNECT BY PRIOR DEPT_CD = P_DEPT_CD))
						CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID)		-- 查找子菜单
					CONNECT BY PRIOR P_MNSCR_ID = MNSCR_ID))		-- 查找父菜单
				START WITH MNSCR_ID IN (
					SELECT MNSCR_ID FROM TBZ01_USERPRIV
					WHERE USER_CD IN (?, ?))						-- 参数：用户、角色代码
				CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID)
			START WITH P_MNSCR_ID = '*'
			CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID											-- 使菜单正常显示
			ORDER SIBLINGS BY MNSCR_DSP_SEQ
		]]>
	</query> 

	<query id="menu_02.select" desc="获取界面(角色 and 部门 or 用户)" fetchSize="10">
		<![CDATA[
			SELECT LEVEL,
				MNSCR_ID,	-- 菜单/画面 ID
				MNSCR_NM,	-- 菜单/画面名
				MNSCR_URL	-- 菜单/画面URL路径
			FROM TBZ01_MNSCR
			WHERE UPCHECK_YN = 'Y'															-- 菜单/界面必须有效
			  AND (MNSCR_TP = 'M' OR MNSCR_ID IN (
				SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
				WHERE 'Y' = ? OR MNSCR_ID IN (						-- 参数：最高管理
					SELECT DISTINCT MNSCR_ID FROM TBZ01_MNSCR
					WHERE MNSCR_TP = 'S'
					START WITH MNSCR_ID IN (
						SELECT DISTINCT MNSCR_ID FROM TBZ01_DEPTPRIV
						WHERE DT_CD IN (
							SELECT DEPT_CD FROM TBZ01_DEPT
							START WITH DEPT_CD = ?					--  参数：部门代码
							CONNECT BY PRIOR DEPT_CD = P_DEPT_CD))	-- 子部门
					CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID)			-- 子界面
				START WITH MNSCR_ID IN (
					SELECT MNSCR_ID FROM TBZ01_USERPRIV
					WHERE USER_CD IN (?, ?))						-- 参数：用户、角色代码
				CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID))
			START WITH MNSCR_ID = ?									-- 参数：菜单代码 (如：E01)
			CONNECT BY PRIOR MNSCR_ID = P_MNSCR_ID
			ORDER SIBLINGS BY MNSCR_DSP_SEQ
		]]>
	</query> 

</queryMap>