|
SELECT DISTINCT ADI.PTADSNAME , ADI.RECNAME , C.CLASSID , C.TREE_NAME , C.ACCESS_GROUP , CASE WHEN (C.READ_ADS_AGRP='Y' AND C.WRITE_ADS_AGRP='Y') THEN 'RW' WHEN C.WRITE_ADS_AGRP='Y' THEN 'W' WHEN C.READ_ADS_AGRP='Y' THEN 'R' END FROM PSADSDEFNITEM ADI , PSPTSCRTY_ADS_A C WHERE ADI.RECNAME NOT IN ( SELECT RD.RELLANGRECNAME FROM PSRECDEFN RD WHERE RD.RECNAME IN ( SELECT DISTINCT ADI2.RECNAME FROM PSADSDEFNITEM ADI2 WHERE ADI2.PTADSNAME = ADI.PTADSNAME ) ) AND ADI.RECNAME IN ( SELECT DISTINCT B.TREE_NODE FROM PSTREEDEFN A , PSTREENODE B , PSTREENODE E , PSRECDEFN Z WHERE A.SETID =' ' AND A.TREE_STRCT_ID='ACCESS_GROUP' AND A.EFF_STATUS ='A' AND A.EFFDT = ( SELECT MAX(D.EFFDT) FROM PSTREEDEFN D WHERE D.SETID =' ' AND D.TREE_NAME=A.TREE_NAME AND D.EFFDT <= %CurrentDateIn ) AND C.TREE_NAME = A.TREE_NAME AND ( C.READ_ADS_AGRP ='Y' OR C.WRITE_ADS_AGRP='Y') AND B.SETID =' ' AND B.TREE_NAME = E.TREE_NAME AND B.EFFDT = E.EFFDT AND B.TREE_NODE_TYPE='R' AND B.TREE_NODE = Z.RECNAME AND Z.RECTYPE IN (0, 1, 5, 6) AND E.SETID =' ' AND E.TREE_NAME = A.TREE_NAME AND E.EFFDT = A.EFFDT AND E.TREE_NODE_TYPE='G' AND B.TREE_NODE_NUM BETWEEN E.TREE_NODE_NUM AND E.TREE_NODE_NUM_END AND C.ACCESS_GROUP=E.TREE_NODE AND ( (NOT EXISTS ( SELECT 'X' FROM PSPTSCRTY_ADS_A F WHERE F.CLASSID = C.CLASSID AND F.TREE_NAME=A.TREE_NAME AND ((F.READ_ADS_AGRP='N' AND C.READ_ADS_AGRP ='Y') OR (F.WRITE_ADS_AGRP='N' AND C.WRITE_ADS_AGRP ='Y'))) ) OR (E.TREE_NODE_NUM = ( SELECT MAX(G.TREE_NODE_NUM) FROM PSTREENODE G , PS_SCRTY_ACC_GRP H WHERE G.SETID =' ' AND G.TREE_NAME =A.TREE_NAME AND G.EFFDT=A.EFFDT AND G.TREE_NODE_TYPE='G' AND B.TREE_NODE_NUM BETWEEN G.TREE_NODE_NUM AND G.TREE_NODE_NUM_END AND H.CLASSID=C.CLASSID AND H.TREE_NAME =A.TREE_NAME AND H.ACCESS_GROUP=G.TREE_NODE ) ) ) ) ORDER BY C.CLASSID, C.TREE_NAME, C.ACCESS_GROUP, ADI.PTADSNAME, ADI.RECNAME
|