SELECT PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
ACT_PAB.ACTION_CODE,
ACT_PAB.ACTION_TYPE_CODE ACTION_TYPE,
PACOS.ACTION_DATE,
PACOS.CREATED_BY,
PACOS.LAST_UPDATED_BY,
PACOS.CREATION_DATE,
PACOS.LAST_UPDATE_DATE
FROM PER_ACTION_OCCURRENCES PACOS,
PER_ACTIONS_B ACT_PAB,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_M MAAP
WHERE PACOS.ACTION_ID = ACT_PAB.ACTION_ID
AND ACT_PAB.ACTION_CODE IN ('GLB_TRANSFER')
AND PACOS.PARENT_ENTITY_KEY_ID=PAPF.PERSON_ID
AND PAPF.PERSON_ID= MAAP.PERSON_ID
AND MAAP.ASSIGNMENT_TYPE = 'E'
AND MAAP.EFFECTIVE_LATEST_CHANGE='Y'
AND MAAP.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND TRUNC(SYSDATE) BETWEEN MAAP.EFFECTIVE_START_DATE AND MAAP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
