|
SELECT S.USERID , T.DBNAME , H.PTIASPRPTNO , H.PTIASPRPTNOBASE , H.PTIASPSPVERS , H.PTIASPRPTSUBJECT , H.PTIASPPRDID , H.PTIASPPRDCMP , M.PTIASPPRDCMPNM , H.PTIASPPRDSUBCMP , M.PTIASPPRDSUBCMPNM ,
H.PTIASPRPTFIXDT , H.PTIASPRPTPOSTDT , H.PTIASPRPTTYPECODE , H.PTIASPRPTTYPE , H.PTIASPRPTSEVCODE , H.PTIASPRPTSEVERITY , H.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 INSTALLEDCD , H.PTIASPSPVERS - L.PTIAIMGAPPLFRM AS PTIASPAPLDLAG , L.PTIAIMGAPPLFRM , NVL2(G.PTIASPRPTNO , 'E' , 'B') AS ENHANCEMENT FROM PS_PTIASPRPTHEAD H JOIN PS_PTIAUSERTGTSELS S ON H.PTIASPRPTNO >1000 AND H.PTIASPRPTNO <600000000 AND NOT EXISTS ( SELECT 'X' FROM PS_PTIASPRPTTAGS A WHERE
A.PTIASPRPTNO=H.PTIASPRPTNO AND A.PTIASPRPTTAGNAME='Standard' AND A.PTIASPRPTTAGVALUE='Required At Upgrade') JOIN PS_PTIASPTARGETS T ON S.DBNAME =T.DBNAME LEFT JOIN PS_PTIASPLOGTGT L ON
T.DBNAME=L.DBNAME AND H.PTIASPRPTNO=L.PTIASPRPTNO JOIN PS_PTIASPPRD P ON H.PTIASPPRDID=P.PTIASPPRDID LEFT JOIN PS_PTIASPINSTPRD I ON T.DBNAME =I.DBNAME AND P.PTIASPPRDGRPCD=I.PTIASPPRDGRPCD AND
P.PTIASPPRDCD =I.PTIASPPRDCD LEFT JOIN ( SELECT PTIASPRPTNO FROM PS_PTIASPRPTTAGS WHERE PTIASPRPTTAGNAME='Enhancement' AND PTIASPRPTTAGVALUE='Enhancement') G ON H.PTIASPRPTNO=G.PTIASPRPTNO LEFT JOIN
PS_PTIASP_SCOMP_VW M ON H.PTIASPPRDID=M.PTIASPPRDID AND H.PTIASPPRDCMP=M.PTIASPPRDCMP AND H.PTIASPPRDSUBCMP=M.PTIASPPRDSUBCMP
|
USERID