SELECT DISTINCT PSEC.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNF.FIRST_NAME||' '||PPNF.LAST_NAME EMPLOYEE_NAME,
TO_CHAR(PPOSE.DATE_START,'DD-MON-YYYY' ) START_DATE,
PJS_OLD.NAME OLD_JOB,
PJS_NEW.NAME NEW_JOB
FROM PER_PERSON_NAMES_F PPNF,
PER_PERSON_SECURED_LIST_V PSEC,
PER_ALL_ASSIGNMENTS_M PAAM_OLD,
PER_ALL_ASSIGNMENTS_M PAAM_NEW,
PER_PERIODS_OF_SERVICE PPOSE,
PER_PEOPLE_LEGISLATIVE_F PPLSF,
PER_JOBS PJS_OLD,
PER_JOBS PJS_NEW
WHERE PPNF.PERSON_ID=PSEC.PERSON_ID
AND PPNF.PERSON_ID=PPLSF.PERSON_ID
AND PAAM_NEW.PERSON_ID(+) =PSEC.PERSON_ID
AND PAAM_OLD.ASSIGNMENT_ID=PAAM_NEW.ASSIGNMENT_ID
AND PSEC.PERSON_ID=PPOSE.PERSON_ID
AND PPOSE.PERIOD_OF_SERVICE_ID=PAAM_NEW.PERIOD_OF_SERVICE_ID
AND PAAM_NEW.JOB_ID= PJS_NEW.JOB_ID
AND PAAM_OLD.JOB_ID = PJS_OLD.JOB_ID
AND PAAM_NEW.PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND PAAM_NEW.ASSIGNMENT_TYPE = 'E'
AND PAAM_NEW.ASSIGNMENT_STATUS_TYPE ='ACTIVE'
AND PAAM_NEW.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PAAM_OLD.EFFECTIVE_END_DATE = PAAM_NEW.EFFECTIVE_START_DATE-1
AND PJS_NEW.JOB_ID<>PJS_OLD.JOB_ID
AND TRUNC(SYSDATE ) BETWEEN PSEC.EFFECTIVE_START_DATE AND PSEC.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPLSF.EFFECTIVE_START_DATE AND PPLSF.EFFECTIVE_END_DATE
ORDER BY PSEC.PERSON_NUMBER ASC
