CCR_CODE_VW

(SQL View)
 Index Back

SELECT A.DUNS_NUMBER , A.CCR_DUNS_PLUS4 , A.CCR_CD_QUAL , A.CCR_VNDR_CODE , B.DESCR , 'A' FROM PS_CCR_VNDR_CD A LEFT OUTER JOIN PS_CCR_CD_TBL B ON A.CCR_CD_QUAL = B.CCR_CD_QUAL AND A.CCR_VNDR_CODE = B.CCR_VNDR_CODE WHERE A.SOURCE_SYSTEM = 'FED' AND NOT EXISTS ( SELECT 'X' FROM PS_SIC_CODE_TBL C WHERE C.SIC_CD_QUAL = A.CCR_CD_QUAL) UNION SELECT A.DUNS_NUMBER , A.CCR_DUNS_PLUS4 , A.CCR_CD_QUAL , A.CCR_VNDR_CODE , B.DESCR100 , 'B' FROM PS_CCR_VNDR_CD A , PS_SIC_CODE_TBL B WHERE A.CCR_CD_QUAL = B.SIC_CD_QUAL AND A.CCR_VNDR_CODE = B.SIC_CODE AND A.SOURCE_SYSTEM = 'FED' UNION SELECT DISTINCT A.DUNS_NUMBER , A.CCR_DUNS_PLUS4 , A.CCR_CD_QUAL , A.CCR_VNDR_CODE , ' ' , 'C' FROM PS_CCR_VNDR_CD A WHERE A.SOURCE_SYSTEM = 'FED' AND EXISTS ( SELECT 'X' FROM PS_SIC_CODE_TBL C WHERE C.SIC_CD_QUAL = A.CCR_CD_QUAL) AND NOT EXISTS ( SELECT 'X' FROM PS_SIC_CODE_TBL C WHERE C.SIC_CD_QUAL = A.CCR_CD_QUAL AND C.SIC_CODE = A.CCR_VNDR_CODE)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 DUNS_NUMBER Character(9) VARCHAR2(9) NOT NULL DUNS Number
2 CCR_DUNS_PLUS4 Character(4) VARCHAR2(4) NOT NULL DUNS Suffix
3 CCR_CD_QUAL Character(3) VARCHAR2(3) NOT NULL Codetype
BUS=Business Types
FSC=Federal Supply Class
ORG=Organization Codes
PSC=Public Service Codes
SIC=Standard Industry Codes
USA=NAICS Code
4 CCR_VNDR_CODE Character(40) VARCHAR2(40) NOT NULL Vendor CCR Codes
5 DESCR100 Character(100) VARCHAR2(100) NOT NULL Length 100 Description
6 SOURCE_FLAG Character(1) VARCHAR2(1) NOT NULL Team member created from
6=Primavera P6
G=Proposal Management
M=MSP
P=Projects online
R=Resource Management