SELECT		'STORE' AS SETID,
		D.TREE_NODE AS DEPTID,
		A.TREE_NODE AS DEPTID_FILTR,
		J.DESCR AS MS_RPT_TREE_DEPT,
		H.DESCR AS MS_RPT_TREE_PRNT,
		I.DESCR AS MS_RPT_TREE_GRND,
		D.TREE_LEVEL_NUM AS 'CHILD_LEVEL',
		F.TREE_NODE AS 'REPORTS_TO_DEPT',
		A.TREE_LEVEL_NUM AS 'PARENT_LEVEL_NUM' 
FROM		PSTREENODE A
INNER JOIN	PSTREENODE D ON D.SETCNTRLVALUE = A.SETCNTRLVALUE AND D.TREE_NAME = A.TREE_NAME AND D.SETID = A.SETID AND D.EFFDT = A.EFFDT
INNER JOIN	PSTREENODE F ON F.SETCNTRLVALUE = A.SETCNTRLVALUE AND F.TREE_NAME = D.TREE_NAME AND F.SETID = D.SETID AND F.EFFDT = D.EFFDT AND F.TREE_NODE_NUM = D.PARENT_NODE_NUM
INNER JOIN	PSTREENODE G ON G.SETCNTRLVALUE = A.SETCNTRLVALUE AND G.TREE_NAME = F.TREE_NAME AND G.SETID = F.SETID AND G.EFFDT = F.EFFDT AND G.TREE_NODE_NUM = F.PARENT_NODE_NUM
INNER JOIN	PS_DEPT_TBL H ON H.DEPTID = F.TREE_NODE AND H.SETID = A.SETID
INNER JOIN	PS_DEPT_TBL I ON I.SETID = A.SETID AND I.DEPTID = G.TREE_NODE
INNER JOIN	PS_DEPT_TBL J ON J.DEPTID = D.TREE_NODE AND J.SETID = A.SETID 
WHERE		A.TREE_NAME = 'DEPT_SECURITY' 
		AND A.SETID = 'STORE' 
		AND A.SETCNTRLVALUE = ' ' 
		AND D.TREE_LEVEL_NUM BETWEEN 3 AND 6 
		AND D.TREE_NODE_NUM BETWEEN	(	
							SELECT	B.TREE_NODE_NUM 
							FROM	PSTREENODE B 
							WHERE	B.TREE_NODE = A.TREE_NODE 
								AND B.TREE_NAME = A.TREE_NAME 
								AND B.EFFDT = A.EFFDT 
								AND B.SETID = A.SETID
						) AND
						(
							SELECT	C.TREE_NODE_NUM_END 
							FROM	PSTREENODE C 
							WHERE	C.TREE_NODE = A.TREE_NODE 
								AND C.TREE_NAME = A.TREE_NAME 
								AND C.EFFDT = A.EFFDT 
								AND C.SETID = A.SETID
						) 
		AND A.EFFDT = 	(	SELECT	MAX(TD.EFFDT) 
					FROM	PSTREEDEFN TD 
					WHERE	TD.SETID = A.SETID 
						AND TD.TREE_NAME = A.TREE_NAME 
						AND TD.EFFDT <= CONVERT(CHAR(10), GETDATE(), 110)
				) 
		AND H.EFFDT = 	(
					SELECT	MAX(C_EFFDT.EFFDT) 
					FROM	PS_DEPT_TBL C_EFFDT 
					WHERE	C_EFFDT.SETID = H.SETID 
						AND C_EFFDT.DEPTID = H.DEPTID 
						AND C_EFFDT.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE()), 1, 11)
				) 
		AND I.EFFDT = 	(
					SELECT	MAX(C_EFFDT.EFFDT) 
					FROM	PS_DEPT_TBL C_EFFDT 
					WHERE	C_EFFDT.SETID = I.SETID 
						AND C_EFFDT.DEPTID = I.DEPTID 
						AND C_EFFDT.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE()), 1, 11)
				) 
		AND J.EFFDT = 	(
					SELECT	MAX(C_EFFDT.EFFDT) 
					FROM	PS_DEPT_TBL C_EFFDT 
					WHERE	C_EFFDT.SETID = J.SETID 
						AND C_EFFDT.DEPTID = J.DEPTID 
						AND C_EFFDT.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE()), 1, 11)
				)