PTIASPPRDVW

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 Field Type Column Type Description
DBNAME Character(8) VARCHAR2(8) NOT NULL Database Name
PTIASPPRDNAME Character(240) VARCHAR2(240) NOT NULL Product Name
PTIA_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL Percent field for PTIA use.
PTIA_TEXT1 Character(15) VARCHAR2(15) NOT NULL Text field for PTIA use.
PTIA_TEXT2 Character(15) VARCHAR2(15) NOT NULL Text field for PTIA use.
index back (c)David Kurtz 2020, www.go-faster.co.uk PeopleTools 8.58
PTRef generated on 21-DEC-20