|
SELECT C2.USERID , C2.DBNAME , C2.PTIASPRPTNO , C2.PTIASPRPTNOBASE , C2.PTIASPSPVERS , C2.PTIASPRPTSUBJECT , C2.PTIASPPRDID , C2.PTIASPPRDCMP , M.PTIASPPRDCMPNM , C2.PTIASPPRDSUBCMP , M.PTIASPPRDSUBCMPNM , C2.PTIASPRPTFIXDT , C2.PTIASPRPTPOSTDT , C2.PTIASPRPTTYPECODE , C2.PTIASPRPTTYPE , C2.PTIASPRPTSEVCODE , C2.PTIASPRPTSEVERITY , C2.RELEASELABEL , NVL2(L.PTIASPRPTNO , 'P' , 'N') AS PTIASPAPPLIED , L.PTIASPPKGAPPLYDT , P.PTIASPPRDGRPCD , P.PTIASPPRDCD , CASE WHEN SUBSTR(P.PTIASPPRDNAME , 1 , 22) = 'PeopleSoft Enterprise ' THEN SUBSTR(P.PTIASPPRDNAME , 23) ELSE P.PTIASPPRDNAME END AS PTIASPPRDNAME , NVL2(I.PTIASPPRDCD , 'Y' , 'N') AS PTIASPPRDINSTCD , C2.PTIASPRPTTAGNAME , C2.PTIASPRPTTAGVALUE , CASE WHEN C2.MINTOOLS='z' THEN ' Available' ELSE MINTOOLS END AS MINTOOLS FROM ( SELECT C1.USERID , C1.DBNAME , C1.PTIASPRPTNO , C1.PTIASPRPTNOBASE , C1.PTIASPSPVERS , C1.PTIASPRPTSUBJECT , C1.PTIASPPRDID , C1.PTIASPPRDCMP , C1.PTIASPPRDSUBCMP , C1.PTIASPRPTCREATEDT , C1.PTIASPRPTFIXDT , C1.PTIASPCLSDDT , C1.PTIASPRPTPOSTDT , C1.PTIASPRPTTYPECODE , C1.PTIASPRPTTYPE , C1.PTIASPRPTSEVCODE , C1.PTIASPRPTSEVERITY , C1.RELEASELABEL , C1.PTIASPRPTTAGNAME , C1.PTIASPRPTTAGVALUE , MIN( CASE WHEN D.PTIASPTOOLSMAJOR=C1.PTIASPTOOLSMAJOR THEN ( CASE WHEN D.PTIASPTOOLSMINOR>C1.PTIASPTOOLSMINOR THEN D.PTIASPTOOLSMAJOR || '.' || TO_CHAR(D.PTIASPTOOLSMINOR , 'FM09') ELSE ' Available' END) WHEN D.PTIASPTOOLSMAJOR>C1.PTIASPTOOLSMAJOR THEN D.PTIASPTOOLSMAJOR || '.' || TO_CHAR(D.PTIASPTOOLSMINOR , 'FM09') ELSE 'z' END) AS MINTOOLS FROM ( SELECT S.USERID , T.DBNAME , H.PTIASPRPTNO , H.PTIASPRPTNOBASE , H.PTIASPSPVERS , H.PTIASPRPTSUBJECT , H.PTIASPPRDID , H.PTIASPPRDCMP , H.PTIASPPRDSUBCMP , H.PTIASPRPTCREATEDT , H.PTIASPRPTFIXDT , H.PTIASPCLSDDT , H.PTIASPRPTPOSTDT , H.PTIASPRPTTYPECODE , H.PTIASPRPTTYPE , H.PTIASPRPTSEVCODE , H.PTIASPRPTSEVERITY , H.RELEASELABEL , T.PTIASPTOOLSMAJOR , T.PTIASPTOOLSMINOR , G.PTIASPRPTTAGNAME , G.PTIASPRPTTAGVALUE FROM PS_PTIASPRPTHEAD H , PS_PTIAUSERTGTSELS S , PS_PTIASPTARGETS T , PS_PTIASPRPTTAGS G WHERE H.PTIASPRPTNO >1000 AND H.PTIASPRPTNO <600000000 AND S.DBNAME =T.DBNAME AND H.PTIASPRPTNO =G.PTIASPRPTNO AND G.PTIASPRPTTAGNAME='Enhancement' AND G.PTIASPRPTTAGVALUE='Enhancement' AND NOT EXISTS ( SELECT 'X' FROM PS_PTIASPRPTTAGS A WHERE A.PTIASPRPTNO=H.PTIASPRPTNO AND A.PTIASPRPTTAGNAME='Standard' AND A.PTIASPRPTTAGVALUE='Required At Upgrade')) C1 LEFT JOIN PS_PTIASPRPTTLSDEP D ON C1.PTIASPRPTNO=D.PTIASPRPTNO GROUP BY C1.USERID , C1.DBNAME , C1.PTIASPRPTNO , C1.PTIASPRPTNOBASE , C1.PTIASPSPVERS , C1.PTIASPRPTSUBJECT , C1.PTIASPPRDID , C1.PTIASPPRDCMP , C1.PTIASPPRDSUBCMP , C1.PTIASPRPTCREATEDT , C1.PTIASPRPTFIXDT , C1.PTIASPCLSDDT , C1.PTIASPRPTPOSTDT , C1.PTIASPRPTTYPECODE , C1.PTIASPRPTTYPE , C1.PTIASPRPTSEVCODE , C1.PTIASPRPTSEVERITY , C1.RELEASELABEL, C1.PTIASPRPTTAGNAME, C1.PTIASPRPTTAGVALUE ) C2 LEFT JOIN PS_PTIASPLOGTGT L ON C2.DBNAME=L.DBNAME AND C2.PTIASPRPTNO=L.PTIASPRPTNO JOIN PS_PTIASPPRD P ON C2.PTIASPPRDID=P.PTIASPPRDID LEFT JOIN PS_PTIASPINSTPRD I ON C2.DBNAME =I.DBNAME AND P.PTIASPPRDGRPCD=I.PTIASPPRDGRPCD AND P.PTIASPPRDCD =I.PTIASPPRDCD LEFT JOIN PS_PTIASP_SCOMP_VW M ON C2.PTIASPPRDID=M.PTIASPPRDID AND C2.PTIASPPRDCMP=M.PTIASPPRDCMP AND C2.PTIASPPRDSUBCMP=M.PTIASPPRDSUBCMP
|
USERID