DARTS : UM_ORIG_ADDR_VW

SELECT DISTINCT A.EMPLID,
                A.ADDRESS1,
                A.ADDRESS2,
                A.ADDRESS3,
                A.ADDRESS4,
                A.CITY,
                A.STATE,
                B.UM_COUNTY_NAME,
                A.POSTAL,
                A.COUNTRY,
                A.ADDRESS_TYPE,
                NVL(Z.CITIZENSHIP_STATUS, ' ') AS CITIZENSHIP_STATUS,
                NVL(X.COUNTRY, ' ') AS COUNTRY_OTHER
  FROM SYSADM.PS_ADDRESSES A
  LEFT OUTER JOIN SYSADM.PS_UM_ZIP_COUNTY B
    ON (SUBSTR(A.POSTAL, 1, 5) = B.UM_ZIP_CD)
  LEFT OUTER JOIN (SELECT EMPLID,
                          MAX(COUNTRY) AS COUNTRY,
                          MAX(CITIZENSHIP_STATUS) AS CITIZENSHIP_STATUS
                     FROM SYSADM.PS_CITIZENSHIP
                    WHERE (COUNTRY <> 'USA' AND CITIZENSHIP_STATUS <> '4')
                    GROUP BY EMPLID) X
    ON (A.EMPLID = X.EMPLID)
  LEFT OUTER JOIN (SELECT EMPLID, CITIZENSHIP_STATUS CITIZENSHIP_STATUS
                     FROM SYSADM.PS_CITIZENSHIP
                    WHERE COUNTRY = 'USA') Z
    ON (A.EMPLID = Z.EMPLID)
 WHERE A.EFFDT = (SELECT MIN(A_ED.EFFDT)
                    FROM SYSADM.PS_ADDRESSES A_ED
                   WHERE A.EMPLID = A_ED.EMPLID
                     AND A.ADDRESS_TYPE = A_ED.ADDRESS_TYPE
                     AND A_ED.EFFDT <= SYSDATE)
   AND DECODE(A.ADDRESS_TYPE,
              'HOME',
              1,
              'BILL',
              2,
              'MAIL',
              3,
              'BUSN',
              4,
              'CHK',
              5,
              'CAMP',
              6,
              'RESH',
              7,
              8) =
       (SELECT MIN(DECODE(C.ADDRESS_TYPE,
                          'HOME',
                          1,
                          'BILL',
                          2,
                          'MAIL',
                          3,
                          'BUSN',
                          4,
                          'CHK',
                          5,
                          'CAMP',
                          6,
                          'RESH',
                          7,
                          8))
          FROM SYSADM.PS_ADDRESSES C
         WHERE A.EMPLID = C.EMPLID
           AND C.EFFDT = (SELECT MIN(C_ED.EFFDT)
                            FROM SYSADM.PS_ADDRESSES C_ED
                           WHERE C.EMPLID = C_ED.EMPLID
                             AND C.ADDRESS_TYPE = C_ED.ADDRESS_TYPE
                             AND C_ED.EFFDT <= SYSDATE));