PTIASPBUGSTSVW2

(SQL View)
Index Back

BUG Status View

BUG Status by database view.

SELECT D.DBNAME ,'Applied' ,'Installed' ,CASE WHEN ( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B1 WHERE B1.PTIASPPRDID IN ( SELECT P1.PTIASPPRDID FROM PS_PTIASPPRD P1 ,PS_PTIASPINSTPRD IP1 WHERE IP1.PTIASPPRDGRPCD = P1.PTIASPPRDGRPCD AND IP1.PTIASPPRDCD = P1.PTIASPPRDCD AND IP1.DBNAME = D.DBNAME AND IP1.PTIASPINSTALLTBL = 'Y' ) ) = 0 ) THEN 100 ELSE ROUND(( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B2 WHERE EXISTS ( SELECT 1 FROM PS_PTIASPLOGTGT L2 WHERE L2.DBNAME = D.DBNAME AND L2.PTIASPRPTNO = B2.PTIASPRPTNO ) AND B2.PTIASPPRDID IN ( SELECT P2.PTIASPPRDID FROM PS_PTIASPPRD P2 ,PS_PTIASPINSTPRD IP2 WHERE IP2.PTIASPPRDGRPCD = P2.PTIASPPRDGRPCD AND IP2.PTIASPPRDCD = P2.PTIASPPRDCD AND IP2.DBNAME = D.DBNAME AND IP2.PTIASPINSTALLTBL = 'Y' ) ) * 100 / ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B3 WHERE B3.PTIASPPRDID IN ( SELECT P3.PTIASPPRDID FROM PS_PTIASPPRD P3 ,PS_PTIASPINSTPRD IP3 WHERE IP3.PTIASPPRDGRPCD = P3.PTIASPPRDGRPCD AND IP3.PTIASPPRDCD = P3.PTIASPPRDCD AND IP3.DBNAME = D.DBNAME AND IP3.PTIASPINSTALLTBL = 'Y' ) ) ), 2) END AS PERCENT FROM PS_PTIASPTGTDBVW D UNION SELECT D.DBNAME ,'Unapplied' ,'Installed' ,CASE WHEN ( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B1 WHERE B1.PTIASPPRDID IN ( SELECT P1.PTIASPPRDID FROM PS_PTIASPPRD P1 ,PS_PTIASPINSTPRD IP1 WHERE IP1.PTIASPPRDGRPCD = P1.PTIASPPRDGRPCD AND IP1.PTIASPPRDCD = P1.PTIASPPRDCD AND IP1.DBNAME = D.DBNAME AND IP1.PTIASPINSTALLTBL = 'Y' ) ) = 0 ) THEN 0 ELSE ROUND(( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B2 WHERE NOT EXISTS ( SELECT 1 FROM PS_PTIASPLOGTGT L2 WHERE L2.DBNAME = D.DBNAME AND L2.PTIASPRPTNO = B2.PTIASPRPTNO ) AND B2.PTIASPPRDID IN ( SELECT P2.PTIASPPRDID FROM PS_PTIASPPRD P2 ,PS_PTIASPINSTPRD IP2 WHERE IP2.PTIASPPRDGRPCD = P2.PTIASPPRDGRPCD AND IP2.PTIASPPRDCD = P2.PTIASPPRDCD AND IP2.DBNAME = D.DBNAME AND IP2.PTIASPINSTALLTBL = 'Y' ) ) * 100 / ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B3 WHERE B3.PTIASPPRDID IN ( SELECT P3.PTIASPPRDID FROM PS_PTIASPPRD P3 ,PS_PTIASPINSTPRD IP3 WHERE IP3.PTIASPPRDGRPCD = P3.PTIASPPRDGRPCD AND IP3.PTIASPPRDCD = P3.PTIASPPRDCD AND IP3.DBNAME = D.DBNAME AND IP3.PTIASPINSTALLTBL = 'Y' ) ) ), 2) END AS PERCENT FROM PS_PTIASPTGTDBVW D UNION SELECT D.DBNAME ,'Applied' ,'Not Installed' ,CASE WHEN ( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B1 WHERE B1.PTIASPPRDID IN ( SELECT P1.PTIASPPRDID FROM PS_PTIASPPRD P1 ,PS_PTIASPINSTPRD IP1 WHERE IP1.PTIASPPRDGRPCD = P1.PTIASPPRDGRPCD AND IP1.PTIASPPRDCD = P1.PTIASPPRDCD AND IP1.DBNAME = D.DBNAME AND IP1.PTIASPINSTALLTBL = 'N' ) ) = 0 ) THEN 100 ELSE ROUND(( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B2 WHERE EXISTS ( SELECT 1 FROM PS_PTIASPLOGTGT L2 WHERE L2.DBNAME = D.DBNAME AND L2.PTIASPRPTNO = B2.PTIASPRPTNO ) AND B2.PTIASPPRDID IN ( SELECT P2.PTIASPPRDID FROM PS_PTIASPPRD P2 ,PS_PTIASPINSTPRD IP2 WHERE IP2.PTIASPPRDGRPCD = P2.PTIASPPRDGRPCD AND IP2.PTIASPPRDCD = P2.PTIASPPRDCD AND IP2.DBNAME = D.DBNAME AND IP2.PTIASPINSTALLTBL = 'N' ) ) * 100 / ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B3 WHERE B3.PTIASPPRDID IN ( SELECT P3.PTIASPPRDID FROM PS_PTIASPPRD P3 ,PS_PTIASPINSTPRD IP3 WHERE IP3.PTIASPPRDGRPCD = P3.PTIASPPRDGRPCD AND IP3.PTIASPPRDCD = P3.PTIASPPRDCD AND IP3.DBNAME = D.DBNAME AND IP3.PTIASPINSTALLTBL = 'N' ) ) ), 2) END AS PERCENT FROM PS_PTIASPTGTDBVW D UNION SELECT D.DBNAME ,'Unapplied' ,'Not Installed' ,CASE WHEN ( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B1 WHERE B1.PTIASPPRDID IN ( SELECT P1.PTIASPPRDID FROM PS_PTIASPPRD P1 ,PS_PTIASPINSTPRD IP1 WHERE IP1.PTIASPPRDGRPCD = P1.PTIASPPRDGRPCD AND IP1.PTIASPPRDCD = P1.PTIASPPRDCD AND IP1.DBNAME = D.DBNAME AND IP1.PTIASPINSTALLTBL = 'N' ) ) = 0 ) THEN 0 ELSE ROUND(( ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B2 WHERE NOT EXISTS ( SELECT 1 FROM PS_PTIASPLOGTGT L2 WHERE L2.DBNAME = D.DBNAME AND L2.PTIASPRPTNO = B2.PTIASPRPTNO ) AND B2.PTIASPPRDID IN ( SELECT P2.PTIASPPRDID FROM PS_PTIASPPRD P2 ,PS_PTIASPINSTPRD IP2 WHERE IP2.PTIASPPRDGRPCD = P2.PTIASPPRDGRPCD AND IP2.PTIASPPRDCD = P2.PTIASPPRDCD AND IP2.DBNAME = D.DBNAME AND IP2.PTIASPINSTALLTBL = 'N' ) ) * 100 / ( SELECT COUNT(*) FROM PS_PTIASPRPTHEAD B3 WHERE B3.PTIASPPRDID IN ( SELECT P3.PTIASPPRDID FROM PS_PTIASPPRD P3 ,PS_PTIASPINSTPRD IP3 WHERE IP3.PTIASPPRDGRPCD = P3.PTIASPPRDGRPCD AND IP3.PTIASPPRDCD = P3.PTIASPPRDCD AND IP3.DBNAME = D.DBNAME AND IP3.PTIASPINSTALLTBL = 'N' ) ) ), 2) END AS PERCENT FROM PS_PTIASPTGTDBVW D ORDER BY 1,4,3

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