PTIASPPRDSTATVW

 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 Field Type Column Type Description
DBNAME Character(8) VARCHAR2(8) NOT NULL Database Name
PTIASPPRDID Number(8,0) INTEGER NOT NULL Product ID
PTIASPPRDGRPCD Character(12) VARCHAR2(12) NOT NULL Product Group
PTIASPPRDCD Character(8) VARCHAR2(8) NOT NULL Product
PTIASPPRDNAME Character(240) VARCHAR2(240) NOT NULL Product Name
DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
PTIASPINSTALLTBL Character(30) VARCHAR2(30) NOT NULL Column name on PS_INSTALLATION
PTIA_TEXT1 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