PTIASPPRDVW

(SQL View)
 Index Back

BUG Percent by Product

BUG Percent Applied / Unapplied by Product

SELECT D.DBNAME ,REPLACE(P.PTIASPPRDNAME , 'PeopleSoft Enterprise ' , '') AS PRODUCT ,CASE WHEN ( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B2 WHERE B2.PTIASPPRDID = P.PTIASPPRDID ) = 0 ) THEN 100 ELSE ROUND(( ( SELECT COUNT(*) FROM PS_PTIASPLOGTGT L ,PS_PTIASPRPTHEAD B WHERE L.DBNAME = D.DBNAME AND L.PTIASPRPTNO = B.PTIASPRPTNO AND B.PTIASPPRDID = P.PTIASPPRDID ) * 100 / ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B1 WHERE B1.PTIASPPRDID = P.PTIASPPRDID ) ), 2) END AS PERCENT ,'Applied' ,'Installed' FROM PS_PTIASPTGTDBVW D ,PS_PTIASPPRD P ,PS_PTIASPINSTPRD IP WHERE D.DBNAME = IP.DBNAME AND IP.PTIASPPRDGRPCD = P.PTIASPPRDGRPCD AND IP.PTIASPPRDCD = P.PTIASPPRDCD AND IP.PTIASPINSTALLTBL = 'Y' UNION SELECT D.DBNAME ,REPLACE(P.PTIASPPRDNAME , 'PeopleSoft Enterprise ' , '') AS PRODUCT ,CASE WHEN ( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B2 WHERE B2.PTIASPPRDID = P.PTIASPPRDID ) = 0 ) THEN 0 ELSE ROUND(100 - ( ( SELECT COUNT(*) FROM PS_PTIASPLOGTGT L ,PS_PTIASPRPTHEAD B WHERE L.DBNAME = D.DBNAME AND L.PTIASPRPTNO = B.PTIASPRPTNO AND B.PTIASPPRDID = P.PTIASPPRDID ) * 100 / ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B1 WHERE B1.PTIASPPRDID = P.PTIASPPRDID ) ), 2) END AS PERCENT ,'Unapplied' ,'Installed' FROM PS_PTIASPTGTDBVW D ,PS_PTIASPPRD P ,PS_PTIASPINSTPRD IP WHERE D.DBNAME = IP.DBNAME AND IP.PTIASPPRDGRPCD = P.PTIASPPRDGRPCD AND IP.PTIASPPRDCD = P.PTIASPPRDCD AND IP.PTIASPINSTALLTBL = 'Y' UNION SELECT D.DBNAME ,REPLACE(P.PTIASPPRDNAME , 'PeopleSoft Enterprise ' , '') AS PRODUCT ,CASE WHEN ( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B2 WHERE B2.PTIASPPRDID = P.PTIASPPRDID ) = 0 ) THEN 100 ELSE ROUND(( ( SELECT COUNT(*) FROM PS_PTIASPLOGTGT L ,PS_PTIASPRPTHEAD B WHERE L.DBNAME = D.DBNAME AND L.PTIASPRPTNO = B.PTIASPRPTNO AND B.PTIASPPRDID = P.PTIASPPRDID ) * 100 / ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B1 WHERE B1.PTIASPPRDID = P.PTIASPPRDID ) ), 2) END AS PERCENT ,'Applied' ,'Not Installed' FROM PS_PTIASPTGTDBVW D ,PS_PTIASPPRD P ,PS_PTIASPINSTPRD IP WHERE D.DBNAME = IP.DBNAME AND IP.PTIASPPRDGRPCD = P.PTIASPPRDGRPCD AND IP.PTIASPPRDCD = P.PTIASPPRDCD AND IP.PTIASPINSTALLTBL = 'N' UNION SELECT D.DBNAME ,REPLACE(P.PTIASPPRDNAME , 'PeopleSoft Enterprise ' , '') AS PRODUCT ,CASE WHEN ( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B2 WHERE B2.PTIASPPRDID = P.PTIASPPRDID ) = 0 ) THEN 0 ELSE ROUND(100 - ( ( SELECT COUNT(*) FROM PS_PTIASPLOGTGT L ,PS_PTIASPRPTHEAD B WHERE L.DBNAME = D.DBNAME AND L.PTIASPRPTNO = B.PTIASPRPTNO AND B.PTIASPPRDID = P.PTIASPPRDID ) * 100 / ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B1 WHERE B1.PTIASPPRDID = P.PTIASPPRDID ) ), 2) END AS PERCENT ,'Unapplied' ,'Not Installed' FROM PS_PTIASPTGTDBVW D ,PS_PTIASPPRD P ,PS_PTIASPINSTPRD IP WHERE D.DBNAME = IP.DBNAME AND IP.PTIASPPRDGRPCD = P.PTIASPPRDGRPCD AND IP.PTIASPPRDCD = P.PTIASPPRDCD AND IP.PTIASPINSTALLTBL = 'N' ORDER BY 2 ,1

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 DBNAME Character(8) VARCHAR2(8) NOT NULL Database Name
2 PTIASPPRDNAME Character(240) VARCHAR2(240) NOT NULL Product Name
3 PTIA_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL Percent field for PTIA use.
4 PTIA_TEXT1 Character(15) VARCHAR2(15) NOT NULL Text field for PTIA use.
5 PTIA_TEXT2 Character(15) VARCHAR2(15) NOT NULL Text field for PTIA use.