SELECT DISTINCT PPSLV.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNF.FIRST_NAME||' '||PPNF.LAST_NAME EMPLOYEE_NAME,
PDTS.NAME DEPARTMENT_NAME,
HLOCA.LOCATION_NAME LOCATION_NAME,
PJBS.NAME JOB_TITLE_NAME,
PGRS.NAME JOB_GRADE_NAME,
PLYRS.NAME LEGAL_EMPLOYER_NAME,
DECODE(CMP_COMP_CARD.CALCULATE_PREVIOUS_SAL(CPSY.ASSIGNMENT_ID, CPSY.DATE_FROM),'',
CPSY.SALARY_AMOUNT,
CMP_COMP_CARD.CALCULATE_PREVIOUS_SAL(CPSY.ASSIGNMENT_ID, CPSY.DATE_FROM)) OLD_SALARY,
CPSY.SALARY_AMOUNT NEW_SALARY,
CMP_COMP_CARD.CALCULATE_SAL_CHANGE_PCT(CPSY.ASSIGNMENT_ID, CPSY.DATE_FROM) PERECENTAGE_OF_CHANGE
FROM PER_PERSON_SECURED_LIST_V PPSLV,
PER_GRADES PGRS,
PER_ALL_ASSIGNMENTS_M PAAM,
HCM_LOOKUPS HUPS,
PER_PERSON_NAMES_F PPNF,
PER_LEGAL_EMPLOYERS PLYRS,
PER_DEPARTMENTS PDTS,
HR_LOCATIONS_ALL HLOCA,
PER_JOBS PJBS,
PER_PEOPLE_LEGISLATIVE_F LPPF,
CMP_SALARY CPSY
WHERE PPNF.PERSON_ID =PPSLV.PERSON_ID
AND PPNF.PERSON_ID =LPPF.PERSON_ID
AND PAAM.PERSON_ID(+) = PPSLV.PERSON_ID
AND HUPS.LOOKUP_TYPE='SEX'
AND HUPS.LOOKUP_CODE=LPPF.SEX(+)
AND PAAM.ORGANIZATION_ID = PDTS.ORGANIZATION_ID(+)
AND PAAM.LOCATION_ID = HLOCA.LOCATION_ID(+)
AND PAAM.JOB_ID = PJBS.JOB_ID(+)
AND PAAM.GRADE_ID = PGRS.GRADE_ID(+)
AND CPSY.ASSIGNMENT_ID(+) = PAAM.ASSIGNMENT_ID
AND PAAM.PRIMARY_ASSIGNMENT_FLAG ='Y'
AND PAAM.ASSIGNMENT_TYPE ='E'
AND PAAM.ASSIGNMENT_STATUS_TYPE ='ACTIVE'
AND PAAM.EFFECTIVE_LATEST_CHANGE ='Y'
AND PPNF.NAME_TYPE ='GLOBAL'
AND PAAM.LEGAL_ENTITY_ID=PLYRS.ORGANIZATION_ID(+)
AND TRUNC(CPSY.DATE_FROM) BETWEEN PPSLV.EFFECTIVE_START_DATE AND PPSLV.EFFECTIVE_END_DATE
AND TRUNC(CPSY.DATE_FROM) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC(CPSY.DATE_FROM) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND TRUNC(CPSY.DATE_FROM) BETWEEN LPPF.EFFECTIVE_START_DATE AND LPPF.EFFECTIVE_END_DATE
AND TRUNC(CPSY.DATE_FROM) BETWEEN PDTS.EFFECTIVE_START_DATE AND PDTS.EFFECTIVE_END_DATE
AND TRUNC(CPSY.DATE_FROM) BETWEEN PLYRS.EFFECTIVE_START_DATE AND PLYRS.EFFECTIVE_END_DATE
