SP_ASSOC_SPCS_V (SQL View)	Index Back
Find associated Prov Contacts Finds all Service Provider Contacts who are associated with the same supplier/location as that of the logged on SPC

SP_ASSOC_SPCS_V

(SQL View)

Find associated Prov Contacts

Finds all Service Provider Contacts who are associated with the same supplier/location as that of the logged on SPC

SELECT A.PERSON_ID , D.OPRID , A.FIRST_NAME , A.LAST_NAME , A.USER_ACC_STATUS FROM PS_SPB_PERSON_TBL A , PS_SPB_SERVICE_PRV B , PSOPRALIAS D , PSOPRALIAS E WHERE A.PERSON_ID = B.PERSON_ID AND B.SP_FUNCTION_TYPE = 'PRCT' AND E.PERSON_ID = A.PERSON_ID AND E.OPRALIASTYPE = 'SPP' AND EXISTS ( SELECT 'Y' FROM PS_SPB_SERVICE_PRV C , PSOPRALIAS F WHERE C.VENDOR_SETID = B.VENDOR_SETID AND C.VENDOR_ID = B.VENDOR_ID AND C.LOCATION = B.LOCATION AND C.PERSON_ID = D.PERSON_ID AND F.OPRID = D.OPRID )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 PROVIDER_CONTACT Character(15) VARCHAR2(15) NOT NULL PROVIDER CONTACT
Prompt Table: SPA_PRCT_VW

2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

3 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name

4 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name

5 USER_ACC_STATUS Character(4) VARCHAR2(4) NOT NULL Account Status
0=Inactive
1=Active
2=Pending Registration

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	PROVIDER_CONTACT	Character(15)	VARCHAR2(15) NOT NULL	PROVIDER CONTACT Prompt Table: SPA_PRCT_VW
2	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
3	FIRST_NAME	Character(30)	VARCHAR2(30) NOT NULL	First Name
4	LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Last Name
5	USER_ACC_STATUS	Character(4)	VARCHAR2(4) NOT NULL	Account Status 0=Inactive 1=Active 2=Pending Registration