SELECT DISTINCT PPSLV.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNF.FIRST_NAME||' '||PPNF.LAST_NAME EMPLOYEE_NAME,
TO_CHAR(ADDR_OLD.EFFECTIVE_START_DATE,'DD-MON-YYYY') OLD_ADDRESS_START_DATE,
TO_CHAR( ADDR_OLD.EFFECTIVE_END_DATE,'DD-MON-YYYY') OLD_ADDRESS_END_DATE ,
ADDR_OLD.ADDRESS_LINE_1 OLD_ADDRESS_LINE_1 ,
ADDR_OLD.ADDRESS_LINE_2 OLD_ADDRESS_LINE_2,
ADDR_OLD.BUILDING OLD_BUILDING,
ADDR_OLD.TOWN_OR_CITY OLD_TOWN_OR_CITY ,
TO_CHAR(ADDR_NEW.EFFECTIVE_START_DATE,'DD-MON-YYYY') NEW_ADDRESS_START_DATE,
TO_CHAR(ADDR_NEW.EFFECTIVE_END_DATE ,'DD-MON-YYYY') NEW_ADDRESS_END_DATE ,
ADDR_NEW.ADDRESS_LINE_1 NEW_ADDRESS_LINE_1,
ADDR_NEW.ADDRESS_LINE_2 NEW_ADDRESS_LINE_2,
ADDR_NEW.BUILDING NEW_BUILDING ,
ADDR_NEW.TOWN_OR_CITY NEW_TOWN_OR_CITY
FROM PER_PERSON_SECURED_LIST_V PPSLV,
PER_PERSON_ADDR_USAGES_F PPAUF_OLD,
PER_PERSON_ADDR_USAGES_F PPAUF_NEW,
PER_ADDRESSES_F ADDR_OLD,
PER_ADDRESSES_F ADDR_NEW,
PER_PERSON_NAMES_F PPNF
WHERE 1 =1
AND PPNF.PERSON_ID =PPSLV.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PPSLV.PERSON_ID =PPAUF_OLD.PERSON_ID
AND PPSLV.PERSON_ID =PPAUF_NEW.PERSON_ID
AND PPAUF_OLD.ADDRESS_ID=ADDR_OLD.ADDRESS_ID
AND PPAUF_NEW.ADDRESS_ID =ADDR_NEW.ADDRESS_ID
AND PPAUF_OLD.PERSON_ADDR_USAGE_ID= PPAUF_NEW.PERSON_ADDR_USAGE_ID
AND PPAUF_OLD.ADDRESS_TYPE=PPAUF_NEW.ADDRESS_TYPE
AND PPAUF_OLD.ADDRESS_TYPE='HOME'
AND ADDR_OLD.COUNTRY=ADDR_NEW.COUNTRY
AND PPAUF_OLD.EFFECTIVE_END_DATE = PPAUF_NEW.EFFECTIVE_START_DATE-1
AND ADDR_OLD.EFFECTIVE_END_DATE = ADDR_NEW.EFFECTIVE_START_DATE-1
AND TRUNC(SYSDATE )BETWEEN PPSLV.EFFECTIVE_START_DATE AND PPSLV.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ADDR_NEW.EFFECTIVE_START_DATE AND ADDR_NEW.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPAUF_NEW.EFFECTIVE_START_DATE AND PPAUF_NEW.EFFECTIVE_END_DATE
