SPB_PRV_ROST_VW

(SQL View)
 Index Back

Consultant Roster View

SELECT A.PERSON_ID , A.FIRST_NAME , A.LAST_NAME , A.USER_ACC_STATUS , B.PROJ_ROLE , B.PROVIDER_CONTACT FROM PS_SPB_PERSON_TBL A , PS_SPB_SERVICE_PRV B WHERE B.PERSON_ID = A.PERSON_ID AND B.SP_FUNCTION_TYPE = 'PROV' AND A.USER_ACC_STATUS = '1'

SELECT A.PERSON_ID , A.FIRST_NAME , A.LAST_NAME , A.USER_ACC_STATUS , B.PROJ_ROLE , B.PROVIDER_CONTACT , B.VENDOR_SETID ,B.VENDOR_ID FROM PS_SPB_PERSON_TBL A , PS_SPB_SERVICE_PRV B WHERE B.PERSON_ID = A.PERSON_ID AND (B.SP_FUNCTION_TYPE = 'PROV' OR B.SP_FUNCTION_TYPE = 'INDP') AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_SPB_SERVICE_PRV D WHERE D.PERSON_ID = B.PERSON_ID AND D.SP_FUNCTION_TYPE = B.SP_FUNCTION_TYPE AND D.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PERSON_ID Character(15) VARCHAR2(15) NOT NULL Person ID
2 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
3 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
4 USER_ACC_STATUS Character(4) VARCHAR2(4) NOT NULL Account Status
0=Inactive
1=Active
2=Pending Registration
5 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role
6 PROVIDER_CONTACT Character(15) VARCHAR2(15) NOT NULL PROVIDER CONTACT
7 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
8 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier