GPGB_EDI_ADR_VW

(SQL View)
 Index Back

Home/Mail Address View

This view is created for fetching home and mail address of employees selected for EDI processing

SELECT DISTINCT A.EMPLID , A.ADDRESS1 , A.ADDRESS2 , A.ADDRESS3 , A.ADDRESS4 , A.CITY , A.POSTAL , A.STATE , A.COUNTRY FROM PS_ADDRESSES A WHERE ((A.EFF_STATUS = 'A' AND A.ADDRESS_TYPE = 'HOME' AND (A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ADDRESSES D WHERE D.EMPLID = A.EMPLID AND D.ADDRESS_TYPE = 'HOME' AND D.EFF_STATUS = 'A' AND 1 = ( SELECT COUNT(*) FROM PS_ADDRESSES A1 WHERE A1.EMPLID = A.EMPLID AND A1.ADDRESS_TYPE = 'HOME')) OR (A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ADDRESSES D WHERE D.EMPLID = A.EMPLID AND D.ADDRESS_TYPE = 'HOME' AND D.EFF_STATUS = 'A' AND D.EFFDT <= %CurrentDateIn AND 1 < ( SELECT COUNT(*) FROM PS_ADDRESSES A1 WHERE A1.EMPLID = A.EMPLID AND A1.ADDRESS_TYPE = 'HOME'))))) OR ((A.EFF_STATUS = 'A' AND A.ADDRESS_TYPE = 'MAIL' AND (A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ADDRESSES D WHERE D.EMPLID = A.EMPLID AND D.EFF_STATUS = 'A' AND D.ADDRESS_TYPE = 'MAIL' AND 1 = ( SELECT COUNT(*) FROM PS_ADDRESSES A1 WHERE A1.EMPLID = D.EMPLID AND A1.ADDRESS_TYPE = D.ADDRESS_TYPE)) OR A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ADDRESSES D WHERE D.EMPLID = A.EMPLID AND D.EFF_STATUS = 'A' AND D.ADDRESS_TYPE = 'MAIL' AND D.effdt <= %CurrentDateIn AND 1 < ( SELECT COUNT(*) FROM PS_ADDRESSES A1 WHERE A1.EMPLID = D.EMPLID AND A1.ADDRESS_TYPE = D.ADDRESS_TYPE))) AND (A.EMPLID NOT IN ( SELECT H.EMPLID FROM PS_ADDRESSES H WHERE H.EMPLID = A.EMPLID AND H.EFF_STATUS = 'A' AND H.ADDRESS_TYPE = 'HOME' AND H.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ADDRESSES G WHERE G.EMPLID = H.EMPLID AND G.ADDRESS_TYPE = 'HOME' AND G.EFF_STATUS = 'A')) AND A.EMPLID IN ( SELECT H.EMPLID FROM PS_ADDRESSES H WHERE H.EMPLID = A.EMPLID AND H.EFF_STATUS = 'A' AND H.ADDRESS_TYPE = 'MAIL' AND H.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ADDRESSES G WHERE G.EMPLID = H.EMPLID AND G.ADDRESS_TYPE = 'MAIL' AND G.EFF_STATUS = 'A'))))))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
3 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
4 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
5 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
6 CITY Character(30) VARCHAR2(30) NOT NULL City
7 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
8 STATE Character(6) VARCHAR2(6) NOT NULL State
9 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country