VIEW: APPS.HR_ATH_EMPLOYEE_V

Source

View Text - Preformatted

SELECT TO_CHAR(paf.person_id) employee_id ,HR_AUTH_INT.GET_PASSWORD(paf.person_id) pin ,TO_CHAR(paf.business_group_id) employer_id ,TO_CHAR(paf.organization_id) employee_groupid ,HR_AUTH_BRIDGE.GET_PERSON_START_DATE(paf.person_id, paf.period_of_service_id, paf.effective_start_date,paf.assignment_type) start_date ,HR_AUTH_BRIDGE.GET_PERSON_END_DATE(paf.person_id,paf.period_of_service_id, paf.effective_end_date,paf.assignment_type) end_date ,ppf.full_name full_name ,TO_CHAR(ppf.date_of_birth,'MM/DD/YYYY') date_of_birth ,paa.address_line1 address_line1 ,paa.region_1 city ,paa.region_2 state ,paa.postal_code postal_code ,HR_GENERAL.DECODE_JOB(paf.job_id) job ,HR_GENERAL.DECODE_GRADE(paf.grade_id) grade ,ppf.national_identifier national_identifier ,sup.full_name supervisor ,HR_GENERAL.GET_USER_STATUS(paf.assignment_status_type_id) assignment_status ,HR_GENERAL.DECODE_POSITION_LATEST_NAME(paf.position_id) position ,grp.group_name people_group ,HR_GENERAL.DECODE_LOOKUP('BARGAINING_UNIT_CODE',paf.bargaining_unit_code) bargaining_unit ,HR_AUTH_BRIDGE.GET_PROPOSED_SALARY(paf.assignment_id) salary ,HR_AUTH_BRIDGE.GET_SALARY_CHANGE_DATE(paf.assignment_id) change_date ,HR_AUTH_BRIDGE.GET_PERFORMANCE_RATING(paf.person_id) performance_rating FROM per_all_assignments_f paf ,per_addresses paa ,per_all_people_f ppf ,per_all_people_f sup ,pay_people_groups grp WHERE TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date AND paf.assignment_id = hr_auth_bridge.get_assignment_id(ppf.person_id) AND paf.person_id = paa.person_id AND paa.primary_flag = 'Y' AND paf.person_id = ppf.person_id AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND sup.person_id(+)= paf.supervisor_id AND paf.effective_start_date BETWEEN NVL(sup.effective_start_date, paf.effective_start_date) AND NVL(sup.effective_end_date, paf.effective_start_date) AND grp.people_group_id(+)= paf.people_group_id

View Text - HTML Formatted

SELECT TO_CHAR(PAF.PERSON_ID) EMPLOYEE_ID
, HR_AUTH_INT.GET_PASSWORD(PAF.PERSON_ID) PIN
, TO_CHAR(PAF.BUSINESS_GROUP_ID) EMPLOYER_ID
, TO_CHAR(PAF.ORGANIZATION_ID) EMPLOYEE_GROUPID
, HR_AUTH_BRIDGE.GET_PERSON_START_DATE(PAF.PERSON_ID
, PAF.PERIOD_OF_SERVICE_ID
, PAF.EFFECTIVE_START_DATE
, PAF.ASSIGNMENT_TYPE) START_DATE
, HR_AUTH_BRIDGE.GET_PERSON_END_DATE(PAF.PERSON_ID
, PAF.PERIOD_OF_SERVICE_ID
, PAF.EFFECTIVE_END_DATE
, PAF.ASSIGNMENT_TYPE) END_DATE
, PPF.FULL_NAME FULL_NAME
, TO_CHAR(PPF.DATE_OF_BIRTH
, 'MM/DD/YYYY') DATE_OF_BIRTH
, PAA.ADDRESS_LINE1 ADDRESS_LINE1
, PAA.REGION_1 CITY
, PAA.REGION_2 STATE
, PAA.POSTAL_CODE POSTAL_CODE
, HR_GENERAL.DECODE_JOB(PAF.JOB_ID) JOB
, HR_GENERAL.DECODE_GRADE(PAF.GRADE_ID) GRADE
, PPF.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, SUP.FULL_NAME SUPERVISOR
, HR_GENERAL.GET_USER_STATUS(PAF.ASSIGNMENT_STATUS_TYPE_ID) ASSIGNMENT_STATUS
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(PAF.POSITION_ID) POSITION
, GRP.GROUP_NAME PEOPLE_GROUP
, HR_GENERAL.DECODE_LOOKUP('BARGAINING_UNIT_CODE'
, PAF.BARGAINING_UNIT_CODE) BARGAINING_UNIT
, HR_AUTH_BRIDGE.GET_PROPOSED_SALARY(PAF.ASSIGNMENT_ID) SALARY
, HR_AUTH_BRIDGE.GET_SALARY_CHANGE_DATE(PAF.ASSIGNMENT_ID) CHANGE_DATE
, HR_AUTH_BRIDGE.GET_PERFORMANCE_RATING(PAF.PERSON_ID) PERFORMANCE_RATING
FROM PER_ALL_ASSIGNMENTS_F PAF
, PER_ADDRESSES PAA
, PER_ALL_PEOPLE_F PPF
, PER_ALL_PEOPLE_F SUP
, PAY_PEOPLE_GROUPS GRP
WHERE TRUNC(SYSDATE) BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAF.ASSIGNMENT_ID = HR_AUTH_BRIDGE.GET_ASSIGNMENT_ID(PPF.PERSON_ID)
AND PAF.PERSON_ID = PAA.PERSON_ID
AND PAA.PRIMARY_FLAG = 'Y'
AND PAF.PERSON_ID = PPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND SUP.PERSON_ID(+)= PAF.SUPERVISOR_ID
AND PAF.EFFECTIVE_START_DATE BETWEEN NVL(SUP.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_START_DATE)
AND NVL(SUP.EFFECTIVE_END_DATE
, PAF.EFFECTIVE_START_DATE)
AND GRP.PEOPLE_GROUP_ID(+)= PAF.PEOPLE_GROUP_ID