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