PTIASPBUGSTATVW

(SQL View)
 Index Back

Bug Status

Union to find the status of the bugs

SELECT DBS.USERID , DBS.DBNAME , BUGS.PTIASPRPTNO , BUGS.PTIASPRPTNOBASE , BUGS.PTIASPSPVERS , BUGS.PTIASPRPTSUBJECT , BUGS.PTIASPPRDID , BUGS.PTIASPPRDCMP , BUGS.PTIASPPRDSUBCMP , SCMP.PTIASPPRDCMPNM , SCMP.PTIASPPRDSUBCMPNM , BUGS.PTIASPRPTCREATEDT , BUGS.PTIASPRPTFIXDT , BUGS.PTIASPCLSDDT , BUGS.PTIASPRPTPOSTDT , BUGS.PTIASPRPTTYPECODE , BUGS.PTIASPRPTTYPE , BUGS.PTIASPRPTSEVCODE , BUGS.PTIASPRPTSEVERITY , BUGS.RELEASELABEL , 'N' , NULL , 'Unapplied' FROM PS_PTIASPRPTHEAD BUGS LEFT OUTER JOIN PS_PTIASP_SCOMP_VW SCMP ON BUGS.PTIASPPRDID = SCMP.PTIASPPRDID AND BUGS.PTIASPPRDCMP = SCMP.PTIASPPRDCMP AND BUGS.PTIASPPRDSUBCMP = SCMP.PTIASPPRDSUBCMP , PS_PTIAUSERTGTSELS DBS WHERE 1=1 AND BUGS.PTIASPRPTNO NOT IN ( SELECT DISTINCT APPLIED_BUGS.PTIASPRPTNO FROM PS_PTIASPLOGTGT APPLIED_BUGS WHERE 1=1 AND APPLIED_BUGS.DBNAME = DBS.DBNAME) AND (BUGS.PTIASPRPTNO>1000 AND BUGS.PTIASPRPTNO<600000000) UNION SELECT DBS.USERID , DBS.DBNAME , BUGS.PTIASPRPTNO , BUGS.PTIASPRPTNOBASE , BUGS.PTIASPSPVERS , BUGS.PTIASPRPTSUBJECT , BUGS.PTIASPPRDID , BUGS.PTIASPPRDCMP , BUGS.PTIASPPRDSUBCMP , SCMP.PTIASPPRDCMPNM , SCMP.PTIASPPRDSUBCMPNM , BUGS.PTIASPRPTCREATEDT , BUGS.PTIASPRPTFIXDT , BUGS.PTIASPCLSDDT , BUGS.PTIASPRPTPOSTDT , BUGS.PTIASPRPTTYPECODE , BUGS.PTIASPRPTTYPE , BUGS.PTIASPRPTSEVCODE , BUGS.PTIASPRPTSEVERITY , BUGS.RELEASELABEL , 'Y' , APPLIED_BUGS.PTIASPPKGAPPLYDT , 'Applied' FROM PS_PTIASPLOGTGT APPLIED_BUGS , PS_PTIASPRPTHEAD BUGS LEFT OUTER JOIN PS_PTIASP_SCOMP_VW SCMP ON BUGS.PTIASPPRDID = SCMP.PTIASPPRDID AND BUGS.PTIASPPRDCMP = SCMP.PTIASPPRDCMP AND BUGS.PTIASPPRDSUBCMP = SCMP.PTIASPPRDSUBCMP , PS_PTIAUSERTGTSELS DBS WHERE 1=1 AND BUGS.PTIASPRPTNO = APPLIED_BUGS.PTIASPRPTNO AND APPLIED_BUGS.DBNAME = DBS.DBNAME AND (BUGS.PTIASPRPTNO>1000 AND BUGS.PTIASPRPTNO<600000000)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 USERID Character(30) VARCHAR2(30) NOT NULL A user's ID
2 DBNAME Character(8) VARCHAR2(8) NOT NULL Database Name
3 PTIASPRPTNO Number(10,0) DECIMAL(10) NOT NULL BUG Number

Prompt Table: PTIASPRPTHEAD

4 PTIASPRPTNOBASE Number(10,0) DECIMAL(10) NOT NULL Base BUG Number
5 PTIASPSPVERS Number(5,0) INTEGER NOT NULL Numeric value for Patch Database Version Number
6 PTIASPRPTSUBJECT Character(240) VARCHAR2(240) NOT NULL Bug Subject
7 PTIASPPRDID Number(8,0) INTEGER NOT NULL Product ID
8 PTIASPPRDCMP Character(10) VARCHAR2(10) NOT NULL Product Component
9 PTIASPPRDSUBCMP Character(14) VARCHAR2(14) NOT NULL Product Sub Component
10 PTIASPPRDCMPNM Character(30) VARCHAR2(30) NOT NULL Product Component Name
11 PTIASPPRDSUBCMPNM Character(40) VARCHAR2(40) NOT NULL Product Sub Component
12 PTIASPRPTCREATEDT DateTime(26) TIMESTAMP Date this Bug was Created
13 PTIASPRPTFIXDT DateTime(26) TIMESTAMP Date this Bug was Fixed
14 PTIASPCLSDDT DateTime(26) TIMESTAMP Date this Bug was Closed
15 PTIASPRPTPOSTDT DateTime(26) TIMESTAMP Date this Bug was Posted
16 PTIASPRPTTYPECODE Character(1) VARCHAR2(1) NOT NULL Bug Types
B=Bug
E=Enhancement
17 PTIASPRPTTYPE Character(30) VARCHAR2(30) NOT NULL BUG Type
18 PTIASPRPTSEVCODE Number(3,0) SMALLINT NOT NULL BUG Severity
19 PTIASPRPTSEVERITY Character(45) VARCHAR2(45) NOT NULL BUG Severity Description
20 RELEASELABEL Character(50) VARCHAR2(50) NOT NULL Release Label
00=Core
10=Education and Government
18=Service Industries
19=Comm., Transportation & Util
20=Retail
21=Performance Measurement
22=HealthCare
23=Student Administration
25=U.S. Federal Govt
26=Canadian Govt
30=Intl Translations
32=Netherlands
33=United Kingdom
34=Espanol/Spain
35=France
36=Portuguese
37=Italy
39=German
50=South Africa
60=Latin America Local
61=Mexico
62=Argentina
63=Brazil
81=Japanese
88=Asia/Pacific
99=PeopleSoft Select
21 PTIASPAPPLIED Character(1) VARCHAR2(1) NOT NULL Apply Info
N=Unapplied
O=Other Products
P=Applied
22 PTIASPPKGAPPLYDT Date(10) DATE Apply Date
23 PTIA_TEXT1 Character(15) VARCHAR2(15) NOT NULL Text field for PTIA use.