FO_CAN_CITY_OTH

(SQL View)

SELECT A.CANDIDATE_ID , %Coalesce(B.CITY_OTHER, C.CITY_OTHER) , %Coalesce(B.STATE_OTHER, C.STATE_OTHER) , %Coalesce(B.POSTAL_OTHER, C.POSTAL_OTHER) FROM %Table(FO_CANDIDATE_W) A ,%Table(APPL_DATA_EFFDT) B , %Table(PERS_DATA_EFFDT) C WHERE A.CANDIDATE_ID = C.EMPLID (+) AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_PERS_DATA_EFFDT C1 WHERE C.EMPLID = C1.EMPLID AND C1.EFFDT = %CurrentDateIn) AND A.CANDIDATE_ID = B.APPLID (+) AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_APPL_DATA_EFFDT B1 WHERE B.APPLID = B1.APPLID AND B1.EFFDT = %CurrentDateIn) AND A.EMPL_STATUS ='A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 CANDIDATE_ID Character(11) VARCHAR2(11) NOT NULL Used as a general ID for anything from Employee id to other types of ID like asset and material.

2 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other

3 STATE Character(6) VARCHAR2(6) NOT NULL State

4 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	CANDIDATE_ID	Character(11)	VARCHAR2(11) NOT NULL	Used as a general ID for anything from Employee id to other types of ID like asset and material.
2	CITY_OTHER	Character(30)	VARCHAR2(30) NOT NULL	City Other
3	STATE	Character(6)	VARCHAR2(6) NOT NULL	State
4	POSTAL	Character(12)	VARCHAR2(12) NOT NULL	Postal