PTIASPPRDSTATVW

(SQL View)
 Index Back

Products Status

Union of products and installed products

SELECT DISTINCT DBS.DBNAME , PRODUCTS.PTIASPPRDID , PRODUCTS.PTIASPPRDGRPCD , PRODUCTS.PTIASPPRDCD , PRODUCTS.PTIASPPRDNAME , CASE WHEN SUBSTR(PRODUCTS.PTIASPPRDNAME , 1 , 22) = 'PeopleSoft Enterprise ' THEN SUBSTR(PRODUCTS.PTIASPPRDNAME , 23) ELSE PRODUCTS.PTIASPPRDNAME END , 'N' , 'Not Installed' FROM PS_PTIASPPRD PRODUCTS , PS_PTIASPUSERTGTS DBS WHERE 1=1 AND NOT EXISTS ( SELECT * FROM PS_PTIASPINSTPRD INSTALLED_PRODUCTS WHERE DBS.DBNAME = INSTALLED_PRODUCTS.DBNAME AND PRODUCTS.PTIASPPRDGRPCD = INSTALLED_PRODUCTS.PTIASPPRDGRPCD AND PRODUCTS.PTIASPPRDCD = INSTALLED_PRODUCTS.PTIASPPRDCD) UNION SELECT INSTALLED_PRODUCTS.DBNAME , PRODUCTS.PTIASPPRDID , PRODUCTS.PTIASPPRDGRPCD , PRODUCTS.PTIASPPRDCD , PRODUCTS.PTIASPPRDNAME , CASE WHEN SUBSTR(PRODUCTS.PTIASPPRDNAME , 1 , 22) = 'PeopleSoft Enterprise ' THEN SUBSTR(PRODUCTS.PTIASPPRDNAME , 23) ELSE PRODUCTS.PTIASPPRDNAME END , INSTALLED_PRODUCTS.PTIASPINSTALLTBL , 'Installed' FROM PS_PTIASPPRD PRODUCTS , PS_PTIASPINSTPRD INSTALLED_PRODUCTS , PS_PTIASPUSERTGTS DBS WHERE 1=1 AND DBS.DBNAME = INSTALLED_PRODUCTS.DBNAME AND PRODUCTS.PTIASPPRDCD = INSTALLED_PRODUCTS.PTIASPPRDCD AND PRODUCTS.PTIASPPRDGRPCD = INSTALLED_PRODUCTS.PTIASPPRDGRPCD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 DBNAME Character(8) VARCHAR2(8) NOT NULL Database Name
2 PTIASPPRDID Number(8,0) INTEGER NOT NULL Product ID
3 PTIASPPRDGRPCD Character(12) VARCHAR2(12) NOT NULL Product Group
4 PTIASPPRDCD Character(8) VARCHAR2(8) NOT NULL Product
5 PTIASPPRDNAME Character(240) VARCHAR2(240) NOT NULL Product Name
6 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
7 PTIASPINSTALLTBL Character(30) VARCHAR2(30) NOT NULL Column name on PS_INSTALLATION
8 PTIA_TEXT1 Character(15) VARCHAR2(15) NOT NULL Text field for PTIA use.